INNER JOIN execution in PostgreSQL - Mini Project: Build & Apply

INNER JOIN Execution in PostgreSQL

📖 Scenario: You are managing a small online bookstore database. You have two tables: authors and books. Each book has an author_id that links it to an author. You want to find the names of authors along with the titles of their books.

🎯 Goal: Build a SQL query using INNER JOIN to combine the authors and books tables, showing each author's name with their book titles.

📋 What You'll Learn

Create a table called authors with columns author_id (integer) and author_name (text).

Create a table called books with columns book_id (integer), title (text), and author_id (integer).

Insert the exact data provided into both tables.

Write a SQL query using INNER JOIN to select author_name and title from the joined tables.

💡 Why This Matters

🌍 Real World

INNER JOIN is used in databases to combine related data from different tables, such as linking customers to their orders or students to their classes.

💼 Career

Understanding INNER JOIN is essential for database querying roles, data analysis, and backend development where combining data from multiple tables is common.

1

Create the authors and books tables with data

Write SQL statements to create a table called authors with columns author_id (integer) and author_name (text). Then create a table called books with columns book_id (integer), title (text), and author_id (integer). Insert these exact rows into authors: (1, 'Jane Austen'), (2, 'Mark Twain'), (3, 'Charles Dickens'). Insert these exact rows into books: (101, 'Pride and Prejudice', 1), (102, 'Emma', 1), (103, 'Adventures of Huckleberry Finn', 2), (104, 'Oliver Twist', 3).

PostgreSQL

-- Create authors table
-- Create books table
-- Insert data into authors
-- Insert data into books

Need a hint?

Use CREATE TABLE statements to define the tables. Use INSERT INTO with multiple rows to add the data.

2

Set up a variable for the join condition

Write a SQL comment that explains the join condition between authors.author_id and books.author_id. This will help you remember how the tables are connected.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

CREATE TABLE books (
    book_id INTEGER,
    title TEXT,
    author_id INTEGER
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Emma', 1),
(103, 'Adventures of Huckleberry Finn', 2),
(104, 'Oliver Twist', 3);

-- Your join condition comment here

Need a hint?

Write a comment starting with -- that clearly states the join condition.

3

Write the INNER JOIN query

Write a SQL query that uses INNER JOIN to select author_name and title from the authors and books tables. Use the join condition authors.author_id = books.author_id.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

CREATE TABLE books (
    book_id INTEGER,
    title TEXT,
    author_id INTEGER
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Emma', 1),
(103, 'Adventures of Huckleberry Finn', 2),
(104, 'Oliver Twist', 3);

-- Write INNER JOIN query below

Need a hint?

Use SELECT to choose columns, FROM for the first table, and INNER JOIN with ON for the join condition.

4

Complete the query with ordering

Add an ORDER BY clause to the existing query to sort the results by author_name in ascending order.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

CREATE TABLE books (
    book_id INTEGER,
    title TEXT,
    author_id INTEGER
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Emma', 1),
(103, 'Adventures of Huckleberry Finn', 2),
(104, 'Oliver Twist', 3);

-- Add ORDER BY clause below

SELECT authors.author_name, books.title
FROM authors
INNER JOIN books ON authors.author_id = books.author_id

Need a hint?

Use ORDER BY authors.author_name ASC at the end of the query to sort results alphabetically by author name.