Paula Noone
SQL - SUMMER, 2009
P 125 ASSIGNMENT


#1.  List part #, desc, and Price for all Parts

SELECT Part_num, Description, Price
FROM Part
LIMIT 0 , 30


Part_num  Description  Price

      AT94 Iron 24.95
      BV06 Home Gym 794.95
      CD52 Microwave Oven 165.00
      DL71 Cordless Drill 129.95
      DR93 Gas Range 495.00
      DW11 Washer 399.99
      FD21 Stand Mixer 159.95
      KL62 Dryer 349.95
      KV29 Treadmill 1390.00


#2.  List all rows & columns for the Orders table

SHOW COLUMNS FROM Orders


Field  Type  Null  Key  Default  Extra

ORDER_NUM char(5) NO PRI NULL
ORDER_DATE date NO   NULL
CUSTOMER_NUM char(3) NO   NULL


#3.  List cust names with credit limit =< $7500

SELECT Customer_name
FROM Customer
WHERE Credit_limit <=7500
LIMIT 0 , 30


  Customer_name
      Al's Appliance and Sport
      Ferguson's
      The Everything Shop
      Lee's Sport and Appliance
      Deerfield's Four Seasons
      All Season


#4.  List order # for @ order by cust #148 on 10/20/07

SELECT Order_num
FROM Orders
WHERE Customer_num =148
AND 2007 -10 -20
LIMIT 0 , 30

 Order_num
      21608
      21619


#5.  List # and cust name represented by rep 35 or 65

SELECT Customer_name, Customer_num
FROM Customer
WHERE Rep_num = '35'
OR Rep_num = '65'
LIMIT 0 , 30


Customer_name  Customer_num
      Brooking's Direct 282
      Ferguson's 356
      The Everything Shop 408
      Bargain's Galore 462
      Johnson's Dept. Store 608
      Lee's Sport and Appliance 687
      Deerfield's Four Seasons 725


#6.  List part # and desc of each part that is not in class SG

SELECT Part_num, Description
FROM Part
WHERE class <> 'SG'
LIMIT 0 , 30


  Part_num  Description
      AT94 Iron
      CD52 Microwave Oven
      DL71 Cordless Drill
      DR93 Gas Range
      DW11 Washer
      FD21 Stand Mixer
      KL62 Dryer


#7.  List part #, desc, and # of units on hand for each part that has between 10-25 units,

incl both 10 and 25.  Do two ways.

SELECT Part_num, Description, On_hand
FROM Part
WHERE (
On_hand >=10
)
AND (
On_hand <=25
)
LIMIT 0 , 30


Part_num  Description  On_hand
      DL71 Cordless Drill 21
      DW11 Washer 12
      FD21 Stand Mixer 22
      KL62 Dryer 12


#8.  List part #, desc, and on-hand value (units on hand*price) of each part in item class AP.  (On-hand value is really units on hand times cost, but there is no cost.)  Assign the name On_Hand_Value in computation.

SELECT Part_num, Description, (
On_hand * Price
) AS On_hand_value
FROM Part
WHERE class = 'AP'
LIMIT 0 , 30


Part_num  Description  On_hand_value
CD52 Microwave Oven 5280.00
DR93 Gas Range 3960.00
DW11 Washer 4799.88
KL62 Dryer 4199.40


#9.  List part #, desc, and on-hand value for each part at least $7500.  Assign the name On_Hand_Value to that column.

SELECT Part_num, Description, (
On_hand * Price <=7500
) AS On_hand_value
FROM Part
LIMIT 0 , 30


Part_num  Description  On_hand_value

AT94 Iron 1
BV06 Home Gym 0
CD52 Microwave Oven 1
DL71 Cordless Drill 1
DR93 Gas Range 1
DW11 Washer 1
FD21 Stand Mixer 1
KL62 Dryer 1
KV29 Treadmill 0


#10.  Use the In operator to list the Part # and desc of each part in item class AP or SG.

SELECT Part_num, Description
FROM Part
WHERE Class
IN (
'AP', 'SG'
)
LIMIT 0 , 30


Part_num  Description
      BV06 Home Gym
      CD52 Microwave Oven
      DR93 Gas Range
      DW11 Washer
      KL62 Dryer
      KV29 Treadmill


#11.  Find the # and name of @ customer whose name begins with the letter 'K'.

SELECT Customer_name
FROM Customer
WHERE Customer_name LIKE 'K%'
LIMIT 0 , 30

Customer_name
      Kline's


#12.  List all details about all parts.  Order the output by part desc.

SELECT *
FROM Part
ORDER
BY Description
LIMIT 0 , 30


PART_NUM  DESCRIPTION  ON_HAND  CLASS  WAREHOUSE  PRICE
      DL71 Cordless Drill 21 HW 3 129.95
      KL62 Dryer 12 AP 1 349.95
      DR93 Gas Range 8 AP 2 495.00
      BV06 Home Gym 45 SG Z 794.95
      AT94 Iron 50 HW 3 24.95
      CD52 Microwave Oven 32 AP 1 165.00
      FD21 Stand Mixer 22 HW 3 159.95
      KV29 Treadmill 9 SG 2 1390.00
      DW11 Washer 12 AP 3 399.99


#13.  List all details about all parts.  Order the output by part # within item class.  (That is, order the output by item class and then by part #.)

SELECT *
FROM Part
ORDER BY Class, Part_num
LIMIT 0 , 30


 PART_NUM  DESCRIPTION  ON_HAND  CLASS  WAREHOUSE  PRICE

      CD52 Microwave Oven 32 AP 1 165.00
      DR93 Gas Range 8 AP 2 495.00
      DW11 Washer 12 AP 3 399.99
      KL62 Dryer 12 AP 1 349.95
      AT94 Iron 50 HW 3 24.95
      DL71 Cordless Drill 21 HW 3 129.95
      FD21 Stand Mixer 22 HW 3 159.95
      BV06 Home Gym 45 SG Z 794.95
      KV29 Treadmill 9 SG 2 1390.00


#14.  How many cust have balances that are more than their credit limits?

SELECT count( * )
FROM Customer
WHERE Balance > Credit_limit

count(*)

2


#15.  Find the total of the bal for all cust rep by sales rep 65 with bal that are less than their credit limits.

SELECT count( * ) , sum( Balance )
FROM Customer
WHERE Balance < Credit_limit
AND Rep_num = '65'


count(*)  sum(Balance)

3 11303.00


#16.  List the part #, part desc, and on-hand value of @ part whose # of units on hand is more than the avg # of units on hand for all parts.  (Use a subquery.)

SELECT Part_num, Description
FROM Part
WHERE On_hand > (
SELECT avg( On_hand )
FROM Part )


Part_num  Description

      AT94 Iron
      BV06 Home Gym
      CD52 Microwave Oven


#17.  What is the price of the most expensive part in the database?

SELECT max( Price )
FROM Part


max(Price)
1390.00


#18.  What is the part #, desc, and price of the most expensive part in the database? (Use a subquery.)

SELECT Part_num, Description
FROM Part
WHERE Price = (
SELECT max( Price )
FROM Part )


  Part_num  Description

      KV29 Treadmill


#19.  List the sum of the bal of all cust for @ sales rep.  Order and group the results by sales rep #.

SELECT Rep_num, sum( Balance )
FROM Customer
GROUP BY Rep_num
ORDER BY Rep_num
LIMIT 0 , 30


Rep_num  sum(Balance)

20 27533.00
35 8815.50
65 11303.00


#20.  List the sum of the bal of all cust for each sales rep but restrict the output those sales reps for which the sum is > $10k.

SELECT Rep_num, Balance
FROM Customer
WHERE Balance >10000
GROUP BY Rep_num
ORDER BY Rep_num
LIMIT 0 , 30


Rep_num  Balance
      20 12762.00


#21.  List the part # of any part with an unknown desc.

MySQL returned an empty result set
(i.e. zero rows). ( Query took 0.0004 sec )
SELECT Part_num
FROM Part
WHERE Description IS NULL ;

LIMIT 0 , 30