I’ve been taking some refresher courses on SQL for an upcoming job through Codecademy (I highly recommend it if you want to learn to code — one of the best platforms I’ve ever used — you can get a month for free). As I was learning it, I thought it would be handy to create a cheat sheet with the most-used basic SQL command-lines, for a quick learning-session or refresher, for me and for you.
Calculate how many rows are in a table.
Returns sum of all the values in the column.
MAX() returns the largest value in a column.
MIN() returns the smallest value in a column.
Calculate the average value of a column.
Rounds the value in the column to the specified number of decimal places.
ROUND() takes two arguments, a column name and an integer (the decimal places).
GROUP BY I (one column)
SELECT to arrange data into groups.
GROUP BY II (two columns)
When we need to reference more than one columns, we can use column reference numbers:
1is the first column selected
2is the second column selected
3is the third column selected
Used to filter what groups to include or exclude.
WHERE() can only filter rows, not groups. The query below only includes price points that have more than 10 orders placed.
Combine tables based on common column(s) and keep rows from both tables.
Combine tables and only keep rows that match the
Combine tables and keep all rows from the first table regardless of whether there’s a matching row in the second table.
PRIMARY KEY vs FOREIGN KEY
Primary key is a column that uniquely identifies each row of a table.
Requirements for primary keys:
- None of the values can be
- Each value must be unique
- A table can not have more than one primary key column
When the primary key for one table appears in a different table.
CROSS JOIN combines all rows of one table with all rows of another table.
UNION is used to stack one dataset on top of the other:
- The tables must have the same number of columns
- The columns must have the same data types in the same order
For more complicated cases when we want to combine two tables, and one of which is the result of a calculation. we can use
WITH to achieve it.