SQL Querying

-- Basic SELECT: choose which columns, from which table
SELECT name, department, salary
FROM   employees;

-- All columns (avoid in production code — fragile if schema changes)
SELECT * FROM employees;

-- Column aliases
SELECT
    name                       AS employee_name,
    salary                     AS annual_salary,
    salary / 12.0              AS monthly_salary,
    UPPER(department)          AS dept_upper
FROM employees;

-- DISTINCT — remove duplicate rows
SELECT DISTINCT department FROM employees;

-- LIMIT and OFFSET — pagination
SELECT name, salary
FROM   employees
ORDER  BY salary DESC
LIMIT  10 OFFSET 20;   -- rows 21-30

-- Comparison operators
SELECT * FROM employees WHERE salary > 80000;
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE hire_date >= '2020-01-01';

-- Logical operators: AND, OR, NOT
SELECT * FROM employees
WHERE  department = 'Engineering'
AND    salary > 80000;

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

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

-- BETWEEN — inclusive range
SELECT * FROM employees
WHERE  salary BETWEEN 60000 AND 90000;

-- LIKE — pattern matching
-- % = zero or more characters,  _ = exactly one character
SELECT * FROM employees WHERE name LIKE 'P%';       -- starts with P
SELECT * FROM employees WHERE email LIKE '%@gmail.com';
SELECT * FROM employees WHERE name LIKE '_riya';    -- 5-letter names ending in "riya"

-- ILIKE — case-insensitive LIKE (PostgreSQL)
SELECT * FROM employees WHERE name ILIKE 'priya';

-- NULL checks — NEVER use = NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- ORDER BY — sort results
SELECT name, salary, department
FROM   employees
ORDER  BY salary DESC, name ASC;   -- salary descending, then name ascending

-- CASE — conditional expression (like if/else in SQL)
SELECT
    name,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 70000  THEN 'Mid-level'
        WHEN salary >= 50000  THEN 'Junior'
        ELSE                       'Entry'
    END AS level
FROM employees;

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

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

-- NULLIF — return NULL if two values are equal (prevents division by zero)
SELECT revenue / NULLIF(expenses, 0) AS ratio FROM financials;