SQL Cheat Sheet

SELECT name, salary
FROM employees
WHERE dept_id = 1
  AND (salary >= 120000 OR hired_on < '2019-01-01');

SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC
LIMIT 3 OFFSET 0;

SELECT COUNT(*)          AS headcount,
       COUNT(dept_id)    AS with_dept,
       SUM(salary)       AS payroll,
       AVG(salary)       AS avg_salary,
       MIN(hired_on)     AS first_hire,
       COUNT(DISTINCT dept_id) AS depts_used
FROM employees;

SELECT dept_id, COUNT(*) AS n, AVG(salary) AS avg_sal
FROM employees
WHERE hired_on >= '2019-01-01'   -- filters rows
GROUP BY dept_id
HAVING COUNT(*) >= 2             -- filters groups
ORDER BY avg_sal DESC;

SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON d.id = e.dept_id
ORDER BY dept, e.name;

-- departments with no employees
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name
HAVING COUNT(e.id) = 0;

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

SELECT customer FROM orders WHERE amount >= 250
UNION            -- deduplicated: 3 rows
SELECT customer FROM orders WHERE order_date >= '2024-02-01';

SELECT customer FROM orders WHERE amount >= 250
UNION ALL        -- keeps duplicates: 5 rows
SELECT customer FROM orders WHERE order_date >= '2024-02-01';

-- earn above the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- in Sales or HR
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE name IN ('Sales','HR'));

-- earn above their own department's average
SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary)
                  FROM employees x
                  WHERE x.dept_id = e.dept_id);

-- departments that have at least one employee
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

WITH dept_pay AS (
  SELECT dept_id, SUM(salary) AS payroll
  FROM employees
  GROUP BY dept_id
)
SELECT d.name, p.payroll
FROM dept_pay p
JOIN departments d ON d.id = p.dept_id
ORDER BY p.payroll DESC;

SELECT name,
       CASE WHEN salary >= 120000 THEN 'senior'
            WHEN salary >= 80000  THEN 'mid'
            ELSE 'junior' END AS band
FROM employees;

-- pivot: headcount per band in one row
SELECT SUM(CASE WHEN salary >= 100000 THEN 1 ELSE 0 END) AS high,
       SUM(CASE WHEN salary <  100000 THEN 1 ELSE 0 END) AS low
FROM employees;

-- highest-paid employee per department
SELECT name, dept_id, salary FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY dept_id
                            ORDER BY salary DESC, id) AS rn
  FROM employees e
) t
WHERE rn = 1;

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

SELECT order_date, amount,
       LAG(amount)  OVER (ORDER BY order_date, id) AS prev_amt,
       amount - LAG(amount) OVER (ORDER BY order_date, id) AS change,
       LEAD(order_date, 1, 'none') OVER (ORDER BY order_date, id) AS next_date
FROM orders;

SELECT customer, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer
                         ORDER BY order_date, id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS running_total
FROM orders
ORDER BY customer, order_date, id;

-- 2nd highest distinct salary (N = 2)
SELECT (SELECT DISTINCT salary FROM employees
        ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest;

-- same via DENSE_RANK, also works per department
SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees) t
WHERE dr = 2;

-- which (customer, amount, date) combos repeat?
SELECT customer, amount, order_date, COUNT(*) AS copies
FROM orders
GROUP BY customer, amount, order_date
HAVING COUNT(*) > 1;

-- delete duplicates, keeping the lowest id
DELETE FROM orders
WHERE id NOT IN (SELECT MIN(id) FROM orders
                 GROUP BY customer, amount, order_date);

CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);

EXPLAIN QUERY PLAN
SELECT name FROM employees
WHERE dept_id = 2 AND salary > 80000;
-- SEARCH employees USING INDEX idx_emp_dept_salary (dept_id=? AND salary>?)

BEGIN;
UPDATE employees SET salary = salary - 5000 WHERE id = 2;
UPDATE employees SET salary = salary + 5000 WHERE id = 3;
-- something looks wrong? undo both updates
ROLLBACK;

BEGIN;
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 3;
COMMIT;   -- make it permanent