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.