SQL Cheatsheet

This is a reminder cheat sheet that may or may not be updated in the future.

Data types

Main types of data in PostgreSQL are:

Primary and Foreign keys

A primary key is a column used to identify a row uniquely in a table; a foreign key identifies a primary key in another table.

Constraints

Are rules enforced on data columns; used to prevent invalid data from being entered; ensures accuracy and reliability.

CREATE table

General syntax

CREATE TABLE table_name(
column_name TYPE column_constraint,
column_name TYPE column_constraint,
table_constraint table_constraint
)INHERITS existing_table_name;

INSERT

a.

INSERT INTO table (col1, col2, ...)
VALUES
	(value1, value2, ...),
	(value1, value2, ...),...;

INSERT INTO table(col1, col2, ...)
SELECT col1, col2,...
FROM other_table
WHERE condition;

UPDATE

UPDATE table
SET
	column1 = value1,
	column2 = value2,
WHERE
	condition

RETURNING columns_if_you_want;

DELETE

DELETE FROM table
WHERE row_id = number

ALTER

Its a clause that allow for changes to an existing table structure, such as:

ALTER TABLE table
RENAME COLUMN col TO new_name

DROP

Works for complete removal of a column, use it well. In PostgreSQL it will also remove all indexes and constraints related to the column, but wont remove remove columns used in views, triggers, or stored procedures without the CASCADE clause.

ALTER TABLE table
DROP COLUMN col_name CASCADE

CHECK

Is a constraint that allows for the creation of more customized constraints.

CREATE TABLE table(
col1 CONSTRAINT
col2 CONSTRAINT CHECK(condition)
);

Query from a table:

 SELECT  col   FROM  tb

 SELECT  col  FROM  tb  WHERE  cond

 SELECT   DISTINCT  col  FROM  tb  WHERE  cond

 SELECT  col   FROM  tb  ORDER BY  col  ASC/DESC

 SELECT  col   FROM  tb  LIMIT  num

 SELECT  col ,   AGG (coln)  FROM  tb   GROUP BY  col

SELECT  col ,   AGG (coln)  FROM  tb
GROUP BY  col  HAVING  cond

SELECT DATE(payment_date) FROM payment

SELECT col AS new_name FROM table

Using operators

 SELECT  col  FROM  tb  WHERE (cond);

 SELECT  col  FROM  tb  WHERE  col  BETWEEN  n  AND  m;

SELECT * FROM calendar
WHERE dates
BETWEEN '2001-02-01' AND '2001-02-28'

SELECT col FROM tb WHERE c1 [NOT] [I]LIKE pattern;

SELECT col FROM tb WHERE col NOT IN value_list;

SELECT col FROM tb WHERE col IS [NOT] NULL;

Aggregate functions

AVG()

COUNT()

SUM()

MAX()

MIN()

JOINs

INNER JOIN: match in both tables

SELECT * FROM table1
INNER JOIN table2
ON table1.col_match = table2.col_match;

(FULL) OUTER JOIN: all records in both tables, matching where is possible (else, null cell)

SELECT * FROM table1
FULL OUTER JOIN table2
ON table1.col = table2.col;

LEFT OUTER JOIN: all from table1 and matches on table2 (when existing, else, null)

SELECT * FROM table1
LEFT OUTER JOIN table2
ON table1.col = table2.col;

Exclusive Left Join:

SELECT * FROM table1
LEFT OUTER JOIN table2
ON table.col = table2.col
WHERE table2.col IS NULL;

RIGHT OUTER JOIN: all from table2 and matches on table1 (when existing, else, null)

SELECT * FROM table1
LEFT OUTER JOIN table2
ON table1.col = table2.col;

UNION: combine the result-set of two or more SELECT statements; basically, direct concatenation; they should match up tho

SELECT col_names FROM table1
UNION
SELECT col_names FROM table2;

Timestamps

Self explanatory:

SHOW TIMEZONE

SELECT NOW()
--as a string
SELECT TIMEOFDAY()

SELECT CURRENT_TIME

SELECT CURRENT_DATE

EXTRACT() subcomponent of a date value

EXTRACT(YEAR FROM date_column)

AGE() calculates the age given a timestamp (how old the timestamp is)

AGE(date_column)

TO_CHAR() is a general function to convert data types to text and can be used for more than timestamp formatting

TO_CHAR(date_column, 'mm-dd-yyyy')

Mathematical Functions and Operators

PostgreSQL has plenty of Mathematical operators available and that can be checked at their www.postgresql.org/docs/current/functions-math.hmtl

Some interesting functions are:

abs(x) --absolute value

degrees(dp) --radians to degrees

pi() --constant

sqrt(dp/numeric) --squareroot

random(dp) --generates a random value between 0.0 and 1.0

sin(x) --sine

dp: double precision.

SubQuery

A query on the results of another query: basically, using two SELECT statements.

SELECT col_name FROM table WHERE EXISTS
(SELECT col_name
FROM other_col
WHERE condition)

Self-Join

A query in which a table is joined to itself; useful for comparing values in a column of rows within the same table. To do this, you simply JOIN the same table in both parts – but an alias is required for the table, otherwise the query would be ambiguous.

SELECT tableA.col, tableB.col
FROM table AS tableA
JOIN table AS tableB ON
tableA.some_col = tableB.other_col

Conditional Expressions and Procedures

CASE

“General CASE”

SELECT whatever,
CASE
	WHEN condition THEN result
	WHEN condition2 THEN result2
	ELSE other_result
END AS column_name_you_want
FROM table;

“Expression CASE”: will evaluate an expression first, then compare the result with the values in the WHEN clauses, sequentially.

CASE expression
	WHEN value1 THEN result1
	WHEN value2 THEN result2
	ELSE other_result
END AS column_name_you_want

COALESCE

Accepts an unlimited number of arguments, returns the first not null. if all are null, returns null.

COALESCE(arg_1, arg_2,...)

Example; get the price for an item minus it’s discount, if there’s no discount (the discount value its null) then use COALESCE.

SELECT item, (price - COALESCE(discount, 0)) AS final_price FROM table

CAST

Used to convert from one data type to another, although not every instance of a data type can be CAST to another - it should be a reasonable operation.

General SQL CAST syntax:

SELECT CAST('5' AS INTEGER)

PostgreSQL CAST syntax:

SELECT '5'::INTEGER

It can be used on a whole column as well.

NULLIF

Takes 2 inputs, returns NULL if both are equal, else, returns the first argument passed.

NULLIF(arg1, arg2)

Views

A view is a database object, stored of a query; can be accessed as a virtual table (in PSQL); it doest not store data, just a query.

CREATE OR REPLACE VIEW view_name AS
SELECT query;

SELECT * FROM view_name;