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

Need a 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

Need a 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

Need a 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

Need a hint?

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