katlegohw

katlegohw.sql

1. Create tables to hold data for your movie collection.

Create table Movies (id int identity primary key, 
Title varchar (100),
Director varchar (100), 
Runtime smallint,   --Consider naming this runtime_minutes (this makes it clear as to the units)
Genre_id int, 
Release_Year year);


-- director could be split out into it’s own table if you wanted more information about the director than just name
-- arguably it should be split out so we don’t have repeating strings in the table


Create table Genre (id int identity primary key,
genre_name varchar (100));

Create table ActorsandActresses (id int identity primary key,
first_name varchar (100),
last_name varchar (100),
DOB date);

Create table Movie_actors (id int identity primary key, 
movie_id int references movies(id),
ActorsandActresses_id int references ActorsandActresses(id), 
lead_role boolean); 


2. Once the tables are created please insert data into each table

Insert into Genre (genre_name)
values (‘Drama’), (‘Comedy’), (‘Horror’), (‘Documentary’), (‘Rom_Com’)

Insert into Movies ( Title, Director, Runtime, Genre_id, Release_Year)
values (‘Batman_Begins’, ‘Christopher_Nolan’, ’98’, ‘1’, ’2005’),
	(‘The_Dark_Knight’, ‘Christopher_Nolan’, ’98’, ‘1’, ’2008’),
	(‘The_Dark_Knight_Returns’, ‘Christopher_Nolan’, ’98’, ‘1’, ‘2012’),
	(‘Get_Out’, ‘Jordan_Peele’, ’90’, ‘3’, ‘2017’),
	(‘Airplane’, ‘David_Zucker’, ’65’, ‘2’,’1980’),
	(‘500_Days_of_Summer’, ‘Marc_Webb’, ’70’, ‘5’, ‘2009’);


-- You don’t need underscores for values being inserted.   Most times we will not want to display the data that way in the application.


Insert into ActorsandActresses (first_name, last_name, DOB)
values (‘Marion’, ‘Cortillard’, ’1975’),
	(‘Christian’, ‘Bale’, ’1974’), 
	(‘Morgan’, ‘Freeman’, ’1937’),
	(‘Brad’, ‘Pitt’, ’1970’),
	(‘Keanu’, ‘Reeves’, ’1963’),
	(‘Gwyneth’, ‘Paltrow’, ’1965’),
	(‘Chris’, ‘Evans’, ’1978’), 
	(‘James’, ‘Dean’, ’1931’),
	(‘Sandra’, ‘Bullock’, ’1972’), 
	(‘Paul’, ‘Downs’, ’1980’), 
	(‘Paul’, ‘Walker’, ‘1980’),
	(‘Elizabeth’, ‘Olsen’, ’1985’),
	(‘Jennifer’, ‘Lawrence’, ‘1995’),
	(‘Amber’, ‘Heard’, ‘1990’),
	(‘Jason’, ’Sudeikis’, ’1975’);

-- When you insert the year only into a date time column, it will become January 1, of the year you inserted.  Not a bad thing, just something to be aware of.


3. select out all the movie titles and their genres in 1 statement

Select title, genre.genre_name
From Movies
Join Genre on movies.genre_id=genre.id

--looks good!


4. select out all the movie titles, the actors/actresses in them, and the movie's genre in 1 statement

Select Title, Genre.genre_name, ActorsandActresses.first_name, ActorsandActresses.last_name
From Movies
Join Genre on Movies.genre_id=genre.id
Join Movie_actors on Movies.id=Movie_actors.id
Join ActorsandActresses on ActorsandActresses.id=Movie_actors.id


5. Update the tables about movies and change the year that 2 of movies came out to something else

Update Movies
Set Release_Year = ‘2010’
where Title = ‘Airplane’

Update Movies
Set Release_Year = ‘1990’
where Title = ‘Batman_Begins’


6. Select out all the movies that are 1 of the 3 genres you used, your choice

Select Title, Release_Year, Genre.genre_name
From Movies
Join Genre on Movies.genre_id=genre.id
Where Genre.genre_name = ’Drama’

7. Delete enough movies so that one of your genres is no longer being referenced

Delete from Movies 
where Title = ‘Airplane’

-- This works b/c you know that Airplane is the only movie currently using that genre.
-- a safer method would be to join to genres to ensure all the movies of that genre are deleted:

Delete from Movies
From movies
Join genre on genre.id = movies.genre_id
Where genre.genre_name = ‘comedy’


8. delete the genre that is no longer being used

Delete from Genre
where genre_name = ‘Comedy’