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