Lab 08: Advanced SQL Queries & Database Management | Môn Principle of database management - Trường Đại học Quốc tế, Đại học Quốc gia Thành phố Hồ Chí Minh

lOMoAR cPSD| 58097008
PDM LAB 08 Advance SQL Queries
Figure 1. Sales Orders Modify Database
Import Database by using SQL files provided on Backboard.
Write SQL statements for each requirement. Capture your output for each answer:
1. “Show me all the orders shipped on October 3, 2017, and each order’s related customer last name.” lOMoAR cPSD| 58097008
2. “List all the customer names and a count of the orders they placed.”
3. “List customers and all the details from their last order.”
4. “Find all accessories that are priced greater than any clothing item. (hint use ALL)
5. “Find all the customers who ordered a bicycle.” (Use EXISTS)
6. “List customers who ordered bikes.” (use IN) ‘ lOMoAR cPSD| 58097008
7. “Display customers who ordered clothing or accessories.” (= SOME)
8. “Find all the customers who ordered a bicycle helmet.” (IN)
9. “What products have never been ordered?” (NOT IN)
10. “List vendors and a count of the products they sell to us.”
11. “Display customers who ordered clothing or accessories.
12. “Display products and the latest date each product was ordered.” (Hint: Use the MAX
aggregate function. (40 rows).
13. “Calculate a total of all unique wholesale costs for the products we sell.” (use SUM) lOMoAR cPSD| 58097008
14. “What is the average item total for order 64?”
15. “Calculate an average of all unique product prices.”
16. “What is the lowest price we charge for a product?”
17. “How many different products were ordered on order number 553, and what was the total
cost of that order?” (use SUM and COUNT)
18. “List the product names and numbers that have a quoted price greater than or equal to the
overall average retail price in the products table.”
19. “What is the average retail price of a mountain bike?
20. What was the date of our most recent order?” lOMoAR cPSD| 58097008
21. “What was the total amount for order number 8?”
22. “Show me each vendor and the average by vendor of the number of days to deliver
products.” (Hint: Use the AVG aggregate function and group on vendor.)
23. “My clothing supplier just announced a price increase of 4 percent. Update the price of
the clothing products and add 4 percent.” (use UPDATE)
24. “Modify products by increasing the retail price by 4 percent for products that are clothing.” (use UPDATE)
25. “Change the orders table by setting the order total to the sum of quantity ordered times
quoted price for all related order detail rows.” (use UPDATE with subquery)
26. “Reduce the quoted price by 2 percent for orders shipped more than 30 days after the order
date.” (use UPDATE with subquery) lOMoAR cPSD| 58097008 lOMoAR cPSD| 58097008
27. * “Make sure the retail price for all bikes is at least a 45 percent markup over the wholesale
price of the vendor with the lowest cost.” (update and subsequent) - 1 row
28. “Apply a 5 percent discount to all orders for customers who purchased more than $50,000
in the month of October 2017. (hint You need a subquery within a subquery to fetch the
order numbers for all orders where the customer ID of the order is in the set of customers
who ordered more than $50,000 in the month of October.) (639 rows changed).
29. “Set the retail price of accessories (category = 1) to the wholesale price of the highest-
priced vendor plus 35 percent.” 11 rows changed). lOMoAR cPSD| 58097008
30. “Copy to the Employees table the relevant columns in the Customers table for customer David Smith. (INSERT INTO)
31. “Add a new product named ‘Hot Dog Spinner’ with a retail price of $895 in the Bikes category. (INSERT INTO) ---The end---