-- 123
SELECT id
FROM students
WHERE name = 'Bart';

-- 10001
SELECT id
FROM courses
WHERE name = 'Computer Science 142';

-- B-
SELECT grade
FROM grades
WHERE student_id = 123
AND course_id = 10001;

-- basic join
SELECT *
FROM students
JOIN grades;

SELECT *
FROM students
JOIN grades ON id = student_id;

SELECT t.name
FROM teachers t
JOIN courses c ON t.id = teacher_id;

-- exercise 1
SELECT g.grade
FROM grades g
JOIN courses c ON g.course_id = c.id
JOIN students s ON g.student_id = s.id
WHERE s.name = 'Bart'
AND c.name = 'Computer Science 142';

-- What are the names of all teachers Bart has had?
-- from: teachers
-- select: name
-- join: students, grades, courses
-- where: bart
SELECT DISTINCT t.name
FROM teachers t
JOIN courses c ON 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';

-- students <-> grades <-> courses <-> teachers

-- How many movies has Clint Eastwood directed?
-- from: movies
-- select: name
-- join: movies_directors, directors
-- where: clint eastwood

select count(m.name)
from movies m
join movies_directors md on md.movie_id = m.id
join directors d on md.director_id = d.id
where d.first_name = 'Clint'
and d.last_name = 'Eastwood';