SQL TUTORIALS

Answer Key

1. Query used:
   SELECT state, (SELECT SUM(item_price) FROM orders.order_items) AS revenue
FROM orders.customers
GROUP BY state;
2. Query used:
   SELECT MAX(total_purchase_price) AS largest_order_amount
FROM (SELECT order_id, SUM(item_price)
      FROM orders.order_items GROUP BY order_id) AS inr_query;
3. Query used:
   SELECT order_date, SUM(item_price) AS total_revenue
FROM orders.orders AS o
JOIN orders.order_items AS oi ON o.order_id = oi.order_id
WHERE order_date IN(SELECT order_date
                    FROM orders.orders AS o
                    JOIN orders.customers AS c ON o.customer_id = c.customer_id
                    WHERE state IN('CA', 'CO', 'CT'))
GROUP BY order_date;