SELECT col1, col2, col1*2 AS doubled
FROM table
WHERE col > 5
AND name LIKE 'A%'
AND status IN ('active','pending')
AND date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY col DESC, col2 ASC
LIMIT 10 OFFSET 20;
-- Distinct
SELECT DISTINCT city FROM customers;
-- Case expression
SELECT
CASE WHEN score >= 90 THEN 'A'
WHEN score >= 70 THEN 'B'
ELSE 'C' END AS grade
FROM students;
-- INNER: only matching rows SELECT * FROM A INNER JOIN B ON A.id = B.a_id; -- LEFT: all A, NULLs for unmatched B SELECT * FROM A LEFT JOIN B ON A.id = B.a_id; -- RIGHT: all B (use LEFT in practice) -- FULL OUTER: all rows from both -- Multiple joins SELECT s.name, c.name AS company, m.name AS mgr FROM students s JOIN companies c ON s.company_id = c.id LEFT JOIN managers m ON s.manager_id = m.id; -- Self join (org hierarchy) SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
SELECT department, COUNT(*) AS headcount, COUNT(DISTINCT id) AS unique_ids, SUM(salary) AS total_pay, AVG(salary) AS avg_pay, MAX(salary) AS top_pay, MIN(salary) AS floor_pay, ROUND(AVG(salary),2) AS avg_rounded FROM employees WHERE active = 1 GROUP BY department HAVING AVG(salary) > 50000 ORDER BY total_pay DESC; -- ROLLUP (subtotals) GROUP BY ROLLUP(region, city) -- Useful aggregate STRING_AGG(name, ', ') -- concat ARRAY_AGG(name) -- array (PG)
-- Scalar subquery SELECT name, (SELECT AVG(salary) FROM emp) AS avg_sal FROM employees; -- IN subquery SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city='Mumbai' ); -- EXISTS (more efficient) SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.cust_id = c.id ); -- FROM subquery (derived table) SELECT dept, avg_sal FROM ( SELECT dept, AVG(salary) as avg_sal FROM employees GROUP BY dept ) t WHERE avg_sal > 60000;
SELECT
name, dept, salary,
-- Ranking
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC),
RANK() OVER (ORDER BY salary DESC),
DENSE_RANK() OVER (ORDER BY salary DESC),
NTILE(4) OVER (ORDER BY salary) AS quartile,
PERCENT_RANK() OVER (ORDER BY salary),
-- Aggregates over window
SUM(salary) OVER (PARTITION BY dept),
AVG(salary) OVER (PARTITION BY dept),
COUNT(*) OVER (PARTITION BY dept),
-- Lead/Lag (previous/next row)
LAG(salary, 1) OVER (ORDER BY hire_date),
LEAD(salary, 1) OVER (ORDER BY hire_date),
-- Running totals
SUM(salary) OVER (ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM employees;
-- CTE (Common Table Expression) WITH high_earners AS ( SELECT * FROM employees WHERE salary > 100000 ), dept_counts AS ( SELECT dept, COUNT(*) as n FROM high_earners GROUP BY dept ) SELECT * FROM dept_counts ORDER BY n DESC; -- Recursive CTE (org hierarchy) WITH RECURSIVE org AS ( SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, o.level+1 FROM employees e JOIN org o ON e.manager_id = o.id ) SELECT * FROM org; -- COALESCE / NULLIF COALESCE(col, 0) -- first non-null NULLIF(col, 0) -- null if equal COALESCE(a,b,'default') -- fallback chain
-- INSERT INSERT INTO table (col1, col2) VALUES (v1, v2); INSERT INTO table SELECT * FROM other_table; -- UPDATE UPDATE employees SET salary = salary * 1.1 WHERE dept = 'Engineering' AND hired_year < 2022; -- DELETE (careful!) DELETE FROM table WHERE condition; TRUNCATE TABLE table; -- faster, no rollback -- UPSERT (PostgreSQL) INSERT INTO t (id, val) VALUES (1, 'x') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;
| Category | Function | Use |
|---|---|---|
| String | UPPER/LOWER | Case change |
| String | TRIM/LTRIM/RTRIM | Remove spaces |
| String | SUBSTRING(s,1,5) | Extract chars |
| String | CONCAT(a,' ',b) | Combine strings |
| String | LENGTH/LEN(s) | String length |
| Date | NOW()/GETDATE() | Current datetime |
| Date | DATE_TRUNC('month',d) | Truncate date |
| Date | DATEDIFF(d2,d1) | Days between |
| Numeric | ROUND(x,2) | Round decimals |
| Numeric | ABS/FLOOR/CEIL | Math ops |
| Null | IS NULL / IS NOT NULL | Null check |