Subqueries in FROM (derived tables) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from a derived table named 'sub'.

PostgreSQL

SELECT [1] FROM (SELECT id, name FROM employees) AS sub;

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

A*

Bemployees

Cname

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using the original table name instead of the alias.

Selecting a column not present in the derived table.

Using * selects all columns from the derived table sub.

2fill in blank

medium

Complete the code to calculate the average salary from a derived table named 'dept_salaries'.

PostgreSQL

SELECT AVG(salary) FROM (SELECT salary FROM employees WHERE department = [1]) AS dept_salaries;

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

A"Sales"

BSales

C'Sales'

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting quotes around string literals.

Using double quotes instead of single quotes.

String literals in SQL must be enclosed in single quotes, so 'Sales' is correct.

3fill in blank

hard

Fix the error in the code by completing the alias for the derived table.

PostgreSQL

SELECT sales_summary.id, sales_summary.total FROM (SELECT id, SUM(amount) AS total FROM sales GROUP BY id) [1];

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

AAS sales_summary

Bsummary

CAS

Dsales_summary

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the alias entirely.

Using AS without an alias name.

In PostgreSQL, the alias for a derived table can be given without the AS keyword, so sales_summary alone is correct.

4fill in blank

hard

Fill both blanks to select employee names and their department names using a derived table.

PostgreSQL

SELECT e.name, d.[1] FROM employees e JOIN (SELECT id, [2] FROM departments) d ON e.department_id = d.id;

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

Adepartment_name

Bname

Cdept_name

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not exist in the departments table.

Mixing up alias names.

The derived table selects id and name from departments, aliased as d. The main query selects d.name as the department name.

5fill in blank

hard

Fill all three blanks to create a derived table that counts employees per department and select departments with more than 5 employees.

PostgreSQL

SELECT dept_name, emp_count FROM (SELECT d.name AS [1], COUNT(e.id) AS [2] FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name) AS dept_counts WHERE [3] > 5;

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

Adept_name

Bemp_count

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names not aliased in the derived table.

Using the original column names in the WHERE clause instead of aliases.

The derived table aliases d.name as dept_name and the count as emp_count. The WHERE clause filters rows where emp_count is greater than 5.