Last updated Nov 17, 2024

Use the Magic Store database for the following queries. Each query is worth 5 points.

6. Write a query to display the total quantity of products ordered by the client with the last name “Martinez”. Display the client’s full name as “Client Name” and the total quantity of products as “Total Products Ordered”.

Expected result:

7. Write a query to display the total amount of revenue that has been generated from the “Snail” product. To do this your query will need to:
   1. Find the total quantity of “Snail” products that have been ordered.
   2. Multiply the total quantity of snail’s purchased by the price of a snail.
   3. Rename this column “Snail Total Revenue”.

Expected result:

8. Write a query to display the prices of the most and least expensive products ordered in January through March of 2021. Name the columns “Cheapest” and “Most Expensive”.

Expected result:

Use the Northwind database for the following queries. Each query is worth 5 points.

9. The procurement team wants to know the number of suppliers from each country. They need a report showing the country and the corresponding supplier count.

Expected results:

10. The inventory manager wants to know the number of products supplied by each supplier for products with a unit price greater than 30.

Expected results:

11. The procurement team wants to analyze the price range (highest / lowest) and average price of products supplied by each supplier.

Expected results:

12. The customer service team wants to identify the 5 most active customers based on the number of orders they have placed. Write a query to accomplish this.

Expected results:

Please put all SQL queries for the practice questions into a single SQL file. Use comments to label each query with its respective question number. Submit this .sql file for your assignment on Canvas.

• SQL - W10 Advanced Querying - Introduction
• SQL - W10 Advanced Querying - Aggregate Functions
• SQL - W10 Advanced Querying - Column Aliasing
• SQL - W10 Advanced Querying - Calculating Values
• SQL - W10 Advanced Querying - Northwind Database
• SQL - W10 Advanced Querying - GROUP BY
• SQL - W10 Advanced Querying - Data Analytics with GROUP BY
• SQL - W10 Advanced Querying and Data Analytics Assignment