/* Database Systems, Coronel/Morris */ /* Type of SQL : MySQL */ Drop schema if exists sales; Create schema Sales; Use sales; DROP TABLE IF EXISTS vendor; CREATE TABLE VENDOR ( `V_CODE` int not null, `V_NAME` varchar(15), `V_CONTACT` varchar(50), `V_AREACODE` varchar(3), `V_PHONE` varchar(8), `V_STATE` varchar(2), `V_ORDER` varchar(1), 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'); DROP TABLE IF EXISTS product; CREATE TABLE PRODUCT ( `P_CODE` varchar(10) not null, `P_DESCRIPT` varchar(35), `P_INDATE` datetime, `P_QOH` int, `P_MIN` int, `P_PRICE` float(8), `P_DISCOUNT` float(8), `V_CODE` int, Primary key(`P_Code`) ); INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','2013-11-3','8','5','109.99','0','25595'); INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2013-12-13','32','15', '14.99','0.05','21344'); INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2013-11-13','18','12','17.49','0','21344'); INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2014-1-15','15','8','39.95','0','23119'); INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2014-1-15','23','5','43.99','0','23119'); INSERT INTO PRODUCT VALUES('2232/QTY','B\&D jigsaw, 12-in. blade','2013-12-30','8','5','109.92','0.05','24288'); INSERT INTO PRODUCT VALUES('2232/QWE','B\&D jigsaw, 8-in. blade','2013-12-24','6','5','99.87','0.05','24288'); INSERT INTO PRODUCT VALUES('2238/QPD','B\&D cordless drill, 1/2-in.','2014-1-20','12','5','38.95','0.05','25595'); INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2014-1-20','23','10','9.95','0.1','21225'); INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2014-1-2','8','5','14.40','0.05', NULL); INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2013-12-15','43','20','4.99','0','21344'); INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2014-2-7','11','5','256.99','0.05','24288'); INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2014-2-20','188','75','5.87','0', NULL); INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25','2014-3-1','172','75','6.99','0','21225'); INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2014-2-24','237','100','8.45','0','21231'); INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','2014-1-17','18','5','119.95','0.1','25595'); DROP TABLE IF EXISTS CUSTOMER; CREATE TABLE CUSTOMER ( `CUS_CODE` int not null, `CUS_LNAME` varchar(15), `CUS_FNAME` varchar(15), `CUS_INITIAL` varchar(1), `CUS_AREACODE` varchar(3), `CUS_PHONE` varchar(8), `CUS_BALANCE` float(8), 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.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 not null, `CUS_CODE` int, `INV_DATE` datetime, `INV_SUBTOTAL` float(8), `INV_TAX` float(8), `INV_TOTAL` float(8), Primary Key(`inv_number`) ); INSERT INTO INVOICE VALUES('1001','10014','2008-1-16','24.8999996185303','1.99000000953674','26.8899993896484'); INSERT INTO INVOICE VALUES('1002','10011','2008-1-16','9.97999954223633','0.800000011920929','10.7799997329712'); INSERT INTO INVOICE VALUES('1003','10012','2008-1-16','153.850006103516','12.3100004196167','166.160003662109'); INSERT INTO INVOICE VALUES('1004','10011','2008-1-16','34.9700012207031','2.79999995231628','37.7700004577637'); INSERT INTO INVOICE VALUES('1005','10018','2008-1-17','70.4400024414062','5.6399998664856','76.0800018310547'); INSERT INTO INVOICE VALUES('1006','10014','2008-1-17','397.829986572266','31.8299999237061','429.660003662109'); INSERT INTO INVOICE VALUES('1007','10015','2008-1-17','34.9700012207031','2.79999995231628','37.7700004577637'); INSERT INTO INVOICE VALUES('1008','10011','2008-1-17','399.149993896484','31.9300003051758','431.079986572266'); DROP TABLE IF EXISTS line; CREATE TABLE LINE ( `INV_NUMBER` int, `LINE_NUMBER` int not null, `P_CODE` varchar(10), `LINE_UNITS` float(8), `LINE_PRICE` float(8), `LINE_TOTAL` float(8), Primary Key(`inv_number`,`line_number`) ); 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'); 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','1945-6-15','1985-3-15','615','324-5456',NULL); INSERT INTO EMP VALUES('101','Ms.','Lewis','Rhonda','G','1965-3-19','1986-4-25','615','324-4472','100'); INSERT INTO EMP VALUES('102','Mr.','VanDam','Rhett','','1958-11-14','1990-12-20','901','675-8993','100'); INSERT INTO EMP VALUES('103','Ms.','Jones','Anne','M','1974-10-16','1994-8-28','615','898-3456','100'); INSERT INTO EMP VALUES('104','Mr.','Lange','John','P','1971-11-8','1994-10-20','901','504-4430','105'); INSERT INTO EMP VALUES('105','Mr.','Williams','Robert','D','1975-3-14','1998-11-8','615','890-3220', NULL); INSERT INTO EMP VALUES('106','Mrs.','Smith','Jeanine','K','1968-2-12','1989-1-5','615','324-7883','105'); INSERT INTO EMP VALUES('107','Mr.','Diante','Jorge','D','1974-8-21','1994-7-2','615','890-4567','105'); INSERT INTO EMP VALUES('108','Mr.','Wiesenbach','Paul','R','1966-2-14','1992-11-18','615','897-4358',NULL); INSERT INTO EMP VALUES('109','Mr.','Smith','George','K','1961-6-18','1989-4-14','901','504-3339','108'); INSERT INTO EMP VALUES('110','Mrs.','Genkazi','Leighla','W','1970-5-19','1990-12-1','901','569-0093','108'); INSERT INTO EMP VALUES('111','Mr.','Washington','Rupert','E','1966-1-3','1993-6-21','615','890-4925','105'); INSERT INTO EMP VALUES('112','Mr.','Johnson','Edward','E','1961-5-14','1983-12-1','615','898-4387','100'); INSERT INTO EMP VALUES('113','Ms.','Smythe','Melanie','P','1970-9-15','1999-5-11','615','324-9006','105'); INSERT INTO EMP VALUES('114','Ms.','Brandon','Marie','G','1956-11-2','1979-11-15','901','882-0845','108'); INSERT INTO EMP VALUES('115','Mrs.','Saranda','Hermine','R','1972-7-25','1993-4-23','615','324-5505','105'); INSERT INTO EMP VALUES('116','Mr.','Smith','George','A','1965-11-8','1988-12-10','615','890-2984','108'); Task Write SQL statements to answer the following questions from the assignment5 Database: Answer all questions List all the record/s in the vendor table that do not have a value for the attribute V_STATE. Find the customers whose balance is greater than 250 but less than 400. Create a query based on invoice table to show a 15% increase on tax in a new column. Show the first and last names of the customers for whom we have issued invoices. Show the CUS_CODE of the customers for whom we DO NOT have invoices. Show the names of the customers for whom we DO NOT have invoices Find out how many times a customer generated an invoice make sure the counted column heading reads Invoices_generated . List the names of the customers and how many times a customer generated an invoice make sure the counted column heading reads N_Of_Invoices . Find the customers whose last name is either Dunne, Orlando or Brown and live in area 615, sort in alphabetical order by the CUS_LNAME,. Add a new attribute called email_address to emp table and use data type and length VARCHAR-20. Add a new row/record to the `customer` table to include your actual student ID in the CUS_CODE, your lastname as the CUS_LNAME` and your firstname as CUS_FNAME. Add any other details in the other fields. Delete all the vendors in the FL state. Rationale This assignment will assess your knowledge and ability to use SQL to create database tables. It will also test your ability to write both simple and complex queries given a set of results that are required and to the run those queries to return the requested data.