Question

Using the My Guitar Shop database you installed in Module 1, develop the following queries.

Write a SELECT statement that returns these columns:
The count of the number of orders in the Orders table

The sum of the tax_amount columns in the Orders table

Execute the query and take a screenshot of the query and the results.

Write a SELECT statement that returns one row for each category that has products with these columns:
The category_name column from the Categories table

The count of the products in the Products table

The list price of the most expensive product in the Products table.

Sort the result set so the category with the most products appears first. Execute the query, and take a screenshot of the query and the results.

Write a SELECT statement that returns one row for each customer that has orders with these columns:
The email_address column from the Customers table

The sum of the item price in the Order_Items table multiplied by the quantity in the Order_Items table

The sum of the discount amount column in the Order_Items table multiplied by the quantity in the Order_Items table

Sort the result set in descending sequence by the item price total for each customer. Execute the query and take a screenshot of the query and the results.

Write a SELECT statement that returns one row for each customer that has orders with these columns:
The email_address column from the Customers table

A count of the number of orders

The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)

Return only those rows where the customer has more than one order. Sort the result set in descending sequence by the sum of the line item amounts. Execute the query and take a screenshot of the query and the results.

All the screenshots should show current date. Example of screenshot.

Answer 1

1. Query for the count of the number of orders and the sum of the tax_amount columns in the Orders table:

SELECT COUNT(*) AS order_count, SUM(tax_amount) AS total_tax_amount

FROM Orders;

2. Query for the category_name, count of products, and the list price of the most expensive product for each category:

SELECT c.category_name, COUNT(p.product_id) AS product_count, MAX(p.list_price) AS max_list_price

FROM Categories c

JOIN Products p ON c.category_id = p.category_id

GROUP BY c.category_name

ORDER BY product_count DESC;

3. Query for the email_address, sum of item price multiplied by quantity, and sum of discount amount multiplied by quantity for each customer with orders:

SELECT c.email_address, SUM(oi.item_price * oi.quantity) AS total_item_price, SUM(oi.discount_amount * oi.quantity) AS total_discount_amount

FROM Customers c

JOIN Orders o ON c.customer_id = o.customer_id

JOIN Order_Items oi ON o.order_id = oi.order_id

GROUP BY c.email_address

ORDER BY total_item_price DESC;

4. Query for the email_address, count of number of orders, and the total amount for each order (price - discount) multiplied by quantity for customers with more than one order:

SELECT c.email_address, COUNT(o.order_id) AS order_count, SUM((oi.item_price - oi.discount_amount) * oi.quantity) AS total_amount

FROM Customers c

JOIN Orders o ON c.customer_id = o.customer_id

JOIN Order_Items oi ON o.order_id = oi.order_id

GROUP BY c.email_address

HAVING COUNT(o.order_id) > 1

ORDER BY total_amount DESC;

replace the table and column names with the appropriate names from your My Guitar Shop database.

Once you have executed these queries, you can capture screenshots of the query and the result set in your database management tool or interface. Make sure to include the current date in the screenshots as required.