SQL


A collection of mostly implementation-independent notes.

Selections (SELECT, FROM, AS, DISTINCT, CASE, WHEN, ELSE, END)

SELECT id,
CASE
  WHEN celsius >= 30 OR (celsius >= 20 AND humid) THEN `HOT`
  WHEN celsius <= 10 THEN `COLD` ELSE `COOL`
END AS temperature FROM MY_TABLE

The WHEN clauses are evaluated from top to bottom, and evaluation stops on the first match. Notice a WHEN or ELSE clause can also return NULL.

Filters (WHERE, BETWEEN, IN, IS, LIKE, REGEXP)

Wildcard Description
% Any number of characters, including nothing
_ A single character

Notice that the wildcard pattern must be a string literal, surrounded by single quotes.

SELECT NORMALIZED_PEOPLE.name
  FROM (SELECT name || ' ' || surname AS name FROM PEOPLE) AS NORMALIZED_PEOPLE
SELECT name, (
  SELECT count(*) FROM MESSAGE WHERE MESSAGE.to = PEOPLE.name
) AS messages FROM PEOPLE
SELECT name FROM PEOPLE WHERE name IN (SELECT VIPs.name FROM VIPs)

Orders (ORDER, BY, ASC, DESC)

Unions (UNION, ALL)

Inserts (INSERT, INTO, VALUES)

Updates (UPDATE, SET)

Deletes (DELETE, TRUNCATE)

Indexes (CREATE, INDEX, ON, UNIQUE, DROP)

Joins (JOIN, INNER, OUTER, LEFT, RIGHT, ON)

SELECT CUSTOMER.ID, ORDER.DATE
FROM CUSTOMER INNER JOIN ORDER
ON CUSTOMER.ID = ORDER.ID

Notice we had to write CUSTOMER.ID on the SELECT part as ID belongs to both tables, and therefore the query would be ambiguous.

SELECT CUSTOMER.ID, PRODUCT.PRICE, ORDER.DATE
FROM CUSTOMER INNER JOIN ORDER
ON CUSTOMER.ID = ORDER.ID
INNER JOIN PRODUCT
ON ORDER.PRODUCT_ID = PRODUCT.ID
SELECT CUSTOMER.ID, ORDER.DATE
FROM CUSTOMER LEFT JOIN ORDER
ON CUSTOMER.ID = ORDER.ID

The only difference is LEFT JOIN instead of INNER JOIN. If a customer didn’t place any order, then any column from ORDER we select is going to be null.

Tables

The skeleton command to create a table is:

CREATE TABLE <name> (
  <column1> <type> <constraints...>,
  <column2> <type> <constraints...>,
  <column3> <type> <constraints...>
)

Some of the main supported types are:

Some of the main constraints are:

Structure Description
PRIMARY KEY The column is a primary key (there can be more than one)
AUTOINCREMENT The column value will increment automatically
NOT NULL This column can never be NULL
DEFAULT(value) Set a default value for the column. If the type is BOOLEAN, use DEFAULT(1) and DEFAULT(0) for true and false, respectively
REFERENCES table (column) A foreign key to column column from table table

To drop a table MY_TABLE, use DROP TABLE MY_TABLE.

Views (CREATE, VIEW, AS)

A view is a pre-packaged SELECT operation that can be referred to as if it was a real table.

Transactions (BEGIN, TRANSACTION, COMMIT)

A utility to execute more than one SQL statement atomically. The skeleton to create a transaction is:

BEGIN TRANSACTION
<statement1>;
<statement2>;
<statement3>;
<statementN>;
COMMIT;

For example:

BEGIN TRANSACTION
UPDATE ACCOUNTS SET balance = balance - 1000 WHERE ACCOUNT_ID = 123;
UPDATE ACCOUNTS SET balance = balance + 1000 WHERE ACCOUNT_ID = 456;
COMMIT;

Notice that its required to end every statement with a semicolon.

Aggregations

Here are some of the most common aggregation functions. As a rule of thumb, aggregate functions never consider null values in their computations.

Name Arguments Description
COUNT(column) Any column, or * Count the number of non NULL occurences of a column, or the number of returned records if the column is *
SUM(column) A number column Sum all the selected values from the column
MIN(column) A number column The lowest instance of the selected values from the column
MAX(column) A number column The highest instance of the selected values from the column
AVG(column) A number column The average of the selected values from the column

For example: SELECT AVG(temperature) AS average_temperature FROM MY_TABLE
 WHERE year >= 2000. Notice we always rename aggregation columns, otherwise the name will literally be the aggregation formula, i.e. AVG(temp).

Grouping

year count
2015 36
2016 58
2017 54
year month count
2015 jan 4
2015 feb 6
2015 mar 5
2015 apr 6

The GROUP BY keyword also accepts ordinal positions. For example:

SELECT year, month, COUNT(*) AS count
FROM MESSAGE
WHERE destination = 'johndoe@example.com'
GROUP BY 1, 2

And in this case 1 and 2 match to year and month, respectively, based on the SELECT operation from above.

Filtering

We can’t filter over aggregated columns using WHERE. This is invalid:

SELECT year,SUM(precipitation) as total_precipitation
FROM station_data
GROUP BY year
WHERE total_precipitation > 30

We have to instead use HAVING, which is a variant of WHERE that works with aggregates:

SELECT year,SUM(precipitation) as total_precipitation
FROM station_data
GROUP BY year
HAVING total_precipitation > 30

Mapping

We can use CASE expression inside aggregates. Notice we need to add the END keyword after each CASE expression.

SELECT year
	SUM(CASE WHEN has_precipitation THEN precipitation ELSE 0 END) AS total_precipitation
FROM MY_TABLE GROUP BY year

Utility Functions

Name Arguments Return Value Description
round(num, decimals) A float and an integer A rounded version of the float Round the float to X number of decimals
length(string) A string An integer Get the length of a string
coalesce(value, default) Any for both Either the value or the default Return default if value is NULL, or value otherwise
string || string Two strings Concatenated string An infix function to concatenate strings

SQLite Commands