常見的SQL筆試題和面試題（上）：經典50題

來自專欄猴子聊數據分析

先自己做一遍，然後再看對應的答案，這樣效果更好。

已知有如下4張表：

學生表：STUDENT(S#,SNAME,SAGE,SSEX)

課程表：COURSE(C#,CNAME,T#)

成績表：SC(S#,C#,SCORE)

教師表：TEACHER(T#,TNAME)

其中，

1）學生表裡的欄位含義：

S#代表學號，SNAME代表學生姓名，SAGE代表學生年齡，SSEX代表學生性別

2）課程表裡的欄位含義：

C#代表課程編號，CNAME代表課程名字，T#代表教師編號，

3）成績表

S#代表學號,C#代表課程編號,SCORE代表成績

4）教師表的欄位含義：

T#代表教師編號，TNAME代表教師姓名

猴子

根據以上信息按照下面要求寫出對應的SQL語句。

ps：這些題考察SQL的編寫能力，對於這類型的題目，需要你先把4張表之間的關聯關係搞清楚了，最好的辦法是自己在草稿紙上畫關聯圖，然後再編寫對應的SQL語句就比較容易了。下圖是我在草稿紙上畫的這4張表的關係圖，不好理解，你可以列舉一些數據案例來輔助理解：

如果遇到不會的，就回到這裡進入系統的sql複習：如何學習 SQL 語言？

1、查詢課程編號為「001」的課程比「002」的課程成績高的所有學生的學號。

【解題思路】首先查詢課程編號分別為001和002的所有學生的學號及其分數作為內嵌視圖A和B，然後將A和B通過學號關聯，過濾條件就是A的分數大於B的分數，最終SQL如下：

SELECTA.S#FROM(SELECTS#,SCOREFROMSCWHEREC#=001)A,(SELECTS#,SCOREFROMSCWHEREC#=002)BWHEREA.SCORE>B.SCOREANDA.S#=B.S#;

2、查詢平均成績大於60分的學生的學號和平均成績

【解題思路】通過SC表即可獲取，按照SC表的S#分組後即可求出平均成績，最後通過HAVING子句來過濾平均分大於60的學生

select S#, avg(score) from sc group by S# having avg(score)>60;

3、查詢所有學生的學號、姓名、選課數、總成績

【解題思路】學生姓名通過STUDENT表獲取，成績通過SC表獲取，考察的是COUNT和GROUPBY函數，最終SQL如下：

SELECTSTUDENT.S#,STUDENT.SNAME,COUNT(SC.C#),SUM(SCORE)FROMSTUDENTLEFTOUTERJOINSCONSTUDENT.S#=SC.S#GROUPBYSTUDENT.S#,SNAME;

4、查詢姓「猴」的老師的個數

【解題思路】考察模糊查詢

select count(T#)from teacher where tname like 猴%;

5、查詢沒學過「猴子」老師課的學生的學號、姓名

【解題思路】首先查詢學習過「猴子」老師課的學生的學號作為子查詢，而「猴子」老師涉及到TEACHER表，TEACHER表要和學生有關聯必須通過課程和成績表。

select student.S#, student.Sname from Student where S# not in (select distinct(SC.S#) from SC,Course,Teacher where sc.C#=course.C# AND teacher.T#=course.T# AND Teahcer.Tname =猴子);

6、查詢學過「猴子」老師所教的所有課的同學的學號、姓名

【解題思路】這道題和第6題比較相似，需要理解題意，題目說的是查詢學過「猴子」老師所教的所有課的同學的學號、姓名，舉個例子，比如「猴子」老師教過語文和數學，那麼就得找出哪些同學同時學習了語文和數學這2門課程。

select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=猴子 group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=猴子));

7、查詢學過編號為「001」的課程並且也學過編號為「002」的課程的學生的學號、姓名

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=001and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=002);

8、查詢課程編號為「002」的總成績

【解題思路】考查SUM函數

select sum(score) from SC where C#=002;

9、查詢所有課程成績小於60分的學生的學號、姓名

select S#, sname from student where s# not in (select student.s# from student, sc where s.s# = sc.s# and score>60);

10、查詢沒有學全所有課的學生的學號、姓名

【解題思路】也就是學生學習的課程數小於總的課程數

select student.s#, student.sname from student, scwhere student.s#=sc.s# group by student.s#, student.sname having count(c#)<(select count(c#) from course);

11、查詢至少有一門課與學號為「1001」的學生所學課程相同的學生的學號和姓名

【解題思路】首先找出學號為1001的學生學習過的課程，然後根據這些課程號就可以找到有哪些學生學習過部分1001學生學習過的課程

select s#, Sname from Student, SC where student.s# = sc.s# and c# in (select c# from SC where s#=1001);

12、：查詢和「1001」號同學所學課程完全相同的其他同學的學號

【解題思路】首先找出學號為1001的學生學習過的課程，然後根據這些課程號和所學課程總數就可以找到有哪些同學學習過和他一樣的課程

select sno from sc where c# in(select c# from sc from sc where sno=1001)group by sno having count(*)=(select count(*) from sc where sno=1001)

13、把「SC」表中「猴子」老師教的課的成績都更改為此課程的平均成績

【解題思路】考察資料庫更改操作。首先找到李多多老師教過哪些課程及其課程的平均成績，然後根據課程號關聯成績表進行更新

Update Sc Set Score=(Select Avg(s2_Score) From sc s2 Where s2.c#=sc.c#) Where c# IN(Select c# From sc cs INNER JOIN Teacher tc ON cs.t#=tc.t# WHERE tname =猴子);

14、查詢和「1002」號的同學學習的課程完全相同的其他同學學號和姓名

select s# from sc where c# in (select c# from sc where s#=1002) group by s# having count(*)=(select count(*) from sc where s#=1002);

15、刪除學習「猴子」老師課的SC表記錄

【解題思路】

delect sc from course, Teacher where course.c#=sc.c# and course.t#=teacher.t# and tname=猴子;

16、向SC表中插入一些記錄這些記錄要求符合以下條件：沒有上過編號為「003」課程的學生的學號、編號為002的課程的平均成績

Insert SC select S#,002,(Select avg(score) from SC where C#=002) from Student where S# not in (Select S# from SC where C#=002);

17、按平均成績從高到低顯示所有學生的「資料庫」、「企業管理」、「英語」三門的課程成績，按如下形式顯示：學生ID，資料庫，企業管理，英語，有效課程數，有效平均分

select s# as 學生ID,(select score from sc where sc.s#=t.s# and c#=004) as 資料庫,(select score from sc where sc.s#=t.s# and c#=001) as 企業管理,(select score from sc where sc.s#=t.s# and c#=006) as 英語,count(*) as 有效課程數, avg(t.score) as 平局成績from sc as tgroup by s# order by avg(t.score)

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

select L.c# as 課程ID, L.score as 最高分,R.score as 最低分from sc L, sc R where L.c# = R.c# and L.score = (select max(IL.score) from sc IL, student as IM where L.c#=IL.c# and IM.s#=IL.s# group by IL.c#)and R.score = (select min(IR.score) from sc as IR where R.c#=IR.c# group by IR.c#);

19、按各科平均成績從低到高和及格率的百分數從高到低排列，以如下形式顯示：

課程號課程名平均成績及格百分數

SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數 FROM SC T,Course where t.C#=course.C# GROUP BY t.C# ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC;

20、查詢如下課程平均成績和及格率的百分數（用1行顯示），其中企業管理為001，馬克思為002，UML為003，資料庫為004

這個不做

21、查詢不同老師所教不同課程平均分從高到低顯示

SELECT max(Z.T#) AS 教師ID, MAX(Z.Tname) AS 教師姓名, C.C# AS 課程ID, AVG(Score) AS 平均成績 FROM SC AS T,Course AS C ,Teacher AS Z where T.C#=C.C# and C.T#=Z.T# GROUP BY C.C# ORDER BY AVG(Score) DESC;

22、查詢如下課程成績第3名到第6名的學生成績單，其中企業管理為001，馬克思為002，UML為003，資料庫為004，以如下形式顯示：

學生ID學生姓名企業管理馬克思UML資料庫平均成績

這個不做

23、使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計各分數段人數：課程ID和課程名稱

SELECT SC.C# as 課程ID, Cname as 課程名稱,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70],SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60],SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] FROM SC,Course where SC.C#=Course.C# GROUP BY SC.C#,Cname;

24、查詢學生平均成績及其名次

SELECT 1+(SELECT COUNT( distinct 平均成績) FROM (SELECT S#,AVG(score) AS 平均成績 FROM SC GROUP BY S# ) AS T1 WHERE 平均成績 > T2.平均成績) as 名次, S# as 學生學號,平均成績 FROM (SELECT S#,AVG(score) 平均成績 FROM SC GROUP BY S# ) AS T2 ORDER BY 平均成績 desc;

25、查詢各科成績前三名的記錄（不考慮成績並列情況）

SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數 FROM SC t1 WHERE score IN (SELECT TOP 3 score FROM SC WHERE t1.C#= C# ORDER BY score DESC) ;

26、查詢每門課程被選修的學生數

select c#, count(s#) from sc group by c#;

27、查詢出只選修了一門課程的全部學生的學號和姓名

select sc.s#, student.sname, count(c#) as 選課數from sc,student where sc.s# =student.s# group by sc.s#,Student.sname having count(c#)=1;

28、查詢男生、女生人數

select count(Ssex) as 男生人數 from student group by Ssex having Ssex=男；select count(Ssex) as 女生人數 from student group by Ssex having Ssex=女;

29、查詢姓「張」的學生名單

select sname from student where sname like 張%;

30、查詢同名同性學生名單並統計同名人數

select sanme,count(*) from student group by sname havang count(*)>1;

31、1981年出生的學生名單（註：student表中sage列的類型是datetime）

select sname, convert(char(11),DATEPART(year,sage)) as agefrom student where convert(char(11),DATEPART(year,Sage))=1981;

32、查詢平均成績大於85的所有學生的學號、姓名和平均成績

select Sname,SC.S# ,avg(score) from Student,SC where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;

33、查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列

select C#, avg(score) from sc group by c# order by avg(score), c# desc;

34、查詢課程名稱為「資料庫」且分數低於60的學生姓名和分數

select sname, isnull(score,0) from student, sc ,course where sc.s#=student.s# and sc.c#=course.c# and course.cname=資料庫 and score<60;

35、查詢所有學生的選課情況

select sc.s#,sc.c#,sname,cname from sc,student course where sc.s#=student.s# and sc.c#=course.c#;

36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數

select distinct student.s#,student.sname,sc.c#,sc.score from student,sc where sc.score>=70 and sc.s#=student.s#;

37、查詢不及格的課程並按課程號從大到小排列

select c# from sc where score<60 order by c#;

38、查詢課程編號為003且課程成績在80分以上的學生的學號和姓名

select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#=003;

39、查詢選了課程的學生人數

select count(*) from sc;

40、查詢選修「猴子」老師所授課程的學生中成績最高的學生姓名及其成績

select student.sname,score from student,sc,course c, teacher where student.s#=sc.S# and sc.c#=c.c#and c.T#=teacher.T#and teacher.tname=猴子 and sc.score=(select max(score) from sc where c#=c.c#);

41、查詢各個課程及相應的選修人數

select count(*) from sc group by c#;

42、查詢有2門不同課程成績相同的學生的學號、課程號、學生成績

select distinct a.s#,b.score from sc a ,sc b where a.score=b.score and a.c#<>b.c#;

43、查詢每門課程成績最好的前兩名

select t1.s# as 學生ID,t1.c# 課程ID, Score as 分數from sc t1 where score in (select top 2 score from sc where t1.c#=c# order by score desc)order by t1.c#;

44、統計每門課程的學生選修人數(超過10人的課程才統計)。要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序

select c# as 課程號,count(*) as 人數from sc group by c#order by count(*) desc c#;

45、查詢至少選修兩門課程的學生學號

select s# from sc group by s# having count(*)>=2;

46、查詢全部學生都選修的課程的課程號和課程名

select c# ,cnamefrom course where c# in (select c# from sc group by c#);

47、查詢沒學過「猴子」老師講授的任一門課程的學生姓名

select sname from student where s# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c# and tname=猴子);

48、查詢兩門以上不及格課程的同學的學號及其平均成績

select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score<60 group by s# having count(*)>2)group by s#;

49、檢索課程編號為「004」且分數小於60的學生學號，結果按按分數降序排列

select s# from sc where c#=004 and score<60 order by score desc;

50、刪除學生編號為「002」的課程編號為「001」的成績

delect from sc where s#=002 and c#=001;

這些題要融會貫通，以後碰到類似的面試題都可以找到對應場景的使用案例。