Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

PART II Question 1: The diagram below (also attached in DBST651_final_part2_ERD.

ID: 3820756 • Letter: P

Question

PART II Question 1:

The diagram below (also attached in DBST651_final_part2_ERD.pdf ) is an ERD for an order entry database for a company. A customer orders products through company employee.

Study the ERD and answer the following questions:

Identify all relationships and specify cardinality and business rules. For example: 1:M between Customer and Orders: a customer can place many orders; an order will be placed by one and only one customer.

For each entity, identify primary key and foreign key if any. For foreign key, also specify parent entity and matching attribute in parent entity.

Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables.

Write SQL statement for the following scenario:

Add a new customer John Smith with custono 1 and custzip 23456.

Save changes permanently.

Changer customer John Smith zip from 23456 to 20001.

Cancel the change made in step c, restore data to its original status prior to step c.

Delete customer John Smith.

Save changes permanently.

For SQL code, submit both source statements and results of running your statements.

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 below and then respond the following SQL problems:

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;

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.

ORDERS CUSTOMER ORDNO VARCHAR2 (8 BYTE) ORD DATE DATE CUSTNO VARCHAR2 (8 BYTE) CUST FIRSTNAME VARCHAR2 20 BYTE) CUSTNO VARCHAR2 (8 BYTE) CUST LASTNAME VARCHAR2 30 BYTE) EMPNO VARCHAR2 (8 BYTE) VARCHAR2 (50 BYTE CUSTSTREET ORD NAME VARCHAR2 (50 BYTE CUST CITY VARCHAR2 30 BYTE) ORD STREET VARCHAR2 (50 BYTE ORD CITY VARCHAR2 300 BYTE) Pi CUST STATE VARCHAR2 BYTE) CUSTZP VARCHAR2 (10 BYTE) ORD STATE VARCHAR2 BYTE) CUSTBAL NUMBER 12,2) ORDZIP VARCHAR2 (10 BYTE PRODUCT ORDER LINE P PRODNO VARCHAR2 (8 BYTE) PF ORDN0 VARCHAR2 (8 BYTE PROD NAME VARCHAR2 (50 BYTE) PF PRODNO VARCHAR2 (8 BYTE) PROD MFG QTY NUMBER 00 PRODOOH NUMBER CO) PROD PRICE NUMBER (12,2) PROD NEXT SHIPDATE DATE EMPLOYEE EMPNO EMPFIRSTNAME VARCHAR2 BYTE) EMPLASTNAME VARCHAR2 80 BYTE) EMP PHONE VARCHAR2 (15 BYTE) EMPEMAIL VARCHAR2 (50 BYTE) SUPEMPNO VARCHAR2 (8 BNTE) EMP COMMRATE NUMBER 3.3) NOT NULL P: Primary Key F: Foreign Key U: Unique PF: Primary Key and Foreign Key

Explanation / Answer

1.select CUSTNO, CUSTLASTNAME,count(ORDNO) ,avg(PRODPRICE) from CUSTOMER C, ORDERS O, ORDER_LINE OL, PRODUCT P where C. CUSTNO=0. CUSTNO and O. ORDNO=OL. ORDNO and

P. PRODNO =OL. PRODNO group by CUSTNO;

2. select CUSTNO, CUSTLASTNAME,Distinct(PRODNAME) as “Unique Product” from CUSTOMER C, ORDERS O, ORDER_LINE OL, PRODUCT P where C. CUSTNO=0. CUSTNO and O. ORDNO=OL. ORDNO and P. PRODNO =OL. PRODNO group by CUSTNO;

3.select EMPNO, EMPLASTNAME, count(ORDNO) ,avg(PRODPRICE) from EMPLOYEE E, ORDERS O, ORDER_LINE OL, PRODUCT P where E. ORDNO =0. ORDNO and O. ORDNO=OL. ORDNO and

P. PRODNO =OL. PRODNO and E. EMPCOMMRATE<0.05 group by EMPNO order by count(ORDNO);

4.select PRODNO, PRODNAME ,count(distinct CUSTNO) from CUSTOMER C, ORDERS O, ORDER_LINE OL, PRODUCT P where C. CUSTNO=0. CUSTNO and O. ORDNO=OL. ORDNO and

P. PRODNO =OL. PRODNO and ORDDATE =’JAN 2007’ and PRODMFG=’Connex’ group by PRODNO order by PRODNAME;

Related Questions

PART E) Find the potential difference across each capacitor. For the capacitor n
Question #1278112
PART E: Assessments Match the cranial nerves in column A with the associated fun
Question #3482436
PART EIGHT What are the professional codes of software engineering ethics? How d
Question #3792138
PART F: Assessments I. Summarie the rmualts of the buasie al tets perfomed to de
Question #3478978
PART FOUR The Hawthorne effect refers to _____. a. poor sampling procedures lead
Question #3205477
PART G: DECOMPOSITION REACTION. AB A+B THIS REACTION MUST BE PERFORMED IN THE FU
Question #1046215
PART I (Arguments and Non-arguments) Determine which of the following passages c
Question #3012729
PART I (Arguments and Nonarguments) (4pts, ca. x 10 40pts) Determine which of th
Question #3445115

Navigate

Previous
PART II Problem 2 TAMIU Company\'s record of transactions concerning part X for
Next
PART II Question 1: The diagram below (also attached in DBST651_final_part2_ERD.

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.