SQL practice

# select the movie Troll 2
SELECT title FROM movies WHERE title LIKE '%Troll 2%';

#select the year that Troll 2 was made in
SELECT year FROM movies WHERE title LIKE '%Troll 2%';

#select all the movies with a rating above 90
SELECT title FROM movies WHERE rating > 90;

#select all the movies that came out in 1996
SELECT title FROM movies WHERE year = 1990;


#select all the movies that came out between 1990 and 1999
SELECT title FROM movies WHERE year between 1990 AND 1999;

#select all the movies that came out in the same year as Troll 2 (you might want to look into sub queries for this)
SELECT title, year FROM movies WHERE year = (SELECT year FROM movies WHERE title like '%Troll 2%');

#select all the characters that Bruce Willis has played (you will need a join statement for this)
SELECT cast_members.character FROM cast_members
JOIN actors ON cast_members.actor_id = actors.id
WHERE actors.name = 'Bruce Willis';

#select the actor who played James Bond in Skyfall
SELECT actors.name FROM actors
JOIN cast_members ON actors.id = cast_members.actor_id
JOIN movies ON cast_members.movie_id = movies.id
WHERE cast_members.character = 'James Bond' AND movies.title = 'Skyfall';

#select Mel Gibson's highest rated movie
SELECT movies.title, movies.rating FROM movies
JOIN cast_members ON cast_members.movie_id = movies.id J
OIN actors ON cast_members.actor_id = actors.id
WHERE actors.name = 'Mel Gibson'
ORDER BY  movies.rating DESC LIMIT 1 ;


# Find the character that Daniel Craig played in his highest rated movie
SELECT cast_members.character, movies.title, movies.rating FROM cast_members 
JOIN movies ON cast_members.movie_id = movies.id
JOIN actors ON cast_members.actor_id = actors.id
WHERE actors.name = 'Daniel Craig' ORDER BY movies.rating DESC LIMIT 1;