Basic SQL

Entity Relationship Diagram(ERD): 
ERD is a common way to view data in a database.
It helps you visualize the data you are analyzing including:

1. the names of the tables.
2. the columns in each table
3. the way the tables work together

SQL (Structured Query Language)

• easy to understand
• access data directly
• easy to audit and replicate
• multiple tables at once
• allows you to analyze more complex questions than dashboard tools like Google Analytics

Why Do Business Choose SQL?

1. Data integrity is ensured. 
2. Data can be accessed quickly. 
3. Data is easily shared. 

Types of Statements

1. Create table: creates a new table in a database
2. Drop table: removes a table in a database
3. Select - query: allows you to read data and display it. 

Select & From

• Select: tell the query what columns you want back. 
• From: what table you are querying from. 

select *
from demo.order

Queries
LIMIT

• seeing only a few lines of data 
• in MySQL, used as 

select top 10 *
from orders;

ORDER BY

• order out table by any row 
• default: ascending order / put 'desc' at the end for descending order

select *
from web_events
order by occurred_at desc

• create an order within the order

select occurred_at, total_amt_usd

from orders

order by total_amt_usd desc, occurred_at desc

limit 5

WHERE

subset out tables based on conditions that must be met. 

when finding non-numeric values, put a single quotation mark

1. > (greater than)
2. < (less than)
3. >= (greater than or equal to)
4. <= (less than or equal to)
5. = (equal to)
6. != (not equal to) 

select * 

from orders

where gloss_amt_usd > 1000

limit 5

select name, website, primary_poc

from accounts

where name = 'Exxon Mobil'

DERIVED COLUMN

common operators include: 

1. * (Multiplication)
2. + (Addition)
3. - (Subtraction)
4. / (Division)

select id, account_id, standard_amt_usd/standard_qty as unit_price

from orders

limit 10

LIKE

This allows you to perform operations similar to using WHERE and =, 

but for cases when you might not know exactly what you are looking for. 

• Wildcards(%)
• Case-sensitive

select * 

from orders

where account LIKE '%google%'

select name

from accounts

where name like 'C%'

IN

This allows you to perform operations similar to using WHERE and =, 

but for more than one condition.

select name, primary_poc,sales_rep_id

from accounts

where name in ('Walmart', 'Target', 'Nordstrom')

NOT

This is used with IN and LIKE to select all of the rows NOT LIKE or NOT IN a certain condition.

select name, primary_poc,sales_rep_id

from accounts

where name NOT in ('Walmart', 'Target', 'Nordstrom')

select *

from web_events

where channel NOT in ('organic', 'adwords' )

select name

from accounts

where name not like '%s'

AND & BETWEEN

These allow you to combine operations where all combined conditions must be true.

select * 

from orders

where standard_qty > 1000 

and poster_qty = 0

and gloss_qty = 0

select * 

from accounts

where name NOT LIKE 'C%' 

AND NAME NOT LIKE '%s'

select * 

from web_events

where channel in ('organic', 'adwords')

and occurred_at between '2016-01-01' and '2017-01-01'

order by occurred_at desc

OR

This allows you to combine operations where at least one of the combined conditions must be true.

select id

from orders

where gloss_qty > 4000 or poster_qty > 4000

select *

from orders

where standard_qty = 0 or (gloss_qty > 1000 or poster_qty > 1000)

select *

from accounts

where *name like 'C%' OR NAME LIKE 'W%' )

and (primary_poc LIKE '%ana%' or primary_poc LIKE '%Ana%')

and or primary_poc NOT LIKE '%eana%'

Summary

Statement	How to Use It	Other Details
SELECT	SELECT Col1, Col2, ...	Provide the columns you want
FROM	FROM Table	Provide the table where the columns exist
LIMIT	LIMIT 10	Limits based number of rows returned
ORDER BY	ORDER BY Col	Orders table based on the column. Used with DESC.
WHERE	WHERE Col > 5	A conditional statement to filter your results
LIKE	WHERE Col LIKE '%me%'	Only pulls rows where column has 'me' within the text
IN	WHERE Col IN ('Y', 'N')	A filter for only rows with column of 'Y' or 'N'
NOT	WHERE Col NOT IN ('Y', 'N')	NOT is frequently used with LIKE and IN
AND	WHERE Col1 > 5 AND Col2 < 3	Filter rows where two or more conditions must be true
OR	WHERE Col1 > 5 OR Col2 < 3	Filter rows where at least one condition must be true
BETWEEN	WHERE Col BETWEEN 3 AND 5	Often easier syntax than using an AND