Using the data below, write SQL statements that will accurately return data for
ID: 3780025 • Letter: U
Question
Using the data below, write SQL statements that will accurately return data for the following:
List all product sales for which the units sold is greater than the average units sold -- for that product.
To the previous query, add a correlated in-line sub-query to list the average units sold per product.
List all customers who purchased products 13-Q2/P2 and 23109-HB.
List all products what a product cost greater than all individual product costs of products provided by vendors in Florida. Create this query as a Stored Procedure.
List the difference between each product’s price and the average product price.
List all vendors to contact for products with a quantity on hand <= double P_MIN. Create this query as a Stored Procedure.
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)
);
ALTER TABLE customer
CHANGE COLUMN CUS_CODE CUS_CODE INT(11) NOT NULL,
ADD PRIMARY KEY (CUS_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');
/* -- */
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 vendor
CHANGE COLUMN V_CODE V_CODE INT(11) NOT NULL,
ADD PRIMARY KEY (V_CODE);
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');
/* -- */
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
);
ALTER TABLE product
CHANGE COLUMN P_CODE P_CODE VARCHAR(10) NOT NULL,
ADD PRIMARY KEY (P_CODE),
ADD INDEX V_CODE_idx (V_CODE ASC);
ALTER TABLE product
ADD CONSTRAINT V_CODE
FOREIGN KEY (V_CODE)
REFERENCES vendor (V_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
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','21344');
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','24004');
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, 25','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');
/* -- */
CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
ALTER TABLE invoice
CHANGE COLUMN INV_NUMBER INV_NUMBER INT(11) NOT NULL,
ADD PRIMARY KEY (INV_NUMBER), ADD INDEX CUS_CODE_idx (CUS_CODE ASC);
ALTER TABLE invoice
ADD CONSTRAINT CUS_CODE
FOREIGN KEY (CUS_CODE)
REFERENCES customer (CUS_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
INSERT INTO INVOICE VALUES('1001','10014','2016-01-16','24.90','1.99','26.89');
INSERT INTO INVOICE VALUES('1002','10011','2016-01-16','9.98','0.80','10.78');
INSERT INTO INVOICE VALUES('1003','10012','2016-01-16','153.85','12.31','166.16');
INSERT INTO INVOICE VALUES('1004','10011','2016-01-17','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1005','10018','2016-01-17','70.44','5.64','76.08');
INSERT INTO INVOICE VALUES('1006','10014','2016-01-17','397.83','31.83','429.66');
INSERT INTO INVOICE VALUES('1007','10015','2016-01-17','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1008','10011','2016-01-17','399.15','31.93','431.08');
/* -- */
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)
);
ALTER TABLE line
CHANGE COLUMN INV_NUMBER INV_NUMBER INT(11) NOT NULL,
CHANGE COLUMN LINE_NUMBER LINE_NUMBER INT(11) NOT NULL,
ADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER),
ADD INDEX P_CODE_idx (P_CODE ASC);
ALTER TABLE line
ADD CONSTRAINT P_CODE
FOREIGN KEY (P_CODE)
REFERENCES product (P_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
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');
Explanation / Answer
/* 1 List all product sales for which the units sold is
greater than the average units sold -- for that product. */
SELECT INV_NUMBER, P_CODE, LINE_UNITS
FROM LINE A
WHERE A.LINE_UNITS > (SELECT AVG(LINE_UNITS)
FROM LINE B
WHERE B.P_CODE = A.P_CODE);
/* 2To the previous query, add a correlated in-line sub-query to list the
average units sold per product. */
SELECT INV_NUMBER, P_CODE, LINE_UNITS,
(SELECT round(AVG(LINE_UNITS),2) FROM LINE C WHERE C.P_CODE = A.P_CODE) AS AVG_UNITS
FROM LINE A WHERE A.LINE_UNITS > (SELECT AVG(LINE_UNITS)
FROM LINE B
WHERE B.P_CODE = A.P_CODE);
/* 3List all customers who purchased products 13-Q2/P2 and 23109-HB. */
SELECT DISTINCT A.CUS_CODE, A.CUS_FNAME
FROM CUSTOMER AS A
INNER JOIN INVOICE AS B ON A.CUS_CODE =B.CUS_CODE
INNER JOIN LINE AS C ON B.INV_NUMBER =C.INV_NUMBER
WHERE C.P_CODE IN ('13-Q2/P2','23109-HB');
/* 5 List the difference between each products price and the average product price. */
SELECT P_CODE, P_PRICE, ROUND(AVG_PRICE,2), ROUND((P_PRICE- AVG_PRICE),2) DIFF
FROM PRODUCT AS A
JOIN (SELECT AVG(P_PRICE) AS AVG_PRICE FROM PRODUCT) AS B
ON A.P_CODE IS NOT NULL;
/* 4 List all products what a product cost greater than all individual product costs of products
provided by vendors in Florida. Create this query as a Stored Procedure. */
DELIMITER //
CREATE PROCEDURE ProdListPriceGTotherProds
(IN STATECODE CHAR(2))
BEGIN
SELECT P_CODE, P_QOH, P_PRICE
FROM PRODUCT
WHERE P_PRICE > (SELECT MAX(A.P_PRICE)
FROM PRODUCT AS A JOIN VENDOR AS B
ON A.V_CODE = B.V_CODE AND B.V_STATE=STATECODE);
END //
DELIMITER ;
/* TO CALL THE PROCEDURE GIVE THE STATE CODE AS INPUT */
CALL ProdListPriceGTotherProds('FL');
/* 6 List all vendors to contact for products with a quantity on hand <= double P_MIN.
Create this query as a Stored Procedure. */
DELIMITER //
CREATE PROCEDURE VendorsInfo()
BEGIN
SELECT * FROM VENDOR
WHERE EXISTS (SELECT * FROM PRODUCT
WHERE P_QOH<=P_MIN*2
AND VENDOR.V_CODE = PRODUCT.V_CODE);
END //
DELIMITER ;
/* TO CALL THE PROCEDURE */
CALL VendorsInfo();
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.