Correlated subquery execution model in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select employees whose salary is greater than the average salary in their department.

SQL

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

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

Aemployees.department_id

Ce.department_id

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using a table alias that is not defined in the subquery.

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

The subquery compares the department_id of the outer query with the department_id in the subquery. Using department_id correctly refers to the outer query's column in the correlated subquery.

2fill in blank

medium

Complete the code to find customers who have placed orders with amounts greater than the average order amount for that customer.

SQL

SELECT customer_id FROM orders o1 WHERE order_amount > (SELECT AVG(order_amount) FROM orders o2 WHERE o2.customer_id = [1]);

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

Ao1.customer_id

Bo2.customer_id

Ccustomer_id

Dorders.customer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using the subquery alias o2 instead of the outer query alias.

Using unqualified column names causing ambiguity.

The subquery is correlated by matching o2.customer_id with the outer query's o1.customer_id. So, o1.customer_id is the correct reference.

3fill in blank

hard

Fix the error in the correlated subquery to correctly find products with price higher than the average price in their category.

SQL

SELECT product_id, price FROM products p WHERE price > (SELECT AVG(price) FROM products WHERE category_id = [1]);

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

Ap.product_id

Bproducts.category_id

Cp.category_id

Dcategory_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using unaliased column names causing the subquery to be uncorrelated.

Using the wrong alias or column name.

The subquery must correlate with the outer query's category_id using the alias p. So, p.category_id is correct.

4fill in blank

hard

Fill both blanks to select orders where the order amount is greater than the average order amount for the same customer.

SQL

SELECT order_id, order_amount FROM orders o WHERE order_amount > (SELECT AVG(order_amount) FROM orders [1] WHERE [2].customer_id = o.customer_id);

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

Ao2

Bo

Dorders

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same alias for both outer and inner queries.

Not correlating the subquery properly.

The subquery uses alias o2 and correlates with the outer query alias o by matching o2.customer_id to o.customer_id.

5fill in blank

hard

Fill all three blanks to select employees whose salary is above the average salary in their department and whose department is active.

SQL

SELECT e.employee_id, e.salary FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees [1] WHERE [2].department_id = e.department_id) AND e.department_id IN (SELECT department_id FROM departments WHERE status = [3]);

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

Aemp

C'active'

D'inactive'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same alias for outer and inner queries.

Using wrong status string causing no results.

The subquery uses alias emp to refer to employees and correlates with outer alias e. The department status must be 'active' to filter active departments.