INNER JOIN with ON condition in SQL - Mini Project: Build & Apply

INNER JOIN with ON condition

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

🎯 Goal: Build an SQL query that uses INNER JOIN with an ON condition to combine the Books and Authors tables, showing book titles and author names.

📋 What You'll Learn

Create a Books table with columns book_id, title, and author_id.

Create an Authors table with columns author_id and author_name.

Write an INNER JOIN query joining Books and Authors on author_id.

Select the title from Books and author_name from Authors.

💡 Why This Matters

🌍 Real World

Joining tables is common in databases to combine related information, like linking books to their authors.

💼 Career

Understanding INNER JOIN with ON condition is essential for database querying in roles like data analyst, backend developer, and database administrator.

1

Create the Books table with sample data

Write SQL statements to create a table called Books with columns book_id (integer), title (text), and author_id (integer). Insert these exact rows into Books: (1, 'The Great Gatsby', 101), (2, '1984', 102), (3, 'To Kill a Mockingbird', 103).

SQL

-- Create Books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the Authors table with sample data

Write SQL statements to create a table called Authors with columns author_id (integer) and author_name (text). Insert these exact rows into Authors: (101, 'F. Scott Fitzgerald'), (102, 'George Orwell'), (103, 'Harper Lee').

SQL

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

INSERT INTO Books (book_id, title, author_id) VALUES
  (1, 'The Great Gatsby', 101),
  (2, '1984', 102),
  (3, 'To Kill a Mockingbird', 103);

-- Create Authors table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO similar to Step 1.

3

Write the INNER JOIN query with ON condition

Write an SQL SELECT query that uses INNER JOIN to join Books and Authors on the condition that Books.author_id = Authors.author_id. Select the columns Books.title and Authors.author_name.

SQL

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

INSERT INTO Books (book_id, title, author_id) VALUES
  (1, 'The Great Gatsby', 101),
  (2, '1984', 102),
  (3, 'To Kill a Mockingbird', 103);

CREATE TABLE Authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO Authors (author_id, author_name) VALUES
  (101, 'F. Scott Fitzgerald'),
  (102, 'George Orwell'),
  (103, 'Harper Lee');

-- Write INNER JOIN query to select title and author_name
-- Your code here

Need a hint?

Use INNER JOIN Authors ON Books.author_id = Authors.author_id to join the tables.

4

Complete the query with ordering

Add an ORDER BY clause to the previous query to sort the results by Books.title in ascending order.

SQL

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

INSERT INTO Books (book_id, title, author_id) VALUES
  (1, 'The Great Gatsby', 101),
  (2, '1984', 102),
  (3, 'To Kill a Mockingbird', 103);

CREATE TABLE Authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO Authors (author_id, author_name) VALUES
  (101, 'F. Scott Fitzgerald'),
  (102, 'George Orwell'),
  (103, 'Harper Lee');

SELECT Books.title, Authors.author_name
FROM Books
INNER JOIN Authors ON Books.author_id = Authors.author_id
-- Add ORDER BY clause to sort by title
-- Your code here

Need a hint?

Use ORDER BY Books.title ASC to sort the results alphabetically by title.