Why subqueries nest queries in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

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

MySQL

SELECT * FROM employees WHERE department_id = [1];

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

Aemployee_id

B5

C10

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name in the WHERE clause.

Using a value other than 5.

The query filters employees whose department_id equals 5.

2fill in blank

medium

Complete the code to select employees who work in the department named 'Sales' using a subquery.

MySQL

SELECT * FROM employees WHERE department_id = (SELECT [1] FROM departments WHERE name = 'Sales');

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

Aid

Bname

Cdepartment_id

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the department name instead of its ID.

Selecting employee columns in the subquery.

The subquery must return the id of the department named 'Sales' to match department_id in employees.

3fill in blank

hard

Fix the error in the subquery to find employees with salary greater than the average salary.

MySQL

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

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

Asalary

BMAX(salary)

CAVG(salary)

DMIN(salary)

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting salary directly without aggregation.

Using MAX or MIN instead of AVG.

The subquery must calculate the average salary using AVG(salary) to compare each employee's salary.

4fill in blank

hard

Fill both blanks to select employees who work in departments with more than 10 employees.

MySQL

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

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

Aid

Bemployee_count

Cnum_employees

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names for department ID or employee count.

Using department_id in the WHERE clause instead of employee count.

The subquery selects department id where the number of employees (num_employees) is greater than 10.

5fill in blank

hard

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

MySQL

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

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

Asalary

Bdepartment_id

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns for average or matching condition.

Confusing employee_id with department_id.

The subquery calculates the average salary for employees in the same department_id as the outer query employee.