Subqueries vs JOINs comparison in MySQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

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

MySQL

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

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

Aemployee_id

Bname

Cdepartment_id

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using the department name instead of its id in the subquery.

Selecting a column that does not exist in the departments table.

The subquery must return the department's id to match with employees' department_id.

2fill in blank

medium

Complete the code to join employees with departments to get employee names and their department names.

MySQL

SELECT employees.name, departments.[1] FROM employees JOIN departments ON employees.department_id = departments.id;

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

Aname

Bdepartment_id

Cid

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the department id instead of the name.

Using a column that belongs to employees instead of departments.

We want to select the name of the department to show alongside employee names.

3fill in blank

hard

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

MySQL

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

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

ACOUNT

BSUM

CAVG

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of AVG.

Using COUNT which counts rows, not salary values.

The query needs the average salary, so the function AVG is correct.

4fill in blank

hard

Fill both blanks to write a JOIN that selects employee names and their manager names.

MySQL

SELECT e.name AS employee, m.[1] AS manager FROM employees e JOIN employees m ON e.[2] = m.id;

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

Aname

Bmanager_id

Cid

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the wrong column for manager's name.

Using department_id instead of manager_id for the join.

The manager's name is selected, and employees' manager_id links to managers' id.

5fill in blank

hard

Fill all three blanks to write a subquery that finds departments with more than 5 employees.

MySQL

SELECT name FROM departments WHERE id IN (SELECT [1] FROM employees GROUP BY [2] HAVING COUNT([3]) > 5);

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

Adepartment_id

Bemployee_id

C*

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee_id instead of department_id in GROUP BY.

Counting department_id instead of all rows with *.

The subquery selects department_id from employees, groups by it, and counts all rows with * to find departments with more than 5 employees.