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;

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

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;

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

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