SQL Mastery
32 Real Interview Questions

SQL (Structured Query Language) is the standard language for managing and querying relational databases. SQL keywords are NOT case-sensitive — SELECT, select, and Select are identical. However, data values inside rows may be case-sensitive depending on the column's collation setting (e.g., WHERE name = 'Alice' vs 'alice' may behave differently based on the DB configuration).

Quick category recap:

• DDL (Data Definition Language): structures the database — CREATE TABLE, ALTER TABLE, DROP TABLE. These auto-commit; you can't roll them back in most databases.
• DML (Data Manipulation Language): changes data — INSERT, UPDATE, DELETE. These can be rolled back inside a transaction.
• DQL (Data Query Language): retrieves data — just SELECT. Read-only.

Tables: Employees (emp_id, name, dept_id) and Departments (dept_id, dept_name)

-- INNER JOIN: only employees who have a matching department
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- Result: employees WITH a dept only. John (dept=NULL) excluded.

-- LEFT JOIN: all employees, even those without a department
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- Result: John (no dept) appears with dept_name = NULL

-- RIGHT JOIN: all departments, even those with no employees
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-- Result: "Research" dept (no employees yet) appears with name = NULL

-- FULL OUTER JOIN: everything from both sides
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;
-- MySQL doesn't support FULL OUTER JOIN directly — use UNION:
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- CROSS JOIN: every employee paired with every department
SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d;
-- 5 employees × 4 departments = 20 rows. Rarely useful. Be careful!

-- SELF JOIN: find employees and their manager (same table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

WHERE filters individual rows — it runs before GROUP BY, so it operates on raw table data. You cannot use aggregate functions in WHERE because the aggregates haven't been computed yet.

HAVING filters groups — it runs after GROUP BY, so it operates on grouped/aggregated data. You must use HAVING for conditions on aggregate results.

-- WRONG: cannot use aggregate in WHERE
SELECT dept_id, COUNT(*) FROM employees
WHERE COUNT(*) > 3  -- ❌ Error: WHERE can't see aggregates
GROUP BY dept_id;

-- CORRECT: filter groups after aggregation using HAVING
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
WHERE salary > 30000          -- filters rows BEFORE grouping
GROUP BY dept_id
HAVING COUNT(*) > 3;          -- filters GROUPS after aggregation
-- "Find departments (with avg salary > 30k) having more than 3 employees"

CREATE TABLE employees (
    emp_id    INT PRIMARY KEY,       -- PK: unique + not null + clustered
    email     VARCHAR(100) UNIQUE,    -- Unique key: unique but nullable
    dept_id   INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL             -- if dept deleted, set to NULL
);

A subquery (nested query) is a SELECT statement embedded inside another SQL statement. The inner query runs first; its result is used by the outer query.

-- Regular subquery: find employees earning above average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Inner query runs ONCE, returns 58000. Outer query uses that value.

A correlated subquery references a column from the outer query — the inner query re-executes for each row of the outer query. This is powerful but often slow.

-- Correlated subquery: employees earning more than avg in THEIR OWN dept
SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE dept_id = e.dept_id  -- references outer query's row
);
-- Inner query runs ONCE PER OUTER ROW — can be slow on large tables

This is the single most commonly asked SQL query in fresher interviews. Know all three approaches:

-- Method 1: Nested MAX (classic, easy to explain)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: LIMIT + OFFSET (simple, MySQL/PostgreSQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- skip the highest, take next one

-- Method 3: DENSE_RANK (best — handles Nth and ties gracefully)
SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;  -- change 2 to N for Nth highest

-- Why DENSE_RANK is best: handles ties correctly
-- If salaries are [100, 100, 80, 60]:
-- Nested MAX gives 80 ✓ but LIMIT/OFFSET gives 100 (the tie) ✗
-- DENSE_RANK gives 80 (correctly 2nd unique salary) ✓

This classic question tests self-join knowledge. The trick: join the employees table to itself — once as "employee", once as "manager."

-- Table: employees(emp_id, name, salary, manager_id)
-- manager_id references emp_id in the same table

SELECT
    e.name   AS employee_name,
    e.salary AS employee_salary,
    m.name   AS manager_name,
    m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

-- We alias the same table twice: e = employee row, m = manager row
-- JOIN matches each employee to their manager's record
-- WHERE filters to only keep cases where employee earns more

Why LEFT JOIN instead of JOIN matters here: if you use JOIN (INNER), employees with no manager (CEO/top-level) are excluded — because they have no matching manager row. If the question asks to include all employees, use LEFT JOIN.

-- Step 1: Find which emails have duplicates
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Step 2: See all duplicate rows
SELECT * FROM users
WHERE email IN (
    SELECT email FROM users
    GROUP BY email HAVING COUNT(*) > 1
);

-- Step 3: Delete duplicates, keep the one with smallest id (MySQL)
DELETE u1 FROM users u1
INNER JOIN users u2
    ON u1.email = u2.email  -- same email
    AND u1.id > u2.id;      -- u1 has higher id → delete u1, keep u2

-- Alternative: PostgreSQL / modern SQL using CTE + ROW_NUMBER
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- Use CHAR for fixed-length, predictable data
country_code CHAR(2)         -- Always 2 chars: US, IN, UK
gender       CHAR(1)         -- Always 1 char: M, F
md5_hash     CHAR(32)        -- Always 32 hex chars

-- Use VARCHAR for variable-length data
first_name   VARCHAR(50)     -- Could be 2 chars or 50
email        VARCHAR(255)    -- Highly variable
description  VARCHAR(1000)   -- Could be empty or long

-- DELETE: remove specific rows (rollbackable)
DELETE FROM employees WHERE dept_id = 5;
ROLLBACK;  -- ✅ Works — changes are undone

-- TRUNCATE: remove all rows fast (structure remains)
TRUNCATE TABLE employees;
-- ✅ Table still exists, just empty. Auto-increment reset to 1.

-- DROP: destroy the table entirely
DROP TABLE employees;
-- ❌ Cannot rollback in most databases. Table is gone.

-- UNION: combine active and inactive customers (remove duplicates)
SELECT name, email FROM active_customers
UNION
SELECT name, email FROM inactive_customers;
-- If same person is in both tables, they appear ONCE

-- UNION ALL: combine all sales records (duplicates fine — different dates)
SELECT sale_id, amount, 2023 AS year FROM sales_2023
UNION ALL
SELECT sale_id, amount, 2024 AS year FROM sales_2024;
-- All records kept. Faster because no deduplication needed.

-- Requirements: both queries must have SAME number of columns
-- and compatible data types in each position

This is a question interviewers ask to check whether you truly understand relational database design — not just syntax.

SQL injection is an attack where malicious user input is inserted into a SQL query string, causing the database to execute unintended commands — stealing data, deleting records, or bypassing authentication.

-- VULNERABLE: string concatenation — NEVER DO THIS
String query = "SELECT * FROM users WHERE username='" + username + "'";
-- If username = "admin' OR '1'='1"
-- Query becomes: SELECT * FROM users WHERE username='admin' OR '1'='1'
-- '1'='1' is always true → returns ALL users → attacker logs in as anyone

-- EVEN WORSE: DROP TABLE injection
-- username = "'; DROP TABLE users; --"
-- Query: SELECT * FROM users WHERE username=''; DROP TABLE users; --'
-- The users table is DELETED

Prevention (in order of importance):

1. Parameterized queries / Prepared statements — always: the query structure is compiled separately from user input. Input can never change the query structure.
2. Use an ORM — Hibernate, SQLAlchemy, Django ORM use parameterized queries by default.
3. Input validation and sanitization — validate type, length, format. Reject unexpected characters.
4. Principle of least privilege — DB user should only have SELECT on read-only operations, not DROP or ALTER.
5. Web Application Firewall (WAF) — second line of defense (not a primary defense).

-- SAFE: Parameterized query (Java PreparedStatement)
PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = ?");
stmt.setString(1, username); // user input is treated as DATA, not SQL code
ResultSet rs = stmt.executeQuery();

This tests date function knowledge — which varies slightly by database.

-- MySQL
SELECT name, joining_date
FROM employees
WHERE joining_date >= CURDATE() - INTERVAL 30 DAY;

-- PostgreSQL
SELECT name, joining_date
FROM employees
WHERE joining_date >= CURRENT_DATE - INTERVAL '30 days';

-- SQL Server
SELECT name, joining_date
FROM employees
WHERE joining_date >= DATEADD(DAY, -30, GETDATE());

-- Oracle
SELECT name, joining_date
FROM employees
WHERE joining_date >= SYSDATE - 30;

-- Table: orders(order_id, customer_id, product_id, amount)
-- Some rows: order_id=1, customer_id=NULL, product_id=101, amount=50
--            order_id=2, customer_id=5,    product_id=101, amount=80
--            order_id=3, customer_id=5,    product_id=202, amount=30

SELECT
    COUNT(*)                    AS total_rows,       -- 3 (counts ALL rows, including NULLs)
    COUNT(customer_id)          AS non_null_cust,    -- 2 (row 1 has NULL customer_id, excluded)
    COUNT(DISTINCT customer_id) AS unique_customers, -- 1 (customer_id=5 appears twice, counted once)
    COUNT(DISTINCT product_id)  AS unique_products   -- 2 (products 101 and 202)
FROM orders;

Key rules:

• COUNT(*): counts every row — NULLs included. Use to count total records.
• COUNT(col): counts only non-NULL values in that column. Use to count how many rows have a value.
• COUNT(DISTINCT col): counts unique non-NULL values. Use to count distinct customers, products, etc.

-- employees(emp_id, name, dept_id, salary, hire_date)

-- Aggregates per department
SELECT
    dept_id,
    COUNT(*)          AS employee_count,
    SUM(salary)       AS total_salary,
    AVG(salary)       AS avg_salary,
    MIN(salary)       AS min_salary,
    MAX(salary)       AS max_salary,
    MIN(hire_date)    AS earliest_hire,
    MAX(hire_date)    AS latest_hire
FROM employees
GROUP BY dept_id
ORDER BY avg_salary DESC;

-- Rule: every column in SELECT must either:
-- 1. Be in the GROUP BY clause, OR
-- 2. Be inside an aggregate function (COUNT, SUM, AVG, MIN, MAX)
-- Violating this is an error in strict SQL (though MySQL allows it with caveats)

Combining WHERE and HAVING:

-- Departments with > 3 employees, excluding interns (salary < 20000)
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
WHERE salary >= 20000           -- filter rows BEFORE grouping
GROUP BY dept_id
HAVING COUNT(*) > 3             -- filter GROUPS after aggregation
ORDER BY avg_sal DESC;

-- Stored Procedure: give a raise to all employees in a dept
DELIMITER //
CREATE PROCEDURE give_raise(IN dept INT, IN pct DECIMAL(5,2))
BEGIN
    UPDATE employees
    SET salary = salary * (1 + pct / 100)
    WHERE dept_id = dept;
END //
DELIMITER ;

CALL give_raise(3, 10.0);  -- 10% raise for dept 3

-- Function: calculate tax for a given salary
CREATE FUNCTION calc_tax(salary DECIMAL(10,2))
RETURNS DECIMAL(10,2) DETERMINISTIC
RETURN salary * 0.3;

SELECT name, salary, calc_tax(salary) AS tax FROM employees;

Benefits of stored procedures: performance (cached execution plan), reduced network traffic (single CALL vs many SQL statements), centralized business logic, reusability.

A view is a virtual table defined by a SELECT query. The data is not physically stored — it's recomputed from the underlying tables every time the view is queried.

-- Create a view for frequently queried employee-department data
CREATE VIEW emp_dept_view AS
SELECT e.emp_id, e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- Now query the view like a regular table
SELECT * FROM emp_dept_view WHERE dept_name = 'Engineering';

-- The database internally rewrites this as the full JOIN query

Benefits of views:

• Security: expose only certain columns/rows to specific users without granting access to base tables
• Simplicity: hide complex JOIN logic behind a simple table-like interface
• Consistency: business logic defined once, used everywhere

A materialized view physically stores the query result — it's a pre-computed snapshot. Much faster for complex aggregations. Needs explicit refresh when underlying data changes.

-- PostgreSQL materialized view (pre-computes expensive aggregation)
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(amount) AS total_sales
FROM orders GROUP BY 1;

-- Refresh when data changes (manual or scheduled)
REFRESH MATERIALIZED VIEW monthly_sales_summary;

A database index is a separate data structure (typically a B-tree) that the database maintains alongside a table to speed up data retrieval on indexed columns — similar to how a book index lets you jump to a page without reading the whole book.

-- Create an index on email column (frequently searched)
CREATE INDEX idx_employees_email ON employees(email);

-- Composite index: speeds up queries filtering by both columns together
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);

-- Unique index: enforces uniqueness AND speeds up lookups
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

Advantages:

• Dramatically faster SELECT with WHERE, JOIN, ORDER BY, GROUP BY on indexed columns
• Turns O(n) full table scan into O(log n) B-tree lookup
• Unique indexes enforce data integrity at the database level

Disadvantages:

• Slower writes: every INSERT/UPDATE/DELETE must also update all affected indexes — can be 2–5x slower for write-heavy tables
• Extra disk space: each index is a copy of data in a different order — can be 10–50% of table size
• Maintenance overhead: index fragmentation over time requires periodic maintenance (REBUILD/REORGANIZE)

Visual analogy:

• Clustered index: like a physical dictionary — words are stored in alphabetical order ON THE PAGE ITSELF. Finding "algorithm" means opening to the A section; the definition is right there.
• Non-clustered index: like a book's index at the back — you look up "algorithm" in the index, which gives you a page number (pointer), then you flip to that page to read the content.

-- In MySQL InnoDB: Primary Key = Clustered Index
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,  -- THIS is the clustered index
    name   VARCHAR(100),
    email  VARCHAR(100)
);
-- Rows are physically stored sorted by emp_id
-- When you SELECT * FROM employees WHERE emp_id = 42:
-- One B-tree traversal → directly at the row data

CREATE INDEX idx_email ON employees(email); -- non-clustered
-- SELECT * WHERE email = 'x@y.com':
-- 1. Traverse email index B-tree → find pointer (emp_id=42)
-- 2. Traverse PRIMARY KEY B-tree → find actual row data
-- This second lookup is called a "bookmark lookup" or "index scan back"

Starting point — un-normalized order data:

OrderID | CustomerName | CustomerCity | Products              | ProductPrices
--------|--------------|--------------|------------------------|--------------
1       | Alice        | Mumbai       | Laptop, Mouse          | 60000, 500
2       | Bob          | Delhi        | Keyboard               | 800

Step 1 — Apply 1NF: Make all values atomic (one value per cell). No multi-valued attributes.

OrderID | LineNum | CustomerName | CustomerCity | Product  | Price
--------|---------|--------------|--------------|----------|-------
1       | 1       | Alice        | Mumbai       | Laptop   | 60000
1       | 2       | Alice        | Mumbai       | Mouse    | 500
2       | 1       | Bob          | Delhi        | Keyboard | 800

-- PK = (OrderID, LineNum) — composite key
-- All cells are atomic ✅ — 1NF satisfied
-- Problem: CustomerName and CustomerCity only depend on OrderID (not on LineNum)
-- This is a PARTIAL DEPENDENCY → violates 2NF

Step 2 — Apply 2NF: Remove partial dependencies. Split into Orders and OrderItems tables.

-- Orders table: non-key attrs that depend ONLY on OrderID
Orders: (OrderID PK | CustomerName | CustomerCity)
1       | Alice        | Mumbai
2       | Bob          | Delhi

-- OrderItems table: non-key attrs that depend on (OrderID, LineNum)
OrderItems: (OrderID FK | LineNum | Product | Price)
1           | 1       | Laptop   | 60000
1           | 2       | Mouse    | 500
2           | 1       | Keyboard | 800

-- 2NF satisfied: every non-key attribute depends on the WHOLE key ✅
-- Problem: CustomerCity depends on CustomerName (not directly on OrderID)
-- If Alice moves, ALL her orders need updating → UPDATE ANOMALY

Step 3 — Apply 3NF: Remove transitive dependencies (CustomerCity depends on CustomerName, not OrderID directly).

-- Customers table: CustomerName determines CustomerCity
Customers: (CustomerID PK | CustomerName | CustomerCity)
1          | Alice        | Mumbai
2          | Bob          | Delhi

-- Orders table: reference Customer by ID
Orders: (OrderID PK | CustomerID FK)
1       | 1
2       | 2

-- 3NF satisfied: no transitive dependencies ✅
-- Each fact is stored in exactly one place

BCNF (Boyce-Codd Normal Form) is a stricter form of 3NF. The rule: for every functional dependency A → B, A must be a candidate key (or a superkey).

A table can be in 3NF but not BCNF if it has a functional dependency where the left-hand side is not a candidate key. This happens when there are overlapping candidate keys.

-- Classic BCNF violation example:
-- Table: CourseRegistration(Student, Course, Teacher)
-- Functional dependencies:
--   (Student, Course) → Teacher  [a student in a course has one teacher]
--   Teacher → Course              [each teacher teaches only one course]

-- Candidate keys: (Student, Course) and (Student, Teacher)
-- FD: Teacher → Course  violates BCNF because "Teacher" alone is not a candidate key
-- Teacher determines Course but is not a candidate key

-- BCNF fix: decompose into two tables:
TeacherCourse: (Teacher PK | Course)
             → Teacher → Course dependency now has Teacher as PK ✅

StudentTeacher: (Student | Teacher | FK references TeacherCourse)
-- Now both dependencies are preserved without BCNF violation

Anomalies are problems that arise from data redundancy in poorly designed (un-normalized) schemas. All three are prevented by proper normalization.

-- Un-normalized table (violates 2NF):
-- Orders(OrderID, CustomerName, CustomerCity, Product, Price)
OrderID | CustomerName | CustomerCity | Product  | Price
1       | Alice        | Mumbai       | Laptop   | 60000
2       | Alice        | Mumbai       | Mouse    | 500
3       | Bob          | Delhi        | Keyboard | 800

Insertion Anomaly: Cannot add a new customer until they place an order.

-- Want to register Charlie from Kolkata as a customer (no orders yet)
-- IMPOSSIBLE: OrderID is NOT NULL and there's no product to attach
-- We can't store Charlie's city until he buys something ← ANOMALY

Update Anomaly: Changing Alice's city requires updating EVERY row with Alice's name.

-- Alice moves from Mumbai to Pune
UPDATE Orders SET CustomerCity = 'Pune' WHERE CustomerName = 'Alice';
-- Must update rows 1 AND 2. If we miss row 2 → inconsistency
-- Two rows say Alice lives in different cities ← DATA CORRUPTION

Deletion Anomaly: Deleting an order can accidentally delete customer information.

-- Bob cancels his only order (OrderID=3)
DELETE FROM Orders WHERE OrderID = 3;
-- Bob's existence (name + city) is now GONE from the database
-- We've lost information about a customer because he had no orders ← ANOMALY

Denormalization is the intentional introduction of redundancy into a database schema to improve read performance. You're reversing normalization — combining tables, duplicating columns, storing derived values — to reduce the number of JOINs needed for frequently executed queries.

When to denormalize:

• Data warehouses / analytics (OLAP): reports run complex queries over billions of rows. The cost of JOINs is prohibitive. Star/snowflake schemas deliberately denormalize for read performance.
• Read-heavy, write-light tables: if a table is read 10,000 times and written once a day, the redundancy cost is trivial versus the query speed gain.
• Caching pre-computed aggregates: storing total_order_count directly on the customer row instead of counting orders on each request.

When NOT to denormalize:

• OLTP systems where data changes frequently — redundancy causes update anomalies
• When you haven't profiled to confirm JOINs are the bottleneck — premature optimization

Scenario: transferring ₹1000 from Account A (balance: ₹5000) to Account B (balance: ₹2000)

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
COMMIT;

A — Atomicity: "All or nothing"
If the system crashes after the first UPDATE (deducted from A) but before the second UPDATE (not yet added to B), the transaction is automatically rolled back. Neither UPDATE persists. The ₹1000 doesn't vanish into thin air. Implemented via: undo logs (for rollback).

C — Consistency: "Valid state to valid state"
Before the transaction: A=5000, B=2000, total=7000. After: A=4000, B=3000, total=7000. The total always equals 7000 — money is conserved. The database enforces all constraints (NOT NULL, foreign keys, CHECK constraints) throughout. Implemented via: constraint checking.

I — Isolation: "As if transactions ran one at a time"
If another transaction reads Account A's balance concurrently during the transfer, it either sees the original 5000 (before transfer) or 4000 (after commit) — never an inconsistent intermediate state like 5000 deducted but B not yet credited. Implemented via: locking or MVCC.

D — Durability: "Committed data survives crashes"
Once COMMIT succeeds, the transfer is permanent. Even if the server loses power 1 millisecond after the COMMIT, the data is safe. Implemented via: redo logs (WAL — Write-Ahead Log), which persist changes to durable storage before confirming the commit.

-- Complete transaction pattern
START TRANSACTION;  -- or BEGIN / BEGIN TRANSACTION

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

-- Check if business rule satisfied (application layer)
-- IF error → ROLLBACK, otherwise → COMMIT

COMMIT;    -- make all changes permanent (durable)
-- or
ROLLBACK; -- undo ALL changes since START TRANSACTION

-- SAVEPOINT: partial rollback
START TRANSACTION;
INSERT INTO orders VALUES (101, 5, 'pending');
SAVEPOINT after_order;
INSERT INTO payments VALUES (201, 101, 500);
-- Payment fails
ROLLBACK TO after_order;   -- undo payment only, keep order
COMMIT;                    -- order committed, payment rolled back

Transaction states:

• Active: transaction is executing
• Partially Committed: last statement executed, changes not yet written to disk
• Committed: changes durably written — cannot be rolled back
• Failed: an error occurred during execution
• Aborted: rolled back — database restored to pre-transaction state

Isolation levels control how much concurrent transactions can "see" each other's in-progress changes. Higher isolation = fewer anomalies but lower concurrency and throughput.

Three anomalies to know:

• Dirty Read: reading uncommitted data from another transaction (data that might still be rolled back)
• Non-Repeatable Read: reading the same row twice in a transaction and getting different values (another transaction committed a change between reads)
• Phantom Read: running the same query twice and getting different rows (another transaction inserted/deleted rows that match your WHERE clause)

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

-- Default isolation levels:
-- MySQL InnoDB: REPEATABLE READ (with snapshot isolation for reads)
-- PostgreSQL: READ COMMITTED
-- Oracle: READ COMMITTED
-- SQL Server: READ COMMITTED

A window function performs calculations across a set of related rows (a "window") without collapsing them into a single output row — unlike GROUP BY which collapses. The window is defined by the OVER() clause.

-- employees(emp_id, dept_id, name, salary)

-- Running total of salaries ordered by emp_id
SELECT
    emp_id, name, salary,
    SUM(salary) OVER (ORDER BY emp_id) AS running_total
FROM employees;

-- Result:
-- emp_id | name  | salary | running_total
-- 1      | Alice | 60000  | 60000
-- 2      | Bob   | 45000  | 105000
-- 3      | Carol | 70000  | 175000   ← each row keeps its data + cumulative sum

-- Salary vs department average (PARTITION BY)
SELECT
    emp_id, name, dept_id, salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg
FROM employees;
-- Without PARTITION BY: window is the entire table
-- With PARTITION BY dept_id: separate window per department

-- Scenario: 4 employees with salaries [90k, 85k, 85k, 70k]
SELECT
    name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- Result:
-- name  | salary | row_num | rnk | dense_rnk
-- Alice | 90000  |    1    |  1  |     1
-- Bob   | 85000  |    2    |  2  |     2    ← tie
-- Carol | 85000  |    3    |  2  |     2    ← same dense_rnk as Bob
-- Dave  | 70000  |    4    |  4  |     3    ← RANK skips 3, DENSE_RANK doesn't

-- Classic use: top-3 salaries per department using DENSE_RANK
SELECT * FROM (
    SELECT emp_id, name, dept_id, salary,
           DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr
    FROM employees
) ranked
WHERE dr <= 3;  -- top-3 salaries in each department

LAG() and LEAD() access values from previous or subsequent rows within the same window — without a self-join.

-- monthly_sales(month, total_sales)
-- Calculate month-over-month sales change
SELECT
    month,
    total_sales,
    LAG(total_sales, 1, 0)  OVER (ORDER BY month) AS prev_month_sales,
    total_sales - LAG(total_sales, 1, 0) OVER (ORDER BY month) AS mom_change,
    LEAD(total_sales, 1)    OVER (ORDER BY month) AS next_month_sales
FROM monthly_sales;

-- LAG(col, offset, default): value from 'offset' rows BEFORE current row
-- LEAD(col, offset, default): value from 'offset' rows AFTER current row
-- The third arg is the default value when there's no previous/next row

-- Result:
-- month   | total_sales | prev_month | mom_change | next_month
-- 2025-01 |   100000    |     0      |   100000   |   120000
-- 2025-02 |   120000    |   100000   |    20000   |    95000
-- 2025-03 |    95000    |   120000   |   -25000   |    NULL

A trigger is a stored program that automatically fires in response to a DML event (INSERT, UPDATE, or DELETE) on a specific table — BEFORE or AFTER the event.

-- Audit trigger: log every salary change
DELIMITER //
CREATE TRIGGER salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary != OLD.salary THEN
        INSERT INTO salary_changes(emp_id, old_sal, new_sal, changed_at)
        VALUES(OLD.emp_id, OLD.salary, NEW.salary, NOW());
    END IF;
END //
DELIMITER ;

-- BEFORE trigger: validate data before inserting
CREATE TRIGGER validate_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END //

Good uses of triggers:

• Audit logging (who changed what and when)
• Enforcing complex business rules that CHECK constraints can't handle
• Automatically maintaining derived/summary columns
• Preventing invalid operations with BEFORE triggers

When to avoid triggers:

• Complex business logic — triggers are invisible to application developers, making debugging nightmarish
• Performance-critical paths — triggers add overhead to every DML operation
• Cascading triggers (trigger calls trigger calls trigger) — virtually impossible to reason about
• When application-layer logic would serve just as well