SOLVING THIS PROBLEM USING MYSQL WORKBENCH FOR WRITING CODE,please Use the Sales
ID: 3595635 • Letter: S
Question
SOLVING THIS PROBLEM USING MYSQL WORKBENCH FOR WRITING CODE,please
Use the Sales_Company SQL script file to create the tables and load the data into a schema called sales_co. You may have already completed this as part of you last assignment so you may not need to do it again. You may find an example ERD in the previous assignment.
Using MySQL SELECT commands answer the questions listed below within the sales_co schema. Submit via the D2L drop box the final SQL script for all problems. All questions are worth 2 points each.
1. Write a query to produce the output listed below. The results should be sorted by cus_balance from highest to lowest and by cus_lname from lowest to highest.
2. How many customers have outstanding balances and what is the overall total and average of the outstanding balances? Your output should have the same column headings as shown below and the balances should be listed out to two decimal places.
3. Write a query to produce the output listed below which shows the number of invoice lines and the overall total of the subtotals of all the invoice lines for each customer. The results should have the same column headings, the Total_Purchases column listed to two decimal places and the data sorted by cus_lname and then by cus_fname. (Hint: The Total_Purchases column is a calculated column.)
4. Write the query to produce the output listed below which shows by vendor the number of products supplied and the lowest and highest product price. The results should have the same column headings and be sorted by Num_Products_Supplied in highest to lowest values and then by Vendor.
5. Write a query to produce the output listed below which shows any product, which has a price greater than the average of all products. The results should be sorted by the p_indate column.
Below is a sale company SQL script file for running in mySQL
/* Database Systems, 9th Ed., Coronel/Morris/Rob */
/* Type of SQL : MySQL */
/* This script creates and loads data in order to create the Sale Company database*/
CREATE SCHEMA IF NOT EXISTS SALES_CO;
USE SALES_CO;
DROP TABLE IF EXISTS line;
DROP TABLE IF EXISTS invoice;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS vendor;
DROP TABLE IF EXISTS customer;
/*Create table Customer*/
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 DECIMAL(9,2) DEFAULT 0.00);
/*Create table Vendor*/
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*/
CREATE TABLE product
(p_code VARCHAR(10) PRIMARY KEY,
p_descript VARCHAR(35) NOT NULL,
p_indate DATE NOT NULL,
p_qoh INTEGER NOT NULL,
p_min INTEGER NOT NULL,
p_price DECIMAL(8,2) NOT NULL,
p_discount DECIMAL(5,2) NOT NULL,
v_code INTEGER,
CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (v_code) REFERENCES vendor(v_code));
/*Create table Invoice*/
CREATE TABLE invoice
(inv_number INTEGER PRIMARY KEY,
cus_code INTEGER NOT NULL REFERENCES customer(cus_code),
inv_date DATE NOT NULL,
CONSTRAINT INVOICE_CUSTOMER_FK FOREIGN KEY (cus_code) REFERENCES customer (cus_code),
CONSTRAINT INV_CK1 CHECK (inv_date > TO_DATE('2010-01-01','YYYY-MM-DD')));
/*Create table Line*/
CREATE TABLE line
(inv_number INTEGER NOT NULL,
line_number INTEGER NOT NULL,
p_code VARCHAR(10) NOT NULL,
line_units DECIMAL(9,2) DEFAULT 0.00 NOT NULL,
line_price DECIMAL(9,2) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (inv_number, line_number),
FOREIGN KEY (inv_number) REFERENCES invoice(inv_number) ON DELETE CASCADE,
FOREIGN KEY (p_code) REFERENCES product(p_code));
/*Insert data into Customer*/
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);
/*Insert data into vendor*/
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');
/*Insert data into Product*/
INSERT INTO product VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'2009-11-03', 8, 5,109.99,0.00,25595);
INSERT INTO product VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'2009-12-13', 32, 15, 14.99,0.05,21344);
INSERT INTO product VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'2009-11-13', 18, 12, 17.49,0.00,21344);
INSERT INTO product VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'2010-01-15', 15, 8, 39.95,0.00,23119);
INSERT INTO product VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'2010-01-15', 23, 5, 43.99,0.00,23119);
INSERT INTO product VALUES('2232/QTY','B&D jigsaw, 12-in. blade' ,'2009-12-30', 8, 5,109.92,0.05,24288);
INSERT INTO product VALUES('2232/QWE','B&D jigsaw, 8-in. blade' ,'2009-12-24', 6, 5, 99.87,0.05,24288);
INSERT INTO product VALUES('2238/QPD','B&D cordless drill, 1/2-in.' ,'2010-01-20', 12, 5, 38.95,0.05,25595);
INSERT INTO product VALUES('23109-HB','Claw hammer' ,'2010-01-20', 23, 10, 9.95,0.10,21225);
INSERT INTO product VALUES('23114-AA','Sledge hammer, 12 lb.' ,'2010-01-02', 8, 5, 14.40,0.05,NULL);
INSERT INTO product VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'2009-12-15', 43, 20, 4.99,0.00,21344);
INSERT INTO product VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'2010-02-07', 11, 5,256.99,0.05,24288);
INSERT INTO product VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'2010-02-20',188, 75, 5.87,0.00,NULL);
INSERT INTO product VALUES('SM-18277','1.25-in. metal screw, 25' ,'2010-03-01',172, 75, 6.99,0.00,21225);
INSERT INTO product VALUES('SW-23116','2.5-in. wd. screw, 50' ,'2010-02-24',237,100, 8.45,0.00,21231);
INSERT INTO product VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','2010-01-17', 18, 5,119.95,0.10,25595);
/*Insert data into Invoice*/
INSERT INTO invoice VALUES(1001,10014,'2010-01-16');
INSERT INTO invoice VALUES(1002,10011,'2010-01-16');
INSERT INTO invoice VALUES(1003,10012,'2010-01-16');
INSERT INTO invoice VALUES(1004,10011,'2010-01-17');
INSERT INTO invoice VALUES(1005,10018,'2010-01-17');
INSERT INTO invoice VALUES(1006,10014,'2010-01-17');
INSERT INTO invoice VALUES(1007,10015,'2010-01-17');
INSERT INTO invoice VALUES(1008,10011,'2010-01-17');
/*Insert data into Line*/
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);
Explanation / Answer
(1)
SELECT * FROM CUSTOMER ORDER BY CUS_BALANCE DESC, CUS_LNAME;
(2)
SELECT count(*) as CUSTOMERS_WITH_OUTSTNDING_BAL,
sum(CUS_BALANCE) as TOTAL_OUTSTNDING_BAL,
avg(CUS_BALANCE) as AVERAGE_OUTSTNDING_BAL
FROM CUSTOMER WHERE (CUS_BALANCE > 0);
(3)
SELECT sum(LINE_NUMBER), INV_NUMBER,
sum(LINE_PRICE) AS Total_Purchases FROM LINE GROUP BY INV_NUMBER;
(4)
SELECT count(*) as PRODUCTS_SUPPLIED_BY_VENDOR,
min(p_price) as MIN_PROD_PRICE,
max(P_PRICE) as MAX_PROD_PRICE,
V_CODE
FROM PRODUCT
GROUP BY v_code
ORDER BY PRODUCTS_SUPPLIED_BY_VENDOR, V_CODE;
(5)
SELECT P_CODE, P_DESCRIPT, P_PRICE, P_INDATE
FROM PRODUCT
WHERE P_PRICE > (SELECT avg(P_PRICE) FROM PRODUCT)
ORDER BY P_INDATE;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.