Instructions:
In this project, you’re going to make your own table with some small set of “famous people”, then make more tables about things they do and join those to create nice human-readable lists.
For example, here are types of famous people and the questions your data could answer:
- Movie stars: What movies are they in? Are they married to each other?
- Singers: What songs did they write? Where are they from?
- Authors: What books did they write?
- Fictional characters: How are they related to other characters? What books do they show up in?
Created Tables:
/* Create table about the people and what they do here */
CREATE TABLE famous_people (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT);
INSERT INTO famous_people(first_name,last_name) VALUES ("Amber", "Heard");
INSERT INTO famous_people(first_name,last_name) VALUES ("Johnny", "Depp");
INSERT INTO famous_people(first_name,last_name) VALUES ("Angelina", "Jolie");
INSERT INTO famous_people(first_name,last_name) VALUES ("Brad", "Pitt");
INSERT INTO famous_people(first_name,last_name) VALUES ("Jennifer", "Aniston");
INSERT INTO famous_people(first_name,last_name) VALUES ("Daniel", "Radcliffe");
INSERT INTO famous_people(first_name,last_name) VALUES ("Emma", "Watson");
INSERT INTO famous_people(first_name,last_name) VALUES ("Keira", "Knightley");
INSERT INTO famous_people(first_name,last_name) VALUES ("Orlando", "Bloom");
INSERT INTO famous_people(first_name,last_name) VALUES ("John", "Krasinski");
INSERT INTO famous_people(first_name,last_name) VALUES ("Emily", "Blunt");
INSERT INTO famous_people(first_name,last_name) VALUES ("Nancy Ellen", "Walls");
INSERT INTO famous_people(first_name,last_name) VALUES ("Steve", "Carell");
INSERT INTO famous_people(first_name,last_name) VALUES ("Nicole", "Kidman");
INSERT INTO famous_people(first_name,last_name) VALUES ("Tom", "Cruise");
CREATE TABLE marriage_partners (id INTEGER PRIMARY KEY AUTOINCREMENT, person_id1 INTEGER, person_id2 INTEGER);
INSERT INTO marriage_partners (person_id1,person_id2) VALUES (1,2);
INSERT INTO marriage_partners (person_id1,person_id2) VALUES (3,4);
INSERT INTO marriage_partners (person_id1,person_id2) VALUES (10,11);
INSERT INTO marriage_partners (person_id1,person_id2) VALUES (12,13);
INSERT INTO marriage_partners (person_id1,person_id2) VALUES (14,15);
CREATE TABLE movie_partners (id INTEGER PRIMARY KEY AUTOINCREMENT, person_id1 INTEGER, person_id2 INTEGER, movie_title TEXT);
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (1,2, "The Rum Diary");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (3,4, "Mr. and Mrs. Smith");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (4,5,"Fast Times at Ridgemont High Table Read");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (6,7, "Harry Potter");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (8,9, "Pirates of the Caribbean");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (2,8, "Pirates of the Caribbean");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (2,8, "Berlin I love you");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (2,9, "Pirates of the Caribbean");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (2,3, "The Tourist");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (10,11, "A Quiet Place");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (12,13, "40-year old virgin");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (10,13, "The Office");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (10,4, "The Big Short");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (14,15, "Days of Thunder");
INSERT INTO movie_partners (person_id1,person_id2, movie_title) VALUES (11,15, "Edge of Tomorrow 2");
For Movies made together, here is the code and the corresponding query results.
Code:
SELECT movie_partners.movie_title, a.first_name || ' ' || a.last_name AS actor1, b.first_name || ' ' || b.last_name AS actor2
FROM movie_partners
JOIN famous_people a
ON movie_partners.person_id1 = a.id
JOIN famous_people b
ON movie_partners.person_id2 = b.id;
Query Results:
| movie_title | actor1 | actor2 |
|---|---|---|
| The Rum Diary | Amber Heard | Johnny Depp |
| Mr. and Mrs. Smith | Angelina Jolie | Brad Pitt |
| Fast Times at Ridgemont High Table Read | Brad Pitt | Jennifer Aniston |
| Harry Potter | Daniel Radcliffe | Emma Watson |
| Pirates of the Caribbean | Keira Knightley | Orlando Bloom |
| Pirates of the Caribbean | Johnny Depp | Keira Knightley |
| Berlin I love you | Johnny Depp | Keira Knightley |
| Pirates of the Caribbean | Johnny Depp | Orlando Bloom |
| The Tourist | Johnny Depp | Angelina Jolie |
| A Quiet Place | John Krasinski | Emily Blunt |
| 40-year old virgin | Nancy Ellen Walls | Steve Carell |
| The Office | John Krasinski | Steve Carell |
| The Big Short | John Krasinski | Brad Pitt |
| Days of Thunder | Nicole Kidman | Tom Cruise |
| Edge of Tomorrow 2 | Emily Blunt | Tom Cruise |
For famous people who were married, the code is:
SELECT a.first_name || ' ' || a.last_name AS actor1, b.first_name || ' ' || b.last_name AS actor2
FROM marriage_partners
JOIN famous_people a
ON marriage_partners.person_id1 = a.id
JOIN famous_people b
ON marriage_partners.person_id2 = b.id;
Query results:
| actor1 | actor2 |
|---|---|
| Amber Heard | Johnny Depp |
| Angelina Jolie | Brad Pitt |
| John Krasinski | Emily Blunt |
| Nancy Ellen Walls | Steve Carell |
| Nicole Kidman | Tom Cruise |
Note: In this particular exercise, I utilize the concatenate operator "||" in order to concatenate two separate columns for the first name and the last name.
Comments
Post a Comment