1. Write a SELECT query that returns the same result set as this SELECT query, but do...
Question:
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