I need the SQL statements for the following questions: 1. For each order, list t
ID: 3811974 • Letter: I
Question
I need the SQL statements for the following questions:
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 15, 2015, 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, item number, number of units ordered, and quoted price for each order line that makes up the order.
4. Use the IN operator to find the number and name of each customer that placed an order on October 15, 2015.
5. Repeat Exercise 4, but this time use the EXISTS operator in your answer.
6. Find the number and name of each customer that did not place an order on October 15,2015.
7. For each order, list the order number, order date, item number, description, and category for each item that makes up the order.
8. Repeat Exercise 7, but this time order the rows by category and then by order number.
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.
Here are the tables:
E CUSTOMER ITEM ETORDE ORDER LINE E REP POSTAL CODE BALANCE CREDIT LIMIT REP NUM Click to Add $1,210.25 $7,500.00 15 900 $575.00 $10,000.00 30 90092 $2,345.75 $7,500.00 45 90097 $879.25 $7,500.00 30 90098 90085 $345.00 $5,000.00 45 $5,025.75 $5,000.00 15 90104 90125 $3,456.75 $15,000.00 45 $678.90 $7,500.00 30 90092 $4,234.60 $10,000.00 15 90097 $124.75 $7,500.00 45 90098 $2,475.99 $15,000.00 30 90104 90085 $935.75 $7,500.00 15Explanation / Answer
These are big queries to do at once. I am giving you the solution for first 5 queries. Please post the remaining quesries again.
Please give thumbs up, If it is helpful for you. Thankyou!!
1)
SELECT ORDERS.ORDER_NUM, ORDERS.ORDER_DATE, ORDERS.CUSTOMER_NUM, CUSTOMER.CUSTOMER_NAME
FROM ORDERS INNER JOIN CUSTOMER ON ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM;
2)
SELECT ORDERS.ORDER_NUM, ORDERS.ORDER_DATE, ORDERS.CUSTOMER_NUM, CUSTOMER.CUSTOMER_NAME
FROM ORDERS INNER JOIN CUSTOMER ON ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM
WHERE (((ORDERS.ORDER_DATE) = #10/15/2015#));
3)
SELECT ORDERS.ORDER_NUM, ORDERS.ORDER_DATE, ORDERS_LINE.ITEM_NUM, ORDERS_LINE.NUM_ORDERED, ORDERS_LINE.QUOTED_PRICE
FROM ORDERS INNER JOIN ORDERS_LINE ON ORDERS.ORDERS_NUM = ORDERS_LINE.ORDERS_NUM;
4)
SELECT CUSTOMER.CUSTOMER_NUM, CUSTOMER.CUSTOMER_NAME
FROM ORDERS INNER JOIN CUSTOMER ON ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM
WHERE (((ORDERS.ORDER_DATE) In (#10/15/2015#)));
5)
SELECT CUSTOMER.CUSTOMER_NUM, CUSTOMER.CUSTOMER_NAME
FROM ORDERS INNER JOIN CUSTOMER ON ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM
WHERE EXISTS (SELECT ORDERS.ORDER_DATE
FROM ORDERS
WHERE ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM AND ORDERS.ORDER_DATE = #10/15/2015#);