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 '_onathan'

Percent to match any number of letters before or after depending on placement.

 

WHERE name LIKE 'J%'

Review Time

Select Empty Data

SELECT * FROM users

WHERE name IS NULL;

SELECT * FROM users

WHERE name IS NOT NULL

Review Time

Add Rows

INSERT INTO users (

  first_name,

  last_name,

  email

) VALUES (

  'Bryan',

  'Smith',

  'bryan@devmountain.com'

);

Review Time

Delete Rows

DELETE FROM users

WHERE name = 'Bryan';

Be careful with this one. It is always a good practice to do a select first to see what data you will be deleting.

Review Time

Update Rows

UPDATE users

SET name = 'Jase'

WHERE user_id = 4;

Be careful with this one. It is always a good practice to do a select first to see what data you will be updating.

Review Time

DISTINCT

SELECT DISTINCT name FROM users;

Find the unique values. No duplicates.

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>

Changing 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.

SQL II

By jonmcd

SQL II

  • 165