In 1.sql, write a SQL query to list the titles of all movies released in 2008.

select title
FROM movies
WHERE year = 2008;

In 2.sql, write a SQL query to determine the birth year of Emma Stone.

select birth
FROM people
WHERE name = 'Emma Stone';

In 3.sql, write a SQL query to list the titles of all movies with a release date on or after 2018, in alphabetical order.

select title
FROM movies
WHERE year >= 2018;

In 4.sql, write a SQL query to determine the number of movies with an IMDb rating of 10.0.

select count(*)
FROM movies
JOIN ratings
	on movies.id =
	ratings.movie_id
WHERE rating = 10;

In 5.sql, write a SQL query to list the titles and release years of all Harry Potter movies, in chronological order

select title, year
FROM movies
WHERE title like 'Harry Potter %'
ORDER by year ASC;

In 6.sql, write a SQL query to determine the average rating of all movies released in 2012

select round(avg(rating))
FROM ratings
JOIN movies
	on ratings.movie_id = 
	movies.id
WHERE year = 2012;

In 7.sql, write a SQL query to list all movies released in 2010 and their ratings, in descending order by rating. For movies with the same rating, order them alphabetically by title.

select title, year, rating
FROM ratings
JOIN movies
	on ratings.movie_id = 
	movies.id
WHERE year = 2010
ORDER BY rating DESC, title;

In 8.sql, write a SQL query to list the names of all people who starred in Toy Story

WITH actor_name AS (SELECT name, movie_id
FROM people
JOIN stars
	on people.id =
	stars.person_id
	)
SELECT actor_name.name
FROM actor_name
JOIN movies
	on actor_name.movie_id =
	movies.id
WHERE movies.title = "Toy Story";

In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year

WITH actor_name AS (SELECT name, movie_id,
		birth
FROM people
JOIN stars
	on people.id =
	stars.person_id
	)
SELECT actor_name.name
FROM actor_name
JOIN movies
	on actor_name.movie_id =
	movies.id
WHERE movies.year = 2004
GROUP BY actor_name.name
ORDER BY actor_name.birth;

In 10.sql, write a SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.

WITH actor_name AS (SELECT name, movie_id
FROM people
JOIN directors
	on people.id =
	directors.person_id
	)
SELECT actor_name.name
FROM actor_name
JOIN ratings
	on actor_name.movie_id =
	ratings.movie_id
WHERE rating >= 9.0;

In 11.sql, write a SQL query to list the titles of the five highest rated movies (in order) that Chadwick Boseman starred in, starting with the highest rated.

WITH actor_info AS (SELECT movie_id, person_id, title, name
FROM movies m INNER JOIN stars s
	on m.id = s.movie_id INNER JOIN people p
	on p.id = s.person_id
	)
SELECT actor_info.name
from actor_info
JOIN ratings
	on actor_info.movie_id =
	ratings.movie_id
WHERE actor_info.name = "Chadwick Boseman"
ORDER BY rating DESC
LIMIT 5;

In 12.sql, write a SQL query to list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred

WITH actor_info AS (SELECT movie_id, person_id, title, name
FROM movies m INNER JOIN stars s
	on m.id = s.movie_id INNER JOIN people p
	on p.id = s.person_id
	)
SELECT actor_info.title
from actor_info
JOIN ratings
	on actor_info.movie_id =
	ratings.movie_id
	WHERE actor_info.name in ("Johnny Depp", "Helena Bonham Carter");
  
  In 13.sql, write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred
  
  SELECT DISTINCT(name)
FROM people
WHERE name IS NOT "Kevin Bacon"
AND id IN (
SELECT person_id
FROM stars
WHERE movie_id IN (
SELECT movie_id
FROM stars
WHERE person_id IN (
SELECT id 
 FROM people 
 WHERE name IS "Kevin Bacon" 
 and birth = 1958
)
)
);