Question

sql练习：查询所有课程成绩小于60 分的同学的学号、姓名；

student表sno
course表 cno，tno
sc表sno，cno，score
teacher表 tno
这个sql怎么写？
还有一个不会，如下：
(2)查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
(1)select st.*,s.score from student st
join sc s on st.sno=s.sno
join course c on s.cno=c.cno
where s.score <60

(2)select st.* from student st join sc s on st.sno=s.sno
join course c on s.cno=c.cno
join teacher t on c.tno=t.tno
where t.tname='谌燕'

这么写感觉不对呢

Answer 1

（2）这么写坑定不对，这么写得出的答案是“上过那个老师的课程的学生”而不是“上过那个老师所有课程的学生”,而且那个答案效率太低，还不如这个：
select sno,sname from student
where sno in(select sno from sc
where cno in (select cno from course
where tno in (select tno from teacher where tname='谌燕')));

Answer 2

select s.sno,s.sname from student s join sc c on s.sno=c.sno where c.sno not in (select distinct sno from sc where score>60);

Answer 3

第一个：
select sno,sname from student where sno not in (select sno from sc where score>=60)
第二个那个sc表中sno是sc表的主键还是student表的主键

追问

sno是student表的主键

Answer 4

select st.*,s.score from student st
inner join sc s on st.sno=s.sno
inner join course c on s.cno=c.cno
where s.score <60

掉了 inner 或者 left 或者 right