SQL Cheat Sheet: from Basic to Intermediate Level, Cheat Sheet of Programming Languages

Download SQL Cheat Sheet: from Basic to Intermediate Level and more Cheat Sheet Programming Languages in PDF only on Docsity!

SQL

CHEAT SHEET

created by

Tomi Mester

I originally created this cheat sheet for my SQL course and workshop participants.* But I have decided to open-source it and make it available for everyone who wants to learn SQL. It's designed to give you a meaningful structure but also to let you add your own notes (that's why the empty boxes are there). It starts from the absolute basics (SELECT * FROM table_name;) and guides you to the intermediate level (JOIN, HAVING, subqueries). I added everything that you will need as a data analyst/ scientist. The ideal use case of this cheat sheet is that you print it in color and keep it next to you while you are learning and practicing SQL on your computer. Enjoy! Cheers, Tomi Mester *The workshops and courses I mentioned: Online SQL tutorial (free): data36.com/sql-tutorial Live SQL workshop: data36.com/sql-workshop Practice SQL - an online SQL course for practicing: data36.com/practice-sql

FILTERING (the WHERE CLAUSE)

SELECT * FROM table_name WHERE column1 = 'expression'; "Horizontal filtering." This query returns every column from table_name - but only those rows where the value in column1 is 'expression'. Obviously this can be something other than text: a number (integer or decimal), date or any other data format, too.

ADVANCED FILTERING

Comparison operators help you compare two values. (Usually a value that you define in your query and values that exist in your SQL table.) Mostly, they are mathematical symbols, with a few exceptions: Comparison operator What does it mean? = Equal to <> Not equal to != Not equal to < Less than <= Less than or equal to > Greater than >= Greater than or equal to LIKE ‘%expression%’ Contains ‘expression’ IN (‘exp1’, ‘exp2’, ‘exp3’) Contains any of ‘exp1’, ‘exp2’, or ‘exp3’

A few examples: SELECT * FROM table_name WHERE column1 != 'expression'; This query returns every column from table_name, but only those rows where the value in column1 is NOT 'expression'. SELECT * FROM table_name WHERE column2 >= 10 ; It returns every column from table_name, but only those rows where the value in column2 is greater or equal to 10. SELECT * FROM table_name WHERE column3 LIKE ‘%xzy%’; It returns every column from table_name, but only those rows where the value in column3 contains the 'xyz' string.

MULTIPLE CONDITIONS

You can use more than one condition to filter. For that, we have two logical operators: OR, AND. SELECT * FROM table_name WHERE column1 != ‘expression’ AND column3 LIKE ‘%xzy%’; This query returns every column from table_name, but only those rows where the value in column1 is NOT ‘expression’ AND the value in column3 contains the 'xyz' string. SELECT * FROM table_name WHERE column1 != ‘expression’ OR column3 LIKE ‘%xzy%’; This query returns every column from table_name, but only those rows where the value in column1 is NOT ‘expression’ OR the value in column3 contains the 'xyz' string.

CORRECT KEYWORD ORDER

SQL is extremely sensitive to keyword order. So make sure you keep it right:

**1. SELECT

2. FROM
3. WHERE
4. ORDER BY
5. LIMIT**

SQL FUNCTIONS FOR AGGREGATION

In SQL, there are five important aggregate functions for data analysts/scientists:

**- COUNT()

• SUM()
• AVG()
• MIN()
• MAX()** A few examples: SELECT COUNT() FROM table_name WHERE column1 = 'something';* It counts the number of rows in the SQL table in which the value in column1 is 'something'. SELECT AVG(column1) FROM table_name WHERE column2 > 1000; It calculates the average (mean) of the values in column1, only including rows in which the value in column2 is greater than 1000.

SQL GROUP BY

The GROUP BY clause is usually used with an aggregate function (COUNT, SUM, AVG, MIN, MAX). It groups the rows by a given column value (specified after GROUP BY) then calculates the aggregate for each group and returns that to the screen. SELECT column1, COUNT(column2) FROM table_name GROUP BY column1; This query counts the number of values in column2 - for each group of unique column1 values. SELECT column1, SUM(column2) FROM table_name GROUP BY column1; This query sums the number of values in column2 - for each group of unique column1 values. SELECT column1, MIN(column2) FROM table_name GROUP BY column1; This query finds the minimum value in column2 - for each group of unique column values. SELECT column1, MAX(column2) FROM table_name GROUP BY column1; This query finds the maximum value in column2 - for each group of unique column values.

SQL HAVING

The execution order of the different SQL keywords doesn't allow you to filter with the WHERE clause on the result of an aggregate function (COUNT, SUM, etc.). This is because WHERE is executed before the aggregate functions. But that's what HAVING is for: SELECT column1, COUNT(column2) FROM table_name GROUP BY column HAVING COUNT(column2) > 100; This query counts the number of values in column2 - for each group of unique column1 values. It returns only those results where the counted value is greater than 100. Detailed explanation and examples here: https://data36.com/sql-data-analysis- advanced-tutorial-ep6/

CORRECT KEYWORD ORDER AGAIN

SQL is extremely sensitive to keyword order. So make sure you keep it right:

**1. SELECT

2. FROM
3. JOIN (ON)
4. WHERE
5. GROUP BY
6. HAVING
7. ORDER BY
8. LIMIT**

SUBQUERIES

You can run SQL queries within SQL queries. (Called subqueries.) Even queries within queries within queries. The point is to use the result of one query as an input value of another query. Example: SELECT COUNT() FROM (SELECT column1, COUNT(column2) AS inner_number_of_values FROM table_name GROUP BY column1) AS inner_query WHERE inner_number_of_values > 100;* The inner query counts the number of values in column2 - for each group of unique column1 values. Then the outer query uses the inner query's results and counts the number of values where inner_number_of_values are greater than 100. (The result is one number.) Detailed explanation here: https://data36.com/sql-data-analysis-advanced- tutorial-ep6/