SQL Cheat Sheet

⚡Quick Reference

📦Database Basics

🗂️SQL Command Categories

⚡Execution Order CRITICAL

You write SQL in one order. The database runs it in a completely different order.

📝SELECT — the master skeleton

SELECT    column1, column2, COUNT(*)
FROM      table1
JOIN      table2 ON table1.id = table2.fk
WHERE     row_filter_condition
GROUP BY  column1
HAVING    group_filter_condition
ORDER BY  column1 ASC, column2 DESC
LIMIT     10;

Each clause is optional except SELECT. Use only what you need.

🔍WHERE Operators

🔢ORDER BY · LIMIT

SELECT name, salary
FROM Employees
ORDER BY salary DESC, name ASC
LIMIT 5;

• ASC = ascending (default, small→large)
• DESC = descending (large→small)
• Can sort by multiple columns — secondary sort breaks ties
• LIMIT N = first N rows · LIMIT N OFFSET M = skip M then take N
• ORDER BY can use SELECT aliases (e.g., ORDER BY total DESC)

🔗JOINs — all six types

-- Basic INNER JOIN
SELECT c.name, o.amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;

-- Find customers who never ordered (LEFT JOIN trick)
SELECT c.name
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- SELF JOIN: each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.emp_id;

📊GROUP BY · Aggregates · HAVING

SELECT department, COUNT(*), AVG(salary)
FROM Employees
WHERE status = 'active'      -- filter rows BEFORE grouping
GROUP BY department
HAVING COUNT(*) > 5;     -- filter groups AFTER aggregation

🪆Subqueries

Scalar subquery — returns one value

-- Employees above company average
SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

IN-list subquery — returns a list

SELECT name FROM Customers
WHERE customer_id IN (SELECT customer_id FROM Orders WHERE amount > 5000);

Correlated subquery — references outer query

-- Employees earning above their OWN department average
SELECT name, salary
FROM Employees e1
WHERE salary > (
    SELECT AVG(salary) FROM Employees e2
    WHERE e2.dept_id = e1.dept_id   -- references outer row
);

🧱CTEs (WITH clause)

A CTE is a named temporary result set. Use it to break complex queries into readable layers.

WITH customer_totals AS (
    SELECT customer_id, city, SUM(amount) AS total
    FROM Orders JOIN Customers USING(customer_id)
    GROUP BY customer_id
)
SELECT name, total
FROM customer_totals
WHERE total > (
    SELECT AVG(total) FROM customer_totals
);

∪UNION · DISTINCT · Set Ops

SELECT name FROM Customers
UNION
SELECT name FROM Suppliers;

SELECT DISTINCT city FROM Customers;

⚠️NULL Handling CRITICAL

NULL means "unknown value" — not zero, not empty string. Special rules apply.

Replacing NULL with a default

COALESCE(column, default_value)        -- ANSI standard, multiple args
IFNULL(column, default_value)          -- MySQL, SQLite
NVL(column, default_value)             -- Oracle
ISNULL(column, default_value)          -- SQL Server

🔐Keys & Constraints

CREATE TABLE Employees (
    emp_id   INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    email    TEXT UNIQUE,
    dept_id  INTEGER,
    salary   REAL CHECK (salary > 0),
    status   TEXT DEFAULT 'active',
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

🧬Normalization

Anomalies normalization prevents

• Insert anomaly — can't add data without dummy values
• Update anomaly — same fact stored in multiple rows; must update everywhere
• Delete anomaly — deleting one row loses unrelated info

📇Indexes

🛠️Views · Triggers · Procedures · Functions

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

-- Trigger (logs every salary change)
CREATE TRIGGER log_salary_change
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END;

🛡️ACID Properties

💸Transactions

BEGIN TRANSACTION;     -- or START TRANSACTION
    UPDATE accounts SET balance = balance - 3000 WHERE name = 'Ahmad';
    UPDATE accounts SET balance = balance + 3000 WHERE name = 'Sara';
COMMIT;                  -- save permanently
-- or ROLLBACK to undo everything since BEGIN

SAVEPOINT — partial rollback

BEGIN TRANSACTION;
    UPDATE ...;
    SAVEPOINT step1;
    UPDATE ...;          -- if this is wrong:
ROLLBACK TO step1;       -- undoes only after the savepoint
COMMIT;

🔒Isolation Levels & Read Phenomena

The 3 read anomalies

4 isolation levels (weakest → strongest)

✅ = prevents the anomaly · ❌ = allows it

🎯Top 15 Gotchas EXAM

1. NULL = NULL returns UNKNOWN — always use IS NULL
2. WHERE filters rows; HAVING filters groups
3. SELECT aliases NOT available in WHERE (WHERE runs before SELECT)
4. SELECT aliases ARE available in ORDER BY (runs after SELECT)
5. INNER JOIN uses ON, not WHERE for the join condition
6. GROUP BY: every non-aggregated column in SELECT must be grouped
7. SUM/AVG/MIN/MAX on empty/all-NULL = NULL, not 0
8. AVG ignores NULLs — both numerator AND denominator change
9. COUNT(*) counts NULLs; COUNT(column) doesn't
10. LIMIT without ORDER BY = unpredictable rows
11. TRUNCATE is DDL (no rollback); DELETE is DML (can rollback)
12. CROSS JOIN (no ON) = Cartesian product — usually a bug
13. Single quotes for text values: 'Lahore', not "Lahore"
14. LIKE: % = any chars, _ = exactly one char
15. UNION removes duplicates; UNION ALL keeps them (faster)

🎤Interview Q&A — prepared answers

📚Bonus topics — quick mention

CASE WHEN — inline if/else

SELECT name,
       CASE
           WHEN salary < 50000 THEN 'Junior'
           WHEN salary < 100000 THEN 'Mid'
           ELSE 'Senior'
       END AS level
FROM Employees;

Window functions — aggregate without collapsing rows

SELECT name, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM Employees;

Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM/AVG/COUNT OVER(...).

Date functions (vary by database)

NOW()                   -- current timestamp
CURRENT_DATE            -- today's date
DATE('2024-01-15')      -- cast to date
YEAR(date_col)         -- extract year
DATEDIFF(d1, d2)       -- difference in days

String functions

UPPER(s) LOWER(s)      -- case conversion
LENGTH(s)              -- string length
SUBSTR(s, start, len)  -- substring
TRIM(s)                -- remove leading/trailing spaces
CONCAT(s1, s2) or s1 || s2  -- combine
REPLACE(s, old, new)   -- replace substring

NoSQL families (recognition only)