暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 50道SQL经典面试题(下)

50道SQL经典面试题(下)

SQL数据库开发 2020-06-19
1172

点击关注上方“SQL数据库开发”，

设为“置顶或星标”，第一时间送达干货。

由于篇幅较长，上次已经给小伙伴们分享了25道面试题，还没看的可以戳这里：50道SQL经典面试题(上)

今天继续给小伙伴们补上剩下的25道。

为便于阅读理解，我们还是把表结构和测试数据给大家补上。

一、表结构

1、学生表

Student(Sid,Sname,Sage,Ssex)

学生编号，学生姓名，出生年月，学生性别

2、课程表

Course(Cid,Cname,Tid) 

课程编号，课程名称，教师编号

3、教师表

Teacher(Tid,Tname)

教师编号，教师姓名

4、成绩表

SC(Sid,Cid,Score)

学生编号，课程编号，分数

二、表之间的关系
四张表之间的关系如下图：
我们来解读一下上面的关系：
1、课程表Course的课程编号(Cid)作为主键，在成绩表(SC)中可以看到一个或多个学生的课程分数，两表之间是属于1：n的关系。同理学生表(Student)与成绩表(SC)也是1：n的关系
2、教师表Teacher的教师编号(Tid)作为主键，在课程表(Course)中可以带一门或多门课程，两表之间也是属于1：n的关系。
三、测试数据
1、学生表
--建表语句CREATE TABLE Student (  SID VARCHAR (10),  Sname nvarchar (10),  Sage datetime,  Ssex nvarchar (10))--插入测试数据INSERT INTO Student VALUES('01' , N'赵雷' , '1990-01-01' , N'男')INSERT INTO Student VALUES('02' , N'钱电' , '1990-12-21' , N'男')INSERT INTO Student VALUES('03' , N'孙风' , '1990-05-20' , N'男')INSERT INTO Student VALUES('04' , N'李云' , '1990-08-06' , N'男')INSERT INTO Student VALUES('05' , N'周梅' , '1991-12-01' , N'女')INSERT INTO Student VALUES('06' , N'吴兰' , '1992-03-01' , N'女')INSERT INTO Student VALUES('07' , N'郑竹' , '1989-07-01' , N'女')INSERT INTO Student VALUES('08' , N'王菊' , '1990-01-20' , N'女')

结果如下：

2、课程表

--建表语句CREATE TABLE Course (  CID VARCHAR (10),  Cname nvarchar (10),  TID VARCHAR (10))--插入测试数据INSERT INTO Course VALUES('01' , N'语文' , '02')INSERT INTO Course VALUES('02' , N'数学' , '01')INSERT INTO Course VALUES('03' , N'英语' , '03')

结果如下：

3、教师表

--建表语句CREATE TABLE Teacher (  TID VARCHAR (10),  Tname nvarchar (10))--插入测试数据INSERT INTO Teacher VALUES('01' , N'张三')INSERT INTO Teacher VALUES('02' , N'李四')INSERT INTO Teacher VALUES('03' , N'王五')

结果如下：

4、成绩表

--建表语句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)

结果如下：

四、面试题及参考答案

26、查询出只选修两门课程的学生学号和姓名 

SELECT SID,SnameFROM StudentWHERE SID in(SELECT SID FROM  (SELECT SID,COUNT(CID) 课程数   FROM SC GROUP BY SID   ) A WHERE A.课程数=2)

27、查询男生、女生人数

SELECT Ssex,COUNT(Ssex) 人数FROM StudentGROUP BY Ssex

28、查询名字中含有「风」字的学生信息

SELECT * FROM StudentWHERE Sname like '%风%'

29、查询同名同性学生名单，并统计这些人数

SELECT A.*,B.人数FROM Student ALEFT JOIN (SELECT Sname,Ssex,COUNT(*)   人数FROM Student GROUP BY Sname,Ssex) BON A.Sname=B.Sname and A.Ssex=B.SsexWHERE B.人数>1

30、查询 1990 年出生的学生名单

SELECT * FROM StudentWHERE YEAR(Sage)=1990

31、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT CID,AVG(score) 平均成绩FROM SCGROUP BY CID ORDER BY 平均成绩 DESC,CID

32、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 

SELECT A.SID,A.Sname,B.平均成绩FROM Student ALEFT JOIN (SELECT SID,AVG(score) 平均成绩FROM SC GROUP BY SID) B on A.SID=B.SIDWHERE B.平均成绩>85

33、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT B.Sname,A.score FROM(SELECT * FROM SCWHERE score<60 and CID=(SELECT CID FROM CourseWHERE Cname='数学')) ALEFT JOIN Student B on A.SID=B.SID

34、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT A.SID,B.CID,B.scoreFROM Student ALEFT JOIN SC B on A.SID=B.SID

35、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT A.Sname,D.Cname,D.scoreFROM (SELECT B.*,C.CnameFROM(SELECT * FROM SC WHERE score>70) BLEFT JOIN Course C on B.CID=C.CID) DLEFT JOIN Student A on D.SID=A.SID

36、查询不及格的课程学生姓名，课程名及分数

SELECT C.Sname,B.Cname,A.score FROM SC AJOIN Course B ON A.CID=B.CIDJOIN Student C ON A.SID=C.SIDWHERE A.score<60

37、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

SELECT A.SID,B.Sname FROM (SELECT * FROM SC WHERE score>80 and CID='01') ALEFT JOIN Student B on A.SID=B.SID

38、求每门课程的学生人数（假设每个学生都有参加考试且有成绩）

SELECT CID,COUNT(*) 学生人数 FROM SC GROUP BY CID

39、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT TOP 1 * FROM SC WHERE CID=(SELECT CID FROM Course WHERE TID=(SELECT TID FROM Teacher WHERE Tname='张三')) ORDER BY score DESC

40、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT * FROM ( SELECT *,DENSE_RANK()OVER(ORDER BY score DESC) A FROM SC WHERE CID=(SELECT CID FROM Course WHERE TID=(SELECT TID FROM Teacher WHERE Tname='张三')))BWHERE B.A=1

41、查询每门功成绩最好的前两名

SELECT * FROM(SELECT *,ROW_NUMBER()OVER (PARTITION BY CID ORDER BY score DESC)A FROM SC)BWHERE B.A<3

42、统计每门课程的学生选修人数（超过5人的课程才统计），要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT CID,COUNT(SID) 选修人数 FROM SC GROUP BY CIDHAVING COUNT(SID)>5ORDER BY 选修人数 DESC,CID

43、检索至少选修两门课程的学生学号 

SELECT SID FROM SCGROUP BY SIDHAVING COUNT(CID)>=2

44、查询选修了全部课程的学生信息

SELECT SID FROM SC GROUP BY SID HAVING  COUNT(CID)=(SELECT DISTINCT COUNT(1) a FROM Course)

45、查询各学生的年龄，只按年份来算

SELECT SID,DATEDIFF(Year,Sage,GETDATE()) 年龄 FROM Student

46、按照出生日期来算，当前月日小于出生日期的月日则年龄减1岁

SELECT *,(CASE WHEN  CONVERT(INT,'1'+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5,8)) < CONVERT(int,'1'+SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),112),5,8)) THEN DATEDIFF(YY,Sage,GETDATE()) ELSE DATEDIFF(YY,Sage,GETDATE())-1 END)age FROM Student

47、查询本周过生日的学生

SELECT *,(CASE WHEN DATENAME(wk,CONVERT(DATETIME,(CONVERT(VARCHAR(10),YEAR(GETDATE()))+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5,8))))=DATENAME(WK,GETDATE()) THEN 1 ELSE 0 END) 生日提醒FROM Student

48、查询下周过生日的学生

SELECT *,(CASE WHEN DATENAME(wk,CONVERT(DATETIME,(CONVERT(VARCHAR(10),YEAR(GETDATE()))+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5,8))))=DATENAME(WK,GETDATE())+1THEN 1 ELSE 0 END) 生日提醒FROM Student

49、查询本月过生日的学生

SELECT *,(CASE WHEN MONTH(CONVERT(DATETIME,(CONVERT(VARCHAR(10),YEAR(GETDATE()))+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5,8))))=MONTH(GETDATE())THEN 1 ELSE 0 end) 生日提醒FROM Student

50、查询下月过生日的学生

SELECT *,(CASE WHEN MONTH(CONVERT(DATETIME,(CONVERT(VARCHAR(10),YEAR(GETDATE()))+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5,8))))=MONTH(GETDATE())+1then 1 else 0 end)生日提醒FROM Student
以上就是50道经典SQL面试题的全部内容，如有疑问，欢迎在底下留言讨论。

——End——

后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。
推荐阅读
这是一个能学到技术的公众号，欢迎关注
点击「阅读原文」了解SQL训练营

面试
最后修改时间：2020-06-20 09:45:56
文章转载自SQL数据库开发，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论