Task

NOTES

This assignment requires you to use SQLite to complete the tasks listed below.Student can also use SQL Workbench or another software (if they are familiar) to complete the tasks.

For guidance on how to install SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=9ynCDVg54H0

For guidance on how to create tables using SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=kij5lPw0F-o

Typing the SQL statements or the results is NOT acceptable.

Instructions for using SQLite:

From Resources under Main Menu in Interact 2 go to Assignment 5 Folder

Copy sqlite3.exe and cmd.exe files from the Assignment 5 Folder into a folder in your machine\

Copy a5.txt from the Assignment 5 Folder into the folder in your machine where your SQLite file is located

From the SQLite Command Prompt execute the a5.txt script. To do this, at the SQLite Command Line type .read a5.txt [to create the tables you will use to answer this assignments questions]

At the SQLite Command Line type .headers on

At the SQLite Command Line type .mode column

Assignment 5 tasks: each query is worth 5 marks Write and run SQL statements to complete the following tasks Part A - DML

1. Show the details of the products where the product code starts with '22'. 2. Display the vendor details from areacode 615. 3. Find the balance for customer 'George Williams'. 4. Show the number of products from each vendor and display the vendor code if there are more than 2 products from the same vendor. 5. Display the vendor code, name, phone number and the number of products from each vendor. 6. Find the customers whose balance is zero. 7. List the name of the customers for whom we have invoices. 8. Show the name and cus_code of the customers for whom we DO NOT have invoices. 9. Find out how many times a customer generated an invoice.

Part B - DDL

1. Add a new attribute email_address varchar(30) to the customer table. 2. Add email_address = 'jbrown@anymail.com' for customer '10016'. 3. Delete the record for employee Edward Johnson from EMP table.

You are required to submit:

The SQL statements for each query, which should be copied from your SQLite3 Command Line and pasted into your submission file. If you type .echo on at the SQLite3 Command Line, SQLite will output your SQL statement with the results of the query making it easy to copy and paste both.

The screenshots of the results of the SQL statements which are to be copied from your SQLite3 Command Line and pasted into your submission file immediately after you execute the SQL query. If you used an output file please submit only the output file

Here is a5.txt

-- Creating Tables .headers on .mode column .separator , DROP TABLE IF EXISTS vendor; 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) ); 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',null ,'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'); DROP TABLE IF EXISTS product; 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','11/3/2007','8','5','109.98999786377','0','25595'); INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','12/13/2007','32','15','14.9899997711182','0.05','21344'); INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','11/13/2007','18','12','17.4899997711182','0','21344'); INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','1/15/2008','15','8','39.9500007629395','0','23119'); INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','1/15/2008','23','5','43.9900016784668','0','23119'); INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','12/30/2007','8','5','109.919998168945','0.05','24288'); INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','12/24/2007','6','5','99.870002746582','0.05','24288'); INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','1/20/2008','12','5','38.9500007629395','0.05','25595'); INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','1/20/2008','23','10','9.94999980926514','0.1','21225'); INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','1/2/2008','8','5','14.3999996185303','0.05',''); INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','12/15/2007','43','20','4.98999977111816','0','21344'); INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2/7/2008','11','5','256.989990234375','0.05','24288'); INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2/20/2008','188','75','5.86999988555908','0',''); INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25','3/1/2008','172','75','6.98999977111816','0','21225'); INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2/24/2008','237','100','8.44999980926514','0','21231'); INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','1/17/2008','18','5','119.949996948242','0.1','25595'); DROP TABLE IF EXISTS CUSTOMER; 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.859985351562'); 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.190002441406'); INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.929992675781'); INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.550003051758'); INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0'); DROP TABLE IF EXISTS invoice; CREATE TABLE INVOICE ( INV_NUMBER int, CUS_CODE int, INV_DATE datetime, INV_SUBTOTAL float(8), INV_TAX float(8), INV_TOTAL float(8) ); INSERT INTO INVOICE VALUES('1001','10014','1/16/2008','24.8999996185303','1.99000000953674','26.8899993896484'); INSERT INTO INVOICE VALUES('1002','10011','1/16/2008','9.97999954223633','0.800000011920929','10.7799997329712'); INSERT INTO INVOICE VALUES('1003','10012','1/16/2008','153.850006103516','12.3100004196167','166.160003662109'); INSERT INTO INVOICE VALUES('1004','10011','1/17/2008','34.9700012207031','2.79999995231628','37.7700004577637'); INSERT INTO INVOICE VALUES('1005','10018','1/17/2008','70.4400024414062','5.6399998664856','76.0800018310547'); INSERT INTO INVOICE VALUES('1006','10014','1/17/2008','397.829986572266','31.8299999237061','429.660003662109'); INSERT INTO INVOICE VALUES('1007','10015','1/17/2008','34.9700012207031','2.79999995231628','37.7700004577637'); INSERT INTO INVOICE VALUES('1008','10011','1/17/2008','399.149993896484','31.9300003051758','431.079986572266'); DROP TABLE IF EXISTS line; 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) ); INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.9899997711182','14.9899997711182'); INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.94999980926514','9.94999980926514'); INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.98999977111816','9.97999954223633'); INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.9500007629395','38.9500007629395'); INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.9500007629395','39.9500007629395'); INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.9899997711182','74.9499969482422'); INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.98999977111816','14.9700002670288'); INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.94999980926514','19.8999996185303'); INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.86999988555908','70.4400024414062'); INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.98999977111816','20.9699993133545'); INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.919998168945','109.919998168945'); INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.94999980926514','9.94999980926514'); INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.989990234375','256.989990234375'); INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.9899997711182','29.9799995422363'); INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.98999977111816','4.98999977111816'); INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.86999988555908','29.3500003814697'); INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.949996948242','359.850006103516'); INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.94999980926514','9.94999980926514'); DROP TABLE IF EXISTS EMP; CREATE TABLE EMP ( EMP_NUM int, EMP_TITLE varchar(4), EMP_LNAME varchar(15), EMP_FNAME varchar(15), EMP_INITIAL varchar(1), EMP_DOB datetime, EMP_HIRE_DATE datetime, EMP_AREACODE varchar(3), EMP_PHONE varchar(8), EMP_MGR int ); INSERT INTO EMP VALUES('100','Mr.','Kolmycz','George','D','6/15/1942','3/15/1985','615','324-5456',''); INSERT INTO EMP VALUES('101','Ms.','Lewis','Rhonda','G','3/19/1965','4/25/1986','615','324-4472','100'); INSERT INTO EMP VALUES('102','Mr.','VanDam','Rhett','','11/14/1958','12/20/1990','901','675-8993','100'); INSERT INTO EMP VALUES('103','Ms.','Jones','Anne','M','10/16/1974','8/28/1994','615','898-3456','100'); INSERT INTO EMP VALUES('104','Mr.','Lange','John','P','11/8/1971','10/20/1994','901','504-4430','105'); INSERT INTO EMP VALUES('105','Mr.','Williams','Robert','D','3/14/1975','11/8/1998','615','890-3220',''); INSERT INTO EMP VALUES('106','Mrs.','Smith','Jeanine','K','2/12/1968','1/5/1989','615','324-7883','105'); INSERT INTO EMP VALUES('107','Mr.','Diante','Jorge','D','8/21/1974','7/2/1994','615','890-4567','105'); INSERT INTO EMP VALUES('108','Mr.','Wiesenbach','Paul','R','2/14/1966','11/18/1992','615','897-4358',''); INSERT INTO EMP VALUES('109','Mr.','Smith','George','K','6/18/1961','4/14/1989','901','504-3339','108'); INSERT INTO EMP VALUES('110','Mrs.','Genkazi','Leighla','W','5/19/1970','12/1/1990','901','569-0093','108'); INSERT INTO EMP VALUES('111','Mr.','Washington','Rupert','E','1/3/1966','6/21/1993','615','890-4925','105'); INSERT INTO EMP VALUES('112','Mr.','Johnson','Edward','E','5/14/1961','12/1/1983','615','898-4387','100'); INSERT INTO EMP VALUES('113','Ms.','Smythe','Melanie','P','9/15/1970','5/11/1999','615','324-9006','105'); INSERT INTO EMP VALUES('114','Ms.','Brandon','Marie','G','11/2/1956','11/15/1979','901','882-0845','108'); INSERT INTO EMP VALUES('115','Mrs.','Saranda','Hermine','R','7/25/1972','4/23/1993','615','324-5505','105'); INSERT INTO EMP VALUES('116','Mr.','Smith','George','A','11/8/1965','12/10/1988','615','890-2984','108'); .output yourstudentid.txt select * from VENDOR; select * from Product; select * from customer; select * from invoice; select * from line; select * from EMP;