- Explain what a Relational Databases is
- Describe why Relational Databases are useful
- Diagram 1-to-many & many-to-many relationships with ERDs
- Create primary and foreign keys on tables
- Join tables with SQL
- Use aggregate & group by queries
-
Explain what a Relational Databases is in your own words.
Your answer...
-
Why are Relational Databases useful?
Your answer...
-
Build an ERD for a database with
pets
andspecies
andowners
entites? When finished, add a new entity of your choosing.Your answer...
-
Add some attributes to your pet-based ERD, then practice writing
CREATE TABLE
queries for the tables.Your answer...
-
Identify 4 Postgres column constraints:
- Not-null
- Unique
- Primary key
- Foreign key
Your answer...
-
What does a join refer to in a relational database?
Your answer...
-
What is the difference between a
FULL JOIN
and anINNER JOIN
?Your answer...
-
Here is an example set of tables for the pets-erd: https://git.io/fpYDd. Run the following command to start with a populated pet database:
dropdb pets_dev createdb pets_dev curl -fsSL https://git.io/fpYDd | psql pets_dev psql pets_dev
Take a minute to explore the tables, and data using psql commands. What will the following query return?
SELECT * FROM pets INNER JOIN toys ON pets.id = toys.pet_id;
Your answer...
-
How would you query all pets with their respective owners?
Your answer...
Starting with this business logic, create an ERD and SQL queries to match it.
Movies
have a title, duration, rating and genreAwards
have a kind and nameActor
have a name, bio, and birthdate- Each
award
can only be given to onemovie
- Each
movie
can have multipleawards
- Each
movie
can have multipleactors
- Each
actor
can be in multiplemovies
-
Translate logic to an ERD
-
Create database and tables:
- Run
createdb gmovies_dev
- Update these
CREATE TABLE
queries with the necessary primary and foreign keys:CREATE TABLE movies ( id serial, title text, duration integer, rating varchar(10), genre text, is_3d boolean NOT NULL, released_at timestamp with time zone, score numeric(3, 1) ); CREATE TABLE plots ( id serial, movie_id integer, summary text ); CREATE TABLE awards ( id serial, movie_id integer, kind text, name text ); CREATE TABLE actors ( id serial, name text, birthed_at timestamp with time zone ); CREATE TABLE actors_movies ( id serial, actor_id integer, movie_id integer, role text );
- Run the queries above in your
gmovies_dev
database and check for errors. - Run the following command to populate the tables defined above:
(Note: this command must be run outside of pqsl)curl -fsSL https://git.io/f7eca | psql gmovies_dev
- Run
-
Use Inner Joins:
- Write a join query to get all movies with awards
- Write a join query to get the actors name, movies title, and role.
- Using the query from above, select only name, title & role from The Princess Bride.
- Write an SQL command that displays the follow rows.
title | released_at | score | actor_name | role --------------------+------------------------+-------+--------------------+-------------------------- Pulp Fiction | 1994-10-13 17:00:00-07 | 8.9 | John Travolta | Vincent Vega Pulp Fiction | 1994-10-13 17:00:00-07 | 8.9 | Amanda Plummer | Honey Bunny / Yolanda Pulp Fiction | 1994-10-13 17:00:00-07 | 8.9 | Tim Roth | Pumpkin / Ringo Pulp Fiction | 1994-10-13 17:00:00-07 | 8.9 | Samuel L. Jackson | Jules Winnfield The Princess Bride | 1987-10-08 17:00:00-07 | 8.1 | Robin Wright | The Princess Bride The Princess Bride | 1987-10-08 17:00:00-07 | 8.1 | Cary Elwes | Westley The Princess Bride | 1987-10-08 17:00:00-07 | 8.1 | Chris Sarandon | Prince Humperdinck Frozen | 2013-11-26 16:00:00-08 | 7.6 | Idina Menzel | Elsa Frozen | 2013-11-26 16:00:00-08 | 7.6 | Kristen Bell | Anna X-Men: Apocalypse | 2016-05-26 17:00:00-07 | 7.4 | Jennifer Lawrence | Raven / Mystique X-Men: Apocalypse | 2016-05-26 17:00:00-07 | 7.4 | Michael Fassbender | Erik Lehnsherr / Magneto X-Men: Apocalypse | 2016-05-26 17:00:00-07 | 7.4 | James McAvoy | Professor Charles Xavier