-- who was in Lost in Translation??

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
WHERE m.name = 'Lost in Translation';



-- How many total students has Ms. Krabappel taught, and what are their names? 

SELECT s.name
FROM students s
JOIN grades g ON g.student_id = s.id
JOIN courses c ON g.course_id = c.id
JOIN teachers t ON c.teacher_id = t.id
WHERE t.name = 'Krabappel';



-- What teachers has Bart had?

SELECT t.name
FROM teachers t
JOIN courses c ON c.teacher_id = t.id
JOIN grades g ON g.course_id = c.id
JOIN students s ON s.id = g.student_id
WHERE s.name = 'Bart';



-- What courses have both Bart and Lisa taken?

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



-- Show all the students and the grades they have earned in Computer Science courses.

SELECT stud.name, g.grade, c.name
FROM students stud
JOIN grades g ON stud.id = g.student_id
JOIN courses c ON c.id = g.course_id
WHERE c.name like "Computer Science%"
ORDER BY stud.name;