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 to make the following changes to the Premiere Products database. After each change, execute an appropriate query
to show that the change was made correctly.
1. Use the following information to create a new table named NON_SP_GOOD.
|
Column
|
Type
|
Length
|
Decimal Places
|
Nulls Allowed?
|
Description
|
|
PART_NUM
|
Char
|
4
|
|
No
|
Part number (primary Key)
|
|
DESCRIPTION
|
Char
|
15
|
|
|
Part Description
|
|
ON_HAND
|
Decimal
|
4
|
0
|
|
Number of units on hand
|
|
CLASS
|
Char
|
2
|
|
|
Item class
|
|
PRICE
|
Decimal
|
6
|
2
|
|
Unit price
|
2. Insert into the NON_SP_GOOD table the part number, part description, number of units on hand, item class, and unit price
from the PART table for each part that is not in item class SG.
3. In the NON_SP_GOOD table, change the description of part number AT94 to "Deluxe Iron."
4. In the NON_SP_GOOD table, increase the price of each item in item class HW by 2%. (Hint: Multiply each price by 1.02.)
5. Add the following part to the NON_SP_GOOD table: part number: LJ28; description: Electric Razor; number of units on hand:
21; class: AP; and price: 39.95.
6. Delete every part in the NON_SP_GOOD table for which the class is HW.
7. In the NON_SP_GOOD table, change the class for part KL62 to null.
8. Add a column named ON_HAND_VALUE to the NON_SP_GOOD table. The allocation is a seven-digit number with two decimal
places, representing the product of the number of units on hand and the price. Then set all values of ON_HAND to ON_HAND *
PRICE.
9. In the NON_SP_GOOD table, increase the length of the PART_DESCRIPTION column to 30 characters.
10. Delete the NON_SP_GOOD table from the Premiere Products database.