Why subqueries are needed in PostgreSQL - 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.

PostgreSQL

SELECT * FROM employees WHERE department_id = [1];

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

A5

Bemployee_id

Cdepartment_id

D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name instead of the department ID.

Using a wrong number for the department.

The department ID 5 is used to filter employees working in that department.

2fill in blank

medium

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

PostgreSQL

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

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

Aemployee_id

Bdepartment_id

Cname

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the department name instead of its ID in the subquery.

Using employee columns inside the subquery.

The subquery selects the department's ID where the name is 'Sales'. This ID is used to find employees in that department.

3fill in blank

hard

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

PostgreSQL

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

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

Asalary

BAVG(salary)

CMAX(salary)

DMIN(salary)

Attempts:

3 left

💡 Hint

Common Mistakes

Using salary column 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 against it.

4fill in blank

hard

Fill both blanks to select employees who have the highest salary in their department.

PostgreSQL

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

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

AMAX(salary)

BMIN(salary)

Cdepartment_id

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using MIN instead of MAX.

Comparing with employee_id instead of department_id.

The subquery finds the maximum salary in the employee's department. The outer query compares each employee's salary to that maximum.

5fill in blank

hard

Fill all three blanks to select departments with more than 10 employees.

PostgreSQL

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

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

Adepartment_id

C*

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee_id instead of department_id for grouping.

Counting employee_id instead of all rows with COUNT(*).

The subquery groups employees by department_id and counts all employees (*) in each group. Departments with more than 10 employees are selected.