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.