EXPLAIN plan for query analysis in SQL - Mini Project: Build & Apply

Understanding EXPLAIN Plan for Query Analysis

📖 Scenario: You are working with a small online bookstore database. You want to understand how the database processes your search queries to make them faster.

🎯 Goal: Learn how to use the EXPLAIN command to see the query plan and understand how the database executes your SQL queries.

📋 What You'll Learn

Create a simple table with book details

Write a SELECT query to find books by a specific author

Use EXPLAIN to analyze the query execution plan

Interpret the output to understand query steps

💡 Why This Matters

🌍 Real World

Database administrators and developers use EXPLAIN to understand and improve query performance in real applications.

💼 Career

Knowing how to analyze query plans is essential for roles like database developer, data analyst, and backend engineer.

1

Create the books table

Create a table called books with columns id (integer), title (text), and author (text).

SQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Insert sample data

Insert these exact rows into books: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee').

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);
-- Your code here to insert sample data

Need a hint?

Use INSERT INTO books (id, title, author) VALUES (...), (...), (...); with the exact data.

3

Write a SELECT query

Write a SELECT query to get all columns from books where the author is 'George Orwell'.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Your code here to select books by George Orwell

Need a hint?

Use SELECT * FROM books WHERE author = 'George Orwell'; to get the books by that author.

4

Use EXPLAIN to analyze the query

Add EXPLAIN before the SELECT query to see the query plan for selecting books by 'George Orwell'.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Your code here to add EXPLAIN before the SELECT query

Need a hint?

Simply add EXPLAIN before your SELECT query to see how the database plans to run it.