sql查询

[董哥007]

学生表：S(sno,sname)
课程表：C(cno,cname,cteacher)
成绩表：G(gno,cno,sno,scores)

1.查询出没有被‘李明老师’所教的所有学生姓名。
2.查询出至少有2门课程不及格的所有学生姓名及其平均分。

[misaka去年夏天]

这题太经典了，楼上解答得没错(⊙v⊙)

[misaka去年夏天]

第二问这样写行不行？

select S.sname,avg(G.scores),sum(scores<60) as gk from S inner join G on S.sno=G.sno having gk>=2

[果酱很好吃]

1、两种都试试
SELECT S.sname FROM S INNER JOIN G ON S.sno=G.sno INNER JOIN C ON C.cno=G.cno WHERE C.cteacher<>'李明老师';
SELECT S.sname FROM S,G WHERE S.sno=G.sno AND G.cno NOT IN(SELECT cno FROM C WHERE cteacher='李明老师');
2、
SELECT S.sname,G.sno,cno FROM G,S WHERE S.sno=G.sno AND scores<60 GROUP BY G.sno HAVING cno>1;