Question

Write a SELECT statement that answers this question: What is the total quantity purchased for each product within each category? Return these columns:

a. The category_name column from the category table
b. The product_name column from the products table
c. The total quantity purchased for each product with orders in the Order_Items table

Answer 1

The provided SQL SELECT statement joins categories, products, and order_items tables to calculate the total quantity purchased for each product within each category, grouped and ordered by category name and total quantity.

Step-by-step explanation:

To write a SELECT statement that shows the total quantity purchased for each product within each category, we need to join three tables: categories, products, and order_items. We will assume these tables have the following relationships: products table has a foreign key to categories, and order_items has a foreign key to products. Here is the SQL statement that will retrieve the required information:

SELECT
c.category_name,
p.product_name,
SUM(oi.quantity) AS total_quantity_purchased
FROM
categories c
JOIN
products p ON c.category_id = p.category_id
JOIN
order_items oi ON p.product_id = oi.product_id
GROUP BY
c.category_name,
p.product_name
ORDER BY
c.category_name,
total_quantity_purchased DESC;

This SQL query first joins the categories table with the products table on the category_id. Then, it joins the resultant table with the order_items table on the product's product_id. It uses SUM to calculate the total quantity purchased for each product and groups the results by both category_name and product_name to provide the total per product within each category. Additionally, we sort the results by category_name and the total quantity in descending order.

Answer 2

Step-by-step explanation:

SELECT CategoryName, COUNT(*) AS ProductCount,

MAX(ListPrice) AS MostExpensiveProduct

FROM Categories c JOIN Products p

ON c.CategoryID = p.CategoryID

GROUP BY CategoryName

ORDER BY ProductCount DESC