SQL from Basics to Advanced.

CREATE DATABASE techforge_db;
USE techforge_db;
SHOW DATABASES;   -- List all databases
DROP DATABASE techforge_db;  --  Permanent!

CREATE TABLE users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL,
    gender        ENUM('Male', 'Female', 'Other'),
    salary        DECIMAL(10,2) DEFAULT 0,
    date_of_birth DATE,
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Verify structure
DESCRIBE users;

-- Remove table entirely (irreversible)
DROP TABLE IF EXISTS users;

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(15);

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Modify column type
ALTER TABLE users MODIFY COLUMN name VARCHAR(150) NOT NULL;

-- Rename column (MySQL 8.0+)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Rename table
RENAME TABLE users TO members;

-- Single row
INSERT INTO users (name, email, gender, salary, date_of_birth)
VALUES ('Alice', 'alice@example.com', 'Female', 85000, '1995-04-12');

-- Multiple rows at once (more efficient)
INSERT INTO users (name, email, gender, salary, date_of_birth)
VALUES
    ('Bob',     'bob@example.com',     'Male',   72000, '1990-11-23'),
    ('Charlie', 'charlie@example.com', 'Other',  91000, '1988-02-17'),
    ('Divya',   'divya@example.com',   'Female', 67000, '2000-08-09');

-- Basic SELECT
SELECT * FROM users;
SELECT name, email, salary FROM users;

-- WHERE with operators
SELECT * FROM users WHERE gender = 'Female';
SELECT * FROM users WHERE salary BETWEEN 60000 AND 90000;
SELECT * FROM users WHERE name LIKE 'A%';   -- starts with A
SELECT * FROM users WHERE email IS NOT NULL;

-- AND / OR / NOT
SELECT * FROM users WHERE salary > 70000 AND gender = 'Male';
SELECT * FROM users WHERE department IN ('Eng', 'Design');

-- ORDER and LIMIT
SELECT * FROM users ORDER BY salary DESC LIMIT 10;
SELECT * FROM users ORDER BY name ASC LIMIT 5 OFFSET 10; -- pagination

-- Aliases
SELECT name AS full_name, salary * 12 AS annual_salary FROM users;

-- UPDATE specific rows
UPDATE users SET salary = 95000 WHERE id = 1;
UPDATE users SET salary = salary * 1.10 WHERE gender = 'Female'; -- 10% raise

-- DELETE specific rows
DELETE FROM users WHERE id = 3;
DELETE FROM users WHERE is_active = FALSE;

-- TRUNCATE: delete all rows, keep table structure (faster than DELETE)
TRUNCATE TABLE users;

SELECT COUNT(*)                    AS total_users    FROM users;
SELECT COUNT(DISTINCT department)  AS dept_count      FROM users;
SELECT MIN(salary), MAX(salary)    AS max_sal        FROM users;
SELECT SUM(salary)                  AS total_payroll  FROM users;
SELECT ROUND(AVG(salary), 2)        AS avg_salary     FROM users;

-- String functions
SELECT UPPER(name), LOWER(email), LENGTH(name) FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT SUBSTRING(email, 1, INSTR(email, '@') - 1) AS username FROM users;

-- Date functions
SELECT YEAR(date_of_birth), MONTH(date_of_birth) FROM users;
SELECT DATEDIFF(NOW(), date_of_birth) / 365 AS age FROM users;
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month FROM users;

SELECT NULL = NULL;       -- NULL, not true
SELECT * FROM users WHERE deleted_at IS NULL;   -- correct

-- Aggregates skip NULLs; COUNT(*) does not
SELECT COUNT(*), COUNT(phone), AVG(score) FROM users;

SELECT name,
       CASE WHEN score >= 90 THEN 'A'
            WHEN score >= 80 THEN 'B'
            ELSE 'C'
       END AS grade,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders
FROM students LEFT JOIN orders USING(id)
GROUP BY name, grade;

-- ROW_NUMBER: unique rank per partition
SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM users;

-- RANK vs DENSE_RANK: ties behave differently
-- RANK skips numbers after ties; DENSE_RANK does not
SELECT name, salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
FROM users;

-- LAG/LEAD: compare current row with previous/next
SELECT name, salary,
    LAG(salary)  OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM users;

-- Running total
SELECT name, salary,
    SUM(salary) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS running_total
FROM users;

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- credit

-- If both succeed:
COMMIT;

-- If something fails, undo everything:
ROLLBACK;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;              -- a partial rollback point
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO after_debit;            -- undo the credit, keep the debit
COMMIT;

CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    total      DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- ON DELETE CASCADE: deleting a user also deletes their orders
-- ON DELETE SET NULL: sets user_id to NULL when user is deleted

-- INNER JOIN: users who have placed orders
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: ALL users, with orders if they exist
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Find users with NO orders
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- SELF JOIN: find users in the same department
SELECT a.name, b.name, a.department
FROM users a
JOIN users b ON a.department = b.department AND a.id != b.id;

-- UNION: combines results, removes duplicates
SELECT name FROM users
UNION
SELECT name FROM admin_users;

-- UNION ALL: keeps duplicates (faster)
SELECT name FROM users
UNION ALL
SELECT name FROM admin_users;

-- Subquery in WHERE
SELECT name, salary FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

-- Subquery in FROM (derived table)
SELECT dept, avg_sal
FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM users GROUP BY department
) dept_avg
WHERE avg_sal > 80000;

-- Correlated: runs once per employee
SELECT name, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

-- Same answer with a window function, one pass
SELECT name, salary FROM (
  SELECT name, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM employees
) t WHERE salary > dept_avg;

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL      -- anchor: the root
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id  -- recursive step
)
SELECT * FROM chain ORDER BY depth;

SELECT c.name, o.total, o.placed_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT total, placed_at FROM orders
  WHERE customer_id = c.id ORDER BY placed_at DESC LIMIT 3
) o;

-- Create index on frequently queried column
CREATE INDEX idx_email ON users(email);

-- Composite index: column order matters!
CREATE INDEX idx_dept_salary ON users(department, salary);

-- EXPLAIN: see if index is being used
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Drop index
DROP INDEX idx_email ON users;

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.name;

-- Count employees per department
SELECT department, COUNT(*) AS headcount
FROM users
GROUP BY department
ORDER BY headcount DESC;

-- HAVING: only departments with avg salary > 75k
SELECT department, ROUND(AVG(salary),2) AS avg_sal
FROM users
GROUP BY department
HAVING AVG(salary) > 75000
ORDER BY avg_sal DESC;

-- WHERE + GROUP BY + HAVING together
SELECT department, COUNT(*) AS cnt
FROM users
WHERE is_active = TRUE              -- filter rows first
GROUP BY department                 -- then group
HAVING COUNT(*) > 5               -- then filter groups
ORDER BY cnt DESC;                  -- then sort

CREATE VIEW active_customers AS
SELECT id, name, email FROM customers WHERE status = 'active';

SELECT * FROM active_customers;   -- queried like a table

-- Materialized: stores the result, must be refreshed
CREATE MATERIALIZED VIEW sales_by_month AS
SELECT date_trunc('month', sold_at) AS m, SUM(total) FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW sales_by_month;

-- Procedure: called explicitly
CREATE PROCEDURE give_raise(IN emp_id INT, IN pct DECIMAL(4,2))
BEGIN
  UPDATE employees SET salary = salary * (1 + pct) WHERE id = emp_id;
END;

CALL give_raise(42, 0.10);

-- Trigger: the database calls it for you
CREATE TRIGGER audit_salary AFTER UPDATE ON employees
FOR EACH ROW
  INSERT INTO salary_audit(emp_id, old_val, new_val, changed_at)
  VALUES(OLD.id, OLD.salary, NEW.salary, NOW());

-- Vulnerable: input becomes part of the statement
query = "SELECT * FROM users WHERE email = '" + input + "'";

-- input:  ' OR '1'='1
SELECT * FROM users WHERE email = '' OR '1'='1';   -- returns every row

-- Safe: the value is sent separately from the statement
SELECT * FROM users WHERE email = ?;

ALTER TABLE events ADD COLUMN payload JSONB;

-- query inside the document
SELECT id FROM events WHERE payload->>'type' = 'signup';

-- and index it, or every read is a full scan
CREATE INDEX idx_events_type ON events ((payload->>'type'));