Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

SQL Cheat Sheet

Query structure

// the order you write a query
SELECT col, AGG(col) AS alias FROM t WHERE cond GROUP BY col HAVING AGG(col) > 1 ORDER BY alias DESC LIMIT 10;
SELECT DISTINCT city FROM students;
SELECT * FROM t WHERE c IN (1, 2) AND name LIKE 'A%' AND d IS NOT NULL;
SELECT * FROM t WHERE c BETWEEN 10 AND 20;

Joins

SELECT * FROM a JOIN b ON b.a_id = a.id;
SELECT * FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.id IS NULL;
SELECT e.name, m.name AS manager FROM emp e LEFT JOIN emp m ON m.id = e.manager_id;

Aggregates and CASE

SELECT COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x) FROM t;
SELECT CASE WHEN marks >= 90 THEN 'A' ELSE 'B' END FROM students;
COALESCE(a, 0); NULLIF(a, 0); ROUND(x, 2); UPPER(s); LENGTH(s);

Subqueries, CTEs, windows

SELECT * FROM t WHERE x > (SELECT AVG(x) FROM t);
WITH c AS (SELECT dept, AVG(x) AS a FROM t GROUP BY dept) SELECT * FROM c;
SELECT name, RANK() OVER (PARTITION BY dept ORDER BY marks DESC) FROM t;
SELECT x, LAG(x) OVER (ORDER BY d) FROM t;

Changing data

INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');
UPDATE t SET a = a + 1 WHERE id = 5;
DELETE FROM t WHERE id = 5;
BEGIN; UPDATE t SET a = 1; COMMIT;

Tables and indexes

CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, dept_id INT REFERENCES d(id));
ALTER TABLE t ADD COLUMN email VARCHAR(100);
CREATE INDEX idx_t_name ON t (name);
CREATE VIEW v AS SELECT * FROM t WHERE active = 1;
DROP TABLE t;

Full SQL documentation