• Semicolons: End each statement with a semicolon.
• Quotes: 
• Use single quotes for string literals: 'Hello World'.
• Double quotes for identifiers with capital letters: "CreatedAt"
• Use two single quotes to escape a single quote in a string: ''
• Comments: 
• Single line comments: -- This is a comment
• Multi-line comments: /* multi-line comment here */ 

• Capitalization: SQL is case insensitive. Convention: uppercase keywords (shell commands excepted), lower case identifiers (table names, column names, etc.) SELECT title, author, publisher FROM books; 
• There is no consensus on capitalization of data types (e.g., varchar vs VARCHAR). Either can be used.
• Naming Conventions: Common practice to use snake_case:
• Table names: No consensus on pluralization: library_book, library_books
• Column names: Singular snake_case is common: title, postal_code

• Execute query: Execute/Refresh toolbar ▶ 
• Toggle comments: Cmd+Shift+/

• Create Database: Rt-click Databases > Create: Database
• Drop Database: Rt-click database_name > Delete/Drop 
• Create Table: Rt-click Tables > Create: Table 
• Drop Table: Rt-click tablename > Delete/Drop 
• Add column to table: tablename > Rt-click Columns > Create: Column 

• Ref (full list): postgresql.org/docs/current/datatype 
• Capitalization: No consensus. Docs show data types in lower case.
• When creating a table each column must include the name and datatype:

Character string data types

Numeric data types

• INTEGER | alias: INT Integers (no decimal points) range +/-2.1 billion. 
• SMALLINT (range +/-32k). 
• BIGINT (range +/- 9x10^18).
• NUMERIC | alias: DECIMAL Allows decimal points. 
• NUMERIC(percision,scale) Specify precision (total digits), and scale (number of digits after the decimal point).
• Floating point numbers (aka floats): the decimal point can "float" to the appropriate position.
• REAL single precision floating-point number. 
• DOUBLE PRECISION double precision floating-point number.

Boolean data type (true or false)

• BOOLEAN True values: true, yes, on, 1; False values: false, no, off, 0.

Date/Time data types

• DATE 'YYYY-MM-DD'
• TIME 'hh:mm:ss'
• TIMESTAMP 'YYYY-MM-DD hh:mm:ss[.fraction] [time zone]'
• Timestamp values are converted from the current time zone to UTC for storage, and back to current time zone on retrieval.
• Current time zone defaults to the server's time.

Binary data type (e.g., binary image file)

• BYTEA Variable-length binary string.

• Data type can be SMALLINT, INT, or BIGINT. Can be PRIMARY KEY.
• id INT GENERATED ALWAYS AS IDENTITY does not allow you to set id.
• id INT GENERATED BY DEFAULT AS IDENTITY allows you to set id.

• Statements are case insensitive. By convention SQL statements are shown in caps.
• Statements must end in a semicolon. Exceptions: QUIT, USE, SOURCE
• Strings can be in single or double quotes.

• Ref (full list): dev.mysql.com/doc/refman/en/data-types
• When creating a table each column must include the name and datatype:

Character string data types

• CHAR(n) Fixed length (max: 255). Values less than n length are padded with spaces.
• VARCHAR(n) Variable length up to n.
• TEXT Strings above 255 characters. Also: tinytext, mediumtext, longtext.
• BLOB Stores binary data (e.g., an image file). Also: tinyblob, mediumblog, longblog.

Number data types

• INT | INTEGER Integers (no decimal points). Also: smallint, mediumint, bigint.
• INT(5) Specify percision (number of digits). Allowed range: -99999 to 99999.
• DECIMAL | NUMERIC Allows decimal points. 
• DECIMAL(5,2) Specify percision (5 total digits), and scale (2 digits after the decimal point). Allowed range: -999.99 to 999.99.
• BOOLEAN Stores 0 for false, 1 for true.

Date/time data types

• YEAR YYYY
• DATE 'YYYY-MM-DD'
• TIME 'hh:mm:ss'
• DATETIME 'YYYY-MM-DD hh:mm:ss[.fraction]'. Fractions of a second are optional.
• TIMESTAMP 'YYYY-MM-DD hh:mm:ss[.fraction]'
• Unlike Datetime, Timestamp values are converted from the current time zone to UTC for storage, and back to current time zone on retrieval.
• Current time zone defaults to the server's time.

• Sqlite uses dynamic typing. Columns are assigned data type affinities but will accept values of any data type. Values themselves have a data class. 

• Set current timestamp as column default value:
• created_at TEXT DEFAULT CURRENT_TIMESTAMP

• column_name DATA_TYPE DEFAULT value

Optional Constraints:

• NOT NULL: Field must contain a value.
• column_name DATA_TYPE NOT NULL 
• UNIQUE: Cannot be another row with the same value for the field.
• column_name DATA_TYPE UNIQUE 
• CHECK(expression): Add a custom constraint
• column_name DATA_TYPE CHECK(expression) 
• Ex: CHECK(length(title) <= 200) CHECK(price >= 0)

• Auto-incrementing: Commonly the primary key will be an auto-incrementing "id" field.

CREATE [UNIQUE] INDEX index_name 
ON table_name(column_name);

CREATE INDEX index_name 
ON table_name(column1, column2);

• Parent table: A row can be associated with zero, one, or multiple rows in child table. Child rows can only reference a named column (not Sqlite's rowid).

CREATE TABLE parent_tablename (id INTEGER PRIMARY KEY, column_name TYPE, ...);

• Child table: Contains column with foreign key constraint referencing one id row of parent table.

CREATE TABLE child_tablename (
  column_name TYPE,
  parent_tablename_id INTEGER NOT NULL,
  FOREIGN KEY (parent_tablename_id) 
    REFERENCES parent_tablename (id)
      ON DELETE action
);

ALTER TABLE table_name RENAME TO new_name;

ALTER TABLE table_name ADD column_name TEXT;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

SELECT column1, column2 AS other_name FROM table_name;

SELECT DISTINCT column_name FROM table_name;

SELECT * FROM table_name
ORDER BY column_name [ASC | DESC];

SELECT * FROM table_name
ORDER BY column_name [ASC | DESC], column_name [ASC | DESC];

SELECT * FROM table_name
ORDER BY column_name
LIMIT x;

SELECT * FROM table_name
ORDER BY column_name
LIMIT x OFFSET y;

SELECT MIN(column_name) FROM table_name;

SELECT MAX(column_name) FROM table_name;

SELECT SUM(column_name) FROM table_name;

SELECT * FROM table_name
WHERE column_name IN (val1, val2, val3, ...);

SELECT * FROM table_name
WHERE column_name LIKE '[%]pattern[%]';

• Postgres: ILIKE
• MySQL: downcase the column first LOWER(column_name) LIKE  '[%]pattern[%]' 
  
• Sqlite: LIKE is case insensitive by default.

• Put quotes around TEXT, no quotes on numeric.
• For multiple rows, separate with commas:

INSERT INTO table_name(column1, column2, column3) 
VALUES('val1', 'val2', val3),
      ('val1', 'val2', val3),
      ('val1', 'val2', val3);

UPDATE table_name SET col1 = val, col2 = val;

UPDATE table_name SET column_name = value
WHERE condition1 AND condition2; 

DELETE FROM table_name
WHERE conditions;

• You can join two associated tables using a WHERE clause condition.
• Prefixing the table name is optional unless the same column name is in both tables.

SELECT table1.column_name, table2.column_name
FROM table1, table2
WHERE table1.associating_column = table2.identifying_column;

SELECT column1, column2, ...
FROM table1
[INNER] JOIN table2 
  ON table1.associating_column = table2.identifying_column;

• Include LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.
• Sqlite only supports LEFT OUTER JOIN.

LEFT OUTER JOIN / LEFT JOIN

• All rows from the first (left) table are returned. Rows without a valid associated row in the joined table show NULL in associate column(s).
• The OUTER keyword is optional.

SELECT column1, column2, ...
FROM left_table
LEFT [OUTER] JOIN right_table 
  ON left_table.associating_column = right_table.identifying_column;