Premiere Products Database (figure 1.2)
Table: REP
|
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: 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.