Querying through views in SQL - Mini Project: Build & Apply

Querying through Views

📖 Scenario: You work at a small bookstore that keeps track of book sales and customer information in a database. To make it easier for the sales team to get quick summaries, you want to create a view that shows total sales per customer.

🎯 Goal: Create a view named customer_sales_view that shows each customer's customer_id, customer_name, and their total sales_amount. Then write a query to select all data from this view.

📋 What You'll Learn

Create a table named customers with columns customer_id (integer) and customer_name (text).

Create a table named sales with columns sale_id (integer), customer_id (integer), and amount (numeric).

Insert the exact data provided into both tables.

Create a view named customer_sales_view that joins customers and sales and sums the amount per customer.

Write a query to select all columns from customer_sales_view.

💡 Why This Matters

🌍 Real World

Views help businesses create easy-to-use summaries of complex data, making it faster for teams to get insights without writing complicated queries every time.

💼 Career

Database developers and analysts often create and query views to improve data accessibility and performance in real-world applications.

1

Create tables and insert data

Create a table called customers with columns customer_id (integer) and customer_name (text). Then create a table called sales with columns sale_id (integer), customer_id (integer), and amount (numeric). Insert these exact rows into customers: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'). Insert these exact rows into sales: (101, 1, 50.00), (102, 2, 30.00), (103, 1, 20.00), (104, 3, 40.00).

SQL

-- Create customers and sales tables and insert data
-- Your code here

Hint

Use CREATE TABLE statements for both tables. Use INSERT INTO with multiple rows for inserting data.

2

Create the view for customer sales

Create a view named customer_sales_view that shows customer_id, customer_name, and the total sales amount as total_sales. Use a JOIN between customers and sales and group by customer_id and customer_name.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  customer_id INTEGER,
  amount NUMERIC
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO sales (sale_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 2, 30.00),
  (103, 1, 20.00),
  (104, 3, 40.00);

-- Create the view customer_sales_view below
-- Your code here

Hint

Use CREATE VIEW with a SELECT statement that joins the two tables and groups by customer.

3

Query the view

Write a query to select all columns from the view customer_sales_view.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  customer_id INTEGER,
  amount NUMERIC
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO sales (sale_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 2, 30.00),
  (103, 1, 20.00),
  (104, 3, 40.00);

CREATE VIEW customer_sales_view AS
SELECT
  customers.customer_id,
  customers.customer_name,
  SUM(sales.amount) AS total_sales
FROM customers
JOIN sales ON customers.customer_id = sales.customer_id
GROUP BY customers.customer_id, customers.customer_name;

-- Select all columns from customer_sales_view below
-- Your code here

Hint

Use SELECT * FROM customer_sales_view; to get all data from the view.

4

Add an ORDER BY clause to the query

Modify the query to select all columns from customer_sales_view and order the results by total_sales in descending order.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  customer_id INTEGER,
  amount NUMERIC
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO sales (sale_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 2, 30.00),
  (103, 1, 20.00),
  (104, 3, 40.00);

CREATE VIEW customer_sales_view AS
SELECT
  customers.customer_id,
  customers.customer_name,
  SUM(sales.amount) AS total_sales
FROM customers
JOIN sales ON customers.customer_id = sales.customer_id
GROUP BY customers.customer_id, customers.customer_name;

-- Modify the query to order by total_sales descending
-- Your code here

Hint

Add ORDER BY total_sales DESC after the SELECT statement to sort results from highest to lowest sales.

Practice

1. What is a view in SQL?
SELECT * FROM view_name; works because a view is:

easy

A. A saved query that acts like a virtual table

B. A physical table storing data permanently

C. A type of index to speed up queries

D. A backup copy of a database

Solution

1. Step 1: Understand what a view represents

   A view is not a physical table but a stored SQL query that behaves like a table.

2. Step 2: Recognize how views are queried

   You can query a view just like a table because it returns the result of its saved query.

3. Final Answer:

   A saved query that acts like a virtual table -> Option A

4. Quick Check:

   View = saved query acting like table [OK]

Hint: Remember: views are virtual tables, not physical data [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing views with indexes
• Assuming views are backups

2. Which of the following is the correct syntax to create a view named employee_view showing all columns from employees table?

easy

A. CREATE employee_view VIEW AS SELECT * FROM employees;

B. CREATE VIEW employee_view FROM employees;

C. VIEW CREATE employee_view AS SELECT * FROM employees;

D. CREATE VIEW employee_view AS SELECT * FROM employees;

Solution

1. Step 1: Recall the standard syntax for creating a view

   The correct syntax is: CREATE VIEW view_name AS SELECT ...

2. Step 2: Match the syntax with options

   CREATE VIEW employee_view AS SELECT * FROM employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW employee_view AS SELECT * FROM employees; -> Option D

4. Quick Check:

   CREATE VIEW ... AS SELECT ... [OK]

Hint: Use 'CREATE VIEW view_name AS SELECT ...' format [OK]

Common Mistakes:

• Swapping keywords CREATE and VIEW
• Using FROM instead of AS
• Incorrect keyword order

3. Given a view high_salary defined as:

CREATE VIEW high_salary AS SELECT name, salary FROM employees WHERE salary > 70000;

What will this query return?

SELECT * FROM high_salary WHERE salary > 80000;

medium

A. Syntax error because salary filter is repeated

B. All employees with salary greater than 70000

C. All employees with salary greater than 80000

D. Empty result because salary > 70000 is overridden

Solution

1. Step 1: Understand the view definition

   The view returns employees with salary > 70000 only.

2. Step 2: Apply the query filter on the view

   The query further filters those results to salary > 80000, so only employees with salary above 80000 are returned.

3. Final Answer:

   All employees with salary greater than 80000 -> Option C

4. Quick Check:

   View filters 70000+, query filters 80000+ [OK]

Hint: Filters on views stack, narrowing results [OK]

Common Mistakes:

• Thinking the second filter overrides the first
• Assuming syntax error due to repeated conditions
• Believing the result will be empty

4. You have this view:

CREATE VIEW dept_count AS SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department;

Which query will cause an error when run on this view?

medium

A. SELECT department FROM dept_count;

B. SELECT department, salary FROM dept_count;

C. SELECT emp_count FROM dept_count WHERE department = 'Sales';

D. SELECT * FROM dept_count WHERE emp_count > 5;

Solution

1. Step 1: Identify columns in the view

   The view has columns: department and emp_count only.

2. Step 2: Check each query's column usage

   SELECT department, salary FROM dept_count; tries to select 'salary' which does not exist in the view, causing an error.

3. Final Answer:

   SELECT department, salary FROM dept_count; causes error -> Option B

4. Quick Check:

   Querying non-existent column = error [OK]

Hint: Select only columns defined in the view [OK]

Common Mistakes:

• Selecting columns not in the view
• Assuming all original table columns exist in view
• Ignoring GROUP BY effects on columns

5. You want to create a view active_customers that shows customers with at least one order in the last 30 days.
Given tables:
customers(id, name)
orders(id, customer_id, order_date)
Which is the correct SQL to create this view?

hard

A. CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days');

B. CREATE VIEW active_customers AS SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE order_date > CURRENT_DATE - 30);

C. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL 30 DAY;

D. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days';

Solution

1. Step 1: Understand the requirement

   The view must include customers with orders in last 30 days only.

2. Step 2: Analyze each option's correctness

   CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days'); uses EXISTS with correct date interval syntax and correlates orders to customers properly. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days'; uses JOIN but may duplicate customers if multiple orders exist. CREATE VIEW active_customers AS SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE order_date > CURRENT_DATE - 30); has incorrect date subtraction syntax. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL 30 DAY; uses LEFT JOIN but filters on order_date, which can exclude customers without recent orders incorrectly.

3. Final Answer:

   CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days'); -> Option A

4. Quick Check:

   Use EXISTS with correct date interval for filtering [OK]

Hint: Use EXISTS with correct date interval for filtering [OK]

Common Mistakes:

• Using incorrect date interval syntax
• Using JOIN causing duplicate rows
• Filtering on LEFT JOIN columns incorrectly