SQL II
Review Time
*
Select Everything
Review Time
ORDER BY
Choose column which you would like your data ordered by. Has option ASCending or DESCending value.
Review Time
LIMIT
Limit the amount of data you get back from your query.
Review Time
SUM()
Finds the sum of the data. Access it like this: sum(COLUMN_NAME)
Review Time
COUNT()
Finds the count, or total number of items, of the data we query for. Access it like this: count(COLUMN_NAME)
Review Time
AVG()
Finds the average of the data we query for. Access it like this: avg(COLUMN_NAME). Remember average is the total amount divided by the total count
Review Time
MAX()
Finds the max of the data we query for. Access it like this: max(COLUMN_NAME). Returns only one piece of data.
Review Time
MIN()
Finds the minimum of the data we query for. Access it like this: min(COLUMN_NAME).
Returns only one piece of data.
Review Time
LIKE
Underscore to place for single letter
WHERE name LIKE '_ryan'
Percent to match any number of letters before or after depending on placement.
WHERE name LIKE 'B%'
Review Time
IS NULL, IS NOT NULL
SELECT * FROM users
WHERE name IS NOT NULL;
Review Time
INSERT INTO
INSERT INTO users (
first_name,
last_name,
) values (
'Bryan',
'Smith',
'bryan@devmountain.com'
);
Review Time
DELETE
Be careful with this one. It is always a good practice to do a select first to see what data you will be deleting.
DELETE FROM users
WHERE name = 'Bryan';
Review Time
UPDATE
Be careful with this one. It is always a good practice to do a select first to see what data you will be updating.
UPDATE users
SET name = 'Coco'
WHERE user_id = 4;
Review Time
DISTINCT
Find the unique values. No duplicates.
SELECT DISTINCT name FROM users;
Normalization
As your application grows, you will find a need for multiple tables. Each table will have data that another table may find useful. The process of splitting out our data into multiple tables is called normalization.
Please don't pile all of your data into one table. This would be like stuffing all of your clothes into one dresser drawer. You want a swimsuit? Too bad, here is that shirt you've had since 8th grade
Adding Columns
ALTER TABLE <table name>
ADD COLUMN <column name> <data type>
Adding Column Data Type
ALTER TABLE <table name>
ALTER COLUMN <column name>
SET DATA TYPE <data type>;
Renaming Column
ALTER TABLE <table name>
RENAME COLUMN <column name>
TO <new column name>;
Removing Column
ALTER TABLE <table name>
DROP COLUMN <column name>;
Change Table Name
ALTER TABLE <table name>
RENAME TO <new table name>;
Drop Table PERMANENT
DROP TABLE <table name>;
Relationships
- One to One
- One to Many
- Many to Many
Relationships
Primary Keys: Completely unique and only used once.
Foreign Keys: References a primary key from another table. Can be used many times. This is how tables relate to each other
CREATE TABLE <table name> (
id SERIAL PRIMARY KEY,
foreign_id INTEGER REFERENCES <table name> (<column name>)
)
One to One
Text
Person and a Passport
| human_id | human_name | human_dob |
|---|---|---|
| 1 | Bryan Smith | 11/06/1989 |
| 2 | Coco Smith | 07/25/14 |
| passport_id | human_id | country | issuded |
|---|---|---|---|
| 1 | 1 | USA | 04/14/2012 |
| 2 | 2 | USA | 06/11/2017 |
One to Many
Text
Authors and Books
| author_id | author_name |
|---|---|
| 1 | JK Rowling |
| 2 | Rudyard Kipling |
| book_id | author_id | title |
|---|---|---|
| 1 | 1 | harry potter and the philosopher's stone |
| 2 | 2 | jungle book |
| 3 | 1 | harry potter and the goblet of fire |
Many to Many
Text
Songs and Playlists
| song_id | title |
|---|---|
| 1 | new slang |
| 2 | angels |
| 3 | float on |
| 4 | sunflower |
| 5 | 2009 |
| id | song_id | playlist_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
| 4 | 4 | 1 |
| 5 | 5 | 2 |
| playlist_id | playlist_title |
|---|---|
| 1 | bahbeque |
| 2 | cool yo jets |
Joins
Text
To be able to access data from different tables, we need to join them together. This is usually done via a common piece of data like an ID
- INNER
- LEFT
- RIGHT
- FULL
INNER or simply JOIN
Text
One of the more common joins you will use. This will match rows from both tables that have the same key
SELECT * FROM users
JOIN favorite_colors
ON users.user_id = favorite_colors.user_id
But what if you want data from two tables that doesn't belong on both tables?
LEFT JOIN
Text
Imagine you have two tables, table_a and table_b and we want to join table_a to table_b. A LEFT JOIN will allow you to include all rows from table_a even if no match is found in table_b.
RIGHT JOIN
Text
This is the exact opposite as LEFT JOIN. It will include all rows from table_b even if they are not found on table_a
FULL JOIN
Text
Keep every row regardless of match.
Something you will need to consider when working with these different JOIN statements is that there will be null data.
Sometimes JOINS just won't cut it
Text
We can nest queries but this should be used as a last resort.
SELECT <column name>
FROM <table name>
WHERE <column name> IN(SELECT <column name>
FROM <table 2 name>
WHERE <column name>)
Grouping Data
Text
GROUP BY
Moves all selected data into a single row that shares the same value
HAVING
Similar to WHERE but HAVING combines data after groups and aggregates.