Learning SQL — The SQL Cheatsheet — Part II

COUNT

Calculate how many rows are in a table.

SUM

Returns sum of all the values in the column.

MAX/MIN

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

AVERAGE

Calculate the average value of a column.

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

GROUP BY I (one column)

Used with 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:

  • 1 is the first column selected
  • 2 is the second column selected
  • 3 is the third column selected

HAVING

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.

JOIN ON

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

INNER JOIN

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

Inner join from Codecademy

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

PRIMARY KEY vs FOREIGN KEY

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

  • None of the values can be NULL
  • Each value must be unique
  • A table can not have more than one primary key column

CROSS JOIN

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

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Chi

Chi

Books | Marketing | Data Viz | Analytics & Experimentation | Entrepreneurship 💡Founder of beautydupes.xyz | Blog: goodmarketing.club