Database... SQL 1. Display the product description, vendor code, product indate,
ID: 3939689 • Letter: D
Question
Database... SQL
1. Display the product description, vendor code, product indate, and product price of the products whose indate is before 21-Jan-2014 and product price less than or equal to 50.00. Order the output in descending order of the vendor code.
2. Find the total amount owned by customer.
3. Find the number of customers in each area code (Use Group by)
*Script
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');
/* -- */
CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
INSERT INTO INVOICE VALUES('1001','10014','16-Jan-2008', '24.8999996185303' , '1.99000000953674' ,' 26.8899993896484');
INSERT INTO INVOICE VALUES('1002','10011', '16-Jan-2008', '9.97999954223633', '0.800000011920929', '10.7799997329712');
INSERT INTO INVOICE VALUES('1003','10012', '16-Jan-2008','153.850006103516','12.3100004196167','166.160003662109');
INSERT INTO INVOICE VALUES('1004','10011', '17-Jan-2008','34.9700012207031','2.79999995231628','37.7700004577637');
INSERT INTO INVOICE VALUES('1005','10018', '17-Jan-2008','70.4400024414062','5.6399998664856','76.0800018310547');
INSERT INTO INVOICE VALUES('1006','10014', '17-Jan-2008','397.829986572266','31.8299999237061','429.660003662109');
INSERT INTO INVOICE VALUES('1007','10015', '17-Jan-2008','34.9700012207031','2.79999995231628','37.7700004577637');
INSERT INTO INVOICE VALUES('1008','10011', '17-Jan-2008','399.149993896484','31.9300003051758','431.079986572266');
/* -- */
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');
/* -- */
CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
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','3-Nov-2007','8','5','109.98999786377','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','13-Dec-2007','32','15','14.9899997711182','0.05','21344');
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','13-Dec-2007','18','12','17.4899997711182','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','15-Jan-2008','15','8','39.9500007629395','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','15-Jan-2008','23','5','43.9900016784668','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B/D jigsaw, 12-in. blade','30-Dec-2007','8','5','109.919998168945','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B/D jigsaw, 8-in. blade','24-Dec-2007','6','5','99.870002746582','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B/D cordless drill, 1/2-in.','20-Jan-2008','12','5','38.9500007629395','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','20-Jan-2008','23','10','9.94999980926514','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2-Jan-2008','8','5','14.3999996185303','0.05','');
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','15-Dec-2007','43','20','4.98999977111816','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','7-Feb-2008','11','5','256.989990234375','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','20-Feb-2008','188','75','5.86999988555908','0','');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25','1-Mar-2008','172','75','6.98999977111816','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','24-Feb-2008','237','100','8.44999980926514','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','17-Jan-2008','18','5','119.949996948242','0.1','25595');
/* -- */
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','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');
Explanation / Answer
a)
SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE FROM PRODUCT WHERE P_INDATE < '21-Jan-2014' AND P_PRICE <= 50 ORDER BY V_CODE DESC;
The above SQL statement will display the description, vendor code , indate and price from the product table where the product indate is less than 21-Jan-2014 and the price less than or equal to 50 in descending order of the vendor code.
b)
SELECT SUM(CUS_BALANCE) FROM CUSTOMER;
This sql statement will sum all the balances of the customer in the customer table.
c)
SELECT CUS_AREACODE, COUNT(*) FROM CUSTOMER GROUP BY CUS_AREACODE;
This sql statement will count the number of customers in each area code in the customer table.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.