CSE 190 M 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';


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;