-- Show all databases
SHOW DATABASES;

-- Show current date and time
SELECT NOW();

-- Simple calculation
SELECT 2 + 2 AS result;

-- MySQL version
SELECT VERSION();

CREATE DATABASE bookstore;          -- create database
CREATE DATABASE IF NOT EXISTS bookstore; -- safe version
USE bookstore;                       -- switch to it
SHOW TABLES;                         -- list tables in current DB
DROP DATABASE bookstore;             -- delete (irreversible!)

USE bookstore;

CREATE TABLE authors (
    author_id   INT            AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50)    NOT NULL,
    last_name   VARCHAR(50)    NOT NULL,
    email       VARCHAR(120)   UNIQUE,
    country     CHAR(2)        DEFAULT 'US',
    bio         TEXT,
    created_at  TIMESTAMP      DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE books (
    book_id     INT            AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200)   NOT NULL,
    author_id   INT            NOT NULL,
    genre       VARCHAR(50),
    price       DECIMAL(8,2)  NOT NULL,
    stock       INT            DEFAULT 0,
    published   DATE,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
                              ON DELETE CASCADE
);

-- Add a column
ALTER TABLE books ADD COLUMN rating DECIMAL(3,2);

-- Modify column type
ALTER TABLE books MODIFY COLUMN genre VARCHAR(80) NOT NULL;

-- Rename a column
ALTER TABLE books RENAME COLUMN published TO publish_date;

-- Drop a column
ALTER TABLE books DROP COLUMN rating;

-- Show table structure
DESCRIBE books;

USE bookstore;

-- Single row — specify all columns
INSERT INTO authors (first_name, last_name, email, country)
VALUES ('George', 'Orwell', 'gorwell@mail.com', 'GB');

-- Multiple rows in one statement (much faster!)
INSERT INTO authors (first_name, last_name, email, country)
VALUES
    ('J.K.',   'Rowling',  'jkr@mail.com',    'GB'),
    ('Harper', 'Lee',      'hlee@mail.com',   'US'),
    ('Gabriel','Garcia',   'gmarquez@mail.com','CO'),
    ('Agatha', 'Christie', 'achristie@mail.com','GB');

-- Get the auto-generated ID of last insert
SELECT LAST_INSERT_ID();

INSERT INTO books (title, author_id, genre, price, stock, publish_date)
VALUES
    ('1984',                    1, 'Dystopian',   12.99, 150, '1949-06-08'),
    ('Animal Farm',             1, 'Satire',      8.99,  200, '1945-08-17'),
    ("Harry Potter and the PS",  2, 'Fantasy',    14.99, 300, '1997-06-26'),
    ('To Kill a Mockingbird',    3, 'Classic',    11.99, 120, '1960-07-11'),
    ('One Hundred Years',        4, 'Magical Realism',13.99,80,'1967-05-30'),
    ('And Then There Were None', 5, 'Mystery',    9.99,  175, '1939-11-06'),
    ('Murder on the Orient',     5, 'Mystery',    10.99, 160, '1934-01-01');

-- Insert if not exists, update if duplicate key found
INSERT INTO books (book_id, title, price, stock)
VALUES (1, '1984', 12.99, 200)
ON DUPLICATE KEY UPDATE
    stock = stock + VALUES(stock),  -- add 200 to existing stock
    price = VALUES(price);            -- update price

-- Select all columns
SELECT * FROM books;

-- Select specific columns
SELECT title, price, stock FROM books;

-- Aliases — rename columns in output
SELECT
    title             AS 'Book Title',
    price             AS 'Price ($)',
    stock * price     AS 'Inventory Value'
FROM books;

-- Sort by price ascending (default)
SELECT title, price FROM books
ORDER BY price ASC;

-- Sort by price descending
SELECT title, price, stock FROM books
ORDER BY price DESC;

-- Sort by multiple columns
SELECT genre, title, price FROM books
ORDER BY genre ASC, price DESC;

-- Top 3 most expensive books
SELECT title, price FROM books
ORDER BY price DESC
LIMIT 3;

-- Page 2 (rows 4-6) — OFFSET skips rows
SELECT title, price FROM books
ORDER BY price DESC
LIMIT 3 OFFSET 3;   -- skip first 3, return next 3

-- Unique genres in the books table
SELECT DISTINCT genre FROM books
ORDER BY genre;

-- Comparison operators: = != < > <= >=
SELECT title, price FROM books
WHERE price > 12;

-- AND / OR / NOT
SELECT title, genre, price FROM books
WHERE genre = 'Mystery' AND price < 11;

-- BETWEEN is inclusive on both ends
SELECT title, price FROM books
WHERE price BETWEEN 10 AND 13;

-- IN — match any value in a list
SELECT title, genre FROM books
WHERE genre IN ('Mystery', 'Fantasy', 'Dystopian');

-- NOT IN — exclude values
SELECT title, genre FROM books
WHERE genre NOT IN ('Satire', 'Classic');

-- % matches any number of characters (including none)
-- _ matches exactly ONE character

SELECT title FROM books WHERE title LIKE '%Harry%';   -- contains "Harry"
SELECT title FROM books WHERE title LIKE '1%';        -- starts with "1"
SELECT title FROM books WHERE title LIKE '%Farm';     -- ends with "Farm"
SELECT email FROM authors WHERE email LIKE '%@mail.com';

-- RLIKE / REGEXP — full regular expressions
SELECT title FROM books
WHERE title REGEXP '^(Harry|Animal)';

-- ALWAYS use IS NULL / IS NOT NULL — never = NULL
SELECT * FROM authors WHERE bio IS NULL;
SELECT * FROM authors WHERE bio IS NOT NULL;

-- IFNULL — replace NULL with a default
SELECT first_name, IFNULL(bio, 'No bio available') AS bio
FROM authors;

-- COALESCE — returns first non-NULL from a list
SELECT COALESCE(bio, email, 'Unknown') AS contact
FROM authors;

SELECT
    COUNT(*)                  AS total_books,
    COUNT(genre)              AS books_with_genre, -- ignores NULLs
    SUM(stock)                AS total_stock,
    AVG(price)                AS avg_price,
    MIN(price)                AS cheapest,
    MAX(price)                AS most_expensive,
    ROUND(AVG(price), 2)      AS avg_price_rounded
FROM books;

-- Count and total stock per genre
SELECT
    genre,
    COUNT(*) AS num_books,
    SUM(stock) AS total_stock,
    ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
ORDER BY total_stock DESC;

-- WHERE filters rows BEFORE grouping
-- HAVING filters groups AFTER aggregation

SELECT genre, COUNT(*) AS num_books, AVG(price) AS avg_price
FROM books
WHERE stock > 50                     -- filter rows first
GROUP BY genre
HAVING AVG(price) > 10               -- then filter groups
ORDER BY avg_price DESC;

-- Get books with their author names
SELECT
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) AS author,
    b.genre,
    b.price
FROM books AS b
INNER JOIN authors AS a
    ON b.author_id = a.author_id
ORDER BY b.price DESC;

-- All authors, including those with no books
SELECT
    CONCAT(a.first_name, ' ', a.last_name) AS author,
    COUNT(b.book_id) AS book_count,
    IFNULL(SUM(b.price), 0) AS total_value
FROM authors AS a
LEFT JOIN books AS b
    ON a.author_id = b.author_id
GROUP BY a.author_id, a.first_name, a.last_name
ORDER BY book_count DESC;

-- Create customers and orders for the bookstore
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE NOT NULL,
    city        VARCHAR(80)
);
INSERT INTO customers (name, email, city) VALUES
    ('Alice Wang',   'alice@mail.com',   'New York'),
    ('Bob Martinez', 'bob@mail.com',     'Chicago'),
    ('Carol Smith',  'carol@mail.com',   'London');

CREATE TABLE orders (
    order_id    INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    book_id     INT NOT NULL,
    quantity    INT DEFAULT 1,
    order_date  DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (book_id)     REFERENCES books(book_id)
);
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES
    (1, 1, 2, '2024-03-01'),  -- Alice ordered 1984 x2
    (1, 3, 1, '2024-03-02'),  -- Alice ordered Harry Potter
    (2, 4, 3, '2024-03-05'),  -- Bob ordered To Kill x3
    (3, 6, 1, '2024-03-08');  -- Carol ordered Christie

-- 3-table JOIN: Customer + Order + Book + Author
SELECT
    c.name                                            AS customer,
    b.title                                           AS book,
    CONCAT(a.first_name, ' ', a.last_name)         AS author,
    o.quantity,
    b.price,
    o.quantity * b.price                             AS total
FROM orders AS o
INNER JOIN customers AS c  ON o.customer_id = c.customer_id
INNER JOIN books     AS b  ON o.book_id     = b.book_id
INNER JOIN authors  AS a  ON b.author_id   = a.author_id
ORDER BY c.name;

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    name        VARCHAR(60),
    manager_id  INT,  -- references emp_id of this same table
    department  VARCHAR(50)
);
INSERT INTO employees VALUES
    (1, 'Sarah CEO',    NULL,'Executive'),
    (2, 'Alice',        1,   'Engineering'),
    (3, 'Bob',          1,   'Marketing'),
    (4, 'Carol',        2,   'Engineering'),
    (5, 'David',        2,   'Engineering');

-- Show each employee with their manager's name
SELECT
    e.name           AS employee,
    IFNULL(m.name, 'No Manager') AS manager
FROM employees AS e
LEFT JOIN employees AS m  -- same table aliased twice!
    ON e.manager_id = m.emp_id;

-- Books priced above the average
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books)
ORDER BY price DESC;

-- Books by authors from Great Britain
SELECT title FROM books
WHERE author_id IN (
    SELECT author_id FROM authors
    WHERE country = 'GB'
);

-- Authors with more than 1 book (using derived table)
SELECT author, book_count
FROM (
    SELECT
        CONCAT(a.first_name, ' ', a.last_name) AS author,
        COUNT(b.book_id) AS book_count
    FROM authors AS a
    LEFT JOIN books AS b ON a.author_id = b.author_id
    GROUP BY a.author_id
) AS author_counts                 -- derived table MUST have alias
WHERE book_count > 1;

-- Same query as above using CTE — much more readable!
WITH author_counts AS (
    SELECT
        a.author_id,
        CONCAT(a.first_name, ' ', a.last_name) AS author,
        COUNT(b.book_id) AS book_count,
        SUM(b.price)    AS total_value
    FROM authors AS a
    LEFT JOIN books AS b ON a.author_id = b.author_id
    GROUP BY a.author_id
)
SELECT author, book_count, total_value
FROM author_counts
WHERE book_count > 1
ORDER BY total_value DESC;

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT
    title, genre, price,
    ROW_NUMBER()   OVER (PARTITION BY genre ORDER BY price DESC) AS row_n,
    RANK()         OVER (ORDER BY price DESC)                   AS price_rank,
    AVG(price)     OVER (PARTITION BY genre)                       AS genre_avg,
    SUM(price)     OVER ()                                           AS grand_total
FROM books
ORDER BY genre, price DESC;

SELECT
    CONCAT('Hello', ' ', 'World')        AS concat_ex,
    UPPER('hello')                        AS upper_ex,
    LOWER('WORLD')                        AS lower_ex,
    LENGTH('Python')                      AS len_ex,
    TRIM('  hello  ')                    AS trim_ex,
    SUBSTRING('MySQL Mastery', 1, 5)    AS substr_ex,
    REPLACE('Hello World', 'World', 'SQL') AS replace_ex,
    LEFT('MySQL', 3)                      AS left_ex,
    RIGHT('MySQL', 3)                     AS right_ex,
    INSTR('Hello World', 'World')        AS pos_ex,
    LPAD('42', 6, '0')                   AS lpad_ex,
    REPEAT('AB', 3)                       AS repeat_ex;

SELECT
    NOW()                              AS current_datetime,
    CURDATE()                          AS current_date,
    CURTIME()                          AS current_time,
    YEAR('2024-03-15')                AS yr,
    MONTH('2024-03-15')               AS mo,
    DAY('2024-03-15')                 AS dy,
    DAYNAME('2024-03-15')             AS day_name,
    MONTHNAME('2024-03-15')           AS month_name,
    DATEDIFF('2024-12-31', CURDATE()) AS days_left,
    DATE_ADD('2024-01-01', INTERVAL 30 DAY) AS plus_30,
    DATE_FORMAT('2024-03-15', '%d/%m/%Y')  AS formatted;

SELECT
    ROUND(3.14159, 2)   AS rounded,     -- 3.14
    CEIL(4.1)            AS ceiling,     -- 5
    FLOOR(4.9)           AS floored,     -- 4
    ABS(-42)             AS absolute,    -- 42
    POWER(2, 10)         AS two_to_ten,  -- 1024
    SQRT(144)            AS sq_root,     -- 12
    MOD(17, 5)           AS modulus,     -- 2
    RAND()               AS random_num;  -- 0 to 1

SELECT
    title,
    price,
    stock,
    CASE
        WHEN price < 10   THEN 'Budget'
        WHEN price < 13   THEN 'Mid-range'
        ELSE                    'Premium'
    END AS price_tier,
    CASE
        WHEN stock = 0    THEN 'Out of Stock'
        WHEN stock < 100  THEN 'Low Stock'
        WHEN stock < 200  THEN 'In Stock'
        ELSE                    'Well Stocked'
    END AS stock_status
FROM books
ORDER BY price;

-- ALWAYS check what you're about to update first!
SELECT * FROM books WHERE book_id = 1;

-- Update a single row
UPDATE books
SET
    price = 13.99,
    stock = stock + 50   -- increment existing value
WHERE book_id = 1;

-- Update multiple rows — 10% price increase for all Mystery books
UPDATE books
SET price = ROUND(price * 1.10, 2)
WHERE genre = 'Mystery';

-- Conditional update with CASE
UPDATE books
SET price = CASE
    WHEN genre = 'Fantasy'  THEN price * 1.05
    WHEN genre = 'Classic'  THEN price * 0.95  -- discount classics
    ELSE price
END;

-- Delete a specific row
DELETE FROM books WHERE book_id = 99;

-- Delete with condition
DELETE FROM books
WHERE stock = 0 AND publish_date < '1950-01-01';

-- TRUNCATE — delete ALL rows (very fast, cannot roll back)
TRUNCATE TABLE temp_table;

-- Soft delete pattern (industry best practice)
-- Instead of deleting, mark as deleted:
ALTER TABLE books ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

UPDATE books
SET deleted_at = NOW()
WHERE book_id = 5;   -- "soft deleted" — still in DB, hidden from queries

-- Query only active records
SELECT * FROM books WHERE deleted_at IS NULL;

-- Single-column index
CREATE INDEX idx_genre   ON books(genre);
CREATE INDEX idx_price   ON books(price);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_email ON authors(email);

-- Composite index (columns used together in WHERE)
CREATE INDEX idx_genre_price ON books(genre, price);

-- Full-text index (for LIKE '%...%' type searches)
CREATE FULLTEXT INDEX idx_title_ft ON books(title);

-- Show all indexes on a table
SHOW INDEX FROM books;

-- Drop an index
DROP INDEX idx_genre ON books;

-- Prepend EXPLAIN to see MySQL's query plan
EXPLAIN
SELECT title, price
FROM books
WHERE genre = 'Mystery'
ORDER BY price;

-- Create a view = saved SELECT query
CREATE VIEW v_book_details AS
SELECT
    b.book_id,
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) AS author,
    a.country,
    b.genre,
    b.price,
    b.stock
FROM books b
JOIN authors a ON b.author_id = a.author_id;

-- Query the view just like a table!
SELECT * FROM v_book_details WHERE genre = 'Mystery';

-- List all views
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Drop a view
DROP VIEW v_book_details;

DELIMITER //   -- change delimiter so ; inside proc doesn't end it

CREATE PROCEDURE get_books_by_genre(
    IN  p_genre      VARCHAR(50),
    IN  p_max_price  DECIMAL(8,2),
    OUT p_count      INT
)
BEGIN
    SELECT title, price, stock
    FROM books
    WHERE genre = p_genre
      AND price <= p_max_price
    ORDER BY price;

    SELECT COUNT(*) INTO p_count
    FROM books
    WHERE genre = p_genre AND price <= p_max_price;
END//

DELIMITER ;   -- restore delimiter

-- Call the procedure
CALL get_books_by_genre('Mystery', 15.00, @count);
SELECT @count AS books_found;

DELIMITER //

CREATE TRIGGER trg_reduce_stock
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- Automatically reduce book stock when an order is placed
    UPDATE books
    SET    stock = stock - NEW.quantity
    WHERE  book_id = NEW.book_id;
END//

DELIMITER ;

-- Show all triggers
SHOW TRIGGERS;

-- Setup
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    name       VARCHAR(60),
    balance    DECIMAL(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Alice', 1000.00), (2, 'Bob', 500.00);

-- ── Successful transfer ─────────────────────────────
START TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;

COMMIT;   -- permanently saves both changes

SELECT * FROM accounts;

START TRANSACTION;

UPDATE accounts SET balance = balance - 2000 WHERE account_id = 1;
-- ↑ This would make Alice's balance = -1200 → violates CHECK constraint!

ROLLBACK;   -- undo everything since START TRANSACTION

-- Verify: Alice's balance is unchanged
SELECT * FROM accounts;

START TRANSACTION;

INSERT INTO orders (customer_id, book_id, quantity, order_date)
VALUES (1, 2, 1, CURDATE());

SAVEPOINT after_order;   -- bookmark this point

UPDATE books SET stock = stock - 99999 WHERE book_id = 2;
-- Oops! Too large a deduction

ROLLBACK TO SAVEPOINT after_order;   -- go back to savepoint, keep order insert

UPDATE books SET stock = stock - 1 WHERE book_id = 2;
-- Correct deduction

COMMIT;