經(jīng)典SQL50題

? ? ? ? 二刷經(jīng)典SQL面試50題，當(dāng)當(dāng)赊舶，決定將解法重新梳理一遍淤刃。從一刷的磕磕絆絆晒他，自己就是常見錯誤解法，到二刷思維很順暢逸贾，90%可以獨立解出正確答案仪芒，可見那句老話“熟能生巧”！其實SQL語言的邏輯是比較簡單的耕陷，基本的增刪改查可以應(yīng)對工作中80%的內(nèi)容掂名，反而構(gòu)建解決問題的思路，基于業(yè)務(wù)場景理解各表之間的關(guān)系才是重中之重~~~

一哟沫、建表并插入數(shù)據(jù)

經(jīng)典50題的場景比較簡單饺蔑，有四張表：學(xué)生表、學(xué)科表嗜诀、老師表和成績表猾警。

-- 建立學(xué)生表

CREATE TABLE `Student`(

`s_id` VARCHAR(20),

`s_name` VARCHAR(20) NOT NULL DEFAULT '',

`s_birth` VARCHAR(20) NOT NULL DEFAULT '',

`s_sex` VARCHAR(10) NOT NULL DEFAULT '',

PRIMARY KEY(`s_id`));

-- 建立課程表

CREATE TABLE `Course`(

`c_id` VARCHAR(20),

`c_name` VARCHAR(20) NOT NULL DEFAULT '',

`t_id` VARCHAR(20) NOT NULL,

PRIMARY KEY(`c_id`));

-- 建立教師表

CREATE TABLE `Teacher`(

`t_id` VARCHAR(20),

`t_name` VARCHAR(20) NOT NULL DEFAULT '',

PRIMARY KEY(`t_id`));

-- 建立成績表

CREATE TABLE `Score`(

`s_id` VARCHAR(20),

`c_id` VARCHAR(20),

`s_score` INT(3),

PRIMARY KEY(`s_id`,`c_id`));

-- 插入學(xué)生表測試數(shù)據(jù)

insert into Student values('01' , '趙雷' , '1990-01-01' , '男');

insert into Student values('02' , '錢電' , '1990-12-21' , '男');

insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');

insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 課程表測試數(shù)據(jù)

insert into Course values('01' , '語文' , '02');

insert into Course values('02' , '數(shù)學(xué)' , '01');

insert into Course values('03' , '英語' , '03');

-- 教師表測試數(shù)據(jù)

insert into Teacher values('01' , '張三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

-- 成績表測試數(shù)據(jù)

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

select * from Student;

select * from Score;

select * from Teacher;

select * from Course;

二、題目實戰(zhàn)

TIPS：重要的是思路隆敢，可以先在腦海中或者Excel中模擬自己想要的表格是什么效果发皿，再一步一步sql實現(xiàn)！７餍穴墅！


-- 1.查詢課程編號為“01”的課程比“02”的課程成績高的所有學(xué)生的學(xué)號（重點）

select a.s_id as s_no,s_name, a.s_score as '01',b.s_score as '02' from

(select * from Score where c_id ='01') a

join

(select * from Score where c_id ='02') b on a.s_id=b.s_id

join Student c on a.s_id=c.s_id

where a.s_score>b.s_score;

-- 2.查詢平均成績大于60分的學(xué)生的學(xué)號和平均成績（簡單，重點）

TIPS：用group by 分組聚合后温自，查詢的字段不要包含分組與聚合運算之外的字段玄货，是沒意義的。

select s_id,round(avg(s_score),2) as '平均成績'

from Score?

group by s_id

having 平均成績 >60

ORDER BY 平均成績 desc;

-- 3.查詢所有學(xué)生的學(xué)號悼泌、姓名松捉、選課數(shù)、總成績

TIPS：還是group by 中select之后的字段問題馆里，嚴(yán)格來說：既不是統(tǒng)計值隘世，也不是group? by之后的分組條件，是不能出現(xiàn)在select之后的鸠踪。

select a.s_id as '學(xué)號',s_name,count(c_id) as '選課數(shù)',

case

when sum(s_score)? is null then 0

else sum(s_score)?

end as '總成績'

from Student a

left join Score b on a.s_id=b.s_id

group by a.s_id,a.s_name;

-- 4.查詢姓“張”的老師的個數(shù)

select t_name,count(*) from Teacher group by t_name having t_name like '張%';

-- 5.查詢沒學(xué)過“張三”老師課的學(xué)生的學(xué)號丙者、姓名（重點） !!!

-- 自己開始做了好幾次都不對，還要再鞏固慢哈！

TIPS：先找出學(xué)過張三老師課的學(xué)生蔓钟，再用 not in 排除！

①卵贱、select s_id,s_name from Student where s_id not in

(select s_id from Score c where c.c_id=

(select c_id from Course a join Teacher b on a.t_id=b.t_id

where t_name ='張三'));

②滥沫、select s_name from Student where s_name not in (select s_name

from Student a left join Score b on a.s_id = b.s_id left join Course c on b.c_id = c.c_id

left join Teacher d on c.t_id = d.t_id where t_name = '張三'?

group by a.s_id,s_name,t_name);

-- 6.查詢學(xué)過“張三”老師所教的所有課的同學(xué)的學(xué)號侣集、姓名（重點）

select a.s_id,s_name from Student a left join Score b on a.s_id = b.s_id

left join Course c on b.c_id = c.c_id

left join Teacher d on c.t_id = d.t_id

where t_name = '張三' group by a.s_id ,s_name,t_name;

-- 7.查詢學(xué)過編號為“01”的課程并且也學(xué)過編號為“02”的課程的學(xué)生的學(xué)號、姓名（重點）

-- 學(xué)過01課程的同學(xué) 與 學(xué)過02課程的同學(xué)兰绣，取交集

-- 子查詢世分，和第一題的思路相似

select a.s_id,s_name from

(select * from Score where c_id =01) a inner join

(select * from Score where c_id =02) b on a.s_id = b.s_id

left join Student c on a.s_id = c.s_id;

-- 8.查詢課程編號為“02”的總成績（不重點）

select c_id,sum(s_score) as '總成績' from Score where c_id=02 group by c_id;

-- 9.查詢 (所有的課程成績） 小于60分的學(xué)生的學(xué)號、姓名

select? a.s_id, s_name, min(s_score)as '最高分' from Score a

join Student b on a.s_id = b.s_id

group by a.s_id,s_name having max(s_score)<60;

-- 10.查詢沒有學(xué)全所有課的學(xué)生的學(xué)號缀辩、姓名(重點)

-- 子查詢 (select count(distinct c_id) from Course)

select a.s_id,s_name,count(c_id) from Student a left join Score b on a.s_id = b.s_id

group by s_id having count( distinct c_id) !=(select count(distinct c_id) from Course);

-- 11.查詢至少有一門課與學(xué)號為“01”的學(xué)生所學(xué)課程相同的學(xué)生的學(xué)號和姓名（重點）

select a.s_id,s_name from Score a join Student b on a.s_id = b.s_id

where c_id? in

(select c_id from Score where s_id =01)

GROUP BY a.s_id,s_name;

-- 12.查詢和“01”號同學(xué)所學(xué)課程完全相同的其他同學(xué)的學(xué)號(重點) ３袈瘛！臀玄！

TIPS：01號同學(xué)學(xué)了01,02,03三門課

-- ① 選出的課不在01,02,03三門課--排除

-- ② 剩下的同學(xué)肯定選了01,02,03三門課瓢阴，再判斷所學(xué)課程數(shù)=3

select * from Student?

weher s_id in ( select s_id from Score where s_id !=01

group by s_id

having count(distinct c_id) = (select count(distinct c_id ) from Score where s_id =01))

and s_id not in

(select s_id from Score where c_id

not in (select c_id from Score where s_id=01));

-- 15.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績（重點）

select c.s_id,s_name,平均成績,不及格科目數(shù)

from (select a.s_id,s_name,avg(s_score) as '平均成績'

from Student a join? Score b on a.s_id = b.s_id

group by s_id) c

join

(select s_id,count(*) as '不及格科目數(shù)' from Score

where s_score<60 group by s_id having count(*)>=2) d

on c.s_id =d.s_id

-- 16.檢索"01"課程分?jǐn)?shù)小于60健无，按分?jǐn)?shù)降序排列的學(xué)生信息（和34題重復(fù)荣恐，不重點）

select a.s_id,s_name,c_id,s_score

from Score a join Student b on a.s_id = b.s_id

where c_id=01 and s_score <60 order by s_score desc;

-- 17.按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績(重重點與35一樣)

--? 把語文、數(shù)學(xué)累贤、英語各門課程成績橫著排出來叠穆，有點厲害哦~

select s_id as '學(xué)號',avg(s_score)as '平均成績',

max(case when c_id =01 then s_score else null end) as '語文',

max(case when c_id =02 then s_score else null end) as '數(shù)學(xué)',

max(case when c_id =03 then s_score else null end) as '英語'

from Score

group by s_id

order by avg(s_score) desc;

-- 18.查詢各科成績最高分、最低分和平均分?(超級重點)? ＞矢唷Ｅ鸨弧！：

以如下形式顯示：課程ID渗磅，課程name嚷硫，最高分，最低分夺溢，平均分论巍，及格率烛谊，中等率风响，優(yōu)良率，優(yōu)秀率丹禀。及格為>=60状勤，中等為：70-80，優(yōu)良為：80-90双泪，優(yōu)秀為：>=90

select a.c_id as '課程id',c_name as '課程名稱',

avg(s_score) as '平均分',max(s_score) as '最高分',min(s_score ) as '最低分' ,

sum(case when s_score >=60 then 1 else 0 end)/count(*) as '及格率',

sum(case when s_score >=70 and s_score <80 then 1 else 0 end)/count(*) as '中等率',

sum(case when s_score >=80 and s_score <90 then 1 else 0 end)/count(*) as '優(yōu)良率',

sum(case when s_score >=90 then 1 else 0 end)/count(*) as '優(yōu)秀率'

from Score a

join Course b

on a.c_id = b.c_id

group by a.c_id;

-- 19. 按各科成績進(jìn)行排序持搜，并顯示排名 (重點！１好葫盼！) 涉及到窗口函數(shù)，但是我的navicat運行不出來村斟。

select s_id,c_id,s_score,rank() over(order by s_score desc) as 'rank' from window_test

from Score ;

-- 20. 查詢學(xué)生的總成績并進(jìn)行排名（不重點）

select s_id,sum(s_score) from Score group by s_id order by sum(s_score) desc;

-- 21. 查詢不同老師所教不同課程平均分從高到低顯示(不重點)

select t_id,c_name,avg(s_score) from Score a

join Course b on a.c_id = b.c_id group by t_id,c_name

order by avg(s_score) desc;

-- 22. 查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績（重要 25類似）窗口函數(shù)

select * from

(select s_name,c_id,s_score,row number() over(partition by c_id order by s_score desc) as 'm'

from Score a join Student b on a.s_id = b.s_id) c where m in (2,3);

-- 23.使用分段[100-85],[85-70],[70-60],[<60]來統(tǒng)計各科成績贫导，分別統(tǒng)計各分?jǐn)?shù)段人數(shù)：課程ID和課程名稱(重點和18題類似)

-- 也是按條件計數(shù)

select a.c_id as '課程id',c_name as '課程名稱',

sum(case when s_score <60 then 1 else 0 end) as '不及格',

sum(case when s_score >=60 and s_score<70 then 1 else 0 end) as '中等',

sum(case when s_score >=80 and s_score<90 then 1 else 0 end) as '優(yōu)良',

sum(case when s_score >=90 then 1 else 0 end) as '優(yōu)秀'

from Score a join Course b on a.c_id = b.c_id group by a.c_id,c_name;

-- 24抛猫、查詢學(xué)生平均成績及其名次（同19題，重點） 涉及窗口函數(shù)

select s_name,avg(s_score),rank() over(order by avg(s_score) desc) as 'rank'

from Student a join Score b on a.s_id = b.s_id group by s_name;

-- 26孩灯、查詢每門課程被選修的學(xué)生數(shù)(不重點)

select c_id,count(distinct s_id) as '選課人數(shù)' from Score group by c_id;

-- 27闺金、 查詢出只有兩門課程的全部學(xué)生的學(xué)號和姓名(不重點)

select s_id,count(distinct c_id) as '課程數(shù)' from Score group by s_id having 課程數(shù) = 2;

-- 28、查詢男生峰档、女生人數(shù)(不重點)

select s_sex,count(*) from Student group by s_sex;

-- 29 查詢名字中含有"風(fēng)"字的學(xué)生信息（不重點）

select * from Student where s_name like '%風(fēng)%' ;

-- 31败匹、查詢1990年出生的學(xué)生名單（重點year）

select * from Student where year(s_birth) = 1990;

-- 32、查詢平均成績大于等于85的所有學(xué)生的學(xué)號讥巡、姓名和平均成績（不重要）

select a.s_id,s_name,avg(s_score) from Student a join Score b on a.s_id =b.s_id

group by a.s_id,s_name having avg(s_score)>=85;

-- 33掀亩、查詢每門課程的平均成績，結(jié)果按平均成績升序排序欢顷，平均成績相同時归榕，按課程號降序排列（不重要）

select a.c_id,c_name,avg(s_score) from Score a join Course b on a.c_id = b.c_id

group by a.c_id order by avg(s_score)asc,a.c_id desc;

-- 34、查詢課程名稱為"數(shù)學(xué)"吱涉，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)（不重點）

select s_name,s_score from Score a join Course b on a.c_id = b.c_id

join Student c on a.s_id = c.s_id where c_name='數(shù)學(xué)' and s_score <60;

-- 35刹泄、查詢所有學(xué)生的課程及分?jǐn)?shù)情況（重點）

select a.s_id,s_name,

max(case when c_name='語文' then s_score else null end) as '語文',

max(case when c_name='數(shù)學(xué)' then s_score else null end) as '數(shù)學(xué)',

max(case when c_name='英語' then s_score else null end) as '英語'

from Student a left join Score b on a.s_id = b.s_id

join Course c on b.c_id = c.c_id

group by a.s_id,s_name;

-- 36、查詢?nèi)魏我婚T課程成績在70分以上的姓名怎爵、課程名稱和分?jǐn)?shù)（重點）

select s_name,

max(case when c_name='語文' then s_score else null end) as '語文',

max(case when c_name='數(shù)學(xué)' then s_score else null end) as '數(shù)學(xué)',

max(case when c_name='英語' then s_score else null end) as '英語'

from Score a join Student b on a.s_id=b.s_id

join Course c on c.c_id = a.c_id

where s_score>=70

group by s_name;

-- 37特石、查詢不及格的課程并按課程號從大到小排列(不重點)

select a.c_id,s_score from Score a join Course b on a.c_id = b.c_id

where s_score<60 order by a.c_id desc;

-- 38、查詢課程編號為03且課程成績在80分以上的學(xué)生的學(xué)號和姓名（不重要）

select a.s_id,s_name,s_score from Student a join Score b on a.s_id = b.s_id

where b.c_id=03 and s_score >80;

-- 39鳖链、求每門課程的學(xué)生人數(shù)（不重要）

select c_id,count(*) from Score group by c_id;

-- 40姆蘸、查詢選修“張三”老師所授課程的學(xué)生中成績最高的學(xué)生姓名及其成績（重要top）

select s_name,s_score from Score a join Student b on a.s_id =b.s_id

where c_id = (select c_id from Course

where t_id = (select t_id from Teacher where t_name = '張三'))

order by s_score desc limit 0,1;

-- 41.查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號芙委、學(xué)生成績 （重點）

select a.s_id from (select s_id,s_score

from Score a group by s_id,s_score) a group by s_id having count(*) =1;

-- 42逞敷、查詢每門功成績最好的前兩名（同22和25題）

-- 43、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計）灌侣。要求輸出課程號和選修人數(shù)推捐，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同侧啼，按課程號升序排列（不重要）

select c_id,count(distinct s_id) from Score group by c_id

having count(distinct s_id)>5 order by count(s_id) desc,c_id;

-- 44牛柒、檢索至少選修兩門課程的學(xué)生學(xué)號（不重要）

select s_id,count(c_id) from Score group by s_id having count(c_id) >=2;

-- 45、 查詢選修了全部課程的學(xué)生信息

select s_id,count(distinct c_id) from Score group by s_id?

having count(distinct c_id)=(select count(c_id) from Course);

-- 46痊乾、查詢各學(xué)生的年齡（精確到月份）

select s_id,s_name,FLOOR(DATEDIFF(now(),s_birth)/365) as '年齡' from Student;

-- 47皮壁、查詢沒學(xué)過“張三”老師講授的任一門課程的學(xué)生姓名

select s_id,s_name from Student where s_id not in

(select s_id from Score where c_id=

(select c_id from Course where t_id=

(select t_id from Teacher where t_name = '張三')));

-- 48、查詢下周過生日的學(xué)生

select * from Student

where week(concat('2020-',substring(s_birth,6,5)),1)= week('2020-5-15',1)+1;

-- 49哪审、查詢本月過生日的學(xué)生

select * from Student where month(s_birth)=month(now());

-- 50蛾魄、查詢下個月過生日的學(xué)生

select * from Student where

case when month(now())=12 then month(s_birth) =1

else month(s_birth) = month(now()) +1 end;

三、說明

題目來自于：

常見的SQL面試題：經(jīng)典50題 - 知乎（?https://zhuanlan.zhihu.com/p/38354000）

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市滴须，隨后出現(xiàn)的幾起案子缴川，更是在濱河造成了極大的恐慌，老刑警劉巖描馅，帶你破解...
    沈念sama閱讀 212,542評論 6贊 493
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件把夸，死亡現(xiàn)場離奇詭異，居然都是意外死亡铭污，警方通過查閱死者的電腦和手機(jī)恋日，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,596評論 3贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來嘹狞，“玉大人岂膳，你說我怎么就攤上這事“跬” “怎么了谈截？”我有些...
    開封第一講書人閱讀 158,021評論 0贊 348
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長涧偷。 經(jīng)常有香客問我簸喂，道長，這世上最難降的妖魔是什么燎潮？ 我笑而不...
    開封第一講書人閱讀 56,682評論 1贊 284
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任喻鳄，我火速辦了婚禮，結(jié)果婚禮上确封，老公的妹妹穿的比我還像新娘除呵。我一直安慰自己，他們只是感情好爪喘，可當(dāng)我...
    茶點故事閱讀 65,792評論 6贊 386
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布颜曾。 她就那樣靜靜地躺著，像睡著了一般秉剑。 火紅的嫁衣襯著肌膚如雪泛豪。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 49,985評論 1贊 291
  • 城市分裂傳說
    那天秃症，我揣著相機(jī)與錄音候址，去河邊找鬼。 笑死种柑，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的匹耕。 我是一名探鬼主播聚请，決...
    沈念sama閱讀 39,107評論 3贊 410
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了驶赏？” 一聲冷哼從身側(cè)響起炸卑，我...
    開封第一講書人閱讀 37,845評論 0贊 268
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎煤傍，沒想到半個月后盖文，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 44,299評論 1贊 303
  • ?護(hù)林員之死
    正文 獨居荒郊野嶺守林人離奇死亡蚯姆，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,612評論 2贊 327
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年五续，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片龄恋。...
    茶點故事閱讀 38,747評論 1贊 341
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡疙驾，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出郭毕，到底是詐尸還是另有隱情它碎，我是刑警寧澤，帶...
    沈念sama閱讀 34,441評論 4贊 333
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布显押，位于F島的核電站扳肛，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏乘碑。R本人自食惡果不足惜敞峭，卻給世界環(huán)境...
    茶點故事閱讀 40,072評論 3贊 317
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望蝉仇。 院中可真熱鬧旋讹，春花似錦、人聲如沸轿衔。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,828評論 0贊 21
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽害驹。三九已至鞭呕，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間宛官，已是汗流浹背葫松。 一陣腳步聲響...
    開封第一講書人閱讀 32,069評論 1贊 267
  • 情欲美人皮
    我被黑心中介騙來泰國打工， 沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留底洗，地道東北人腋么。 一個月前我還...
    沈念sama閱讀 46,545評論 2贊 362
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像亥揖，于是被迫代替她去往敵國和親珊擂。 傳聞我的和親對象是個殘疾皇子圣勒，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,658評論 2贊 350