Why joins are essential in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from two tables joined on a common column.

PostgreSQL

SELECT * FROM employees [1] departments ON employees.department_id = departments.id;

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

AJOIN

BSELECT

CWHERE

DGROUP BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using SELECT instead of JOIN causes syntax errors.

Using WHERE without JOIN does not combine tables properly.

The JOIN keyword combines rows from two tables based on a related column.

2fill in blank

medium

Complete the code to get employee names and their department names using a join.

PostgreSQL

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

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

Aemployees

Bprojects

Cmanagers

Ddepartments

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting from the wrong table causes errors or wrong results.

Using a table not joined in the query causes errors.

We select the department name from the departments table.

3fill in blank

hard

Fix the error in the join clause to correctly join employees and departments.

PostgreSQL

SELECT * FROM employees JOIN departments ON employees.[1] = departments.id;

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

Aid

Bdepartment_id

Cname

Ddept_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name causes join errors.

Mixing up primary and foreign keys leads to wrong results.

The department_id in employees matches the id in departments.

4fill in blank

hard

Fill both blanks to select employee names and their project names using a join.

PostgreSQL

SELECT employees.[1], projects.[2] FROM employees JOIN projects ON employees.project_id = projects.id;

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

Aname

Bid

Ctitle

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting wrong columns leads to confusing results.

Using IDs instead of names or titles does not show meaningful data.

We select name from employees and title from projects.

5fill in blank

hard

Fill all three blanks to select employee names, department names, and project titles using joins.

PostgreSQL

SELECT employees.[1], departments.[2], projects.[3] FROM employees JOIN departments ON employees.department_id = departments.id JOIN projects ON employees.project_id = projects.id;

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

Aname

Btitle

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up column names causes wrong output.

Using 'id' instead of descriptive columns shows numbers, not names.

Select employee name, department name, and project title.

Practice

1. Why are JOINs essential in PostgreSQL when working with multiple tables?

easy

A. They delete duplicate rows from a table.

B. They speed up the database server hardware.

C. They create new tables automatically.

D. They combine related data from different tables into one result.

Solution

1. Step 1: Understand the purpose of JOINs

   JOINs are used to combine rows from two or more tables based on a related column between them.

2. Step 2: Identify what JOINs do not do

   JOINs do not delete rows, create tables, or affect hardware speed.

3. Final Answer:

   They combine related data from different tables into one result. -> Option D

4. Quick Check:

   JOINs combine tables = B [OK]

Hint: Remember: JOINs merge related rows from tables [OK]

Common Mistakes:

• Thinking JOINs delete or create tables
• Confusing JOINs with database hardware functions
• Assuming JOINs work without related columns

2. Which of the following is the correct syntax to join two tables employees and departments on the column department_id?

easy

A. SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;

B. SELECT * FROM employees WHERE department_id = departments.department_id;

C. SELECT * FROM employees JOIN departments USING (department_id);

D. SELECT * FROM employees JOIN departments ON employees.id = departments.id;

Solution

1. Step 1: Check the JOIN syntax with ON clause

   The correct JOIN syntax uses ON to specify the matching columns: employees.department_id = departments.department_id.

2. Step 2: Verify other options

   SELECT * FROM employees WHERE department_id = departments.department_id; uses WHERE incorrectly for joining. SELECT * FROM employees JOIN departments USING (department_id); uses correct USING syntax with parentheses. SELECT * FROM employees JOIN departments ON employees.id = departments.id; joins on wrong columns (id instead of department_id).

3. Final Answer:

   SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id; -> Option A

4. Quick Check:

   JOIN with ON correct columns = C [OK]

Hint: Use ON with matching columns for JOINs [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Joining on wrong columns
• Incorrect USING syntax without parentheses

3. Given two tables:
students(id, name)
grades(student_id, score)
What will be the result of this query?

SELECT students.name, grades.score FROM students JOIN grades ON students.id = grades.student_id;

medium

A. A list of scores without student names.

B. A list of student names with their corresponding scores.

C. An error because JOIN syntax is wrong.

D. A list of all students with NULL scores.

Solution

1. Step 1: Understand the JOIN condition

   The JOIN matches students.id with grades.student_id, linking each student to their scores.

2. Step 2: Predict the output columns

   The SELECT returns student names and their scores from matching rows only.

3. Final Answer:

   A list of student names with their corresponding scores. -> Option B

4. Quick Check:

   JOIN matches students to scores = D [OK]

Hint: JOIN returns matched rows combining columns [OK]

Common Mistakes:

• Assuming all students appear even without scores
• Thinking JOIN causes syntax error here
• Expecting only scores without names

4. Identify the error in this JOIN query:

SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.idd;

medium

A. Column name 'idd' does not exist in departments table.

B. Missing JOIN keyword.

C. Using table aliases incorrectly.

D. SELECT statement missing FROM clause.

Solution

1. Step 1: Check the JOIN condition columns

   The query uses 'd.idd' which is likely a typo; the correct column is probably 'd.id'.

2. Step 2: Confirm other syntax parts

   JOIN keyword and aliases are correctly used; FROM clause is present.

3. Final Answer:

   Column name 'idd' does not exist in departments table. -> Option A

4. Quick Check:

   Typo in column name = A [OK]

Hint: Check column names carefully in JOIN conditions [OK]

Common Mistakes:

• Typos in column names in ON clause
• Confusing table aliases usage
• Missing FROM clause (not in this case)

5. You have two tables:
orders(order_id, customer_id, amount)
customers(customer_id, name)
You want to list all customers and their total order amounts, including customers with no orders. Which query achieves this?

hard

A. SELECT c.name, SUM(o.amount) FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.name;

B. SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

C. SELECT c.name, SUM(o.amount) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

D. SELECT c.name, SUM(o.amount) FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

Solution

1. Step 1: Understand the requirement for all customers

   To include customers with no orders, use LEFT JOIN from customers to orders.

2. Step 2: Use aggregation with GROUP BY

   SUM(o.amount) calculates total orders per customer; GROUP BY c.name groups results by customer.

3. Step 3: Check other options

   SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id; excludes customers without orders (INNER JOIN). SELECT c.name, SUM(o.amount) FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.name; reverses join direction, losing customers without orders. SELECT c.name, SUM(o.amount) FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name; uses RIGHT JOIN but groups by customer name from customers table, which may cause errors or exclude customers.

4. Final Answer:

   SELECT c.name, SUM(o.amount) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name; -> Option C

5. Quick Check:

   LEFT JOIN keeps all customers = A [OK]

Hint: Use LEFT JOIN to keep all from left table [OK]

Common Mistakes:

• Using INNER JOIN excludes customers without orders
• Confusing LEFT and RIGHT JOIN directions
• Forgetting GROUP BY when using aggregation