View as a saved query mental model in SQL - Mini Project: Build & Apply

Create and Use a View as a Saved Query

📖 Scenario: You work in a small bookstore's database team. The store wants to easily see a list of all books with their authors and prices without writing the full query every time.

🎯 Goal: Build a VIEW named BookDetails that saves a query joining the Books and Authors tables. Then, select from this view to see the combined data.

📋 What You'll Learn

Create a view named BookDetails that joins Books and Authors on author_id

The view should include columns: book_id, title, author_name, and price

Select all columns from the BookDetails view

💡 Why This Matters

🌍 Real World

Views help save complex queries so users can reuse them easily without rewriting SQL every time.

💼 Career

Database developers and analysts use views to simplify data access and improve query management in real projects.

1

Create the Books and Authors tables with sample data

Write SQL statements to create two tables: Books and Authors. Insert these exact rows: Authors with author_id 1 and 2, names 'Jane Austen' and 'Mark Twain'. Books with book_id 101 and 102, titles 'Pride and Prejudice' and 'Adventures of Huckleberry Finn', author_id 1 and 2, and prices 9.99 and 12.50.

SQL

-- Create Authors table and insert data
-- Create Books table and insert data

Hint

Use CREATE TABLE to define tables and INSERT INTO to add rows with the exact values given.

2

Define the view to join Books and Authors

Write a SQL statement to create a view named BookDetails. This view should select book_id, title, author_name, and price by joining Books and Authors on author_id.

SQL

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100)
);

INSERT INTO Authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain');

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    price DECIMAL(5,2),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

INSERT INTO Books (book_id, title, author_id, price) VALUES
(101, 'Pride and Prejudice', 1, 9.99),
(102, 'Adventures of Huckleberry Finn', 2, 12.50);

-- Create the view BookDetails here

Hint

Use CREATE VIEW BookDetails AS SELECT ... FROM Books JOIN Authors ON ... to save the query.

3

Select all columns from the BookDetails view

Write a SQL query to select all columns from the view named BookDetails.

SQL

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100)
);

INSERT INTO Authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain');

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    price DECIMAL(5,2),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

INSERT INTO Books (book_id, title, author_id, price) VALUES
(101, 'Pride and Prejudice', 1, 9.99),
(102, 'Adventures of Huckleberry Finn', 2, 12.50);

CREATE VIEW BookDetails AS
SELECT Books.book_id, Books.title, Authors.author_name, Books.price
FROM Books
JOIN Authors ON Books.author_id = Authors.author_id;

-- Select all columns from BookDetails view here

Hint

Use SELECT * FROM BookDetails; to get all columns from the saved query view.

4

Use the view to filter books priced above 10

Write a SQL query to select all columns from the BookDetails view where the price is greater than 10.

SQL

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100)
);

INSERT INTO Authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain');

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    price DECIMAL(5,2),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

INSERT INTO Books (book_id, title, author_id, price) VALUES
(101, 'Pride and Prejudice', 1, 9.99),
(102, 'Adventures of Huckleberry Finn', 2, 12.50);

CREATE VIEW BookDetails AS
SELECT Books.book_id, Books.title, Authors.author_name, Books.price
FROM Books
JOIN Authors ON Books.author_id = Authors.author_id;

SELECT * FROM BookDetails
-- Add WHERE clause to filter price > 10

Hint

Use WHERE price > 10 after selecting from the view to filter results.

Practice

1. What is a SQL view best described as?

easy

A. A backup copy of a database

B. A physical table storing data permanently

C. A saved query that acts like a virtual table

D. A user account with special permissions

Solution

1. Step 1: Understand what a view is

   A view is not a physical table but a saved SQL query that can be treated like a table.

2. Step 2: Compare options to definition

   A saved query that acts like a virtual table matches the definition exactly. The other options describe different database concepts like backups, physical tables, or user permissions.

3. Final Answer:

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

4. Quick Check:

   View = saved query acting like table [OK]

Hint: Remember: Views are saved queries, not real tables [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing views with backups
• Assuming views are user accounts

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

easy

A. VIEW CREATE EmployeeView SELECT * FROM Employees;

B. CREATE TABLE EmployeeView AS SELECT * FROM Employees;

C. SELECT * INTO EmployeeView FROM Employees;

D. CREATE VIEW EmployeeView AS SELECT * FROM Employees;

Solution

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

   The correct syntax starts with CREATE VIEW, followed by the view name, AS, then the SELECT query.

2. Step 2: Check each option

   CREATE VIEW EmployeeView AS SELECT * FROM Employees; matches the correct syntax. CREATE TABLE EmployeeView AS SELECT * FROM Employees; creates a table, not a view. SELECT * INTO EmployeeView FROM Employees; is for SELECT INTO which creates a table. VIEW CREATE EmployeeView SELECT * FROM Employees; is invalid syntax.

3. Final Answer:

   CREATE VIEW EmployeeView AS SELECT * FROM Employees; -> Option D

4. Quick Check:

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

Hint: Use CREATE VIEW ... AS SELECT ... to define views [OK]

Common Mistakes:

• Using CREATE TABLE instead of CREATE VIEW
• Confusing SELECT INTO with view creation
• Incorrect keyword order in syntax

3. Given the table Products with columns id, name, and price, and the view created as:
CREATE VIEW CheapProducts AS SELECT id, name FROM Products WHERE price < 50;
What will the query SELECT * FROM CheapProducts; return if Products contains:

id | name    | price
1  | Pen     | 10
2  | Notebook| 60
3  | Eraser  | 30

medium

A. Rows with id 1, 2, and 3 showing all columns

B. Rows with id 1 and 3 showing id and name columns

C. Rows with id 2 only showing id and name columns

D. No rows returned because price is not selected

Solution

1. Step 1: Understand the view definition

   The view selects id and name from Products where price is less than 50.

2. Step 2: Apply the filter to the data

   Products with price less than 50 are id 1 (price 10) and id 3 (price 30). The view returns only id and name columns.

3. Final Answer:

   Rows with id 1 and 3 showing id and name columns -> Option B

4. Quick Check:

   View filters price < 50 and selects id, name [OK]

Hint: View returns filtered columns and rows as defined [OK]

Common Mistakes:

• Expecting all columns in view output
• Including rows that don't meet WHERE condition
• Thinking view stores data separately

4. You created a view with:
CREATE VIEW ActiveUsers AS SELECT id, name FROM Users WHERE active = 1;
But running SELECT * FROM ActiveUsers; gives an error: ERROR: relation "activeusers" does not exist
What is the most likely cause?

medium

A. The view was not created successfully or was dropped

B. The SELECT query inside the view has syntax errors

C. The Users table does not have an 'active' column

D. You must refresh the view before selecting from it

Solution

1. Step 1: Analyze the error message

   The error says the relation (view) "activeusers" does not exist, meaning the view is missing.

2. Step 2: Consider causes for missing view

   This usually means the view was never created or was dropped. Syntax errors or missing columns cause errors during creation, not this runtime error. Views do not require refreshing.

3. Final Answer:

   The view was not created successfully or was dropped -> Option A

4. Quick Check:

   Missing view = creation failed or dropped [OK]

Hint: Check if view exists before querying it [OK]

Common Mistakes:

• Assuming syntax errors cause this runtime error
• Thinking views need refreshing like materialized views
• Ignoring case sensitivity or schema issues

5. You want to create a view RecentOrders that always shows orders placed in the last 7 days from the Orders table with columns order_id, customer_id, and order_date. Which SQL statement correctly creates this view?

hard

A. CREATE VIEW RecentOrders AS SELECT order_id, customer_id, order_date FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days';

B. CREATE VIEW RecentOrders AS SELECT order_id, customer_id FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days';

C. CREATE VIEW RecentOrders AS SELECT * FROM Orders WHERE order_date > DATEADD(day, -7, GETDATE());

D. CREATE VIEW RecentOrders AS SELECT order_id, customer_id FROM Orders WHERE order_date > SYSDATE - 7;

Solution

1. Step 1: Identify needed columns and date filter

   The view should include order_id, customer_id, and order_date columns and filter orders from last 7 days.

2. Step 2: Check each option's correctness

   CREATE VIEW RecentOrders AS SELECT order_id, customer_id FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days'; misses order_date column, so it won't show the date. CREATE VIEW RecentOrders AS SELECT order_id, customer_id, order_date FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days'; includes all needed columns and uses correct standard SQL interval syntax. CREATE VIEW RecentOrders AS SELECT * FROM Orders WHERE order_date > DATEADD(day, -7, GETDATE()); uses SQL Server syntax (DATEADD, GETDATE) which may not be standard. CREATE VIEW RecentOrders AS SELECT order_id, customer_id FROM Orders WHERE order_date > SYSDATE - 7; uses SYSDATE which is Oracle-specific and may not work in standard SQL.

3. Final Answer:

   CREATE VIEW RecentOrders AS SELECT order_id, customer_id, order_date FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days'; -> Option A

4. Quick Check:

   Include needed columns and use standard interval syntax [OK]

Hint: Include all needed columns and use standard date intervals [OK]

Common Mistakes:

• Omitting important columns in view
• Using non-standard date functions
• Forgetting to filter by date correctly