One-to-many relationship design in SQL - Mini Project: Build & Apply

One-to-many relationship design

📖 Scenario: You are building a simple database for a library. Each author can write many books, but each book has only one author. You want to organize this data so you can easily find all books by a specific author.

🎯 Goal: Create two tables, Authors and Books, with a one-to-many relationship where each book references its author. Then insert sample data and write a query to list all books with their authors.

📋 What You'll Learn

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

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

Insert exactly two authors: AuthorID 1 with Name 'Jane Austen' and AuthorID 2 with Name 'Mark Twain'.

Insert exactly three books: 'Pride and Prejudice' by Jane Austen, 'Emma' by Jane Austen, and 'Adventures of Huckleberry Finn' by Mark Twain.

Write a query to select the book Title and the author's Name by joining the two tables.

💡 Why This Matters

🌍 Real World

One-to-many relationships are common in databases, such as customers and orders, authors and books, or teachers and students.

💼 Career

Understanding how to design and query one-to-many relationships is essential for database developers, data analysts, and backend engineers.

1

Create the Authors table

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

SQL

-- Create the Authors table with AuthorID as primary key and Name as text
-- Your code here

Need a hint?

Use CREATE TABLE with the column definitions including PRIMARY KEY for AuthorID.

2

Create the Books table with foreign key

Write a SQL statement to create a table called Books with columns BookID as an integer primary key, Title as text, and AuthorID as an integer foreign key referencing Authors.AuthorID.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);
-- Create the Books table with BookID as primary key, Title as text, and AuthorID as foreign key
-- Your code here

Need a hint?

Remember to add FOREIGN KEY constraint for AuthorID referencing Authors table.

3

Insert sample data into Authors and Books

Insert two authors into Authors: (1, 'Jane Austen') and (2, 'Mark Twain'). Then insert three books into Books: 'Pride and Prejudice' with AuthorID 1, 'Emma' with AuthorID 1, and 'Adventures of Huckleberry Finn' with AuthorID 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)
);
-- Insert authors and books data
-- Your code here

Need a hint?

Use INSERT INTO with exact values for both tables.

4

Query books with their authors

Write a SQL query to select the Title of each book and the Name of its author by joining the Books and Authors tables on AuthorID.

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)
);

INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Jane Austen');
INSERT INTO Authors (AuthorID, Name) VALUES (2, 'Mark Twain');

INSERT INTO Books (BookID, Title, AuthorID) VALUES (1, 'Pride and Prejudice', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (2, 'Emma', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (3, 'Adventures of Huckleberry Finn', 2);
-- Write a SELECT query joining Books and Authors to get Title and Name
-- Your code here

Need a hint?

Use JOIN to connect Books and Authors on AuthorID and select Title and Name.