0
0

SQL经典50题

参考内容

首先这篇记录是根据下面B站的视频进行的简单记录！
因为是自己个人写的，所以有些SQL是自己的想法，可能会与视频中有出入，但个人认为有些SQL有几种写法，自己喜欢哪种写哪种，而且视频中只是讲了这些题目的实现逻辑，实际当中肯定要更加规范一点。
以下内容中如有错误，请各位大佬及时指出，并请不吝赐教！

SQL面试50题

视频中参考文章：

图解SQL面试题：经典50题

【SQL】SQL经典50题&答案

数据库

下载这个SQL文件新建一个数据库直接导入即可，sql语句太长就不往这放了

sql50.sql

img

50题

有些例题比较简单可以略过！

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

SELECT
    Student.*,01_score.CId,1_score,02_score.CId,2_score 
FROM
    Student
    JOIN(SELECT SId, CId, score AS1_score FROM SC WHERE CId ='01')AS01_score ON Student.SId =01_score.SId
    JOIN(SELECT SId, CId, score AS2_score FROM SC WHERE CId ='02')AS02_score ON01_score.SId =02_score.SId 
WHERE1_score >2_score

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

SELECT
    sc.SId,avg( sc.score )FROM
    sc 
GROUPBY
    sc.SId 
HAVINGavg( 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
    LEFTJOIN sc ON sc.SId = a.Sid 
GROUPBY
    a.Sid

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

SELECTcount( Tid )FROM
    teacher 
WHERE
    Tname LIKE'张%'

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

SELECT
    student.SId,
    student.sname 
FROM
    student 
WHERE
    student.sid NOTIN(SELECT
        sc.SId 
    FROM
        sc
        INNERJOIN course ON sc.Cid = course.CId
        INNERJOIN teacher ON course.TId = teacher.TId 
    WHERE
    teacher.Tname ='张三')

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

SELECT
    student.SId,
    student.sname,
    course.Cname,
    teacher.Tname 
FROM
    sc
    INNERJOIN course ON sc.Cid = course.CId
    INNERJOIN teacher ON course.TId = teacher.TId
    INNERJOIN student ON sc.SId = student.sid 
WHERE
    teacher.Tname ='张三'ORDERBY
    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
    INNERJOIN(SELECT sid FROM sc WHERE sc.CId ='02')AS b ON a.sid = b.sid 
    )

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

SELECTsum( 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 <60GROUPBY sc.sid )AS a
    INNERJOIN(SELECT sc.sid,count( sc.CId )AS bcount FROM sc GROUPBY sc.sid )AS b ON a.sid = b.sid 
    innerjoin student on student.sid = a.sid
WHERE
    a.acount = b.bcount

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

SELECT
    student.sid,
    student.sname 
FROM
    student
    LEFTJOIN sc ON sc.sid = student.sid 
GROUPBY
    student.sid 
HAVINGcount(DISTINCT sc.cid )<(SELECTcount( cid )FROM course)

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

SELECT
    student.sid,
    student.sname 
FROM
    student
    INNERJOIN(SELECTDISTINCT
        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 
    GROUPBY SId 
    HAVINGcount( CId )=(SELECTcount( CId )FROM sc c WHERE SId =01))AND SId !=01

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

SELECT
    student.sid,
    student.sname 
FROM
    student 
WHERE
    student.sid NOTIN(SELECT
        sc.sid 
    FROM
        sc
        INNERJOIN course ON sc.CId = course.CId
        INNERJOIN teacher ON course.tid = teacher.TId 
    AND teacher.Tname ='张三')

14.

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

SELECT
    sc.sid,
    student.sname,avg( sc.score )FROM
    sc
    INNERJOIN student ON student.sid = sc.sid 
WHERE
    sc.score <60GROUPBY sid HAVINGcount(DISTINCT sc.cid )>=2

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

SELECT
    student.sid,
    student.sname 
FROM
    sc
    INNERJOIN student ON sc.sid = student.sid 
WHERE
    sc.cid ='01'AND sc.score <60ORDERBY
    sc.score DESC

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

SELECT
    sc.sid,max(CASEWHEN sc.cid ='01'THEN sc.score ELSENULLEND)'语文',max(CASEWHEN sc.cid ='02'THEN sc.score ELSENULLEND)'数学',max(CASEWHEN sc.cid ='03'THEN sc.score ELSENULLEND)'英语',avg( sc.score )FROM
    sc 
GROUPBY
    sc.sid 
ORDERBYavg( sc.score )DESC

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

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
    course.cid,
    course.Cname,max( sc.score )'最高分',min( sc.score )'最低分',avg( sc.score )'平均分',sum(CASEWHEN sc.score >=60THEN1ELSE0END)/count( sc.SId )'及格',sum(CASEWHEN sc.score >=70AND sc.score <80THEN1ELSE0END)/count( sc.SId )'中等',sum(CASEWHEN sc.score >=80AND sc.score <90THEN1ELSE0END)/count( sc.SId )'良好',sum(CASEWHEN sc.score >=90THEN1ELSE0END)/count( sc.SId )'优秀'FROM
    course
    INNERJOIN sc ON sc.cid = course.cid 
GROUPBY
    course.cid

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

-- 窗口函数
https://blog.csdn.net/hyazz_/article/details/95052222SELECT
    sc.sid,
    sc.cid,
    sc.score,
    ROW_NUMBER()over(ORDERBY sc.score DESC)FROM
    sc

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

SELECT
    sc.SId,sum( sc.score )FROM
    sc 
GROUPBY
    sc.sid 
ORDERBYsum( sc.score )DESC

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

SELECT
    teacher.Tname,
    course.Cname,avg( sc.score )FROM
    teacher
    INNERJOIN course ON teacher.TId = course.tid
    INNERJOIN sc ON course.cid = sc.cid 
GROUPBY
    sc.cid 
ORDERBYavg( sc.score )DESC

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

-- 窗口函数SELECT*FROM(SELECT*,
        ROW_NUMBER()over(PARTITIONBY sc.cid ORDERBY sc.score DESC) m 
    FROM
        sc
        INNERJOIN student stu ON stu.sid = sc.sid 
    ) a 
WHERE
    m IN(2,3)

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

SELECT
    course.cid,
    course.Cname,count(CASEWHEN sc.score >=60THEN1ELSENULLEND)'及格',count(CASEWHEN sc.score >=70AND sc.score <80THEN1ELSENULLEND)'中等',count(CASEWHEN sc.score >=80AND sc.score <90THEN1ELSENULLEND)'良好',count(CASEWHEN sc.score >=90THEN1ELSENULLEND)'优秀'FROM
    course
    INNERJOIN sc ON sc.cid = course.cid 
GROUPBY
    course.cid

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

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

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

-- 窗口函数SELECT*FROM(SELECT*,
        ROW_NUMBER()over(PARTITIONBY sc.cid ORDERBY sc.score DESC) m 
    FROM
        sc
        INNERJOIN student stu ON stu.sid = sc.sid 
    ) a 
WHERE
    m IN(1,2,3)

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

SELECT
    course.cid,count( sc.sid )FROM
    sc
    INNERJOIN course ON sc.CId = course.cid 
GROUPBY
    course.cid

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

SELECT
    student.sid,
    student.sname 
FROM
    student
    INNERJOIN sc ON student.sid = sc.sid 
GROUPBY
    sc.sid 
HAVINGcount( sc.cid )=2

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

SELECT
    student.ssex,count( student.sid )FROM
    student 
GROUPBY
    student.ssex

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

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

30.

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

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

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

SELECT
    student.sid,
    student.sname,avg( sc.score )FROM
    sc
    INNERJOIN student ON sc.sid = student.sid 
GROUPBY
    sc.sid 
HAVINGavg(sc.score )

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

SELECT
    sc.cid,avg( sc.score )FROM
    sc 
GROUPBY
    sc.cid 
ORDERBYavg( sc.score )ASC,
    sc.cid DESC

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

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

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

SELECT
    student.sid,
    student.sname,max(CASEWHEN course.cname ='语文'THEN sc.score ELSENULLEND)'语文',max(CASEWHEN course.cname ='数学'THEN sc.score ELSENULLEND)'数学',max(CASEWHEN course.cname ='英语'THEN sc.score ELSENULLEND)'英语'FROM
    student
    INNERJOIN sc ON sc.sid = student.sid
    INNERJOIN course ON sc.CId = course.CId 
GROUPBY
    student.sid

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

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

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

SELECT
    student.sid,
    student.sname,
    course.cname,
    sc.cid,
    sc.score 
FROM
    sc
    INNERJOIN course ON course.cid = sc.cid
    INNERJOIN student ON student.sid = sc.SId 
WHERE
    sc.score <60ORDERBY
    sc.cid DESC

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

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

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

SELECT
  course.cid,count(distinct course.sid )FROM
    course
    INNERJOIN sc ON sc.CId = course.cid 
GROUPBY
    course.cid

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

SELECT
    student.sname,
    sc.score 
FROM
    sc
    INNERJOIN student ON sc.sid = student.sid
    INNERJOIN course ON sc.cid = course.cid
    INNERJOIN teacher ON teacher.tid = course.TId 
    AND teacher.Tname ='张三'ORDERBY
    sc.score DESCLIMIT1

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

SELECT
    sid 
FROM(SELECT b.sid,b.score 
     FROM sc AS b
     INNERJOIN(SELECT sid FROM sc GROUPBY sid HAVINGcount(DISTINCT cid )>1)AS c ON b.sid = c.sid 
    GROUPBY
        b.sid,
        b.score 
    )AS a 
GROUPBY sid HAVINGcount( sid )=1

42.查询每一门课程最好的前两名

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

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
    sc.cid,COUNT(1) cnt 
FROM
    sc 
GROUPBY
    sc.cid 
HAVINGcount(1)>5ORDERBY
    cnt DESC,cid ASC

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

SELECT
    student.sid,
    student.sname 
FROM
    student
    INNERJOIN sc ON student.sid = sc.sid 
GROUPBY
    sc.sid 
HAVINGcount( sc.cid )>=2

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

SELECT
    student.sid,
    student.sname,count( sc.cid ) cnt 
FROM
    student
    INNERJOIN sc ON sc.sid = student.sid 
GROUPBY
    sc.sid 
HAVING
    cnt =(SELECTcount( cid )FROM course)

日期函数

参考：https://www.cnblogs.com/yuyueq/p/16039789.html#24-%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0
函数描述NOW()返回当前的日期和时间CURDATE()返回当前的日期CURTIME()返回当前的时间DATE()提取日期或日期/时间表达式的日期部分EXTRACT()返回日期/时间按的单独部分DATE_ADD()给日期添加指定的时间间隔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 
WHEREmonth( sage)=month(CURDATE());

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

SELECT*FROM
    student 
WHERECASEWHENMONTH(sage)=12THENMONTH(sage)=1ELSEMONTH(sage)=(CURDATE())+1END;

B站例题

img

img

SELECT
    course.Cname,
    a.score,
    student.sname 
FROM(SELECTmax( sc.score ) score, cid FROM sc GROUPBY cid ) a
    LEFTJOIN sc ON sc.score = a.score AND sc.cid = a.cid
    LEFTJOIN course ON sc.cid = course.cid
    LEFTJOIN student ON student.sid = sc.sid
标签： sql 数据库 java
本文转载自: https://blog.csdn.net/Frank_dwx/article/details/125668895
版权归原作者 余月七 所有， 如有侵权，请联系我们删除。
登录后发布评论

“SQL经典50题”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

Linux在Ubuntu系统下安装MySQL数据库（全网最详细）

chrome浏览器selenium点击下载pdf时总是提示“已阻止不安全的下载”

GuiHelper：面向Java Swing的开源GUI设计辅助工具

数据仓库面试题集&离线&实时

毕业设计项目 基于大数据的K-means广告效果分析

大数据-229 离线数仓 - ODS层的构建 Hive处理 JSON 数据处理 结构化

前端三件套(HTML + CSS + JS)

文章导航