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 8: Haxorz Queries
SELECT a.first_name, a.last_name, a.film_count
FROM actors a
ORDER BY a.film_count DESC
LIMIT 5;


SELECT mg.genre, count(mg.genre)
FROM movies_genres mg
GROUP BY mg.genre
ORDER BY count(mg.genre) DESC
LIMIT 3;


SELECT d.first_name, d.last_name
FROM movies_directors md
JOIN directors d ON md.director_id = d.id
GROUP BY md.director_id
ORDER BY count(md.director_id) DESC
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;