Subquery in WHERE clause in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all employees who work in the department with ID 5.

SQL

SELECT * FROM employees WHERE department_id = [1];

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

A5

B10

Cdepartment_id

D'5'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the number 5, which makes it a string.

Using the column name instead of a value.

The department ID to filter by is 5, so we use 5 without quotes because it is a number.

2fill in blank

medium

Complete the code to select all employees whose department_id is in the list of department IDs from the departments table.

SQL

SELECT * FROM employees WHERE department_id IN (SELECT [1] FROM departments);

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

Asalary

Bemployee_id

Cname

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting employee_id instead of department_id in the subquery.

Selecting a column unrelated to departments.

The subquery should select department_id from the departments table to match with employees' department_id.

3fill in blank

hard

Fix the error in the query to select employees who have a salary greater than the average salary in their department.

SQL

SELECT * FROM employees WHERE salary > (SELECT AVG([1]) FROM employees WHERE department_id = employees.department_id);

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

Aemployee_id

B*

Csalary

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using department_id inside AVG, which is not numeric for averaging.

Using * inside AVG, which is invalid.

The subquery calculates the average salary, so it must use the salary column.

4fill in blank

hard

Fill both blanks to select all products with a price higher than the average price in their category.

SQL

SELECT * FROM products WHERE price > (SELECT [1] FROM products WHERE category_id = [2]);

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

AAVG(price)

Bproducts.category_id

Cdepartment_id

DMAX(price)

Attempts:

3 left

💡 Hint

Common Mistakes

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

Using department_id instead of products.category_id.

The subquery calculates the average price, so use AVG(price). The category filter uses products.category_id to match the outer query.

5fill in blank

hard

Fill all three blanks to select employees whose salary is greater than the average salary in their department and whose department name is 'Sales'.

SQL

SELECT * FROM employees WHERE salary > (SELECT AVG([1]) FROM employees WHERE department_id = [2]) AND department_id = (SELECT id FROM departments WHERE name = [3]);

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

Asalary

Bemployees.department_id

C'Sales'

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using department_id without table prefix in the subquery, causing ambiguity.

Using Sales without quotes, causing syntax error.

Using wrong column names in AVG or subqueries.

The first blank is the salary column for averaging. The second blank must reference the outer employees table's department_id. The third blank is the string 'Sales' to match the department name.