sql 笔试题 student，teacher，course，sc 4个表

1、查询平均成绩大于60分的同学的学号和平均成绩。

               1) select sid,avg(score) from sc group by sid having avg(score)>60;

               2) select sid,avg(score)"savg" from sc group by sid having savg>60;     使用别名

2、查询没学过张三老师课的同学的学号和姓名。

              1)  select sid,sname from student where sid not in (select sid from sc where cid=(select cid from course where tid=(select tid from teacher where tname='张三')));

              2)  select sid,sname from student where sid not in (select sid from sc where cid=(select cid from course,teacher where course.tid=teacher.tid and tname='张三'));   笛卡尔积

              3) select sid,sname from student where sid not in (SELECT sc.sid FROM `sc` INNER JOIN course on course.cid=sc.cid INNER JOIN teacher on teacher.tid=course.tid where tname='张三')

3、查询各科成绩最高分、最低分和平均分。

             1) select cid,max(score),min(score),avg(score) from sc group by cid;

             2) select course.cid,course.cname,max(score),min(score),avg(score) from course,sc where course.cid=sc.cid group by sc.cid;

             3) select course.cid,course.cname,max(score),min(score),avg(score) from course inner join sc on course.cid=sc.cid group by sc.cid;

4、查询不同老师所教不同课程平均分从高到低显示。

1. select teacher.tid,teacher.tname,course.cid,course.cname,avg(score) from teacher,course,sc where teacher.tid=course.tid and course.cid=sc.cid group by sc.cid order by avg(score) DESC;
2. select teacher.tid,teacher.tname,course.cid,course.cname,avg(score)"avgs" from teacher inner join course on teacher.tid=course.tid inner join sc on course.cid=sc.cid group by cid order by avgs desc;
3. select teacher.tid,teacher.tname,course.cid,course.cname,avg(score) from teacher inner join course on teacher.tid=course.tid inner join sc on course.cid=sc.cid group by cid order by avg(score) desc;

本文参考链接：https://blog.csdn.net/qq_40197728/article/details/117563618