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 a new table in the database with column name and column data type specified.
Below, we've created a table with the name
brand_name as the first column that stores data type
year as the second column that stores data type
Insert a new row into a table with
Select and view columns from a table.
ALTER TABLE is used to add a new column into a table.
UPDATE is used with
SET to change existing records in a table.
DELETE FROM is used to delete one or more rows from a table.
Constraints are used to specify the restriction of a column, any inserted data that doesn't adhere to the restriction will be rejected.
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 KEYbut a table can have many different
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' );
Rename a column with alias using
SELECT __ AS '__' (remember to use a single quote). The column name in the database won't be changed.
Return unique values in a column.
Filter rows where the specified condition is true.
!=not equal to
>=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.
_ : you can substitute any character here. 'DIOR' or 'D12R' could be returned from
IS NULL & WHERE
Used together to filter out
IS NOT NULL or to find missing values
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 2010returns 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.
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
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
Used to limit the number of rows displayed, similar to Python's
Used to specify different cases similar to if-then clauses.
CASE WHEN CONDITION1 THEN RESULT1 ELSE RESULT2 END;
Mentioned in this post:
- Codeacademy (get 1st month for free)