SQL Syntax Cheat Sheet
Table of contents
Select Statement
SELECT * FROM tbl
/* select all rows and columns from table tbl */
SELECT c1, c2 FROM tbl
/* select column 1, column 2 and all rows from table tbl */
SELECT c1, c2
FROM tbl
WHERE conditions
ORDER BY c1 ASC, c2 DESC
/* select column 1, column 2 with where conditions and from table tbl order result by column 1 in ascending order and column 2 in descending order. */
SELECT DISTINCT c1, cs FROM tbl
/* select distinct rows by column 1 and column 2 from table tbl. */
SELECT c1, aggregate(expr)
FROM tbl
GROUP BY c1
/* select column 1 and use aggregate function on expression expr, group columns by column 1. */
SELECT c1, aggregate(expr) AS c2
FROM tbl
GROUP BY c1
HAVING c2 > v
/* select column 1 and column 2 as column alias of the result of aggregate function on expr, filter group of records with column 2 greater than value v. */
Update Table
INSERT INTO tbl(c1,c2, ...) VALUES(v1, v2...)
/* insert data into table tbl */
INSERT INTO tbl(c1,c2, ...) SELECT c1, c2 ... FROM tbl2 WHERE conditions
/* insert data from tbl2 into tbl */
UPDATE t SET c1 = v1, c2 = v2 ... WHERE conditions
/* update data in table tbl */
DELETE FROM tbl WHERE conditions
/* delete records from table tbl based on WHERE conditions. */
TRUNCATE TABLE tbl
/* drop table tbl and re-create it, all data is lost */
Table Statements
CREATE TABLE tbl (
c1 datatype(length)
c2 datatype(length)
...
PRIMARY KEY (c1)
)
/* create table tbl with primary key is c1 */
DROP TABLE tbl
/* remove table tbl from database */
ALERT TABLE tbl ADD COLUMN c1 datatype(length)
/* add column c1 to table tbl */
ALERT TABLE tbl DROP COLUMN c1
/* drop column c1 from table tbl */
Join Statements
SELECT * FROM tbl1 INNER JOIN tbl2 ON join-conditions
/* inner join table tbl1 with tbl2 based on join-conditions.*/
SELECT * FROM tbl1 LEFT JOIN tbl2 ON join-conditions
/* left join table tbl1 with tbl2 based on join-conditions. */
SELECT * FROM tbl1 RIGHT JOIN tbl2 ON join-conditions
/* right join table tbl1 with tbl2 based on join-conditions. */
SELECT * FROM tbl1 RIGHT JOIN tbl2 ON join-conditions
Group By
AVG() - returns average value
COUNT() - returns number of value
MAX() - returns maximum value
MIN() - returns minimum value
SUM() - returns the sum of all value
ROUND() - round up number
Joins
- Inner join syntax
SELECT *
FROM table_a
INNER JOIN table_b
ON table_a.col_name = table_b.col_name
- Full outer join syntax
SELECT * FROM table_a
FULL OUTER JOIN table_b
ON table_a.col_name = table_b.col_name
- Full outer join with WHERE, get rows unique to either table (rows not found in both tables)
SELECT * FROM table_a
FULL OUTER JOIN table_b
ON table_a.col_name = table_b.col_name
WHERE table_a.id IS null OR table_b.id IS null
- Left outer join
SELECT * FROM table_a
LEFT JOIN table_b
ON table_a.col_name = table_b.col_name
- Right outer join
SELECT * FROM table_a
RIGHT JOIN table_b
ON table_a.col_name = table_b.col_name
- Union
SELECT col_name FROM table_a
UNION
SELECT col_name FROM table_b
Timestamp
SHOW ALL
SHOW TIMEZONE
SELECT NOW() /* will give timestamp with time zone */
SELECT TIMEOFDAY() /* string */
SELECT CURRENT_TIME
SELECT CURRENT_DATE
- Timestamp & Extract
EXTRACT(YEAR FROM date_col) /* YEAR, MONTH, DAY, WEEK, QUARTER */ AGE(date_col) /* calculate and returns the current age given a timestamp */ TO_CHAR(date_col, 'mm-dd-yyyy') /* TO_CHAR => to CHARacter, this is to convert date type to text */ /* 'MM-YYYY' can be any string in this. Read more here: https://www.postgresql.org/docs/12/functions-formatting.html */
Math
SELECT ROUND(price_A/total_price, 2)*100 AS price_in_percentage
FROM tbl
String Function
SELECT LENGTH(col_1) FROM tbl
SELECT col_1 || ' ' || col_2 FROM tbl
/* string concatenation (like merge cell in spreadsheet) */
SELECT LOWER(LEFT(first_name,1)) || LOWER(last_name) || '@gmail.com' FROM tbl
/* create email, left(col, 1) => extract the letter */
Sub-Query
SELECT col_a, col_b
FROM tbl
WHERE col_a > (SELECT AVG(col_b) FROM tbl)
SELECT col_a, col_b
FROM tbl_a
WHERE col_a IN (SELECT col_a FROM tbl_b)
SELECT column_name
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
Self Join
SELECT tableA.col, tableB.col
FROM table AS tableA
JOIN table AS tableB ON
tableA.some_col = tableB.other_col
Conditional Expression
- General syntax
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
ELSE result_3
END
- CASE Expression Syntax
CASE expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
ELSE result_3
END
- Coalesce
SELECT COALESCE (1,2)
SELECT COALESCE (NULL,2,3)
- Cast
SELECT CAST('5' AS INTEGER) /* General Syntax */
SELECT '5'::INTEGER /* PostgreSQL syntax */
SELECT CAST(date AS TIMESTAMP) FROM table
- Null IF
NULLIF(arg1, arg2) /* takes 2 inputs and return NULL if both are equal,
otherwise it returns first argument parsed. */
NULLIF(10,12) /* returns: 10 */
Example use case
Name | Department |
---|---|
Amy | A |
Tom | A |
John | B |
Ratio of A to B is 2:1 , or 200%, to work out ratio here,
A / B
SELECT ( /* to work out ratio, A / B */
SUM(CASE WHEN departments = 'A' THEN 1 ELSE 0 END)/
SUM(CASE WHEN departments = 'B' THEN 1 ELSE 0 END)
) AS department_ratio
FROM tbl
/* delete B */
DELETE FROM tbl
WHERE departments = 'B'
Use NULLIF to parse a value
SELECT (
SUM(CASE WHEN departments = 'A' THEN 1 ELSE 0 END)/
NULLIF(SUM(CASE WHEN departments = 'B' THEN 1 ELSE 0 END),0)
) AS department_ratio
FROM tbl