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

thcjp 阅读:49 2022-09-20 16:58:29 评论:0

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
标签:mysql
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

搜索
关注我们

一个IT知识分享的公众号