50道SQL经典面试题(下)

2020-06-18 00:00:00 查询 成绩 课程 学生 选修

点击关注上方“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,Sname
FROM Student
WHERE SID in
(SELECT SID FROM
  (SELECT SID,COUNT(CID) 课程数
   FROM SC GROUP BY SID 
  ) A
 WHERE A.课程数=2
)


27、查询男生、女生人数


SELECT Ssex,COUNT(Ssex) 人数
FROM Student
GROUP BY Ssex


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


SELECT * FROM Student
WHERE Sname like '%风%'


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


SELECT A.*,B.同名人数
FROM Student A
LEFT JOIN 
(SELECT Sname,Ssex,COUNT(*) 同名人数
FROM Student GROUP BY Sname,Ssex
) B
ON A.Sname=B.Sname and A.Ssex=B.Ssex
WHERE B.同名人数>1


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


SELECT * FROM Student
WHERE YEAR(Sage)=1990


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


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


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


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


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


SELECT B.Sname,A.score FROM
(SELECT * FROM SC
WHERE score<60 
and CID=
(SELECT CID FROM Course
WHERE Cname='数学'
)
) A
LEFT JOIN Student B on A.SID=B.SID


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


SELECT A.SID,B.CID,B.score
FROM Student A
LEFT JOIN SC B on A.SID=B.SID


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


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


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


SELECT C.Sname,B.Cname,A.score FROM SC A
JOIN Course B ON A.CID=B.CID
JOIN Student C ON A.SID=C.SID
WHERE A.score<60


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


SELECT A.SID,B.Sname 
FROM 
(SELECT * FROM SC 
WHERE score>80 and CID='01'
) A
LEFT 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='张三'
)
)
)B
WHERE B.A=1


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


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


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


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


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


SELECT SID FROM SC
GROUP BY SID
HAVING 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())+1
THEN 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())+1
then 1 else 0 end)生日提醒
FROM Student

以上就是50道经典SQL面试题的全部内容，如有疑问，欢迎在底下留言讨论。

——End——

后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。
推荐阅读

  • 50道SQL经典面试题(上)

  • SQL Server常用函数整理

  • 真惨！连各大编程语言都摆起地摊了！

  • SELECT COUNT(*) 底层到底干了什么？

  • SQL查询，你还在 SELECT * 吗？


这是一个能学到技术的公众号，欢迎关注
点击「阅读原文」了解SQL训练营

相关文章