SQL NULL Comparison — Why = NULL Returns Zero Rows

-- ============================================================
-- 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;

-- ============================================================
-- Joining books and authors to show book title + author name.
-- INNER JOIN = only rows where the match exists in both tables.
-- ============================================================

-- Basic INNER JOIN: get every book with its author's full name.
SELECT books.title,
       authors.full_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

-- Add sorting to see them in a nice order.
SELECT books.title AS "Book Title",
       authors.full_name AS "Author"
FROM books
INNER JOIN authors ON books.author_id = authors.author_id
ORDER BY authors.full_name ASC;

-- Use table aliases to keep the query readable.
SELECT b.title,
       a.full_name
FROM books   AS b
INNER JOIN authors AS a ON b.author_id = a.author_id;

-- ============================================================
-- Adding, updating, and deleting data in our bookstore.
-- Run each block separately and verify with SELECT afterward.
-- ============================================================

-- 1. INSERT a new author.
INSERT INTO authors (author_id, full_name, country)
VALUES (5, 'Elena Ferrante', 'Italy');

-- 2. INSERT multiple books at once.
INSERT INTO books (book_id, title, author_id, genre, price, published)
VALUES
    (7, 'My Brilliant Friend',       5, 'Literary Fiction',  14.99, 2012),
    (8, 'The Story of a New Name',   5, 'Literary Fiction',  14.99, 2013);

-- 3. UPDATE the price of all books by a specific author (author_id = 1).
-- FIRST, preview which rows will be updated:
SELECT book_id, title, price
FROM books
WHERE author_id = 1;

-- THEN, run the UPDATE (10% price increase for George Orwell's books).
UPDATE books
SET price = price * 1.10
WHERE author_id = 1;

-- 4. DELETE a specific book by its ID.
-- Preview first:
SELECT * FROM books WHERE book_id = 8;

-- Then delete:
DELETE FROM books
WHERE book_id = 8;

-- 5. Danger: UPDATE without WHERE updates ALL rows.
-- Uncomment the next line only if you really mean it.
-- UPDATE books SET price = 0.00;