SQL Interview Questions and How to Answer Them (45+ Questions)

-- WHERE filters individual rows before the GROUP BY runs
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'          -- applied to individual rows
GROUP BY department
HAVING COUNT(*) > 5;             -- applied to aggregated groups

-- This FAILS — you cannot reference an aggregate in WHERE
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5               -- syntax error
GROUP BY department;

-- This WORKS
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

1. FROM / JOIN        -- which tables, which rows qualify from joins
2. WHERE              -- filter individual rows
3. GROUP BY           -- aggregate rows into groups
4. HAVING             -- filter groups
5. SELECT             -- compute the output columns
6. DISTINCT           -- remove duplicates (if specified)
7. ORDER BY           -- sort the result
8. LIMIT / OFFSET     -- return a subset

SELECT department, AVG(salary) AS avg_sal   -- step 5
FROM employees                               -- step 1
WHERE hire_date > '2020-01-01'              -- step 2
GROUP BY department                          -- step 3
HAVING AVG(salary) > 60000                  -- step 4
ORDER BY avg_sal DESC                        -- step 7
LIMIT 5;                                     -- step 8

-- DELETE: can be targeted and rolled back
BEGIN;
DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK; -- rows come back

-- TRUNCATE: removes all rows, keeps the table structure
TRUNCATE TABLE session_logs;

-- DROP: destroys the table completely
DROP TABLE temp_calculations;

-- NULL comparisons with = always return NULL (unknown), not true/false
SELECT * FROM employees WHERE manager_id = NULL;    -- returns 0 rows (wrong)
SELECT * FROM employees WHERE manager_id IS NULL;   -- correct

-- Use COALESCE to substitute a default value
SELECT name, COALESCE(phone, 'not provided') AS phone
FROM customers;

-- Use NULLIF to turn a specific value into NULL
-- Prevents division-by-zero errors
SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM monthly_summary;

-- Difference between COUNT(*) and COUNT(column)
SELECT
  COUNT(*)          AS total_rows,       -- counts all rows
  COUNT(salary)     AS rows_with_salary  -- skips NULLs
FROM employees;

CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,            -- PRIMARY KEY: unique + not null
  email       VARCHAR(255) UNIQUE NOT NULL,  -- UNIQUE, NOT NULL
  department_id INT REFERENCES departments(id) ON DELETE SET NULL, -- FOREIGN KEY
  salary      NUMERIC CHECK (salary >= 0),   -- CHECK constraint
  status      VARCHAR(20) DEFAULT 'active'   -- DEFAULT value
);

-- employees: id, name, department_id
-- departments: id, name

-- Sample data
employees: (1,'Ana',1), (2,'Bob',2), (3,'Carlos',NULL)
departments: (1,'Engineering'), (2,'Marketing'), (3,'HR')

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- Returns: Ana/Engineering, Bob/Marketing
-- Carlos (no dept) and HR (no employees) are excluded

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- Returns: Ana/Engineering, Bob/Marketing, Carlos/NULL

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
-- Returns: Ana/Engineering, Bob/Marketing, NULL/HR

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
-- Returns: Ana/Engineering, Bob/Marketing, Carlos/NULL, NULL/HR

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;
-- Returns 3 * 3 = 9 rows (use carefully — can explode on large tables)

-- Find employees and their managers (both in the same table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- UNION removes duplicate rows (slower — requires sorting/hashing)
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL keeps all rows including duplicates (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

SELECT e.id, e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;
-- Returns employees with no matching department

SELECT e.id, e.name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

-- DANGEROUS if department_id contains any NULLs — returns 0 rows
SELECT id, name
FROM employees
WHERE department_id NOT IN (SELECT id FROM departments);

-- Safe version (explicitly exclude NULLs)
SELECT id, name
FROM employees
WHERE department_id NOT IN (
  SELECT id FROM departments WHERE id IS NOT NULL
);

SELECT
  e.name         AS employee,
  e.salary       AS employee_salary,
  m.name         AS manager,
  m.salary       AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

SELECT
  e.department_id,
  COUNT(*) AS count_earning_more_than_manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary
GROUP BY e.department_id;

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT salary AS second_highest
FROM ranked
WHERE rnk = 2
LIMIT 1;

-- Find email addresses that appear more than once
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- Get all columns for duplicate rows
SELECT *
FROM customers
WHERE email IN (
  SELECT email
  FROM customers
  GROUP BY email
  HAVING COUNT(*) > 1
)
ORDER BY email;

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

-- PostgreSQL-style using ctid (row identifier)
DELETE FROM customers a
USING customers b
WHERE a.email = b.email
  AND a.id > b.id;

SELECT
  category,
  SUM(sale_amount)                                              AS category_total,
  ROUND(
    SUM(sale_amount) * 100.0 / SUM(SUM(sale_amount)) OVER (), 2
  )                                                             AS pct_of_total
FROM sales
GROUP BY category
ORDER BY category_total DESC;

SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;
-- Employees with NULL department_id appear as one group with department_id = NULL

SELECT COALESCE(department_id::text, 'No Department') AS dept, COUNT(*)
FROM employees
GROUP BY department_id;

SELECT
  order_date,
  sale_amount,
  SUM(sale_amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

SELECT
  customer_id,
  order_date,
  sale_amount,
  SUM(sale_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS customer_running_total
FROM orders;

-- Aggregate: collapses rows
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- Returns one row per department

-- Window function: keeps all rows
SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- Returns all employees PLUS the department average on each row

SELECT
  name,
  salary,
  ROW_NUMBER()  OVER (ORDER BY salary DESC) AS row_num,    -- always unique: 1,2,3,4
  RANK()        OVER (ORDER BY salary DESC) AS rnk,        -- gaps after ties: 1,2,2,4
  DENSE_RANK()  OVER (ORDER BY salary DESC) AS dense_rnk   -- no gaps: 1,2,2,3
FROM employees;

WITH ranked_employees AS (
  SELECT
    name,
    department,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
  FROM employees
)
SELECT name, department, salary, dept_rank
FROM ranked_employees
WHERE dept_rank <= 3
ORDER BY department, dept_rank;

-- Month-over-month revenue change
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month)          AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS revenue_change,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS pct_change
FROM monthly_revenue
ORDER BY month;

-- ROWS: physical rows relative to current row
SELECT
  date,
  sales,
  SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- last 7 rows (by position)
  ) AS rolling_7_day_sum
FROM daily_sales;

-- RANGE: logical range based on the ORDER BY value
-- RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
-- includes all rows within 6 days of the current date value,
-- regardless of how many physical rows that is
SELECT
  date,
  sales,
  SUM(sales) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7_day_sum
FROM daily_sales;

-- Subquery version (nested, harder to read)
SELECT name, salary
FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE budget > 1000000
);

-- CTE version (readable, named)
WITH well_funded_depts AS (
  SELECT id FROM departments WHERE budget > 1000000
)
SELECT name, salary
FROM employees
WHERE department_id IN (SELECT id FROM well_funded_depts);

-- employees table: id, name, manager_id (NULL for the CEO)

WITH RECURSIVE org_chart AS (
  -- Base case: start with the top-level employee (no manager)
  SELECT id, name, manager_id, 0 AS level, name::text AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: join to the CTE itself
  SELECT e.id, e.name, e.manager_id, oc.level + 1,
         oc.path || ' > ' || e.name
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, level, path
FROM org_chart
ORDER BY path;

CEO (level 0, path: CEO)
  CTO (level 1, path: CEO > CTO)
    Dev Lead (level 2, path: CEO > CTO > Dev Lead)
      Ana (level 3, path: CEO > CTO > Dev Lead > Ana)

-- Inner query runs once, produces a list of dept IDs
SELECT name FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'NYC'
);

-- For each employee, the inner query runs with THAT employee's department_id
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id  -- correlates to outer query
);

-- Same result, better performance
SELECT e.name, e.salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_sal;

-- Simple version
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 1;

-- Robust version (handles ties and includes dept name)
WITH dept_avgs AS (
  SELECT
    d.name AS department,
    AVG(e.salary) AS avg_salary,
    RANK() OVER (ORDER BY AVG(e.salary) DESC) AS rnk
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY d.name
)
SELECT department, ROUND(avg_salary, 2) AS avg_salary
FROM dept_avgs
WHERE rnk = 1;

-- Example of atomicity
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- debit
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- credit
COMMIT;  -- both succeed
-- If anything fails, ROLLBACK automatically reverts both updates

-- Set isolation level in PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT balance FROM accounts WHERE id = 1;
  -- ... do some work ...
  SELECT balance FROM accounts WHERE id = 1; -- guaranteed same result
COMMIT;

-- PostgreSQL: INSERT ... ON CONFLICT
INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id) DO UPDATE
  SET theme    = EXCLUDED.theme,
      language = EXCLUDED.language,
      updated_at = NOW();

-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON DUPLICATE KEY UPDATE
  theme    = VALUES(theme),
  language = VALUES(language);

-- Standard SQL using a subquery
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (
  SELECT id FROM departments WHERE name = 'Engineering'
);

-- PostgreSQL / SQL Server: UPDATE with JOIN (more readable for multiple columns)
UPDATE employees e
SET salary = e.salary * 1.10,
    updated_at = NOW()
FROM departments d
WHERE e.department_id = d.id
  AND d.name = 'Engineering';

-- MySQL syntax (different!)
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.10
WHERE d.name = 'Engineering';

-- Create a basic index
CREATE INDEX idx_employees_email ON employees(email);

-- Create a composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Create a partial index (only indexes rows matching a condition)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Create a unique index (enforces uniqueness as a side effect)
CREATE UNIQUE INDEX idx_unique_email ON customers(email);

EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 80000;

-- Before: slow query (correlated subquery runs once per employee row)
SELECT * FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);

-- After: fast equivalent with window function
SELECT * FROM (
  SELECT *, MAX(salary) OVER (PARTITION BY department_id) AS max_dept_salary
  FROM employees
) sub
WHERE salary = max_dept_salary;

-- Bad (1NF violation — phone_numbers is multi-valued)
customers: id | name | phone_numbers
              1 | Ana  | "555-1234, 555-5678"

-- Good (1NF)
customer_phones: customer_id | phone_number
                 1           | 555-1234
                 1           | 555-5678

-- Bad (3NF violation: zip_code determines city, not the employee id)
employees: id | name | zip_code | city

-- Good (3NF)
employees: id | name | zip_code
zip_codes:  zip_code | city

CREATE TABLE customers (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  name       VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  price       NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock_qty   INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0)
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status      VARCHAR(50) NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMP DEFAULT NOW(),
  total_amount NUMERIC(12, 2) -- denormalized for fast reads
);

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id  INTEGER NOT NULL REFERENCES products(id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  NUMERIC(10, 2) NOT NULL, -- snapshot price at time of order
  UNIQUE (order_id, product_id)
);

CREATE INDEX idx_orders_customer  ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

-- Using conditional aggregation (works everywhere)
SELECT
  student_id,
  MAX(CASE WHEN subject = 'math'    THEN score END) AS math,
  MAX(CASE WHEN subject = 'science' THEN score END) AS science,
  MAX(CASE WHEN subject = 'english' THEN score END) AS english
FROM scores
GROUP BY student_id;

-- PostgreSQL: using crosstab (tablefunc extension)
SELECT * FROM crosstab(
  'SELECT student_id, subject, score FROM scores ORDER BY 1,2',
  'SELECT DISTINCT subject FROM scores ORDER BY 1'
) AS pivot(student_id INT, english NUMERIC, math NUMERIC, science NUMERIC);

-- Create a materialized view of expensive aggregation
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  product_category,
  SUM(sale_amount) AS total_sales,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
JOIN order_items USING (order_id)
JOIN products USING (product_id)
GROUP BY 1, 2;

-- Refresh the materialized view (runs the underlying query again)
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- Refresh without locking reads (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

-- Find missing order IDs in a sequence
WITH expected AS (
  SELECT generate_series(
    (SELECT MIN(id) FROM orders),
    (SELECT MAX(id) FROM orders)
  ) AS expected_id
)
SELECT expected_id AS missing_id
FROM expected
WHERE expected_id NOT IN (SELECT id FROM orders);

-- Find missing dates in a date series
WITH date_range AS (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
  )::date AS expected_date
)
SELECT expected_date AS missing_date
FROM date_range
WHERE expected_date NOT IN (SELECT DISTINCT sale_date FROM daily_sales);

SELECT
  sale_date,
  daily_revenue,
  ROUND(
    AVG(daily_revenue) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  ) AS rolling_7_day_avg
FROM daily_sales
ORDER BY sale_date;

-- COALESCE: returns the first non-NULL argument (standard SQL, works everywhere)
SELECT COALESCE(phone, mobile, 'no contact') FROM customers;

-- NULLIF: returns NULL if two expressions are equal, otherwise returns the first
-- Most common use: prevent division by zero
SELECT total / NULLIF(denominator, 0) AS ratio FROM metrics;

-- ISNULL: SQL Server/Sybase only — equivalent to COALESCE with 2 arguments
-- Not standard SQL; prefer COALESCE for portability
SELECT ISNULL(phone, 'no phone') FROM customers; -- SQL Server only

-- NVL: Oracle-specific equivalent of COALESCE with 2 arguments
SELECT NVL(phone, 'no phone') FROM customers; -- Oracle only

-- Using UNION ALL (universally portable)
SELECT product_id, 'Q1' AS quarter, q1 AS revenue FROM quarterly_sales
UNION ALL
SELECT product_id, 'Q2', q2 FROM quarterly_sales
UNION ALL
SELECT product_id, 'Q3', q3 FROM quarterly_sales
UNION ALL
SELECT product_id, 'Q4', q4 FROM quarterly_sales
ORDER BY product_id, quarter;

-- PostgreSQL: VALUES with a CROSS JOIN LATERAL (cleaner for many columns)
SELECT qs.product_id, v.quarter, v.revenue
FROM quarterly_sales qs
CROSS JOIN LATERAL (VALUES
  ('Q1', qs.q1),
  ('Q2', qs.q2),
  ('Q3', qs.q3),
  ('Q4', qs.q4)
) v(quarter, revenue);

CREATE TABLE dim_customer (
  surrogate_key  SERIAL PRIMARY KEY,
  customer_id    INTEGER NOT NULL,          -- business/natural key
  name           VARCHAR(100),
  email          VARCHAR(255),
  city           VARCHAR(100),
  valid_from     DATE NOT NULL,
  valid_to       DATE,                      -- NULL means current record
  is_current     BOOLEAN DEFAULT TRUE
);

-- When a customer changes their city:
-- Step 1: Expire the old record
UPDATE dim_customer
SET valid_to   = CURRENT_DATE - 1,
    is_current = FALSE
WHERE customer_id = 42 AND is_current = TRUE;

-- Step 2: Insert the new record
INSERT INTO dim_customer (customer_id, name, email, city, valid_from, is_current)
VALUES (42, 'Ana García', 'ana@email.com', 'Buenos Aires', CURRENT_DATE, TRUE);

-- Query: what city was customer 42 in on 2024-06-15?
SELECT city
FROM dim_customer
WHERE customer_id = 42
  AND valid_from <= '2024-06-15'
  AND (valid_to >= '2024-06-15' OR valid_to IS NULL);

-- Method: date - ROW_NUMBER() trick
-- Consecutive dates produce the same "group date" when you subtract their row number

WITH user_activity AS (
  SELECT DISTINCT user_id, activity_date
  FROM events
),
ranked AS (
  SELECT
    user_id,
    activity_date,
    activity_date - CAST(ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY activity_date
    ) AS INT) AS streak_group
  FROM user_activity
),
streaks AS (
  SELECT
    user_id,
    streak_group,
    MIN(activity_date) AS streak_start,
    MAX(activity_date) AS streak_end,
    COUNT(*) AS streak_length
  FROM ranked
  GROUP BY user_id, streak_group
)
SELECT user_id, streak_start, streak_end, streak_length
FROM streaks
ORDER BY streak_length DESC;

-- Return every 5th row (row 5, 10, 15, ...)
SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM employees
) numbered
WHERE rn % 5 = 0;

-- In PostgreSQL, you can also use the ctid trick for approximation
-- but ROW_NUMBER() is always correct and portable

-- Function: calculate total order value
CREATE OR REPLACE FUNCTION get_order_total(p_order_id INT)
RETURNS NUMERIC AS $$
  SELECT SUM(quantity * unit_price)
  FROM order_items
  WHERE order_id = p_order_id;
$$ LANGUAGE SQL STABLE;

-- Usage
SELECT id, get_order_total(id) AS total FROM orders;

-- Trigger: automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_employees_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Transaction A:  locks row 1, waits for row 2
Transaction B:  locks row 2, waits for row 1
→ Neither can proceed

-- PostgreSQL: lock rows in a consistent order to prevent deadlocks
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Process...
COMMIT;

-- Fail immediately if lock cannot be acquired (avoids long waits)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- Give up after 5 seconds
SET lock_timeout = '5s';

-- CASE WHEN approach (works everywhere)
SELECT
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders,
  SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS completed_revenue
FROM orders;

-- FILTER approach (cleaner, PostgreSQL/DuckDB)
SELECT
  COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
  SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

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

-- Standard SQL workaround using window functions
SELECT AVG(salary) AS median_salary
FROM (
  SELECT salary,
    COUNT(*) OVER ()                              AS total_count,
    ROW_NUMBER() OVER (ORDER BY salary)           AS rn_asc,
    ROW_NUMBER() OVER (ORDER BY salary DESC)      AS rn_desc
  FROM employees
) t
WHERE rn_asc IN (total_count / 2, total_count / 2 + 1, (total_count + 1) / 2)
  AND rn_desc IN (total_count / 2, total_count / 2 + 1, (total_count + 1) / 2);

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,      -- primary key: unique + not null + clustered
  email VARCHAR(255) UNIQUE,     -- unique key: unique but can be null (bad practice here)
  ssn   VARCHAR(11) UNIQUE       -- alternate unique identifier
);

-- Standard SQL (works in PostgreSQL, MySQL, SQL Server)
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name ILIKE '%email%'   -- case-insensitive search
ORDER BY table_schema, table_name;

-- PostgreSQL: find all foreign keys referencing a specific table
SELECT
  conname AS constraint_name,
  conrelid::regclass AS table_name,
  a.attname AS column_name,
  confrelid::regclass AS referenced_table
FROM pg_constraint
JOIN pg_attribute a ON a.attrelid = conrelid AND a.attnum = ANY(conkey)
WHERE contype = 'f'                  -- 'f' = foreign key
  AND confrelid = 'customers'::regclass;

SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';

-- Page 3 of 20 results per page (0-indexed pages)
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;  -- (page_number - 1) * page_size

-- PostgreSQL
SELECT order_id, STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM order_items JOIN products USING (product_id)
GROUP BY order_id;

-- MySQL
SELECT order_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ')
FROM order_items JOIN products USING (product_id)
GROUP BY order_id;

INSERT INTO archived_orders (id, customer_id, total, archived_at)
SELECT id, customer_id, total, NOW()
FROM orders
WHERE created_at < '2023-01-01' AND status = 'completed';

UPDATE employees
SET salary = CASE
  WHEN performance_rating = 'A' THEN salary * 1.15
  WHEN performance_rating = 'B' THEN salary * 1.08
  WHEN performance_rating = 'C' THEN salary * 1.03
  ELSE salary  -- no change for others
END
WHERE review_year = 2024;