For this cheat sheet, let’s use an example of sales reps in each state and their sales numbers each day. Our first table, the “rep” table, has each sales rep’s name, ID, and state. The second table, the daily_sales table, has every sale that was made with the corresponding sales rep ID and revenue amount for every day. First, we’ll write the syntax for each command and then we’ll walk through a couple of examples using our sales tables.

Syntax

Working with Tables

CREATE TABLE

Create a table with columns

CREATE TABLE table_name (

column_1 datatype,

column_2 datatype,

column_3 datatype); 

DELETE

Delete values from a table based on given criteria

DELETE FROM table_name 

WHERE column_name = xyz;

ALTER TABLE

Add a column to a table

ALTER TABLE table_name

ADD column_name datatype:

AS

Rename a column

SELECT column_name AS 'Alias'

FROM table_name;

INSERT

Insert new values into a table

INSERT INTO table_name (column_1, column_2, column_3) 

VALUES (value_1, 'value_2', value_3);

LIMIT

Export a limited number of rows from your table

SELECT column_name(s)

FROM table_name

LIMIT number;

UPDATE

Update values in your table based on given criteria

UPDATE table_name

SET some_column = some_value

WHERE some_column = some_value;

LOGIC

BETWEEN

Select entries between two values

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value_1 AND value_2;

CASE WHEN

Set values of a column based on a set of conditions

SELECT column_name,

CASE

WHEN condition THEN 'Result_1'

WHEN condition THEN 'Result_2'

ELSE 'Result_3'

END AS new_column

FROM table_name;

GROUP BY

Group entries based on one or many column values (similar to Pivot grouping)

SELECT column_name, COUNT(*)

FROM table_name

GROUP BY column_name;

HAVING

Filter based on condition after performing a GROUP BY

SELECT column_name, COUNT(*)

FROM table_name

GROUP BY column_name

HAVING COUNT(*) > value;

IS NULL

Select entries where value is NULL

SELECT column_name(s)

FROM table_name

WHERE column_name IS NULL;

LIKE

Select entries that match a text pattern

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE ‘xyz’;

AND 

Select entries that meet multiple conditions

SELECT column_name 

FROM table_name

WHERE column_1 = xyz

AND column_2 = abc;

OR

Select entries that meet one of multiple conditions

SELECT column_name

FROM table_name

WHERE column_name = value_1

OR column_name = value_2;

DISTINCT

Find the distinct values from a column (remove duplicates)

SELECT DISTINCT column_name

FROM table_name;

MATH

COUNT

Return the number of values

SELECT COUNT(column_name)

FROM table_name;

MAX

Return the maximum value of the column

SELECT MAX(column_name)

FROM table_name;

MIN

Return the minimum value of the column

SELECT MIN(column_name)

FROM table_name;

ORDER BY

Sort based on a column either in descending or ascending order

SELECT column_name

FROM table_name

ORDER BY column_name ASC | DESC;

ROUND

Round values in a column

SELECT ROUND(column_name, integer)

FROM table_name;

SUM

Add the values in a column

SELECT SUM(column_name)

FROM table_name;

AVG

Find the average of the values in a column

SELECT AVG(column_name)

FROM table_name;

JOINS

INNER JOIN

Join two tables only where values are present in both tables

SELECT column_name(s)

FROM table_1

JOIN table_2

ON table_1.column_name = table_2.column_name;

OUTER JOIN

Join two tables with all values in one table and only the matches from another

SELECT column_name(s)

FROM table_1

LEFT JOIN table_2

ON table_1.column_name = table_2.column_name;

Applications

Find the name of rep 555

SELECT name

FROM rep

WHERE ID = ‘555’;

Find the maximum revenue in one day

SELECT MAX(revenue)

FROM daily_sales;

Get total revenue for by day

SELECT date, SUM(revenue)

FROM daily_sales

GROUP BY date

ORDER BY date;

Revenue by state and by day 

SELECT a.state, b.date, sum(revenue)

FROM rep a

JOIN daily_sales b

ON a.ID = b.ID

GROUP BY state, date;