做數據sql不熟練?解析50道經典面試題,sql從入門到進階

明天要面試，十萬火急！！！

數據下午出，火燒眉毛了！！！

阿狗怎麼辦？？？

遇到事情不要慌，打開題庫刷一刷。

給大家整理了流傳已久的50道sql面試經典題目，解題思路和SQL答案一併附上，難度循序漸進，考察點非常全面，自己刷完這些題目後基本可以應對大部分面試題目和工作場景。工作之餘也可以拿出來練練手，對知識點查缺補漏。

題目背景：資料庫中有四張表，全部題目以四張表為基礎，欄位如下：

複習下建表和插入數據的語句（可以自己在navicat中練習）：

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '趙雷' , '1990-01-01' , '男');insert into Student values('02' , '錢電' , '1990-12-21' , '男');insert into Student values('03' , '孫風' , '1990-12-20' , '男');insert into Student values('04' , '李雲' , '1990-12-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吳蘭' , '1992-01-01' , '女');insert into Student values('07' , '鄭竹' , '1989-01-01' , '女');insert into Student values('09' , '張三' , '2017-12-20' , '女');insert into Student values('10' , '李四' , '2017-12-25' , '女');insert into Student values('11' , '李四' , '2012-06-06' , '女');insert into Student values('12' , '趙六' , '2013-06-13' , '女');insert into Student values('13' , '孫七' , '2014-06-01' , '女');
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));insert into Course values('01' , '語文' , '02');insert into Course values('02' , '數學' , '01');insert into Course values('03' , '英語' , '03');
create table Teacher(TId varchar(10),Tname varchar(10));insert into Teacher values('01' , '張三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);
建好表後第一件事要先把幾張表的關聯關係理清楚，然後再來看題目。這是網友做的關係圖：
題目：
1. 查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數
思路：關聯每名學生的兩門課程成績，再選出01課程比02課程高的學生
select s.*, sc1.score, sc2.scorefrom Student sleft join （select SId, score from SC where CId = '01') sc1 on s.SId = sc1.SIdleft join (select SId, score from SC where CId = '02') sc2on s.SId = sc2.SIdwhere sc1.score > sc2.score
2. 查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
思路：在成績表中分組計算平均分，再關聯學生表
select s.*, r.Sname from (select SId, avg(score) as sfrom sc group by SIdhaving avg(scoare) >= 60) rleft join Student st on r.SId = st.SId
3. 查詢在 SC 表存在成績的學生信息
select distinct s.*from Student s, scwhere s.SId = sc.SId
4. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的成績總和
思路：涉及所有同學的所有課程，Student表 join SC表
select s.SId, s.Sname, count(SC.CId), sum(SC.score)from Student sjoin SC on s.SId=  SC.SIdgroup by s.SId, s.Sname
5. 查詢「李」姓老師的數量
select count(*)from Teacher where Tname like '李%'
6. 查詢學過「張三」老師授課的同學的信息
思路：多表聯合查詢
select distinct s.*from Student s, Teacher t, SC, Course cwhere s.SId = SC.SId and SC.CId = c.CId and c.TId = t.Tid and T.Tname = '張三'order by s.SId
7. 查詢沒有學全所有課程的同學的信息
思路：統計學生課程數小於課程表裡的課程數
select s.SId, s.Sname, count(SC.CId)from Student s inner join SC on s.SId=  SC.SIdgroup by s.SId, s.Sname having count(SC.CId) < (select count(distinct CId) from Course)
8. 查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息
思路：找出01同學上過的課程，統計SC表中在這些課程中的SId
select s.*from Student s, SC where s.SId = SC.SId and SC.CId in (select CId from SC where SId = '01')and s.SId <> '01'
9. 查詢和" 01 "號的同學學習的課程完全相同的其他同學的信息（較難）
思路：group_concat函數
select s.*from Student s where s.SId in (select sid from sc where sid<>'01' group by sidhaving group_concat(CId order by CId) = (select group_concat(CId order by CId) from SC where sid = '01'))
10. 查詢沒學過"張三"老師講授的任一門課程的學生姓名
思路：三重嵌套，張三老師的課->上過課的學生->不在這個集合中的學生
select s.* from Student where s.SId not in (select SId from SC where SC.CId in (select c.CIdfrom Teacher t, Course c where t.TId = c.TId and t.Tname = '張三'))
11. 查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
思路：SC表中分組找到兩門及以上不及格的學生，再關聯他的平均成績
select s.SId, s.Sname, avg(SC.Score)from Student s, SC where s.SId = SC.SId and SC.SId in (select SId from SC where score < 60 group by SId having Count(1) >= 2)group by s.SId, s.Sname
12. 檢索" 01 "課程分數小於 60，按分數降序排列的學生信息
select s.SId, s.Sname, SC.score from SC, Student swhere SC.SId = s.SId and SC.score < 60 and SC.CId = '01'order by SC.score desc
13. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
SELECT S.Sid, B.平均成績, SUM(CASE WHEN Cid = '01' THEN Score ELSE NULL END) AS 成績01, SUM(CASE WHEN Cid = '02' THEN Score ELSE NULL END) AS 成績02, SUM(CASE WHEN Cid = '03' THEN Score ELSE NULL END) AS 成績03 FROM SC INNER JOIN (SELECT Sid, AVG(score) AS 平均成績 FROM SC GROUP BY Sid) AS B ON B.Sid = SC.Sid RIGHT OUTER JOIN Student AS S ON S.Sid = SC.Sid GROUP BY S.Sid, B.平均成績 ORDER BY B.平均成績 DESC NULLS LAST
14. 查詢各科成績最高分、最低分和平均分：
以如下形式顯示：
課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率（及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90）
要求：輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列。
select sc.CId ,max(sc.score)as 最高分,min(sc.score)as 最低分,AVG(sc.score)as 平均分,count(*)as 選修人數,sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 優良率,sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 優秀率 from scGROUP BY sc.CIdORDER BY count(*)DESC, sc.CId ASC
15. 按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺
SELECT Sid, Cid, Score, RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS ranking FROM SC
16. 查詢學生的總成績，並進行排名，總分重複時不保留名次空缺
SELECT Sid, SUM(Score), RANK () OVER (ORDER BY SUM(Score) DESC) AS ranking FROM SC GROUP BY Sid
17. 統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比。
        SELECT Cid, COUNT(*) AS 選課人數, SUM(CASE WHEN Score>= 85 THEN 1 ELSE 0 END) * 100 / COUNT(*) AS 分數85_100比例, SUM(CASE WHEN Score BETWEEN  70 AND 85 THEN 1 ELSE 0 END) * 100 / COUNT(*) AS 分數70_85比例, SUM(CASE WHEN Score BETWEEN  60 AND 70 THEN 1 ELSE 0 END) * 100 / COUNT(*) AS 分數60_70比例, SUM(CASE WHEN Score BETWEEN  0 AND 60 THEN 1 ELSE 0 END) * 100 / COUNT(*) AS 分數0_60比例 FROM SC GROUP BY Cid
18. 查詢各科成績前三名的記錄
SELECT *FROM( SELECT Sid, Cid, Score, RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS ranking FROM SC GROUP BY Sid, Cid) AS A WHERE A.ranking IN (1, 2, 3);
19. 查詢每門課程被選修的學生數
select cid, count(sid) from sc group by cid
20. 查詢出只選修兩門課程的學生學號和姓名
select student.sid, student.sname from studentwhere student.sid in(select sc.sid from scgroup by sc.sidhaving count(sc.cid)=2)
21. 查詢男生、女生人數
select Ssex, count(*) from studentgroup by Ssex
22. 查詢名字中含有「風」字的學生信息
select *from student where student.Sname like '%風%'
23. 查詢同名學生名單，並統計同名人數
select sname, count(*) from studentgroup by snamehaving count(*)>1
24. 查詢 1990 年出生的學生名單
select *from studentwhere YEAR(student.Sage)=1990
25. 查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, coursewhere sc.cid = course.cidgroup by sc.cid order by average desc,cid asc
26. 查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績
select student.sid, student.sname, AVG(sc.score) as aver from student, scwhere student.sid = sc.sidgroup by sc.sidhaving aver > 85
27. 查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數
select student.sname, sc.score from student, sc, coursewhere student.sid = sc.sidand course.cid = sc.cidand course.cname = "數學"and sc.score < 60
28. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）
select student.sname, cid, score from studentleft join scon student.sid = sc.sid;
29. 查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數
select student.sname, course.cname,sc.score from student,course,scwhere sc.score>70and student.sid = sc.sidand sc.cid = course.cid
30. 查詢存在不及格的課程
select cid from scwhere score< 60group by cid
31. 查詢課程編號為 01 且課程成績在 80 分及以上的學生的學號和姓名
select student.sid,student.sname from student,scwhere cid= '01'and score>=80and student.sid = sc.sid
32. 求每門課程的學生人數
select sc.CId,count(*) as 學生人數from scGROUP BY sc.CId
33. 成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
select student.*, sc.score, sc.cid from student, teacher, course,sc where teacher.tid = course.tidand sc.sid = student.sidand sc.cid = course.cidand teacher.tname = "張三"order by score desclimit 1
34. 成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
select student.*, sc.score, sc.cid from student, teacher, course,sc where teacher.tid = course.tidand sc.sid = student.sidand sc.cid = course.cidand teacher.tname = '張三'and sc.score = (select Max(sc.score) from sc,student, teacher, coursewhere teacher.tid = course.tidand sc.sid = student.sidand sc.cid = course.cidand teacher.tname = '張三')
35. 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
select  a.cid, a.sid,  a.score from sc as ainner join sc as bon a.sid = b.sidand a.cid != b.cidand a.score = b.scoregroup by cid, sid
36. 查詢每門功成績最好的前兩名
select a.sid,a.cid,a.score from sc as a left join sc as b on a.cid = b.cid and a.score<b.scoregroup by a.cid, a.sidhaving count(b.cid)<2order by a.cid
37. 統計每門課程的學生選修人數（超過 5 人的課程才統計）
select sc.cid, count(sid) as cc from scgroup by cidhaving cc > 5
38. 檢索至少選修兩門課程的學生學號
select sid, count(cid) as cc from scgroup by sidhaving cc>=2
39. 查詢選修了全部課程的學生信息
select student.*from sc ,student where sc.SId=student.SIdGROUP BY sc.SIdHAVING count(*) = (select DISTINCT count(*) from course )
40. 查詢各學生的年齡，只按年份來算
SELECT Sid, Sname, Sage,EXTRACT(YEAR FROM CURRENT_TIMESTAMP) - EXTRACT(YEAR FROM Sage) AS 年齡FROM Student
41. 接上一題按照出生日期來算，當前月日 < 出生年月的月日，則年齡減一
select student.SId as 學生編號,student.Sname  as  學生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 學生年齡from student
42. 查詢本周過生日的學生
select *from student where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())
43. 查詢下周過生日的學生
select *from student where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1
44. 查詢本月過生日的學生
select *from student where MONTH(student.Sage)=MONTH(CURDATE())
45. 查詢下月過生日的學生
select *from student where MONTH(student.Sage)=MONTH(CURDATE())+1
46. 查詢同時存在" 01 「課程和」 02 "課程的情況
SELECT C1.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1, C2.score AS score2 FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 INNER JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 ON C1.sid = C2.sid INNER JOIN Student AS C3 ON C1.sid = C3.sid WHERE C1.score <> C2.score OR C1.score = C2.score
47. 查詢存在" 01 「課程但可能不存在」 02 「課程的情況 (不存在時顯示為null)
SELECT C1.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1, COALESCE(CAST(C2.score AS VARCHAR(4)), 'NULL') AS score2 FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 LEFT JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 ON C1.sid = C2.sid INNER JOIN Student AS C3 ON C1.sid = C3.sid
48. 查詢不存在" 01 「課程但存在」 02 "課程的情況
SELECT C2.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1, C2.score AS score2 FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 RIGHT JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 ON C1.sid = C2.sid INNER JOIN Student AS C3 ON C2.sid = C3.sid WHERE C1.score IS NULL
49. 查有成績的學生信息
select * from studentwhere student.sid in (select sc.sid from sc)
50. 按各科成績進行排序，並顯示排名， Score 重複時合併名次
SELECT Sid, Cid, Score, DENSE_RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS dense_ranking FROM SC
針對這50道經典題目，有B站up主專門製作了講解視頻，連結也一起附上：
https://www.bilibili.com/video/BV1q4411G7Lw/?p=4
喜歡的同學記得點下【收藏】和【在看】哦！