a) By using MySQL queries , JOIN the following tables to return UNIFIED_TABLE b)
ID: 3728485 • Letter: A
Question
a) By using MySQL queries, JOIN the following tables to return UNIFIED_TABLE
b) Create a view named PURCHASE_INFO that has the same content as the return from UNIFIED_TABLE
****Answers must be typed! Not written!
Create a MySQL database to hold the following table data
Table Data:
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)
);
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');
CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE datetime
);
INSERT INTO INVOICE VALUES('1001','10014','2014-01-16');
INSERT INTO INVOICE VALUES('1002','10011','2014-01-16');
INSERT INTO INVOICE VALUES('1003','10012','2014-01-16');
INSERT INTO INVOICE VALUES('1004','10011','2014-01-17');
INSERT INTO INVOICE VALUES('1005','10018','2014-01-17');
INSERT INTO INVOICE VALUES('1006','10014','2014-01-17');
INSERT INTO INVOICE VALUES('1007','10015','2014-01-17');
INSERT INTO INVOICE VALUES('1008','10011','2014-01-17');
CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95');
CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE datetime,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','2013-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2013-12-13','32','15', '14.99','0.05','21344');
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2013-11-13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2014-01-15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2014-01-15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2013-12-30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2013-12-24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2014-01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2014-01-20','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2014-01-02','8','5','14.40','0.05','');
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2013-12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2014-02-07','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2014-02-20','188','75','5.87','0','');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25','2014-03-01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2014-02-24','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','2014-01-17','18','5','119.95','0.1','25595');
UNIFIED_TABLE
TABLE: CUSTOMER TABLE: LINE I INV_NUMBER LINE NUMBER P CODE I LINE UNITS | LINE PRICE I I cuS_CODE | cuS LINAME | CUS FNAME | CuS_INITIAL I cus AREACODE | CUS_PHONE | CUS_BALANCE I 10810 Ramas Alfred 10015 I o'Brian I Any 1 | PVC23DRT I 1 | SM-18277 | 10817 | Williams George I TABLE: INVOICE TABLE: INVOICE TABLE: PRODUCT INV NUMBER | CUS_CODE | INV_DATE 1001 10014 | 2014-01-16 00:00:00 | 1002|10011 | 2014-01-16 00:00:00 | 1003 10012| 2014-01-16 00:00:00 | 1004 10011 | 2014-01-17 00:00:00 I 100510018 | 2014-01-17 00:00:00 1006 10014| 2014-01-17 00:00:00 100710015 | 2014-01-17 00:00:00 1008 10011 | 2014-01-17 00:00:00 | l p-00H l p-MIN | P-PRICE I P-DISCOUNT I v-co0E 1 l 11QER/31 I Power painter, 15 psi., 3-nozzle 1302/P2 . 2011 12 33 0:80: 0 321 1 14.99 0.52594 2913-12-13 00:00:00 14-01/L3 1 9.00-in. pwr. saw blade 2014-01-15 00:00:00 2232/QTY I B8D jigsau, 12-in. blade 2238/OPD I B3D cordless drill, 1/2-in. 54778-2T | Rat-tail file, 1/8-in. fine PVC230RT | PVC pipe, 3.5-in., 8-ft SM-23116 1 2.5-in. wd. screw, 50 l 2232/QWE | B80 11gsau, 8-1n. blade 2013-12-24 80:80:00 2013-12-15 00:80:00 2014-82-87 80:80:88 I 89-HRE-Q I Hicut chain saw, 16 in. I WR3/TT3 I Steel matting, 4'x8 x1/6*, .5" nesh | 2814-01-17 60:00:00 | 18Explanation / Answer
a) By using MySQL queries, JOIN the following tables to return UNIFIED_TABLE
SELECT C.CUS_CODE, C.CUS_FNAME, C.CUS_LNAME, L.LINE_PRICE AS "UNIT PRICE", L.LINE_UNITS AS "QUANTITY", P.P_DESCRIPT AS "DESCRIPTION"
FROM
CUSTOMER C JOIN INVOICE ON (C.CUS_CODE = INVOICE.CUS_CODE)
JOIN LINE L ON (L.INV_NUMBER = INVOICE.INV_NUMBER)
JOIN PRODUCT P ON (P.P_CODE = L.P_CODE);
b) Create a view named PURCHASE_INFO that has the same content as the return from UNIFIED_TABLE
CREATE VIEW PURCHASE_INFO
AS
SELECT C.CUS_CODE, C.CUS_FNAME, C.CUS_LNAME, L.LINE_PRICE AS "UNIT PRICE", L.LINE_UNITS AS "QUANTITY", P.P_DESCRIPT AS "DESCRIPTION"
FROM
CUSTOMER C JOIN INVOICE ON (C.CUS_CODE = INVOICE.CUS_CODE)
JOIN LINE L ON (L.INV_NUMBER = INVOICE.INV_NUMBER)
JOIN PRODUCT P ON (P.P_CODE = L.P_CODE);
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.