Lessons › SQL › Deep Dive: Database Admin & ETL

SQL lesson

Views

A view is a saved query given a name so you can treat it like a table - reference it anytime without rewriting the underlying logic.

Example

Saving Retail Reporting Logic with SQL Views

CREATE VIEW completed_orders AS
SELECT order_id, customer_id, revenue, discount, order_date
FROM orders
WHERE status = 'completed';

SELECT customer_id, SUM(revenue) AS total_revenue
FROM completed_orders
GROUP BY customer_id
ORDER BY total_revenue DESC

Result:

Step by step

1. CREATE VIEW saves a query so you can reuse it like a table.

   CREATE VIEW completed_orders AS

2. After SELECT, list the column names you want, separated by commas.

   CREATE VIEW completed_orders AS
   SELECT order_id, customer_id, revenue, discount, order_date

3. FROM tells SQL which table to pull the data from.

   CREATE VIEW completed_orders AS
   SELECT order_id, customer_id, revenue, discount, order_date
   FROM orders

Practice

Try each one in your head first, then open the answer.

CREATE VIEW orders_summary AS SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id

CREATE VIEW store_sales_summary AS SELECT store_id, COUNT(*) FROM store_sales GROUP BY store_id

CREATE VIEW products_summary AS SELECT product_id, COUNT(*) FROM products GROUP BY product_id

CREATE VIEW customers_summary AS SELECT email, COUNT(*) FROM customers GROUP BY email

CREATE VIEW returns_summary AS SELECT return_id, COUNT(*) FROM returns GROUP BY return_id