Read: 08

SQL Cheat Sheet

To retrieve data from a SQL database, we need to write SELECT statements, which are often colloquially refered to as queries. A query in itself is just a statement which declares what data we are looking for, where to find it in the database, and optionally, how to transform it before it is returned. It has a specific syntax though, which is what we are going to learn in the following exercises.

As we mentioned in the introduction, you can think of a table in SQL as a type of an entity (ie. Dogs), and each row in that table as a specific instance of that type (ie. A pug, a beagle, a different colored pug, etc). This means that the columns would then represent the common properties shared by all instances of that entity (ie. Color of fur, length of tail, etc).

And given a table of data, the most basic query we could write would be one that selects for a couple columns (properties) of the table with all the rows (instances).

Select query with constraints

`SELECT column, another_column, …`

`FROM mytable`

`WHERE condition`

    `AND/OR another_condition`

    `AND/OR …;`

Select query with constraints

`SELECT column, another_column, …`

`FROM mytable`

`WHERE condition`

    `AND/OR another_condition`

    `AND/OR …;`

Select query with ordered results

`SELECT column, another_column, …`

`FROM mytable`

`WHERE condition(s)`

`ORDER BY column ASC/DESC;`

Select query with INNER JOIN on multiple tables

`SELECT column, another_table_column, …`

`FROM mytable`

`INNER JOIN another_table `

    `ON mytable.id = another_table.id`

`WHERE condition(s)`

`ORDER BY column, … ASC/DESC`

`LIMIT num_limit OFFSET num_offset;`

Insert statement with values for all columns

`INSERT INTO mytable`

`VALUES (value_or_expr, another_value_or_expr, …),`

       `(value_or_expr_2, another_value_or_expr_2, …),`

       `…;`

Insert statement with specific columns

`INSERT INTO mytable`

`(column, another_column, …)`

`VALUES (value_or_expr, another_value_or_expr, …),`

      `(value_or_expr_2, another_value_or_expr_2, …),`

      `…;`