-- CSE 190 M, Spring 2008
-- SQL queries done in lecture
-- (SQL comments start with a --)

-- 1a. This query shows the IDs of all courses that both bart and lisa have taken.
-- the thought process give me all courses where
-- there is a grade record for lisa (id=123) taking the course, AND
-- there is a grade record for bart (id=888) taking the course

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


-- 1b. However, the preceding query isn't so good.  It requires you to know the
-- IDs of Bart and Lisa's student records, and all you get is course IDs.
-- We'd prefer to work with names.  To map from the names 'Bart' and 'Lisa'
-- to course names like 'Computer Science 143', we would do:

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


-- 2. the names of all Bart's teachers
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 g.student_id = s.id
WHERE	s.name = 'Bart';
