sql面试50题

学过张三老师所教的所有课的同学的学号姓名

select st.st_name,st.st_id
from student as st
inner join score as s
on s.s_id = st.s_id
inner join  course as c
on c.s_id=s.s_id 
inner join teacher as t
on t.t_id = c.t_id
where t.name = "张三"
group by st.s_id

查询学过01和02的课程的学生

select s_id ,s_name from student 
where s_id  in 
(
select s_id from
(select s_id from score where c_id = "01") as a
inner join
(select s_id from score where c_id = "02") as 
b
on a.s_id = b.s_id 
)