How the database engine processes a SELECT in SQL - Try It Yourself

How the database engine processes a SELECT

📖 Scenario: You are working with a small library database. You want to understand how the database engine processes a simple SELECT query to get book titles and authors.

🎯 Goal: Build a step-by-step SQL query that shows how the database engine processes a SELECT statement by creating a table, inserting data, selecting specific columns, and filtering results.

📋 What You'll Learn

Create a table named books with columns id, title, and author

Insert exactly three rows into the books table with given values

Write a SELECT query to retrieve only the title and author columns

Add a WHERE clause to filter books by a specific author

💡 Why This Matters

🌍 Real World

Understanding how SELECT queries work helps you retrieve exactly the data you need from databases in real applications like libraries, stores, or websites.

💼 Career

Database querying is a fundamental skill for data analysts, backend developers, and anyone working with data storage and retrieval.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as text, and author as text.

SQL

-- Your code here to create the books table

Hint

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

2

Insert data into books

Insert these three rows into the books table: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), and (3, 'To Kill a Mockingbird', 'Harper Lee'). Write three separate INSERT INTO statements.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);
-- Your code here to insert the three rows

Hint

Use three INSERT INTO books statements with the exact values.

3

Select title and author columns

Write a SELECT query to get only the title and author columns from the books table.

SQL

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

INSERT INTO books (id, title, author) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien');
INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Your code here to select title and author

Hint

Use SELECT title, author FROM books to get only those columns.

4

Filter books by author

Add a WHERE clause to the SELECT query to get only books where the author is 'George Orwell'.

SQL

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

INSERT INTO books (id, title, author) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien');
INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee');

SELECT title, author FROM books
-- Your code here to add WHERE clause filtering author

Hint

Add WHERE author = 'George Orwell' after the SELECT statement.

Practice

1. Which step does the database engine perform first when processing a SELECT query?

easy

A. It sorts the results according to the ORDER BY clause.

B. It groups rows using the GROUP BY clause.

C. It reads the table and filters rows based on the WHERE clause.

D. It picks which columns to show based on the SELECT list.

Solution

1. Step 1: Understand the role of the WHERE clause

   The WHERE clause filters rows early to reduce data for later steps.

2. Step 2: Recognize the order of operations in SELECT

   The database first filters rows, then picks columns, groups, and sorts.

3. Final Answer:

   It reads the table and filters rows based on the WHERE clause. -> Option C

4. Quick Check:

   Filtering rows happens before selecting columns [OK]

Hint: Filtering rows happens before picking columns or sorting [OK]

Common Mistakes:

• Thinking ORDER BY happens before filtering
• Assuming columns are picked before filtering rows
• Confusing grouping with filtering order

2. Which of the following is the correct syntax to select only the name and age columns from a table called people?

easy

A. SELECT name age FROM people;

B. SELECT * FROM people WHERE name, age;

C. SELECT name AND age FROM people;

D. SELECT name, age FROM people;

Solution

1. Step 1: Recall correct SELECT syntax for columns

   Columns are listed separated by commas after SELECT.

2. Step 2: Check each option's syntax

   SELECT name, age FROM people; uses commas correctly; others use wrong keywords or miss commas.

3. Final Answer:

   SELECT name, age FROM people; -> Option D

4. Quick Check:

   Columns separated by commas in SELECT [OK]

Hint: Use commas to separate columns in SELECT [OK]

Common Mistakes:

• Using AND instead of commas between columns
• Trying to filter columns in WHERE clause
• Omitting commas between column names

3. Given the table employees with columns id, name, and salary, what will this query return?

SELECT name FROM employees WHERE salary > 50000 ORDER BY name;

medium

A. All employee names sorted alphabetically regardless of salary.

B. All employee names sorted alphabetically with salary above 50000.

C. All employee names with salary below 50000, unsorted.

D. Syntax error due to missing GROUP BY clause.

Solution

1. Step 1: Analyze the WHERE clause

   The query filters employees with salary greater than 50000.

2. Step 2: Understand ORDER BY effect

   Results are sorted by name alphabetically.

3. Final Answer:

   All employee names sorted alphabetically with salary above 50000. -> Option B

4. Quick Check:

   Filter then sort results [OK]

Hint: Filter rows first, then sort results [OK]

Common Mistakes:

• Ignoring the salary filter
• Thinking GROUP BY is needed for ORDER BY
• Assuming all rows are returned

4. What is wrong with this query?

SELECT id, name FROM users WHERE age > 18 ORDER BY;

medium

A. Missing column name after ORDER BY causes syntax error.

B. WHERE clause should come after ORDER BY.

C. SELECT must include all columns in ORDER BY.

D. ORDER BY cannot be used with WHERE.

Solution

1. Step 1: Check ORDER BY syntax

   ORDER BY must specify at least one column to sort by.

2. Step 2: Identify error in query

   The query ends ORDER BY; without column name, causing syntax error.

3. Final Answer:

   Missing column name after ORDER BY causes syntax error. -> Option A

4. Quick Check:

   ORDER BY needs a column name [OK]

Hint: Always specify column after ORDER BY [OK]

Common Mistakes:

• Placing WHERE after ORDER BY
• Thinking ORDER BY needs all SELECT columns
• Believing ORDER BY can't be used with WHERE

5. You want to find the average salary for each department but only for employees earning more than 40000. Which query correctly processes this?

hard

A. SELECT department, AVG(salary) FROM employees WHERE salary > 40000 GROUP BY department;

B. SELECT department, AVG(salary) FROM employees GROUP BY department WHERE salary > 40000;

C. SELECT department, AVG(salary) FROM employees GROUP BY salary > 40000;

D. SELECT department, AVG(salary) FROM employees HAVING salary > 40000 GROUP BY department;

Solution

1. Step 1: Apply WHERE before grouping

   Filter employees with salary > 40000 before grouping by department.

2. Step 2: Use GROUP BY on department and calculate average salary

   Group filtered rows by department and compute average salary.

3. Final Answer:

   SELECT department, AVG(salary) FROM employees WHERE salary > 40000 GROUP BY department; -> Option A

4. Quick Check:

   Filter rows before grouping for correct average [OK]

Hint: Use WHERE before GROUP BY to filter rows [OK]

Common Mistakes:

• Placing WHERE after GROUP BY
• Using HAVING without aggregation condition
• Grouping by a condition instead of column