001經典SQL筆試題題干

學生表：student(學號sno,學生姓名sname,出生年月sbirth,性別ssex)
成績表：score(學號sno,課程號cno,成績score)
課程表：course(課程號cno,課程名稱cname,教師號ctno)
教師表：teacher(教師號tno,教師姓名tname)
注意：下面SQL的實現以MySQL為主

002查詢所有課程成績小于60分學生的學號、姓名。

/*
第1步，寫子查詢（所有課程成績 < 60 的學生）*/
select 學號  from score where 成績 < 60;
/*第2步，查詢結果：學生學號，姓名，條件是前面1步查到的學號*/
select 學號,姓名 from student where  學號 in ( select 學號 
from score where 成績 < 60);

003查詢沒有學全所有課的學生的學號、姓名。

/*
查找出學號，條件：沒有學全所有課，也就是該學生選修的課程數 < 總的課程數
【考察知識點】in，子查詢
*/
select 學號,姓名from student where 學號 in( select 學號 from score
group by 學號 having count(課程號) < (select count(課程號) from course));

004查詢出只選修了兩門課程的全部學生的學號和姓名。

select 學號,姓名from student where 學號 in( select 學號 from score
group by 學號having count(課程號)=2);

005按課程號分組取成績最大值所在行的數據。

/*我們可以使用分組（group by）和匯總函數得到每個組里的一個值（最大值，最小值，平均值等）。
但是無法得到成績最大值所在行的數據。*/
select 課程號,max(成績) as 最大成績 from score group by 課程號;
/*我們可以使用關聯子查詢來實現：*/
select * from score as a where 成績 = (select max(成績) 
from score as b where b.課程號 = a.課程號);
/*上面查詢結果課程號“0001”有2行數據，是因為最大成績80有2個
分組取每組最小值：按課程號分組取成績最小值所在行的數據*/
select * from score as a where 成績 = (select min(成績) 
from score as b where b.課程號 = a.課程號);

006查詢各科成績前兩名的記錄。

/*第1步，查出有哪些組，我們可以按課程號分組，查詢出有哪些組，對應這個問題里就是有哪些課程號*/
select 課程號,max(成績) as 最大成績from score group by 課程號;
/*第2步：先使用order by子句按成績降序排序（desc），然后使用limt子句返回topN（對應這個問題返回的成績前兩名*/
select * from score where 課程號 = '0001' order by 成績 ?desc?limit 2;
/*第3步，使用union all 將每組選出的數據合并到一起.同樣的，可以寫出其他組的（其他課程號）取出成績前2名的sql*/
(select * from score where 課程號 = '0001' order by 成績 ?desc limit 2) union all
(select * from score where 課程號 = '0002' order by 成績 ?desc limit 2) union all
(select * from score where 課程號 = '0003' order by 成績 ?desc limit 2);

007查詢所有學生的學號、姓名、選課數、總成績。

select a.學號,a.姓名,count(b.課程號) as 選課數,sum(b.成績) as 總成績
from student as a left join score as b on a.學號 = b.學號group by a.學號;

008查詢平均成績大于85的所有學生的學號、姓名和平均成績。

select a.學號,a.姓名, avg(b.成績) as 平均成績
from student as a left join score as b
on a.學號 = b.學號group by a.學號having avg(b.成績)>85;

009查詢學生的選課情況：學號，姓名，課程號，課程名稱。

select a.學號, a.姓名, c.課程號,c.課程名稱
from student a inner join score b on a.學號=b.學號
inner join course c on b.課程號=c.課程號;

010查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名。

select a.學號,a.姓名
from student  as a inner join score as b on a.學號=b.學號
where b.課程號='0003' and b.成績>80;