Using the database below write SQL statements that will accu

Using the database below, write SQL statements that will accurately return data for the following:

List the Customer Codes in Area Code 615 who have been invoiced.

List all Vendors that provide Products.

List all Customers who ordered the Product “Claw Hammer”.

List all Customers that purchased any type of Hammer or any kind of Saw or Saw Blade.

List all Products with a total quantity sold greater than the average quantity sold.

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

So, here are the answers of the queries. I have used Nested Queries for all the answer. last part I found little ambiguous.

1)SELECT CUS_FNAME AS FNAME, CUS_LNAME AS LNAME FROM CUSTOMER WHERE CUS_AREACODE=\'615\' AND CUS_CODE IN (SELECT CUS_CODE FROM INVOICE)

2)SELECT V_NAME FROM VENDOR WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT)

3)SELECT CUS_FNAME AS FNAME, CUS_LNAME AS LNAME FROM CUSTOMER WHERE CUS_CODE IN (SELECT CUS_CODE FROM INVOICE WHERE INV_NUMBER IN (SELECT INV_NUMBER FROM LINE WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT WHERE PRODUCT=\'Claw Hammer\')))

4)SELECT CUS_FNAME AS FNAME, CUS_LNAME AS LNAME FROM CUSTOMER WHERE CUS_CODE IN (SELECT CUS_CODE FROM INVOICE WHERE INV_NUMBER IN (SELECT INV_NUMBER FROM LINE WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT WHERE PRODUCT LIKE \'%SAW%\' OR PRODUCT LIKE \'%HAMMER%\')))

5)I think the last part is little ambiguous.