Joining on primary key to foreign key in SQL - Mini Project: Build & Apply

Joining on primary key to foreign key

📖 Scenario: You work at a small bookstore. The store keeps two tables: one for authors and one for books. Each book is written by one author. You want to combine information from both tables to see the book titles along with their authors' names.

🎯 Goal: Create two tables, authors and books, with a primary key and a foreign key relationship. Then write a SQL query to join these tables on the author's ID to list each book with its author's name.

📋 What You'll Learn

Create a table called authors with columns author_id (primary key) and author_name.

Create a table called books with columns book_id (primary key), title, and author_id (foreign key referencing authors.author_id).

Insert exactly these authors: (1, 'Jane Austen'), (2, 'Mark Twain'), (3, 'J.K. Rowling').

Insert exactly these books: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2), (103, 'Harry Potter and the Sorcerer''s Stone', 3).

Write a SQL query that joins books and authors on author_id to select title and author_name.

💡 Why This Matters

🌍 Real World

Bookstores, libraries, and many businesses use primary key to foreign key joins to combine related data from different tables, like linking books to their authors.

💼 Career

Understanding how to join tables on primary and foreign keys is essential for database querying, data analysis, and backend development roles.

1

Create the authors table and insert data

Write SQL statements to create a table called authors with columns author_id as an integer primary key and author_name as text. Then insert these rows exactly: (1, 'Jane Austen'), (2, 'Mark Twain'), and (3, 'J.K. Rowling').

SQL

-- Create the authors table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE authors (author_id INTEGER PRIMARY KEY, author_name TEXT); to create the table. Use INSERT INTO authors (author_id, author_name) VALUES (...), (...), (...); to add the rows.

2

Create the books table and insert data

Write SQL statements to create a table called books with columns book_id as an integer primary key, title as text, and author_id as an integer foreign key referencing authors.author_id. Then insert these rows exactly: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2), and (103, 'Harry Potter and the Sorcerer''s Stone', 3).

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'J.K. Rowling');

-- Create the books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE books (book_id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER, FOREIGN KEY (author_id) REFERENCES authors(author_id)); to create the table. Use INSERT INTO books (book_id, title, author_id) VALUES (...), (...), (...); to add the rows.

3

Write a SQL query to join books and authors

Write a SQL SELECT query that joins the books and authors tables on the author_id column. Select the title from books and the author_name from authors.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'J.K. Rowling');

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2),
(103, 'Harry Potter and the Sorcerer''s Stone', 3);

-- Write the SELECT query joining books and authors
-- Your code here

Need a hint?

Use SELECT books.title, authors.author_name FROM books JOIN authors ON books.author_id = authors.author_id; to join the tables and select the columns.

4

Complete the project with a comment describing the join

Add a SQL comment above the SELECT query explaining that this query joins the books table to the authors table using the primary key author_id from authors and the foreign key author_id from books.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'J.K. Rowling');

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2),
(103, 'Harry Potter and the Sorcerer''s Stone', 3);

-- Add a comment explaining the join
-- Your code here
SELECT books.title, authors.author_name
FROM books
JOIN authors ON books.author_id = authors.author_id;

Need a hint?

Add a comment line starting with -- before the SELECT query describing the join keys.