USE SQL & show screenshots (Questions: 7,9,10,11,13,15,16) 8:09 .For each order,

Question

USE SQL & show screenshots

(Questions: 7,9,10,11,13,15,16)

8:09 .For each order, the database must store the order number, the part number, the number of units ordered, and the quoted price for each order line. The part description is stored with the information about parts. The result of multiply- ing the number of units ordered by the quoted price is not stored because the computer can calculate it when needed. .The overall order total is not stored. Instead, the database calculates the total whenever an order is printed or displayed on the screen. Figure 1-2 shows sample data for Premiere Products. REP REP LAST FIAST STREET CITY STATE ZIP COMMISSION RATE NUM NAME NAME 20 Kaiser Valerie 35 Hull Richard 532 JacksonSheldon FL. 33321$20,542.50 0.05 33553$39,216.00 0.07 |33336 I s23,487.00 10.05 24 Randall Goe FL 1 Perez | Juan 11626 Taylor l Fillmore | FL CUSTOMER CUSTOMER STREET NAME STATE ZIP BALANCE CREDIT REP LIMIT NUM 148 Fillmore FL 33336$6,550.00 $7,300.00 20 Al's Appliance 2837 and Sport 282 Brookings 3827 Devon Grove FL 33321 $431.50 $10,000.00 33 356 3146 $5,783.00 $7,300.00 63 Ferguson's The 82 Wildwood Northfield FL. 1S28 Raven Crystal FL 33503 $5,283.25 $5,000.00 35 Shop 462 3829 Central Grove FL 33321 $3,412.00 $10,000.00 65 524 Kline's 838 Ridgeland Fillmore FL 33336 $12,762.00 $15,000.00 20 372 Oxford Sheldon FL 33553 $2,106.00 $10,000.00 65 Johnson's Store Lee's Sport 282 Evergreen Altonville FL 32343 $2,851.00 $5,000.00 35 Deerfield's 282 Columbia Sheldon FL 33553 S248.00$7,500.0035 687 725 Four Seasons All Season 28 Lakeview Grove Sample data for Premiere Products 842 FIGURE 1-2 Chapter 1 Copyright 2006 Thomson Learning, Inc. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part.

Explanation / Answer

7) Table ORDERS, ORDER_LINE and PART are joined to get all related fields.

SELECT o.ORDER_NUM, o.ORDER_DATE, p.PART_NUM, p.DESCRIPTION, p.CLASS
FROM ORDERS AS o
INNER JOIN ORDER_LINE AS ol
ON o.ORDER_NUM = ol.ORDER_NUM
INNER JOIN PART AS p
ON ol.PART_NUM = p.PART_NUM;

9) Sunquery is used in WHERE clause to get the Rep_num from the customer table.

SELECT r.REP_NUM, r.LAST_NAME, r.FIRST_NAME
FROM REP AS r
WHERE r.REP_NUM IN (SELECT DISTINCT REP_NUM FROM CUSTOMER WHERE CREDIT_LIMIT = 5000.00);

10) INNER JOIN is between two table REP and CUSTOMEr is used to get all the relevant REP.

SELECT DISTINCT r.REP_NUM, r.LAST_NAME, r.FIRST_NAME
FROM REP AS r
INNER JOIN CUSTOMER AS c
ON c.REP_NUM = r.REP_NUM
WHERE c.CREDIT_LIMIT = 5000.00;

11) 4 tables are joined to get the data. In where class Gas Range filter is used on part description.

SELECT c.CUSTOMER_NUM, c.CUSTOMER_NAME
FROM CUSTOMER AS c
INNER JOIN ORDERS AS o
ON o.CUSTOMER_NUM = c.CUSTOMER_NUM
INNER JOIN ORDER_LINE AS ol
ON o.ORDER_NUM = ol.ORDER_NUM
INNER JOIN PART AS p
ON p.PART_NUM = ol.PART_NUM
WHERE p.DESCRIPTION = 'Gas Range';

13) Subquery is used in where clause to get the customer_num by name Johnson's Department Store.

SELECT o.ORDER_NUM, o.ORDER_DATE
FROM ORDERS AS o
WHERE o.CUSTOMER_NUM = (SELECT CUSTOMER_NUM FROM CUSTOMER WHERE CUSTOMER_NAME = 'Johnson''s Department Store');

15) 4 tables are joined and in WHERE clause two conditions are joined by OR condition.

SELECT o.ORDER_NUM, o.ORDER_DATE
FROM CUSTOMER AS c
INNER JOIN ORDERS AS o
ON o.CUSTOMER_NUM = c.CUSTOMER_NUM
INNER JOIN ORDER_LINE AS ol
ON o.ORDER_NUM = ol.ORDER_NUM
INNER JOIN PART AS p
ON p.PART_NUM = ol.PART_NUM
WHERE p.DESCRIPTION = 'Gas Range' OR c.CUSTOMER_NAME = 'Johnson''s Department Store';

16) 4 tables are joined and in where clause two conditions are joined by AND .

SELECT o.ORDER_NUM, o.ORDER_DATE
FROM CUSTOMER AS c
INNER JOIN ORDERS AS o
ON o.CUSTOMER_NUM = c.CUSTOMER_NUM
INNER JOIN ORDER_LINE AS ol
ON o.ORDER_NUM = ol.ORDER_NUM
INNER JOIN PART AS p
ON p.PART_NUM = ol.PART_NUM
WHERE p.DESCRIPTION = 'Gas Range' AND c.CUSTOMER_NAME = 'Johnson''s Department Store';

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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