Why query patterns matter in SQL - See It in Action

Why Query Patterns Matter

📖 Scenario: You are working as a data analyst for a small online bookstore. You need to organize and retrieve information about books and their sales efficiently. Understanding how to write clear and effective SQL queries will help you get the right data quickly and avoid mistakes.

🎯 Goal: Build a simple database with books and sales tables, then write queries using good patterns to retrieve useful information about book sales.

📋 What You'll Learn

Create a books table with columns book_id, title, and author.

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

Write a query to find the total quantity sold for each book using a proper JOIN and GROUP BY pattern.

Write a query to find books that have no sales using a LEFT JOIN pattern.

💡 Why This Matters

🌍 Real World

In real businesses, writing clear and efficient SQL queries helps get the right data fast, which supports good decisions.

💼 Career

Data analysts and developers use these query patterns daily to combine and summarize data from multiple tables.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns book_id (integer), title (text), and author (text). Then insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee').

SQL

-- Create the 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

Write SQL statements to create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Then insert these exact rows: (1, 1, 3), (2, 2, 5), (3, 1, 2).

SQL

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

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

Need a hint?

Use CREATE TABLE and INSERT INTO like before, matching the exact column names and values.

3

Write a query to find total quantity sold per book

Write a SQL query that uses JOIN to combine books and sales tables on book_id. Use GROUP BY on books.book_id and books.title to find the total quantity sold for each book. Select books.title and the sum of sales.quantity as total_sold.

SQL

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  book_id INTEGER,
  quantity INTEGER
);

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

-- Write the query to find total quantity sold per book
-- Your code here

Need a hint?

Use JOIN to connect tables and GROUP BY to sum quantities per book.

4

Write a query to find books with no sales

Write a SQL query that uses LEFT JOIN to join books with sales on book_id. Select books.title for books where sales.book_id is NULL, meaning no sales exist for that book.

SQL

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  book_id INTEGER,
  quantity INTEGER
);

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

SELECT books.title, SUM(sales.quantity) AS total_sold
FROM books
JOIN sales ON books.book_id = sales.book_id
GROUP BY books.book_id, books.title;

-- Write the query to find books with no sales
-- Your code here

Need a hint?

Use LEFT JOIN and check for NULL in the joined table to find books without sales.