Query execution plans in DBMS Theory - Mini Project: Build & Apply

Understanding Query Execution Plans

📖 Scenario: You are a database analyst who wants to understand how a database runs queries to improve performance. You will explore a simple SQL query and learn how to view its execution plan step-by-step.

🎯 Goal: Build a basic SQL query and learn how to generate and interpret its execution plan to see how the database processes the query.

📋 What You'll Learn

Create a simple table with data

Write a basic SELECT query

Use EXPLAIN to get the query execution plan

Identify key parts of the execution plan

💡 Why This Matters

🌍 Real World

Understanding query execution plans helps database users and developers optimize queries for faster and more efficient data retrieval.

💼 Career

Database administrators and developers use execution plans daily to troubleshoot slow queries and improve database performance.

1

Create a table with sample data

Write SQL code to create a table called employees with columns id (integer) and name (text). Insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

DBMS Theory

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write a simple SELECT query

Write a SQL query that selects all columns from the employees table using SELECT * FROM employees.

DBMS Theory

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
-- Write the SELECT query below
-- Your code here

Need a hint?

Use SELECT * FROM employees; to get all rows and columns.

3

Generate the query execution plan

Write a SQL statement using EXPLAIN before the SELECT query to show the query execution plan for SELECT * FROM employees.

DBMS Theory

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
-- Add EXPLAIN to show the query plan
-- Your code here

Need a hint?

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

4

Identify key parts of the execution plan

Add a comment below the EXPLAIN statement describing what the execution plan shows about how the database reads the employees table.

DBMS Theory

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
EXPLAIN SELECT * FROM employees;
-- Describe the execution plan here
-- Your code here

Need a hint?

Look for terms like 'Seq Scan' or 'sequential scan' in the plan and explain it simply.