Learning SQL — The SQL Cheatsheet Part I

5 min readJan 30, 2021

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.

CREATE TABLE

Create a new table in the database with the column name and column data type specified.

Below, we’ve created a table with the name catalogue with brand_name as the first column that stores data type TEXT, year as the second column that stores data type YEAR, etc.

INSERT

Insert a new row into a table with INSERT INTO.

SELECT

Select and view columns from a table.

ALTER

ALTER TABLE is used to add a new column into a table.

UPDATE

UPDATE is used with SET to change existing records in a table.

DELETE

DELETE FROM is used to delete one or more rows from a table.

CONSTRAINTS

Constraints are used to specify the restriction of a column, any inserted data that doesn’t adhere to the restriction will be rejected.

Examples include:

PRIMARY KEY : a table can only have one primary key - used to uniquely identify a row, rows with duplicated values will be rejected.
UNIQUE: similar to PRIMARY KEY but a table can have many different UNIQUE columns.
NOT NULL: the column must have a value. Any row without value will not be inserted.
DEFAULT: specify a default value if a new row does not have a value for that column.

AS

Rename a column with alias using SELECT __ AS '__' (remember to use a single quote). The column name in the database won't be changed.

DISTINCT

Return unique values in a column.

WHERE

Filter rows where the specified condition is true.

= equal to
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to

LIKE & WHERE

Used together to filter data to match a specified pattern in a column, by default it’s not case sensitive without further settings.

%: match zero or more missing letters in the pattern.
D%: returns any brand name that begins with the letter 'D'.
%D: returns any brand name end with the letter 'D'.
%IO%: returns any brand name with the letters 'IO' in between.

you can substitute any character here. ‘DIOR’ or ‘D12R’ could be returned from LIKE 'D__R'.

IS NULL & WHERE

Used together to filter out IS NOT NULL or to find missing values IS NULL.

BETWEEN & WHERE

Used together to filter the result to be in a certain range.

For text, WHERE brand_name BETWEEN 'A' AND 'Z' returns result with brand_name begin with letter 'A' up to but no including 'Z', but will include a brand with the name 'Z', not 'Zocdoc'.

For numbers, WHERE year BETWEEN 2000 AND 2010 returns result with year from 2000 up to and including 1999.

AND & WHERE

Used together to combine multiple conditions, return result where all conditions are true.

OR & WHERE

Used together to combine multiple conditions, return result where any condition is true, select all the rows that include those that are really old year<1994 or really new year>2010.

ORDER BY

Used to sort the result in an ascending ASC or descending DESC order, alphabetically or numerically. You can sort the result first by one column year DEC then by another brand_name ASC.