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.