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). 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.

COUNT

Calculate how many rows are in a table.

SELECT COUNT(*)
FROM table_name;
Count rows

SUM

Returns sum of all the values in the column.

SELECT SUM(column_name)
FROM table_name;
Sum values

MAX/MIN

MAX() returns the largest value in a column. MIN() returns the smallest value in a column. 

--MAX():
SELECT MAX(column_name)
FROM table_name;

--MIN():
SELECT MIN(column_name)
FROM table_name;
Maximum and Minimum of values

AVERAGE

Calculate the average value of a column. 

SELECT AVG(column_name)
FROM table_name;
Sum values

ROUND

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). 

SELECT ROUND(AVG(price),0)
FROM table_name;
Round values

GROUP BY I (one column)

Used with SELECT to arrange data into groups.

SELECT price, COUNT(*)
FROM table_name
GROUP BY price;
Group result by a column

GROUP BY II (two columns)

When we need to reference more than one columns, we can use column reference numbers:

  • 1 is the first column selected
  • 2 is the second column selected
  • 3 is the third column selected
SELECT price, category, AVG(orders)
FROM table_name
GROUP BY 2,1;
Group result by referencing more than 1 column

HAVING

Used to filter what groups to include or exclude. WHERE() can only filter rows, not groups. The query below only include price points that have more than 10 orders placed.

SELECT price, category, AVG(orders)
FROM table_name
GROUP BY 1
HAVING COUNT(orders)>10;
Filter groups

JOIN ON

Combine tables based on common column(s) and keep rows from both tables.

SELECT *
FROM orders
JOIN customers
ON orders.customer_id=customers.customer_id
AS combined
WHERE combined.price>0;
Filter groups

INNER JOIN

Combine tables and only keep rows that match the ON condition.

Inner join from Codecademy
SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id=customers.customer_id;
Inner join

LEFT JOIN

Combine tables and keep all rows from the first table regardless  of whether there's a matching row in the second table.

Left join from Codecademy
SELECT *
FROM orders
LEFT JOIN customers
ON orders.customer_id=customers.customer_id;
Left join

PRIMARY KEY vs FOREIGN KEY

Primary key is a column that uniquely identifies each row of a table.

Requirement for primary keys:

  • None of the values can be NULL
  • 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

CROSS JOIN combines all rows of one table with all rows of another table.

Cross Join
SELECT *
FROM orders
CORSS JOIN customers;
Cross Join

UNION

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
Union

WITH

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. 

WITH calculated_results AS (
   SELECT ...
   ...
   ...
   ...
)
SELECT *
FROM calculated_results
JOIN customers
  ON _____ = _____;
WITH in SQL

Mentioned in this post: