SQL TUTORIALS

Answer Key

1. Query used:
   CREATE VIEW orders.revenue_by_state AS
   SELECT state, SUM(item_price) AS totol_revenue
   FROM orders.customers AS c
   JOIN orders.orders AS o ON c.customer_id = o.customer_id
   JOIN orders.order_items AS oi ON o.order_id = oi.order_id
   GROUP BY state;
2. Query used:
   CREATE VIEW orders.largest_orders AS
   SELECT o.*, SUM(item_price) AS total_price
   FROM orders.orders AS o
   JOIN orders.order_items AS oi ON o.order_id = oi.order_id
   GROUP BY o.order_id, order_date
   ORDER BY total_price DESC
   LIMIT 5;
3. Query used:
   CREATE VIEW orders.orders_2022 AS
   SELECT first_name, last_name, item_name, item_description, item_price
   FROM orders.customers AS c
   JOIN orders.orders AS o ON c.customer_id = o.customer_id
   JOIN orders.order_items AS oi ON o.order_id = oi.order_id
   WHERE order_year = 2022;