## My SQL Cheat Sheet

I love to work with algorithms. I’m not so excited about getting the source data that algorithms need. I see SQL as necessary but not fun. Because I don’t use SQL very often, I often have to refresh my memory. Here’s my personal mini SQL cheat sheet that I use to get me started after being away from SQL for a while.

SELECT DISTINCT c1, c2 FROM t1 WHERE cond ORDER BY c1 ASC(or DESC)

SELECT c1, AVG(c2) FROM t1 WHERE cond1 GROUP BY c1 HAVING cond2

SELECT c1, c2 FROM t1 (INNER) JOIN t2 ON cond [ rows where c1 and c2 have a match ]

also: LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN
(full outer: all rows from left and right. cross: all combinations of left and right)

SELECT c1, c2 FROM t1 UNION (ALL) SELECT c1, c2 FROM t2 (remove dups or not)

also: INTERSECT, MINUS

==

CREATE TABLE t(id INT PRIMARY KEY, name VARCHAR NOT NULL)

CREATE TABLE t(c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY c2 REFERENCES t2(c3))

CREATE TABLE t(c1 INT, c2 INT, CHECK(c1 > 0))

ALTER TABLE t ADD c1 (or many other operations)

DROP TABLE t vs TRUNCATE TABLE t (delete table vs. delete the data)

==

INSERT INTO t(c1, c2) VALUES( . . )

INSERT INTO t1(c1, c2) SELECT c3, c4 FROM t2

UPDATE t SET c1 = v1, c2 = v2 WHERE cond

DELETE FROM t WHERE cond

==

CREATE INDEX idx ON t(c1)

0