Data types

Operators

Functions, by family

ABS(-4)          -- 4
ROUND(3.456, 2)  -- 3.46
CEIL(4.1)        -- 5
FLOOR(4.9)       -- 4
POWER(2, 10)     -- 1024
SQRT(81)         -- 9
MOD(17, 5)       -- 2

CONCAT(first, ' ', last)
SUBSTRING(name FROM 1 FOR 3)
LENGTH(name)
UPPER(name) / LOWER(name)
TRIM('  padded  ')
REPLACE(url, 'http:', 'https:')
LPAD(id::text, 6, '0')

CURRENT_DATE / CURRENT_TIMESTAMP
DATE_ADD(hired_on, INTERVAL 30 DAY)   -- MySQL
hired_on + INTERVAL '30 days'         -- Postgres
DATEDIFF(day, hired_on, CURRENT_DATE) -- SQL Server
EXTRACT(YEAR FROM hired_on)
DATE_TRUNC('month', hired_on)         -- Postgres

COUNT(*) / COUNT(DISTINCT col)
SUM(amount)
AVG(amount)
MIN(amount) / MAX(amount)
STRING_AGG(name, ', ')     -- Postgres/SQL Server
GROUP_CONCAT(name)         -- MySQL

Joins

-- INNER: only customers who have placed an order
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

-- LEFT: every customer, with NULLs for those who haven't ordered
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- ANTI-JOIN pattern: customers with NO orders at all
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- SELF JOIN: employees paired with their manager
SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Pivot & unpivot

Pivoting turns row values into columns (long → wide); unpivoting does the reverse (wide → long). Syntax is the least standardized part of SQL here — some engines have dedicated keywords, others lean on conditional aggregation.

SELECT
  department,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue END) AS q4
FROM sales
GROUP BY department;

SELECT department, [Q1], [Q2], [Q3], [Q4]
FROM sales
PIVOT (
  SUM(revenue) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS p;

SELECT department, quarter, revenue
FROM sales
UNPIVOT (
  revenue FOR quarter IN (q1, q2, q3, q4)
) AS u;

Commit & rollback

Whether a DML statement takes effect immediately depends on the connection's autocommit setting. With autocommit off, nothing is durable until COMMIT runs; ROLLBACK discards everything back to the last commit or savepoint.

Higher isolation buys correctness at the cost of concurrency — SERIALIZABLE can force transactions to retry when they'd otherwise conflict.

Views

A view is a stored SELECT that behaves like a read-only (or sometimes updatable) table — useful for hiding join complexity, restricting which columns a role can see, or reusing a common query shape.

CREATE VIEW active_employees AS
SELECT id, full_name, department
FROM employees
WHERE terminated_on IS NULL;

CREATE OR REPLACE VIEW active_employees AS ...
DROP VIEW active_employees;

Subqueries

SELECT full_name
FROM employees
WHERE department IN (
  SELECT id FROM departments
  WHERE region = 'APAC'
);

SELECT e.full_name
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

Subqueries can sit in SELECT (scalar), FROM (as a derived table), or WHERE — a correlated one in WHERE is easy to write but often slower than the equivalent JOIN or window function.

CTEs (WITH clause)

A common table expression names a subquery so it can be referenced once and read top-to-bottom, instead of nesting derived tables inside FROM. Most engines don't materialize a CTE by default — it's inlined like a view, so it isn't automatically faster than the equivalent subquery.

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.full_name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.department = e.department
WHERE e.salary > d.avg_salary;

WITH RECURSIVE org_chart AS (
  SELECT id, full_name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- anchor: the top of the tree

  UNION ALL

  SELECT e.id, e.full_name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id   -- recursive step
)
SELECT * FROM org_chart ORDER BY depth;

Window functions

A window function computes across a set of rows related to the current one — via PARTITION BY — without collapsing them into one row the way GROUP BY does.

SELECT
  full_name,
  department,
  salary,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  LAG(salary)  OVER (PARTITION BY department ORDER BY salary DESC) AS next_lowest,
  SUM(salary)  OVER (PARTITION BY department
                      ORDER BY salary DESC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

Stored procedures

A named, precompiled block of SQL and control-flow logic, called rather than sent as a fresh statement each time. Syntax varies more than anything else in SQL — this is Postgres' PL/pgSQL shape.

CREATE PROCEDURE give_raise(emp_id INT, pct NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE employees
  SET salary = salary * (1 + pct / 100)
  WHERE id = emp_id;
END;
$$;

CALL give_raise(101, 5);

Triggers

Code that fires automatically around an INSERT, UPDATE, or DELETE — before or after the row change is applied. Common uses: audit logging, enforcing a rule a CHECK constraint can't express, keeping a derived column in sync.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
  VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Transactions & ACID

Best practices

Normalization

Each normal form removes a specific kind of redundancy. Most application schemas stop at 3NF; analytics tables often denormalize deliberately for read speed.

ER modeling

Entities become tables, attributes become columns, and relationships become foreign keys — except many-to-many, which needs a junction table in between.

Order and Product have a many-to-many relationship — one order can contain many products, and one product appears on many orders — resolved through the Order_Item junction table, which turns it into two one-to-many relationships.

Data shapes & modeling

Beyond normalizing a single schema, how tables relate to each other at a system level depends on what the data is for — transactional systems and analytical systems tend to converge on different shapes.

-- wide: one row per employee
-- id | q1_sales | q2_sales | q3_sales

-- long: one row per (employee, quarter)
-- id | quarter | sales

Star schema

A central fact table (the events being measured — orders, clicks, sales) with foreign keys out to surrounding dimension tables (who, what, when, where) that describe those events. A snowflake schema is the same idea with dimensions further normalized into sub-dimensions.

-- fact table: one row per line item sold
fact_sales(sale_id, date_id, customer_id, product_id, store_id, quantity, revenue)

-- dimension tables describe the "who/what/when/where"
dim_date(date_id, day, month, quarter, year)
dim_customer(customer_id, name, region)
dim_product(product_id, name, category)
dim_store(store_id, city, country)

SQL injection

Injection happens when untrusted input is concatenated directly into a SQL string, letting the input change the query's structure instead of just supplying a value.

-- application code builds this string directly
"SELECT * FROM users WHERE username = '" + input + "'"

SELECT * FROM users WHERE username = ?;
-- driver binds `input` as a value, never as SQL text

Backups & scalable architecture

Two numbers frame the plan: RPO (recovery point objective — how much recent data you can afford to lose) and RTO (recovery time objective — how long you can afford to be down).

Scaling paths