MS SQL

GO Question 6 Eraserhead ---------- We''ll be switching over to the IMDB database for the remaining questions this week.

Show all principals listed for the movie "Eraserhead." For each principal, show the name of the movie, the category, and the name of the principal. Also include the characters that actors and actresses played (display N/A for NULL values). Format all columns to 20 characters wide.

Display in order by name.

Hint: title_basics has information about shows and name_basics has information about people. title_principals links people to shows with information about the what kind of job the person had on the show. Use the category column in title_principals here, rather than the job column.

Correct results will look like this:

Title Category Name Characters -------------------- -------------------- -------------------- -------------------- Eraserhead actor Allen Joseph ["Mr. X"] Eraserhead actress Charlotte Stewart ["Mary X"] Eraserhead director David Lynch N/A Eraserhead cinematographer Frederick Elmes N/A Eraserhead cinematographer Herbert Cardwell N/A Eraserhead actor Jack Nance ["Henry Spencer"] Eraserhead actress Jeanne Bates ["Mrs. X"] ' + CHAR(10)

GO

USE IMDB

-- -- [Insert your code here]

-- GO

GO Question 7 ----------------- What genres has Quentin Tarantino directed? Produce the title, type, year and genre for shows where Quentin Tarantino was the director. Format Title as 30 characters wide and genre as 20 characters wide. Order by Year.

Hint: title_directors has information about which people directed which shows. title_genres has information about what genres (up to 3) a particular show belongs to.

Correct results will have 63 rows formatted as:

Title Type Year Genre ------------------------------ ------------ ----------- -------------------- Untitled Star Trek Project movie NULL Action Untitled Star Trek Project movie NULL Adventure Untitled Star Trek Project movie NULL Sci-Fi Love Birds in Bondage short 1983 Comedy Love Birds in Bondage short 1983 Drama Love Birds in Bondage short 1983 Short My Best Friend''s Birthday movie 1987 Comedy Reservoir Dogs: Sundance Insti video 1991 Drama Reservoir Dogs: Sundance Insti video 1991 Short Reservoir Dogs movie 1992 Crime ... Untitled Quentin Tarantino/196 movie 2019 Crime Untitled Quentin Tarantino/196 movie 2019 Drama Untitled Quentin Tarantino/196 movie 2019 Thriller ' + CHAR(10)

GO

-- -- [Insert your code here]

-- GO

GO Question 8 Quentin Tarantino''s popular movies ----------------------------------- Produce a list of all of Quentin Tarantino''s movies in descending order by rating. Format title as 30 characters wide. Your results should appear as below (I escaped the apostrophe in the last result).

Hint: title_ratings contains the number of votes and average rating for each show.

Title Type Year Rating ------------------------------ ------------ ----------- --------------------------------------- Pulp Fiction movie 1994 8.9 Kill Bill: The Whole Bloody Af movie 2011 8.8 Django Unchained movie 2012 8.4 Inglourious Basterds movie 2009 8.3 Reservoir Dogs movie 1992 8.3 Kill Bill: Vol. 1 movie 2003 8.1 Kill Bill: Vol. 2 movie 2004 8.0 Sin City movie 2005 8.0 The Hateful Eight movie 2015 7.8 Grindhouse movie 2007 7.6 Jackie Brown movie 1997 7.5 Death Proof movie 2007 7.1 Four Rooms movie 1995 6.7 My Best Friend''s Birthday movie 1987 5.7 ' + CHAR(10)

GO

-- -- [Insert your code here]

-- GO

GO Question 9 Top 10 SF Series ---------------- Find the 10 most highly rated SF TV series. Include only series with 10000 or more votes. Format Title using 30 characters.

Hint: Use SELECT DISTINCT genre and SELECT DISTINCT titleType to determine the correct values to use to match SF TV series.

Correct results will look like this:

Title Rating # of Votes ------------------------------ --------------------------------------- ----------- Firefly 9.1 210460 Westworld 8.9 254053 Black Mirror 8.9 202575 The X-Files 8.7 170522 Mystery Science Theater 3000 8.6 20614 The Prisoner 8.6 10202 The Venture Bros. 8.6 20478 The Handmaid''s Tale 8.6 55338 Altered Carbon 8.5 35421 Utopia 8.5 32097 ' + CHAR(10)

GO

-- -- [Insert your code here]

-- GO

GO Question 10 ------------------------------ Use INTERSECT and the title_principals table to find all movies where David Lynch and Angelo Badalamenti worked together. Include ID and Title, format Title as 20 characters wide, and order alphabetically by Title. Don''t bother with title_directors or title_writers, just use title_principals.

Correct results will look like this:

ID Title --------- ------------------------------ tt0090756 Blue Velvet tt0116922 Lost Highway tt0166896 The Straight Story tt0105665 Twin Peaks: Fire Walk with Me tt5334704 Twin Peaks: The Missing Pieces tt0100935 Wild at Heart ' + CHAR(10)