Database Schema: CREATE TABLE CUSTOMER ( CUS_CODE int, CUS_LNAME varchar(15), CU
ID: 3715686 • Letter: D
Question
Database Schema:
CREATE TABLE CUSTOMER (
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
CREATE TABLE VENDOR (
V_CODE int,
V_NAME varchar(15),
V_CONTACT varchar(50),
V_AREACODE varchar(3),
V_PHONE varchar(8),
V_STATE varchar(2),
V_ORDER varchar(1)
);
ALTER TABLE customer
ADD PRIMARY KEY(CUS_CODE);
ALTER TABLE invoice
ADD PRIMARY KEY (INV_NUMBER);
ALTER TABLE invoice
ADD CONSTRAINT FK_CUS_CODE
FOREIGN KEY (CUS_CODE) REFERENCES customer(CUS_CODE);
ALTER TABLE line
ADD PRIMARY KEY (LINE_NUMBER);
ALTER TABLE line
ADD CONSTRAINT FK_INV_NUMBER
FOREIGN KEY (INV_NUMBER) REFERENCES invoice(INV_NUMBER);
ALTER TABLE product
ADD PRIMARY KEY (P_CODE);
ALTER TABLE line
ADD CONSTRAINT FK_P_CODE
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE);
ALTER TABLE vendor
ADD PRIMARY KEY (V_CODE);
ALTER TABLE product
ADD CONSTRAINT FK_V_CODE
FOREIGN KEY (V_CODE) REFERENCES vendor(V_CODE);
INSERT INTO CUSTOMER VALUES(10010,'Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES(10011,'Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES(10012,'Smith','Kathy','W','615','894-2285','345.86');
INSERT INTO CUSTOMER VALUES(10013,'Olowski','Paul','F','615','894-2180','536.75');
INSERT INTO CUSTOMER VALUES(10014,'Orlando','Myron','','615','222-1672','0');
INSERT INTO CUSTOMER VALUES(10015,'O''Brian','Amy','B','713','442-3381','0');
INSERT INTO CUSTOMER VALUES(10016,'Brown','James','G','615','297-1228','221.19');
INSERT INTO CUSTOMER VALUES(10017,'Williams','George','','615','290-2556','768.93');
INSERT INTO CUSTOMER VALUES(10018,'Farriss','Anne','G','713','382-7185','216.55');
INSERT INTO CUSTOMER VALUES(10019,'Smith','Olette','K','615','297-3809','0');
INSERT INTO INVOICE VALUES(1001,10014,1/16/2016,24.90,1.99,26.89);
INSERT INTO INVOICE VALUES(1002,10011,1/16/2016,9.98,0.80,10.78);
INSERT INTO INVOICE VALUES(1003,10012,1/16/2016,153.85,12.31,166.16);
INSERT INTO INVOICE VALUES(1004,10011,1/16/2017,34.97,2.80,37.77);
INSERT INTO INVOICE VALUES(1005,10018,1/16/2017,70.44,5.64,76.08);
INSERT INTO INVOICE VALUES(1006,10014,1/16/2017,397.83,31.83,429.66);
INSERT INTO INVOICE VALUES(1007,10015,1/16/2017,34.97,2.80,37.77);
INSERT INTO INVOICE VALUES(1008,10011,1/16/2017,399.15,31.93,431.08);
INSERT INTO LINE VALUES(1001,1,'13-Q2/P2',1,14.99,14.99);
INSERT INTO LINE VALUES(1001,2,'23109-HB',1,9.95,9.95);
INSERT INTO LINE VALUES(1002,1,'54778-2T',2,4.99,9.98);
INSERT INTO LINE VALUES(1003,1,'2238/QPD',1,38.95,38.95);
INSERT INTO LINE VALUES(1003,2,'1546-QQ2',1,39.95,39.95);
INSERT INTO LINE VALUES(1003,3,'13-Q2/P2',5,14.99,74.95);
INSERT INTO LINE VALUES(1004,1,'54778-2T',3,4.99,14.97);
INSERT INTO LINE VALUES(1004,2,'23109-HB',2,9.95,19.90);
INSERT INTO LINE VALUES(1005,1,'PVC23DRT',12,5.87,70.44);
INSERT INTO LINE VALUES(1006,1,'SM-18277',3,6.99,20.97);
INSERT INTO LINE VALUES(1006,2,'2232/QTY',1,109.92,109.92);
INSERT INTO LINE VALUES(1006,3,'23109-HB',1,9.95,9.95);
INSERT INTO LINE VALUES(1006,4,'89-WRE-Q',1,256.99,256.99);
INSERT INTO LINE VALUES(1007,1,'13-Q2/P2',2,14.99,29.98);
INSERT INTO LINE VALUES(1007,2,'54778-2T',1,4.99,4.99);
INSERT INTO LINE VALUES(1008,1,'PVC23DRT',5,5.87,29.35);
INSERT INTO LINE VALUES(1008,2,'WR3/TT3',3,119.95,359.85);
INSERT INTO LINE VALUES(1008,3,'23109-HB',1,9.95,9.95);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','2015-11-03',8,5,109.99,0,25595);
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12-13',32,15,14.99,0.05,0);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11-13',18,12,17.49,0,21344);
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01-15',15,8,39.95,0,23119);
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01-15',23,5,43.99,0,23119);
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12-30',8,5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12-24',6,5,99.87,0.05,24288);
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016-01-20',12,5,38.95,0.05,25595);
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01-12',23,10,9.95,0.1,21225);
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer 12 lb.','2016-01-2',8,5,14.40,0.05,0);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015-12-15',43,20,4.99,0,21344);
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02-17',11,5,256.99,0.05,24288);
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02-27',188,75,5.87,0,24004);
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw','2016-03-01',172,75,6.99,0,21225);
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02-14',237,100,8.45,0,21231);
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','2016-01-27',18,5,119.95,0.1,25595);
INSERT INTO VENDOR VALUES(21225,'Bryson, Inc.','Smithson','615','223-3234','TN','Y');
INSERT INTO VENDOR VALUES(21226,'SuperLoo, Inc.','Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES(21231,'D&E Supply','Singh','615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES(21344,'Gomez Bros.','Ortega','615','889-2546','KY','N');
INSERT INTO VENDOR VALUES(22567,'Dome Supply','Smith','901','678-1419','GA','N');
INSERT INTO VENDOR VALUES(23119,'Randsets Ltd.','Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES(24004,'Brackman Bros.','Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES(24288,'ORDVA, Inc.','Hakford','615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES(25443,'B&K, Inc.','Smith','904','227-0093','FL','N');
INSERT INTO VENDOR VALUES(25501,'Damal Supplies','Smythe','615','890-3529','TN','N');
INSERT INTO VENDOR VALUES(25595,'Rubicon Systems','Orton','904','456-0092','FL','Y');
Explanation / Answer
1. select c.CUS_CODE from customer c,invoice i where CUS_AREACODE='615' and c.CUS_CODE=i.CUS_CODE;
The above query will match the customer code with the customer codes present in invoice table and from those customers who belongs to area code 615 will be retrieved.
2. select v.V_CODE from vendor v,product p where v.V_CODE=p.V_CODE;
The above query will match the vendors who are in the products table and will retrieve them.
3. select c.CUS_CODE,c.CUS_LNAME,c.CUS_FNAME from customer c,product p,line l,invoice i where c.CUS_CODE=i.CUS_CODE and i.INV_NUMBER=l.INV_NUMBER and l.PCODE=p.P_CODE and p.P_DESCRIPT='Claw Hammer';
The above query will retrieve the customer who ordered Claw hammer.
4. select c.CUS_CODE,c.CUS_LNAME,c.CUS_FNAME from customer c,product p,line l,invoice i where c.CUS_CODE=i.CUS_CODE and i.INV_NUMBER=l.INV_NUMBER and l.PCODE=p.P_CODE and p.P_DESCRIPT like '%HAMMER' or p.P_DESCRIPT like 'SAW%';
The above query will retrieve the customers who ordered hammer or saw blades.
As per the chegg policy I am alllowed to answer only 4 parts of the question. Please post the reamining as new post.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.