Nested subqueries in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all employees who have a salary greater than the average salary.

SQL

SELECT * FROM employees WHERE salary > (SELECT [1] FROM employees);

Drag options to blanks, or click blank then click option'

AAVG(salary)

BSUM(salary)

CMAX(salary)

DMIN(salary)

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM() instead of AVG() in the subquery.

Using MAX() or MIN() which find extremes, not averages.

The subquery calculates the average salary using AVG(salary). The main query selects employees earning more than this average.

2fill in blank

medium

Complete the code to find products with a price higher than the average price in their category.

SQL

SELECT product_name FROM products WHERE price > (SELECT [1] FROM products WHERE category_id = products.category_id);

Drag options to blanks, or click blank then click option'

AMAX(price)

BMIN(price)

CAVG(price)

DSUM(price)

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX(price) which finds the highest price, not average.

Not correlating the subquery with the outer query's category_id.

The subquery calculates the average price for the same category using AVG(price). The main query selects products priced above this average.

3fill in blank

hard

Fix the error in the query to select customers who placed orders with total amount greater than 1000.

SQL

SELECT customer_id FROM orders WHERE order_id IN (SELECT order_id FROM order_details GROUP BY order_id HAVING SUM([1]) > 1000);

Drag options to blanks, or click blank then click option'

Aquantity

Bquantity * price

Cprice

Dorder_id

Attempts:

3 left

💡 Hint

Common Mistakes

Summing only quantity or price separately.

Using order_id inside SUM which is not numeric.

The total amount per order is calculated by summing quantity * price in the subquery. Using only quantity or price alone won't give the total order amount.

4fill in blank

hard

Fill both blanks to select employees whose salary is higher than the average salary in their department.

SQL

SELECT employee_id, salary FROM employees WHERE salary > (SELECT [1] FROM employees WHERE department_id = employees.department_id [2]);

Drag options to blanks, or click blank then click option'

AAVG(salary)

BMAX(salary)

CAND employee_id <> employees.employee_id

DOR employee_id = employees.employee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX(salary) instead of AVG(salary).

Not excluding the current employee causing incorrect comparison.

The subquery calculates the average salary with AVG(salary). The condition AND employee_id <> employees.employee_id excludes the current employee to avoid comparing with themselves.

5fill in blank

hard

Fill all three blanks to create a query that lists products with price above the average price in their category and stock greater than 50.

SQL

SELECT product_name, price, stock FROM products WHERE price > (SELECT [1] FROM products WHERE category_id = products.category_id) AND stock [2] [3];

Drag options to blanks, or click blank then click option'

AAVG(price)

B>

C50

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > for stock comparison.

Using SUM(price) instead of AVG(price) in subquery.

The subquery uses AVG(price) to find the average price per category. The main query filters products with price greater than this average and stock greater than 50 using stock > 50.

Practice

1. What does a nested subquery in SQL do?

easy

A. Runs a query inside another query to filter or compare data

B. Creates a new table from existing data

C. Deletes data from multiple tables at once

D. Updates all rows in a table without conditions

Solution

1. Step 1: Understand the concept of nested subqueries

   A nested subquery is a query inside another query that runs first to provide data for the outer query.

2. Step 2: Identify the correct description

   Runs a query inside another query to filter or compare data correctly describes this behavior as running a query inside another to filter or compare data.

3. Final Answer:

   Runs a query inside another query to filter or compare data -> Option A

4. Quick Check:

   Nested subquery = query inside query [OK]

Hint: Nested means one query inside another [OK]

Common Mistakes:

• Confusing nested subquery with table creation
• Thinking nested subqueries delete data
• Assuming nested subqueries update all rows blindly

2. Which of the following is the correct syntax for a nested subquery in SQL?

easy

A. SELECT * FROM table WHERE id IN SELECT id FROM table2 WHERE value = 10;

B. SELECT * FROM table WHERE id == (SELECT id FROM table2 WHERE value = 10);

C. SELECT * FROM table WHERE id = SELECT id FROM table2 WHERE value = 10;

D. SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10);

Solution

1. Step 1: Review correct nested subquery syntax

   The inner query must be enclosed in parentheses and used with operators like = or IN.

2. Step 2: Check each option

   SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10); uses parentheses correctly and equals operator, making it valid SQL syntax.

3. Final Answer:

   SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10); -> Option D

4. Quick Check:

   Nested subquery syntax uses parentheses [OK]

Hint: Always use parentheses around subqueries [OK]

Common Mistakes:

• Missing parentheses around subquery
• Using double equals (==) instead of single =
• Omitting parentheses causing syntax errors

3. Given 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 work in the Sales department

B. All employee names regardless of department

C. Department names where employees work

D. Employee names who do not work in Sales

Solution

1. Step 1: Understand the inner query

   The inner query finds the dept_id for the 'Sales' department from Departments table.

2. Step 2: Apply the outer query condition

   The outer query selects employee names whose dept_id matches the Sales dept_id found by the inner query.

3. Final Answer:

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

4. Quick Check:

   Inner query finds Sales dept_id, outer filters employees [OK]

Hint: Inner query finds filter value, outer applies it [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Assuming it returns employees not in Sales

4. Identify the error in this SQL query:

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

medium

A. Using = instead of IN for subquery

B. Missing parentheses around the subquery

C. Wrong table name used in subquery

D. Subquery returns multiple columns

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid inside WHERE clause.

2. Step 2: Identify the missing parentheses

   The query lacks parentheses around the subquery, causing syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subqueries need parentheses [OK]

Hint: Always wrap subqueries in parentheses [OK]

Common Mistakes:

• Forgetting parentheses around subquery
• Using wrong operator without parentheses
• Assuming subquery syntax is optional

5. You want to find all customers who placed orders with amounts greater than the average order amount. Which query correctly uses a nested subquery to achieve this?

hard

A. SELECT customer_id FROM Orders WHERE amount < (SELECT AVG(amount) FROM Orders);

B. SELECT customer_id FROM Orders WHERE amount = (SELECT AVG(amount) FROM Orders);

C. SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders);

D. SELECT customer_id FROM Orders WHERE amount IN (SELECT AVG(amount) FROM Orders);

Solution

1. Step 1: Understand the goal

   We want customers with orders greater than the average order amount.

2. Step 2: Analyze each option's condition

   SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders); uses > with a subquery calculating average amount, correctly filtering orders above average.

3. Final Answer:

   SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders); -> Option C

4. Quick Check:

   Use > with AVG subquery to find above-average orders [OK]

Hint: Compare with (SELECT AVG(...)) using > for above average [OK]

Common Mistakes:

• Using = instead of > to find above average
• Using < which finds below average
• Using IN with a single value subquery incorrectly