Why understanding relationships matters in SQL - See It in Action

Understanding Relationships in a Library Database

📖 Scenario: You are helping a local library organize its database. The library keeps track of books and authors. Each book can have one author, and each author can write many books. Understanding how these two tables relate helps the library find information quickly.

🎯 Goal: Build a simple database structure with two tables, Authors and Books, and create a relationship between them using a foreign key. This will help you see how data in one table connects to data in another.

📋 What You'll Learn

Create a table called Authors with columns AuthorID (integer, primary key) and Name (text).

Create a table called Books with columns BookID (integer, primary key), Title (text), and AuthorID (integer).

Add a foreign key constraint on Books.AuthorID referencing Authors.AuthorID.

Insert sample data into both tables to demonstrate the relationship.

💡 Why This Matters

🌍 Real World

Libraries, stores, and many businesses use relationships in databases to keep data organized and connected.

💼 Career

Database designers and developers must understand relationships to build efficient and reliable data systems.

1

Create the Authors table

Write SQL code to create a table called Authors with two columns: AuthorID as an integer primary key, and Name as text.

SQL

-- Your code here to create the Authors table

Hint

Use CREATE TABLE statement with AuthorID as the primary key.

2

Create the Books table with AuthorID column

Write SQL code to create a table called Books with three columns: BookID as an integer primary key, Title as text, and AuthorID as an integer.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);
-- Your code here to create the Books table

Hint

Remember to include AuthorID as an integer column to link to authors.

3

Add foreign key constraint to Books table

Modify the Books table creation SQL to add a foreign key constraint on AuthorID that references Authors.AuthorID.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    -- Your code here to add foreign key constraint
);

Hint

Use FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) inside the Books table definition.

4

Insert sample data into Authors and Books

Insert these exact rows into the tables: into Authors, insert (1, 'Jane Austen') and (2, 'Mark Twain'). Into Books, insert (101, 'Pride and Prejudice', 1) and (102, 'Adventures of Huckleberry Finn', 2).

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);
-- Your code here to insert sample data

Hint

Use INSERT INTO statements with the exact values given.

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