Paula Noone
SQL, Summer, 2009
P88 SQL Homework


#1.  Select Part table organize by lower to highest price


SELECT *
FROM Part
ORDER BY price
LIMIT 0 , 30  


  PART_NUM  DESCRIPTION  ON_HAND  CLASS  WAREHOUSE

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

#2.  Select the sum dollar amount multipled by orders from the Order line table

SELECT sum( Num_ordered * Quoted_price )
FROM Order_line  


sum(Num_ordered*Quoted_price)

8910.15

          row(s) starting from record #
in  horizontal horizontal (rotated headers) vertical



#3.  Select the max price from the Part table

SELECT max( price )
FROM Part  


max(price)

1390.00



#4. Join Customer and Rep tables, list Rep's last name, list Customer's name, #balance

credit limit


SELECT r.Last_name, Customer_name, Balance, Credit_limit
FROM Customer AS c, Rep AS r
WHERE c.Rep_num = r.Rep_num

LIMIT 0 , 30  


Last_name  Customer_name  Balance  Credit_limit

Kaiser Al's Appliance and Sport 6550.00 7500.00

Hull Brooking's Direct 431.50 10000.00

Perez Ferguson's 5785.00 7500.00

Hull The Everything Shop 5285.00 5000.00

Perez Bargain's Galore 3412.00 10000.00

Kaiser Kline's 12762.00 15000.00

Perez Johnson's Dept. Store 2106.00 10000.00

Hull Lee's Sport and Appliance 2851.00 5000.00

Hull Deerfield's Four Seasons 248.00 7500.00

Kaiser All Season 8221.00 7500.00



#5.  Join Orders table and Customer table for Order_num and order_date from Orders #table and Customer_num in Customer table with a Rep #65

SELECT Order_num, Order_date
FROM Orders
WHERE Customer_num
IN (

SELECT Customer_num
FROM Customer
WHERE Rep_num =65
)
LIMIT 0 , 30  



  Order_num  Order_date

      21610 2007-10-20
      21617 2007-10-23
      21623 2007-10-23