CREATE VIEW syntax in SQL - Mini Project: Build & Apply

Create a Simple SQL View

📖 Scenario: You work at a bookstore that keeps track of books and their authors in a database. You want to create a simple view to easily see the book titles along with their authors' names.

🎯 Goal: Build a SQL view named BookAuthorView that shows the title of each book and the author_name from the existing tables.

📋 What You'll Learn

Create a table called Books with columns book_id (integer), title (text), and author_id (integer).

Create a table called Authors with columns author_id (integer) and author_name (text).

Insert the exact data into Books: (1, 'The Great Gatsby', 101), (2, '1984', 102), (3, 'To Kill a Mockingbird', 103).

Insert the exact data into Authors: (101, 'F. Scott Fitzgerald'), (102, 'George Orwell'), (103, 'Harper Lee').

Create a view named BookAuthorView that shows title and author_name by joining Books and Authors on author_id.

💡 Why This Matters

🌍 Real World

Views help simplify complex queries by creating a virtual table that users can query easily without writing joins every time.

💼 Career

Database developers and analysts often create views to provide clean, reusable data access layers for applications and reports.

1

Create the Books and Authors tables

Write SQL statements to create a table called Books with columns book_id (integer), title (text), and author_id (integer). Then create a table called Authors with columns author_id (integer) and author_name (text).

SQL

-- Create the Books table
-- Create the Authors table

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Insert data into Books and Authors

Write SQL INSERT INTO statements to add these exact rows into Books: (1, 'The Great Gatsby', 101), (2, '1984', 102), (3, 'To Kill a Mockingbird', 103). Then insert these exact rows into Authors: (101, 'F. Scott Fitzgerald'), (102, 'George Orwell'), (103, 'Harper Lee').

SQL

-- Insert rows into Books
-- Insert rows into Authors

Need a hint?

Use one INSERT INTO statement per table with multiple rows.

3

Write the SELECT query for the view

Write a SQL SELECT statement that selects title from Books and author_name from Authors. Join the tables on Books.author_id = Authors.author_id.

SQL

-- Write the SELECT statement to join Books and Authors
-- Select title and author_name

Need a hint?

Use JOIN to combine the tables on author_id.

4

Create the view BookAuthorView

Write a SQL statement to create a view named BookAuthorView using the SELECT query that shows title and author_name by joining Books and Authors on author_id.

SQL

-- Create the view BookAuthorView using the SELECT query

Need a hint?

Use CREATE VIEW view_name AS SELECT ... syntax.