Could youplease help me on this MySQL questions. Appricite your help Using multi
ID: 3737797 • Letter: C
Question
Could youplease help me on this MySQL questions. Appricite your help
Using multiple-table queries and joins, complete the following tasks (see database below).
2. For each order placed on October 23, 2007, list the order number along with the number and name of the customer that placed the order.
6. 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.
7. Find the number and name of each customer that currently has an order on file for a Gas Range.
8. 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.)
9. List the order number and order date for each order placed by the customer named Johnson’s Department Store. (Hint: To enter an apostrophe (single quotation mark) within a string of characters, type two single quotation marks.)
10. List the order number and order date for each order that contains an order line for an Iron.
11. For each part, list the part number, description, units on hand, order number, and number of units ordered. All parts should be included in the results. For those parts that are currently not on order, the order number and number of units ordered should be left blank. Order the results by part number
This is the MySQL database
Explanation / Answer
Hi,
Please find the below queries.
2. For each order placed on October 23, 2007, list the order number along with the number and name of the customer that placed the order.
SELECT ORD.ORDER_NUM, CR.CUSTOMER_NUM, CR.CUSTOMER_NAME FROM ORDERS ORD, CUSTOMER CR WHERE CR.CUSTOMER_NUM = ORD.CUSTOMER_NUM
AND ORD.ORDER_DATE = '2007-10-23';
6. 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.
SELECT DISTINCT RP.REP_NUM, RP.LAST_NAME, RP.FIRST_NAME
FROM REP RP, CUSTOMER CR WHERE RP.REP_NUM = CR.REP_NUM AND CR.CREDIT_LIMIT = '10000';
7. Find the number and name of each customer that currently has an order on file for a Gas Range.
SELECT CR.CUSTOMER_NUM, CR.CUSTOMER_NAME FROM CUSTOMER CR, ORDERS ORD, PART PR , ORDER_LINE OL
WHERE CR.CUSTOMER_NUM = ORD.CUSTOMER_NUM
AND ORD.ORDER_NUM = OL.ORDER_NUM
AND OL.PART_NUM = PR.PART_NUM
AND PR.DESCRIPTION = 'Gas Range';
9. List the order number and order date for each order placed by the customer named Johnson’s Department Store. (Hint: To enter an apostrophe (single quotation mark) within a string of characters, type two single quotation marks.)
SELECT ORD.ORDER_NUM, ORD.ORDER_DATE FROM CUSTOMER CR, ORDERS ORD WHERE ORD.CUSTOMER_NUM = CR.CUSTOMER_NUM
AND CR.CUSTOMER_NAME = 'Johnson''s Department Store ';
8. 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.)
SELECT PR.PART_NUM, PR.DESCRIPTION, PR.CLASS FROM PART PR , (
SELECT COUNT(*), CLASS CL FROM PART GROUP BY CLASS HAVING COUNT(*) > 1) INN
WHERE PR.CLASS = INN.CL;
10. List the order number and order date for each order that contains an order line for an Iron.
SELECT ORD.ORDER_NUM, ORD.ORDER_DATE FROM ORDERS ORD, ORDER_LINE OL, PART PR
WHERE ORD.ORDER_NUM = OL.ORDER_NUM
AND OL.PART_NUM = PR.PART_NUM
AND PR.DESCRIPTION = 'Iron';
11. For each part, list the part number, description, units on hand, order number, and number of units ordered. All parts should be included in the results. For those parts that are currently not on order, the order number and number of units ordered should be left blank. Order the results by part number
SELECT PR.PART_NUM, PR.DESCRIPTION, PR.ON_HAND, OL.ORDER_NUM, OL.NUM_ORDERED FROM PART PR, ORDER_LINE OL
LEFT JOIN ORDER_LINE AS OL ON PR.PART_NUM = OL.PART_NUM;