One-to-many relationship design in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a JOIN on one-to-many tables

Consider two tables: Authors and Books. Each author can write many books. The tables are:

Authors(id, name)
Books(id, author_id, title)

What is the output of this query?

SELECT Authors.name, Books.title
FROM Authors
JOIN Books ON Authors.id = Books.author_id
WHERE Authors.id = 1
ORDER BY Books.id;

SQL

CREATE TABLE Authors (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Books (id INT PRIMARY KEY, author_id INT, title VARCHAR(100));

INSERT INTO Authors VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Books VALUES (1, 1, 'Book A1'), (2, 1, 'Book A2'), (3, 2, 'Book B1');

A [{"name": "Alice", "title": "Book A1"}, {"name": "Alice", "title": "Book A2"}]

B [{"name": "Alice", "title": "Book A1"}, {"name": "Bob", "title": "Book A2"}]

C [{"name": "Alice", "title": "Book A1"}]

D [{"name": "Bob", "title": "Book B1"}]

💡 Hint

Remember that JOIN matches rows where the foreign key matches the primary key.

The JOIN matches all books where author_id equals the author's id. For author 1 (Alice), there are two books, so two rows are returned.

🧠 Conceptual

intermediate

Identifying the foreign key in one-to-many design

In a one-to-many relationship between Customers and Orders, which table should contain the foreign key?

A Orders table should have a foreign key referencing Customers.

B Both tables should have foreign keys referencing each other.

C Customers table should have a foreign key referencing Orders.

D Neither table needs a foreign key in one-to-many relationships.

💡 Hint

Think about which side 'belongs to' the other.

In one-to-many, the 'many' side (Orders) holds the foreign key referencing the 'one' side (Customers).

📝 Syntax

advanced

Correct foreign key constraint syntax

Which of the following SQL statements correctly creates a foreign key constraint for a one-to-many relationship where Orders.customer_id references Customers.id?

A ALTER TABLE Orders ADD FOREIGN KEY (customer_id) TO Customers(id);

B ALTER TABLE Orders ADD FOREIGN KEY (customer_id) REFERENCES Customers(id);

C ALTER TABLE Orders ADD FOREIGN KEY customer_id REFERENCES Customers(id);

D ALTER TABLE Customers ADD FOREIGN KEY (id) REFERENCES Orders(customer_id);

💡 Hint

Check the syntax for adding foreign keys in SQL.

Option B uses the correct syntax: ALTER TABLE table_name ADD FOREIGN KEY (column) REFERENCES other_table(column);

❓ optimization

advanced

Improving query performance on one-to-many joins

You have large tables Authors and Books with a one-to-many relationship. Which indexing strategy improves the performance of this query?

SELECT Authors.name, Books.title
FROM Authors
JOIN Books ON Authors.id = Books.author_id
WHERE Authors.name LIKE 'A%';

A Create an index on Books.id and Authors.name.

B Create an index only on Authors.id.

C Create an index on Books.author_id and Authors.name.

D Create an index only on Books.title.

💡 Hint

Indexes on columns used in JOIN and WHERE clauses help performance.

Indexing Books.author_id speeds up the JOIN. Indexing Authors.name speeds up the WHERE filter.

🔧 Debug

expert

Diagnosing missing rows in one-to-many join

Given these tables:

Customers(id, name)
Orders(id, customer_id, amount)

You run this query:

SELECT Customers.name, Orders.amount
FROM Customers
LEFT JOIN Orders ON Customers.id = Orders.customer_id
WHERE Orders.amount > 100;

Why might some customers with no orders appear missing from the result?

A Customers with no orders have Orders.amount = 0, so they are filtered out.

B LEFT JOIN does not include customers without orders by default.

C The JOIN condition is incorrect; it should be ON Orders.customer_id = Customers.id.

D The WHERE clause filters out rows where Orders.amount is NULL, removing customers without orders.

💡 Hint

Think about how WHERE affects LEFT JOIN results.

The WHERE clause Orders.amount > 100 excludes rows where Orders.amount is NULL (no matching order), so customers without orders are removed from the result.

Practice

1. What does a one-to-many relationship in a database mean?

easy

A. One record in a table relates to many records in another table

B. Many records in a table relate to one record in the same table

C. One record relates to exactly one record in another table

D. Many records relate to many records in another table

Solution

1. Step 1: Understand relationship types

   A one-to-many relationship means one record in a table connects to multiple records in another table.

2. Step 2: Match definition to options

   One record in a table relates to many records in another table correctly describes this as one record relating to many records in another table.

3. Final Answer:

   One record in a table relates to many records in another table -> Option A

4. Quick Check:

   One-to-many = one record to many records [OK]

Hint: One-to-many means one record links to many records [OK]

Common Mistakes:

• Confusing one-to-many with many-to-many
• Thinking one-to-many means one record links to one record
• Mixing up the direction of the relationship

2. Which SQL statement correctly creates a foreign key for a one-to-many relationship from Orders to Customers?

easy

A. ALTER TABLE Customers ADD FOREIGN KEY (OrderID) REFERENCES Orders(OrderID);

B. ALTER TABLE Customers ADD FOREIGN KEY (CustomerID) REFERENCES Orders(OrderID);

C. ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

D. ALTER TABLE Orders ADD FOREIGN KEY (OrderID) REFERENCES Customers(CustomerID);

Solution

1. Step 1: Identify the 'many' and 'one' tables

   Orders is the 'many' side, Customers is the 'one' side in a one-to-many relationship.

2. Step 2: Add foreign key in 'many' table referencing 'one' table

   The foreign key should be in Orders referencing Customers, so ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); is correct.

3. Final Answer:

   ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); -> Option C

4. Quick Check:

   Foreign key in 'many' table references 'one' table [OK]

Hint: Foreign key goes in 'many' table pointing to 'one' table [OK]

Common Mistakes:

• Placing foreign key in the 'one' table instead of 'many'
• Referencing wrong columns between tables
• Mixing table names in foreign key definition

3. Given these tables:
Customers(CustomerID, Name)
Orders(OrderID, CustomerID, Amount)
What will this query return?

SELECT Customers.Name, COUNT(Orders.OrderID) AS OrderCount FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.Name;

medium

A. List of customers who have placed at least one order

B. List of customers with total amount spent on orders

C. List of orders with customer names repeated for each order

D. List of customers with the number of orders each placed, including customers with zero orders

Solution

1. Step 1: Understand the LEFT JOIN usage

   LEFT JOIN keeps all customers, even those without matching orders.

2. Step 2: COUNT(Orders.OrderID) counts orders per customer

   Grouping by customer name counts how many orders each customer has, zero if none.

3. Final Answer:

   List of customers with the number of orders each placed, including customers with zero orders -> Option D

4. Quick Check:

   LEFT JOIN + COUNT = all customers with order counts [OK]

Hint: LEFT JOIN + COUNT counts all, including zero matches [OK]

Common Mistakes:

• Thinking COUNT counts total amount spent
• Assuming only customers with orders appear
• Confusing JOIN types and their effects

4. You wrote this SQL to create a one-to-many relationship:

CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

But you get an error. What is the most likely cause?

medium

A. OrderID should not be primary key in Orders

B. Customers table does not exist or CustomerID is not a primary key

C. Foreign key should be in Customers table, not Orders

D. CustomerID column type must be VARCHAR, not INT

Solution

1. Step 1: Check foreign key reference validity

   Foreign key must reference an existing table and a primary or unique key column.

2. Step 2: Verify Customers table and CustomerID key

   If Customers table or CustomerID primary key is missing, error occurs.

3. Final Answer:

   Customers table does not exist or CustomerID is not a primary key -> Option B

4. Quick Check:

   Foreign key references must exist and be keys [OK]

Hint: Foreign key target must exist and be primary/unique key [OK]

Common Mistakes:

• Assuming foreign key can reference non-key columns
• Placing foreign key in wrong table
• Mismatching data types between foreign key and referenced key

5. You have two tables:
Authors(AuthorID, Name)
Books(BookID, Title, AuthorID)
You want to find authors who have written more than 3 books. Which query is correct?

hard

A. SELECT Name FROM Authors JOIN Books ON Authors.AuthorID = Books.AuthorID GROUP BY Name HAVING COUNT(BookID) > 3;

B. SELECT Name FROM Authors LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID WHERE COUNT(BookID) > 3;

C. SELECT Name FROM Books GROUP BY AuthorID HAVING COUNT(BookID) > 3;

D. SELECT Name FROM Authors WHERE AuthorID IN (SELECT AuthorID FROM Books WHERE COUNT(BookID) > 3);

Solution

1. Step 1: Join Authors and Books on AuthorID

   We join to connect authors with their books.

2. Step 2: Group by author name and filter by book count

   Use GROUP BY Name and HAVING COUNT(BookID) > 3 to find authors with more than 3 books.

3. Final Answer:

   SELECT Name FROM Authors JOIN Books ON Authors.AuthorID = Books.AuthorID GROUP BY Name HAVING COUNT(BookID) > 3; -> Option A

4. Quick Check:

   JOIN + GROUP BY + HAVING filters authors by book count [OK]

Hint: Use GROUP BY and HAVING to filter by count in one-to-many [OK]

Common Mistakes:

• Using WHERE with aggregate functions instead of HAVING
• Missing GROUP BY clause
• Using LEFT JOIN but filtering with WHERE on aggregate