sql101_hw_barns #sql101

sbarns.sql

--1. Create Tables:

Create table Genres (
Id int primary key
, genre varchar(50))

-- I would recommend making all your ids IDENTITY columns so those ids are generated automatically.  I'm making the comment here but this would be true across the board.

Create table Movies (
id int primary key
, title varchar(150)
, genre_id int references Genres(id)
, director varchar(100)
, runtime_minutes INT
, release_year INT)

-- is 150 long enough for a title?  I would recommend at least 500 or 1000.
 
Create table Actors (
Id int primary key
, first_name varchar(50)
, last_name varchar(50)
, DOB date)
 
Create table Actors_movies (
Id int primary key
, actor_id int references actors(id)
, movie_id int references movies(id))
 
 
2. Insert Data:
 
Insert into Genres (id, genre) values(1, ‘comedy’),(2, ‘horror’),(3, ‘romcom’)
 
Insert into Movies (id, title, genre_id, director, runtime_minutes, release_year)
values(1, ‘Step Brothers’, 1, ‘Adam McKay’, 106, 2008),
(2, ‘Bridesmaids’, 1, ‘Paul Feig’, 132, 2011),
(3, ‘Hereditary’, 2 ‘Ari Aster’, 127, 2018),
(4, ‘Sinister’, 2, ‘Scott Derrickson’, 110, 2012),
(5, ‘Palm Springs’, 3, ‘Max Barbakow’, 90, 2020)
 
 
Insert into Actors (id, first_name, last_name, DOB)
values(1, ‘Will’, ‘Ferrell’, 1967-07-16),
(2, ‘Kathryn’, ‘Hahn’, 1973-07-23),
(3, ‘Adam’, ‘Scott’, 1973-04-03),
(4, ‘Melissa’, ‘McCarthy’, 1970-08-26),
(5, ‘Kristen’, ‘Wiig’, 1973-08-22),
(6, ‘Maya’, ‘Rudolf’, 1972-07-27),
(7, ‘Ellie’, ‘Kemper’, 1980-05-02),
(8, ‘Rebel’, ‘Wilson’, 1980-03-01),
(9, ‘Ethan’, ‘Hawk’, 1970-11-06),
(10, ‘Toni’, ‘Collette’, 1972-11-01),
(11, ‘Ann’, ‘Dowd’, 1956-01-30),
(12, ‘Alex’, ‘Wolff’, 1997-11-01),
(13, ‘Milly’, ‘Shapiro’, 2002-07-16),
(14, ‘Andy’, ‘Samberg’, 1978-08-18),
(15, ‘Cristin’, ‘Milioti’, 1985-08-16)
 
 -- The dates in the insert also need to be in quotes.
 
3.
Select genre_id, title from Movies

-- this only gives you the genre id you need to join to genres to get the actual genre name
 
4. (Definitely struggled on this one)
Select genre_id, title
From movies join
Actors_movies on movie.id=actors_movies.movie_id
Join actors on actor.id=actors_movies.actor_id

-- you need an `s` after movie to make it movies.id
-- same with actor.id  should be actors.id


5.
Update movies set release_year=2010 where ID=1
Update movies set release_year=2020 where ID=2

 
6.
Select * from movies
Where genre_id=2

--While this is correct, we don't necessarily know the id all the time.  A safer way to do it would be to join to genres and specify the name of the genre.
 
7.
Delete from Movies
Where ID=5

-- if you have a large dataset, it would be difficult to do it this way.  You should join to genres and delete all movies tied to one of them:

delete from movies
where genre_id in 
    (select id from genres where genre = 'romcom');
 
8.
Delete from Genres
Where ID=3