Link Search Menu Expand Document

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