ERD is a common way to view data in a database.
It helps you visualize the data you are analyzing including:
- the names of the tables.
- the columns in each table
- 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?
- Data integrity is ensured.
- Data can be accessed quickly.
- Data is easily shared.
Types of Statements
- Create table: creates a new table in a database
- Drop table: removes a table in a database
- 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
- > (greater than)
- < (less than)
- >= (greater than or equal to)
- <= (less than or equal to)
- = (equal to)
- != (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:
- * (Multiplication)
- + (Addition)
- - (Subtraction)
- / (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 |
