Why query patterns matter in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Output of a JOIN with filtering

Given two tables, Employees and Departments, what is the output of the following query?

SELECT e.name, d.department_name FROM Employees e JOIN Departments d ON e.department_id = d.id WHERE d.location = 'New York';

Assume Employees:
1, Alice, 10
2, Bob, 20
3, Carol, 10
Departments:
10, Sales, New York
20, Marketing, Boston

SQL

SELECT e.name, d.department_name FROM Employees e JOIN Departments d ON e.department_id = d.id WHERE d.location = 'New York';

A [{"name": "Bob", "department_name": "Marketing"}]

B [{"name": "Alice", "department_name": "Sales"}, {"name": "Carol", "department_name": "Sales"}]

C [{"name": "Alice", "department_name": "Sales"}, {"name": "Bob", "department_name": "Marketing"}, {"name": "Carol", "department_name": "Sales"}]

D []

💡 Hint

Focus on the JOIN condition and the WHERE clause filtering by location.

The query joins Employees with Departments on department_id and filters departments located in New York. Only employees in Sales (id 10) are included, which are Alice and Carol.

📝 Syntax

intermediate

Identify the syntax error in the query

Which option contains a syntax error in the SQL query?

SELECT name, age FROM Users WHERE age > 20 ORDER BY;

SQL

SELECT name, age FROM Users WHERE age > 20 ORDER BY;

A SELECT name, age FROM Users WHERE age > 20 ORDER BY;

B SELECT name, age FROM Users WHERE age > 20 ORDER BY age DESC;

C SELECT name, age FROM Users WHERE age > 20 ORDER BY 1;

D SELECT name, age FROM Users WHERE age > 20 ORDER BY age;

💡 Hint

ORDER BY must specify a column or expression to sort by.

Option A ends ORDER BY without specifying any column, causing a syntax error.

❓ optimization

advanced

Choose the most efficient query pattern for counting

You want to count how many orders each customer has placed. Which query pattern is the most efficient?

A SELECT customer_id FROM Orders GROUP BY customer_id HAVING COUNT(*) > 0;

B SELECT DISTINCT customer_id, (SELECT COUNT(*) FROM Orders o2 WHERE o2.customer_id = o1.customer_id) FROM Orders o1;

C SELECT customer_id, COUNT(*) FROM Orders GROUP BY customer_id;

D SELECT customer_id, COUNT(order_id) FROM Orders WHERE order_id IS NOT NULL GROUP BY customer_id;

💡 Hint

Consider how many times the Orders table is scanned in each query.

Option C uses GROUP BY with COUNT(*) scanning the table once. Option C uses a correlated subquery causing multiple scans. Option C only filters groups but does not count. Option C is similar to A but unnecessarily filters order_id IS NOT NULL which is redundant if order_id is always present.

🔧 Debug

advanced

Why does this query return no rows?

Given tables Products and Sales, why does this query return no rows?

SELECT p.product_name FROM Products p LEFT JOIN Sales s ON p.id = s.product_id WHERE s.sale_date > '2023-01-01';

Assume some products have no sales.

SQL

SELECT p.product_name FROM Products p LEFT JOIN Sales s ON p.id = s.product_id WHERE s.sale_date > '2023-01-01';

A Because the WHERE clause filters out rows where s.sale_date is NULL, removing unmatched products.

B Because LEFT JOIN requires an ON condition that matches all rows, which is missing.

C Because Products table is empty.

D Because sale_date column does not exist in Sales table.

💡 Hint

Think about how WHERE affects rows with NULLs from LEFT JOIN.

The WHERE clause filters on s.sale_date > '2023-01-01'. For products without sales, s.sale_date is NULL, so the condition fails and those rows are excluded, negating the LEFT JOIN effect.

🧠 Conceptual

expert

Why do query patterns affect database performance?

Which explanation best describes why different SQL query patterns can lead to very different performance even if they return the same results?

A Because databases ignore query patterns and optimize all queries to the same plan automatically.

B Because all queries are executed the same way internally, so patterns do not affect performance.

C Because query patterns only affect the order of results, not the speed of execution.

D Because query patterns determine how the database engine uses indexes, joins, and scans, affecting speed and resource use.

💡 Hint

Think about how databases process queries and use indexes.

Different query patterns can cause the database to choose different execution plans, use or ignore indexes, perform joins differently, and scan more or fewer rows, impacting performance.