Why joins are needed in SQL - See It in Action

Understanding Why Joins Are Needed in SQL

📖 Scenario: Imagine you work at a small bookstore. You have two tables: one lists books with their IDs and titles, and another lists sales with book IDs and quantities sold. You want to see which books sold and how many copies each sold.

🎯 Goal: Build a simple SQL query using a JOIN to combine book titles with their sales quantities.

📋 What You'll Learn

Create a table called books with columns book_id and title.

Create a table called sales with columns sale_id, book_id, and quantity.

Insert specific rows into both tables as given.

Write a SQL query using JOIN to combine books and sales on book_id.

💡 Why This Matters

🌍 Real World

In real businesses, data is often split into multiple tables to keep it organized. Joins let you combine this data to answer questions like 'Which products sold the most?'

💼 Career

Understanding joins is essential for database work, data analysis, and backend development where combining data from multiple sources is common.

1

Create the books table and insert data

Create a table called books with columns book_id (integer) and title (text). Insert these rows exactly: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird').

SQL

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

Need a hint?

Use CREATE TABLE to make the table, then 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), and quantity (integer). Insert these rows exactly: (1, 1, 5), (2, 2, 3), (3, 1, 2).

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

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

Need a hint?

Use CREATE TABLE and INSERT INTO like before, but with the new columns and values.

3

Write a JOIN query to combine books and sales

Write a SQL query that selects title and quantity by joining books and sales on the book_id column using an INNER JOIN.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, quantity INTEGER);
INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 5),
(2, 2, 3),
(3, 1, 2);

-- Write a JOIN query to select title and quantity
-- Your code here

Need a hint?

Use INNER JOIN to combine rows where book_id matches in both tables.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by quantity in descending order.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, quantity INTEGER);
INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 5),
(2, 2, 3),
(3, 1, 2);

SELECT books.title, sales.quantity
FROM books
INNER JOIN sales ON books.book_id = sales.book_id
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY followed by the column name and DESC to sort from highest to lowest.