Correlated subqueries execution model in PostgreSQL - 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.

PostgreSQL

SELECT emp_name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.[1]);

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

Adept_id

Bsalary

Cemp_id

Demp_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee ID instead of department ID for correlation.

Referencing salary or name instead of department ID.

The correlated subquery compares each employee's salary to the average salary of their own department. The correlation happens via dept_id.

2fill in blank

medium

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

PostgreSQL

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

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

Acategory_id

Bproduct_name

Cprice

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using product ID instead of category ID for correlation.

Comparing price to product name.

The subquery is correlated by matching the category ID of the outer product to compute the average price per category.

3fill in blank

hard

Fix the error in the correlated subquery to find customers who placed orders with total amount greater than average order amount for that customer.

PostgreSQL

SELECT customer_id FROM orders o WHERE total_amount > (SELECT AVG(total_amount) FROM orders WHERE customer_id = o.[1]);

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

Aorder_id

Bcustomer_id

Ctotal_amount

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using order ID instead of customer ID for correlation.

Referencing total_amount or order_date instead of customer_id.

The correlation must be on customer_id to compare each order's total amount to the average for that customer.

4fill in blank

hard

Fill both blanks to select employees whose salary is above the average salary in their department and who joined after 2010.

PostgreSQL

SELECT emp_name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.[1]) AND join_year [2] 2010;

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

Adept_id

B>

C<=

Demp_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee ID instead of department ID for correlation.

Using '<=' instead of '>' for join year condition.

The first blank correlates the subquery by department ID. The second blank uses '>' to filter employees who joined after 2010.

5fill in blank

hard

Fill all three blanks to select products with price above average in their category and stock quantity greater than 50.

PostgreSQL

SELECT product_name FROM products p WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.[1]) AND stock_quantity [2] [3];

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

Acategory_id

B>

C50

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using product ID instead of category ID for correlation.

Using '<' or '=' instead of '>' for stock quantity.

Using wrong number instead of 50.

The first blank correlates by category ID. The second blank uses '>' to filter stock quantity, and the third blank is the number 50.