SQL面试50题
?
1.查詢課程編號(hào)為“01”的課程比“02”的課程成績高的所有學(xué)生的學(xué)號(hào)(重點(diǎn)) SELECT a.s_id,a.s_score FROM (SELECT * FROM score WHERE c_id='01') as a INNER JOIN (SELECT * FROM score WHERE c_id='02') as b on a.s_id=b.s_id WHERE a.s_score>b.s_score; View Code 15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(重點(diǎn)) SELECT a.s_id,a.s_name,avg(s_score) FROM student as a INNER JOIN score as b ON a.s_id=b.s_idWHERE a.s_id IN( SELECT s_id FROM score WHERE s_score<60 GROUP BY s_id HAVING COUNT(DISTINCT c_id)>=2) GROUP BY a.s_id,s_name View Code 16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息 # 方法一：個(gè)人 SELECT * FROM student INNER JOIN (SELECT s_id,s_score FROM score WHERE c_id=01 AND s_score<60) as t on student.s_id=t.s_id ORDER BY s_score DESC;# 方法二：個(gè)人 SELECT * FROM student INNER JOIN score ON student.s_id=score.s_idWHERE s_score<60 AND c_id=01 ORDER BY s_score DESC;# 方法三： SELECT * FROM student as t INNER JOIN score as s on t.s_id=s.s_id WHERE s.c_id=01 AND s.s_score<60 ORDER BY s.s_score DESC ; View Code?17、按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績(重重點(diǎn)與35一樣)
SELECT s_id "學(xué)號(hào)", max(CASE WHEN c_id=01 THEN s_score ELSE NULL END ) "語文", max(CASE WHEN c_id=02 THEN s_score ELSE NULL END ) "數(shù)學(xué)", max(CASE WHEN c_id=03 THEN s_score ELSE NULL END ) "英語", avg(s_score) "平均成績" FROM score GROUP BY s_id ORDER BY avg(s_score) DESC View Code18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
SELECT s.c_id "課程ID",c.c_name "課程名字", max(s.s_score) "最高分", min(s.s_score)"最低分", avg(s.s_score)"平均分", sum(CASE WHEN s.s_score>=60 THEN 1 ELSE 0 END )/count(s_id) "及格", sum(CASE WHEN s.s_score>=70 AND s.s_score<80 THEN 1 ELSE 0 END )/count(s_id) "中等", sum(CASE WHEN s.s_score>=80 AND s.s_score<90 THEN 1 ELSE 0 END )/count(s_id)"優(yōu)良", sum(CASE WHEN s.s_score>=90 THEN 1 ELSE 0 END )/count(s_id) "優(yōu)秀" FROM course AS c INNER JOIN score as s ON c.c_id=s.c_id GROUP BY c.c_id View Code SELECT c_id,avg(CASE WHEN s_score >= 0 AND s_score < 60 THEN 1.0 ELSE 0.0 END) "及格率",avg(CASE WHEN s_score >= 60 AND s_score < 70 THEN 1.0 ELSE 0.0 END) "中等率",avg(CASE WHEN s_score >= 70 AND s_score < 85 THEN 1.0 ELSE 0.0 END) "良好率",avg(CASE WHEN s_score >= 85 AND s_score < 100 THEN 1.0 ELSE 0.0 END) "優(yōu)秀率" FROM score GROUP BY c_id; View Code19、按各科成績進(jìn)行排序，并顯示排名(重點(diǎn)row_number)
窗口函數(shù)可以進(jìn)行排序，生成序列號(hào)等一般的聚合函數(shù)無法實(shí)現(xiàn)的高級(jí)操作。
窗口函數(shù)大體可以分為以下兩種：
1.能夠作為窗口函數(shù)的聚合函數(shù)(sum，avg，count，max，min)
2.rank，dense_rank，row_number等專用窗口函數(shù)。
語法的基本使用方法：使用rank函數(shù)
rank函數(shù)是用來計(jì)算記錄排序的函數(shù)
https://blog.csdn.net/qq_41805514/article/details/81772182
專用函數(shù)的種類：1.rank函數(shù)：計(jì)算排序時(shí)，如果存在相同位次的記錄，則會(huì)跳過之后的位次。
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?2.dense_rank函數(shù)：同樣是計(jì)算排序，即使存在相同位次的記錄，也不會(huì)跳過之后的位次。
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?3.row_number函數(shù)：賦予唯一的連續(xù)位次。
??23、使用分段[100-85],[85-70],[70-60],[<60]來統(tǒng)計(jì)各科成績，分別統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù)：課程ID和課程名稱(重點(diǎn)和18題類似)
SELECT c.c_id "課程ID",c_name "課程名字", sum(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE 0 END ) AS "[100,85]", sum(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE 0 END ) "[85,70]", sum(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE 0 END )"[70,60]", sum(CASE WHEN s_score<=60 THEN 1 ELSE 0 END )"<60"FROM score as s INNER JOIN course as c on s.c_id=c.c_id GROUP BY c.c_id,c.c_name; View Code SELECT c.c_id "課程ID",c_name "課程名字", count(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE NULL END ) AS "[100,85]", count(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE NULL END ) "[85,70]", count(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE NULL END )"[70,60]", count(CASE WHEN s_score<=60 THEN 1 ELSE NULL END )"<60"FROM score as s INNER JOIN course as c on s.c_id=c.c_id GROUP BY c.c_id,c.c_name; View Code 24、查詢學(xué)生平均成績及其名次(同19題，重點(diǎn)) SELECT s_id,avg(s_score),rank() OVER (ORDER BY avg(s_score)) as ranking FROM score group by s_id# 不可加partition by，需使用group by View Code 25、查詢各科成績前三名的記錄(不考慮成績并列情況)(重點(diǎn) 與22題類似) 26、查詢每門課程被選修的學(xué)生數(shù)(不重點(diǎn)) 26、查詢每門課程被選修的學(xué)生數(shù)(不重點(diǎn)) SELECT c_name,COUNT(c.c_id) FROM score as s INNER JOIN course as c on s.c_id=c.c_id GROUP BY c_name;# 注：一般需要將select后的字段放在group by后面SELECT c.c_id,c.c_name,count(DISTINCT s.s_id) AS "數(shù)量" FROM score as s INNER JOIN course as c on s.c_id=c.c_id GROUP BY c.c_id,c.c_name; View Code 27、 查詢出只有兩門課程的全部學(xué)生的學(xué)號(hào)和姓名(不重點(diǎn)) # 方法一： SELECT s.s_id,s.s_name FROM student AS s INNER JOINscore as sc on s.s_id=sc.s_id GROUP BY s.s_id HAVING count(DISTINCT c_id)=2;# 錯(cuò)誤 SELECT s.s_name,s.s_id FROM student AS s INNER JOINscore as sc on s.s_id=sc.s_id WHERE count(DISTINCT c_id)=2;# 方法二： SELECT s_id,s_name FROM student WHERE s_id in(SELECT s_id FROM score GROUP BY s_idHAVING count(DISTINCT c_id)=2); View Code 28、查詢男生、女生人數(shù)(不重點(diǎn)) # 方法一： SELECT s_sex,count(s_sex) FROM student GROUP BY s_sex; # 方法二： SELECT sum(CASE WHEN s_sex='男' THEN 1 ELSE 0 END ) "男生人數(shù)", sum(CASE WHEN s_sex='女' THEN 1 ELSE 0 END ) "女生人數(shù)" FROM student # 方法三： SELECT count(CASE WHEN s_sex='男' THEN 1 ELSE NULL END ) "男生人數(shù)", count(CASE WHEN s_sex='女' THEN 1 ELSE NULL END ) "女生人數(shù)" FROM student# 注：null對(duì)count來說是不計(jì)算個(gè)數(shù)的，所以后面不能寫0，只能用null View Code 35、查詢所有學(xué)生的課程及分?jǐn)?shù)情況(重點(diǎn)) # 不對(duì) SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student as s INNER JOIN score as sc ON s.s_id=sc.s_id INNER JOIN course as c ON c.c_id=sc.c_id GROUP BY s.s_id,s.s_name# 正確做法：(行轉(zhuǎn)列) SELECT s.s_id,s.s_name, max(CASE WHEN c.c_name="語文" THEN s_score ELSE NULL END ) AS "語文", max(CASE WHEN c.c_name="數(shù)學(xué)" THEN s_score ELSE NULL END ) AS "數(shù)學(xué)", max(CASE WHEN c.c_name="英語" THEN s_score ELSE NULL END ) AS "英語" FROM student as s LEFT JOIN score as sc ON s.s_id=sc.s_id LEFT JOIN course as c ON c.c_id=sc.c_id GROUP BY s.s_id,s.s_name View Code?
，?36、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分?jǐn)?shù)(重點(diǎn))
# 錯(cuò)誤處理方法： SELECT s.s_name,c.c_name,sc.s_score FROM student as s INNER JOIN score as sc on s.s_id=sc.s_id INNER JOIN course AS c ON c.c_id=sc.c_id GROUP BY s.s_id HAVING sc.s_score>70;# 正確處理方法： SELECT s.s_name,c.c_name,sc.s_score FROM student as s INNER JOIN score as sc on s.s_id=sc.s_id INNER JOIN course AS c ON c.c_id=sc.c_id WHERE sc.s_score>70; # 注：where 后面要跟的是數(shù)據(jù)表里的字段，where針對(duì)數(shù)據(jù)庫文件的發(fā)揮作用， # 而having只是根據(jù)前面查詢出來的結(jié)果集再次進(jìn)行查詢，因此having是針對(duì)結(jié)果集發(fā)揮作用。 View Code 40、查詢選修“張三”老師所授課程的學(xué)生中成績最高的學(xué)生姓名及其成績(重要top) ? SELECT s.s_id,s.s_name,c.c_name,c.c_id,sc.s_score FROM student as s INNER JOIN score as sc ON s.s_id=sc.s_id INNER JOIN course as c ON sc.c_id=c.c_id INNER JOIN teacher as t ON t.t_id=c.t_id WHERE t.t_name="張三" ORDER BY sc.s_score DESC limit 0,1; # 0表示從0開始取，若為降序排列則0是第一位，1表示取幾條# SQL SERVER 中用top /* SELECT top 1 s.s_id,s.s_name,c.c_name,c.c_id FROM student as s INNER JOIN score as sc ON s.s_id=sc.s_id INNER JOIN course as c ON sc.c_id=c.c_id INNER JOIN teacher as t ON t.t_id=c.t_id WHERE t.t_name="張三" ORDER BY DESC */ View Code總結(jié)
