Many-to-many with junction tables in SQL - Mini Project: Build & Apply

Many-to-many with junction tables

📖 Scenario: You are building a simple database for a library. Books can have multiple authors, and authors can write multiple books. To represent this many-to-many relationship, you will use a junction table.

🎯 Goal: Create tables for Books and Authors, then create a junction table called BookAuthors to link books and authors. Insert sample data and write a query to find all authors for a specific book.

📋 What You'll Learn

Create a Books table with columns BookID (primary key) and Title.

Create an Authors table with columns AuthorID (primary key) and Name.

Create a junction table BookAuthors with columns BookID and AuthorID to link books and authors.

Insert at least two books and three authors with appropriate links in BookAuthors.

Write a query to select all author names for the book titled 'The Great Adventure'.

💡 Why This Matters

🌍 Real World

Many real-world databases use many-to-many relationships, such as students enrolled in multiple courses or products with multiple tags.

💼 Career

Understanding how to model and query many-to-many relationships is essential for database design and is a common task in software development and data analysis jobs.

1

Create Books and Authors tables

Write SQL statements to create a table called Books with columns BookID as an integer primary key and Title as text. Also create a table called Authors with columns AuthorID as an integer primary key and Name as text.

SQL

-- Create Books table
-- Create Authors table

Need a hint?

Use CREATE TABLE statements with the specified columns and types.

2

Create the junction table BookAuthors

Write a SQL statement to create a junction table called BookAuthors with columns BookID and AuthorID. Both columns should be integers and together form the primary key. This table links books and authors.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT
);

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

-- Create BookAuthors junction table

Need a hint?

Define both columns as integers and set a composite primary key on both.

3

Insert sample data into Books, Authors, and BookAuthors

Insert two books into Books: (1, 'The Great Adventure') and (2, 'Mystery of the Night'). Insert three authors into Authors: (1, 'Alice Smith'), (2, 'Bob Johnson'), and (3, 'Carol Lee'). Then insert links into BookAuthors: book 1 with authors 1 and 2, and book 2 with author 3.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT
);

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

CREATE TABLE BookAuthors (
    BookID INTEGER,
    AuthorID INTEGER,
    PRIMARY KEY (BookID, AuthorID)
);

-- Insert sample data into Books, Authors, and BookAuthors

Need a hint?

Use INSERT INTO statements with the exact values given.

4

Query authors for 'The Great Adventure'

Write a SQL query to select the Name of all authors who wrote the book titled 'The Great Adventure'. Use the tables Books, Authors, and BookAuthors with appropriate joins.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT
);

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

CREATE TABLE BookAuthors (
    BookID INTEGER,
    AuthorID INTEGER,
    PRIMARY KEY (BookID, AuthorID)
);

INSERT INTO Books (BookID, Title) VALUES (1, 'The Great Adventure'), (2, 'Mystery of the Night');

INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Alice Smith'), (2, 'Bob Johnson'), (3, 'Carol Lee');

INSERT INTO BookAuthors (BookID, AuthorID) VALUES (1, 1), (1, 2), (2, 3);

-- Write query to find all authors for 'The Great Adventure'

Need a hint?

Use JOINs to connect the three tables and filter by the book title.