Why subqueries are needed in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Subqueries in SQL

Why do we use subqueries in SQL? Choose the best explanation.

A To create multiple tables at once in a single SQL statement.

B To perform a query inside another query to filter or calculate data before the main query runs.

C To speed up the database server by running queries in parallel automatically.

D To permanently store query results as new tables without writing extra commands.

💡 Hint

Think about how subqueries help break down complex questions into smaller parts.

Subqueries let you run a query inside another query. This helps you filter or calculate data first, then use that result in the main query. It makes complex data retrieval easier and clearer.

❓ query_result

intermediate

Output of a Subquery Filtering

Given the tables employees (id, name, department_id) and departments (id, name), what is the output of this query?

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

PostgreSQL

employees:
1, 'Alice', 2
2, 'Bob', 3
3, 'Charlie', 2
4, 'Diana', 1
departments:
1, 'HR'
2, 'Sales'
3, 'Marketing'

A Alice, Bob, Charlie

B Bob, Diana

C Alice, Charlie

D Diana

💡 Hint

Look at which department has the name 'Sales' and match employees with that department_id.

The subquery finds the id of the 'Sales' department, which is 2. The main query selects employees whose department_id is 2. That includes Alice and Charlie.

📝 Syntax

advanced

Identify the Syntax Error in Subquery Usage

Which option contains a syntax error in using a subquery in PostgreSQL?

PostgreSQL

SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = 'John');

A SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = 'John' LIMIT 1);

B SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = 'John');

C SELECT * FROM orders WHERE customer_id = ANY (SELECT id FROM customers WHERE name = 'John');

D SELECT * FROM orders WHERE customer_id IN SELECT id FROM customers WHERE name = 'John';

💡 Hint

Check the placement of parentheses and keywords around the subquery.

Option D is missing parentheses around the subquery after IN, which causes a syntax error. The correct syntax requires parentheses enclosing the subquery.

❓ optimization

advanced

Why Use Subqueries Instead of Joins in Some Cases?

Which reason best explains why a subquery might be preferred over a JOIN in a query?

A Subqueries can simplify queries when filtering based on aggregated results that are hard to express with JOINs.

B Subqueries always run faster than JOINs because they use less memory.

C Subqueries automatically create indexes to speed up queries, unlike JOINs.

D Subqueries allow combining data from unrelated tables without any conditions.

💡 Hint

Think about when you need to filter by a calculated value like a maximum or count.

Subqueries are useful when you want to filter rows based on aggregated data, such as maximum values or counts, which can be complex to do with JOINs alone.

🔧 Debug

expert

Debugging a Subquery Returning Multiple Rows Error

What error will this query produce and why?

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE location = 'New York');

Assume the subquery returns multiple department ids.

A ERROR: subquery returns more than one row because '=' expects a single value.

B No error, query runs successfully returning all matching employees.

C ERROR: syntax error near '=' because subqueries cannot be used in WHERE clauses.

D ERROR: missing FROM clause in subquery.

💡 Hint

Consider what happens when a subquery returns more than one value but '=' expects one.

The '=' operator expects a single value on the right side. If the subquery returns multiple rows, PostgreSQL raises an error about multiple rows returned.