001經(jīng)典SQL筆試題題干

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

002查詢各科成績最高和最低的分， 以如下的形式顯示：課程號，最高分，最低分

/*
分析思路
select 查詢結(jié)果 [課程ID：是課程號的別名,最高分：max(成績) ,最低分：min(成績)]
from 從哪張表中查找數(shù)據(jù) [成績表score]
where 查詢條件 [沒有]
group by 分組 [各科成績：也就是每門課程的成績，需要按課程號分組];
*/
select 課程號,max(成績) as 最高分,min(成績) as 最低分from score
group by 課程號;

003查詢平均成績大于60分學(xué)生的學(xué)號和平均成績。

/* 
題目翻譯成大白話：
平均成績：展開來說就是計算每個學(xué)生的平均成績
這里涉及到“每個”就是要分組了
平均成績大于60分，就是對分組結(jié)果指定條件
*/
select 學(xué)號, avg(成績)from score group by 學(xué)號having avg(成績)>60;

004查詢至少選修兩門課程的學(xué)生學(xué)號。

/* 
翻譯成大白話：
第1步，需要先計算出每個學(xué)生選修的課程數(shù)據(jù)，需要按學(xué)號分組
第2步，至少選修兩門課程：也就是每個學(xué)生選修課程數(shù)目>=2，對分組結(jié)果指定條件
*/
select 學(xué)號, count(課程號) as 選修課程數(shù)目from score group by 學(xué)號 having count(課程號)>=2;

005查詢同名同性學(xué)生名單并統(tǒng)計同名人數(shù)。

/* 
翻譯成大白話，問題解析：
1）查找出姓名相同的學(xué)生有誰，每個姓名相同學(xué)生的人數(shù)
查詢結(jié)果：姓名,人數(shù)
條件：怎么算姓名相同？按姓名分組后人數(shù)大于等于2，因為同名的人數(shù)大于等于2
*/
select 姓名,count(*) as 人數(shù) from student group by 姓名
having count(*)>=2;

006查詢每門課程的平均成績，結(jié)果按平均成績升序排序，平均成績相同時，按課程號降序排列。

select 課程號, avg(成績) as 平均成績from score
group by 課程號 order by 平均成績 asc,課程號 desc;

007統(tǒng)計每門課程的學(xué)生選修人數(shù)(超過2人的課程才統(tǒng)計)。

要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排序，若人數(shù)相同，按課程號升序排序。

select 課程號, count(學(xué)號) as '選修人數(shù)'
from score group by 課程號having count(學(xué)號)>2
order by count(學(xué)號) desc,課程號 asc;

008查詢兩門以上不及格課程的同學(xué)的學(xué)號及其平均成績。

/*第1步：得到每個學(xué)生的平均成績，顯示學(xué)號，平均成績*/
select 學(xué)號, avg(成績) as 平均成績 from score group by 學(xué)號;
/* 第2步：再加上限制條件：*/
select 學(xué)號, avg(成績) as 平均成績from score where 成績 <60 group by 學(xué)號 having count(課程號)>=2;