天津大學 2015 軟體學院 保研複試 上機——資料庫

標籤：

第一題（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 having 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吧。。。

天津大學 2015 軟體學院 保研複試 上機——資料庫