PostgreSQL
Fundamentals
Basics
Create Database CREATE DATABASE test; \c <database name>: connects to the database \?: help \q: ...
Data Constraints
Data Constraints Column/Table Constraints CREATE TABLE products( product_no integer, name te...
Data Types
Frequently Used Data Types Name Aliases Description boolean bool true/false integer int ...
Data Insertion / Retrieval / Update
Insertion test =# INSERT INTO courses(c_no, title, hours) VALUES ('CS301', 'Databases', 3...
Transactions
CREATE TABLE groups( g_no text PRIMARY KEY, monitor integer NOT NULL REFERENCES students(s_id...
Pattern Matching
LIKE LIKE expression returns true/false depending on the string matches the supplied pattern. ...
Subqueries
Intro Subqueries are nested SELECT command in parantheses SELECT name, (SELECT SCORE FROM ex...
Arrays
Intro CREATE TABLE sal_emp( name text, pay_by_quarter integer[], schedule text[][]); Array...
Aggregate Functions
Intro An aggregate fucnction computes a single result from multiple input rows. SELECT max(temp_l...
Window Functions
Description Window functions are somewhat similar to aggregate functions but do not cause rows to...
Recursive Queries
Intro PostgreSQL provides with statement that allows to construct auxiliary statements for use in...
Importing, Exporting and Using Files
Import Commonly Used WITH Options FORMAT format_name specifies the type of file reading or writ...
Practical SQL
Creating Table
Creating a Table create table spot_qt( year int, qt int, revenue numeric,...
Importing and Exporting Data
Import COPY COPY table_name FROM `/var/lib/postgres/data/spot_qt.csv' with (format csv, header) ...
Basic Math and Stats with SQL
Median with percentile functions CREATE TABLE percentile_test ( numbers integer ); INSER...
JOIN and Set Operators
JOIN Types Type Description JOIN =INNER JOIN, returns rows from both tables where matching...
Primary Keys & Foreign Keys
Primary Keys Natural Keys CREATE TABLE natural_key_example ( license_id text CONSTRAINT license...
Modifying Data + Transactions
Modifying Tables with ALTER TABLE -- add a column ALTER TABLE table_name ADD COLUMN column_name d...
Statistical Functions in SQL
Correlation SELECT corr(med_hh_inc, pct_bach_higher) FROM acs_2014_2018_stats; corr --...
Working with Dates and Times
Data Types for Dates and Times data type description timestamp records date and time times...