[Link].

com

SQL Query – Cheat Sheet

After completing Master SQL for Data Science ([Link] from Imtiaz
Ahmad on Udemy, which I highly recommend, I felt the need to create a cheat sheet for myself so I’ll have the basics
to hand when memory fails – which it inevitably does in the beginning… This document does not explain how
everything works, it’s literally just quick reminders so do the course first, and then make use of this next!

A few general comments to begin:

‘Single quotation marks’ are required for most occasions!

Each statement is followed by a semi-colon; but if you only have one statement then this is irrelevant.

SQL is not a case-sensitive language but by convention commands, functions and keywords appear in upper-case
and fields, tables, views, etc. will appear in lower-case.

Putting data in
SQL Script Notes

CREATE TABLE table_name ( Create a new table called table_name with 2 fields:
field1 varchar(100), field1 and field2. The primary key will be the
field2 varchar(100), unique identifier that is always present. Examples
primary key (field1) of data types are variable characters, date,
); integer, etc.

INSERT INTO table_name VALUES (‘Cape Town’,’Western Cape’); Add new rows to the table with values as specified
INSERT INTO table_name VALUES (‘Hermanus’,’Western Cape’);
INSERT INTO table_name VALUES (‘Queenstown’,’Eastern Cape’ );

Basic data selection

SQL Script Notes

SELECT * FROM table_name; Select all fields from the specified table

SELECT firstname, lastname, email Select only the fields specified from the table specified
FROM table_name where specific criteria are met
WHERE region = “Western Cape”;

* - full wildcard

% - partial wildcard, for example: Select first names that contain ‘ind’ with any characters
SELECT first_name, last_name, email before or after, e.g. Cinderella, Linda, etc.
FROM table_name
WHERE first_name like ‘%ind%’;

SELECT * Twisted logic – it will always bring you all results!

FROM employees
WHERE 1=1;

SELECT * Selection with multiple conditions – AND

FROM table_name Returns all the well-paid Lindas
WHERE first_name = Linda
AND salary > 500000;

SELECT * Selection with multiple conditions – OR

FROM table_name Returns people named Linda or people who earn well
WHERE first_name = Linda
OR salary > 500000;

Page|1
[Link]

SQL Script Notes

SELECT * Combining AND’s and OR’s requires brackets

FROM table_name
WHERE salary < 500000
AND (first_name = 'Linda' OR first_name = 'Carmen') ;

SELECT * With multiple OR’s it is more practical to use the IN keyword

FROM table_name
WHERE first_name IN (‘Linda’, ‘Carmen’, ‘Julia’ ) ;

SELECT * With multiple numeric values we can use ranges rather

FROM table_name
WHERE salary BETWEEN 400000 AND 500000;

SELECT first_name, birth_date Notice that with multiple date values single quotation marks
FROM table_name are required
WHERE birth_date BETWEEN '1970-01-01' AND '1990-01-01’;

SELECT * Various ways of saying NOT

FROM table_name
WHERE NOT first_name = ‘Linda; Note that comments that are not executable can be preceded
-- or otherwise like this with --, a double-dash
SELECT *
FROM table_name
WHERE first_name != ‘Linda;

SELECT * Special select of null values from a field

FROM table_name
WHERE first_name IS NULL;

SELECT * Or selection of non-null values from a field

FROM table_name
WHERE NOT first_name IS NULL;

-- Sort ascending Sort fields once selected, in the specified order – ascending
SELECT * by default (or with asc), otherwise descending (with desc)
FROM table_name
WHERE NOT first_name IS NULL
ORDER BY region;
-- Sort descending
SELECT *
FROM table_name
WHERE NOT first_name IS NULL
ORDER BY region DESC;

SELECT DISTINCT first_name Get a list of unique first names from your data, sorted
FROM table_name alphabetically
ORDER BY first_name;

SELECT * Just get the first 10 records – a quick way to visualize the
FROM table_name data
LIMIT 10;

-- Alternate field names with no spaces Columns are given default names at run-time but we can re-name
SELECT DISTINCT first_name as unique_first_name columns as required using the AS keyword. Note the use of
FROM table_name double quotation marks in this case!
ORDER BY unique_first_name
-- Alternate field names with spaces
SELECT DISTINCT first_name as "First Name"
FROM table_name
ORDER BY "First Name";

Formatting and manipulating the data

SQL Script Notes

SELECT UPPER(first_name) Function to display all data in upper case

FROM table_name;

SELECT LOWER(first_name) Function to display all data in lower case

FROM table_name;

Page|2
[Link]

SQL Script Notes

-- Rounding to a set number of decimal places Function to round the resulting numbers, either to 0
SELECT ROUND(price * 1.15, 2) as price_incl_vat decimal places by default or to the set number desired
FROM table_name;
-- Rounding to the default number of decimal places (0)
SELECT ROUND(price * 1.15) as price_incl_vat
FROM table_name;

SELECT LENGTH(first_name) Function to return the length of each field entry, e.g.
FROM table_name this could find us the longest number of letters used
ORDER BY length DESC; for the first_name field

SELECT POSITION('@' IN email) Function to return which position the @ sign occurs in
FROM table_name;

SELECT TRIM(first_name) Function to get rid of that pesky white space people
FROM table_name; sometimes leave at the beginning and/or end of their
data

SELECT LENGTH(TRIM(first_name)) Functions can live inside other functions – for example
FROM table_name this statement returns the length of first name after
ORDER BY length DESC; trimming

SELECT first_name || ' ' || last_name as "Full Name" Concatenation is achieved with || - add extra between
FROM table_name; values in single quotation marks

-- Displaying the first 5 chars Function to display only n characters of the string
SELECT SUBSTRING(first_name FROM 1 FOR 5)
FROM table_name;
-- Displaying character 5 onwards
SELECT SUBSTRING(first_name FROM 5)
FROM table_name;

SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) So with a function inside a function, we can split our
FROM table_name; data to get “only domain names from email addresses”

SELECT SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) Or we can split our data to get “only usernames from
FROM table_name; email addresses”

SELECT first_name, last_name, (‘Western Cape’ IN (region)) as Adds a third column which gives true if the region is
region_wc Western Cape, otherwise false
FROM table_name;

SELECT first_name, REPLACE(first_name, 'Linda', 'Linda Lye') Adds a second column which gives the intended
FROM table_name; replacement text for each first_name column

SELECT COALESCE(first_name, 'NONE') Adds a second column which gives all the first names
FROM employees; that were there + NONE where the first_name column
contained a NULL value

Aggregate functions
SQL Script Notes

SELECT MAX(salary) Maximum

FROM table_name;

SELECT MIN(salary) Minimum

FROM table_name;

SELECT AVG(salary) Average

FROM table_name;

-- Select count of a single field Count non-null records

SELECT COUNT(salary)
FROM table_name;
-- Select count of records
SELECT COUNT(*)
FROM table_name;

SELECT SUM(salary) Sum

FROM table_name;

Page|3
[Link]

Group By
Where aggregate functions are used in conjunction with other fields, GROUP BY is used to group the data so that the
aggregate still makes sense. Obviously, the fields to group by themselves should make sense e.g. to show the sum of
salaries by first name would be nonsensical, but to show the sum of salaries by region would make sense!

SQL Script Notes

SELECT region, AVG(salary) Returns average salary by region. The number of records
FROM table_name will be equal to the number of regions
GROUP BY region;

SELECT region, COUNT(*) total_employees, ROUND(AVG(salary)) We can build in additional functions, so the query
avg_salary, MIN(salary) min_salary, MAX(salary) max_salary alongside re-names all the aggregate fields and then
FROM table_name sorts the results in descending order of average salary
WHERE salary > 500000
GROUP BY region
ORDER BY avg_salary desc;

SELECT municipality, district, region, MAX(salary) In this example we have 3 non-aggregate fields, and
FROM table_name therefore the same 3 fields in GROUP BY
GROUP BY municipality, district, region;

SELECT region, count(*) Filtering on aggregated data is not done with WHERE but
FROM table_name rather with HAVING. In this example, after obtaining the
GROUP BY region count of records per region we then only display those
HAVING count(*) > 100 where the count was more than 100, smaller regions will
ORDER BY region; be omitted.

Sub-queries and views as data sources

Queries can exist within queries. Always read from the inside out to work out what is going on. In general, it looks like
it would be easier to create views for complicated subqueries as these can then easily be referenced by name and
you don’t have to have all those pesky wheels within wheels...

SQL Script Notes

SELECT data.first_name Here we have a typical SELECT statement within another

FROM (SELECT first_name, last_name, region SELECT statement. The inner select statement has been
FROM table_name) data; given the alias data and becomes the data source of the
outer select statement

SELECT first_name, salary Sub-queries can be used in the FROM clause. In this case
FROM (SELECT * FROM table_name WHERE salary > 500000) as set; an alias must always be assigned.

SELECT DISTINCT(region) Sub-queries can also be used in the WHERE clause. In

FROM table_name this example we find all regions in table table_name
WHERE region NOT IN (SELECT region FROM table_regions); that do not exist in table_regions, a useful data
integrity check

SELECT first_name, region_id, salary - (SELECT MAX(salary) Sub-queries can also be used in the SELECT clause. In
FROM employees) shortfall this case only one row of data should be returned by the
FROM employees; sub-query e.g. MAX(salary) alongside.

SELECT known_as, curr_salary If columns are re-named in the sub-query then that is
FROM (SELECT first_name known_as, salary curr_salary FROM how they must be referenced in the outer query
table_name WHERE salary > 500000) as set;

CREATE VIEW v_people_info as Create a view for the entire query. This view can then
SELECT first_name, region_id, salary - (SELECT MAX(salary) be referenced just like a table by name. The v_* naming
FROM employees) shortfall convention is traditional!
FROM employees;

-- Look for people whose salary is greater than Correlated sub-queries which make use of aliases for
-- the regional average differentiation, are also possible, but very expensive
SELECT first_name, salary because for each record of the outer query, the inner
FROM table_name t1 query has to run which means with 1000 records in the
WHERE salary > (SELECT ROUND(AVG(salary)) outer query the inner query will have to run 1000 times
FROM table_name t2 in order to return a result!
WHERE [Link] = [Link]);

Page|4
[Link]

Boolean logic
SQL Script Notes

= != <> > < Equals, Not equal, Not equal, Greater than, Less than

-- In a table with 30 records, the following returns the 1 st 9 < ALL or > ALL can be used as a Boolean operator against
SELECT running_number a set returned by another SELECT statement.
FROM table_name
WHERE running_number < ALL (
SELECT running_number
FROM table_name
WHERE running_number > 9
);
-- In a table with 30 records, the following returns none
SELECT running_number
FROM table_name
WHERE running_number > ALL (
SELECT running_number
FROM table_name
WHERE running_number > 9
);

-- In a table with 30 records, the following returns all < ANY or > ANY can be used as a Boolean operator against
SELECT running_number a set returned by another SELECT statement.
FROM table_name
WHERE running_number < ANY (
SELECT running_number
FROM table_name
WHERE running_number > 9
);
-- In a table with 30 records, the following returns 11 onwards
SELECT running_number
FROM table_name
WHERE running_number > ANY (
SELECT running_number
FROM table_name
WHERE running_number > 9
);

Conditional expressions
SQL Script Notes

SELECT first_name, salary, The CASE clause let’s you create a new column and fill
CASE its contents conditional on various conditions being met
WHEN salary < 200000 THEN 'Under-paid' (WHEN / THEN) with a catch-all (ELSE) as optional but
WHEN salary >= 200000 THEN 'Over-paid' good practice.
ELSE ‘Not determined’
END payment_status
FROM table_name;

Page|5
[Link]

Joins
The diagram…

SQL Script Notes

SELECT first_name, region A simple join between 2 tables

FROM table_name, table_regions
WHERE table_name.region_id = table_regions.region_id;

SELECT first_name, country, category A more typical syntax – INNER join specified, you can
FROM table_name t INNER JOIN table_regions r daisy chain these up
ON t.region_id = r.region_id
INNER JOIN table_categories c
ON t.category_id = c.category_id;

SELECT DISTINCT table_names.region, table_regions.region An example of LEFT OUTER JOIN, all single values from
FROM table_names LEFT OUTER JOIN table_regions table_names and matching values from table_regions
ON table_names.region_id = table_regions.region_id;

SELECT DISTINCT table_names.region, table_regions.region An example of RIGHT OUTER JOIN, all single values from
FROM table_names RIGHT OUTER JOIN table_regions table_regions and matching values from table_names
ON table_names.region_id = table_regions.region_id;

SELECT DISTINCT table_names.region, table_regions.region An example of FULL OUTER JOIN, all single values from
FROM table_names FULL OUTER JOIN table_regions table_names and all single values from table_regions –
ON table_names.region_id = table_regions.region_id but then by specifying we only want null values we get
WHERE table_names.region IS NULL the small subset of values where there are null values
OR table_regions.region IS NULL; in either set.

Some set type clauses

SQL Script Notes

SELECT region This query PLUS that query, stacked up on top of one
FROM table_names another with unique values (duplicates removed)
UNION
SELECT region
FROM table_regions;

Page|6
[Link]

SQL Script Notes

SELECT region This query PLUS that query, stacked up on top of another
FROM table_names with all values (no duplicates removed)
UNION ALL
SELECT region
FROM table_regions;

SELECT region This query MINUS that query – all the results from the
FROM table_names first query except for those that occurred in the second
EXCEPT query. In fact in the Oracle environment the MINUS
SELECT region keyword is used in lieu of EXCEPT!
FROM table_regions;

Window functions using OVER()

SQL Script Notes

select first_name, region, In this scenario you get all records from table_name and
COUNT(*) OVER(PARTITION BY region) a 3rd column which gives you the count of records by
FROM table_name; region

select first_name, region, You can use different aggregate functions, as we have
AVG(salary) OVER(PARTITION BY region) seen so far. In this scenario you get all records from
FROM table_name table_name and a 3rd column which gives you the sum of
salaries in each region

select first_name, region, And because this is essentially adding a field each
COUNT(*) OVER(PARTITION BY region) ppl_region, time, you can add other fields at will
salary,
AVG(salary) OVER(PARTITION BY region) avg_salary
FROM employees
ORDER BY region

SELECT first_name, hire_date, salary, Using ORDER BY within OVER you can get a new column with
SUM(salary) OVER(ORDER BY hire_date) running_total a running total. In this scenario you get all records
FROM table_name from table_name and a 3rd column which gives you the sum
-- In longhand, the above actually means of salary in this row + the preceding row
SELECT first_name, hire_date, salary,
SUM(salary) OVER(ORDER BY hire_date RANGE BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) running_total
FROM table_name

SELECT first_name, hire_date, salary, This means you can specify various values for the number
SUM(salary) OVER(ORDER BY hire_date ROWS BETWEEN 1 PRECEDING of preceding rows to include in your operation, e.g. in
AND CURRENT ROW) running_total this example, for each row the value in the preceding 1
FROM table_name rows is added. You could vary how many “PRECEDING” you
include, e.g. 3 PRECEDING, etc.

SELECT first_name, hire_date, region, salary, And using a combination or PARTITION BY and ORDER BY
SUM(salary) OVER(PARTITION BY region ORDER BY hire_date) within OVER you can get a new column with a running
running total total that resets each time your PARTITION BY value
FROM table_name changes

SELECT first_name, region, salary, Here we are ranking by salary in descending order, with
RANK() OVER(PARTITION BY region ORDER BY salary DESC) a reset each time region changes
FROM table_name

SELECT * FROM ( These types of queries can also be used as inline

SELECT first_name, region, salary, queries, so in the example alongside we only wanted to
RANK() OVER(PARTITION BY region ORDER BY salary DESC) see the top-ranking earners in each region
FROM table_name) data_source
WHERE rank = 1

SELECT * FROM ( Here we are splitting our data into n groups, so 5

SELECT first_name, region, salary, buckets or 10 buckets
NTILE(5) OVER(PARTITION BY region ORDER BY salary DESC)
salary_bracket
FROM table_name) data_source

Page|7
[Link]

SQL Script Notes

SELECT first_name, region, salary, Here we are making a 4th column which will contain the
FIRST_VALUE(salary) OVER(PARTITION BY region ORDER BY salary first value in salary (which has been sorted descending
DESC) and thus will provide the top salary) and will reset
FROM table_name each time region changes. It seems like you have to be
-- Another way to do the same thing is: very sure that FIRST_VALUE will always correctly meet
SELECT first_name, region, salary, your criteria, in this case I think MAX would just be
MAX(salary) OVER(PARTITION BY region ORDER BY salary DESC) safer?!
salary_bracket
FROM employees

SELECT first_name, region, salary, Along with FIRST_VALUE we have the companion NTH_VALUE
NTH_VALUE(salary, 5) OVER(PARTITION BY department ORDER BY where you can specify which number value you want to
salary ASC) salary_bracket pull out
FROM table_name

SELECT first_name, salary, In this scenario you get all records from table_name and
LEAD(salary) OVER() next_row_salary a 3rd column which gives you the salary in the next row
FROM table_name so think of LEAD giving you a headstart

SELECT first_name, salary, In this scenario you get all records from table_name and
LAG(salary) OVER() next_row_salary a 3rd column which gives you the salary in the previous
FROM table_name row

SELECT first_name, region, salary, In this scenario we’d use LEAD to give us a 4th column
LEAD(salary) OVER(PARTITION BY region ORDER BY salary asc) with the next highest salary in the region compared to
next_higher_salary the current record
FROM table_name

Thanks for reading J

Page|8