For each area code with total of customer balances below 500, list customer counts and the total of customer balances. This query will list one row for each area code.
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.
SQL Script:
/* HomeToolsInc.sql - Script file for MS SQL Server DBMS */
/* This script file creates the following tables: */
/* REGION, STORE, CATEGORY, VENDOR, PRODUCT, CUSTOMER, CUSTOMER_2, INVOICE, LINE, EMP */
/* and loads some data into each table */
USE MASTER;
CREATE DATABASE HomeToolsInc;
GO
USE HomeToolsInc;
DROP TABLE IF EXISTS LINE;
DROP TABLE IF EXISTS INVOICE;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS CUSTOMER_2;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS PRODUCT;
DROP TABLE IF EXISTS CATEGORY;
DROP TABLE IF EXISTS VENDOR;
DROP TABLE IF EXISTS STORE;
DROP TABLE IF EXISTS REGION;
CREATE TABLE REGION(
REGION_ID INTEGER PRIMARY KEY,
REGION_NAME VARCHAR(15) NOT NULL);
CREATE TABLE STORE(
STORE_ID INTEGER PRIMARY KEY,
STORE_ZIPCODE INTEGER NOT NULL,
REGION_ID INTEGER NOT NULL REFERENCES REGION(REGION_ID));
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 CATEGORY (
CAT_ID INTEGER PRIMARY KEY,
CAT_NAME VARCHAR(20) NOT NULL);
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,
CAT_ID INTEGER NOT NULL,
CONSTRAINT PRODUCT_V_CODE_FK
FOREIGN KEY (V_CODE) REFERENCES VENDOR,
CONSTRAINT PRODUCT_CAT_ID_FK FOREIGN KEY (CAT_ID) REFERENCES CATEGORY);
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_NAME_UN 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) NOT NULL,
CUS_PHONE CHAR(8) NOT NULL);
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 NOT NULL,
EMP_HIRE_DATE DATETIME DEFAULT GETDATE(),
EMP_AREACODE CHAR(3) NOT NULL,
EMP_PHONE CHAR(8) NOT NULL,
EMP_MGR INTEGER,
STORE_ID INTEGER FOREIGN KEY REFERENCES STORE(STORE_ID),
FOREIGN KEY (EMP_MGR) REFERENCES EMPLOYEE(EMP_NUM));
CREATE TABLE INVOICE (
INV_NUMBER INTEGER PRIMARY KEY,
CUS_CODE INTEGER NOT NULL REFERENCES CUSTOMER(CUS_CODE),
EMP_NUM INTEGER NOT NULL REFERENCES EMPLOYEE(EMP_NUM),
INV_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
STORE_ID INTEGER NOT NULL REFERENCES STORE(STORE_ID),
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));
BEGIN TRANSACTION;
/* REGION rows */
INSERT IGNORE INTO REGION VALUES(1, 'East');
INSERT IGNORE INTO REGION VALUES(2, 'West');
/* STORE rows */
INSERT IGNORE INTO STORE VALUES(101, 48079, 1);
INSERT IGNORE INTO STORE VALUES(102, 48103, 2);
INSERT IGNORE INTO STORE VALUES(103, 48065, 1);
/* CATEGORY rows */
INSERT IGNORE INTO CATEGORY VALUES(1, 'Cutting');
INSERT IGNORE INTO CATEGORY VALUES(2, 'Drilling');
INSERT IGNORE INTO CATEGORY VALUES(3, 'Others');
/* VENDOR rows */
INSERT IGNORE INTO VENDOR VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y');
INSERT IGNORE INTO VENDOR VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL','N');
INSERT IGNORE INTO VENDOR VALUES(21231,'D&E Supply' ,'Singh' ,'615','228-3245','TN','Y');
INSERT IGNORE INTO VENDOR VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY','N');
INSERT IGNORE INTO VENDOR VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA','N');
INSERT IGNORE INTO VENDOR VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA','Y');
INSERT IGNORE INTO VENDOR VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN','N');
INSERT IGNORE INTO VENDOR VALUES(24288,'ORDVA, Inc.' ,'Hakford' ,'615','898-1234','TN','Y');
INSERT IGNORE INTO VENDOR VALUES(25443,'B&K, Inc.' ,'Smith' ,'904','227-0093','FL','N');
INSERT IGNORE INTO VENDOR VALUES(25501,'Damal Supplies' ,'Smythe' ,'615','890-3529','TN','N');
INSERT IGNORE INTO VENDOR VALUES(25595,'Rubicon Systems' ,'Orton' ,'904','456-0092','FL','Y');
/* PRODUCT rows */
INSERT IGNORE INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'03-NOV-2009', 8, 5,109.99,0.00,25595, 25,0,3);
INSERT IGNORE INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'13-DEC-2009', 32, 15, 14.99,0.05,21344, 50,0,1);
INSERT IGNORE INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'13-NOV-2009', 18, 12, 17.49,0.00,21344, 50,0,1);
INSERT IGNORE INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'15-JAN-2010', 15, 8, 39.95,0.00,23119, 35,0,3);
INSERT IGNORE INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'15-JAN-2010', 23, 5, 43.99,0.00,23119, 25,0,3);
INSERT IGNORE INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade' ,'30-DEC-2009', 8, 5,109.92,0.05,24288, 15,0,2);
INSERT IGNORE INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade' ,'24-DEC-2009', 6, 5, 99.87,0.05,24288, 15,0,2);
INSERT IGNORE INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.' ,'20-JAN-2010', 12, 5, 38.95,0.05,25595, 12,0,2);
INSERT IGNORE INTO PRODUCT VALUES('23109-HB','Claw hammer' ,'20-JAN-2010', 23, 10, 9.95,0.10,21225, 25,0,3);
INSERT IGNORE INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.' ,'02-JAN-2010', 8, 5, 14.40,0.05,NULL , 12,0,3);
INSERT IGNORE INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'15-DEC-2009', 43, 20, 4.99,0.00,21344, 25,0,3);
INSERT IGNORE INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'07-FEB-2010', 11, 5,256.99,0.05,24288, 10,0,1);
INSERT IGNORE INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'20-FEB-2010',188, 75, 5.87,0.00,NULL , 50,0,3);
INSERT IGNORE INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25' ,'01-MAR-2010',172, 75, 6.99,0.00,21225, 50,0,2);
INSERT IGNORE INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50' ,'24-FEB-2010',237,100, 8.45,0.00,21231,100,0,2);
INSERT IGNORE INTO PRODUCT VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2010', 18, 5,119.95,0.10,25595, 10,0,3);
/* CUSTOMER rows */
INSERT IGNORE INTO CUSTOMER VALUES(10010,'Ramas' ,'Alfred','A' ,'615','844-2573',0);
INSERT IGNORE INTO CUSTOMER VALUES(10011,'Dunne' ,'Leona' ,'K' ,'713','894-1238',0);
INSERT IGNORE INTO CUSTOMER VALUES(10012,'Smith' ,'Kathy' ,'W' ,'615','894-2285',345.86);
INSERT IGNORE INTO CUSTOMER VALUES(10013,'Olowski' ,'Paul' ,'F' ,'615','894-2180',536.75);
INSERT IGNORE INTO CUSTOMER VALUES(10014,'Orlando' ,'Myron' ,NULL,'615','222-1672',0);
INSERT IGNORE INTO CUSTOMER VALUES(10015,'O''Brian','Amy' ,'B' ,'713','442-3381',0);
INSERT IGNORE INTO CUSTOMER VALUES(10016,'Brown' ,'James' ,'G' ,'615','297-1228',221.19);
INSERT IGNORE INTO CUSTOMER VALUES(10017,'Williams','George',NULL,'615','290-2556',768.93);
INSERT IGNORE INTO CUSTOMER VALUES(10018,'Farriss' ,'Anne' ,'G' ,'713','382-7185',216.55);
INSERT IGNORE INTO CUSTOMER VALUES(10019,'Smith' ,'Olette','K' ,'615','297-3809',0);
/* CUSTOMER_2 rows */
INSERT IGNORE INTO CUSTOMER_2 VALUES(345,'Terrell','Justine','H','615','322-9870');
INSERT IGNORE INTO CUSTOMER_2 VALUES(347,'Olowski','Paul','F',615,'894-2180');
INSERT IGNORE INTO CUSTOMER_2 VALUES(351,'Hernandez','Carlos','J','723','123-7654');
INSERT IGNORE INTO CUSTOMER_2 VALUES(352,'McDowell','George',NULL,'723','123-7768');
INSERT IGNORE INTO CUSTOMER_2 VALUES(365,'Tirpin','Khaleed','G','723','123-9876');
INSERT IGNORE INTO CUSTOMER_2 VALUES(368,'Lewis','Marie','J','734','332-1789');
INSERT IGNORE INTO CUSTOMER_2 VALUES(369,'Dunne','Leona','K','713','894-1238');
/* EMPLOYEE rows */
INSERT IGNORE INTO EMPLOYEE VALUES(100,'Mr.' ,'Kolmycz' ,'George' ,'D' ,'15-JUN-1942','15-MAR-1985','615','324-5456',NULL,101);
INSERT IGNORE INTO EMPLOYEE VALUES(101,'Ms.' ,'Lewis' ,'Rhonda' ,'G' ,'19-MAR-1965','25-APR-1986','615','324-4472',100,101);
INSERT IGNORE INTO EMPLOYEE VALUES(102,'Mr.' ,'Vandam' ,'Rhett' ,NULL,'14-NOV-1958','20-DEC-1990','901','675-8993',100,101);
INSERT IGNORE INTO EMPLOYEE VALUES(103,'Ms.' ,'Jones' ,'Anne' ,'M' ,'16-OCT-1974','28-AUG-1994','615','898-3456',100,101);
INSERT IGNORE INTO EMPLOYEE VALUES(104,'Mr.' ,'Williams' ,'Robert' ,'D' ,'14-MAR-1975','08-NOV-1998','615','890-3220',NULL,102);
INSERT IGNORE INTO EMPLOYEE VALUES(105,'Mr.' ,'Lange' ,'John' ,'P' ,'08-NOV-1971','20-OCT-1994','901','504-4430',104,102);
INSERT IGNORE INTO EMPLOYEE VALUES(106,'Mrs.','Smith' ,'Jeanine','K' ,'12-FEB-1968','05-JAN-1989','615','324-7883',104,102);
INSERT IGNORE INTO EMPLOYEE VALUES(107,'Mr.' ,'Diante' ,'Jorge' ,'D' ,'21-AUG-1974','02-JUL-1994','615','890-4567',104,102);
INSERT IGNORE INTO EMPLOYEE VALUES(108,'Mr.' ,'Wiesenbach','Paul' ,'R' ,'14-FEB-1966','18-NOV-1992','615','897-4358',NULL,103);
INSERT IGNORE INTO EMPLOYEE VALUES(109,'Mr.' ,'Smith' ,'George' ,'K' ,'18-JUN-1961','14-APR-1989','901','504-3339',108,103);
INSERT IGNORE INTO EMPLOYEE VALUES(110,'Mrs.','Genkazi' ,'Leighla','W' ,'19-MAY-1970','01-DEC-1990','901','569-0093',108,103);
INSERT IGNORE INTO EMPLOYEE VALUES(111,'Mr.' ,'Washington','Rupert' ,'E' ,'03-JAN-1966','21-JUN-1993','615','890-4925',104,102);
INSERT IGNORE INTO EMPLOYEE VALUES(112,'Mr.' ,'Johnson' ,'Edward' ,'E' ,'14-MAY-1961','01-DEC-1983','615','898-4387',100,101);
INSERT IGNORE INTO EMPLOYEE VALUES(113,'Ms.' ,'Smythe' ,'Melanie','P' ,'15-SEP-1970','11-MAY-1999','615','324-9006',104,102);
INSERT IGNORE INTO EMPLOYEE VALUES(114,'Ms.' ,'Brandon' ,'Marie' ,'G' ,'02-NOV-1956','15-NOV-1979','901','882-0845',108,103);
INSERT IGNORE INTO EMPLOYEE VALUES(115,'Mrs.','Saranda' ,'Hermine','R' ,'25-JUL-1972','23-APR-1993','615','324-5505',104,102);
INSERT IGNORE INTO EMPLOYEE VALUES(116,'Mr.' ,'Smith' ,'George' ,'A' ,'08-NOV-1965','10-DEC-1988','615','890-2984',108,103);
/* INVOICE rows */
INSERT IGNORE INTO INVOICE VALUES(1001,10014,101,'16-JAN-2010',101);
INSERT IGNORE INTO INVOICE VALUES(1002,10011,101,'16-JAN-2010',101);
INSERT IGNORE INTO INVOICE VALUES(1003,10012,112,'16-JAN-2010',101);
INSERT IGNORE INTO INVOICE VALUES(1004,10011,111,'17-JAN-2010',102);
INSERT IGNORE INTO INVOICE VALUES(1005,10018,111,'17-JAN-2010',102);
INSERT IGNORE INTO INVOICE VALUES(1006,10014,109,'17-JAN-2010',103);
INSERT IGNORE INTO INVOICE VALUES(1007,10015,104,'17-JAN-2010',102);
INSERT IGNORE INTO INVOICE VALUES(1008,10011,114,'17-JAN-2010',103);
/* LINE rows */
INSERT IGNORE INTO LINE VALUES(1001,1,'13-Q2/P2',1,14.99);
INSERT IGNORE INTO LINE VALUES(1001,2,'23109-HB',1,9.95);
INSERT IGNORE INTO LINE VALUES(1002,1,'54778-2T',2,4.99);
INSERT IGNORE INTO LINE VALUES(1003,1,'2238/QPD',1,38.95);
INSERT IGNORE INTO LINE VALUES(1003,2,'1546-QQ2',1,39.95);
INSERT IGNORE INTO LINE VALUES(1003,3,'13-Q2/P2',5,14.99);
INSERT IGNORE INTO LINE VALUES(1004,1,'54778-2T',3,4.99);
INSERT IGNORE INTO LINE VALUES(1004,2,'23109-HB',2,9.95);
INSERT IGNORE INTO LINE VALUES(1005,1,'PVC23DRT',12,5.87);
INSERT IGNORE INTO LINE VALUES(1006,1,'SM-18277',3,6.99);
INSERT IGNORE INTO LINE VALUES(1006,2,'2232/QTY',1,109.92);
INSERT IGNORE INTO LINE VALUES(1006,3,'23109-HB',1,9.95);
INSERT IGNORE INTO LINE VALUES(1006,4,'89-WRE-Q',1,256.99);
INSERT IGNORE INTO LINE VALUES(1007,1,'13-Q2/P2',2,14.99);
INSERT IGNORE INTO LINE VALUES(1007,2,'54778-2T',1,4.99);
INSERT IGNORE INTO LINE VALUES(1008,1,'PVC23DRT',5,5.87);
INSERT IGNORE INTO LINE VALUES(1008,2,'WR3/TT3',3,119.95);
INSERT IGNORE INTO LINE VALUES(1008,3,'23109-HB',1,9.95);
COMMIT;
GRANT SELECT ON VENDOR TO PUBLIC;
GRANT SELECT ON PRODUCT TO PUBLIC;