SQL — Complete Reference Guide

1What is SQL

SQL (Structured Query Language) is the standard language for storing, manipulating, and retrieving data in relational databases. Developed at IBM in the 1970s, it became an ANSI standard in 1986 and is now supported by every major database: MySQL, PostgreSQL, SQL Server, Oracle, SQLite, and MariaDB.

SQL is declarative — you describe what data you want, not how to get it. The query optimizer figures out an efficient execution plan. This separation is why SQL has dominated for five decades.

SQL operations fall into four categories: DDL (CREATE, ALTER, DROP — define schema), DML (SELECT, INSERT, UPDATE, DELETE — work with data), DCL (GRANT, REVOKE — manage permissions), and TCL (BEGIN, COMMIT, ROLLBACK — manage transactions).

2RDBMS & SQL Syntax

A Relational Database Management System (RDBMS) organises data into tables (relations). Each table has named columns with defined data types and rows (records). Tables link through primary keys and foreign keys. Popular systems: MySQL, PostgreSQL, SQLite, SQL Server, Oracle.

Every SQL statement ends with a semicolon (;). Whitespace is ignored — you can spread a query across lines. String values use single quotes ('Ada'). Column/table names that conflict with reserved words need backticks (MySQL) or double quotes (ANSI).

-- Keywords case-insensitive — both are identical
SELECT first_name FROM employees;
select first_name from employees;

-- Multi-line for readability
SELECT
    first_name,
    last_name,
    salary
FROM employees
WHERE salary > 50000;

-- Single quotes for strings
SELECT * FROM employees WHERE first_name = 'Ada';

-- Quoting reserved words
SELECT "order", "group" FROM my_table;  -- ANSI
SELECT `order`, `group` FROM my_table;  -- MySQL

3SELECT Statement

SELECT is the most frequently used SQL statement. It retrieves data from one or more tables and returns a virtual result set. The logical evaluation order is: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

This order explains why you cannot use a SELECT alias in a WHERE clause — WHERE runs before SELECT.

-- All columns (avoid in production — fetches unnecessary data)
SELECT * FROM employees;

-- Specific columns
SELECT first_name, last_name, salary FROM employees;

-- Arithmetic and expressions
SELECT first_name, salary, salary * 12 AS annual_salary FROM employees;

-- String concatenation
SELECT first_name || ' ' || last_name AS full_name FROM employees;   -- PostgreSQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; -- MySQL

-- Multi-expression
SELECT
    first_name,
    salary,
    ROUND(salary * 0.20, 2) AS tax,
    ROUND(salary * 0.80, 2) AS take_home
FROM employees;

4SELECT DISTINCT

DISTINCT eliminates duplicate rows from the result set. It operates on the entire selected row — if you select two columns, rows are distinct only if both columns differ.

Common uses: list all unique departments, all countries a company ships to, all distinct error codes in a log table.

-- Without DISTINCT — may return duplicates
SELECT department FROM employees;
-- Engineering, Engineering, Design, Engineering ...

-- With DISTINCT — each unique value once
SELECT DISTINCT department FROM employees;
-- Engineering, Design, Product, Marketing

-- DISTINCT on multiple columns
SELECT DISTINCT department, job_title FROM employees;

-- COUNT DISTINCT
SELECT COUNT(DISTINCT department) AS num_departments FROM employees;
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

5WHERE Clause

WHERE filters rows before they reach the rest of the query. Only rows where the condition evaluates to TRUE are included. Rows where the condition is FALSE or NULL are excluded.

Comparison operators: =, != / <>, >, >=, <, <=. NULL checks always use IS NULL or IS NOT NULL, never = NULL.

-- Equality / inequality
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE department != 'Engineering';
SELECT * FROM employees WHERE department <> 'Engineering';  -- ANSI

-- Numeric comparison
SELECT * FROM employees WHERE salary > 100000;
SELECT * FROM employees WHERE salary BETWEEN 80000 AND 120000;

-- String filter
SELECT * FROM customers WHERE country = 'Germany';

-- Date comparison
SELECT * FROM orders WHERE order_date > '2024-01-01';

-- NULL — always IS NULL or IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;   -- top-level managers
SELECT * FROM employees WHERE phone IS NOT NULL;    -- have a phone

6AND, OR, NOT Operators

Combine multiple conditions: AND (both true), OR (at least one true), NOT (inverts). Precedence: NOT first, then AND, then OR. Use parentheses to make intent explicit.

-- AND
SELECT * FROM employees WHERE department = 'Engineering' AND salary > 100000;

-- OR
SELECT * FROM employees WHERE department = 'Engineering' OR department = 'Design';

-- NOT
SELECT * FROM employees WHERE NOT department = 'HR';

-- Parentheses matter — without them AND binds tighter than OR
-- This: Engineering (any salary) OR (Design AND salary > 90000)
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Design' AND salary > 90000;

-- This (with parens): (Engineering OR Design) AND salary > 90000
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Design') AND salary > 90000;

-- Multiple conditions
SELECT * FROM orders
WHERE status = 'pending'
  AND total_amount > 500
  AND created_at > '2024-06-01'
  AND customer_country IN ('US', 'CA', 'GB');

7ORDER BY

ORDER BY sorts the result set. Without it, SQL makes no guarantee about row order — different runs may return rows in different orders. ASC (default) sorts ascending; DESC sorts descending. NULLs sort last in ASC and first in DESC by default.

-- Ascending (default)
SELECT * FROM employees ORDER BY last_name;
SELECT * FROM employees ORDER BY last_name ASC;

-- Descending
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple columns — primary sort then tiebreaker
SELECT * FROM employees ORDER BY department ASC, salary DESC;

-- ORDER BY expression or alias (runs after SELECT)
SELECT first_name, salary * 12 AS annual FROM employees ORDER BY annual DESC;

-- NULLS placement (PostgreSQL / Oracle)
SELECT * FROM employees ORDER BY manager_id ASC NULLS LAST;
SELECT * FROM employees ORDER BY manager_id DESC NULLS FIRST;

8INSERT INTO

INSERT INTO adds new rows to a table. Specify column names explicitly — it's more resilient to schema changes. Insert multiple rows in one statement for much better performance than looping single inserts.

-- Single row — always list columns
INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES ('Ada', 'Lovelace', 'ada@example.com', 'Engineering', 95000);

-- Multiple rows at once (much faster than looping)
INSERT INTO employees (first_name, last_name, email, salary) VALUES
  ('Grace',  'Hopper',   'grace@example.com',  105000),
  ('Donald', 'Knuth',    'donald@example.com',   98000),
  ('Linus',  'Torvalds', 'linus@example.com',  130000);

-- INSERT from SELECT — copy rows from another table
INSERT INTO archived_employees SELECT * FROM employees WHERE is_active = FALSE;

-- RETURNING generated ID (PostgreSQL)
INSERT INTO orders (customer_id, total) VALUES (42, 299.99) RETURNING id;

-- UPSERT — insert or update on conflict (PostgreSQL)
INSERT INTO employees (id, email, salary)
VALUES (1, 'ada@example.com', 100000)
ON CONFLICT (id) DO UPDATE
  SET salary = EXCLUDED.salary, email = EXCLUDED.email;

-- MySQL equivalent
INSERT INTO employees (id, email, salary) VALUES (1, 'ada@example.com', 100000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

9NULL Values

NULL represents the absence of a value — not zero, not empty string, not false. It means "unknown." NULL propagates through arithmetic: 5 + NULL = NULL. Any comparison with NULL using = or != returns NULL (neither TRUE nor FALSE), so the row is excluded from WHERE.

-- Always use IS NULL / IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- NULL arithmetic always returns NULL
SELECT 100 + NULL;                   -- NULL
SELECT salary + NULL AS result FROM employees;  -- all NULL

-- Aggregates and NULL
SELECT
  COUNT(*)          AS total_rows,   -- counts ALL rows
  COUNT(manager_id) AS has_manager,  -- non-NULL manager_id only
  AVG(salary)       AS avg_salary    -- ignores NULL salaries
FROM employees;

-- NULL-safe equality (PostgreSQL)
SELECT * FROM a JOIN b ON a.val IS NOT DISTINCT FROM b.val;
-- MySQL null-safe equals
SELECT * FROM a JOIN b ON a.val <=> b.val;

10UPDATE Statement

UPDATE modifies existing rows. Always include a WHERE clause — omitting it updates every row in the table. Best practice: run the equivalent SELECT first to confirm exactly which rows will change.

-- Update a single column
UPDATE employees SET salary = 110000 WHERE id = 42;

-- Update multiple columns
UPDATE employees
SET salary = 120000, department = 'Senior Engineering', updated_at = NOW()
WHERE id = 42;

-- Update a group
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';

-- Update using a subquery
UPDATE employees
SET department_id = (SELECT id FROM departments WHERE name = 'Engineering')
WHERE department = 'Engineering' AND department_id IS NULL;

-- Update with JOIN (PostgreSQL)
UPDATE employees e
SET department_id = d.id
FROM departments d
WHERE d.name = e.department AND e.department_id IS NULL;

-- Safe pattern: SELECT first to verify scope
SELECT id, salary FROM employees WHERE department = 'Engineering';
-- then:
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';

11DELETE & TRUNCATE

DELETE removes specific rows (with WHERE). TRUNCATE removes all rows — far faster than DELETE. DROP removes the entire table structure. Each is appropriate in different situations.

-- Delete specific rows
DELETE FROM employees WHERE id = 42;
DELETE FROM employees WHERE is_active = FALSE AND hire_date < '2018-01-01';

-- Delete using subquery
DELETE FROM employees WHERE department_id IN (
  SELECT id FROM departments WHERE budget < 10000
);

-- NULL-safe anti-join delete
DELETE FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);

-- TRUNCATE — removes all rows, much faster, no WHERE, no row triggers
TRUNCATE TABLE temp_staging;

-- DROP — removes table and structure entirely
DROP TABLE temp_staging;
DROP TABLE IF EXISTS temp_staging;

12LIMIT / TOP / FETCH FIRST

Limiting results is essential for pagination and top-N queries. Syntax differs by database — below are the common variants.

-- MySQL / PostgreSQL / SQLite
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

-- With OFFSET — skip first N rows (page 3 of 20-per-page)
SELECT * FROM employees ORDER BY id LIMIT 20 OFFSET 40;

-- SQL Server
SELECT TOP 10 * FROM employees ORDER BY salary DESC;
SELECT TOP 10 PERCENT * FROM employees ORDER BY salary DESC;

-- ANSI SQL (PostgreSQL 8.4+, Oracle 12c+, SQL Server 2012+)
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;

-- FETCH with OFFSET
SELECT * FROM employees ORDER BY id
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- Top earner per department (window function)
SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 1;

13MIN and MAX

MIN() and MAX() find the smallest and largest values in a column. They work on numbers, dates, and strings (alphabetical). Both ignore NULL values. Use with GROUP BY to get per-group extremes.

-- Basic
SELECT MIN(salary) AS lowest, MAX(salary) AS highest FROM employees;

-- On dates
SELECT MIN(hire_date) AS first_hire, MAX(hire_date) AS latest_hire FROM employees;

-- On strings
SELECT MIN(last_name) AS first_alpha, MAX(last_name) AS last_alpha FROM employees;

-- Per group
SELECT department, MIN(salary) AS floor_salary, MAX(salary) AS top_salary
FROM employees
GROUP BY department ORDER BY top_salary DESC;

-- Row with the minimum value
SELECT * FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

-- Cheapest product per category
SELECT p.category, p.product_name, p.price
FROM products p
JOIN (SELECT category, MIN(price) AS min_price FROM products GROUP BY category) m
  ON p.category = m.category AND p.price = m.min_price;

14COUNT, SUM, AVG

These aggregate functions collapse many rows into a single computed value. All except COUNT(*) ignore NULL values. They are almost always used with GROUP BY.

-- COUNT(*) counts all rows; COUNT(col) counts non-NULLs
SELECT COUNT(*) FROM employees;
SELECT COUNT(phone) AS employees_with_phone FROM employees;
SELECT COUNT(DISTINCT department) AS num_departments FROM employees;

-- SUM
SELECT SUM(salary) AS total_payroll FROM employees;
SELECT SUM(quantity * unit_price) AS revenue FROM order_items;

-- AVG (ignores NULLs)
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

-- All in one
SELECT
  COUNT(*)       AS headcount,
  SUM(salary)    AS total_payroll,
  ROUND(AVG(salary), 0) AS avg_salary,
  MIN(salary)    AS min_salary,
  MAX(salary)    AS max_salary
FROM employees WHERE is_active = TRUE;

-- Per group
SELECT
  department,
  COUNT(*)              AS headcount,
  ROUND(AVG(salary), 0) AS avg_sal,
  SUM(salary)           AS payroll
FROM employees
GROUP BY department
ORDER BY payroll DESC;

15LIKE & Wildcards

LIKE performs pattern matching. % matches any sequence of zero or more characters; _ matches exactly one character. LIKE is case-insensitive in MySQL; case-sensitive in PostgreSQL (use ILIKE for case-insensitive there).

-- % wildcard
SELECT * FROM employees WHERE first_name LIKE 'A%';    -- starts with A
SELECT * FROM employees WHERE last_name  LIKE '%son';  -- ends with son
SELECT * FROM employees WHERE email      LIKE '%@gmail.com';
SELECT * FROM employees WHERE first_name LIKE '%an%';  -- contains "an"

-- _ wildcard (exactly one character)
SELECT * FROM employees WHERE first_name LIKE '_da';   -- Ada, Ida...
SELECT * FROM products  WHERE code       LIKE 'PRD-___'; -- PRD- + 3 chars

-- NOT LIKE
SELECT * FROM employees WHERE email NOT LIKE '%@company.com';

-- PostgreSQL case-insensitive
SELECT * FROM employees WHERE first_name ILIKE 'ada';  -- Ada, ADA, ada

-- Escape literal % or _
SELECT * FROM products WHERE description LIKE '50\% off%' ESCAPE '\';

16IN and NOT IN

IN is shorthand for multiple OR conditions. NOT IN excludes all listed values. Be careful: if the NOT IN list contains NULLs (e.g., from a subquery), the entire NOT IN returns NULL and no rows match. Use NOT EXISTS as the NULL-safe alternative.

-- IN with literal list
SELECT * FROM employees WHERE department IN ('Engineering', 'Design', 'Product');

-- NOT IN
SELECT * FROM employees WHERE department NOT IN ('HR', 'Legal', 'Facilities');

-- IN with subquery
SELECT * FROM employees WHERE department_id IN (
  SELECT id FROM departments WHERE budget > 500000
);

-- NOT IN danger — if subquery returns NULL, nothing matches
SELECT * FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);  -- DANGEROUS if any manager_id is NULL

-- Safe: filter NULLs in subquery
SELECT * FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

-- Safest: NOT EXISTS (always NULL-safe)
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees mgr WHERE mgr.manager_id = e.id);

17BETWEEN

BETWEEN tests whether a value falls within a range, inclusive of both endpoints (>= lower AND <= upper). Works with numbers, dates, and strings. NOT BETWEEN excludes the range.

-- Numeric (inclusive)
SELECT * FROM employees WHERE salary BETWEEN 80000 AND 120000;
-- Equivalent: salary >= 80000 AND salary <= 120000

-- NOT BETWEEN
SELECT * FROM employees WHERE salary NOT BETWEEN 80000 AND 120000;

-- Date range
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- String (alphabetical)
SELECT * FROM employees WHERE last_name BETWEEN 'A' AND 'M';

-- Safer date range (avoids timestamp edge cases)
SELECT * FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';

18Column & Table Aliases

Aliases give a column or table a temporary name for the query's duration. Column aliases appear in result headers. Table aliases shorten names — essential for self-joins. AS is optional but recommended for clarity.

-- Column alias
SELECT first_name AS "First Name", salary * 12 AS annual_salary FROM employees;

-- Expression alias
SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  salary * 0.20                       AS estimated_tax
FROM employees;

-- Table alias
SELECT e.first_name, e.salary, d.name AS department
FROM employees e JOIN departments d ON e.department_id = d.id;

-- Self-join requires aliases (same table, two roles)
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

-- Alias in ORDER BY works (ORDER BY runs after SELECT)
SELECT first_name, salary * 12 AS annual FROM employees ORDER BY annual DESC;

-- Alias in WHERE does NOT work (WHERE runs before SELECT)
-- WRONG: SELECT salary * 12 AS annual FROM employees WHERE annual > 100000;
-- RIGHT:
SELECT salary * 12 AS annual FROM employees WHERE salary * 12 > 100000;

19INNER JOIN

INNER JOIN returns only rows where the join condition matches in BOTH tables. Rows in either table without a match are excluded. INNER JOIN is the default when you write just JOIN.

-- Basic INNER JOIN
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- JOIN (same as INNER JOIN)
SELECT e.first_name, d.name
FROM employees e JOIN departments d ON e.department_id = d.id;

-- Multi-table join
SELECT
  o.id        AS order_id,
  c.name      AS customer,
  p.name      AS product,
  oi.quantity,
  oi.quantity * p.price AS line_total
FROM orders o
JOIN customers   c  ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
JOIN products    p  ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.id;

20LEFT JOIN

LEFT JOIN (LEFT OUTER JOIN) returns ALL rows from the left table plus matching rows from the right. Where there is no match, right-table columns are NULL. Use when you need every row from the main table even if there is no related record.

-- All employees, with department (NULL if unassigned)
SELECT e.first_name, d.name AS department
FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

-- Anti-join: employees with NO department
SELECT e.first_name FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

-- All customers with order count (0 for customers with no orders)
SELECT c.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS lifetime_value
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name ORDER BY lifetime_value DESC NULLS LAST;

-- Common mistake: WHERE on right table turns LEFT JOIN into INNER JOIN
-- WRONG (excludes employees with no dept):
SELECT e.first_name, d.name FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';   -- kills LEFT JOIN effect!

-- CORRECT — condition in ON clause:
SELECT e.first_name, d.name FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.name = 'Engineering';

21RIGHT JOIN

RIGHT JOIN returns ALL rows from the right table plus matching rows from the left. Less common than LEFT JOIN — it can always be rewritten as a LEFT JOIN by swapping table order. Most developers prefer LEFT JOIN for consistency.

-- All departments, with employees (NULL if department is empty)
SELECT d.name AS department, e.first_name
FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

-- Equivalent LEFT JOIN (just swap tables)
SELECT d.name AS department, e.first_name
FROM departments d LEFT JOIN employees e ON e.department_id = d.id;

-- Find departments with NO employees (anti-join)
SELECT d.name AS empty_department
FROM employees e RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

22FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. Where there is no match, the missing side's columns are NULL. It is the union of LEFT JOIN and RIGHT JOIN. MySQL does not support it natively — simulate with UNION.

-- All employees and all departments, matched where possible
SELECT e.first_name, d.name AS department
FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.id;

-- Orphaned records on either side
SELECT e.first_name, d.name FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;

-- MySQL simulation with UNION
SELECT e.first_name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.first_name, d.name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

23SELF JOIN

A self-join joins a table to itself using two aliases. Common for hierarchical data (org charts, parent-child categories) and finding pairs of related rows in the same table.

-- Employee and their manager (both in employees table)
SELECT
  e.first_name AS employee, e.salary,
  m.first_name AS manager,  m.salary AS mgr_salary
FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

-- Employees who earn more than their manager
SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary
FROM employees e JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- All pairs in same department (avoid duplicates with a.id < b.id)
SELECT a.first_name AS person1, b.first_name AS person2, a.department
FROM employees a JOIN employees b ON a.department = b.department AND a.id < b.id
ORDER BY a.department;

24CROSS JOIN

CROSS JOIN produces the Cartesian product — every row from the left table combined with every row from the right. 5 rows × 4 rows = 20 rows. Useful for generating combinations; dangerous when the JOIN condition is accidentally missing.

-- All size × color combinations
SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;
-- 3 sizes × 4 colors = 12 rows

-- Generate a date series (PostgreSQL)
SELECT (CURRENT_DATE + s.n)::DATE AS calendar_date
FROM generate_series(0, 364) AS s(n);

-- Accidental Cartesian product (missing JOIN condition — BUG)
SELECT * FROM employees, departments;  -- 1000 × 50 = 50,000 rows!

25UNION & UNION ALL

UNION combines results of two SELECT statements with the same number and compatible columns. UNION removes duplicate rows (slower); UNION ALL keeps all rows (faster). Column names come from the first SELECT.

-- UNION — removes duplicates
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM contractors;

-- UNION ALL — keeps duplicates (faster)
SELECT first_name, last_name, 'Employee'   AS type FROM employees
UNION ALL
SELECT first_name, last_name, 'Contractor' AS type FROM contractors;

-- ORDER BY applies to entire UNION result (at the very end)
SELECT first_name, salary FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT first_name, salary FROM employees WHERE department = 'Design'
ORDER BY salary DESC;

-- INTERSECT — rows in BOTH queries (PostgreSQL, SQL Server; not MySQL)
SELECT product_id FROM orders_2023 INTERSECT SELECT product_id FROM orders_2024;

-- EXCEPT / MINUS — rows in first but NOT second
SELECT product_id FROM orders_2023 EXCEPT SELECT product_id FROM orders_2024;

26GROUP BY

GROUP BY divides rows into groups and applies aggregate functions to each group. Every non-aggregate column in SELECT must appear in GROUP BY — otherwise the query is ambiguous or will error (PostgreSQL/SQL Server enforce this; MySQL may not without ONLY_FULL_GROUP_BY mode).

-- Count per department
SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department ORDER BY headcount DESC;

-- Multiple aggregates
SELECT
  department,
  COUNT(*)             AS headcount,
  SUM(salary)          AS total_payroll,
  ROUND(AVG(salary),0) AS avg_salary,
  MAX(salary)          AS top_salary
FROM employees WHERE is_active = TRUE
GROUP BY department ORDER BY total_payroll DESC;

-- GROUP BY multiple columns
SELECT department, EXTRACT(YEAR FROM hire_date) AS cohort, COUNT(*) AS hires
FROM employees GROUP BY department, EXTRACT(YEAR FROM hire_date) ORDER BY department, cohort;

-- ROLLUP — adds subtotals and grand total
SELECT department, SUM(salary) AS payroll FROM employees GROUP BY ROLLUP(department);
-- Produces one row per dept + a final NULL row = grand total

27HAVING

HAVING filters groups after GROUP BY, the same way WHERE filters rows before. HAVING can reference aggregate functions; WHERE cannot. Both can appear together — WHERE eliminates rows before grouping, HAVING eliminates groups after.

-- Departments with more than 5 employees
SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department HAVING COUNT(*) > 5;

-- Average salary exceeds 100,000
SELECT department, ROUND(AVG(salary), 0) AS avg_sal
FROM employees GROUP BY department HAVING AVG(salary) > 100000 ORDER BY avg_sal DESC;

-- WHERE + HAVING (filter rows first, then filter groups)
SELECT department, COUNT(*) AS active_headcount FROM employees
WHERE is_active = TRUE
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY active_headcount DESC;

-- Customers with 3+ orders totaling over $1,000
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent FROM orders
GROUP BY customer_id HAVING COUNT(*) > 3 AND SUM(total) > 1000;

28EXISTS

EXISTS returns TRUE if the subquery returns at least one row, stopping at the first match — very efficient for "does any related row exist?" NOT EXISTS is the NULL-safe anti-join alternative (safer than NOT IN when NULLs may be present).

-- Employees who manage at least one other employee
SELECT e.first_name FROM employees e
WHERE EXISTS (SELECT 1 FROM employees sub WHERE sub.manager_id = e.id);

-- Customers who placed at least one order
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- NOT EXISTS — customers who NEVER ordered (NULL-safe)
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- EXISTS with correlated subquery
SELECT p.name, p.price FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id AND oi.quantity > 100
);

29ANY and ALL

ANY (or SOME) returns true if the comparison holds for at least one value in the subquery. ALL returns true only if it holds for every value. = ANY is equivalent to IN; <> ALL is a NULL-safe equivalent to NOT IN.

-- ANY — earn more than at least one person in Design
SELECT first_name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Design');
-- Equivalent to: salary > MIN(Design salaries)

-- ALL — earn more than everyone in Design
SELECT first_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Design');
-- Equivalent to: salary > MAX(Design salaries)

-- = ANY is equivalent to IN
SELECT * FROM employees
WHERE department_id = ANY (SELECT id FROM departments WHERE budget > 500000);

-- <> ALL is equivalent to NOT IN but NULL-safe
SELECT * FROM employees
WHERE department_id <> ALL (SELECT id FROM departments WHERE budget < 10000);

30CASE WHEN

CASE WHEN is SQL's conditional expression (if/else). It can appear in SELECT, WHERE, ORDER BY, and GROUP BY. Two forms: searched (most flexible) and simple (compares one value to many).

-- Searched CASE
SELECT first_name, salary,
  CASE
    WHEN salary >= 150000 THEN 'Principal'
    WHEN salary >= 120000 THEN 'Senior'
    WHEN salary >= 80000  THEN 'Mid-level'
    ELSE 'Junior'
  END AS level
FROM employees;

-- Simple CASE
SELECT first_name,
  CASE department
    WHEN 'Engineering' THEN 'Tech'
    WHEN 'Design'      THEN 'Tech'
    WHEN 'Product'     THEN 'Business'
    ELSE 'Other'
  END AS division
FROM employees;

-- CASE in aggregate — conditional count (pivot)
SELECT department,
  SUM(CASE WHEN salary >= 100000 THEN 1 ELSE 0 END) AS senior_count,
  SUM(CASE WHEN salary <  100000 THEN 1 ELSE 0 END) AS junior_count
FROM employees GROUP BY department;

-- CASE in ORDER BY — custom sort order
SELECT * FROM orders
ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'pending' THEN 2 ELSE 3 END;

31NULL Functions

COALESCE is the ANSI standard NULL-handling function — returns the first non-NULL argument. NULLIF returns NULL if two values are equal (used to avoid divide-by-zero). Database-specific equivalents: ISNULL() (SQL Server), NVL() (Oracle), IFNULL() (MySQL).

-- COALESCE — first non-NULL
SELECT first_name, COALESCE(phone, email, 'no contact info') AS contact FROM employees;
SELECT first_name, COALESCE(salary, 0) AS salary FROM employees;

-- NULLIF — returns NULL when two values are equal
SELECT total_revenue / NULLIF(num_transactions, 0) AS avg_transaction FROM daily_metrics;
-- If num_transactions = 0, NULLIF returns NULL → division returns NULL (no error)

-- Clean up empty strings
SELECT NULLIF(TRIM(phone), '') AS phone FROM employees;

-- Database-specific
SELECT ISNULL(salary, 0) FROM employees;         -- SQL Server
SELECT IFNULL(salary, 0) FROM employees;         -- MySQL
SELECT NVL(salary, 0)    FROM employees;         -- Oracle
SELECT NVL2(phone, 'has phone', 'no phone') FROM employees;  -- Oracle 3-arg

32Stored Procedures

Stored procedures are precompiled SQL programs stored in the database. They accept parameters, contain business logic, and can be called repeatedly without re-parsing. Benefits: reduced network round-trips, encapsulated logic, plan caching.

-- PostgreSQL function
CREATE OR REPLACE FUNCTION apply_raise(dept TEXT, pct NUMERIC)
RETURNS void AS $$
BEGIN
  UPDATE employees
  SET salary = ROUND(salary * (1 + pct / 100), 2)
  WHERE department = dept AND is_active = TRUE;
END;
$$ LANGUAGE plpgsql;

SELECT apply_raise('Engineering', 10);  -- 10% raise

-- PostgreSQL function returning a value
CREATE OR REPLACE FUNCTION get_dept_avg(dept TEXT) RETURNS NUMERIC AS $$
  SELECT AVG(salary) FROM employees WHERE department = dept;
$$ LANGUAGE sql;

SELECT get_dept_avg('Engineering');

-- MySQL stored procedure
DELIMITER //
CREATE PROCEDURE ApplyRaise(IN dept VARCHAR(100), IN pct DECIMAL(5,2))
BEGIN
  UPDATE employees SET salary = salary * (1 + pct / 100) WHERE department = dept;
  SELECT ROW_COUNT() AS rows_updated;
END //
DELIMITER ;
CALL ApplyRaise('Engineering', 10);

-- SQL Server
CREATE PROCEDURE dbo.usp_ApplyRaise @dept NVARCHAR(100), @pct DECIMAL(5,2) AS
BEGIN
  UPDATE employees SET salary = salary * (1 + @pct / 100) WHERE department = @dept;
END;
EXEC dbo.usp_ApplyRaise @dept = 'Engineering', @pct = 10;

33SQL Comments & Operators

Single-line comments use --. Multi-line comments use /* */. SQL supports arithmetic, comparison, logical, and bitwise operators.

-- Single-line comment
/* Multi-line
   comment */

-- Arithmetic
SELECT 10 + 3;    -- 13    SELECT 10 - 3;   -- 7
SELECT 10 * 3;    -- 30    SELECT 10 / 3.0; -- 3.333...
SELECT 10 % 3;    -- 1 (modulo)
SELECT 2 ^ 8;     -- 256 (power, PostgreSQL)
SELECT POWER(2, 8); -- 256 (ANSI)

-- Comparison
SELECT 5 = 5;    -- TRUE    SELECT 5 != 5; -- FALSE
SELECT 5 > 3;    -- TRUE    SELECT 5 < 3;  -- FALSE
SELECT 5 >= 5;   -- TRUE    SELECT 5 <= 4; -- FALSE

-- Bitwise (used for permission flags)
SELECT 6 & 3;    -- 2 (AND)
SELECT 6 | 3;    -- 7 (OR)
SELECT 6 ^ 3;    -- 5 (XOR, MySQL)

-- Permission check example
SELECT * FROM users WHERE (permissions & 1) = 1;   -- has READ bit
SELECT * FROM users WHERE (permissions & 3) = 3;   -- has READ and WRITE bits

34CREATE DATABASE & TABLE

Tables define the schema: column names, data types, and constraints. Good schema design prevents data corruption and enables efficient queries.

-- Create a database
CREATE DATABASE company_db;
CREATE DATABASE company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  -- MySQL

-- Create a table
CREATE TABLE employees (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(100) NOT NULL,
    last_name   VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    phone       VARCHAR(20),
    department  VARCHAR(50),
    salary      DECIMAL(12, 2) CHECK (salary >= 0),
    manager_id  INT REFERENCES employees(id) ON DELETE SET NULL,
    hire_date   DATE DEFAULT CURRENT_DATE,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT NOW()
);

-- Create table from another (copy structure + data)
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- Empty copy (PostgreSQL)
CREATE TABLE employees_backup AS SELECT * FROM employees WHERE FALSE;

-- Safe recreation
DROP TABLE IF EXISTS old_temp;
CREATE TABLE old_temp AS SELECT * FROM temp_staging;

35Data Types

Choosing the right data type improves storage efficiency, enforces integrity, and affects query performance. Prefer the most specific type that fits your data.

36Constraints

Constraints enforce rules on data at the database level — independent of application code. This ensures integrity even if multiple applications write to the same database. Constraints are checked on every INSERT, UPDATE, and DELETE.

CREATE TABLE orders (
    -- PRIMARY KEY — unique, NOT NULL, one per table
    id          SERIAL PRIMARY KEY,

    -- NOT NULL — column must always have a value
    customer_id INT NOT NULL,

    -- UNIQUE — all values must differ (NULLs usually allowed)
    order_number VARCHAR(20) UNIQUE NOT NULL,

    -- FOREIGN KEY — value must exist in referenced table
    customer_id INT REFERENCES customers(id),

    -- FK with cascade behavior
    product_id  INT REFERENCES products(id) ON DELETE CASCADE,
    -- ON DELETE CASCADE: delete order when product is deleted
    -- ON DELETE SET NULL: set FK to NULL instead
    -- ON DELETE RESTRICT (default): prevent deletion if referenced

    -- CHECK — enforce a condition
    total       DECIMAL(12,2) CHECK (total >= 0),
    status      VARCHAR(20)   CHECK (status IN ('pending','paid','cancelled','refunded')),

    -- DEFAULT — value used when not specified on INSERT
    created_at  TIMESTAMP DEFAULT NOW(),
    is_paid     BOOLEAN   DEFAULT FALSE
);

-- Composite PRIMARY KEY
CREATE TABLE order_items (
    order_id   INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity   INT NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

-- Add constraints after creation
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary BETWEEN 0 AND 10000000);
ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(id);
ALTER TABLE employees DROP CONSTRAINT chk_salary;

37ALTER TABLE

ALTER TABLE modifies existing table structure. On large production tables, some operations lock the table — use CONCURRENTLY options where available (PostgreSQL) or plan for maintenance windows.

-- Add column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees ADD COLUMN last_login TIMESTAMP DEFAULT NOW();

-- Drop column
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees DROP COLUMN IF EXISTS phone;

-- Rename column (PostgreSQL / MySQL 8+)
ALTER TABLE employees RENAME COLUMN phone TO phone_number;

-- Change data type (PostgreSQL)
ALTER TABLE employees ALTER COLUMN salary TYPE BIGINT;

-- Set / drop DEFAULT
ALTER TABLE employees ALTER COLUMN is_active SET DEFAULT TRUE;
ALTER TABLE employees ALTER COLUMN is_active DROP DEFAULT;

-- Add / drop NOT NULL
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;
ALTER TABLE employees ALTER COLUMN phone DROP NOT NULL;

-- Rename table
ALTER TABLE employees RENAME TO staff;

-- MySQL syntax
ALTER TABLE employees MODIFY COLUMN salary BIGINT NOT NULL;
ALTER TABLE employees CHANGE COLUMN phone phone_number VARCHAR(20);

38Indexes

An index is a data structure that speeds up reads at the cost of storage and slower writes. The default type is B-Tree — enables O(log n) lookups, range scans, and sorted access. Without an index, queries require a full sequential scan O(n).

The query planner uses EXPLAIN ANALYZE to show whether indexes are used. Always check before assuming an index helps.

-- Simple B-Tree index
CREATE INDEX idx_employees_dept ON employees(department);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

-- Composite index — covers (department) and (department + salary) queries
-- Does NOT cover (salary) alone — leftmost prefix rule
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Covering index — answers query from index alone, no heap access
CREATE INDEX idx_dept_cover ON employees(department) INCLUDE (first_name, salary);

-- Partial index — smaller, only indexes qualifying rows
CREATE INDEX idx_active_emp ON employees(department) WHERE is_active = TRUE;

-- Expression index
CREATE INDEX idx_email_lower ON employees(LOWER(email));
-- Enables: WHERE LOWER(email) = 'ada@example.com' to use index

-- Drop index
DROP INDEX IF EXISTS idx_employees_dept;

-- Check query plan (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';
-- Index Scan or Bitmap Index Scan = good; Seq Scan = index not used

39AUTO INCREMENT / SERIAL

Auto-increment columns automatically assign a unique integer to each new row — the standard approach for integer primary keys.

-- PostgreSQL — SERIAL (shorthand)
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));
-- BIGSERIAL for tables > 2 billion rows

-- PostgreSQL — IDENTITY (ANSI SQL, PostgreSQL 10+)
CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);
-- GENERATED ALWAYS — cannot manually insert value
-- GENERATED BY DEFAULT — can insert manually (useful for migrations)

-- MySQL
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
-- Get last inserted ID:
SELECT LAST_INSERT_ID();

-- SQL Server
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));
SELECT SCOPE_IDENTITY();  -- get inserted ID

-- Reset sequence (PostgreSQL)
ALTER SEQUENCE users_id_seq RESTART WITH 1;
-- MySQL
ALTER TABLE users AUTO_INCREMENT = 1;

40Views

A view is a saved SELECT query that behaves like a virtual table. Views simplify complex queries, provide a security layer (expose only certain columns), and create stable interfaces over tables that may change. Materialized views store results physically and must be refreshed.

-- Create a view
CREATE VIEW active_engineers AS
SELECT id, first_name, last_name, salary, hire_date
FROM employees WHERE department = 'Engineering' AND is_active = TRUE;

-- Query the view like a table
SELECT * FROM active_engineers WHERE salary > 100000;
SELECT COUNT(*) FROM active_engineers;

-- Update view definition
CREATE OR REPLACE VIEW active_engineers AS
SELECT id, first_name, last_name, email, salary
FROM employees WHERE department = 'Engineering' AND is_active = TRUE;

-- Security view — expose only non-sensitive columns
CREATE VIEW public_employee_directory AS
SELECT first_name, last_name, department, email FROM employees WHERE is_active = TRUE;
GRANT SELECT ON public_employee_directory TO readonly_role;

-- Drop view
DROP VIEW IF EXISTS active_engineers;

-- Materialized view (PostgreSQL) — stored result
CREATE MATERIALIZED VIEW dept_summary AS
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees WHERE is_active = TRUE GROUP BY department;

REFRESH MATERIALIZED VIEW dept_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_summary;  -- no lock during refresh

41SQL Injection & Prevention

SQL injection is one of the most critical web security vulnerabilities. It occurs when user-supplied input is embedded directly into a SQL string, allowing attackers to modify the query's logic — bypassing authentication, reading sensitive data, or destroying entire databases.

-- VULNERABLE — never do this
username = request.get('username')
query = "SELECT * FROM users WHERE username = '" + username + "'"
-- Input: "admin' OR '1'='1"
-- Result: SELECT * FROM users WHERE username = 'admin' OR '1'='1'
-- Returns ALL users — authentication bypassed!

-- More dangerous input: "'; DROP TABLE users; --"
-- Drops the entire users table!

-- SAFE — parameterized queries (prepared statements)

-- Python (psycopg2)
cursor.execute("SELECT * FROM users WHERE username = %s", (username,))

-- Python (sqlite3)
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

-- Node.js (pg)
client.query("SELECT * FROM users WHERE username = $1", [username])

-- Java (JDBC)
PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = ?");
stmt.setString(1, username);
ResultSet rs = stmt.executeQuery();

-- PHP (PDO)
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$username]);

-- C++ (MySQL Connector/C++)
std::unique_ptr<sql::PreparedStatement> stmt(
    conn->prepareStatement("SELECT * FROM users WHERE username = ?"));
stmt->setString(1, username);
std::unique_ptr<sql::ResultSet> rs(stmt->executeQuery());

-- C++ (SQLite3)
sqlite3_stmt* stmt = nullptr;
sqlite3_prepare_v2(db, "SELECT * FROM users WHERE username = ?", -1, &stmt, nullptr);
sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_TRANSIENT);

Defense in depth:

• Parameterized queries always — the primary fix. User input is passed as a parameter, never interpolated into SQL.
• Least privilege — the app's DB user should only have SELECT/INSERT/UPDATE on needed tables, never DROP or CREATE.
• Input validation — if you expect an integer, parse it as an integer before using it.
• ORM — ORMs use parameterized queries by default, reducing risk significantly.
• Never expose DB errors to users — log server-side, return generic messages to clients.

42Window Functions

Window functions perform calculations across a "window" of rows related to the current row, without collapsing them (unlike GROUP BY). They use the OVER() clause with optional PARTITION BY and ORDER BY. Evaluated after WHERE, GROUP BY, and HAVING — before ORDER BY and LIMIT.

Syntax: function() OVER (PARTITION BY col ORDER BY col ROWS BETWEEN ...)

-- Ranking functions
-- ROW_NUMBER — unique sequential number (no ties)
-- RANK — ties get same rank, next rank skips (1,2,2,4)
-- DENSE_RANK — ties get same rank, next does NOT skip (1,2,2,3)
SELECT first_name, department, salary,
  ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- Top earner per department
SELECT * FROM (
  SELECT first_name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
  FROM employees
) t WHERE dr = 1;

-- LAG / LEAD — access previous / next row's value
SELECT hire_date, salary,
  LAG(salary)  OVER (ORDER BY hire_date) AS prev_salary,
  LEAD(salary) OVER (ORDER BY hire_date) AS next_salary,
  salary - LAG(salary) OVER (ORDER BY hire_date) AS change_from_prev
FROM employees ORDER BY hire_date;

-- Running total and moving average
SELECT hire_date, salary,
  SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM employees;

-- Percentage of total per group
SELECT department, first_name, salary,
  ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

-- NTILE — divide into N equal buckets (quartiles)
SELECT first_name, salary, NTILE(4) OVER (ORDER BY salary) AS salary_quartile FROM employees;

-- FIRST_VALUE / LAST_VALUE
SELECT department, first_name, salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_max_salary
FROM employees;

43Top 25 SQL Interview Questions

The most frequently asked SQL questions at top tech companies with full solutions.

Q1. Find the Nth highest salary

SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) t WHERE rnk = 2;  -- replace 2 with N

-- Simple (no tie handling)
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

Q2. Find duplicate emails / rows

-- Count duplicates
SELECT email, COUNT(*) AS cnt FROM employees GROUP BY email HAVING COUNT(*) > 1;

-- See all duplicate rows
SELECT * FROM employees
WHERE email IN (SELECT email FROM employees GROUP BY email HAVING COUNT(*) > 1)
ORDER BY email;

Q3. Delete duplicates, keep one row

-- Keep the row with the lowest id
DELETE FROM employees WHERE id NOT IN (SELECT MIN(id) FROM employees GROUP BY email);

-- PostgreSQL using ctid
DELETE FROM employees a USING employees b
WHERE a.email = b.email AND a.id > b.id;

Q4. Employees earning more than their manager

SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary AS mgr_salary
FROM employees e JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Q5. Department with the most employees (handle ties)

SELECT department, headcount FROM (
  SELECT department, COUNT(*) AS headcount, RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM employees GROUP BY department
) t WHERE rnk = 1;

Q6. Running total (cumulative sum)

SELECT hire_date, salary,
  SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees ORDER BY hire_date;

Q7. Month-over-month revenue growth

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS growth_pct
FROM monthly ORDER BY month;

Q8. Users who registered but never purchased

SELECT u.id, u.email FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Q9. Pivot rows to columns

SELECT department,
  SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 2022 THEN 1 ELSE 0 END) AS y2022,
  SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 2023 THEN 1 ELSE 0 END) AS y2023,
  SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 2024 THEN 1 ELSE 0 END) AS y2024
FROM employees GROUP BY department;

Q10. Second most recent order per customer

SELECT customer_id, order_id, order_date FROM (
  SELECT customer_id, order_id, order_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) t WHERE rn = 2;

Q11. Consecutive login streak detection

WITH logins AS (
  SELECT DISTINCT user_id, DATE(login_at) AS login_date FROM login_events
),
grouped AS (
  SELECT user_id, login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days')::INTERVAL AS grp
  FROM logins
)
SELECT user_id, MIN(login_date) AS start, MAX(login_date) AS end, COUNT(*) AS streak
FROM grouped GROUP BY user_id, grp HAVING COUNT(*) >= 3;

Q12. Median without MEDIAN()

-- PostgreSQL
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees;

-- Without PERCENTILE_CONT
SELECT AVG(salary) AS median FROM (
  SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS n
  FROM employees
) t WHERE rn IN (FLOOR((n+1)/2.0), CEIL((n+1)/2.0));

Q13. Products never ordered

SELECT p.id, p.name FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

Q14. Customers who bought every product in a category

SELECT customer_id FROM order_items oi
JOIN products p ON oi.product_id = p.id WHERE p.category = 'Electronics'
GROUP BY customer_id
HAVING COUNT(DISTINCT oi.product_id) = (SELECT COUNT(*) FROM products WHERE category = 'Electronics');

Q15. Find gaps in a sequence

SELECT id + 1 AS gap_start FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees WHERE id = e.id + 1)
  AND id < (SELECT MAX(id) FROM employees);

Q16. 7-day rolling average

SELECT order_date, daily_revenue,
  AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM (SELECT DATE(created_at) AS order_date, SUM(total) AS daily_revenue FROM orders GROUP BY 1) d;

Q17. Employees with salary above their department average

SELECT first_name, department, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

Q18. Top 3 earners per department

SELECT department, first_name, salary FROM (
  SELECT department, first_name, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
  FROM employees
) t WHERE dr <= 3;

Q19. Count employees hired per year

SELECT EXTRACT(YEAR FROM hire_date) AS year, COUNT(*) AS hires
FROM employees GROUP BY 1 ORDER BY 1;

Q20. Employees who share the same salary

SELECT a.first_name, b.first_name, a.salary
FROM employees a JOIN employees b ON a.salary = b.salary AND a.id < b.id
ORDER BY a.salary;

Q21. Swap M/F values in a column

UPDATE employees
SET gender = CASE gender WHEN 'M' THEN 'F' WHEN 'F' THEN 'M' ELSE gender END;

Q22. Percentage of total

SELECT department, COUNT(*) AS headcount,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_company
FROM employees GROUP BY department;

Q23. Users active in both Q1 and Q2

SELECT user_id FROM events WHERE period = 'Q1'
INTERSECT
SELECT user_id FROM events WHERE period = 'Q2';

Q24. Recursive CTE — org chart traversal

WITH RECURSIVE org AS (
  SELECT id, first_name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.first_name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT first_name, depth FROM org ORDER BY depth, first_name;

Q25. Manager with the most direct reports

SELECT m.first_name AS manager, COUNT(*) AS direct_reports
FROM employees e JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.first_name ORDER BY direct_reports DESC LIMIT 1;