1. Write a SELECT query that returns the same result set as this SELECT query, but do NOT use a JOIN. Instead, use a subquery in the WHERE clause that uses the IN keyword.

USE my_guitar_shop; 
SELECT DISTINCT CONCAT(last_name, ', ', first_name) AS customer_name
    FROM customers
        JOIN orders USING(customer_id)
    ORDER BY customer_name;

 

2. Write a SELECT query that returns three columns: email_address, order_id, and the sum of all order totals for each customer. To do this, you can group the result set by the order_id column. This should return 9 rows.

Then, write a second SELECT query that uses the first SELECT query in its FROM clause. The main query should return the email_address and a single value representing the largest order total for each customer. Sort the result in descending order from largest to smallest order total. This should return 7 rows.

 

HINT: For this question, the order total = (item_price - discount_amount) * quantity

 

3. Find all customers who have placed an order for a guitar model that costs more than the average price of all guitar models. Make sure to display all attributes for those customers. The result should be 2 rows. Use subquery structure.

 

4. Convert the answer to question 2 into a CTE structure