MySQL SQL Complete Guide: From Beginner to Advanced (With Interview Questions)

-- Create a database
CREATE DATABASE shop DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- List all databases
SHOW DATABASES;

-- Switch to a database
USE shop;

-- Show the current database
SELECT DATABASE();

-- Drop a database (use with caution!)
DROP DATABASE shop;

-- Create a table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'User ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT 'Username',
    email VARCHAR(100) NOT NULL COMMENT 'Email address',
    password VARCHAR(255) NOT NULL COMMENT 'Hashed password',
    age TINYINT UNSIGNED DEFAULT 0 COMMENT 'Age',
    status TINYINT DEFAULT 1 COMMENT 'Status: 1=active, 0=disabled',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Created at',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated at'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Users table';

-- Inspect table structure
DESC users;
SHOW CREATE TABLE users;

-- Alter a table
ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT 'Phone number';   -- add column
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15);                       -- change column type
ALTER TABLE users CHANGE phone mobile VARCHAR(15);                       -- rename column
ALTER TABLE users DROP COLUMN mobile;                                    -- drop column

-- Drop a table
DROP TABLE IF EXISTS users;

-- Single-row insert
INSERT INTO users (username, email, password, age)
VALUES ('alice', '[email protected]', 'hashed_password', 25);

-- Multi-row insert (recommended for efficiency)
INSERT INTO users (username, email, password, age) VALUES
('bob', '[email protected]', 'hashed_password', 30),
('charlie', '[email protected]', 'hashed_password', 28),
('diana', '[email protected]', 'hashed_password', 35);

-- Upsert: insert or update on duplicate key
INSERT INTO users (id, username, email, password)
VALUES (1, 'alice', '[email protected]', 'new_password')
ON DUPLICATE KEY UPDATE email = VALUES(email), password = VALUES(password);

-- Insert and silently skip duplicates
INSERT IGNORE INTO users (username, email, password)
VALUES ('alice', '[email protected]', 'password');

-- Select all columns (avoid in production — hurts performance)
SELECT * FROM users;

-- Select specific columns (recommended)
SELECT id, username, email FROM users;

-- Column aliases
SELECT id AS user_id, username AS name FROM users;

-- Remove duplicates
SELECT DISTINCT status FROM users;

-- Limit rows
SELECT * FROM users LIMIT 10;           -- first 10 rows
SELECT * FROM users LIMIT 10 OFFSET 20; -- skip 20, take 10
SELECT * FROM users LIMIT 20, 10;       -- shorthand for the above

-- Comparison operators
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE status != 0;
SELECT * FROM users WHERE age <> 18;  -- alternative not-equal syntax

-- Logical operators
SELECT * FROM users WHERE age >= 18 AND status = 1;
SELECT * FROM users WHERE age < 18 OR age > 60;
SELECT * FROM users WHERE NOT status = 0;

-- Range queries
SELECT * FROM users WHERE age BETWEEN 18 AND 30;   -- inclusive
SELECT * FROM users WHERE age IN (18, 20, 25, 30);

-- NULL checks
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- Pattern matching
SELECT * FROM users WHERE username LIKE 'alice%';  -- starts with "alice"
SELECT * FROM users WHERE username LIKE '%son';    -- ends with "son"
SELECT * FROM users WHERE username LIKE '%ob%';    -- contains "ob"
SELECT * FROM users WHERE username LIKE 'alic_';   -- _ matches a single character

-- Ascending (default)
SELECT * FROM users ORDER BY age ASC;

-- Descending
SELECT * FROM users ORDER BY created_at DESC;

-- Multi-column sort
SELECT * FROM users ORDER BY status DESC, created_at DESC;

-- Handling NULLs (MySQL treats NULL as the smallest value)
SELECT * FROM users ORDER BY age IS NULL, age;  -- pushes NULLs to the end

-- Basic grouping
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status;

-- Multi-column grouping
SELECT status, age, COUNT(*) AS count
FROM users
GROUP BY status, age;

-- HAVING filters groups (WHERE filters rows before grouping; HAVING filters after)
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status
HAVING user_count > 10;

-- WITH ROLLUP adds a summary row
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status WITH ROLLUP;

-- Update a single field
UPDATE users SET status = 0 WHERE id = 1;

-- Update multiple fields
UPDATE users SET status = 0, updated_at = NOW() WHERE id = 1;

-- Conditional update
UPDATE users SET status = 0 WHERE last_login < '2024-01-01';

-- Batch update with CASE WHEN
UPDATE users SET status = CASE
    WHEN age < 18 THEN 0
    WHEN age >= 60 THEN 2
    ELSE 1
END
WHERE id IN (1, 2, 3, 4, 5);

-- Limit the number of rows updated
UPDATE users SET status = 0 ORDER BY created_at LIMIT 100;

-- Delete with a condition
DELETE FROM users WHERE status = 0;

-- Delete a limited number of rows
DELETE FROM users WHERE status = 0 ORDER BY created_at LIMIT 100;

-- Truncate the entire table (faster, but cannot be rolled back)
TRUNCATE TABLE users;

-- DELETE vs TRUNCATE
-- DELETE:   row-by-row, can be rolled back, triggers fire, auto-increment continues
-- TRUNCATE: instant wipe, cannot be rolled back, triggers do not fire, auto-increment resets

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);

-- Order line items
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- INNER JOIN: returns only rows that match in both tables
SELECT u.username, o.id AS order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: returns all rows from the left table; NULLs where there is no match on the right
SELECT u.username, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN: returns all rows from the right table; NULLs where there is no match on the left
SELECT u.username, o.id AS order_id, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Find users who have never placed an order
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Self join: a table joined to itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Three-table join: fetch full order details
SELECT
    u.username,
    o.id AS order_id,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 1
ORDER BY o.created_at DESC;

-- Scalar subquery (returns a single value)
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- Row subquery (returns one row)
SELECT * FROM users
WHERE (age, status) = (SELECT MAX(age), 1 FROM users);

-- Column subquery (returns one column)
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);

-- EXISTS subquery (checks for existence)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- NOT EXISTS (find users with no orders)
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- UNION (removes duplicates)
SELECT username, email FROM users WHERE status = 1
UNION
SELECT username, email FROM archived_users WHERE status = 1;

-- UNION ALL (keeps duplicates — better performance)
SELECT username, 'active' AS type FROM users WHERE status = 1
UNION ALL
SELECT username, 'inactive' AS type FROM users WHERE status = 0;

-- ROW_NUMBER(): assigns a unique sequential number to each row
SELECT
    username,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;

-- RANK(): same rank for ties, then skips numbers
SELECT
    username,
    age,
    RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

-- DENSE_RANK(): same rank for ties, no gaps
SELECT
    username,
    age,
    DENSE_RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

-- Ranking within groups (rank per status)
SELECT
    username,
    status,
    age,
    ROW_NUMBER() OVER (PARTITION BY status ORDER BY age DESC) AS rank_in_group
FROM users;

-- LAG / LEAD: access previous / next row values
SELECT
    username,
    created_at,
    LAG(created_at, 1) OVER (ORDER BY created_at) AS prev_created,
    LEAD(created_at, 1) OVER (ORDER BY created_at) AS next_created
FROM users;

-- Running total
SELECT
    username,
    total_amount,
    SUM(total_amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- Basic CTE
WITH active_users AS (
    SELECT * FROM users WHERE status = 1
)
SELECT * FROM active_users WHERE age >= 18;

-- Multiple CTEs
WITH
    active_users AS (
        SELECT * FROM users WHERE status = 1
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) AS order_count
        FROM orders
        GROUP BY user_id
    )
SELECT u.username, COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

-- Recursive CTE (e.g., organizational hierarchy)
WITH RECURSIVE org_tree AS (
    -- Base case: top-level nodes
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: child nodes
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

-- Primary key index
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT
);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);
-- or
ALTER TABLE users ADD UNIQUE INDEX idx_email(email);

-- Regular index
CREATE INDEX idx_username ON users(username);

-- Composite (multi-column) index
CREATE INDEX idx_status_created ON users(status, created_at);

-- Prefix index (for long string columns)
CREATE INDEX idx_email_prefix ON users(email(10));

-- Full-text index
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- Assume users has a primary key on `id` and an index idx_username on `username`

-- This query requires a bookmark lookup
SELECT * FROM users WHERE username = 'alice';
-- Step 1: traverse idx_username to find the primary key for username='alice'
-- Step 2: traverse the clustered index using that primary key to get the full row

-- This query does NOT require a bookmark lookup (covering index)
SELECT id, username FROM users WHERE username = 'alice';
-- The secondary index already contains both id and username — done in one step

-- 1. Using a function on an indexed column
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- index not used
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';  -- index used

-- 2. Implicit type conversion
-- Assume phone is VARCHAR
SELECT * FROM users WHERE phone = 13800138000;   -- numeric comparison, index skipped
SELECT * FROM users WHERE phone = '13800138000';  -- string comparison, index used

-- 3. Leading wildcard in LIKE
SELECT * FROM users WHERE username LIKE '%alice';  -- index not used
SELECT * FROM users WHERE username LIKE 'alice%';  -- index used

-- 4. OR with a non-indexed column
SELECT * FROM users WHERE username = 'alice' OR age = 25;  -- if age has no index, the whole query goes full scan

-- 5. Composite index violating the leftmost prefix rule
-- Assume index idx_a_b_c(a, b, c)
SELECT * FROM users WHERE a = 1 AND b = 2;  -- index used
SELECT * FROM users WHERE b = 2 AND c = 3;  -- leftmost prefix violated, index not used
SELECT * FROM users WHERE a = 1 AND c = 3;  -- only column a is used

-- 6. Not-equal conditions
SELECT * FROM users WHERE status != 1;  -- likely full table scan

-- 7. IS NOT NULL (depends on data distribution)
SELECT * FROM users WHERE email IS NOT NULL;  -- if most rows are non-NULL, a full scan may be cheaper

-- Composite index idx_a_b_c(a, b, c)

-- Index IS used
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- only column a participates

-- Index is NOT used
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

-- Assume index idx_name_age(name, age)

-- Covering index: all queried columns are in the index
SELECT name, age FROM users WHERE name = 'alice';  -- EXPLAIN shows "Using index"

-- Needs a bookmark lookup: email is not in the index
SELECT name, age, email FROM users WHERE name = 'alice';  -- must access the clustered index for email

-- Start a transaction
START TRANSACTION;
-- or
BEGIN;

-- Commit
COMMIT;

-- Roll back
ROLLBACK;

-- Savepoints
SAVEPOINT sp1;
ROLLBACK TO sp1;

-- Example: money transfer
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- Verify constraints
SELECT balance FROM accounts WHERE user_id = 1;
-- If balance is negative, roll back
-- ROLLBACK;

COMMIT;

-- Check the current isolation level
SELECT @@transaction_isolation;

-- Change the isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Shared lock (S): multiple transactions can read simultaneously
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0+ syntax
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- Exclusive lock (X): only one transaction can read or write
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- Gap lock: locks the space between index records
-- Activated under RR isolation level for range queries
SELECT * FROM users WHERE age > 20 FOR UPDATE;  -- locks gaps where age > 20

-- Next-key lock = record lock + gap lock
-- This is InnoDB's default row-locking algorithm

-- Transaction A
START TRANSACTION;
UPDATE users SET status = 1 WHERE id = 1;  -- locks row id=1
UPDATE users SET status = 1 WHERE id = 2;  -- waits for row id=2...

-- Transaction B
START TRANSACTION;
UPDATE users SET status = 2 WHERE id = 2;  -- locks row id=2
UPDATE users SET status = 2 WHERE id = 1;  -- waits for row id=1...

-- Deadlock! InnoDB detects it and rolls back one of the transactions.

-- View the latest deadlock information
SHOW ENGINE INNODB STATUS;

-- View current lock waits
SELECT * FROM performance_schema.data_lock_waits;

EXPLAIN SELECT * FROM users WHERE username = 'alice';

-- Check slow query configuration
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable the slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- log queries taking longer than 1 second

-- Analyze the slow query log with mysqldumpslow
-- mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

-- Problem: deep pagination is extremely slow
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;  -- scans 1 million rows

-- Solution 1: deferred join
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

-- Solution 2: keyset (cursor-based) pagination
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- Problem: updating millions of rows at once causes severe lock contention
UPDATE orders SET status = 2 WHERE created_at < '2024-01-01';

-- Solution: process in batches
UPDATE orders SET status = 2
WHERE created_at < '2024-01-01' AND status != 2
LIMIT 1000;

-- Repeat until affected rows = 0

-- COUNT(*) vs COUNT(1) vs COUNT(column)
-- MySQL treats COUNT(*) and COUNT(1) identically — both count all rows.
-- COUNT(column) counts only non-NULL values in that column.

-- For InnoDB, COUNT(*) always requires scanning rows.
-- Optimization strategies:
-- 1. Use an approximate count: SHOW TABLE STATUS LIKE 'orders';
-- 2. Maintain a counter in a cache (e.g., Redis)
-- 3. Use a summary table

-- Problem: filesort is slow
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 10;

-- Solution: create a composite index
CREATE INDEX idx_status_created ON orders(status, created_at);
-- The index is already sorted, so no extra sorting is needed

-- Deferred join
SELECT * FROM t
INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10) t2
ON t.id = t2.id;

-- Keyset pagination
SELECT * FROM t WHERE id > last_id ORDER BY id LIMIT 10;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    class_id INT,
    score INT
);

CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 1. Average score per class, ordered descending
SELECT c.name AS class_name, AVG(s.score) AS avg_score
FROM classes c
LEFT JOIN students s ON c.id = s.class_id
GROUP BY c.id
ORDER BY avg_score DESC;

-- 2. Top scorer(s) in each class
SELECT s.*
FROM students s
INNER JOIN (
    SELECT class_id, MAX(score) AS max_score
    FROM students
    GROUP BY class_id
) t ON s.class_id = t.class_id AND s.score = t.max_score;

-- 3. Students scoring above the overall average
SELECT *
FROM students
WHERE score > (SELECT AVG(score) FROM students);

-- 4. Rank students within each class using window functions
SELECT
    name,
    class_id,
    score,
    RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank_in_class
FROM students;

-- 5. Find users who logged in for at least 3 consecutive days
WITH ranked_logins AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
    FROM login_logs
)
SELECT DISTINCT user_id
FROM ranked_logins
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

-- 1. Daily order count and revenue
SELECT
    DATE(created_at) AS order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY order_date;

-- 2. Each user's first order date and lifetime spend
SELECT
    user_id,
    MIN(created_at) AS first_order_time,
    SUM(total_amount) AS total_spent,
    COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- 3. Top 10 customers by total spend
SELECT
    u.id,
    u.username,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
ORDER BY total_spent DESC
LIMIT 10;

-- 4. Monthly product sales leaderboard
SELECT
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    p.name AS product_name,
    SUM(oi.quantity) AS total_quantity
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
GROUP BY month, p.id
ORDER BY month, total_quantity DESC;