保研复试上机——数据库

第一题（20分）数据库, 本题直接将答案写在试卷上

有如下三个表：

表一：SC(sid,cid,score) 成绩表

   sid：学号；cid,课程编号；score：成绩 (成绩缺省值为0）

表二：Teacher(tid,Tname) 教师表

   tid：教师编号； Tname：教师名字

表三：Course(cid,cname) 课程表

请写出如下的SQL语句：

  测试数据： score 表

 

测试数据：course表

(1).查询学生平均成绩及其名次

mysql> select temp.sid,temp.avg,@rank := @rank + 1 as rank from (select sid,avg(score) as avg from score group by sid order by avg desc) as temp,(select @rank:=0) as r;

 

(2).统计并列印各科成绩,各分数段人数，查询结果包括如下列：

课程ID，课程名称，[100-85]人数，[85-70]人数，[70-60]人数，[ <60]人

select temp1.cid as '课程ID',c.cname as '课程名称',IFNULL(temp2.num,0) as '[100-85]',IFNULL(temp3.num,0) as '[85-70]', IFNULL(temp4.num,0) as '[70-60]',IFNULL(temp5.num,0) as '[<60]' from (select distinct cid from score) as temp1 left join course c on c.cid = temp1.cid left join (select cid, count(*) as num from score where score between 85 and 100 group by cid) as temp2 on temp1.cid = temp2.cid left join (select cid,count(*) as num from score where score between 70 and 84 group by cid) as temp3 on temp3.cid = temp1.cid left join (select cid,count(*) as num from score where score between 60 and 69 group by cid) as temp4 on temp4.cid = temp1.cid left join (select cid,count(*) as num from score where score < 60) as temp5 on temp5.cid = temp1.cid order by temp1.cid asc;

sql 执行过程：

(3).查询两门以上不及格课程的同学的学号及其平均成绩

 

mysql> select s.sid,avg(s.score) from score s where s.sid in (select temp.sid from (select s2.sid ,count(*) as num from score s2 where s2.score < 60 group by s2.sid hav
ing num >= 2) as temp) group by s.sid;

 

 执行过程：

 或者

mysql> select s.sid ,avg(s.score) from score s,(select s2.sid,count(*) as num from score s2 where s2.score < 60 group by s2.sid having num >= 2) as temp where s.sid = temp.sid group by s.sid;

 

执行过程：

原谅我，写这么差的sql吧。。。

转载于:https://www.cnblogs.com/1995hxt/p/5874168.html