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

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

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

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

Need a hint?

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