Delete the nonspgood table from the premiere products

Question

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.