SQL Joins Lesson Notes

01-SQL-Joins.md

SQL Joins

Objectives

• 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

Guiding Questions

• 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 and species and owners 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 an INNER 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...

Exercise

Galvanize Movie Database:

Starting with this business logic, create an ERD and SQL queries to match it.

• Movies have a title, duration, rating and genre
• Awards have a kind and name
• Actor have a name, bio, and birthdate
• Each award can only be given to one movie
• Each movie can have multiple awards
• Each movie can have multiple actors
• Each actor can be in multiple movies

steps

• 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:

    curl -fsSL https://git.io/f7eca | psql gmovies_dev
    

    (Note: this command must be run outside of pqsl)

• 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
    

movies.sql

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('Frozen', 102, 'PG', 'Animation', TRUE, '2013-11-27 00:00:00 UTC', 7.6);

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('X-Men: Apocalypse', 144, 'PG-13', 'Action', TRUE, '2016-05-27 00:00:00 UTC', 7.4);

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('The Princess Bride', 98, 'PG', 'Adventure', FALSE, '1987-10-09 00:00:00 UTC', 8.1);

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('Pulp Fiction', 154, 'R', 'Crime', FALSE, '1994-10-14 00:00:00 UTC', 8.9);

INSERT INTO plots (movie_id, summary)
VALUES (1, 'Anna, a fearless optimist, sets off on an epic journey - teaming up with rugged mountain man Kristoff and his loyal reindeer Sven - to find her sister Elsa, whose icy powers have trapped the kingdom of Arendelle in eternal winter. Encountering Everest-like conditions, mystical trolls and a hilarious snowman named Olaf, Anna and Kristoff battle the elements in a race to save the kingdom. From the outside Anna''s sister, Elsa looks poised, regal and reserved, but in reality, she lives in fear as she wrestles with a mighty secret-she was born with the power to create ice and snow. It''s a beautiful ability, but also extremely dangerous. Haunted by the moment her magic nearly killed her younger sister Anna, Elsa has isolated herself, spending every waking minute trying to suppress her growing powers. Her mounting emotions trigger the magic, accidentally setting off an eternal winter that she can''t stop. She fears she''s becoming a monster and that no one, not even her sister, can help her.');

INSERT INTO plots (movie_id, summary)
VALUES (2, 'Since the dawn of civilization, he was worshipped as a God. Apocalypse, the first and most powerful mutant from Marvel''s X-Men universe, amassed the powers of many other mutants, becoming immortal and invincible. Upon awakening after thousands of years, he is disillusioned with the world as he finds it and recruits a team of powerful mutants, including a disheartened Magneto, to cleanse mankind and create a new world order, over which he will reign. As the fate of the Earth hangs in the balance, Raven with the help of Professor X must lead a team of young X-Men to stop their greatest nemesis and save mankind from complete destruction.');

INSERT INTO plots (movie_id, summary)
VALUES (3, 'A kindly grandfather sits down with his ill grandson and reads him a story. The story is one that has been passed down from father to son for generations. As the grandfather reads the story, the action comes alive. The story is a classic tale of love and adventure as the beautiful Buttercup, engaged to the odious Prince Humperdinck, is kidnapped and held against her will in order to start a war, It is up to Westley (her childhood beau, now returned as the Dread Pirate Roberts) to save her. On the way he meets a thief and his hired helpers, an accomplished swordsman and a huge, super strong giant, both of whom become Westley''s companions in his quest.');

INSERT INTO plots (movie_id, summary)
VALUES (4, 'Jules Winnfield and Vincent Vega are two hitmen who are out to retrieve a suitcase stolen from their employer, mob boss Marsellus Wallace. Wallace has also asked Vincent to take his wife Mia out a few days later when Wallace himself will be out of town. Butch Coolidge is an aging boxer who is paid by Wallace to lose his next fight. The lives of these seemingly unrelated people are woven together comprising of a series of funny, bizarre and uncalled-for incidents.');


INSERT INTO awards (movie_id, kind, name)
VALUES (1, 'Oscar', 'Best Animated Feature Film of the Year');

INSERT INTO awards (movie_id, kind, name)
VALUES (1, 'Oscar', 'Best Achievement in Music Written for Motion Pictures, Original Song');

INSERT INTO awards (movie_id, kind, name)
VALUES (3, 'Saturn Award', 'Best Fantasy Film');

INSERT INTO awards (movie_id, kind, name)
VALUES (3, 'Saturn Award', 'Best Costumes');

INSERT INTO awards (movie_id, kind, name)
VALUES (4, 'Oscar', 'Best Writing, Screenplay Written Directly for the Screen');


INSERT INTO actors (name, birthed_at)
VALUES ('Kristen Bell', '1980-07-18 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Idina Menzel', '1971-05-30 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('James McAvoy', '1979-04-21 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Michael Fassbender', '1977-04-02 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Jennifer Lawrence', '1990-08-15 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Cary Elwes', '1962-10-26 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Robin Wright', '1966-04-08 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Chris Sarandon', '1942-07-24 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('John Travolta', '1954-02-18 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Samuel L. Jackson', '1948-12-21 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Tim Roth', '1961-05-14 00:00:00 UTC');

INSERT INTO actors (name, birthed_at)
VALUES ('Amanda Plummer', '1957-03-23 00:00:00 UTC');


INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (1, 1, 'Anna');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (2, 1, 'Elsa');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (3, 2, 'Professor Charles Xavier');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (4, 2, 'Erik Lehnsherr / Magneto');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (5, 2, 'Raven / Mystique');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (6, 3, 'Westley');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (7, 3, 'The Princess Bride');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (8, 3, 'Prince Humperdinck');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (9, 4, 'Vincent Vega');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (10, 4, 'Jules Winnfield');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (11, 4, 'Pumpkin / Ringo');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (12, 4, 'Honey Bunny / Yolanda');

pets.sql

DROP TABLE IF EXISTS pets CASCADE;
DROP TABLE IF EXISTS toys;
DROP TABLE IF EXISTS owners CASCADE;
DROP TABLE IF EXISTS owners_pets;

CREATE TABLE pets (
  id serial PRIMARY KEY,
  name varchar(255),
  species varchar(255),
  age integer
);

CREATE TABLE toys (
  id serial PRIMARY KEY,
  type varchar(255),
  pet_id integer NOT NULL REFERENCES pets ON DELETE CASCADE
);

CREATE TABLE owners (
  id serial PRIMARY KEY,
  name varchar(255)
);

CREATE TABLE owners_pets (
  id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owners ON DELETE CASCADE,
  pet_id integer NOT NULL REFERENCES pets ON DELETE CASCADE
);

INSERT INTO pets (name, species, age) VALUES ('Zane', 'lizard', 12);
INSERT INTO pets (name, species, age) VALUES ('Pesto', 'dog', 3);
INSERT INTO pets (name, species, age) VALUES ('Mimi', 'pig', 5);

INSERT INTO toys (type, pet_id) VALUES ('just a rock', 1);
INSERT INTO toys (type, pet_id) VALUES ('chewy bone', 2);
INSERT INTO toys (type, pet_id) VALUES ('plush bear', 2);

INSERT INTO owners (name) VALUES ('Terry');
INSERT INTO owners (name) VALUES ('Jo');
INSERT INTO owners_pets (owner_id, pet_id) VALUES (1, 1);
INSERT INTO owners_pets (owner_id, pet_id) VALUES (1, 2);
INSERT INTO owners_pets (owner_id, pet_id) VALUES (2, 2);