How do I write these Queries SQL 11 Find the number and name of each of each cus
ID: 3675823 • Letter: H
Question
How do I write these Queries SQL
11 Find the number and name of each of each customer that currently has an order on file for a Rocking Horse
12 List the item number, description and category for each pair of items that are in the same category.
14. List the order number and order date for each order that contains an order line for a Fire Engine
15. List the order number and order date for each order that was placed by Almondton General Store or that contains an order line for a Fire Engine
16. List the order number and order date for each order that was placed by Almondton General Store and that contains an order line for a Fire Engine
17. List the order number and order date for each order that was placed by Almonditon General Store but that does not contain an order line for a Fire Engine.
18. List the Item number, description, unit price, and category for each item that has a unit price greater than the unit price of every item in category GME. Use either the ALL or ANY operator in your query.
19. For each item, list the item number, description, units on hand, order number, and number of units ordered. All items should be included in the results. For those items that are curently not on order, the order number and number of units ordered should be left blank. Order the results by item number.
20. If you used ALL in number 18, repeate the exercise using ANY. If you used ANY, repeat the exercise using ALL, and then run the new command.
21. For each rep, list the customer number, customer name, rep last name, and rep first name. All reps should be included in the results. Order the results by rep number.
Explanation / Answer
11 Find the number and name of each of each customer that currently has an order on file for a Rocking Horse
SELECT CUSTOMER.CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTOMER_NUM= ORDERS.CUSTOMER_NUM
AND ORDER_NUM IN (SELECT ORDER_NUM FROM ORDER_LINE
WHERE ITEM_NUM IN(SELECT ITEM_NUM FROM ITEM
WHERE DESCRIPTION= "Rocking Horse"))
14. List the order number and order date for each order that contains an order line for a Fire Engine
SELECT ORDER_NUM, ORDER_DATE FROM ORDERS
WHERE ORDER_NUM IN (SELECT ORDER_NUM FROM ORDER_LINE
WHERE ITEM_NUM IN (SELECT ITEM_NUM FROM ITEM
WHERE DESCRIPTION= "Fire Engine"))
15. List the order number and order date for each order that was placed by Almondton General Store or that contains an order line for a Fire Engine
SELECT ORDER_NUM, ORDER_DATE FROM ORDERS
WHERE ORDER_NUM IN (SELECT ORDER_NUM FROM ORDER_LINE
WHERE ITEM_NUM IN (SELECT ITEM_NUM FROM ITEM
WHERE DESCRIPTION= "Fire Engine" OR
STOREHOUSE= "Almondton General Store")
16. List the order number and order date for each order that was placed by Almondton General Store and that contains an order line for a Fire Engine
SELECT ORDER_NUM, ORDER_DATE FROM ORDERS
WHERE ORDER_NUM IN (SELECT ORDER_NUM FROM ORDER_LINE
WHERE ITEM_NUM IN (SELECT ITEM_NUM FROM ITEM
WHERE DESCRIPTION= "Fire Engine" AND
STOREHOUSE= "Almondton General Store")
17. List the order number and order date for each order that was placed by Almonditon General Store but that does not contain an order line for a Fire Engine.
SELECT ORDER_NUM, ORDER_DATE FROM ORDERS
WHERE ORDER_NUM IN (SELECT ORDER_NUM FROM ORDER_LINE
WHERE ITEM_NUM IN (SELECT ITEM_NUM FROM ITEM
WHERE STOREHOUSE= "Almondton General Store"
AND DESCRIPTION NOT LIKE= "Fire Engine")
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.