SQL Basics

SELECT

-- All columns
SELECT * FROM employees;

-- Specific columns
SELECT name, salary FROM employees;

-- With alias
SELECT name AS employee_name, salary * 12 AS annual_salary
FROM employees;

-- Computed column
SELECT name, salary, salary * 0.1 AS bonus FROM employees;

---

WHERE — Filtering Rows

-- Equality, comparison
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE salary > 80000;

-- Multiple conditions
SELECT * FROM employees WHERE department = 'Engineering' AND salary > 80000;
SELECT * FROM employees WHERE department = 'HR' OR department = 'Finance';

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

-- BETWEEN (inclusive)
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;

-- IN — match any value in list
SELECT * FROM employees WHERE department IN ('Engineering', 'Product', 'Design');

-- LIKE — pattern matching
SELECT * FROM employees WHERE name LIKE 'A%';     -- starts with A
SELECT * FROM employees WHERE name LIKE '%son';   -- ends with son
SELECT * FROM employees WHERE name LIKE '%ann%';  -- contains ann
-- ILIKE = case-insensitive LIKE (PostgreSQL)
SELECT * FROM employees WHERE name ILIKE 'ann%';

---

ORDER BY

-- Ascending (default)
SELECT name, salary FROM employees ORDER BY salary;

-- Descending
SELECT name, salary FROM employees ORDER BY salary DESC;

-- Multiple columns — sort by dept ASC, then salary DESC within dept
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

-- ORDER BY column index (avoid in production code)
SELECT name, salary FROM employees ORDER BY 2 DESC;

---

LIMIT / OFFSET

-- Top 5 earners
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;

-- Pagination: page 2, 10 rows per page
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 10;

---

DISTINCT — Remove Duplicate Rows

-- Unique departments
SELECT DISTINCT department FROM employees;

-- Distinct across multiple columns
SELECT DISTINCT department, job_title FROM employees;

-- Count unique values
SELECT COUNT(DISTINCT department) FROM employees;

---

NULL Handling

-- NULL checks — always use IS NULL / IS NOT NULL, never = NULL
SELECT * FROM employees WHERE manager_id IS NULL;      -- top-level managers
SELECT * FROM employees WHERE manager_id IS NOT NULL;  -- everyone else

-- COALESCE — return first non-null value
SELECT name, COALESCE(phone, email, 'no contact') AS contact
FROM employees;

-- NULLIF — return NULL if two values are equal (avoid divide-by-zero)
SELECT total_sales / NULLIF(total_orders, 0) AS avg_order_value
FROM sales_summary;

-- NULL in arithmetic: any operation with NULL = NULL
SELECT NULL + 5;     -- NULL
SELECT NULL = NULL;  -- NULL (not TRUE!)

---

CASE WHEN — Conditional Logic

-- Simple CASE (like switch)
SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 60000  THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

-- CASE in aggregation — count by condition
SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS senior_count,
  COUNT(CASE WHEN salary < 60000  THEN 1 END) AS junior_count
FROM employees;

-- CASE for conditional update logic
SELECT name,
  CASE department
    WHEN 'Engineering' THEN salary * 1.1
    WHEN 'Sales'       THEN salary * 1.05
    ELSE salary
  END AS new_salary
FROM employees;

---

Data Types (PostgreSQL)

-- CAST — explicit type conversion
SELECT CAST('2026-01-01' AS DATE);
SELECT '42'::INT;           -- PostgreSQL shorthand
SELECT 3.14::NUMERIC(5,2);

---

Arithmetic Operators

-- Basic arithmetic in SELECT
SELECT
  salary,
  salary + 5000          AS with_raise,
  salary * 1.10          AS ten_pct_raise,
  salary / 12            AS monthly,       -- integer ÷ integer = integer in some DBs!
  salary / 12.0          AS monthly_exact, -- force decimal division
  salary % 1000          AS remainder,     -- modulo
  (salary + bonus) * 1.2 AS total_comp
FROM employees;

-- Integer division gotcha (PostgreSQL)
SELECT 7 / 2;       -- 3   (integer division — truncates)
SELECT 7 / 2.0;     -- 3.5 (decimal division)
SELECT 7.0 / 2;     -- 3.5
SELECT 7::FLOAT / 2; -- 3.5

-- Arithmetic in WHERE
SELECT * FROM products WHERE price * quantity > 10000;

---

Math Functions

-- Rounding
SELECT ROUND(3.14159, 2);   -- 3.14
SELECT ROUND(3.5);          -- 4
SELECT CEIL(3.1);           -- 4  (ceiling — round up)
SELECT FLOOR(3.9);          -- 3  (floor — round down)
SELECT TRUNC(3.9);          -- 3  (truncate — drop decimal, toward zero)

-- Absolute value
SELECT ABS(-42);            -- 42
SELECT ABS(-3.7);           -- 3.7

-- Power & roots
SELECT POWER(2, 10);        -- 1024
SELECT SQRT(144);           -- 12
SELECT EXP(1);              -- 2.718... (e^1)

-- Modulo (remainder)
SELECT 17 % 5;              -- 2
SELECT MOD(17, 5);          -- 2

-- Min/Max of two values (not aggregate — row-level)
SELECT GREATEST(10, 20, 15);  -- 20
SELECT LEAST(10, 20, 15);     -- 10

-- Log
SELECT LOG(100);            -- 2   (base 10)
SELECT LN(2.718);           -- ~1  (natural log)
SELECT LOG(2, 8);           -- 3   (log base 2 of 8)

-- Random
SELECT RANDOM();            -- 0.0 to 1.0 (PostgreSQL)
SELECT RANDOM() * 100;      -- 0 to 100

---

SQL Division — Relational Division Pattern

"Find all X such that X has a relationship with EVERY Y."

Classic example: "Find users who ordered ALL products in a given list."

-- Method: count matches = count required
SELECT user_id
FROM orders
WHERE product_id IN (1, 2, 3)  -- required products
GROUP BY user_id
HAVING COUNT(DISTINCT product_id) = 3;  -- must have ordered all 3

-- Generic pattern (without hardcoding count)
SELECT user_id
FROM orders
WHERE product_id IN (SELECT id FROM required_products)
GROUP BY user_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM required_products);

Another classic: "Find students who passed ALL exams."

SELECT student_id
FROM exam_results
GROUP BY student_id
HAVING SUM(CASE WHEN passed = FALSE THEN 1 ELSE 0 END) = 0;
-- No failed exams = passed all

---

SQL Execution Order

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Key facts:

• WHERE filters before aggregation — can't use COUNT(*) in WHERE
• HAVING filters after aggregation — use for aggregate conditions
• SELECT aliases are not visible in WHERE or GROUP BY (but some DBs allow in HAVING/ORDER BY)

---

Related

• [[JOINs]] — combining multiple tables
• [[Aggregates]] — GROUP BY, COUNT, SUM, HAVING
• [[SQL for Interviews]] — interview patterns