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

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

002查詢(xún)所有課程成績(jī)小于60分學(xué)生的學(xué)號(hào)、姓名。

/*
第1步，寫(xiě)子查詢(xún)（所有課程成績(jī) < 60 的學(xué)生）*/
select 學(xué)號(hào)  from score where 成績(jī) < 60;
/*第2步，查詢(xún)結(jié)果：學(xué)生學(xué)號(hào)，姓名，條件是前面1步查到的學(xué)號(hào)*/
select 學(xué)號(hào),姓名 from student where  學(xué)號(hào) in ( select 學(xué)號(hào) 
from score where 成績(jī) < 60);

003查詢(xún)沒(méi)有學(xué)全所有課的學(xué)生的學(xué)號(hào)、姓名。

/*
查找出學(xué)號(hào)，條件：沒(méi)有學(xué)全所有課，也就是該學(xué)生選修的課程數(shù) < 總的課程數(shù)
【考察知識(shí)點(diǎn)】in，子查詢(xún)
*/
select 學(xué)號(hào),姓名from student where 學(xué)號(hào) in( select 學(xué)號(hào) from score
group by 學(xué)號(hào) having count(課程號(hào)) < (select count(課程號(hào)) from course));

004查詢(xún)出只選修了兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名。

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

005按課程號(hào)分組取成績(jī)最大值所在行的數(shù)據(jù)。

/*我們可以使用分組（group by）和匯總函數(shù)得到每個(gè)組里的一個(gè)值（最大值，最小值，平均值等）。
但是無(wú)法得到成績(jī)最大值所在行的數(shù)據(jù)。*/
select 課程號(hào),max(成績(jī)) as 最大成績(jī) from score group by 課程號(hào);
/*我們可以使用關(guān)聯(lián)子查詢(xún)來(lái)實(shí)現(xiàn)：*/
select * from score as a where 成績(jī) = (select max(成績(jī)) 
from score as b where b.課程號(hào) = a.課程號(hào));
/*上面查詢(xún)結(jié)果課程號(hào)“0001”有2行數(shù)據(jù)，是因?yàn)樽畲蟪煽?jī)80有2個(gè)
分組取每組最小值：按課程號(hào)分組取成績(jī)最小值所在行的數(shù)據(jù)*/
select * from score as a where 成績(jī) = (select min(成績(jī)) 
from score as b where b.課程號(hào) = a.課程號(hào));

006查詢(xún)各科成績(jī)前兩名的記錄。

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

007查詢(xún)所有學(xué)生的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)。

select a.學(xué)號(hào),a.姓名,count(b.課程號(hào)) as 選課數(shù),sum(b.成績(jī)) as 總成績(jī)
from student as a left join score as b on a.學(xué)號(hào) = b.學(xué)號(hào)group by a.學(xué)號(hào);

008查詢(xún)平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)。

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

009查詢(xún)學(xué)生的選課情況：學(xué)號(hào)，姓名，課程號(hào)，課程名稱(chēng)。

select a.學(xué)號(hào), a.姓名, c.課程號(hào),c.課程名稱(chēng)
from student a inner join score b on a.學(xué)號(hào)=b.學(xué)號(hào)
inner join course c on b.課程號(hào)=c.課程號(hào);

010查詢(xún)課程編號(hào)為0003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名。

select a.學(xué)號(hào),a.姓名
from student  as a inner join score as b on a.學(xué)號(hào)=b.學(xué)號(hào)
where b.課程號(hào)='0003' and b.成績(jī)>80;