Why understanding relationships matters in SQL - Visual Breakdown

Concept Flow - Why understanding relationships matters

Identify Entities

↓

Define Relationships

↓

Create Tables with Keys

↓

Use JOINs to Connect Data

↓

Query Combined Information

↓

Get Meaningful Results

This flow shows how understanding relationships helps connect data from different tables to get useful information.

Execution Sample

SQL

SELECT students.name, courses.title
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.id;

This query combines data from three tables to show which students are enrolled in which courses.

Execution Table

Step	Action	Tables Involved	Resulting Rows	Explanation
1	Start with students table	students	3 rows	We have 3 students: Alice, Bob, Carol
2	Join enrollments on students.id = enrollments.student_id	students, enrollments	4 rows	Matches students to their enrollments; Bob has 2 enrollments
3	Join courses on enrollments.course_id = courses.id	students, enrollments, courses	4 rows	Adds course titles to each enrollment
4	Select students.name and courses.title	final join	4 rows	Shows student names with their course titles
5	End	-	-	All matching data combined; query complete

💡 All matching rows combined; no more joins to perform

Variable Tracker

Variable	Start	After Join 1	After Join 2	Final
students_rows	3	3	3	3
enrollments_rows	4	4	4	4
courses_rows	3	3	3	3
joined_rows	0	4	4	4

Key Moments - 2 Insights

Why do we join tables instead of just using one table?

Because each table holds different pieces of information, joining lets us combine them to get full details, as shown in execution_table step 3.

What happens if a student has no enrollments?

They won't appear in the join result because the join matches only existing relationships, as seen in execution_table step 2 where only matching enrollments are included.

Visual Quiz - 3 Questions

Look at the execution table, how many rows are in the result after joining students and enrollments?

A 3

B 2

C 4

D 5

💡 Hint

Check the 'After Join 1' row in variable_tracker and step 2 in execution_table

At which step do we add course titles to the data?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column in execution_table for when courses are joined

If a new student with no enrollments is added, what happens to the final joined rows?

A The number of rows stays the same

B The number of rows increases

C The number of rows decreases

D The query fails

💡 Hint

Refer to key_moments about students without enrollments and how joins work

Concept Snapshot

Understanding relationships means knowing how tables connect using keys.
Use JOINs to combine related data from multiple tables.
This lets you get complete information, like which students take which courses.
Without relationships, data stays separated and less useful.
Always check join conditions to get correct combined results.

Full Transcript

Understanding relationships in databases is important because data is stored in separate tables. Each table holds different information, like students, courses, and enrollments. To get meaningful results, we join these tables using keys that link them. For example, joining students with enrollments and courses lets us see which student is in which course. The execution table shows step-by-step how the joins combine rows from each table. If a student has no enrollments, they won't appear in the joined result because the join only includes matching rows. This process helps us get complete and useful information from separate tables.

Practice

1. Why is it important to understand relationships between tables in a database?

easy

A. Because relationships prevent any data from being deleted.

B. Because relationships make the database run faster automatically.

C. Because relationships allow us to connect and combine data from different tables.

D. Because relationships store data in a single table only.

Solution

1. Step 1: Understand the role of relationships

   Relationships link tables so data can be combined meaningfully.

2. Step 2: Recognize the benefit of linking data

   Linking data helps answer questions that need info from multiple tables.

3. Final Answer:

   Because relationships allow us to connect and combine data from different tables. -> Option C

4. Quick Check:

   Relationships connect tables = C [OK]

Hint: Relationships connect tables to combine data easily [OK]

Common Mistakes:

• Thinking relationships speed up database automatically
• Believing relationships prevent data deletion
• Assuming all data is stored in one table

2. Which SQL keyword is used to combine rows from two tables based on a related column?

easy

A. JOIN

B. SELECT

C. WHERE

D. GROUP BY

Solution

1. Step 1: Identify the keyword for combining tables

   JOIN is used to link rows from two tables using a common column.

2. Step 2: Differentiate from other keywords

   SELECT retrieves data, WHERE filters rows, GROUP BY groups rows; only JOIN combines tables.

3. Final Answer:

   JOIN -> Option A

4. Quick Check:

   JOIN combines tables = B [OK]

Hint: JOIN links tables on common columns [OK]

Common Mistakes:

• Using SELECT to combine tables
• Confusing WHERE with JOIN
• Thinking GROUP BY combines tables

3. Given two tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT name, dept_name FROM Employees JOIN Departments ON Employees.dept_id = Departments.dept_id;

medium

A. A list of department names only.

B. A list of employee names with their department names.

C. A list of employee names only.

D. An error because JOIN syntax is wrong.

Solution

1. Step 1: Understand the JOIN condition

   The query joins Employees and Departments where dept_id matches.

2. Step 2: Identify selected columns

   It selects employee names and their matching department names.

3. Final Answer:

   A list of employee names with their department names. -> Option B

4. Quick Check:

   JOIN on dept_id returns employee and department names = A [OK]

Hint: JOIN returns combined rows matching keys [OK]

Common Mistakes:

• Expecting only one table's columns
• Thinking JOIN causes syntax error
• Ignoring the ON condition

4. What is wrong with this SQL query?

SELECT name, dept_name FROM Employees JOIN Departments WHERE Employees.dept_id = Departments.dept_id;

medium

A. WHERE cannot be used with JOIN.

B. SELECT cannot have multiple columns.

C. Table names are incorrect.

D. Missing ON keyword for JOIN condition.

Solution

1. Step 1: Check JOIN syntax

   JOIN requires ON keyword to specify join condition, not WHERE.

2. Step 2: Understand WHERE usage

   WHERE filters rows after join; join condition must be in ON clause.

3. Final Answer:

   Missing ON keyword for JOIN condition. -> Option D

4. Quick Check:

   JOIN needs ON for condition = D [OK]

Hint: JOIN condition must use ON, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Thinking SELECT can't have multiple columns
• Assuming table names are wrong

5. You have three tables:
Orders(order_id, customer_id, product_id)
Customers(customer_id, customer_name)
Products(product_id, product_name)
How would you write a query to list each order with the customer name and product name?

hard

A. SELECT order_id, customer_name, product_name FROM Orders JOIN Customers ON Orders.customer_id = Customers.customer_id JOIN Products ON Orders.product_id = Products.product_id;

B. SELECT order_id, customer_name, product_name FROM Orders, Customers, Products WHERE Orders.customer_id = Customers.customer_id;

C. SELECT order_id, customer_name, product_name FROM Orders LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id;

D. SELECT order_id, customer_name, product_name FROM Customers JOIN Products ON Customers.customer_id = Products.product_id;

Solution

1. Step 1: Identify needed joins

   Orders must join Customers on customer_id and Products on product_id to get names.

2. Step 2: Write correct JOIN syntax

   Use JOIN with ON for both tables to link properly.

3. Step 3: Check other options

   B misses the product_id join condition; C misses Products join; D joins unrelated keys.

4. Final Answer:

   SELECT order_id, customer_name, product_name FROM Orders JOIN Customers ON Orders.customer_id = Customers.customer_id JOIN Products ON Orders.product_id = Products.product_id; -> Option A

5. Quick Check:

   Correct JOINs on keys = A [OK]

Hint: Join all related tables on keys using ON [OK]

Common Mistakes:

• Missing one join to include all data
• Joining on wrong columns
• Using WHERE instead of ON for joins