INNER JOIN with multiple conditions in SQL - Mini Project: Build & Apply

INNER JOIN with multiple conditions

📖 Scenario: You are managing a small bookstore database. You have two tables: Books and Sales. You want to find the sales details for books that match both the book ID and the store location.

🎯 Goal: Build an SQL query using INNER JOIN with multiple conditions to combine the Books and Sales tables on book_id and store_location.

📋 What You'll Learn

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

Create a Sales table with columns sale_id, book_id, store_location, and quantity.

Insert the exact data rows provided into both tables.

Write an INNER JOIN query joining Books and Sales on both book_id and store_location.

Select title, store_location, and quantity from the joined tables.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use databases to track inventory and sales across multiple locations. Joining tables on multiple conditions helps get accurate combined data.

💼 Career

Knowing how to write INNER JOIN queries with multiple conditions is essential for data analysts, database administrators, and backend developers working with relational databases.

1

Create the Books table and insert data

Create a table called Books with columns book_id (integer), title (text), and store_location (text). Insert these rows exactly: (1, 'SQL Basics', 'Downtown'), (2, 'Advanced SQL', 'Uptown'), (3, 'Database Design', 'Downtown').

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 Sales table and insert data

Create a table called Sales with columns sale_id (integer), book_id (integer), store_location (text), and quantity (integer). Insert these rows exactly: (101, 1, 'Downtown', 5), (102, 2, 'Uptown', 3), (103, 3, 'Downtown', 7), (104, 1, 'Uptown', 2).

SQL

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

INSERT INTO Books (book_id, title, store_location) VALUES
(1, 'SQL Basics', 'Downtown'),
(2, 'Advanced SQL', 'Uptown'),
(3, 'Database Design', 'Downtown');

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

Need a hint?

Define the Sales table with the specified columns and insert the rows exactly.

3

Write INNER JOIN query with multiple conditions

Write an SQL query that uses INNER JOIN to join Books and Sales on both book_id and store_location. Select title, store_location, and quantity from the joined tables.

SQL

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

INSERT INTO Books (book_id, title, store_location) VALUES
(1, 'SQL Basics', 'Downtown'),
(2, 'Advanced SQL', 'Uptown'),
(3, 'Database Design', 'Downtown');

CREATE TABLE Sales (
    sale_id INTEGER,
    book_id INTEGER,
    store_location TEXT,
    quantity INTEGER
);

INSERT INTO Sales (sale_id, book_id, store_location, quantity) VALUES
(101, 1, 'Downtown', 5),
(102, 2, 'Uptown', 3),
(103, 3, 'Downtown', 7),
(104, 1, 'Uptown', 2);

-- Write INNER JOIN query with multiple conditions
-- Your code here

Need a hint?

Use INNER JOIN with ON clause containing both conditions joined by AND.

4

Complete the query with aliasing

Modify the previous query to use table aliases b for Books and s for Sales. Select b.title, b.store_location, and s.quantity using these aliases.

SQL

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

INSERT INTO Books (book_id, title, store_location) VALUES
(1, 'SQL Basics', 'Downtown'),
(2, 'Advanced SQL', 'Uptown'),
(3, 'Database Design', 'Downtown');

CREATE TABLE Sales (
    sale_id INTEGER,
    book_id INTEGER,
    store_location TEXT,
    quantity INTEGER
);

INSERT INTO Sales (sale_id, book_id, store_location, quantity) VALUES
(101, 1, 'Downtown', 5),
(102, 2, 'Uptown', 3),
(103, 3, 'Downtown', 7),
(104, 1, 'Uptown', 2);

-- Modify query to use aliases b and s
-- Your code here

Need a hint?

Use b and s as aliases for the tables and update the SELECT and ON clauses accordingly.