SQL problems. Please help Use Priemiere Products database to complete the follow
ID: 3567883 • Letter: S
Question
SQL problems. Please help
Use Priemiere Products database to complete the following exercise.
9. Use a subquery to find the rep number, last name, and first name of each sales rep who represents
at least one customer with a credit limit of $10,000. List each sales rep only once
in the results.
10. Repeat Exercise 9, but this time do not use a subquery.
11. Find the number and name of each customer that currently has an order on file for a
Gas Range.
12. List the part number, part description, and item class for each pair of parts that are in the
same item class. (For example, one such pair would be part AT94 and part FD21, because
the item class for both parts is HW.)
13. List the order number and order date for each order placed by the customer named
Johnson
Explanation / Answer
9. SELECT rep.rep_num,rep.last_name,rep.first_name FROM rep WHERE rep.rep_num IN (SELECT rep_num FROM customer WHERE credit_limit>10000);
10. SELECT rep.rep_num,rep.last_name,rep.first_name FROM rep,customer WHERE rep.rep_num=customer.rep_num AND customer.credit_limit>10000;
11. SELECT customer.customer_num,customer.customer_name FROM customer,orders,order_line,part WHERE customer.customer_num=orders.customer_num AND orders.order_num=order_line.order_num AND order_line.part_num=part.part_num AND part.description='Gas Range';
12. SELECT DISTINCT A.part_num,A.description,A.class FROM part AS A,part AS B WHERE A.class=B.class;
13. SELECT orders.order_num,orders.order_date FROM customer,orders WHERE customer.customer_num=orders.customer_num AND customer.customer_name='Johnson's Department Store';
14. SELECT orders.order_num,orders.order_date FROM orders,order_line,part WHERE order_line.order_num=orders.order_num AND order_line.part_num=part.part_num AND part.description='Iron';
15. SELECT orders.order_num,orders.order_date FROM customer,orders,order_line,part WHERE customer.customer_num=orders.customer_num AND order_line.order_num=orders.order_num AND order_line.part_num=part.part_num AND (part.description='Gas Range' OR customer.customer_name='Johnson's Department Store');
16. SELECT orders.order_num,orders.order_date FROM customer,orders,order_line,part WHERE customer.customer_num=orders.customer_num AND order_line.order_num=orders.order_num AND order_line.part_num=part.part_num AND (part.description='Gas Range' AND customer.customer_name='Johnson's Department Store');
17. SELECT orders.order_num,orders.order_date FROM customer,orders,order_line,part WHERE customer.customer_num=orders.customer_num AND order_line.order_num=orders.order_num AND order_line.part_num=part.part_num AND (part.description<>'Gas Range' AND customer.customer_name='Johnson's Department Store');
18. SELECT part.part_num,part.description,part.price,part.class FROM part WHERE part.price>ALL(SELECT price FROM part WHERE class='AP');
19. SELECT part.part_num,part.description,part.price,part.class FROM part WHERE part.price>ANY(SELECT MAX(price) FROM part WHERE class='AP');
20. SELECT part.part_num,part.description,part.on_hand,order_line.order_num,order_line.num_ordered FROM part LEFT OUTER JOIN order_line ON part.part_num=order_line.part_num ORDER BY part.part_num;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.