Paula Noone
SQL Summer 2009
P 177 QUESTIONS
(2A, 5A, 7A, 8B, 9A AT END OF PAGE)


#1. Create Table NONAPPLIANCE, including PART_NUM, DESCRIPTION, ON_HAND, CLASS, PRICE

Field Type Collation Attributes Null Default Extra Action

  PART_NUM char(4) latin1_swedish_ci  No None
  DESCRIPTION char(30) latin1_swedish_ci  Yes NULL
  ON_HAND decimal(4,0)   Yes NULL
  CLASS char(2) latin1_swedish_ci  Yes NULL
  PRICE decimal(6,2)   Yes NULL
  ON_HAND_VALUE decimal(7,2)   Yes NULL


#2.  Insert into NONAPPLIANCE, PART_NUM, DESC, On_Hand, Class, and Price from the Part Table except Class "AP"

INSERT INTO NONAPPLIANCE SELECT "PART_NUM", "DESCRIPTION", "ON_HAND", "CLASS", "PRICE" FROM Part where CLASS <> "AP"


#SHOW RESULTS


SELECT *
FROM `NONAPPLIANCE`
GROUP BY PRICE
LIMIT 0 , 30


PART_NUM  DESCRIPTION  ON_HAND  CLASS  PRICE  ON_HAND_VALUE
      AT94 DELUXE IRON 50 HW 24.95 1247.50
      DL71 Cordless Drill 21 HW 129.95 2728.95
      FD21 Stand Mixer 22 NULL 159.95 3518.90
      TL92 TRIMMER 11 HW 29.95 329.45


#3.  In the NONAPPLIANCE table, change the desc of Part_Num AT 94 to "Deluxe Iron"


UPDATE NONAPPLIANCE SET DESCRIPTION = "DELUXE IRON" WHERE PART_NUM = "AT94";


#4.  In NONAPPLIANCE, increase price 2% (price * 1.02)

UPDATE NONAPPLIANCE SET PRICE = PRICE * 1.02 WHERE CLASS = "SG";


#5.  Add part (row) to NONAPPLIANCE: Part_Num is TL92, Desc is Trimmer, On_hand is 11,

Class is HW, Price is 29.95


INSERT INTO NONAPPLIANCE
VALUES ("TL92", "TRIMMER", "11", "HW", "29.95");


#6.  Delete rows were class = SG


DELETE FROM NONAPPLIANCE WHERE CLASS = "SG";


#7.  In NONAPPLIANCE, change class for Part FD21 to Null.

UPDATE NONAPPLIANCE SET CLASS = NULL WHERE PART_NUM = "FD21";


#8.  Add column to Nonappliance named ON_HAND_VALUE with a 7-digit, two decimal place and

set all values to ON_HAND_VALUE is ON_HAND * PRICE.

ALTER TABLE NONAPPLIANCE ADD ON_HAND_VALUE DEC (7,2);


UPDATE NONAPPLIANCE SET ON_HAND_VALUE = ON_HAND * PRICE;


#9.  Increase the length of the DESCRIPTION column to 30 characters.


ALTER TABLE NONAPPLIANCE MODIFY DESCRIPTION CHAR(30);


#10.  Remove (drop) the NONAPPLIANCE table

Drop table NONAPPLIANCE;


#2A.  DISPLAY TABLE AND SORT BY PRICE IN ASCENDING ORDER:

SELECT *
FROM NONAPPLIANCE
GROUP BY PRICE
LIMIT 0 , 30


PART_NUM  DESCRIPTION  ON_HAND  CLASS  PRICE  ON_HAND_VALUE
      AT94 DELUXE IRON 50 HW 24.95 1247.50
      TL92 TRIMMER 11 HW 29.95 329.45
      DL71 Cordless Drill 21 HW 129.95 2728.95
      FD21 Stand Mixer 22 NULL 159.95 3518.90

#5A.  DISPLAY TABLE AFTER TRIMMER (#5)

SELECT *
FROM NONAPPLIANCE
LIMIT 0 , 30


PART_NUM  DESCRIPTION  ON_HAND  CLASS  PRICE  ON_HAND_VALUE
      AT94 DELUXE IRON 50 HW 24.95 1247.50
      DL71 Cordless Drill 21 HW 129.95 2728.95
      FD21 Stand Mixer 22 NULL 159.95 3518.90
      TL92 TRIMMER 11 HW 29.95 329.45

#7A.  AFTER QUESTION #7

Field Type Collation Attributes Null Default Extra Action
  PART_NUM char(4) latin1_swedish_ci  No None
  DESCRIPTION char(30) latin1_swedish_ci  Yes NULL
  ON_HAND decimal(4,0)   Yes NULL
  CLASS char(2) latin1_swedish_ci  Yes NULL
  PRICE decimal(6,2)   Yes NULL
  ON_HAND_VALUE decimal(7,2)   Yes NULL


8B.  Field Type Collation Attributes Null Default Extra Action
  PART_NUM char(4) latin1_swedish_ci  No None
  DESCRIPTION char(30) latin1_swedish_ci  Yes NULL
  ON_HAND decimal(4,0)   Yes NULL
  CLASS char(2) latin1_swedish_ci  Yes NULL
  PRICE decimal(6,2)   Yes NULL
  ON_HAND_VALUE decimal(7,2)   Yes NULL

9A.

SELECT *
FROM `NONAPPLIANCE`
LIMIT 0 , 30


PART_NUM  DESCRIPTION  ON_HAND  CLASS  PRICE  ON_HAND_VALUE
      AT94 DELUXE IRON 50 HW 24.95 1247.50
      DL71 Cordless Drill 21 HW 129.95 2728.95
      FD21 Stand Mixer 22 NULL 159.95 3518.90
      TL92 TRIMMER 11 HW 29.95 329.45