Paula Noone SQL - SUMMER, 2009 P157 - 20 QUESTIONS #1. For @ order, list order num, date, with # and name of customer that placed order. SELECT ORDER_NUM, ORDER_DATE, Customer.CUSTOMER_NUM, CUSTOMER_NAME FROM Customer, Orders WHERE Customer.CUSTOMER_NUM = Orders.CUSTOMER_NUM LIMIT 0 , 30 ORDER_NUM ORDER_DATE CUSTOMER_NUM CUSTOMER_NAME 21608 2007-10-20 148 Al's Appliance and Sport 21614 2007-10-21 282 Brooking's Direct 21610 2007-10-20 356 Ferguson's 21613 2007-10-21 408 The Everything Shop 21617 2007-10-23 608 Johnson's Dept. Store 21619 2007-10-23 148 Al's Appliance and Sport 21623 2007-10-23 608 Johnson's Dept. Store #2. For @ order placed on 10/23/07 list order #, w/ order # & customer name that placed order. SELECT ORDER_NUM, Customer.CUSTOMER_NUM, CUSTOMER_NAME FROM Customer, Orders WHERE Customer.CUSTOMER_NUM = Orders.CUSTOMER_NUM AND ORDER_DATE = "2007-10-23" LIMIT 0 , 30 ORDER_NUM CUSTOMER_NUM CUSTOMER_NAME 21617 608 Johnson's Dept. Store 21619 148 Al's Appliance and Sport 21623 608 Johnson's Dept. Store #3. For @ order, list order #, date, part #, number of units ordered and quoted price for each order line that makes up the order. SELECT Orders.ORDER_NUM, ORDER_DATE, PART_NUM, NUM_ORDERED, QUOTED_PRICE FROM Order_line, Orders, Customer WHERE Customer.CUSTOMER_NUM = Orders.CUSTOMER_NUM AND Orders.ORDER_NUM = Order_line.ORDER_NUM LIMIT 0 , 30 ORDER_NUM ORDER_DATE PART_NUM NUM_ORDERED QUOTED_PRICE 21608 2007-10-20 AT94 11 21.95 21610 2007-10-20 DR93 1 495.00 21610 2007-10-20 DW11 1 399.00 21613 2007-10-21 KL62 4 329.95 21614 2007-10-21 KT03 2 595.00 21617 2007-10-23 BV06 2 794.95 21617 2007-10-23 CD52 4 150.00 21619 2007-10-23 DR93 1 495.00 21623 2007-10-23 KV29 2 1290.00 #4. Use the IN operator to find the # and name of each customer that placed a order on 10/21/07. SELECT Orders.CUSTOMER_NUM, CUSTOMER_NAME, ORDER_NUM, ORDER_DATE FROM Customer, Orders WHERE ORDER_NUM IN ( SELECT ORDER_NUM FROM Orders WHERE ORDER_DATE = "2007-10-21" ) AND Customer.CUSTOMER_NUM = Orders.CUSTOMER_NUM LIMIT 0 , 30 CUSTOMER_NUM CUSTOMER_NAME ORDER_NUM ORDER_DATE 282 Brooking's Direct 21614 2007-10-21 408 The Everything Shop 21613 2007-10-21 #5. Repeat exercise 4, use the exist operator instead. SELECT Customer.CUSTOMER_NUM, CUSTOMER_NAME, ORDER_NUM, ORDER_DATE FROM Customer, Orders WHERE EXISTS ( SELECT ORDER_NUM FROM Orders WHERE ORDER_DATE = "2007-10-21" ) AND Customer.CUSTOMER_NUM = Orders.CUSTOMER_NUM LIMIT 0 , 30 CUSTOMER_NUM CUSTOMER_NAME ORDER_NUM ORDER_DATE 148 Al's Appliance and Sport 21608 2007-10-20 282 Brooking's Direct 21614 2007-10-21 356 Ferguson's 21610 2007-10-20 408 The Everything Shop 21613 2007-10-21 608 Johnson's Dept. Store 21617 2007-10- #6. Find the # and name of each customer that did not place an order on 10/21/07. SELECT CUSTOMER_NAME, Orders.CUSTOMER_NUM, ORDER_DATE FROM Customer, Orders WHERE Customer.CUSTOMER_NUM = Orders.CUSTOMER_NUM AND ORDER_DATE <> "2007-10-21" ORDER BY `Orders`.`ORDER_DATE` ASC LIMIT 0 , 30 CUSTOMER_NAME CUSTOMER_NUM ORDER_DATE Al's Appliance and Sport 148 2007-10-20 Ferguson's 356 2007-10-20 Johnson's Dept. Store 608 2007-10-23 Al's Appliance and Sport 148 2007-10-23 Johnson's Dept. Store 608 2007-10-23 #7. For each order, list the order #, date, part #, part desc, and item class for @ part that makes up the order. SELECT ORDER_NUM, ORDER_DATE, PART_NUM, DESCRIPTION, CLASS FROM Orders, Part LIMIT 0 , 30 ORDER_NUM ORDER_DATE PART_NUM DESCRIPTION CLASS 21608 2007-10-20 AT94 Iron HW 21614 2007-10-21 AT94 Iron HW 21610 2007-10-20 AT94 Iron HW 21613 2007-10-21 AT94 Iron HW 21617 2007-10-23 AT94 Iron HW 21619 2007-10-23 AT94 Iron HW 21623 2007-10-23 AT94 Iron HW 21608 2007-10-20 BV06 Home Gym SG 21614 2007-10-21 BV06 Home Gym SG 21610 2007-10-20 BV06 Home Gym SG 21613 2007-10-21 BV06 Home Gym SG 21617 2007-10-23 BV06 Home Gym SG 21619 2007-10-23 BV06 Home Gym SG 21623 2007-10-23 BV06 Home Gym SG 21608 2007-10-20 CD52 Microwave Oven AP 21614 2007-10-21 CD52 Microwave Oven AP 21610 2007-10-20 CD52 Microwave Oven AP 21613 2007-10-21 CD52 Microwave Oven AP 21617 2007-10-23 CD52 Microwave Oven AP 21619 2007-10-23 CD52 Microwave Oven AP 21623 2007-10-23 CD52 Microwave Oven AP 21608 2007-10-20 DL71 Cordless Drill HW 21614 2007-10-21 DL71 Cordless Drill HW 21610 2007-10-20 DL71 Cordless Drill HW 21613 2007-10-21 DL71 Cordless Drill HW 21617 2007-10-23 DL71 Cordless Drill HW 21619 2007-10-23 DL71 Cordless Drill HW 21623 2007-10-23 DL71 Cordless Drill HW 21608 2007-10-20 DR93 Gas Range AP 21614 2007-10-21 DR93 Gas Range AP #8. Repeat Ex. 7, order the rows by item class and then by order #. SELECT ORDER_NUM, ORDER_DATE, PART_NUM, DESCRIPTION, CLASS FROM Orders, Part ORDER BY CLASS, ORDER_NUM LIMIT 30 , 70 ORDER_NUM ORDER_DATE PART_NUM DESCRIPTION CLASS 21608 2007-10-20 AT94 Iron HW 21610 2007-10-20 AT94 Iron HW 21610 2007-10-20 FD21 Stand Mixer HW 21610 2007-10-20 DL71 Cordless Drill HW 21613 2007-10-21 AT94 Iron HW 21613 2007-10-21 FD21 Stand Mixer HW 21613 2007-10-21 DL71 Cordless Drill HW 21614 2007-10-21 AT94 Iron HW 21614 2007-10-21 FD21 Stand Mixer HW 21614 2007-10-21 DL71 Cordless Drill HW 21617 2007-10-23 DL71 Cordless Drill HW 21617 2007-10-23 AT94 Iron HW 21617 2007-10-23 FD21 Stand Mixer HW 21619 2007-10-23 DL71 Cordless Drill HW 21619 2007-10-23 AT94 Iron HW 21619 2007-10-23 FD21 Stand Mixer HW 21623 2007-10-23 DL71 Cordless Drill HW 21623 2007-10-23 AT94 Iron HW 21623 2007-10-23 FD21 Stand Mixer HW 21608 2007-10-20 BV06 Home Gym SG 21608 2007-10-20 KV29 Treadmill SG 21610 2007-10-20 BV06 Home Gym SG 21610 2007-10-20 KV29 Treadmill SG 21613 2007-10-21 BV06 Home Gym SG 21613 2007-10-21 KV29 Treadmill SG 21614 2007-10-21 KV29 Treadmill SG 21614 2007-10-21 BV06 Home Gym SG 21617 2007-10-23 BV06 Home Gym SG 21617 2007-10-23 KV29 Treadmill SG 21619 2007-10-23 BV06 Home Gym SG 21619 2007-10-23 KV29 Treadmill SG 21623 2007-10-23 BV06 Home Gym SG 21623 2007-10-23 KV29 Treadmill SG #9. Use a subquery to find the rep #, last name, first name of each sales rep who represents at least one customer with a credit limit of >= $5k. List @ rep only once in the results. SELECT DISTINCT ( LAST_NAME ), FIRST_NAME, Rep.REP_NUM FROM Rep, Customer WHERE Rep.REP_NUM IN ( SELECT Rep.REP_NUM FROM Rep, Customer WHERE Rep.REP_NUM = Customer.REP_NUM ) AND CREDIT_LIMIT >= 5000 LIMIT 0 , 30 LAST_NAME FIRST_NAME REP_NUM Kaiser Valerie 20 Hull Richard 35 Perez Juan 65 #10. Repeat Ex 9, do not use a subquery. SELECT DISTINCT ( LAST_NAME ), FIRST_NAME, Rep.REP_NUM FROM Rep, Customer WHERE Rep.REP_NUM = Customer.REP_NUM AND CREDIT_LIMIT >5000 ORDER BY `Rep`.`REP_NUM` ASC LIMIT 0 , 30 LAST_NAME FIRST_NAME REP_NUM Kaiser Valerie 20 Hull Richard 35 Perez Juan 65 #11. Find the # and name of each customer that currently has an order on file for a gas range. SELECT CUSTOMER_NUM, CUSTOMER_NAME, DESCRIPTION FROM Customer, Part WHERE Description = "Gas Range" LIMIT 0 , 30 CUSTOMER_NUM CUSTOMER_NAME DESCRIPTION 148 Al's Appliance and Sport Gas Range 282 Brooking's Direct Gas Range 356 Ferguson's Gas Range 408 The Everything Shop Gas Range 462 Bargain's Galore Gas Range 524 Kline's Gas Range 608 Johnson's Dept. Store Gas Range 687 Lee's Sport and Appliance Gas Range 725 Deerfield's Four Seasons Gas Range 842 All Season Gas Range #12. List the part #, desc, and class for @ pair of parts that are in the same class (e.g. on such pair would be AT94 and FD21 because they are both in class HW). SELECT P.PART_NUM, P.DESCRIPTION, P.CLASS FROM Part P ORDER BY P.CLASS LIMIT 0 , 30 Show BLOBHide Browser transformation PART_NUM DESCRIPTION CLASS CD52 Microwave Oven AP DR93 Gas Range AP DW11 Washer AP KL62 Dryer AP AT94 Iron HW DL71 Cordless Drill HW FD21 Stand Mixer HW BV06 Home Gym SG KV29 Treadmill SG #13. List the order #, date for each order placed by cust "Johnson's Dept. Store." SELECT ORDER_NUM, ORDER_DATE, CUSTOMER_NAME, Orders.CUSTOMER_NUM FROM Orders, Customer WHERE CUSTOMER_NAME = "Johnson's Dept. Store" AND Orders.CUSTOMER_NUM = Customer.CUSTOMER_NUM LIMIT 0 , 30 ORDER_NUM ORDER_DATE CUSTOMER_NAME CUSTOMER_NUM 21617 2007-10-23 Johnson's Dept. Store 608 21623 2007-10-23 Johnson's Dept. Store 608 #14. List the order #, date for each order that contains an order line for an iron. SELECT Orders.ORDER_NUM, ORDER_DATE, Description FROM Orders, Order_line, Part WHERE Orders.ORDER_NUM = Order_line.ORDER_NUM AND DESCRIPTION = "Iron" LIMIT 0 , 30 ORDER_NUM ORDER_DATE Description 21608 2007-10-20 Iron 21614 2007-10-21 Iron 21610 2007-10-20 Iron 21610 2007-10-20 Iron 21613 2007-10-21 Iron 21617 2007-10-23 Iron 21617 2007-10-23 Iron 21619 2007-10-23 Iron 21623 2007-10-23 Iron #15. List the part num, desc, price, item class for @ part that has a price > than the price of every part in class AP. Use either the all or any. SELECT PART_NUM, DESCRIPTION, PRICE, CLASS FROM Part WHERE PRICE > ALL ( SELECT PRICE FROM Part WHERE CLASS = "AP" ) LIMIT 0 , 30 PART_NUM DESCRIPTION PRICE CLASS BV06 Home Gym 794.95 SG KV29 Treadmill 1390.00 SG #16. Same as 18, use all if you used any, vice versa. (Results show any class, including AP) SELECT PART_NUM, DESCRIPTION, PRICE, CLASS FROM Part WHERE PRICE > ANY( SELECT PRICE FROM Part WHERE CLASS = "AP" ) LIMIT 0 , 30 PART_NUM DESCRIPTION PRICE CLASS BV06 Home Gym 794.95 SG DR93 Gas Range 495.00 AP DW11 Washer 399.99 AP KL62 Dryer 349.95 AP KV29 Treadmill 1390.00 SG #17. For @ part, list the #, desc, On hand, order num, num_ordered. All parts should be included even those with no orders (i.e. null answer). SELECT Part.PART_NUM, DESCRIPTION, ON_HAND, ORDER_NUM, NUM_ORDERED FROM Part LEFT JOIN Order_line ON Part.PART_NUM = Order_line.PART_NUM ORDER BY PART_NUM LIMIT 0 , 30 PART_NUM DESCRIPTION ON_HAND ORDER_NUM NUM_ORDERED AT94 Iron 50 21608 11 BV06 Home Gym 45 21617 2 CD52 Microwave Oven 32 21617 4 DL71 Cordless Drill 21 NULL NULL DR93 Gas Range 8 21610 1 DR93 Gas Range 8 21619 1 DW11 Washer 12 21610 1 FD21 Stand Mixer 22 NULL NULL KL62 Dryer 12 21613 4 KV29 Treadmill 9 21623 2