2. (2 points) Write a query to display the number of rows in the line table.

3. (3 points) Write a query to show everything in the product table. Add a field called Assets to calculate quantity * price, order by this field from highest to lowest.

4. (2 points) Write a query to determine which product has the highest number of units sold.

5. (3 points) Using the product table, write a query to show the top 4 products that are closest to their reorder point.

6. (3 points) Using the product and vendor tables, write a query that shows the vendor along with information about the product they sell.

7. (2 point) Using the query from #6, change your query to find any products without a vendor.

8. (3 points) Using the customer and invoice tables, write a query to show the customer id, first name, last name, and number of purchases the customer made. Rename the last field Orders.

9. (5 points) Using the query from #8, add the line table and add the following fields to your query: number of invoice lines (renamed to OrderItems), total units sold (renamed to UnitsSold). Hint Orders and OrderLines should be different numbers for some customers!

Bonus: 5 extra credit points if you display the correct value for units * price (renamed to SaleAmount). Double-check your work by querying the data to confirm your results.

/* Database Systems, 9th Ed., Coronel/Morris/Rob */ /* Type of SQL : MySQL */

/* This script creates and loads data in order to create the Sale Company database*/ DROP SCHEMA IF EXISTS SALES_CO; CREATE SCHEMA SALES_CO;

USE SALES_CO;

/*Create table Customer*/ CREATE TABLE customer (id INTEGER PRIMARY KEY, last_name VARCHAR(15) NOT NULL, first_name VARCHAR(15) NOT NULL, initial CHAR(1), areacode CHAR(3) DEFAULT '615' NOT NULL, phone CHAR(8) NOT NULL, balance DECIMAL(9,2) DEFAULT 0.00);

/*Create table Vendor*/ CREATE TABLE vendor (id INTEGER PRIMARY KEY, name VARCHAR(35) NOT NULL, contact VARCHAR(15) NOT NULL, areacode CHAR(3) NOT NULL, phone CHAR(8) NOT NULL, state CHAR(2) NOT NULL, discount_flag BOOLEAN NOT NULL);

/*Create table Product*/ CREATE TABLE product (code VARCHAR(10) PRIMARY KEY, description VARCHAR(35) NOT NULL, date_changed DATE NOT NULL, quantity INTEGER NOT NULL, minimum_quantity INTEGER NOT NULL, price DECIMAL(8,2) NOT NULL, discount DECIMAL(5,2) NOT NULL, vendor_id INTEGER, CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (vendor_id) REFERENCES vendor(id));

/*Create table Invoice*/ CREATE TABLE invoice (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, date_shipped DATE NOT NULL, CONSTRAINT INVOICE_CUSTOMER_FK FOREIGN KEY (customer_id) REFERENCES customer (id)); /*Create table Line*/ CREATE TABLE line (invoice_id INTEGER NOT NULL, line_number INTEGER NOT NULL, product_code VARCHAR(10) NOT NULL, units INTEGER DEFAULT 0 NOT NULL, price DECIMAL(9,2) DEFAULT 0.00 NOT NULL, PRIMARY KEY (invoice_id, line_number), FOREIGN KEY (invoice_id) REFERENCES invoice(id) ON DELETE CASCADE, FOREIGN KEY (product_code) REFERENCES product(code));

/*Insert data into Customer*/ 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' ,NULL,'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',NULL,'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 data into vendor*/ INSERT INTO vendor VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN',1); INSERT INTO vendor VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL',0); INSERT INTO vendor VALUES(21231,'D\&E Supply' ,'Singh' ,'615','228-3245','TN',1); INSERT INTO vendor VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY',0); INSERT INTO vendor VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA',0); INSERT INTO vendor VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA',1); INSERT INTO vendor VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN',0); INSERT INTO vendor VALUES(24288,'ORDVA, Inc.' ,'Hakford' ,'615','898-1234','TN',1); INSERT INTO vendor VALUES(25443,'B\&K, Inc.' ,'Smith' ,'904','227-0093','FL',0); INSERT INTO vendor VALUES(25501,'Damal Supplies' ,'Smythe' ,'615','890-3529','TN',0); INSERT INTO vendor VALUES(25595,'Rubicon Systems' ,'Orton' ,'904','456-0092','FL',1);

/*Insert data into Product*/ INSERT INTO product VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'2009-11-03', 8, 5,109.99,0.00,25595); INSERT INTO product VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'2009-12-13', 32, 15, 14.99,0.05,21344); INSERT INTO product VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'2009-11-13', 18, 12, 17.49,0.00,21344); INSERT INTO product VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'2010-01-15', 15, 8, 39.95,0.00,23119); INSERT INTO product VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'2010-01-15', 23, 5, 43.99,0.00,23119); INSERT INTO product VALUES('2232/QTY','B\&D jigsaw, 12-in. blade' ,'2009-12-30', 8, 5,109.92,0.05,24288); INSERT INTO product VALUES('2232/QWE','B\&D jigsaw, 8-in. blade' ,'2009-12-24', 6, 5, 99.87,0.05,24288); INSERT INTO product VALUES('2238/QPD','B\&D cordless drill, 1/2-in.' ,'2010-01-20', 12, 5, 38.95,0.05,25595); INSERT INTO product VALUES('23109-HB','Claw hammer' ,'2010-01-20', 23, 10, 9.95,0.10,21225); INSERT INTO product VALUES('23114-AA','Sledge hammer, 12 lb.' ,'2010-01-02', 8, 5, 14.40,0.05,NULL); INSERT INTO product VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'2009-12-15', 43, 20, 4.99,0.00,21344); INSERT INTO product VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'2010-02-07', 11, 5,256.99,0.05,24288); INSERT INTO product VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'2010-02-20',188, 75, 5.87,0.00,NULL); INSERT INTO product VALUES('SM-18277','1.25-in. metal screw, 25' ,'2010-03-01',172, 75, 6.99,0.00,21225); INSERT INTO product VALUES('SW-23116','2.5-in. wd. screw, 50' ,'2010-02-24',237,100, 8.45,0.00,21231); INSERT INTO product VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','2010-01-17', 18, 5,119.95,0.10,25595);

/*Insert data into Invoice*/ INSERT INTO invoice VALUES(1001,10014,'2010-01-16'); INSERT INTO invoice VALUES(1002,10011,'2010-01-16'); INSERT INTO invoice VALUES(1003,10012,'2010-01-16'); INSERT INTO invoice VALUES(1004,10011,'2010-01-17'); INSERT INTO invoice VALUES(1005,10018,'2010-01-17'); INSERT INTO invoice VALUES(1006,10014,'2010-01-17'); INSERT INTO invoice VALUES(1007,10015,'2010-01-17'); INSERT INTO invoice VALUES(1008,10011,'2010-01-17');

/*Insert data into Line*/ 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);