SQL for Data Analytics · Cheat sheet

Cheat sheet

The cheat sheet

This is the cheat sheet you'll get with the final exam. It shows the syntax of the commands you may need. You can't bring your own notes, so you need to know how to use each command and what it does.

SELECTs, operators, and clauses

-- SELECT Syntax
SELECT Column1, Column2, ... FROM TableName

-- Table JOIN Syntax
JOIN TableName2 ON TableName1.PrimaryKeyColumn = TableName2.ForeignKeyColumn

-- WHERE Syntax
WHERE condition

-- WHERE Operator List
WHERE condition AND condition
WHERE condition OR condition
WHERE ColumnName BETWEEN x AND y
WHERE NOT condition
WHERE ColumnName IN (x, y, z)
WHERE ColumnName LIKE "Pattern"
-- The LIKE operator is used in the WHERE clause to search for a specified pattern in a column. It is often used with wildcard characters such as:
-- The percentage sign (%) represents zero, one, or multiple characters.
-- The underscore sign (_) represents a single character.
-- ORDER BY
-- 1 Column, Descending
ORDER BY ColumnName DESC
-- Multiple Columns, Ascending
ORDER BY Column1, Column2,... ASC

GROUP BY and aggregation

-- GROUP BY Command
SELECT Column1, AggregateFunction(Column2), ... FROM TableName
GROUP BY Column1;

-- Aggregate Functions
COUNT(*)
MAX(ColumnName)
MIN(ColumnName)
SUM(ColumnName)
AVG(ColumnName)

CREATE and INSERT statements

-- CREATE Table
CREATE TABLE TableName(
  -- Add Column
  Column1 datatype,
  -- Can be NULL
  Column2 datatype NULL,
  -- Cannot be NULL
  Column3 datatype NOT NULL,

  -- Establish Primary Key Constraint
  Primary Key (Column1),
  -- Establish Foreign Key Constraint
  FOREIGN KEY (ForeignKeyColumn) REFERENCES OtherTable(PrimaryKeyColumn)
);

INSERT INTO TableName VALUES ("Column1Value", "Column2Value", ...);

-- Data Types
DATE -- Stores the date in the format "YYYY-MM-DD"
INT
TEXT -- Stores long form text
VARCHAR(MaxCharacters)
DECIMAL(Digits, NumberOfDigitsAfterDecimalPoint)