How the database engine processes a SELECT in SQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Output of SELECT with WHERE and ORDER BY

Given the table Employees with columns id, name, and salary, what is the output of this query?

SELECT name FROM Employees WHERE salary > 50000 ORDER BY name ASC;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 60000), (2, 'Bob', 45000), (3, 'Charlie', 70000);
SELECT name FROM Employees WHERE salary > 50000 ORDER BY name ASC;

A [{"name": "Alice"}, {"name": "Charlie"}]

B [{"name": "Charlie"}, {"name": "Alice"}]

C [{"name": "Bob"}]

D []

💡 Hint

Think about filtering first, then sorting alphabetically.

The query filters employees with salary greater than 50000, which are Alice and Charlie. Then it orders them by name ascending, so Alice comes before Charlie.

🧠 Conceptual

intermediate

Order of operations in SELECT processing

In what order does the database engine process the clauses in a SELECT statement?

A 1,2,3,4,5,6

B 5,1,2,3,4,6

C 1,5,2,3,4,6

D 2,1,3,5,4,6

💡 Hint

Think about which clause filters rows first and which one selects columns.

The database engine processes FROM first to get data, then WHERE to filter rows, then GROUP BY to group rows, HAVING to filter groups, SELECT to choose columns, and ORDER BY to sort the final result.

📝 Syntax

advanced

Identify the syntax error in this SELECT query

Which option contains a syntax error when trying to select all columns from Products where price is less than 100?

A SELECT * FROM Products WHERE price < 100 ORDER BY;

B SELECT * Products WHERE price < 100;

C SELECT * FROM Products WHERE price < 100;

D SELECT * FROM Products WHERE price < 100

💡 Hint

Check the FROM clause syntax carefully.

Option B is missing the FROM keyword before the table name, causing a syntax error.

❓ optimization

advanced

Optimizing SELECT with JOIN and WHERE

Given two tables Orders and Customers, which query is more efficient to find orders from customers in 'New York'?

A SELECT o.id FROM Orders o WHERE EXISTS (SELECT 1 FROM Customers c WHERE c.id = o.customer_id AND c.city = 'New York');

B SELECT o.id FROM Orders o, Customers c WHERE o.customer_id = c.id AND c.city = 'New York';

C SELECT o.id FROM Orders o WHERE o.customer_id IN (SELECT id FROM Customers WHERE city = 'New York');

D SELECT o.id FROM Orders o JOIN Customers c ON o.customer_id = c.id WHERE c.city = 'New York';

💡 Hint

Consider how JOINs and subqueries affect query planning.

Option D uses an explicit JOIN which is generally more efficient and clearer for the database optimizer than subqueries or implicit joins.

🔧 Debug

expert

Why does this SELECT query return no rows?

Consider these tables:

Users(id, name)
Orders(id, user_id, total)

Why does this query return no rows?

SELECT u.name FROM Users u LEFT JOIN Orders o ON u.id = o.user_id WHERE o.total > 100;

A Because the ON condition is incorrect and causes no matches.

B Because LEFT JOIN only returns rows with matching orders, so no users without orders appear.

C Because the WHERE clause filters out rows where o.total is NULL, removing all users without orders.

D Because the SELECT clause is missing a GROUP BY for aggregation.

💡 Hint

Think about how WHERE affects rows after a LEFT JOIN.

The LEFT JOIN keeps all users, but the WHERE clause filters rows where o.total > 100. For users without orders, o.total is NULL, so the condition fails and those rows are removed, resulting in no rows if no orders have total > 100.

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