-- CSE 190 M, Spring 2011, Marty Stepp
-- These are some of the queries we typed into the MySQL console in class.
-- These queries and others can be found in the lecture slides on SQL joins.

-- Names of all teachers Bart has 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";



-- All courses taken by both Bart and Lisa:

select c.name
from   courses c
join   grades g1 on g1.course_id = c.id
join   students s1 on s1.id = g1.student_id
join   grades g2 on g2.course_id = c.id
join   students s2 on s2.id = g2.student_id
where  s1.name = "Bart" and s2.name = "Lisa";




-- Names of all courses Ralph has taken, and the grade he got:

select c.name, g.grade
from courses c
join grades g on g.course_id = c.id
join students ralph on ralph.id = g.student_id
where ralph.name = "Ralph";

-- alternate version of the previous query:

select c.name, g.grade
from students ralph
join grades g on ralph.id = g.student_id
join courses c on c.id = g.course_id
where ralph.name = "Ralph";