Subqueries and nested queries in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the purpose of subqueries

What is the main purpose of using a subquery in an SQL statement?

A To create a new table from existing tables

B To perform a query inside another query to filter or calculate values

C To permanently store data in the database

D To delete rows from multiple tables simultaneously

💡 Hint

Think about how one query can depend on the result of another.

A subquery is a query nested inside another query. It helps to filter or calculate values based on the results of the inner query.

📋 Factual

intermediate

Identifying valid subquery placement

Which of the following SQL clauses can contain a subquery?

A WHERE, FROM, and SELECT clauses

B INSERT and DELETE clauses only

C CREATE and DROP clauses only

D ALTER and GRANT clauses only

💡 Hint

Subqueries are often used to filter rows or generate data sets.

Subqueries can be used in WHERE to filter rows, in FROM to create temporary tables, and in SELECT to compute values.

🚀 Application

advanced

Output of nested subquery with aggregation

What will be the output of this query?

SELECT employee_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 10);

A List of employee IDs with salary greater than the average salary in department 10

B List of employee IDs only from department 10

C List of employee IDs with salary greater than the overall average salary of all employees

D Syntax error due to incorrect subquery

💡 Hint

Look at the subquery filtering by department_id = 10 and calculating average salary.

The subquery calculates the average salary for department 10. The outer query selects employees whose salary is greater than this average.

🔍 Analysis

advanced

Analyzing error in correlated subquery

Consider this query:

SELECT e1.employee_id FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

What error or issue will this query produce?

A It will cause a runtime error because the subquery returns multiple rows

B It will cause a syntax error due to alias misuse

C It will run correctly and return employees with salary above their department average

D It will return no rows because the condition is always false

💡 Hint

Check if the subquery is correlated properly and returns a single value per outer row.

This is a correlated subquery comparing each employee's salary to the average salary of their department. It runs correctly and returns expected results.

❓ Reasoning

expert

Determining the number of rows returned by nested queries

Given two tables:

• departments(department_id, department_name)
• employees(employee_id, department_id, salary)

What is the number of rows returned by this query?

SELECT department_id FROM departments WHERE department_id IN (SELECT department_id FROM employees WHERE salary > 50000);

A Number of employees in departments with salary less than or equal to 50000

B Number of employees with salary greater than 50000

C Total number of departments regardless of employee salaries

D Number of departments that have at least one employee with salary greater than 50000

💡 Hint

The subquery finds departments with employees earning more than 50000; the outer query filters departments accordingly.

The subquery returns department IDs where employees earn more than 50000. The outer query returns those departments only, so the count equals departments with such employees.

Practice

1. What is a subquery in a database query?

easy

A. A query nested inside another query

B. A query that updates data

C. A query that deletes tables

D. A query that creates indexes

Solution

1. Step 1: Understand the definition of subquery

   A subquery is a query written inside another query to help filter or calculate data.

2. Step 2: Identify the correct description

   Among the options, only "A query nested inside another query" matches the definition of a subquery.

3. Final Answer:

   A query nested inside another query -> Option A

4. Quick Check:

   Subquery = Nested query [OK]

Hint: Subquery means query inside another query [OK]

Common Mistakes:

• Confusing subquery with update or delete operations
• Thinking subquery creates indexes
• Mixing subquery with table creation

2. Which of the following is the correct syntax to use a subquery in the WHERE clause?

easy

A. SELECT * FROM table WHERE column = (SELECT value FROM other_table);

B. SELECT * FROM table WHERE SELECT value FROM other_table = column;

C. SELECT * FROM table WHERE column IN SELECT value FROM other_table;

D. SELECT * FROM table WHERE column = SELECT value FROM other_table;

Solution

1. Step 1: Review correct subquery syntax in WHERE clause

   The subquery must be enclosed in parentheses and used as a value to compare with a column.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); correctly uses parentheses around the subquery. Options A, B, and D have syntax errors or missing parentheses.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); -> Option A

4. Quick Check:

   Subquery in WHERE uses parentheses [OK]

Hint: Subquery in WHERE must be inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing subquery before column
• Using IN without parentheses

3. Consider the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in Sales

B. Names of all employees regardless of department

C. Names of employees who work in the Sales department

D. Department names of all employees

Solution

1. Step 1: Understand the subquery

   The subquery finds the dept_id for the department named 'Sales'.

2. Step 2: Apply the subquery result in the main query

   The main query selects employee names whose dept_id matches the Sales dept_id.

3. Final Answer:

   Names of employees who work in the Sales department -> Option C

4. Quick Check:

   Subquery filters Sales dept_id, main query filters employees [OK]

Hint: Subquery finds Sales dept_id, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Ignoring subquery filtering

4. Identify the error in this query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Wrong table name used

B. Missing parentheses around the subquery

C. Using = instead of IN

D. Missing WHERE clause in subquery

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses when used in WHERE clause comparisons.

2. Step 2: Identify the error in the query

   The query misses parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subquery needs parentheses [OK]

Hint: Always put subqueries in parentheses [OK]

Common Mistakes:

• Omitting parentheses
• Confusing = with IN unnecessarily
• Incorrect table names

5. You want to find all employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT emp_name FROM Employees WHERE salary > (SELECT MAX(salary) FROM Employees);

B. SELECT emp_name FROM Employees WHERE salary > AVG(salary);

C. SELECT emp_name FROM Employees WHERE salary > (SELECT salary FROM Employees);

D. SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); uses a correlated subquery that calculates the average salary for the employee's department. Options A, B, and C do not correctly calculate the average per department or use wrong aggregates.

3. Final Answer:

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); -> Option D

4. Quick Check:

   Correlated subquery compares salary to dept average [OK]

Hint: Use correlated subquery with dept_id to get average salary [OK]

Common Mistakes:

• Using global average instead of per department
• Using MAX instead of AVG
• Not correlating subquery with outer query