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.