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 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.