-- Multi-table query exercise
SELECT id
FROM students
WHERE name = 'Bart';

SELECT id
FROM courses
WHERE name = 'Computer Science 142';

SELECT grade
FROM grades
WHERE student_id = 123
AND course_id = 10001;

-- Incremental JOIN query
SELECT bart.course_id
FROM grades bart
JOIN grades lisa ON bart.course_id = lisa.course_id
WHERE bart.student_id = 123
AND lisa.student_id = 888;

SELECT courses.name
FROM courses
JOIN grades g1 ON courses.id = g1.course_id
JOIN grades g2 ON courses.id = g2.course_id
JOIN students bart ON g1.student_id = bart.id
JOIN students lisa ON g2.student_id = lisa.id
WHERE bart.name = 'Bart'
AND lisa.name = 'Lisa';

-- More JOIN queries
SELECT DISTINCT teachers.name
FROM teachers
JOIN courses ON courses.teacher_id = teachers.id
JOIN grades ON grades.course_id = courses.id
JOIN students ON grades.student_id = students.id
WHERE students.name = 'Bart';

SELECT DISTINCT students.*
FROM students
JOIN grades ON grades.student_id = students.id
JOIN courses ON courses.id = grades.course_id
JOIN teachers ON teachers.id = courses.teacher_id
WHERE teachers.name = 'Krabappel';

-- IMDB practice queries

-- What are the names of all movies released in 1995?
SELECT name
FROM movies
WHERE year = 1995;

-- How many people played a part in the movie "Lost in Translation"?
SELECT count(a.id)
FROM actors a
JOIN roles r ON r.actor_id = a.id
JOIN movies m ON r.movie_id = m.id
WHERE m.name = 'Lost in Translation';

-- What are the names of all the people who played a part in the movie "Lost in Translation"?
SELECT DISTINCT a.first_name, a.last_name
FROM actors a
JOIN roles r ON r.actor_id = a.id
JOIN movies m ON r.movie_id = m.id
WHERE m.name = 'Lost in Translation';

-- Who directed the movie "Fight Club"?
SELECT d.first_name, d.last_name
FROM directors d
JOIN movies_directors md ON md.director_id = d.id
JOIN movies m ON md.movie_id = m.id
WHERE m.name = 'Fight Club';

-- How many movies has Clint Eastwood directed?
SELECT count(m.name)
FROM movies m
JOIN movies_directors md ON md.movie_id = m.id
JOIN directors d ON md.director_id = d.id
WHERE d.first_name = 'Clint'
AND d.last_name = 'Eastwood';

-- What are the names of all movies Clint Eastwood has directed?
SELECT m.name
FROM movies m
JOIN movies_directors md ON md.movie_id = m.id
JOIN directors d ON md.director_id = d.id
WHERE d.first_name = 'Clint'
AND d.last_name = 'Eastwood';

-- What are the names of all directors who have directed at least one horror film?
SELECT DISTINCT d.first_name, d.last_name
FROM directors d
JOIN movies_directors md ON md.director_id = d.id
JOIN movies m ON md.movie_id = m.id
JOIN movies_genres mg ON mg.movie_id = m.id
WHERE mg.genre = 'Horror';

-- What are the names of every actor who has appeared in a movie directed by Christopher Nolan?
SELECT a.first_name, a.last_name
FROM actors a
JOIN roles r ON r.actor_id = a.id
JOIN movies m ON m.id = r.movie_id
JOIN movies_directors md ON md.movie_id = m.id
JOIN directors d ON md.director_id = d.id
WHERE d.first_name = 'Christopher'
AND d.last_name = 'Nolan';
