Could youplease help me on this MySQL questions. Appricite your help Using multi
ID: 3737664 • 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).
1. For each order, list the order number and order date along with the number and name of the customer that placed the order.
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.
3. For each order, list the order number, order date, part number, number of units ordered, and quoted price for each order line that makes up the order.
4. For each order, list the order number, order date, part number, part description, and item class for each part that makes up the order.
5. Repeat step 4, but this time order the rows by item class and then by order number.
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
1. SELECT O.ORDER_NUM,O.ORDER_DATE,C.CUSTOMER_NUM,C.CUSTOMER_NAME FROM ORDERS O INNER JOIN CUSTOMER C ON O.CUSTOMER_NUM=C.CUSTOMER_NUM;
2. SELECT O.ORDER_NUM,O.ORDER_DATE,C.CUSTOMER_NUM,C.CUSTOMER_NAME FROM ORDERS O INNER JOIN CUSTOMER C ON O.CUSTOMER_NUM=C.CUSTOMER_NUM AND O.ORDER_DATE=TO_DATE('2007-10-23','YYYY-MM-DD');
3. SELECT O.ORDER_NUM,O.ORDER_DATE,P.PART_NUM,L.NUM_ORDERED ,L.QUOTED_PRICE FROM ORDER_LINE L INNER JOIN ORDERS O ON O.ORDER_NUM=L.ORDER_NUM INNER JOIN PART P ON P.PART_NUM=L.PART_NUM;
4. SELECT O.ORDER_NUM,O.ORDER_DATE,P.PART_NUM,P.DESCRIPTION,P.CLASS FROM ORDER_LINE L INNER JOIN ORDERS O ON O.ORDER_NUM=L.ORDER_NUM INNER JOIN PART P ON P.PART_NUM=L.PART_NUM;
5. SELECT O.ORDER_NUM,O.ORDER_DATE,P.PART_NUM,P.DESCRIPTION,P.CLASS FROM ORDER_LINE L INNER JOIN ORDERS O ON O.ORDER_NUM=L.ORDER_NUM INNER JOIN PART P ON P.PART_NUM=L.PART_NUM ORDER BY P.CLASS,O.ORDER_NUM;