Conditions: Table name:Customer: This Table includes : CUST_NUM,CUST_LNAME,\'CUS

Question

Conditions:

Table name:Customer:

This Table includes : CUST_NUM,CUST_LNAME,'CUST_FNAME,CUST BALANCE

first customer: fname:Jeanne lname:Smith, number:1000, balance:1050.11

second customer: fname:Juan lname:ortega number:1001 balance:840.92

Table name: customer 2

This Table includes: CUST_NUM, CUST_LNAME,CUST_FNAME

Customer1: number:2000, lname:McPherson fname:Anne

Cust2: number:2001 lname:Ortega fname:Juan

Cust3: number:2002 lname Kowalski,fname Jan

Cust4: number 2003 lname Chen fname George

Table name:INVOICE

Table includes: INV_NUM, CUST_NUM,INV_DATE,INV_AMOUNT

1st invoice: inv_num:8000, cust_num:1000, date: 23-Mar-10,amount:235.89

1st invoice: inv_num:8001, cust_num:1001, date: 23-Mar-10,amount:312.82

1st invoice: inv_num:8002, cust_num:1001, date: 30-Mar-10,amount:528.10

1st invoice: inv_num:8003, cust_num:1000, date: 12-Apr-10,amount:194.78

1st invoice: inv_num:8004, cust_num:1000, date: 23-Apr-10,amount:619.44

1.      Create the tables.

2.      Insert the data into the tables you created in Problem 1 as shown in the given conditions

3.      List all of customers who have ordered on

Conditions: Table name:Customer: This Table includes : CUST_NUM,CUST_LNAME,'CUST_FNAME,CUST BALANCE first customer: fname:Jeanne lname:Smith, number:1000, balance:1050.11 second customer: fname:Juan lname:ortega number:1001 balance:840.92 Table name: customer 2 This Table includes: CUST_NUM, CUST_LNAME,CUST_FNAME Customer1: number:2000, lname:McPherson fname:Anne Cust2: number:2001 lname:Ortega fname:Juan Cust3: number:2002 lname Kowalski,fname Jan Cust4: number 2003 lname Chen fname George Table name:INVOICE Table includes: INV_NUM, CUST_NUM,INV_DATE,INV_AMOUNT 1st invoice: inv_num:8000, cust_num:1000, date: 23-Mar-10,amount:235.89 1st invoice: inv_num:8001, cust_num:1001, date: 23-Mar-10,amount:312.82 1st invoice: inv_num:8002, cust_num:1001, date: 30-Mar-10,amount:528.10 1st invoice: inv_num:8003, cust_num:1000, date: 12-Apr-10,amount:194.78 1st invoice: inv_num:8004, cust_num:1000, date: 23-Apr-10,amount:619.44

Explanation / Answer

create table CUSTOMER(

CUST_NUM int,

CUST_LNAME varchar2(20),

CUST_FNAME varchar2(20),

CUST_BALANCE double

);

create table INVOICE(

INV_NUM int,

CUST_NUM int,

INV_DATE date,

INV_AMOUNT double

);

create table CUSTOMER_2(

CUST_NUM int,

CUST_LNAME varchar2(20),

CUST_FNAME varchar2(20)

);

QUESTION - 2

INSERT INTO CUSTOMER (CUST_NUM,CUST_LNAME,CUST_FNAME,CUST_BALANCE) VALUES (1000,'Smith','Jeanne',1050.11);

INSERT INTO CUSTOMER (CUST_NUM,CUST_LNAME,CUST_FNAME,CUST_BALANCE) VALUES (1001,'Ortega','Juan',840.92);

INSERT INTO CUSTOMER_2 (CUST_NUM,CUST_LNAME,CUST_FNAME) VALUES (2000,'McPhreson','Anne');

INSERT INTO CUSTOMER_2 (CUST_NUM,CUST_LNAME,CUST_FNAME) VALUES (2001,'Ortega','Juan');

INSERT INTO CUSTOMER_2 (CUST_NUM,CUST_LNAME,CUST_FNAME) VALUES (2002,'Kowalski','Jan');

INSERT INTO CUSTOMER_2 (CUST_NUM,CUST_LNAME,CUST_FNAME) VALUES (2003,'Chen','George');

INSERT INTO INVOICE (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) VALUES (8000,1000,'23-03-10',235.89);

INSERT INTO INVOICE (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) VALUES (8001,1001,'23-03-10',312.82);

INSERT INTO INVOICE (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) VALUES (8002,1001,'30-03-10',528.10);

INSERT INTO INVOICE (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) VALUES (8003,1000,'12-04-10',194.78);

INSERT INTO INVOICE (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) VALUES (8004,1000,'23-04-10',619.44);

QUESTION - 3

SELECT cust1.CUST_NUM,cust1.CUST_LNAME,cust1.CUST_FNAME

FROM

CUSTOMER cust1

INNER JOIN INVOICE inv1

ON cust1.CUST_NUM = inv1.CUST_NUM

WHERE

inv1.INV_DATE = '23-03-10';

QUESTION - 4

SELECT CUST_NUM,AVG(INV_AMOUNT) AS AVERAGE_AMOUNT

FROM

INVOICE

GROUP BY CUST_NUM;

QUESTION - 5

SELECT CUST_NUM,SUM(INV_AMOUNT) AS AVERAGE_AMOUNT

FROM

INVOICE

GROUP BY CUST_NUM;

QUESTION - 6

SELECT DISTINCT CUST_NUM,CUST_LNAME,CUST_FNAME

FROM

CUSTOMER_2;

QUESTION - 7

SELECT INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT

FROM INVOICE

WHERE INV_AMOUNT >= 1000;

QUESTION - 8

SELECT INV_NUM,INV_AMOUNT,AVG(INVOICE_AMOUNT) AS AVG_INV_AMT, (AVG_INV_AMT - INV_AMOUNT) AS DIFF_AMOUNT

FROM

INVOICE

GROUP BY INV_NUM,INV_AMOUNT;

QUESTION - 9

ALTER TABLE CUSTOMER

ADD CUST_DOB DATE;

ALTER TABLE CUSTOMER

ADD CUST_AGE INT;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.