In this project, we'll continue to use postgres.devmountain.com to create more intermediate SQL queries.

When creating tables we can specify a column as having a foreign key. The datatype of our column must match the datatype of the column we are linking to. The most common foriegn key is a primary key which is usually an integer.

• Create a new table called movie with a movie_id, title, and media_type_id.
  • Make media_type_id a foreign key to the media_type_id column on the media_type table.
• Add a new entry into the movie table with a title and media_type_id.
• Query the movie table to get your entry.

CREATE TABLE movie (
  movie_id SERIAL,
  title TEXT,
  media_type_id INTEGER, 
  FOREIGN KEY(media_type_id) REFERENCES media_type(media_type_id)
);

INSERT INTO movie ( title, media_type_id ) VALUES ( 'Aladdin', 3 );

SELECT * FROM movie;

We can also add a foreign key to an existing table. Let's add one to our movie table that references genre_id on the genre table.

• Add a new column called genre_id that references genre_id on the genre table.
• Query the movie table to see your entry.

ALTER TABLE movie ADD COLUMN genre_id INTEGER REFERENCES genre(genre_id);

SELECT * FROM movie;

We don't want to leave the genre_id equal to null so let's add a value using the update command. With an update command you always want to use a WHERE clause. If you don't you will overwrite data on all records.

• Update the first entry in the movie table to a genre_id of 22.
• Query the movie table to see your entry.

UPDATE movie SET genre_id=22 WHERE movie_id=1;

SELECT * FROM movie;

Now that we know how to make foreign keys and change data, let's do some practice queries. The simplest way to use a foreign key is via a join statement.

• Join the artist and album tables to list out the artist name and album name.

SELECT a.title, ar.name 
FROM album a 
JOIN artist ar ON a.artist_id = ar.artist_id;

The next way to use a primary key is with a nested query/sub-select statement. By using parenthesis we can do a select inside of a select. This is really effective when you have a foreign key link between two tables because now we can filter our main query by criteria on a referenced table.

• Use a sub-select statement to get all tracks from the Track table where the genre_id is either Jazz or Blues.

SELECT * FROM Track 
WHERE genre_id IN ( SELECT genre_id FROM genre WHERE name = 'Jazz' OR name = 'Blues' );

• Update Phone on the Employee table to null where the EmployeeId is 1.
• Query the Employee table to get the employee you just updated.

UPDATE Employee SET Phone = null WHERE EmployeeId = 1;

SELECT * FROM Employee WHERE EmployeeId = 1;

Sometimes you want to know when there is no value. For example, let's use the customer table to figure out which customers do not have a company.

• Get all customers from the customer table who do not have a company.

SELECT * from customer WHERE Company IS null;

How many albums does each artist have? We could count manually, but no! Group by allows us to do aggregate counts.

• Select all artist ids, artist names, and count how many albums they have.

SELECT ar.artist_id, ar.name, COUNT(*) 
FROM artist ar
JOIN album a ON ar.artist_id = a.artist_id 
GROUP BY ar.artist_id;

Modify the solution to order the album count by largest to smallest.

Distinct is great if you want to get a dataset with no duplicates.

• Get all countries from the customer table with no duplicates.

SELECT DISTINCT country FROM customer;

Deleting rows can be dangerous if you are not cautious. Always do a select of what you plan to delete to make sure that you are going to delete the correct records.

• Select all records from the customer table where fax is null;
• Delete all records from the customer table where fax is null;

SELECT * FROM customer WHERE fax IS null;

/* The delete won't work since they are children using a foreign key. However, if there wasn't a foreign key, you would successfully delete all customers WHERE fax is null */
DELETE FROM customer WHERE fax IS null;

If you see a problem or a typo, please fork, make the necessary changes, and create a pull request so we can review your changes and merge them into the master repo and branch.

© DevMountain LLC, 2017. Unauthorized use and/or duplication of this material without express and written permission from DevMountain, LLC is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to DevMountain with appropriate and specific direction to the original content.