Why subqueries are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use subqueries in SQL?

Which of the following best explains why subqueries are needed in SQL?

A To perform a query inside another query to filter or calculate based on intermediate results.

B To speed up the database server by running multiple queries at once.

C To replace the need for joins between tables.

D To store data permanently in the database.

💡 Hint

Think about how you can use one query's result inside another query.

Subqueries allow you to use the result of one query inside another. This helps when you need to filter or calculate based on intermediate data.

❓ query_result

intermediate

Output of a subquery filtering example

Given the tables Employees (id, name, salary) and Departments (id, name, manager_id), what is the output of this query?

SELECT name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

A All employee names regardless of salary.

B Names of employees whose salary is above the average salary of all employees.

C Names of employees whose salary is below the average salary of all employees.

D An error because subqueries cannot be used in WHERE clause.

💡 Hint

Look at what the subquery calculates and how it is used in the WHERE clause.

The subquery calculates the average salary. The outer query selects employees with salary greater than that average.

📝 Syntax

advanced

Identify the correct syntax for a subquery in SELECT

Which option shows the correct syntax to use a subquery inside a SELECT statement to get the department name for each employee?

SQL

Tables: Employees(id, name, department_id), Departments(id, name)

A SELECT name, (SELECT name FROM Departments WHERE id = Employees.id) AS dept_name FROM Employees;

B SELECT name, SELECT name FROM Departments WHERE id = Employees.department_id AS dept_name FROM Employees;

C SELECT name, (SELECT name FROM Departments WHERE id = department_id) dept_name FROM Employees;

D SELECT name, (SELECT name FROM Departments WHERE id = Employees.department_id) AS dept_name FROM Employees;

💡 Hint

Check the placement of the subquery and alias syntax.

Option D correctly uses a subquery in SELECT with proper aliasing and references the outer table.

❓ optimization

advanced

Why might subqueries cause performance issues?

Which reason best explains why subqueries can sometimes slow down SQL queries?

A Because subqueries store temporary data on disk.

B Because subqueries prevent the use of indexes.

C Because the subquery runs once for each row of the outer query, increasing execution time.

D Because subqueries always lock the entire table.

💡 Hint

Think about how many times the subquery might execute.

Correlated subqueries run once per outer row, which can slow down queries if the outer table is large.

🔧 Debug

expert

Find the error in this subquery usage

What error will this query produce?

SELECT name FROM Employees WHERE salary > (SELECT salary FROM Employees WHERE id = 10);

A Error: Subquery returns zero rows if no employee with id=10 exists.

B No error, query runs correctly.

C Error: Subquery returns more than one row.

D Error: Subquery cannot reference the same table as outer query.

💡 Hint

What happens if the subquery finds no matching row?

If no employee with id=10 exists, the subquery returns no rows, causing the outer query to compare salary to NULL, resulting in no matches.