CSE 154 Lab 5 (SQL Queries) solutions

SELECT *
FROM actors
WHERE first_name = 'Julia';


SELECT id, name
FROM movies
WHERE year BETWEEN 1995 AND 2000;


SELECT actor_id
FROM roles
WHERE movie_id = 112290;


SELECT r.role
FROM roles r
JOIN movies m ON m.id = r.movie_id
WHERE m.name = 'Pi';


SELECT a.first_name, a.last_name, r.role 
FROM   actors a
JOIN roles r ON r.actor_id = a.id
JOIN movies m ON m.id = r.movie_id
WHERE m.name = 'Pi';


SELECT a.first_name, a.last_name
FROM actors a
JOIN roles r1 ON r1.actor_id = a.id
JOIN roles r2 ON r2.actor_id = a.id
JOIN movies m1 ON m1.id = r1.movie_id
JOIN movies m2 ON m2.id = r2.movie_id
WHERE m1.name = 'Kill Bill: Vol. 1'
AND m2.name = 'Kill Bill: Vol. 2';

Exercise 8a: Find the top 5 actors who have appeared in the most films
SELECT a.first_name, a.last_name, a.film_count
FROM actors a
ORDER BY a.film_count DESC
LIMIT 5;

Exercise 8b: Find the top 3 most popular genres in films
SELECT mg.genre, count(mg.genre)
FROM movies_genres mg
GROUP BY mg.genre
ORDER BY count(mg.genre) DESC
LIMIT 3;

Exercise 8c: Find directory who has directed the most Horror films
SELECT d.first_name, d.last_name, 
       COUNT(genre) as "# of Horror Films"
FROM directors d
JOIN movies_directors md 
  ON d.id = md.director_id
JOIN movies_genres mg
  ON md.movie_id = mg.movie_id
WHERE mg.genre = "Horror"
GROUP BY d.id, mg.genre
LIMIT 1;

(Thanks to Andrew Donaldson for working out these queries!)
SELECT g.grade
FROM grades g
JOIN courses c ON c.id = g.course_id
WHERE c.name = 'Computer Science 143';


SELECT DISTINCT s.name, g.grade
FROM students s
JOIN grades g ON g.student_id = s.id
JOIN courses c ON c.id = g.course_id
WHERE c.name = 'Computer Science 143' AND g.grade <= 'B-';


SELECT s.name, c.name, g.grade
FROM students s
JOIN grades g ON g.student_id = s.id
JOIN courses c ON c.id = g.course_id
WHERE g.grade <= 'B-'
ORDER BY s.name;


SELECT DISTINCT c.name
FROM courses c
JOIN grades g1 ON g1.course_id = c.id
JOIN students s1 ON g1.student_id = s1.id
JOIN grades g2 ON g2.course_id = c.id
JOIN students s2 ON g2.student_id = s2.id
WHERE s1.id < s2.id;