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 column name and column data type specified. 

CREATE TABLE table_name(
	column_1 data_type,
	column_2 data_type
);
Create a table

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. 

CREATE TABLE catalogue(
	brand_name TEXT,
	year YEAR,
	product TEXT, 
	items INTEGER
);
Create a table

INSERT

Insert a new row into a table with INSERT INTO.

INSERT INTO catalogue (brand_name,year,product,items)
VALUES ('Drunk Elephant',2018,'C-Firma Day Serum',20);
Insert rows into a table

SELECT

Select and view columns from a table.

SELECT column1, column2
FROM table_name;
Select columns

ALTER

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

ALTER TABLE catalogue
ADD COLUMN price INTEGER;
Add columns into a table

UPDATE

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

UPDATE catalogue
SET price=80 WHERE product='C-Firma Day Serum';
Update existing records in a table

DELETE

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

DELETE FROM catalogue
WHERE brand_name IS NULL;
Delete 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.
CREATE TABLE catalogue(
	id INTEGER PRIMARY KEY,
	brand_name TEXT NOT NULL,
	product TEXT DEFAULT 'Not Specified'
);

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. 

SELECT brand_name AS 'brand'
FROM catalogue;
Rename a column

DISTINCT

Return unique values in a column. 

SELECT DISTINCT brand_name
FROM catalogue;
Unique values in a column

WHERE

Filter rows where the specified condition is true.

SELECT *
FROM catalogue
WHERE brand_name='Drunk Elephant';
Filter data with where
  • = 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.

  • %: matches zero or more missing letters in the pattern.
  • D%: returns any brand name that begins with the letter 'D'.
  • %D: returns any brand name that ends with the letter 'D'.
  • %IO%: returns any brand name with the letters 'IO' in between.
SELECT *
FROM catalogue
WHERE brand_name LIKE 'D%';
Filter data to match a specified pattern with %

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

SELECT *
FROM catalogue
WHERE brand_name LIKE 'D__R';
Filter data to match a specified pattern with _

IS NULL & WHERE

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

SELECT *
FROM catalogue
WHERE brand_name IS NOT 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'. 
SELECT *
FROM catalogue
WHERE brand_name BETWEEN 'A' AND 'Z';
  • For numbers, WHERE year BETWEEN 2000 AND 2010 returns result with year from 2000 up to and including 1999. 
SELECT *
FROM catalogue
WHERE year BETWEEN 2000 AND 2010;

AND & WHERE

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

SELECT *
FROM catalogue
WHERE brand_name BETWEEN 'A' AND 'Z'
AND year BETWEEN 2000 AND 2010;
Return result if 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.

SELECT *
FROM catalogue
WHERE year < 1994 
OR year>2010;
Return result if any condition is true

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.

SELECT *
FROM catalogue
WHERE brand_name BETWEEN 'A' AND 'Z'
ORDER BY year DESC, brand_name ASC;
Sort result

LIMIT

Used to limit the number of rows displayed,  similar to Python's .head(10).

SELECT *
FROM catalogue
LIMIT 10;
Limit rows displayed

CASE

Used to specify different cases similar to if-then clauses. 

CASE 
	WHEN CONDITION1 THEN RESULT1
	ELSE RESULT2
END;
Example input and output from Codeacademy

Check out Part II of the SQL cheatsheet here.

Mentioned in this post: