Using the data below write SQL statements that will accurate

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\');

Solution

/* 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();