Use the code to make these 4 queries with SQL /* ADVSQLDBINIT.SQL */ /* Advanced SQL */ /* Script file for MS SQL Server DBMS */ /* This script file creates the following tables: */ /* V - default vendor table data */ /* P - default product table data */ /* VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE */ /* EMPLOYEE, EMP */ /* and loads the default data rows */ CREATE DATABASE ABCTools; USE ABCTools; BEGIN TRANSACTION; DROP TABLE LINE; DROP TABLE INVOICE; DROP TABLE CUSTOMER; DROP TABLE CUSTOMER_2; DROP TABLE PRODUCT; DROP TABLE VENDOR; DROP TABLE EMPLOYEE; DROP TABLE EMP; DROP TABLE P; DROP TABLE V; CREATE TABLE V ( V_CODE INTEGER PRIMARY KEY, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL); CREATE TABLE P ( P_CODE VARCHAR(10) PRIMARY KEY, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATETIME NOT NULL, P_QOH INTEGER NOT NULL, P_MIN INTEGER NOT NULL, P_PRICE NUMERIC(8,2) NOT NULL, P_DISCOUNT NUMERIC(4,2) NOT NULL, V_CODE INTEGER, P_MIN_ORDER INTEGER, P_REORDER INTEGER); CREATE TABLE VENDOR ( V_CODE INTEGER, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL, PRIMARY KEY (V_CODE)); CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATETIME NOT NULL, P_QOH INTEGER NOT NULL, P_MIN INTEGER NOT NULL, P_PRICE NUMERIC(8,2) NOT NULL, P_DISCOUNT NUMERIC(4,2) NOT NULL, V_CODE INTEGER, P_MIN_ORDER INTEGER, P_REORDER INTEGER, CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); CREATE TABLE CUSTOMER ( CUS_CODE INTEGER PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3) DEFAULT '615' NOT NULL CHECK(CUS_AREACODE IN ('615','713','931')), CUS_PHONE CHAR(8) NOT NULL, CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00, CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME)); CREATE TABLE CUSTOMER_2 ( CUS_CODE INTEGER PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3), CUS_PHONE CHAR(8)); CREATE TABLE INVOICE ( INV_NUMBER INTEGER PRIMARY KEY, CUS_CODE INTEGER NOT NULL REFERENCES CUSTOMER(CUS_CODE), INV_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CONSTRAINT INV_CK1 CHECK (INV_DATE > '01-JAN-2010')); CREATE TABLE LINE ( INV_NUMBER INTEGER NOT NULL, LINE_NUMBER NUMERIC(2,0) NOT NULL, P_CODE VARCHAR(10) NOT NULL, LINE_UNITS NUMERIC(9,2) DEFAULT 0.00 NOT NULL, LINE_PRICE NUMERIC(9,2) DEFAULT 0.00 NOT NULL, PRIMARY KEY (INV_NUMBER,LINE_NUMBER), FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE, FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE), CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE)); CREATE TABLE EMPLOYEE ( EMP_NUM INTEGER PRIMARY KEY, EMP_TITLE CHAR(10), EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_DOB DATETIME, EMP_HIRE_DATE DATETIME, EMP_YEARS INTEGER, EMP_AREACODE CHAR(3), EMP_PHONE CHAR(8)); CREATE TABLE EMP ( EMP_NUM INTEGER PRIMARY KEY, EMP_TITLE CHAR(10), EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_DOB DATETIME, EMP_HIRE_DATE DATETIME, EMP_AREACODE CHAR(3), EMP_PHONE CHAR(8), EMP_MGR INTEGER); /* V rows */ INSERT INTO V VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y'); INSERT INTO V VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL','N'); INSERT INTO V VALUES(21231,'D&E Supply' ,'Singh' ,'615','228-3245','TN','Y'); INSERT INTO V VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY','N'); INSERT INTO V VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA','N'); INSERT INTO V VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA','Y'); INSERT INTO V VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN','N'); INSERT INTO V VALUES(24288,'ORDVA, Inc.' ,'Hakford' ,'615','898-1234','TN','Y'); INSERT INTO V VALUES(25443,'B&K, Inc.' ,'Smith' ,'904','227-0093','FL','N'); INSERT INTO V VALUES(25501,'Damal Supplies' ,'Smythe' ,'615','890-3529','TN','N'); INSERT INTO V VALUES(25595,'Rubicon Systems' ,'Orton' ,'904','456-0092','FL','Y'); /* P rows */ INSERT INTO P VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'03-NOV-2009', 8, 5,109.99,0.00,25595, 25,0); INSERT INTO P VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'13-DEC-2009', 32, 15, 14.99,0.05,21344, 50,0); INSERT INTO P VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'13-NOV-2009', 18, 12, 17.49,0.00,21344, 50,0); INSERT INTO P VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'15-JAN-2010', 15, 8, 39.95,0.00,23119, 35,0); INSERT INTO P VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'15-JAN-2010', 23, 5, 43.99,0.00,23119, 25,0); INSERT INTO P VALUES('2232/QTY','B&D jigsaw, 12-in. blade' ,'30-DEC-2009', 8, 5,109.92,0.05,24288, 15,0); INSERT INTO P VALUES('2232/QWE','B&D jigsaw, 8-in. blade' ,'24-DEC-2009', 6, 5, 99.87,0.05,24288, 15,0); INSERT INTO P VALUES('2238/QPD','B&D cordless drill, 1/2-in.' ,'20-JAN-2010', 12, 5, 38.95,0.05,25595, 12,0); INSERT INTO P VALUES('23109-HB','Claw hammer' ,'20-JAN-2010', 23, 10, 9.95,0.10,21225, 25,0); INSERT INTO P VALUES('23114-AA','Sledge hammer, 12 lb.' ,'02-JAN-2010', 8, 5, 14.40,0.05,NULL , 12,0); INSERT INTO P VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'15-DEC-2009', 43, 20, 4.99,0.00,21344, 25,0); INSERT INTO P VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'07-FEB-2010', 11, 5,256.99,0.05,24288, 10,0); INSERT INTO P VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'20-FEB-2010',188, 75, 5.87,0.00,NULL , 50,0); INSERT INTO P VALUES('SM-18277','1.25-in. metal screw, 25' ,'01-MAR-2010',172, 75, 6.99,0.00,21225, 50,0); INSERT INTO P VALUES('SW-23116','2.5-in. wd. screw, 50' ,'24-FEB-2010',237,100, 8.45,0.00,21231,100,0); INSERT INTO P VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2010', 18, 5,119.95,0.10,25595, 10,0); /* VENDOR rows */ INSERT INTO VENDOR SELECT * FROM V; /* PRODUCT rows */ INSERT INTO PRODUCT SELECT * FROM P; /* CUSTOMER rows */ 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); /* CUSTOMER_2 rows */ INSERT INTO CUSTOMER_2 VALUES(345,'Terrell','Justine','H','615','322-9870'); INSERT INTO CUSTOMER_2 VALUES(347,'Olowski','Paul','F',615,'894-2180'); INSERT INTO CUSTOMER_2 VALUES(351,'Hernandez','Carlos','J','723','123-7654'); INSERT INTO CUSTOMER_2 VALUES(352,'McDowell','George',NULL,'723','123-7768'); INSERT INTO CUSTOMER_2 VALUES(365,'Tirpin','Khaleed','G','723','123-9876'); INSERT INTO CUSTOMER_2 VALUES(368,'Lewis','Marie','J','734','332-1789'); INSERT INTO CUSTOMER_2 VALUES(369,'Dunne','Leona','K','713','894-1238'); /* INVOICE rows */ INSERT INTO INVOICE VALUES(1001,10014,'16-JAN-2010'); INSERT INTO INVOICE VALUES(1002,10011,'16-JAN-2010'); INSERT INTO INVOICE VALUES(1003,10012,'16-JAN-2010'); INSERT INTO INVOICE VALUES(1004,10011,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1005,10018,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1006,10014,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1007,10015,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1008,10011,'17-JAN-2010'); /* LINE rows */ 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); /* EMP rows */ INSERT INTO EMP VALUES(100,'Mr.' ,'Kolmycz' ,'George' ,'D' ,'15-JUN-1942','15-MAR-1985','615','324-5456',NULL); INSERT INTO EMP VALUES(101,'Ms.' ,'Lewis' ,'Rhonda' ,'G' ,'19-MAR-1965','25-APR-1986','615','324-4472',100); INSERT INTO EMP VALUES(102,'Mr.' ,'Vandam' ,'Rhett' ,NULL,'14-NOV-1958','20-DEC-1990','901','675-8993',100); INSERT INTO EMP VALUES(103,'Ms.' ,'Jones' ,'Anne' ,'M' ,'16-OCT-1974','28-AUG-1994','615','898-3456',100); INSERT INTO EMP VALUES(104,'Mr.' ,'Lange' ,'John' ,'P' ,'08-NOV-1971','20-OCT-1994','901','504-4430',105); INSERT INTO EMP VALUES(105,'Mr.' ,'Williams' ,'Robert' ,'D' ,'14-MAR-1975','08-NOV-1998','615','890-3220',NULL); INSERT INTO EMP VALUES(106,'Mrs.','Smith' ,'Jeanine','K' ,'12-FEB-1968','05-JAN-1989','615','324-7883',105); INSERT INTO EMP VALUES(107,'Mr.' ,'Diante' ,'Jorge' ,'D' ,'21-AUG-1974','02-JUL-1994','615','890-4567',105); INSERT INTO EMP VALUES(108,'Mr.' ,'Wiesenbach','Paul' ,'R' ,'14-FEB-1966','18-NOV-1992','615','897-4358',NULL); INSERT INTO EMP VALUES(109,'Mr.' ,'Smith' ,'George' ,'K' ,'18-JUN-1961','14-APR-1989','901','504-3339',108); INSERT INTO EMP VALUES(110,'Mrs.','Genkazi' ,'Leighla','W' ,'19-MAY-1970','01-DEC-1990','901','569-0093',108); INSERT INTO EMP VALUES(111,'Mr.' ,'Washington','Rupert' ,'E' ,'03-JAN-1966','21-JUN-1993','615','890-4925',105); INSERT INTO EMP VALUES(112,'Mr.' ,'Johnson' ,'Edward' ,'E' ,'14-MAY-1961','01-DEC-1983','615','898-4387',100); INSERT INTO EMP VALUES(113,'Ms.' ,'Smythe' ,'Melanie','P' ,'15-SEP-1970','11-MAY-1999','615','324-9006',105); INSERT INTO EMP VALUES(114,'Ms.' ,'Brandon' ,'Marie' ,'G' ,'02-NOV-1956','15-NOV-1979','901','882-0845',108); INSERT INTO EMP VALUES(115,'Mrs.','Saranda' ,'Hermine','R' ,'25-JUL-1972','23-APR-1993','615','324-5505',105); INSERT INTO EMP VALUES(116,'Mr.' ,'Smith' ,'George' ,'A' ,'08-NOV-1965','10-DEC-1988','615','890-2984',108); /* EMPLOYEE rows */ INSERT INTO EMPLOYEE VALUES(100,'Mr.' ,'Kolmycz' ,'George' ,'D' ,'15-JUN-1942','15-MAR-1985',18,'615','324-5456'); INSERT INTO EMPLOYEE VALUES(101,'Ms.' ,'Lewis' ,'Rhonda' ,'G' ,'19-MAR-1965','25-APR-1986',16,'615','324-4472'); INSERT INTO EMPLOYEE VALUES(102,'Mr.' ,'Vandam' ,'Rhett' ,NULL,'14-NOV-1958','20-DEC-1990',12,'901','675-8993'); INSERT INTO EMPLOYEE VALUES(103,'Ms.' ,'Jones' ,'Anne' ,'M' ,'16-OCT-1974','28-AUG-1994', 8,'615','898-3456'); INSERT INTO EMPLOYEE VALUES(104,'Mr.' ,'Lange' ,'John' ,'P' ,'08-NOV-1971','20-OCT-1994', 8,'901','504-4430'); INSERT INTO EMPLOYEE VALUES(105,'Mr.' ,'Williams' ,'Robert' ,'D' ,'14-MAR-1975','08-NOV-1998', 4,'615','890-3220'); INSERT INTO EMPLOYEE VALUES(106,'Mrs.','Smith' ,'Jeanine','K' ,'12-FEB-1968','05-JAN-1989',14,'615','324-7883'); INSERT INTO EMPLOYEE VALUES(107,'Mr.' ,'Diante' ,'Jorge' ,'D' ,'21-AUG-1974','02-JUL-1994', 8,'615','890-4567'); INSERT INTO EMPLOYEE VALUES(108,'Mr.' ,'Wiesenbach','Paul' ,'R' ,'14-FEB-1966','18-NOV-1992',10,'615','897-4358'); INSERT INTO EMPLOYEE VALUES(109,'Mr.' ,'Smith' ,'George' ,'K' ,'18-JUN-1961','14-APR-1989',13,'901','504-3339'); INSERT INTO EMPLOYEE VALUES(110,'Mrs.','Genkazi' ,'Leighla','W' ,'19-MAY-1970','01-DEC-1990',12,'901','569-0093'); INSERT INTO EMPLOYEE VALUES(111,'Mr.' ,'Washington','Rupert' ,'E' ,'03-JAN-1966','21-JUN-1993', 9,'615','890-4925'); INSERT INTO EMPLOYEE VALUES(112,'Mr.' ,'Johnson' ,'Edward' ,'E' ,'14-MAY-1961','01-DEC-1983',19,'615','898-4387'); INSERT INTO EMPLOYEE VALUES(113,'Ms.' ,'Smythe' ,'Melanie','P' ,'15-SEP-1970','11-MAY-1999', 3,'615','324-9006'); INSERT INTO EMPLOYEE VALUES(114,'Ms.' ,'Brandon' ,'Marie' ,'G' ,'02-NOV-1956','15-NOV-1979',23,'901','882-0845'); INSERT INTO EMPLOYEE VALUES(115,'Mrs.','Saranda' ,'Hermine','R' ,'25-JUL-1972','23-APR-1993', 9,'615','324-5505'); INSERT INTO EMPLOYEE VALUES(116,'Mr.' ,'Smith' ,'George' ,'A' ,'08-NOV-1965','10-DEC-1988',14,'615','890-2984'); COMMIT; GRANT SELECT ON V TO PUBLIC; GRANT SELECT ON P TO PUBLIC; 

1. Provide the number of Vendors hailing from each state. Provide suitable alias for the vendor count. This query will display one row for each Vendor State in the descending order of Vendor count.

2. For each area code with total of customer balances exceeding 1000, list customer counts and the total of customer balances. This query will list one row for each area code.

3. For each product discount offered (P_DISCOUNT), list number of products and average prices (provide suitable headings as aliases) for products whose quantity on hand is greater than 10 units. Your query will display one line for each product discount value.

4. For each state, display the number of vendors residing in the state (give suitable heading as alias) whose V_Order is Y. Your query should display one line for each vendor state sorted in the descending order of number of vendors.