The Complete SQL Reference Guide
From Basics to Advanced Window Functions - With Examples
1. SQL Basics
SQL is the standard language for relational databases. Core statement:
SELECT column1, column2 FROM table_name WHERE condition ORDER BY col DESC;
Examples:
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;
SELECT DISTINCT country FROM customers;
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 10;
2. JOIN Types
INNER JOIN - rows matching in both tables:
SELECT [Link], d.dept_name FROM employees e
INNER JOIN departments d ON e.dept_id = [Link];
LEFT JOIN - all rows from left, NULLs for non-matching right rows:
SELECT [Link], d.dept_name FROM employees e
LEFT JOIN departments d ON e.dept_id = [Link];
FULL OUTER JOIN - all rows from both tables:
SELECT * FROM a FULL OUTER JOIN b ON [Link] = [Link];
Self Join:
SELECT [Link] AS emp, [Link] AS mgr
FROM employees a JOIN employees b ON a.manager_id = [Link];
3. Aggregation & Grouping
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Rule: WHERE filters BEFORE grouping; HAVING filters AFTER grouping.
Aggregate functions: COUNT, SUM, AVG, MIN, MAX, STDDEV, VARIANCE.
4. Subqueries & CTEs
Subquery in WHERE:
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
CTE (Common Table Expression) - reusable and readable:
WITH high_earners AS (
Page 1
SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) FROM high_earners GROUP BY department;
Recursive CTE - for hierarchical/tree data:
WITH RECURSIVE org AS (
SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT [Link], [Link], e.manager_id
FROM employees e JOIN org ON e.manager_id = [Link]
)
SELECT * FROM org;
5. Window Functions
Window functions compute values across rows related to the current row.
ROW_NUMBER - unique sequential rank:
SELECT name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees;
LAG / LEAD - access previous or next row value:
SELECT date, revenue,
LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY date) AS change
FROM daily_sales;
Running total:
SELECT date, SUM(revenue) OVER (ORDER BY date) AS running_total
FROM daily_sales;
RANK() skips numbers after ties; DENSE_RANK() does not skip.
6. Data Modification
INSERT:
INSERT INTO employees (name, department, salary)
VALUES ('Ahmed', 'Engineering', 90000);
UPDATE:
UPDATE employees SET salary = salary * 1.10
WHERE department = 'Engineering';
DELETE:
DELETE FROM employees WHERE last_active < '2020-01-01';
UPSERT (PostgreSQL):
INSERT INTO products (id, price) VALUES (1, 99.99)
ON CONFLICT (id) DO UPDATE SET price = [Link];
Page 2
TRUNCATE - remove all rows instantly (no WHERE clause):
TRUNCATE TABLE session_logs;
7. Indexes & Query Performance
An index speeds reads but adds overhead to writes.
Create index:
CREATE INDEX idx_emp_dept ON employees(department);
CREATE UNIQUE INDEX idx_email ON users(email);
Composite index (column order matters):
CREATE INDEX idx_dept_salary ON employees(department, salary);
View query plan:
EXPLAIN SELECT * FROM employees WHERE department = 'Sales';
EXPLAIN ANALYZE SELECT ...; -- actually executes and shows real timing
Performance tips:
- Filter on indexed columns in WHERE clauses
- Avoid SELECT * in production - list only needed columns
- Use CTEs for readability but be aware of materialization behavior
- Partition large tables by date or category for faster scans
8. Useful SQL Patterns
Deduplication - keep latest row per group:
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM events
)
SELECT * FROM ranked WHERE rn = 1;
Pivot (with CASE):
SELECT user_id,
SUM(CASE WHEN product = 'A' THEN amount ELSE 0 END) AS product_A,
SUM(CASE WHEN product = 'B' THEN amount ELSE 0 END) AS product_B
FROM sales GROUP BY user_id;
Cumulative % of total:
SELECT category, revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM revenue_by_category;
Page 3