3 篇文章 0 订阅

SQL面试50题

【SQL】SQL经典50题&答案

sql50.sql

# 50题

### 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT
Student.*,
01_score.CId,
1_score,
02_score.CId,
2_score
FROM
Student
JOIN ( SELECT SId, CId, score AS 1_score FROM SC WHERE CId = '01' ) AS 01_score ON Student.SId = 01_score.SId
JOIN ( SELECT SId, CId, score AS 2_score FROM SC WHERE CId = '02' ) AS 02_score ON 01_score.SId = 02_score.SId
WHERE
1_score > 2_score


### 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT
sc.SId,
avg( sc.score )
FROM
sc
GROUP BY
sc.SId
HAVING
avg( sc.score )> 60


### 3.(略)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
a.Sid,
count( sc.Cid ),
IFNULL( sum( sc.score ), 0 )
-- sum( CASE WHEN sc.score IS NULL THEN 0 ELSE sc.score END )
FROM
student AS a
LEFT JOIN sc ON sc.SId = a.Sid
GROUP BY
a.Sid


### 4.(略)查询姓“张”的老师个数

SELECT
count( Tid )
FROM
teacher
WHERE
Tname LIKE '张%'


### 5.查询没学过张三老师课的学生的学号和姓名

SELECT
student.SId,
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT
sc.SId
FROM
sc
INNER JOIN course ON sc.Cid = course.CId
INNER JOIN teacher ON course.TId = teacher.TId
WHERE
teacher.Tname = '张三'
)


### 6.查询学过张三老师所教的所有课程的同学的学号和姓名

SELECT
student.SId,
student.sname,
course.Cname,
teacher.Tname
FROM
sc
INNER JOIN course ON sc.Cid = course.CId
INNER JOIN teacher ON course.TId = teacher.TId
INNER JOIN student ON sc.SId = student.sid
WHERE
teacher.Tname = '张三'
ORDER BY
student.sid


### 7.查询学过01课程并且学过02课程的学生

SELECT
*
FROM
student
WHERE
student.sid IN (
SELECT
a.sid
FROM
( SELECT sid FROM sc WHERE sc.CId = '01' ) AS a
INNER JOIN ( SELECT sid FROM sc WHERE sc.CId = '02' ) AS b ON a.sid = b.sid
)


### 8.(略)查询学过02课程的总成绩

SELECT
sum( sc.score )
FROM
sc
WHERE
sc.cid = '02'


### 9.查询所有课程成绩小于60的学生

SELECT
a.sid,
student.sname
FROM
( SELECT sc.sid, count( sc.CId ) AS acount FROM sc WHERE sc.score < 60 GROUP BY sc.sid )
AS a
INNER JOIN
( SELECT sc.sid, count( sc.CId ) AS bcount FROM sc GROUP BY sc.sid )
AS b ON a.sid = b.sid
inner join student on student.sid = a.sid
WHERE
a.acount = b.bcount


### 10.查询没有学全所有课的学生学号、姓名

SELECT
student.sid,
student.sname
FROM
student
LEFT JOIN sc ON sc.sid = student.sid
GROUP BY
student.sid
HAVING
count( DISTINCT sc.cid ) < (SELECT count( cid ) FROM course)


### 11.查询至少有一门课与学号为“01”的学生所学课程相同的学生学号、姓名

SELECT
student.sid,
student.sname
FROM
student
INNER JOIN (
SELECT DISTINCT
sc.SId
FROM
sc
WHERE
sc.CId IN ( SELECT sc.cid FROM sc WHERE sc.SId = '01' )
AND sc.SId != '01'
) AS a ON a.SId = student.sid


### 12.查询和“01”学号学生所学课程完全相同的学生学号

SELECT
*
FROM
student
WHERE
SId IN (SELECT SId
FROM
(SELECT	* FROM	sc a WHERE CId IN ( SELECT CId FROM sc WHERE SId = 01 )) b
GROUP BY SId
HAVING
count( CId ) =(SELECT count( CId ) FROM sc c WHERE SId = 01 ))
AND SId != 01



### 13.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT
student.sid,
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT
sc.sid
FROM
sc
INNER JOIN course ON sc.CId = course.CId
INNER JOIN teacher ON course.tid = teacher.TId
AND teacher.Tname = '张三'
)


### 15.查询两门及其以上不及格的同学的学号、姓名和平均成绩

SELECT
sc.sid,
student.sname,
avg( sc.score )
FROM
sc
INNER JOIN student ON student.sid = sc.sid
WHERE
sc.score < 60 GROUP BY sid HAVING count( DISTINCT sc.cid ) >=2


### 16.(略)检索01课程分数小于60 ，按课程分数降序排列学生信息

SELECT
student.sid,
student.sname
FROM
sc
INNER JOIN student ON sc.sid = student.sid
WHERE
sc.cid = '01'
AND sc.score < 60
ORDER BY
sc.score DESC


### 17.按平均成绩从高到低显示所有学生的所有课程的成绩和平均成绩

SELECT
sc.sid,
max( CASE WHEN sc.cid = '01' THEN sc.score ELSE NULL END ) '语文',
max( CASE WHEN sc.cid = '02' THEN sc.score ELSE NULL END ) '数学',
max( CASE WHEN sc.cid = '03' THEN sc.score ELSE NULL END ) '英语',
avg( sc.score )
FROM
sc
GROUP BY
sc.sid
ORDER BY
avg( sc.score ) DESC


### 18.查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率、优秀率

SELECT
course.cid,
course.Cname,
max( sc.score ) '最高分',
min( sc.score ) '最低分',
avg( sc.score ) '平均分',
sum( CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END )/ count( sc.SId ) '及格',
sum( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END )/ count( sc.SId ) '中等',
sum( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END )/ count( sc.SId ) '良好',
sum( CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END )/ count( sc.SId ) '优秀'
FROM
course
INNER JOIN sc ON sc.cid = course.cid
GROUP BY
course.cid


### 19.按各科成绩进行排序，并显示排名

-- 窗口函数
https://blog.csdn.net/hyazz_/article/details/95052222

SELECT
sc.sid,
sc.cid,
sc.score,
ROW_NUMBER() over ( ORDER BY sc.score DESC )
FROM
sc


### 20.(略)查询学生的总成绩并进行排名

SELECT
sc.SId,
sum( sc.score )
FROM
sc
GROUP BY
sc.sid
ORDER BY
sum( sc.score ) DESC


### 21.(略)查询不同老师所教不同课程平均分从高到低展示

SELECT
teacher.Tname,
course.Cname,
avg( sc.score )
FROM
teacher
INNER JOIN course ON teacher.TId = course.tid
INNER JOIN sc ON course.cid = sc.cid
GROUP BY
sc.cid
ORDER BY
avg( sc.score ) DESC


### 22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- 窗口函数
SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m
FROM
sc
INNER JOIN student stu ON stu.sid = sc.sid
) a
WHERE
m IN ( 2, 3 )


### 23.使用分段来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT
course.cid,
course.Cname,
count( CASE WHEN sc.score >= 60 THEN 1 ELSE NULL END ) '及格',
count( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE NULL END ) '中等',
count( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE NULL END ) '良好',
count( CASE WHEN sc.score >= 90 THEN 1 ELSE NULL END ) '优秀'
FROM
course
INNER JOIN sc ON sc.cid = course.cid
GROUP BY
course.cid


### 24.查询学生平均成绩以及排名

-- 窗口函数
SELECT
sc.sid,
avg(sc.score),
ROW_NUMBER() over ( ORDER BY avg(sc.score) DESC )
FROM
sc


### 25.查询各科成绩前三名的记录（不考虑成绩并列情况）

-- 窗口函数
SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m
FROM
sc
INNER JOIN student stu ON stu.sid = sc.sid
) a
WHERE
m IN ( 1,2, 3 )


### 26.(略)查询每门课程被选修的学生数

SELECT
course.cid,
count( sc.sid )
FROM
sc
INNER JOIN course ON sc.CId = course.cid
GROUP BY
course.cid


### 27.(略)查询出只有两门课程的全部学生的学号和姓名

SELECT
student.sid,
student.sname
FROM
student
INNER JOIN sc ON student.sid = sc.sid
GROUP BY
sc.sid
HAVING
count( sc.cid )=2


### 28.(略)查询男生、女生人数

SELECT
student.ssex,
count( student.sid )
FROM
student
GROUP BY
student.ssex


### 29. (略)查询名字中含有"三"字的学生信息

SELECT
student.sid,
student.sname
FROM
student
WHERE
student.sname LIKE '%三%'


### 31.查询1990年出生的学生名单

SELECT
student.sid,
student.sname
FROM
student
WHERE
YEAR(sage) =1990
-- 或者是用like
SELECT
student.sid,
student.sname
FROM
student
WHERE
student.sage like '1990%'


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

SELECT
student.sid,
student.sname,
avg( sc.score )
FROM
sc
INNER JOIN student ON sc.sid = student.sid
GROUP BY
sc.sid
HAVING
avg(sc.score )


33.(略)查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT
sc.cid,
avg( sc.score )
FROM
sc
GROUP BY
sc.cid
ORDER BY
avg( sc.score ) ASC,
sc.cid DESC


### 34.(略)查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT
student.sid,
student.sname,
sc.score
FROM
sc
INNER JOIN student ON student.sid = sc.sid
INNER JOIN course ON course.cid = sc.CId AND course.cname = '数学'
WHERE
sc.score < 60


### 35.查询所有学生的课程及分数情况

SELECT
student.sid,
student.sname,
max( CASE WHEN course.cname = '语文' THEN sc.score ELSE NULL END ) '语文',
max( CASE WHEN course.cname = '数学' THEN sc.score ELSE NULL END ) '数学',
max( CASE WHEN course.cname = '英语' THEN sc.score ELSE NULL END ) '英语'
FROM
student
INNER JOIN sc ON sc.sid = student.sid
INNER JOIN course ON sc.CId = course.CId
GROUP BY
student.sid


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

SELECT
student.sname,
course.cname,
sc.score
FROM
sc
INNER JOIN student ON student.sid = sc.sid
INNER JOIN course ON sc.cid = course.cid
WHERE
sc.score > 70


### 37.(略)查询不及格的课程并按课程号从大到小排列

SELECT
student.sid,
student.sname,
course.cname,
sc.cid,
sc.score
FROM
sc
INNER JOIN course ON course.cid = sc.cid
INNER JOIN student ON student.sid = sc.SId
WHERE
sc.score < 60
ORDER BY
sc.cid DESC


### 38.(略)查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

SELECT
student.sname,
sc.score
FROM
sc
INNER JOIN student ON student.sid = sc.SId
WHERE
sc.CId = '03'
AND sc.score > 80


### 39.(略)求每门课程的学生人数

SELECT
course.cid,
count( distinct course.sid )
FROM
course
INNER JOIN sc ON sc.CId = course.cid
GROUP BY
course.cid


### 40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT
student.sname,
sc.score
FROM
sc
INNER JOIN student ON sc.sid = student.sid
INNER JOIN course ON sc.cid = course.cid
INNER JOIN teacher ON teacher.tid = course.TId
AND teacher.Tname = '张三'
ORDER BY
sc.score DESC
LIMIT 1


### 41.查询某个不同课程但成绩相同的学生编号、课程编号、学生成绩

SELECT
sid
FROM
(SELECT b.sid,b.score
FROM sc AS b
INNER JOIN ( SELECT sid FROM sc GROUP BY sid HAVING count( DISTINCT cid )> 1 ) AS c ON b.sid = c.sid
GROUP BY
b.sid,
b.score
) AS a
GROUP BY sid HAVING	count( sid )=1


### 43.统计每门课程的学生选修人数（超过5人的课程才统计）。

SELECT
sc.cid,
COUNT( 1 ) cnt
FROM
sc
GROUP BY
sc.cid
HAVING
count( 1 )> 5
ORDER BY
cnt DESC,cid ASC


### 44.检索至少选修两门课程的学生学号

SELECT
student.sid,
student.sname
FROM
student
INNER JOIN sc ON student.sid = sc.sid
GROUP BY
sc.sid
HAVING
count( sc.cid )>=2


### 45.查询选修了全部课程的学生信息

SELECT
student.sid,
student.sname,
count( sc.cid ) cnt
FROM
student
INNER JOIN sc ON sc.sid = student.sid
GROUP BY
sc.sid
HAVING
cnt = (SELECT count( cid ) FROM course)


### 日期函数

NOW()返回当前的日期和时间
CURDATE()返回当前的日期
CURTIME()返回当前的时间
DATE()提取日期或日期/时间表达式的日期部分
EXTRACT()返回日期/时间按的单独部分
DATE_SUB()从日期减去指定的时间间隔
DATEDIFF()返回两个日期之间的天数
DATE_FORMAT()用不同的格式显示日期/时间

### 46.(略)查询各学生的年龄

SELECT
sname,
TIMESTAMPDIFF(YEAR,sage,CURDATE())
FROM
student


### 47.(略)查询本周过生日的学生

SELECT
*
FROM
student
WHERE
WEEK ( sage, 1 )= WEEK ( NOW(), 1 );


### 48.查询下周过生日的学生

-- 要考虑跨年的情况


### 49.(略)查询本月过生日的学生

SELECT
*
FROM
student
WHERE
month ( sage)= month (CURDATE());


### 50.查询下月过生日的学生

SELECT
*
FROM
student
WHERE
CASE WHEN MONTH (sage)= 12 THEN MONTH (sage)= 1 ELSE MONTH (sage)=(CURDATE())+ 1 END;


# B站例题

SELECT
course.Cname,
a.score,
student.sname
FROM
( SELECT max( sc.score ) score, cid FROM sc GROUP BY cid ) a
LEFT JOIN sc ON sc.score = a.score AND sc.cid = a.cid
LEFT JOIN course ON sc.cid = course.cid
LEFT JOIN student ON student.sid = sc.sid

12-02
10-11 161
09-21 396
03-28 9123
03-29
03-19 7288
08-23
09-16 753
11-06 341
02-12 1311

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。