Why views matter in PostgreSQL - See It in Action

Understanding Why Views Matter in PostgreSQL

📖 Scenario: You work at a small online bookstore. The database has tables for books and sales. You want to create a simple way to see total sales per book without writing complex queries every time.

🎯 Goal: Build a PostgreSQL view that shows each book's title and the total number of copies sold. This view will help you quickly check sales without repeating joins and sums.

📋 What You'll Learn

Create a books table with columns book_id (integer) and title (text).

Create a sales table with columns sale_id (integer), book_id (integer), and quantity (integer).

Insert sample data into both tables with exact values.

Create a view named book_sales_summary that shows title and total quantity sold per book.

Use the view to simplify queries for total sales per book.

💡 Why This Matters

🌍 Real World

Views help database users and developers by simplifying complex queries into reusable virtual tables. This saves time and reduces errors when accessing common data summaries.

💼 Career

Database administrators and developers often create views to improve data access efficiency and maintainability in real-world applications.

1

Create the books and sales tables with sample data

Write SQL statements to create a table called books with columns book_id (integer) and title (text). Then create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Insert these exact rows into books: (1, 'Learn SQL'), (2, 'PostgreSQL Basics'), (3, 'Advanced Databases'). Insert these exact rows into sales: (1, 1, 3), (2, 2, 5), (3, 1, 2), (4, 3, 4).

PostgreSQL

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

Hint

Use CREATE TABLE statements for both tables. Use INSERT INTO with exact values given.

2

Define a configuration variable for the view name

Create a SQL variable called view_name and set it to the text 'book_sales_summary'. This will be used to name the view in the next step.

PostgreSQL

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    book_id INTEGER,
    quantity INTEGER
);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'PostgreSQL Basics'),
(3, 'Advanced Databases');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 4);

-- Create a variable called view_name and set it to 'book_sales_summary'
-- Your code here

Hint

Use the psql command \set to define the variable view_name.

3

Create the view book_sales_summary to show total sales per book

Write a SQL statement to create a view named book_sales_summary that shows two columns: title from the books table and the total quantity sold from the sales table. Use a JOIN on book_id and group the results by title.

PostgreSQL

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    book_id INTEGER,
    quantity INTEGER
);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'PostgreSQL Basics'),
(3, 'Advanced Databases');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 4);

\set view_name 'book_sales_summary'

-- Create a view named book_sales_summary showing title and total quantity sold
-- Your code here

Hint

Use CREATE VIEW with a SELECT that joins books and sales, grouping by title.

4

Use the view to query total sales per book

Write a SQL query that selects all columns from the view book_sales_summary to see the total sales per book.

PostgreSQL

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    book_id INTEGER,
    quantity INTEGER
);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'PostgreSQL Basics'),
(3, 'Advanced Databases');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 4);

\set view_name 'book_sales_summary'

CREATE VIEW book_sales_summary AS
SELECT b.title, SUM(s.quantity) AS total_quantity
FROM books b
JOIN sales s ON b.book_id = s.book_id
GROUP BY b.title;

-- Query the view to see total sales per book
-- Your code here

Hint

Use a simple SELECT * FROM book_sales_summary; to get all rows from the view.

Practice

1. What is the main purpose of using a VIEW in PostgreSQL?

easy

A. To save a complex query as a reusable object

B. To permanently store data separately from tables

C. To speed up data insertion into tables

D. To delete rows from multiple tables at once

Solution

1. Step 1: Understand what a VIEW is

   A VIEW in PostgreSQL is a saved SQL query that acts like a virtual table.

2. Step 2: Identify the main use of a VIEW

   It simplifies complex queries by letting you reuse them without rewriting.

3. Final Answer:

   To save a complex query as a reusable object -> Option A

4. Quick Check:

   Views simplify queries = A [OK]

Hint: Views store queries, not data, for easy reuse [OK]

Common Mistakes:

• Thinking views store data separately
• Confusing views with tables
• Assuming views speed up inserts

2. Which of the following is the correct syntax to create a view named active_users showing users with status = 'active'?

easy

A. CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

B. CREATE active_users VIEW AS SELECT * FROM users WHERE status = 'active';

C. VIEW CREATE active_users AS SELECT * FROM users WHERE status = 'active';

D. CREATE VIEW active_users SELECT * FROM users WHERE status = 'active';

Solution

1. Step 1: Recall the correct CREATE VIEW syntax

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

2. Step 2: Match the syntax with options

   CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'; -> Option A

4. Quick Check:

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

Hint: Remember: CREATE VIEW name AS SELECT ... [OK]

Common Mistakes:

• Swapping keywords CREATE and VIEW
• Omitting AS keyword
• Incorrect keyword order

3. Given the table orders with columns id, customer, and amount, what will this view return?

CREATE VIEW big_orders AS SELECT customer, amount FROM orders WHERE amount > 100;

Then running:

SELECT * FROM big_orders;

medium

A. Syntax error because id is missing in view

B. All orders showing id, customer, and amount

C. All orders with amount greater than 100 showing customer and amount

D. Only orders with amount less than or equal to 100

Solution

1. Step 1: Understand the view definition

   The view selects customer and amount from orders where amount is greater than 100.

2. Step 2: Predict the SELECT * from the view

   It returns only customer and amount columns for orders with amount > 100.

3. Final Answer:

   All orders with amount greater than 100 showing customer and amount -> Option C

4. Quick Check:

   View filters amount > 100 and selects customer, amount = D [OK]

Hint: View columns = selected columns, filtered by WHERE [OK]

Common Mistakes:

• Expecting all columns to appear
• Confusing filter condition direction
• Thinking missing columns cause errors

4. You created a view with:

CREATE VIEW recent_sales AS SELECT * FROM sales WHERE sale_date > CURRENT_DATE - INTERVAL '7 days';

But running SELECT * FROM recent_sales; returns an error. What is the likely cause?

medium

A. CURRENT_DATE cannot be used in a view definition

B. The view definition is correct; error is elsewhere

C. The view needs to be refreshed manually to update data

D. The INTERVAL syntax is invalid in the view definition

Solution

1. Step 1: Check if INTERVAL and CURRENT_DATE are valid

   Both INTERVAL and CURRENT_DATE are valid in PostgreSQL and can be used in views.

2. Step 2: Understand view behavior

   Views are virtual and always reflect current data; no manual refresh needed.

3. Final Answer:

   The view definition is correct; error is elsewhere -> Option B

4. Quick Check:

   Valid syntax means error is not in view code = A [OK]

Hint: Check error source; valid syntax means problem is outside view [OK]

Common Mistakes:

• Assuming CURRENT_DATE is disallowed in views
• Thinking views store data needing refresh
• Blaming INTERVAL syntax without checking error message

5. You want to restrict users to see only active employees' names and departments without exposing salaries. Which approach using views is best?

hard

A. Create a view selecting name, department, and salary for all employees

B. Create a view selecting all columns but filter status = 'active'

C. Create a view selecting only salary and status columns

D. Create a view selecting only name and department from employees where status = 'active'

Solution

1. Step 1: Identify data to expose

   Only active employees' names and departments should be visible, no salaries.

2. Step 2: Choose view that filters and limits columns

   Create a view selecting only name and department from employees where status = 'active' selects only needed columns and filters active status, protecting salary data.

3. Final Answer:

   Create a view selecting only name and department from employees where status = 'active' -> Option D

4. Quick Check:

   View limits columns and filters rows = C [OK]

Hint: Select only needed columns and filter rows in view [OK]

Common Mistakes:

• Including salary column exposing sensitive data
• Not filtering active status
• Selecting all columns unnecessarily