50道SQL经典面试题(下)

2020-06-18 22:42:42

点击关注上方“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