SQL notes

Structured Query Language
Relational databases
- MySQL
- SQLite

Structured Query Language

Deleting data

The following will remove all rows from mytable where mycondition evaluates to true.

DELETE FROM mytable
WHERE mycondition;

Distinct

SELECT DISTINCT col FROM mytable;

Filtering and sorting

SELECT col_a, col_b
FROM mytable
WHERE condition
ORDER BY col ASC/DESC
LIMIT num_lim;

Joints

SELECT col_from_a, col_from_b
FROM table_a
INNER JOIN table_b
    ON table_a.id = table_b.matching_id
WHERE condition
ORDER BY col ASC/DESC
LIMIT num_lim;

There are also LEFT, RIGHT and FULL joins depending on which set of rows you are sure you want in your results.

There is also the CROSS JOIN if you want a Cartesian product.

Conditional logic

Simple case expression

SELECT emp_id,
CASE title
WHEN 'President' THEN 'Management'
WHEN 'Vice President' THEN 'Management'
WHEN 'Treasurer' THEN 'Management'
WHEN 'Loan Manager' THEN 'Management'
WHEN 'Operations Manager' THEN 'Operations'
WHEN 'Head Teller' THEN 'Operations'
WHEN 'Teller' THEN 'Operations'
ELSE 'Unknown'
END foo
FROM employee;

Searched case expression

SELECT emp_id,
CASE
WHEN title IN ('President', 'Vice President', 'Treasurer', 'Loan Manager') THEN 'Management'
WHEN title IN ('Operations Manager', 'Head Teller', 'Teller') THEN 'Operations'
ELSE 'Unknown'
END foo
FROM employee;

Miscellaneous

`COALESCE`

You can use this as COALESCE(x1, x2, ..., xn) and it will return the first non-null value (element-wise).

Relational databases

The primary key uniquely identifies a row in a table. This is related to the foreign key which uniquely identifies a row in another table.
The combination of multiple columns yields a compound key.
An SQL query returns a table called a result set.

MySQL

Check that the server is running with sudo systemctl status mysql.
Start the MySQL server with sudo systemctl start mysql and stop it with sudo systemctl stop mysql.
Log in with sudo mysql -u <username> -p. If you have just installed MySQL, then you'll need to set up a username and password, using root.

SQLite

DB Browser for SQLite is a GUI for inspecting and interactive with a SQLite database file.
There are five storage classes: null, integer, real, text, and blob. On top of these storage classes there are datatypes, for example Boolean, which consists of the integers 0 (false) and 1 (true). Dates and times can be stored as text, reals or integers.

SQLite3 REPL

.schema <tableName> will show the schema for the given table.
.tables will list the tables in the current database.

The following will cause the query to be written to CSV rather than printed out. Setting --title on means that the header will be included in the ./foobar.csv output.

.mode csv --titles on
.once ./foobar.csv
select * from <tablename>;