SQL

Cheat Sheet
Mosh Hamedani

Code with Mosh (codewithmosh.com)

1st Edition
About this Cheat Sheet

This cheat sheet includes the materials I’ve covered in my SQL tutorial for
Beginners on YouTube.

https://youtu.be/7S_tz1z_5bA

Both the YouTube tutorial and this cheat cover the core language constructs and
they are not complete by any means.

If you want to learn everything SQL has to offer and become a SQL expert, check
out my Complete SQL Mastery Course.

Use the coupon code CHEATSHEET upon checkout to get this course with a
90% discount:

https://codewithmosh.com/p/complete-sql-mastery/
About the Author

Hi! My name is Mosh Hamedani. I’m a software engineer

with two decades of experience and I’ve taught over three
million how to code or how to become a professional
software engineer. It’s my mission to make software
engineering simple and accessible to everyone.

https://codewithmosh.com

https://youtube.com/user/programmingwithmosh

https://twitter.com/moshhamedani

https://facebook.com/programmingwithmosh/
Basics ........................................................................................................ 5
Comments ................................................................................................. 5
SELECT Clause ........................................................................................5
WHERE Clause ........................................................................................ 6
Logical Operators .................................................................................... 6
IN Operator ..............................................................................................7
BETWEEN Operator ................................................................................7
LIKE Operator .......................................................................................... 7
REGEXP Operator.................................................................................... 7
IS NULL Operator ...................................................................................8
ORDER BY Clause ...................................................................................8
LIMIT Clause ............................................................................................8
Inner Joins ...............................................................................................9
Outer Joins ...............................................................................................9
USING Clause ...........................................................................................9
Cross Joins ................................................................................................9
Unions .....................................................................................................10
Inserting Data ........................................................................................10
Want to Become a SQL Expert? ............................................................ 10
Basics

USE sql_store;

SELECT *
FROM customers
WHERE state = ‘CA’
ORDER BY first_name
LIMIT 3;

• SQL is not a case-sensitive language.

• In MySQL, every statement must be terminated with a semicolon.

Comments
We use comments to add notes to our code.

—- This is a comment and it won’t get executed.

SELECT Clause
—- Using expressions
give the column a name. Moreover, if you want space in the name, you need
quotes: ‘discount factor’
SELECT (points * 10 + 20) AS discount_factor
FROM customers

Order of operations:

• Parenthesis

• Multiplication / division

• Addition / subtraction

—- Removing duplicates

SELECT DISTINCT state

FROM customers
WHERE Clause
We use the WHERE clause to filter data.

Comparison operators:

• Greater than: >

• Greater than or equal to: >=

• Less than: <

• Less than or equal to: <=

• Equal: =

• Not equal: <>

• Not equal: !=

Logical Operators

—- AND (both conditions must be True)

SELECT *
FROM customers
WHERE birthdate > ‘1990-01-01’ AND points > 1000

—- OR (at least one condition must be True)

SELECT *
FROM customers
WHERE birthdate > ‘1990-01-01’ OR points > 1000

—- NOT (to negate a condition)

SELECT *
FROM customers
WHERE NOT (birthdate > ‘1990-01-01’)
IN Operator
—- Returns customers in any of these states: VA, NY, CA
SELECT *
FROM customers
WHERE state IN (‘VA’, ‘NY’, ‘CA’)
put NOT here if needed

BETWEEN Operator
SELECT *
FROM customers
WHERE points BETWEEN 100 AND 200

LIKE Operator
—- Returns customers whose first name starts with b
SELECT *
FROM customers
WHERE first_name LIKE ‘b%’

• %: any number of characters Likewise, can use ‘%b%’ and ‘%b’ to indicate the cases when b is anywhere or
when b is in the end

• _: exactly one character ‘_y’ means 2 characters and ends with y

‘b__y’ means 4 characters in the pattern bXXy

REGEXP Operator
—- Returns customers whose first name starts with a
SELECT *
FROM customers
WHERE first_name REGEXP ‘^a’

• ^: beginning of a string 写前⾯

• $: end of a string 写后⾯

• |: logical OR

• [abc]: match any single characters ‘[gim]e’ looks for ‘ge’, ‘ie’, or ‘me’

• [a-d]: any characters from a to d

More Examples

—- Returns customers whose first name ends with EY or ON

WHERE first_name REGEXP ‘ey$|on$’

—- Returns customers whose first name starts with MY

—- or contains SE
WHERE first_name REGEXP ‘^my|se’

—- Returns customers whose first name contains B followed by

—- R or U
WHERE first_name REGEXP ‘b[ru]’

IS NULL Operator
—- Returns customers who don’t have a phone number
SELECT *
FROM customers
WHERE phone IS NULL

put NOT here if needed

ORDER BY Clause
—- Sort customers by state (in ascending order), and then
—- by their first name (in descending order)
SELECT *
FROM customers
ORDER BY state, first_name DESC

即先看state，再看first name

LIMIT Clause
—- Return only 3 customers
SELECT *
FROM customers
LIMIT 3
—- Skip 6 customers and return 3
SELECT *
FROM customers
LIMIT 6, 3
at the end of the clauses

Inner Joins
SELECT * 如果SELECT customer_id 会导致error, 因为有2 columns of customer_id, so lead to ambiguity, 所以要写o.customer_id or
c.customer_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
When joining, the customer_id column of the customers table should be matched with the customer_id column of the orders table
When joining tables from different databases, prefix the table not part of the current database, not after ‘USE’

Outer Joins
—- Return all customers whether they have any orders or not
SELECT *
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
LEFT: return all in customers tables
RIGHT: return all in orders tables
如果没有left, right, 不满⾜on后⾯条件，即没有orders的顾客就不会被return了

USING Clause
If column names are exactly the same, you can simplify the join with the USING
clause.

SELECT *
FROM customers c
JOIN orders o
USING (customer_id)

Cross Joins 就像 cross product X x Y

—- Combine every color with every size

SELECT *
FROM colors implicit写法：
直接写成 FROM colors, sizes
CROSS JOIN sizes 就没有下⾯这⼀⾏
Unions join是横着combine，但union是竖着combine

—- Combine records from multiple result sets

SELECT name, address
FROM customers
UNION
SELECT name, address
FROM clients

Inserting Data
—- Insert a single record select the columns and can reorder
INSERT INTO customers(first_name, phone, points)
VALUES (‘Mosh’, NULL, DEFAULT)

—- Insert multiple single records insert multiple rows

INSERT INTO customers(first_name, phone, points)
VALUES
(‘Mosh’, NULL, DEFAULT),
(‘Bob’, ‘1234’, 10)

SELECT LAST_INSERT_ID( )

Want to Become a SQL Expert?

If you’re serious about learning SQL and getting a job as a software developer or
data scientist, I highly encourage you to enroll in my Complete SQL Mastery
Course. Don’t waste your time following disconnected, outdated tutorials. My
Complete SQL Mastery Course has everything you need in one place:

• 10 hours of HD video

• Unlimited access - watch it as many times as you want

• Self-paced learning - take your time if you prefer

• Watch it online or download and watch offline

• Certificate of completion - add it to your resume to stand out

• 30-day money-back guarantee - no questions asked

 The price for this course is $149 but the first 200 people who have downloaded this
cheat sheet can get it for $12.99 using the coupon code CHEATSHEET:

https://codewithmosh.com/p/complete-sql-mastery/
COUNT(*): count number of raws even if some are null
COUNT(value): count number of raws that are not null
GROUP BY …: put rows into groups
DATEDIFF(a, b): Days between a and b (e.g., 10 days)
TIMESTAMPDIFF(unit, a, b): Difference in custom units (YEAR, MONTH, DAY, etc.)
DATE_ADD(date, INTERVAL n DAY): Adds n days to a date
DATE_SUB(date, INTERVAL n DAY): Subtracts n days from a date

Clarify the different uses of ‘HAVING’ and “WHERE’: where filters results before grouping and having does so after grouping. Works on aggregated
results (like sums, counts, averages).