SQL for Beginners: Query, Filter and Manage Data From Scratch

-- ============================================================
-- We're building a tiny bookstore database from scratch.
-- Two tables: 'authors' and 'books'.
-- Run this in any SQL environment (SQLite, MySQL, PostgreSQL).
-- ============================================================

-- Create the authors table first, because books will reference it.
CREATE TABLE authors (
    author_id   INTEGER PRIMARY KEY,   -- unique identifier for each author
    full_name   TEXT    NOT NULL,       -- author's full name; NOT NULL means it's required
    country     TEXT                    -- country of origin; optional field
);

-- Create the books table.
CREATE TABLE books (
    book_id     INTEGER PRIMARY KEY,
    title       TEXT    NOT NULL,
    author_id   INTEGER NOT NULL,       -- links back to the authors table
    genre       TEXT,
    price       REAL    NOT NULL,       -- REAL means a decimal number (e.g. 12.99)
    published   INTEGER                -- year published, stored as a number
);

-- Insert four authors into the authors table.
INSERT INTO authors (author_id, full_name, country) VALUES
    (1, 'George Orwell',        'United Kingdom'),
    (2, 'Gabriel García Márquez', 'Colombia'),
    (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
    (4, 'Haruki Murakami',      'Japan');

-- Insert six books into the books table.
INSERT INTO books (book_id, title, author_id, genre, price, published) VALUES
    (1, 'Nineteen Eighty-Four',         1, 'Dystopian',  9.99,  1949),
    (2, 'Animal Farm',                  1, 'Satire',     7.49,  1945),
    (3, 'One Hundred Years of Solitude',2, 'Magical Realism', 12.99, 1967),
    (4, 'Americanah',                   3, 'Literary Fiction', 10.99, 2013),
    (5, 'Purple Hibiscus',              3, 'Literary Fiction',  8.99, 2003),
    (6, 'Norwegian Wood',               4, 'Literary Fiction', 11.50, 1987);

-- ============================================================
-- Querying the bookstore database we created in the last step.
-- Run each query block one at a time to see the output.
-- ============================================================

-- Query 1: Get every column from every row in the books table.
-- Good for exploration, but avoid SELECT * in real apps.
SELECT * FROM books;

-- Query 2: Get only the title and price — we don't need everything.
SELECT title, price
FROM books;

-- Query 3: Filter with WHERE — only books cheaper than $10.00
SELECT title, price
FROM books
WHERE price < 10.00;

-- Query 4: Combine conditions with AND
-- Only books under $10 that were also published after 1980
SELECT title, price, published
FROM books
WHERE price < 10.00
  AND published > 1980;

-- Query 5: Use OR — books that are either Satire OR Dystopian genre
SELECT title, genre
FROM books
WHERE genre = 'Satire'
   OR genre = 'Dystopian';

-- Query 6: A tidier way to write the OR above, using IN
-- IN checks if a value matches any item in a list
SELECT title, genre
FROM books
WHERE genre IN ('Satire', 'Dystopian');

-- ============================================================
-- Sorting, limiting and renaming output columns.
-- These techniques are used in virtually every real-world query.
-- ============================================================

-- Query 1: Sort all books by price, cheapest first (ASC is default).
SELECT title, price
FROM books
ORDER BY price ASC;

-- Query 2: Sort by price, most expensive first.
SELECT title, price
FROM books
ORDER BY price DESC;

-- Query 3: The 3 most expensive books — combine ORDER BY with LIMIT.
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;

-- Query 4: Sort by genre alphabetically, then by price within each genre.
-- This is multi-column sorting.
SELECT title, genre, price
FROM books
ORDER BY genre ASC, price ASC;

-- Query 5: Use AS to give columns friendlier names in the output.
-- 'title' becomes 'Book Title', 'price' becomes 'Price (USD)'.
-- Notice: multi-word aliases need to be wrapped in double quotes.
SELECT title   AS "Book Title",
       price   AS "Price (USD)",
       published AS "Year"
FROM books
ORDER BY price DESC
LIMIT 4;

-- ============================================================
-- Aggregate functions and GROUP BY on our bookstore data.
-- This is where SQL starts to feel genuinely powerful.
-- ============================================================

-- Query 1: How many books are in the entire table?
-- COUNT(*) counts every row regardless of NULL values.
SELECT COUNT(*) AS total_books
FROM books;

-- Query 2: What is the average price of all books?
SELECT ROUND(AVG(price), 2) AS average_price
FROM books;
-- ROUND(value, 2) rounds the result to 2 decimal places.

-- Query 3: Most expensive and cheapest single book.
SELECT MAX(price) AS most_expensive,
       MIN(price) AS cheapest
FROM books;

-- Query 4: How many books does each author have?
-- GROUP BY splits the table into one group per author_id,
-- then COUNT(*) counts the rows in each group.
SELECT author_id,
       COUNT(*) AS number_of_books
FROM books
GROUP BY author_id
ORDER BY number_of_books DESC;

-- Query 5: Average price per genre.
SELECT genre,
       COUNT(*)            AS book_count,
       ROUND(AVG(price),2) AS avg_price
FROM books
GROUP BY genre
ORDER BY avg_price DESC;

-- Query 6: HAVING — only show genres that have MORE than 1 book.
-- We cannot use WHERE here because book_count doesn't exist yet
-- at the point WHERE runs. HAVING runs AFTER GROUP BY.
SELECT genre,
       COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING book_count > 1;