WITH clause syntax in SQL - Mini Project: Build & Apply

Using the WITH Clause in SQL

📖 Scenario: You are managing a small bookstore database. You want to organize your queries better by using the WITH clause to create temporary named result sets.

🎯 Goal: Build a SQL query using the WITH clause to create a temporary table of books published after 2010, then select their titles and authors from that temporary table.

📋 What You'll Learn

Create a WITH clause named recent_books that selects all columns from books where published_year is greater than 2010

Write a main SELECT query that selects title and author from recent_books

💡 Why This Matters

🌍 Real World

Using the WITH clause helps organize complex queries by breaking them into smaller, readable parts.

💼 Career

Many database jobs require writing clear and maintainable SQL queries using WITH clauses for better performance and readability.

1

Create the books table

Write a SQL statement to create a table called books with columns id (integer), title (text), author (text), and published_year (integer).

SQL

-- Your code here

Need a hint?

Use CREATE TABLE books and define the columns with their data types.

2

Insert sample data into books

Insert these exact rows into the books table: (1, 'The Silent Patient', 'Alex Michaelides', 2019), (2, 'Educated', 'Tara Westover', 2018), (3, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925).

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  published_year INTEGER
);
-- Your code here

Need a hint?

Use INSERT INTO books (id, title, author, published_year) VALUES with the given rows.

3

Write the WITH clause for recent books

Write a SQL query using the WITH clause named recent_books that selects all columns from books where published_year is greater than 2010.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  published_year INTEGER
);
INSERT INTO books (id, title, author, published_year) VALUES
  (1, 'The Silent Patient', 'Alex Michaelides', 2019),
  (2, 'Educated', 'Tara Westover', 2018),
  (3, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925);

-- Your code here

Need a hint?

Use WITH recent_books AS (SELECT * FROM books WHERE published_year > 2010).

4

Select titles and authors from recent_books

Complete the SQL query by selecting title and author from the recent_books temporary table defined in the WITH clause.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  published_year INTEGER
);
INSERT INTO books (id, title, author, published_year) VALUES
  (1, 'The Silent Patient', 'Alex Michaelides', 2019),
  (2, 'Educated', 'Tara Westover', 2018),
  (3, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925);

WITH recent_books AS (
  SELECT * FROM books WHERE published_year > 2010
)
-- Your code here

Need a hint?

After the WITH clause, write SELECT title, author FROM recent_books;.