APlusGrades

Not Rated (0)

$15/per page/Negotiable

About APlusGrades

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Accounting,Algebra See all
Accounting,Algebra,Applied Sciences,Architecture and Design,Art & Design,Biology,Business & Finance,Calculus,Chemistry,Communications,Computer Science,Economics,Engineering,English,Environmental science,Essay writing,Foreign Languages,Geography,Geology,Health & Medical,HR Management,Law,Management,Physics,Programming,Science,Statistics Hide all
Teaching Since: Jul 2017
Last Sign in: 364 Weeks Ago, 4 Days Ago
Questions Answered: 1850
Tutorials Posted: 1850

Education

  • Graduate in Biology and Nutrition, MBA Finance
    Florida State University
    Aug-2000 - Jul-2007

Experience

  • Ass. Relationship Manager
    Penn-Florida
    Mar-2009 - Feb-2016

Category > Computer Science Posted 20 Jul 2017 My Price 12.00

ORDERS CUSTOMER P * CUSTNO * CUSTFIRSTNAME * CUSTLASTNAME CUSTSTREET CUSTCITY CUSTSTATE CUSTZIP CUSTBAL VARCHAR2 (8 BYTE) VARCHAR2 (20 BYTE) VARCHAR2...

Question 2: (20 Points)

Continue Question 1 above. It is important for you to create those tables with the exact table/column name and exact column data type/length as shown in the ERD.

Run the DBST651_final_Part2_q2.sql script attached and then respond the following SQL problems:

Create SQL statements for the following scenarios. Your response should include SQL statement, output and any other assumptions you have made to arrive at the solution. Please provide a single SQL query for each problem.

a. For Seattle, WA customers compute the average amount of their orders and the number of orders placed. The result should include the customer number, customer last name, average order amount and the number of orders placed. Show results in the order of average order amount from high to low.

b. For Seattle, WA customers compute the number of unique products ordered. If a product is purchased on multiple orders, it should be counted only one time. The result should include customer number, customer last name and the number of unique products ordered. Show results in the order of customer’s last name.

c. For each employee with a commission less than 0.05, compute the number of orders taken and the average product total quantity per order. The results should include the employee number, employee last name, number of orders taken and the average product total quantity per order. Show results in the order of number of orders taken from low to high.

d. For each Connex product compute the number of unique customers who ordered the product in Jan 2007. The results should include the product number, product name and the number of unique customers. Show results in the order of product name.

 

 

DELETE FROM Order_Line;

DELETE FROM Orders;

DELETE FROM Customer;

DELETE FROM Employee;

DELETE FROM Product;

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P0036566','17 inch Color Monitor','ColorMeg, Inc.',12,'20-Feb-2007',189.00);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P0036577','19 inch Color Monitor','ColorMeg, Inc.',10,'20-Feb-2007',369.00);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P1114590','R3000 Color Laser Printer','Connex',5,'22-Jan-2007',999.00);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P1412138','10 Foot Printer Cable','Ethlite',100,'',18.00);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P1445671','8-Outlet Surge Protector','Intersafe',33,'',19.99);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P1556678','CVP Ink Jet Color Printer','Connex',8, '22-Jan-2007',199.00);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P3455443','Color Ink Jet Cartridge','Connex',24,'22-Jan-2007',68.00);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P4200344','36-Bit Color Scanner','UV Components',16,'29-Jan-2007',169.99);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P6677900','Black Ink Jet Cartridge','Connex',44,'',25.69);

 

INSERT INTO product

(ProdNo, ProdName, ProdMfg, ProdQOH, ProdNextShipDate, ProdPrice)

VALUES ('P9995676','Battery Back-up System','Cybercx',12,'1-Feb-2007',69.00);

 

-------------

 

 

INSERT INTO employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,

  SupEmpNo, EmpCommRate)

VALUES ('E9884325','Thomas','Johnson','(303) 556-9987','TJohnson@bigco.com','',0.05);

 

INSERT INTO employee

(EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,

  SupEmpNo, EmpCommRate)

VALUES ('E8843211','Amy','Tang','(303) 556-4321','ATang@bigco.com','E9884325',0.06);

 

INSERT INTO employee

(EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,

  SupEmpNo, EmpCommRate)

VALUES ('E9345771','Colin','White','(303) 221-4453','CWhite@bigco.com','E9884325',0.04);

 

INSERT INTO employee

(EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,

  SupEmpNo, EmpCommRate)

VALUES ('E1329594','Landi','Santos','(303) 789-1234','LSantos@bigco.com','E8843211',0.08);

 

INSERT INTO employee

(EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,

  SupEmpNo, EmpCommRate)

VALUES ('E8544399','Joe','Jenkins','(303) 221-9875','JJenkins@bigco.com','E8843211',0.02);

 

 

INSERT INTO employee

(EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,

  SupEmpNo, EmpCommRate)

VALUES ('E9954302','Mary','Hill','(303) 556-9871','MHill@bigco.com','E8843211',0.02);

 

INSERT INTO employee

(EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,

  SupEmpNo, EmpCommRate)

VALUES ('E9973110','Theresa','Beck','(720) 320-2234','TBeck@bigco.com','E9884325', 0.09);

 

------------------------

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C0954327','Sheri','Gordon','336 Hill St.','Littleton','CO','80129-5543',230.00);

 

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C1010398','Jim','Glussman','1432 E. Ravenna','Denver','CO','80111-0033',200.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C2388597','Beth','Taylor','2396 Rafter Rd','Seattle','WA','98103-1121',500.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C3340959','Betty','Wise','4334 153rd NW','Seattle','WA','98178-3311',200.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C3499503','Bob','Mann','1190 Lorraine Cir.','Monroe','WA','98013-1095',0.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C8543321','Ron','Thompson','789 122nd St.','Renton','WA','98666-1289',85.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C8574932','Wally','Jones','411 Webber Ave.','Seattle','WA','98105-1093',1500.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C8654390','Candy','Kendall','456 Pine St.','Seattle','WA','98105-3345',50.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9128574','Jerry','Wyatt','16212 123rd Ct.','Denver','CO','80222-0022',100.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9403348','Mike','Boren','642 Crest Ave.','Englewood','CO','80113-5431',0.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9432910','Larry','Styles','9825 S. Crest Lane','Bellevue','WA','98104-2211',250.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9543029','Sharon','Johnson','1223 Meyer Way','Fife','WA','98222-1123',856.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9549302','Todd','Hayes','1400 NW 88th','Lynnwood','WA','98036-2244',0.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9857432','Homer','Wells','123 Main St.','Seattle','WA','98105-4322',500.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9865874','Mary','Hill','206 McCaffrey','Littleton','CO','80129-5543',150.00);

 

INSERT INTO customer

(CustNo, CustFirstName, CustLastName, CustStreet, CustCity,

  CustState, CustZip, CustBal) 

VALUES('C9943201','Harry','Sanders','1280 S. Hill Rd.','Fife','WA','98222-2258',1000.00);

 

-----------------------

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O1116324','23-Feb-2007','C0954327','E8544399','Sheri Gordon','336 Hill St.','Littleton','CO','80129-5543');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O1231231','23-Jan-2007','C9432910','E9954302','Larry Styles','9825 S. Crest Lane','Bellevue','WA','98104-2211');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O1241518','10-Jan-2007','C9549302','','Todd Hayes','1400 NW 88th','Lynnwood','WA','98036-2244');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O1455122','9-Jan-2007','C8574932','E9345771','Wally Jones','411 Webber Ave.','Seattle','WA','98105-1093');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O1579999','5-Jan-2007','C9543029','E8544399','Tom Johnson','1632 Ocean Dr.','Des Moines','WA','98222-1123');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O1615141','23-Jan-2007','C8654390','E8544399','Candy Kendall','456 Pine St.','Seattle','WA','98105-3345');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O1656777','11-Feb-2007','C8543321','','Ron Thompson','789 122nd St.','Renton','WA','98666-1289');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O2233457','12-Jan-2007','C2388597','E9884325','Beth Taylor','2396 Rafter Rd','Seattle','WA','98103-1121');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O2334661','14-Jan-2007','C0954327','E1329594','Mrs. Ruth Gordon','233 S. 166th','Seattle','WA','98011');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O3252629','23-Jan-2007','C9403348','E9954302','Mike Boren','642 Crest Ave.','Englewood','CO','80113-5431');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O3331222','13-Jan-2007','C1010398','','Jim Glussman','1432 E. Ravenna','Denver','CO','80111-0033');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O3377543','15-Jan-2007','C9128574','E8843211','Jerry Wyatt','16212 123rd Ct.','Denver','CO','80222-0022');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O4714645','11-Jan-2007','C2388597','E1329594','Beth Taylor','2396 Rafter Rd','Seattle','WA','98103-1121');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O5511365','22-Jan-2007','C3340959','E9884325','Betty White','4334 153rd NW','Seattle','WA','98178-3311');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O6565656','20-Jan-2007','C9865874','E8843211','Mr. Jack Sibley','166 E. 344th','Renton','WA','98006-5543');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O7847172','23-Jan-2007','C9943201','','Harry Sanders','1280 S. Hill Rd.','Fife','WA','98222-2258');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O7959898','19-Feb-2007','C8543321','E8544399','Ron Thompson','789 122nd St.','Renton','WA','98666-1289');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O7989497','16-Jan-2007','C3499503','E9345771','Bob Mann','1190 Lorraine Cir.','Monroe','WA','98013-1095');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O8979495','23-Jan-2007','C9865874','','HelenSibley','206 McCaffrey','Renton','WA','98006-5543');

 

INSERT INTO Orders

(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,

  OrdState, OrdZip)

VALUES ('O9919699','11-Jan-2007','C9857432','E9954302','Homer Wells','123 Main St.','Seattle','WA','98105-4322');

 

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1116324','P1445671',2);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1231231','P0036566',3);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1231231','P1445671',5);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1241518','P0036577',7);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1455122','P4200344',6);

 

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1579999','P1556678',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1579999','P6677900',9);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1579999','P9995676',9);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1615141','P0036566',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1615141','P1445671',3);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1615141','P4200344',6);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1656777','P1445671',3);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O1656777','P1556678',7);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O2233457','P0036577',7);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O2233457','P1445671',7);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O2334661','P0036566',9);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O2334661','P1412138',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O2334661','P1556678',6);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O3252629','P4200344',3);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O3252629','P9995676',6);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O3331222','P1412138',9);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O3331222','P1556678',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O3331222','P3455443',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O3377543','P1445671',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O3377543','P9995676',7);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O4714645','P0036566',7);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O4714645','P9995676',7);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O5511365','P1412138',3);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O5511365','P1445671',6);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O5511365','P1556678',9);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O5511365','P3455443',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O5511365','P6677900',8);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O6565656','P0036566',10);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7847172','P1556678',1);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7847172','P6677900',1);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7959898','P1412138',5);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7959898','P1556678',5);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7959898','P3455443',5);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7959898','P6677900',5);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7989497','P1114590',2);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7989497','P1412138',2);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O7989497','P1445671',3);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O8979495','P1114590',1);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O8979495','P1412138',1);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O8979495','P1445671',1);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O9919699','P0036577',1);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O9919699','P1114590',1);

 

INSERT INTO Order_Line

(OrdNo, ProdNo, Qty)

VALUES('O9919699','P4200344',1);

 

commit;

Attachments:

Answers

APlusGrades
Not Rated (0)
Status NEW Posted 20 Jul 2017 02:07 PM My Price 12.00

Hel-----------lo -----------Sir-----------/Ma-----------dam----------- Â-----------  -----------Tha-----------nk -----------you----------- fo-----------r u-----------sin-----------g o-----------ur -----------web-----------sit-----------e a-----------nd -----------acq-----------uis-----------iti-----------on -----------of -----------my -----------pos-----------ted----------- so-----------lut-----------ion-----------.Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m Â----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I

Not Rated(0)