SQL Commands

Premiere Products Database (figure 1.2)

REP_NUM LAST_NAME FIRST_NAME STREET CITY STATE ZIP COMMISSION RATE

20 Kaiser Valerie 624 Randall Grove FL 33321 $20,542.50 0.05

35 Hull Richard 532 Jackson Sheldon FL 33553 $39,216.00 0.07

65 Perez Juan 1626 Taylor Fillmore FL 33336 $23,487.00 0.05

Table: REP

Table: CUSTOMER

CUSTOMER_NUM CUSTOMER_NAME STREET CITY STATE ZIP BALANCE CREDIT_LIMIT
REP_NUM

148 Al’s Appliance and Sport 2837 Greenway Fillmore FL 33336
$6,550.00 $7,500.00 20

282 Brookings Direct 3827 Devon Grove FL 33321 $431.50 $10,000.00 35

356 Ferguson’s 382 Wildwood Northfield FL 33146 $5,785.00 $7,500.00 65

408 The Everything Shop 1828 Raven Crystal FL 33503 $5,285.25 $5,000,00
35

462 Bargains Galore 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

608 Johnson’s Department Store 372 Oxford Sheldon FL 33553 $2,106.00
$10,000,00 65

687 Lee’s Sport and Appliance 282 Evergreen Altonville FL 32543
$2,851.00 $5,000.00 35

725 Deerfield’s Four Seasons 282 Columbia Sheldon FL 33553 $248.00
$7,500.00 35

842 All Season 28 Lakeview Grove FL 33321 $8,221.00 $7,500.00 20



Table: ORDERS

ORDER_NUM ORDER_DATE CUSTOMER_NUM

21608 10/20/2003 148

21610 10/20/2003 356

21613 10/21/2003 408

21614 10/21/2003 282

21617 10/23/2003 608

21619 10/23/2003 148

21623 10/23/2003 608



Table: ORDER_LINE

ORDER_NUM PART_NUM NUM_ORDERED QUOTED_PRICE

21608 AT94 11 $21.95

21610 DR93 1 $495.00

21610 DW11 1 $399.99

21613 KL62 4 $329.95

21614 KT03 2 $595.00

21617 BV06 2 $794.95

21617 CD52 4 $150.00

21619 DR93 1 $495.00

21623 KV29 2 $1,290.00



Table: PART

PART_NUM DESCRIPTION ON_HAND CLASS WAREHOUSE PRICE

AT94 Iron 50 HW 3 $24.95

BV06 Home Gym 45 SG 2 $794.95

CD52 Microwave Oven 32 AP 1 $165.00

DL71 Cordless Drill 21 HW 3 $129.95

DR93 Gas Range 8 AP 2 $495.00

DW11 Washer 12 AP 3 $399.99

FD21 Stand Mixer 22 HW 3 $159.95

KL62 Dryer 12 AP 1 $349.95

KT03 Dishwasher 8 AP 3 $595.00

KV29 Treadmill 9 SG 2 $1,390.00



Use SQL, and the Premiere Products database (see Figure 1.2) to complete
the following exercises.

1. For each order placed on October 20, 2003, list the order number
along with the number and name of the customer that placed the order.

2. Use the IN operator to find the number and name of each customer
that placed an order on October 20, 2003.

3. Find the number and name of each customer that did not place an
order on October 20, 2003.

4. For each order, list the order number, order date, part number, part
description, and item class for each part that makes up the order but
order the rows by item class and then by order number.

5. Find the rep number, last name, and first name of each sales rep who
represents at least one customer with a credit limit of $10,000. List
each sales rep only once in the results. Do not use a subquery.

6. List the part number, part description, and item class for each pair
of parts that are in the same item class.

7. List the order number and order date for each order that contains an
order line for a Gas Range.

8. List the order number and order date for each order that was placed
by Ferguline for a Gas Range.

9. List the part number, part description, unit price, and item class
for each part that has a unit price greater that the unit price of every
part in item class AP. Use either the ALL or ANY operator in your
query. (Hint: Make sure you select the correct operator.)

10. For each part, list the part number, description, units on hand,
order number, and number of units ordered. All parts should be included
in the results. For those parts that are currently not on order, the
order number and number of units ordered should be left blank. Order
the results by part number.