精华内容
下载资源
问答
  • sql练习题
    千次阅读 多人点赞
    2020-09-15 18:39:30

    前言

    作为一个SQL小白,在一个月的努力后终于把这50道练习题做完了,分享一下我的答案给大家,说不定能给你提供一些思路。

    我使用的是在线SQL,个人认为十分方便,使用的版本是“Ms SQL Server 2017”。

    一、数据表

    1. 学生表 Student(SId,Sname,Sage,Ssex)–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
    create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '1990-12-21' , '男');
    insert into Student values('03' , '孙风' , '1990-05-20' , '男');
    insert into Student values('04' , '李云' , '1990-08-06' , '男');
    insert into Student values('05' , '周梅' , '1991-12-01' , '女');
    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
    insert into Student values('09' , '张三' , '2017-12-20' , '女');
    insert into Student values('10' , '李四' , '2017-12-25' , '女');
    insert into Student values('11' , '李四' , '2017-12-30' , '女');
    insert into Student values('12' , '赵六' , '2017-01-01' , '女');
    insert into Student values('13' , '孙七' , '2018-01-01' , '女');
    
    1. 课程表 Course(CId,Cname,TId) – CId 课程编号,Cname 课程名称,TId 教师编号
    create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');
    
    1. 教师表 Teacher(TId,Tname)–TId 教师编号,Tname 教师姓名
    create table Teacher(TId varchar(10),Tname varchar(10));
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');
    
    1. 成绩表 SC(SId,CId,score)–SId 学生编号,CId 课程编号,score 分数
    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);
    

    二、题目

    1. 查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
      1.1 查询同时存在” 01 “课程和” 02 “课程的情况
      1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
      1.3 查询不存在” 01 “课程但存在” 02 "课程的情况
    2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    3. 查询在 SC 表存在成绩的学生信息
    4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
      4.1 查有成绩的学生信息
    5. 查询「李」姓老师的数量
    6. 查询学过「张三」老师授课的同学的信息
    7. 查询没有学全所有课程的同学的信息
    8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
    10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
    11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
    13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    14. 查询各科成绩最高分、最低分和平均分
    15. 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
    16. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
    17. 按各科成绩进行排序,并显示排名, Score 重复时合并名次
    18. 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
    19. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
    20. 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
    21. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
    22. 查询各科成绩前三名的记录
    23. 查询每门课程被选修的学生数
    24. 查询出只选修两门课程的学生学号和姓名
    25. 查询男生、女生人数
    26. 查询名字中含有「风」字的学生信息
    27. 查询同名同性学生名单,并统计同名人数
    28. 查询 1990 年出生的学生名单
    29. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    30. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
    31. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
    32. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
    33. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
    34. 查询不及格的课程
    35. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
    36. 求每门课程的学生人数
    37. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    38. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    39. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    40. 查询每门功成绩最好的前两名
    41. 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
    42. 检索至少选修两门课程的学生学号
    43. 查询选修了全部课程的学生信息
    44. 查询各学生的年龄,只按年份来算
    45. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    46. 查询本周过生日的学生
    47. 查询下周过生日的学生
    48. 查询本月过生日的学生
    49. 查询下月过生日的学生

    三、题解

    1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
    select t1.sid, t1.score as class1,t2.score as class2 from
    (select sid,cid,score from sc where cid = '01') t1,
    (select sid,cid,score from sc where cid = '02') t2
    where t1.sid = t2.sid and t1.score > t2.score
    
    • 1.1 查询同时存在" 01 “课程和” 02 "课程的情况
    Select sc.sid from sc
    Where sc.cid = '01' and sc.sid in (select sid from sc where cid = '02')
    
    • 1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
    Select * from 
    (select * from sc where sc.cid =01) t1
    Left join (select * from sc where sc.cid =02) t2
    On t1.sid = t2.sid
    
    • 1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
    Select * from sc
    Where sc.cid =02and sc.sid not in (select sid from sc where cid =01)
    
    1. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    Select sc.sid, st.sname, avg(sc.score) avg_score from sc
    Join student st
    On sc.sid = st.sid
    Group by sc.sid
    Having avg_score >= 60
    
    1. 查询在 SC 表存在成绩的学生信息
    Select sc.*, st.* from sc
    Left join student st
    On sc.sid = st.sid
    
    1. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
    Select st.sid, st.sname, count(sc.cid), sum(sc.score) from student st
    Left join sc 
    On st.sid = sc.sid
    Group by st.sid
    
    • 4.1 查有成绩的学生信息
    Select st.* from student t
    Where st.sid in (select sid from sc)
    
    1. 查询「李」姓老师的数量
    Select count(tid) from teacher
    Where tname like’李%
    1. 查询学过「张三」老师授课的同学的信息
    Select sc.sid, st.* from sc
    Left join student st
    On sc.sid = st.sid
    Where sc.cid in (Select c.cid from course c
    Join teacher t
    On c.tid = t.tid
    Where t.tname = ‘张三’)
    
    1. 查询没有学全所有课程的同学的信息
    Select st.* from student t
    Where st.sid not in 
    (select sid from sc group by sid having count(cid) = (select count(cid) from course))
    
    1. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    Select distinct sc.sid, st.* from sc
    Join student st
    On sc.sid = st.sid
    Where sc.cid in (Select cid from sc where sid =01)
    
    1. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
    Select sc.sid, st.* from sc
    Join student st
    On sc.sid = st.sid
    where sc.cid in (Select cid from sc where sid = '01') and sc.sid != '01'
    group by sc.sid
    having count(sc.cid) = (Select count(cid) from sc where sid = '01')
    
    1. 查询没学过"张三"老师讲授的任一门课程的学生姓名
    select st.sname from student st
    where st.sname not in 
    (select st.sname from student st,sc,course c,teacher t
    where st.sid = sc.sid and sc.cid = c.cid and c.tid = t.tid and t.tname = '张三')
    
    1. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    select sc.sid,st.sname,avg(sc.score) from sc
    join student st
    on sc.sid = st.sid
    group by sc.sid
    having sum(case when sc.score < 60 then 1 else 0 end)>=2
    
    1. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
    select sc.sid,st.sname from sc
    join student st
    on sc.sid = st.sid
    where sc.cid = '01' and sc.score <60
    order by sc.score desc
    
    1. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    select sid,avg(score) over(partition by sid) as avg_score,cid,score from sc
    order by avg_score desc
    
    select sc.sid,t1.avg_score,sc.cid,sc.score from sc
    left join (select sid,avg(score) as avg_score,cid,score from sc group by sid) t1
    on sc.sid = t1.sid
    order by 2 desc
    
    1. 查询各科成绩最高分、最低分和平均分:
    select cid,max(score),avg(score) from sc
    group by cid
    
    1. 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
    select sc.cid,c.cname,max(sc.score),min(sc.score),avg(sc.score),
    sum(case when sc.score>=60 then 1 else 0 end)*1.0/count(sc.score) 及格,
    sum(case when sc.score between 70 and 80 then 1 else 0 end)*1.0/count(sc.score) 中等,
    sum(case when sc.score between 80 and 90 then 1 else 0 end)*1.0/count(sc.score) 优良,
    sum(case when sc.score>=90 then 1 else 0 end)*1.0/count(sc.score) 优秀
    from sc
    join course c
    on sc.cid = c.cid
    group by sc.cid
    
    1. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
    select cid, rank() over (partition by cid order by score desc) rank,score from sc
    
    1. 按各科成绩进行排序,并显示排名, Score 重复时合并名次
    select cid, dense_rank() over (partition by cid order by score desc) rank,score from sc
    
    1. 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
    select cid, count(sid) from sc
    group by cid
    order by 2 desc,1
    
    1. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
    select sum(score) sum_score, rank() over (order by sum(score) desc) rank from sc
    group by sid
    
    1. 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺

    2. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比

    select cid,
    sum(case when score between 85 and 100 then 1 else 0 end)*1.0/count(sid) '85-100',
    sum(case when score between 70 and 85 then 1 else 0 end)*1.0/count(sid) '70-85',
    sum(case when score between 60 and 70 then 1 else 0 end)*1.0/count(sid) '60-70',
    sum(case when score between 85 and 100 then 1 else 0 end)*1.0/count(sid) '0-60'
    from sc group by cid
    
    1. 查询各科成绩前三名的记录
    select * from (select cid, sid,score, row_number() over (partition by cid order by score desc) as rank from sc) t
    where t.rank <= 3
    
    1. 查询每门课程被选修的学生数
    select cid, count(sid) from sc
    group by cid
    
    1. 查询出只选修两门课程的学生学号和姓名
    select sc.sid from sc
    group by sid
    having count(distinct cid)=2
    
    1. 查询男生、女生人数
    select count(distinct t1.sid),count(distinct t2.sid)from 
    (select sid from student where ssex = '男') t1,
    (select sid from student where ssex = '女') t2
    
    1. 查询名字中含有「风」字的学生信息
    select * from student
    where sname like '%风%'
    
    1. 查询同名同性学生名单,并统计同名人数
    select sname, count(*) from student
    group by sname
    having count(*)>1;
    select * from
    (select t1.* from student t1, student t2
    where t1.sname = t2.sname and t1.sid != t2.sid group by t1.sid) a
    
    1. 查询 1990 年出生的学生名单
    select * from student
    where sage between '1990-01-01' and '1991-01-01'
    
    1. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    select distinct cid, avg(score) over (partition by cid) avg_score from sc
    order by 2 desc,1
    
    1. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
    select * from
    (select distinct sid , avg(score) over (partition by sid) avg_score from sc) t1
    where avg_score >= 85
    
    1. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
    select st.sname,sc.*,c.cname from sc
    join course c
    on sc.cid = c.cid and c.cname = '数学'
    join student st
    on sc.sid = st.sid
    where sc.score < 60
    
    1. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
    select st.sid, st.sname,sc.* from student st
    left join sc
    on st.sid = sc.sid
    
    1. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
    select sc.sid, st.sname, c.cname, sc.score from sc
    join  student st
    on sc.sid = st.sid
    join course c
    on sc.cid = c.cid
    where sc.score > 70
    
    1. 查询不及格的课程
    select sc.sid, st.sname, c.cname, sc.score from sc
    join  student st
    on sc.sid = st.sid
    join course c
    on sc.cid = c.cid
    where sc.score < 60
    
    1. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
    select sc.cid, sc.sid, st.sname,sc.score from sc
    join student st
    on sc.sid = st.sid 
    where sc.cid = '01' and sc.score >=80
    
    1. 求每门课程的学生人数
    select sc.cid, count(sc.sid) from sc
    group by sc.cid
    
    1. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    select st.*,max(sc.score) from sc
    join course c 
    on sc.cid = c.cid
    join teacher t
    on c.tid = t.tid and t.tname = '张三'
    join student st
    on sc.sid = st.sid
    
    1. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    select t1.* from
    (select st.*, sc.score from sc
    join course c 
    on sc.cid = c.cid
    join teacher t
    on c.tid = t.tid and t.tname = '张三'
    join student st
    on sc.sid = st.sid) t1,
    (select st.*,max(sc.score) as max_score from sc
    join course c 
    on sc.cid = c.cid
    join teacher t
    on c.tid = t.tid and t.tname = '张三'
    join student st
    on sc.sid = st.sid) t2
    where t1.score = t2.max_score
    
    1. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    select sc.* from sc,
    (select sid,cid,score from sc
    group by sid
    having count(distinct cid) > count(distinct score)) t1
    where sc.sid = t1.sid
    
    1. 查询每门功成绩最好的前两名
    select * from 
    (select cid, sid, row_number() over (partition by cid order by score desc) as rank from sc)
     t1
    where rank<=2
    
    1. 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
    select * from 
    (select cid, count(distinct sid) as num from sc 
    group by cid) t1
    where num > 5
    
    1. 检索至少选修两门课程的学生学号
    select sid from 
    (select sid, count(cid) as num from sc group by sid) t1
    where num > 2
    
    1. 查询选修了全部课程的学生信息
    select sid from sc
    group by sid
    having count(distinct cid) = (select count(distinct cid) from course)
    
    1. 查询各学生的年龄,只按年份来算
    select sid, sname, timestampdiff(year, sage, current_date()) age from student
    
    1. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    select sid, sname, timestampdiff(year, sage, current_date()) age from student
    
    1. 查询本周过生日的学生
    select *
    from student 
    where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(current_date())
    
    1. 查询下周过生日的学生
    select *
    from student 
    where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(current_date())+1
    
    1. 查询本月过生日的学生
    select *
    from student 
    where MONTH(student.Sage)=MONTH(current_date())
    
    1. 查询下月过生日的学生
    select *
    from student 
    where MONTH(student.Sage)=MONTH(current_date())+1
    
    更多相关内容
  • 50道SQL练习题及答案与详细分析 数据表介绍 –1.学生表 Student(SId,Sname,Sage,Ssex) –SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表 Course(CId,Cname,TId) –CId 课程编号,Cname 课程...
  • 50道SQL练习题及答案与详细分析(题目和数据初始化) 50道SQL练习题及答案与详细分析(01~05) 50道SQL练习题及答案与详细分析(06~10) 1.查询”01″课程比”02″课程成绩高的学生的信息及课程分数. SELECT ...
  • 网上有一篇关于SQL的经典文章,超经典SQL练习题,做完这些你的SQL就过关了,引用和分析它的人很多,于是今天复习SQL的时候找来练了练手。
  • 经典SQL练习题(MySQL版)_廖致君的博客-CSDN博客_sql练习.url
  • ORACLE SQL练习题(带答案)ORACLE SQL练习题(带答案)
  • 第二天 1、查询客户表,统计每个机构2000年之前开户数、2000~2005开户数(含头不含尾)、2005~2010开户数(含头不含尾)、2010之后开户数 展示字段:机构号、2000年之前开户数、2000~2005年开户数、2005~2010年开户...
  • 建表: 代码如下: set nocount on –当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。当 SET NOCOUNT 为 OFF 时,返回计数 use SY GO if object_Id(‘dbo.Orders’) is not null drop ...
  • 整理的sql练习题

    2018-11-20 22:03:53
    整理的sql练习,很经典,可以帮助学习,包含数据库的查询,统计,分组,也包含一些高级sql特性
  • 50道SQL练习题

    2018-03-01 12:02:02
    50道SQL练习题,还附带有详细答案,练完后大概就能掌握SQL基本查询语句了
  • 数据库sql练习题

    2019-01-31 10:53:44
    SQL数据查询练习题 面试测试用 适用于初学sql者 练习相关查询
  • sql练习题.pdf

    2019-08-20 17:47:36
    ⽹网上流传较⼴广的50道SQL训练,奋⽃斗了了不不知道多久终于写完了了。前18道的难度依次 递增,从19开始的后半部分算是循环练习和额外function的附加练习,难度恢复到普 通状态。
  • vfp6.0SQL练习题

    2017-11-30 09:42:12
    vfp6.0结构化查询语言SQL练习题1附带答案,包含简单查询、条件查询、查询排序、分组、嵌套查询、SQL数据定义等
  • 50道经典SQL练习题,练手和复习都很不错 关于 这个项目主要来源是之前在网上流传的50道SQL练习题,大小问加起来50道左右,在不断流传的过程中也会有差异,我看到的最早来源是 使用项目 原版用的是Sql Server,在这里...
  • 经典sql练习题答案

    2014-09-06 00:51:27
    select dname deptno from dept where deptno in select deptno from emp ;
  • SQL练习习题.sql

    2022-07-22 14:16:00
    SQL练习习题.sql
  • 精选36道SQL练习题解析 from(原50道SQL练习题

    千次阅读 多人点赞 2020-05-29 10:51:08
    精选36道MySQL练习题解析 from 原50道SQL练习题 对网络上流传的50道SQL练习题做了筛选,去掉了一些重复的题,剩下36道,总结分为6大类,几乎囊括了SQL查询绝大部分知识点,并给出了不同方法及解析,掌握这36道题,...

    SQL练习题

    友情链接

    1、医疗信息管理系统数据库–MySQL

    医疗信息管理系统数据库–MySQL

    2、邮件管理数据库设计–MySQL

    邮件管理数据库设计–MySQL

    3、SQL Server医疗信息管理系统数据库【英文版-源码】–(Medical Management System Database)

    SQL Server医疗信息管理系统数据库【英文版-源码】–(Medical Management System Database)

    4、SQL Server电影院数据库管理系统【英文版-源码】–(Movie Theatre Management System Database)

    SQL Server电影院数据库管理系统【英文版-源码】–(Movie Theatre Management System Database)

    一、MySQL基础知识点总结

    对网络上流传的50道SQL练习题做了筛选,去掉了一些重复的题,剩下36道,总结分为6大类,几乎囊括了SQL查询绝大部分知识点,并给出了不同方法及解析,掌握这36道题,SQL查询语句基本上没啥大问题了,上图:
    在这里插入图片描述

    二、学生成绩管理系统数据库设计

    1.项目背景及需求分析

    1.1 项目背景
    1.2 需求分析

    2.概念结构设计

    2.1 抽象出系统实体
    2.2 全局E-R图

    3.逻辑结构设计

    3.1 关系模式
    3.2 函数依赖识别
    3.3 范式

    4.物理设计和实施

    4.1 SQL练习数据库及表创建

    MySQL版本:8.0.20

    -- 如果已有该数据库,则删除
    DROP DATABASE IF EXISTS StudentScore;
    
    -- 创建数据库
    CREATE DATABASE StudentScore CHARSET=UTF8;
    
    
    -- 使用数据库
    USE StudentScore;
    
    
    -- 创建数据表
    
    -- table 1: students
    
    DROP TABLE IF EXISTS students;
    
    CREATE TABLE students(
    sid INT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    sname VARCHAR(20),
    sclass INT(10),
    sgender VARCHAR(10),
    smajor VARCHAR(20),
    sbirthday DATE,
    credit_points INT(5) -- 学生已修学分
    );
    
    
    -- table 2: teachers
    
    DROP TABLE IF EXISTS teachers;
    
    CREATE TABLE teachers(
    tid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tname VARCHAR(20),
    tschool VARCHAR(20)
    );
    
    
    -- table 3: courses
    
    DROP TABLE IF EXISTS courses;
    
    CREATE TABLE courses(
    cid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    cname VARCHAR(20),
    credit_point INT(5), -- 课程学分
    tid INT(10) UNSIGNED NOT NULL,
    FOREIGN KEY(tid) REFERENCES teachers(tid)
    );
    
    
    -- table 4: scores
    
    DROP TABLE IF EXISTS scores;
    
    CREATE TABLE scores(
    sid INT(10) UNSIGNED NOT NULL,
    cid INT(10) UNSIGNED NOT NULL,
    score DECIMAL(5, 2),
    FOREIGN KEY(sid) REFERENCES students(sid),
    FOREIGN KEY(cid) REFERENCES courses(cid)
    );
    
    

    4.2 SQL练习表数据

    -- 插入数据
    
    INSERT INTO students VALUES
    (0, '赵雷', 1, '男', '计算机', '1990-01-01', 3),
    (0, '钱电', 2, '男', '计算机', '1990-12-21', 3),
    (0, '孙风', 3, '男', '计算机', '1990-12-20', 3),
    (0, '李云', 1, '男', '计算机', '1990-12-06', 3),
    (0, '周梅', 2, '女', '物理', '1991-12-01', 3),
    (0, '吴兰', 3, '女', '物理', '1992-01-01', 3),
    (0, '郑竹', 1, '女', '物理', '1989-01-01', 3),
    (0, '张三', 2, '女', '物理', '2017-12-20', 3),
    (0, '李四', 3, '女', '数学', '2017-12-25', 3),
    (0, '李四', 1, '女', '数学', '2012-06-06', 3),
    (0, '赵六', 2, '女', '数学', '2013-06-13', 3),
    (0, '孙七', 3, '女', '数学', '2014-06-01', 3);
    
    
    INSERT INTO teachers VALUES
    (0, '张若尘', '时空学院'),
    (0, '孙悟空', '魔法学院'),
    (0, '纪梵心', '本源学院'),
    (0, '萧炎', '斗气学院'),
    (0, '鲁班', '机械学院');
    
    
    INSERT INTO courses VALUES
    (0, '变形', 1, 2),
    (0, '时空穿梭', 3, 1),
    (0, '分解术', 1, 3),
    (0, '炼器', 2, 5),
    (0, '炼丹', 2, 4),
    (0, '飞行', 1, 2);
    
    
    INSERT INTO scores VALUES
    (1, 1, 80),
    (1, 2, 90),
    (1, 3, 99),
    (2, 1, 70),
    (2, 2, 60),
    (2, 3, 80),
    (3, 1, 80),
    (3, 2, 80),
    (3, 3, 80),
    (4, 1, 50),
    (4, 2, 30),
    (4, 3, 20),
    (5, 1, 76),
    (5, 2, 87),
    (6, 1, 31),
    (6, 3, 34),
    (7, 2, 89),
    (8, 1, 88),
    (8, 2, 82),
    (8, 4, 81),
    (9, 6, 95),
    (10, 5, 86);
    
    

    4.3 精选36道SQL查询练习题及答案解析

    4.3.1 练习题

    4.3.1.1. 连接查询 - 4题

    1.1 查询同时选修了课程 1 和 课程 2 的学生的信息

    1.2 查询课程 1 比 课程 2 成绩高的学生的信息及课程分数

    1.3 查询课程 1 分数小于 60 的学生信息和课程分数,按分数降序排列

    1.4 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

    4.3.1.2. 子查询、连接查询 - 4题

    2.1 查询有成绩的学生信息

    2.2 查询学过 孙悟空 老师所授课程的学生信息

    2.3 查询至少有一门课与学号为 1 的同学所学相同的学生信息

    2.4 查询选修了课程 2 但是没有选修课程 1 的学生信息

    4.3.1.3. 聚合分组、连接查询 - 8题

    3.1 查询同名学生名单,并统计同名人数

    3.2 查询选修了 3 门课程的学生信息

    3.3 查询平均成绩大于等于 85 的所有学生的学号、姓名、平均成绩(保留2位小数)

    3.4 查询平均成绩大于等于 60 分的学生学号、姓名、平均成绩(保留2位小数)

    3.5 查询两门及以上课程分数小于60分的学生学号、姓名及平均成绩(保留2位小数)

    3.6 查询姓 赵 的同学的学生信息、总分,若没选课则总分显示为 0

    3.7 查询所有同学的学号、姓名、选课总数、总成绩,没选课的学生要求显示选课总数和总成绩为 0

    3.8 查询所有学生学号、姓名、选课名称、总成绩,按总成绩降序排序,没选课的学生显示总成绩为 0

    4.3.1.4. if 或 case 语句 - 2题

    4.1 若学号sid为学生座位编号,现开始对座位号调整,奇数号和偶数号对调,如1和2对调、3和4对调…等, 如果最后一位为奇数,则不调换座位,查询调换后的学生座位号(sid)、姓名,按sid排序

    4.2 查询各科成绩最高分、最低分和平均分: 以如下形式显示:课程id、课程名、选修人数、最高分、最低分、平均分、及格率、中等率、优良率、优秀率
    及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
    要求查询结果按人数降序排列,若人数相同,按课程号升序排列,平均分、及格率等保留2位小数

    4.3.1.5. 时间函数 - 6题

    5.1 查询 1990 年出生的学生信息

    5.2 查询各学生的年龄,分别按年份和按出生日期来算

    5.3 查询本周或下周过生日的学生

    5.4 查询本月或下月过生日的学生

    5.5 查询学生信息,要求:学号和年龄同时至少比一位学生的学号和年龄大

    5.6 查询连续相邻3年出生的学生中,学生性别相同的学生信息

    4.3.1.6.综合应用 - 12题

    6.1 查询和学号为 1 的同学学习的课程完全相同的其他同学的信息

    6.2 查询每科均及格的人的平均成绩:学号、姓名、平均成绩(保留2位小数)

    6.3 查询选修 张若尘 老师所授课程的学生中,该门课成绩最高的学生信息及成绩(成绩可能重复)

    6.4 查询各科成绩,按各科成绩进行排序,并显示排名 分数重复时保留名次空缺,即名次不连续

    6.5 查询各科成绩,按各科成绩进行排序,并显示排名 分数重复时不保留名次空缺,即名次连续

    6.6 查询学生 赵雷 的 变形 课程成绩的排名:学生信息,分数,排名 分数重复时不保留名次空缺,即名次连续

    6.7 查询课程 时空穿梭 成绩在第2-4名的学生,要求显示字段:学号、姓名、课程名、成绩 分数重复时不保留名次空缺,即名次连续

    6.8 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺,即名次连续

    6.9 查询学生的总成绩,并进行排名,总分重复时保留名次空缺,及名次不连续 排名名次不连续,不需要去重

    6.10 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 分别所占百分比 结果:保留2位小数

    6.11 查询各科成绩前三名的记录,按照课程编号和分数排序 分数重复时,重复分数按照一名算,即不保留名次空缺,及名次连续

    6.12 查询各科成绩的前两名,列出学生信息、课程名、分数,按照课程名、分数排序 分数重复时,重复分数按照一名算,即不保留名次空缺,及名次连续

    4.3.2 - 答案及解析

    4.3.2.1 连接查询 - 4题

    
    -- 1.1 查询同时选修了课程 1 和 课程 2 的学生的信息
    
    select distinct s1.* from students s1 join 
    scores s2 on s1.sid = s2.sid join
    scores s3 on s2.sid = s3.sid and s2.cid=1 and s3.cid;
    
    
    -- 1.2 查询课程 1 比 课程 2 成绩高的学生的信息及课程分数 
    
    select * from students s1 join 
    (select t1.sid, t1.score as course1, t2.score as course2
    from scores t1, scores t2 
    where t1.cid=1 and t2.cid=2 and t1.sid=t2.sid and t1.score > t2.score) as t3 
    on s1.sid = t3.sid;
    
    
    -- 1.3 查询课程 1 分数小于 60 的学生信息和课程分数,按分数降序排列
    
    select s1.*, s2.score from students s1 join
    scores s2 on s1.sid = s2.sid and s2.cid = 1 and s2.score < 60 
    order by s2.score desc;
    
    
    -- 1.4 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    
    select distinct s1.* from scores s1 join
    scores s2 on s1.sid =s2.sid and s1.cid !=s2.cid and s1.score =s2.score;
    
    

    4.3.2.2 子查询、连接查询 - 4题

    -- 2.1 查询有成绩的学生信息
    
    -- 连接查询
    select distinct s1.* from students s1, scores s2 where s1.sid = s2.sid;
    
    select distinct s1.* from students s1 join scores s2 on s1.sid = s2.sid;
    -- 子查询
    select * from students where sid in (select distinct sid from scores);
    
    select * from students where exists (select sid from scores where scores.sid = students.sid);
    
    
    -- 2.2 查询学过 孙悟空 老师所授课程的学生信息
    
    -- 连接查询
    select s1.* from students s1 join
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- 多级子查询嵌套
    select * from students where sid in 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 2.3 查询至少有一门课与学号为 1 的同学所学相同的学生信息
    
    -- 多级子查询嵌套
    
    select * from students where sid in 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 2.4 查询选修了课程 2 但是没有选修课程 1 的学生信息 
    
    select s1.* from students s1 join
    -- 需要完整代码请添加文章底部微信,付费咨询
    

    4.3.2.3 聚合分组、连接查询 - 8题

    -- 3.1 查询同名学生名单,并统计同名人数
    
    select sname, count(*) as num from students group by sname having count(*) > 1;
    
    
    -- 3.2 查询选修了 3 门课程的学生信息
    
    select * from students 
    where sid in (select sid from scores group by sid having count(cid) = 3);
    
    
    -- 3.3 查询平均成绩大于等于 85 的所有学生的学号、姓名、平均成绩(保留2位小数)
    
    select s2.sid, s1.sname, avg(s2.score) as avgscore from students s1 join 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 3.4 查询平均成绩大于等于 60 分的学生学号、姓名、平均成绩(保留2位小数)
    
    select s1.sid, s1.sname, s2.avgscore from students s1 join
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 3.5 查询两门及以上课程分数小于60分的学生学号、姓名及平均成绩(保留2位小数)
    
    select s1.sid, s1.sname, s2.avgscore from students s1 join
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- 3.6 查询姓 赵 的同学的学生信息、总分,若没选课则总分显示为 0
    
    select s1.*, ifnull(s2.total, 0) as total_score from students s1 left join
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- 3.7 查询所有同学的学号、姓名、选课总数、总成绩,没选课的学生要求显示选课总数和总成绩为 0 
    
    select s1.sid, s1.sname, 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 3.8 查询所有学生学号、姓名、选课名称、总成绩,按总成绩降序排序,没选课的学生显示总成绩为 0 
    
    select s1.sid, s1.sname, s3.cname, 
    ifnull(s3.total, 0) as total_score 
    from students s1 left join
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    

    4.3.2.4 if 或 case 语句 - 2题

    -- 4.1 若学号sid为学生座位编号,现开始对座位号调整,奇数号和偶数号对调,如1和2对调、3和4对调...等,
    -- 	如果最后一位为奇数,则不调换座位,查询调换后的学生座位号(sid)、姓名,按sid排序
    
    -- 思路:考察对 if 和 case when 的运用,需要关注最后一位是否为奇数,对此进行判断
    
    -- if 
    select if( 
    sid < (select count(*) from students), 
    if(sid mod 2 = 0, sid-1, sid+1), 
    if(sid mod 2 = 0, sid-1, sid)
    ) as sid, sname from students 
    order by sid asc;
    
    -- case when
    select (
    case when sid < (select count(*) from students) and sid % 2 != 0 then sid+1 
    when sid = (select count(*) from students) and sid % 2 != 0 then sid 
    else sid-1 end
    ) as sid, sname from students
    order by sid asc;
    
    
    -- 4.2 查询各科成绩最高分、最低分和平均分:
    -- 	以如下形式显示:课程id、课程名、选修人数、最高分、最低分、平均分、及格率、中等率、优良率、优秀率
    -- 	及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
    -- 	要求查询结果按人数降序排列,若人数相同,按课程号升序排列,平均分、及格率等保留2位小数
    
    -- case when
    select s.cid, 
    c.cname,
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- if 
    select s.cid, c.cname, 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    

    4.3.2.5 时间函数 - 6题

    -- 5.1 查询 1990 年出生的学生信息
    
    -- 时间函数:year()
    select * from students where year(sbirthday)=1990;
    
    
    -- 5.2 查询各学生的年龄,分别按年份和按出生日期来算
    
    -- 按年份算
    select sid, sname, (year(curdate()) - year(sbirthday)) as age from students;
    -- 按出生日期算
    
    select sid, sname, timestampdiff(year, sbirthday, curdate()) as age from students;
    
    
    -- 5.3 查询本周或下周过生日的学生
    
    -- 本周
    -- 需要完整代码请添加文章底部微信,付费咨询
    -- 下周
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 5.4 查询本月或下月过生日的学生
    
    -- 本月
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- 下月
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 5.5 查询学生信息,要求:学号和年龄同时至少比一位学生的学号和年龄大
    
    -- to_days()
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- datediff()
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 5.6 查询连续相邻3年出生的学生中,学生性别相同的学生信息
    
    -- 相邻年份即时间差为1年,通过3表自交对比得出连续3年出生且性别相同的学生,
    -- 注意:select 后跟的是哪个表数据,那个表的 sbirthday 就要分别作为连续3年中最小、中间、最大那一年出现
    
    select distinct s1.* from students s1 join 
    students s2 on s1.sgender = s2.sgender join
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    

    4.3.2.6 综合应用 - 12题

    -- 6.1 查询和学号为 1 的同学学习的课程完全相同的其他同学的信息
    
    -- 	思路:
    -- 	用 case when 对把其余学生选的课与 1 号学生选的每一门课进行对比,
    -- 	相同就计数 1 ,最后 sum 求和得出其他学生与 1 号学生相同选课数 number,
    -- 	如果 number 与 1 号学生选课数相等,则表示该学生与 1 号同学学习的课程完全相同
    
    select * from students 
    where sid in (
    	select sid from (
    		select s1.sid, 
    		sum(case when s2.sid = 1 and s1.cid = s2.cid then 1 else 0 end) as number 
    		from scores s1, scores s2 
    		group by sid having s1.sid != 1) s3 
    	where s3.number = (select count(cid) from scores group by sid having sid = 1));
    
    
    -- 6.2 查询每科均及格的人的平均成绩:学号、姓名、平均成绩(保留2位小数)
    
    -- 思路:可以先筛选出有不及格科目的学生学号,然后在把这些学生排除掉
    
    select s1.sid, s1.sname, s2.avgscore 
    from students s1, 
    (select sid, round(avg(score), 2) as avgscore 
    from scores group by sid) s2 
    where s1.sid = s2.sid 
    and s1.sid not in (select distinct sid from scores where score < 60);
    
    
    -- 6.3 查询选修 张若尘 老师所授课程的学生中,该门课成绩最高的学生信息及成绩(成绩可能重复)
    
    -- 	思路:
    -- 	先找出符合条件的课程和分数列表,然后通过分组聚合、聚合函数得到课程编号和最高成绩
    
    select s1.*, s2.score from students s1, scores s2 
    where s1.sid = s2.sid 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.4 查询各科成绩,按各科成绩进行排序,并显示排名
    -- 	分数重复时保留名次空缺,即名次不连续
    
    -- 	思路:
    -- 	1.排名一类的查询一般通过自交(连接)进行对比,通过计算“比当前分数高的分数有几个”来确定排名
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.5 查询各科成绩,按各科成绩进行排序,并显示排名
    -- 	分数重复时不保留名次空缺,即名次连续
    
    -- 	思路:
    -- 	1.通过distinct 去重score,保证排名名次连续;
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- left join
    select s1.*, count(distinct s2.score)+1 as '排名' from scores s1 
    left join scores s2 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.6 查询学生 赵雷 的 变形 课程成绩的排名:学生信息,分数,排名
    -- 	分数重复时不保留名次空缺,即名次连续
    
    -- 注意:需要对分数进行去重
    
    select s1.*, s2.score, 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.7 查询课程 时空穿梭 成绩在第2-4名的学生,要求显示字段:学号、姓名、课程名、成绩
    -- 	分数重复时不保留名次空缺,即名次连续
    
    select s1.sid, s1.sname, c.cname, s2.score 
    from students s1, scores s2, courses c 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.8 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺,即名次连续
    
    -- 	思路:
    -- 	先分组得出总分,然后通过自交对总分进行比较得出名次,
    -- 	需要注意题目要求总分重复时不保留名次空缺,即需要对自关联的一边去重
    
    -- 通过对比
    select t1.sid, t1.total, count(t1.total) as '排名' from 
    (select sid, sum(score) as total from scores group by sid) t1, 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- dense_rank()
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.9 查询学生的总成绩,并进行排名,总分重复时保留名次空缺,及名次不连续
    -- 	排名名次不连续,不需要去重
    
    -- 通过对比
    select t1.sid, t1.total, count(t1.total) as '排名' from 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- rank()
    select *, rank() over (order by total desc) '排名' 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.10 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 分别所占百分比
    -- 	结果:保留2位小数
    
    -- 核心思路:分组聚合,用case when, sum, cancat,left join结合使用
    
    select c.cname, t.* from courses c
    left join (select cid, 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.11 查询各科成绩前三名的记录,按照课程编号和分数排序
    -- 	分数重复时,重复分数按照一名算,即不保留名次空缺,及名次连续
    -- 查询结果如下:
    -- +-----+-----+-------+
    -- | sid | cid | score |
    -- +-----+-----+-------+
    -- |   8 |   1 | 88.00 |
    -- |   1 |   1 | 80.00 |
    -- |   3 |   1 | 80.00 |
    -- |   5 |   1 | 76.00 |
    -- |   1 |   2 | 90.00 |
    -- |   7 |   2 | 89.00 |
    -- |   5 |   2 | 87.00 |
    -- |   1 |   3 | 99.00 |
    -- |   2 |   3 | 80.00 |
    -- |   3 |   3 | 80.00 |
    -- |   6 |   3 | 34.00 |
    -- |   8 |   4 | 81.00 |
    -- |   9 |   6 | 95.00 |
    -- +-----+-----+-------+
    
    -- 思路:
    -- 这一题逻辑比较简单,但是坑比较多。
    -- 核心逻辑是表内分数相互比较得出前3名,需要将单个表定义成2个虚表来实现对比,
    -- 关键在于要把重复分数的情况考虑进来,不然容易漏掉
    -- 成绩前N名表示比前N名更大的数不会超过N名(去掉重复情况)
    
    -- <=
    select s1.sid, s1.cid, s1.score 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    -- <
    select * from scores s1 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    	-- 解析:
    	-- 当 where (select count(distinct score) from scores s2 
    	-- 	where s1.cid = s2.cid and s1.score < s2.score) < 3 在计数时,
    	-- 会把count(distinct score)为 0 的数算进来,所以此时select count(distinct)
    	-- 的结果是[0, 1, 2],即选出s1.score中比s2.score大0个(没有比第1名大的所以为0)、
    	-- 1个(第1名比第2名大所以为1)、2个(第1,2名比第3名大所以为3)的score;
    
    	-- 图解,以cid=01为例:
    	-- cid   s1.score    s2.score    s2比s1中score大的数有N个(重复不算)
    	-- 01      80			 80               0
    	-- 01      80           80               0
    	-- 01      76           76               1
    	-- 01      70           70               2
    
    	-- 当N=0, 1, 2时,对应s1.score中(80, 80, 76, 70)
    
    -- <= 对比时同时取了等号,此时select count(distinct)的结果是[1, 2, 3]
    
    select * from scores s1 
    where (
    select count(distinct score) from scores s2 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    
    -- 6.12 查询各科成绩的前两名,列出学生信息、课程名、分数,按照课程名、分数排序
    -- 	分数重复时,重复分数按照一名算,即不保留名次空缺,及名次连续
    
    select s1.*, c.cname, s2.score 
    from students s1, scores s2, courses c 
    where 
    -- 需要完整代码请添加文章底部微信,付费咨询
    
    

    4.4 创建一个学生视图,要求显示学生学号、姓名、班级、性别、专业、各科成绩、平均分、总分

    DROP VIEW IF EXISTS v_students_info;
    
    CREATE VIEW v_students_info AS
    SELECT  stu.sid,
    		stu.sname,
    		stu.sclass,
    		stu.sgender,
    		stu.smajor,
    		sum(if(c.cname = "变形", sc.score, 0)) AS "变形",
    		sum(if(c.cname = "时空穿梭", sc.score, 0)) AS "时空穿梭",
    		sum(if(c.cname = "分解术", sc.score, 0)) AS "分解术",
    		sum(if(c.cname = "炼器", sc.score, 0)) AS "炼器",
    		sum(if(c.cname = "炼丹", sc.score, 0)) AS "炼丹",
    		sum(if(c.cname = "飞行", sc.score, 0)) AS "飞行",
    		round(ifnull(avg(sc.score), 0), 2) AS "平均分",
    		ifnull(sum(sc.score), 0) AS "总分"
    FROM 	students stu LEFT JOIN
    		scores sc ON stu.sid = sc.sid LEFT JOIN
    		courses c ON c.cid = sc.cid
    GROUP BY stu.sid;
    

    查看视图:
    在这里插入图片描述
    在这里插入图片描述

    4.5 创建函数

    4.5.1 创建一个通过学号sid获取学生信息的函数

    DROP FUNCTION IF EXISTS get_student_info_by_sid;
    
    DELIMITER //
    CREATE DEFINER = CURRENT_USER FUNCTION get_student_info_by_sid(id INT)
    RETURNS VARCHAR(300)
    -- 需要完整代码请添加文章底部微信,付费咨询
    END//
    DELIMITER ;
    -- 调用函数
    SELECT get_student_info_by_sid(8);
    

    调用函数结果:
    在这里插入图片描述
    其中DELIMITER 先定义结束符为 // , 然后又将结束符改为mysql默认的分号结束符。

    了解delimiter 关键字请点击:
    MySQL中 delimiter 关键字详解
    如果出现报错1418:

    Error Code : 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

    参考下面:
    MySQL ERROR 1418 的解决方法

    4.5.2 自定义函数 2:要求函数体中包含其中一种流程控制语句,要求输入学生学号sid、课程编号,显示学生姓名、课程名称、成绩是否及格(即成绩>=60)

    
    DROP FUNCTION IF EXISTS get_student_scores_by_id;
    
    DELIMITER //
    CREATE DEFINER = CURRENT_USER FUNCTION get_student_scores_by_id(sid INT, cid INT)
    RETURNS VARCHAR(300)
    DETERMINISTIC
    BEGIN
    	-- 多个变量要分开声明,否则会报错
    	DECLARE score INT;
    -- 需要完整代码请添加文章底部微信,付费咨询
    	END IF;
    END//
    DELIMITER ;
    
    -- 调用函数
    SELECT get_student_scores_by_id(1, 2);
    
    

    调用函数结果:
    在这里插入图片描述

    4.6 创建存储过程

    4.6.1 学生每选修一门课,如果该门课程成绩达到60分及以上,则把该门课程学分加到学生学分里面,输出该学生姓名、学分

    DROP PROCEDURE IF EXISTS add_scores;
    
    DELIMITER //
    CREATE DEFINER = CURRENT_USER PROCEDURE add_scores(
    -- 需要完整代码请添加文章底部微信,付费咨询
    COMMIT;
    END//
    DELIMITER ;
    
    -- 测试调用存储过程
    SELECT * FROM students WHERE sid > 10;
    CALL add_scores(11, 2, 33, @name, @s_credit_point);
    SELECT @name, @s_credit_point;
    CALL add_scores(12, 2, 88, @name, @s_credit_point);
    SELECT @name, @s_credit_point;
    
    

    调用存储过程结果:
    在这里插入图片描述

    4.7 创建触发器

    4.7.1 创建一个更新学生学分的触发器,如果该学生分数>=60,则给该学生加上这门课的学分

    语法
    create trigger triggerName
    after/before insert/update/delete on 表名 for each row #这句话在mysql是固定的 begin
    sql语句;
    end;

    
    DROP TRIGGER IF EXISTS update_credit_point;
    
    DELIMITER //
    CREATE TRIGGER update_credit_point
    AFTER INSERT ON scores FOR EACH ROW
    -- 需要完整代码请添加文章底部微信,付费咨询
    	END IF;
    END//
    DELIMITER ;
    
    
    -- 测试数据
    SELECT * FROM students WHERE sid < 3;
    INSERT INTO scores VALUES
    (1, 4, 77),
    (2, 4, 55);
    SELECT * FROM students;
    

    测试结果:
    在这里插入图片描述

    三、知识点

    1. in 和 exists 的用法和区别

    mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的。这个是要区分环境的。

    如果查询的两个表大小相当,那么用in和exists差别不大。
    如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。

    not in 和not exists: 如果查询语句使用了not in,那么内外表都进行全表扫描,没有用到索引;而not extsts的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。

    详情参考:
    MySQL中In与Exists的区别
    MySQL数据库面试题(2020最新版)

    2. rank() 与 dense_rank() 用法

    row_number():返回的是行信息,没有排名;
    dense_rank():返回的相关名次不会跳跃,名次保持连续;
    rank():返回的返回的相关名次会跳跃,名次不连续;

    详情参考:
    rank()、dense_rank()、row_number() 使用详解

    3. 数据库关系代数表达式学习

    点击:数据库关系代数表达式学习

    展开全文
  • 经典SQL练习题(MySQL版)

    万次阅读 多人点赞 2018-09-08 14:26:47
    网上有一篇关于SQL的经典文章,超经典SQL练习题,做完这些你的SQL就过关了,引用和分析它的人很多,于是今天复习SQL的时候找来练了练手。原作者用的是SQL Server 2008,我在这里用的是MySQL 8.0.11(二者语法差别...

    原文首发于简书于[2018.07.30]


    网上有一篇关于SQL的经典文章,超经典SQL练习题,做完这些你的SQL就过关了,引用和分析它的人很多,于是今天复习SQL的时候找来练了练手。原作者用的是SQL Server 2008,我在这里用的是MySQL 8.0.11(二者语法差别不大),文本编辑器用的是Atom 1.28.2(不知道大家用什么,反正用Atom写SQL确实丝质顺滑)。

    题目顺序和原文一致,但是我没有把所有题目都解一遍,因为很多题目是重复的。在每道题题目下我除了放SQL语句外,还把MySQL的运行输出结果放了上来,展示效果更直观一些。另外,因为数据量非常小,所以就没考虑SQL语句的性能优化,只求顺利完成题目,并尽可能写得简单些。

    开始之前,先从SQL常见的一些面试题(太有用啦)搬运几道我认为很不错的经典题目过来,这些题目的解法体现出来的方法和思路可以适用于本文的绝大部分题目,是必备的基础。

    1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

    name course grade
    张三 语文 81
    张三 数学 75
    李四 语文 76
    李四 数学 90
    王五 语文 81
    王五 数学 100
    王五 英语 90

    select name from table group by name having min(grade) > 80
    

    2. 现有学生表如下:
    自动编号 学号 姓名 课程编号 课程名称 分数
    1 2005001 张三 0001 数学 69
    2 2005002 李四 0001 数学 89
    3 2005001 张三 0001 数学 69
    删除除了自动编号不同, 其他都相同的学生冗余信息

    delete from tablename where 自动编号 not in (
        select min( 自动编号) 
        from tablename 
        group by 学号, 姓名, 课程编号, 课程名称, 分数
    )
    

    3. 一个叫 team 的表,里面只有一个字段name, 一共有4 条纪录,分别是a,b,c,d, 对应四个球对,现在四个球对进行比赛,用一条sql 语句显示所有可能的比赛组合

    select a.name, b.name
    from team a, team b 
    where a.name < b.name
    

    4. 请用SQL 语句实现:从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。
    请注意:TestDB 中有很多科目,都有1~12月份的发生额。
    AccID :科目代码,Occmonth :发生额月份,DebitOccur :发生额。
    数据库名:JcyAudit ,数据集:Select * from TestDB

    select a.*
    from TestDB a, 
        (select Occmonth, max(DebitOccur) as Debit101ccur 
        from TestDB 
        where AccID='101' 
        group by Occmonth) b
    where a.Occmonth = b.Occmonth and a.DebitOccur > b.Debit101ccur
    

    5. 怎么把这样一个数据表
    year month amount
    1991 1 1.1
    1991 2 1.2
    1991 3 1.3
    1991 4 1.4
    1992 1 2.1
    1992 2 2.2
    1992 3 2.3
    1992 4 2.4
    查成这样一个结果?
    year m1 m2 m3 m4
    1991 1.1 1.2 1.3 1.4
    1992 2.1 2.2 2.3 2.4

    select year, 
        (select amount from table m where month=1 and m.year=table.year) as m1,
        (select amount from table m where month=2 and m.year=table.year) as m2,
        (select amount from table m where month=3 and m.year=table.year) as m3,
        (select amount from table m where month=4 and m.year=table.year) as m4
    from table group by year
    

    6. 有表A,结构如下:
    p_ID p_Num s_id
    1 10 01
    1 12 02
    2 8 01
    3 11 01
    3 8 03
    其中:p_ID为产品ID,p_Num为产品库存量,s_id为仓库ID。
    请用SQL语句实现将上表中的数据合并,合并后的数据为:
    p_ID s1_id s2_id s3_id
    1 10 12 0
    2 8 0 0
    3 11 0 8
    其中:s1_id为仓库1的库存量,s2_id为仓库2的库存量,s3_id为仓库3的库存量。如果该产品在某仓库中无库存量,那么就是0代替。

    select p_id,
        sum(case when s_id=1 then p_num else 0 end) as s1_id,
        sum(case when s_id=2 then p_num else 0 end) as s2_id,
        sum(case when s_id=3 then p_num else 0 end) as s3_id
    from myPro group by p_id
    

    下面进入正题。首先创建数据表:

    学生表 Student

    create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '1990-12-21' , '男');
    insert into Student values('03' , '孙风' , '1990-05-20' , '男');
    insert into Student values('04' , '李云' , '1990-08-06' , '男');
    insert into Student values('05' , '周梅' , '1991-12-01' , '女');
    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
    insert into Student values('08' , '王菊' , '1990-01-20' , '女')
    

    成绩表 SC

    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)
    
    

    课程表 Course

    create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03')
    

    教师表 Teacher

    create table Teacher(Tid varchar(10),Tname varchar(10));
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五')
    

    四张表之间的关联很简单:

    表格关联

    (以下题目的顺序和原文相对应)

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

    select s.*, a.score as score_01, b.score as score_02
    from student s,
         (select sid, score from sc where cid=01) a,
         (select sid, score from sc where cid=02) b
    where a.sid = b.sid and a.score > b.score and s.sid = a.sid
    
    +------+--------+---------------------+------+----------+----------+
    | Sid  | Sname  | Sage                | Ssex | score_01 | score_02 |
    +------+--------+---------------------+------+----------+----------+
    | 02   | 钱电   | 1990-12-21 00:00:00 ||     70.0 |     60.0 |
    | 04   | 李云   | 1990-08-06 00:00:00 ||     50.0 |     30.0 |
    +------+--------+---------------------+------+----------+----------+
    2 rows in set (0.00 sec)
    

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

    select s.sid, sname, avg(score) as avg_score
    from student as s, sc
    where s.sid = sc.sid
    group by s.sid
    having avg_score > 60
    
    +------+--------+-----------+
    | sid  | sname  | avg_score |
    +------+--------+-----------+
    | 01   | 赵雷   |  89.66667 |
    | 02   | 钱电   |  70.00000 |
    | 03   | 孙风   |  80.00000 |
    | 05   | 周梅   |  81.50000 |
    | 07   | 郑竹   |  93.50000 |
    +------+--------+-----------+
    5 rows in set (0.00 sec)
    

    3. 查询在 SC 表存在成绩的学生信息

    select * from student where sid in (select sid from sc where score is not null)
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 01   | 赵雷   | 1990-01-01 00:00:00 ||
    | 02   | 钱电   | 1990-12-21 00:00:00 ||
    | 03   | 孙风   | 1990-05-20 00:00:00 ||
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    | 05   | 周梅   | 1991-12-01 00:00:00 ||
    | 06   | 吴兰   | 1992-03-01 00:00:00 ||
    | 07   | 郑竹   | 1989-07-01 00:00:00 ||
    +------+--------+---------------------+------+
    7 rows in set (0.00 sec)
    

    4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

    这道题得用到left join或者right join,不能用where连接,因为题目说了要求有显示为null的,where是inner join,不会出现null,在这道题里会查不出第08号学生。

    select s.sid, s.sname, count(cid) as 选课总数, sum(score) as 总成绩
    from student as s left join sc
    on s.sid = sc.sid
    group by s.sid
    
    +------+--------+--------------+-----------+
    | sid  | sname  | 选课总数     | 总成绩    |
    +------+--------+--------------+-----------+
    | 01   | 赵雷   |            3 |     269.0 |
    | 02   | 钱电   |            3 |     210.0 |
    | 03   | 孙风   |            3 |     240.0 |
    | 04   | 李云   |            3 |     100.0 |
    | 05   | 周梅   |            2 |     163.0 |
    | 06   | 吴兰   |            2 |      65.0 |
    | 07   | 郑竹   |            2 |     187.0 |
    | 08   | 王菊   |            0 |      NULL |
    +------+--------+--------------+-----------+
    8 rows in set (0.00 sec)
    

    4.1 查有成绩的学生信息

    select s.sid, s.sname, count(*) as 选课总数, sum(score) as 总成绩,
        sum(case when cid = 01 then score else null end) as score_01,
        sum(case when cid = 02 then score else null end) as score_02,
        sum(case when cid = 03 then score else null end) as score_03
    from student as s, sc
    where s.sid = sc.sid
    group by s.sid
    
    +------+--------+--------------+-----------+----------+----------+----------+
    | sid  | sname  | 选课总数     | 总成绩    | score_01 | score_02 | score_03 |
    +------+--------+--------------+-----------+----------+----------+----------+
    | 01   | 赵雷   |            3 |     269.0 |     80.0 |     90.0 |     99.0 |
    | 02   | 钱电   |            3 |     210.0 |     70.0 |     60.0 |     80.0 |
    | 03   | 孙风   |            3 |     240.0 |     80.0 |     80.0 |     80.0 |
    | 04   | 李云   |            3 |     100.0 |     50.0 |     30.0 |     20.0 |
    | 05   | 周梅   |            2 |     163.0 |     76.0 |     87.0 |     NULL |
    | 06   | 吴兰   |            2 |      65.0 |     31.0 |     NULL |     34.0 |
    | 07   | 郑竹   |            2 |     187.0 |     NULL |     89.0 |     98.0 |
    +------+--------+--------------+-----------+----------+----------+----------+
    7 rows in set (0.00 sec)
    

    5. 查询「李」姓老师的数量

    select count(tname) from teacher where tname like '李%'
    
    +--------------+
    | count(tname) |
    +--------------+
    |            1 |
    +--------------+
    1 row in set (0.00 sec)
    

    6. 查询学过「张三」老师授课的同学的信息

    select * from student where sid in (
        select sid from sc, course, teacher
        where sc.cid = course.cid
         and course.tid = teacher.tid
         and tname = '张三'
    )
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 01   | 赵雷   | 1990-01-01 00:00:00 ||
    | 02   | 钱电   | 1990-12-21 00:00:00 ||
    | 03   | 孙风   | 1990-05-20 00:00:00 ||
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    | 05   | 周梅   | 1991-12-01 00:00:00 ||
    | 07   | 郑竹   | 1989-07-01 00:00:00 ||
    +------+--------+---------------------+------+
    6 rows in set (0.00 sec)
    

    原作者的写法里面用到了等号 =,虽然得到同样的结果,但是这样写不太好,因为不确定张三老师是不是只教授一门课(只不过现在的数据量太小了而已),in 适用于一个或多个返回结果的情况,适应性比等号更广。

    select * from Student
    where sid in(select distinct Sid from SC
    where cid=(select Cid from Course
    where Tid=(select Tid from Teacher where Tname='张三')))
    

    7. 查询没有学全所有课程的同学的信息

    select * from student where sid in (select sid from sc group by sid having count(cid) < 3)
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 05   | 周梅   | 1991-12-01 00:00:00 ||
    | 06   | 吴兰   | 1992-03-01 00:00:00 ||
    | 07   | 郑竹   | 1989-07-01 00:00:00 ||
    +------+--------+---------------------+------+
    3 rows in set (0.00 sec)
    

    9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
    这道题号称是所有题目里最难的一道,我虽然做了出来,但是写法很麻烦,不必要。原作者写的很简洁:

    select * from Student
    where Sid in(
        select Sid from SC
        where Cid in (select Cid from SC where Sid = '01') and Sid <>'01'
        group by Sid
        having COUNT(Cid)>=3
    )
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 02   | 钱电   | 1990-12-21 00:00:00 ||
    | 03   | 孙风   | 1990-05-20 00:00:00 ||
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    +------+--------+---------------------+------+
    3 rows in set (0.00 sec)
    

    我写的就太麻烦啦。。

    select * from student where sid in (
        select B.sid
        from
            (select sid,
                sum(case when cid=01 then 1 else 0 end) as course_01,
                sum(case when cid=02 then 1 else 0 end) as course_02,
                sum(case when cid=03 then 1 else 0 end) as course_03
            from sc where sid = 01 group by sid) as A,
            (select sid,
                sum(case when cid=01 then 1 else 0 end) as course_01,
                sum(case when cid=02 then 1 else 0 end) as course_02,
                sum(case when cid=03 then 1 else 0 end) as course_03
            from sc where sid != 01 group by sid) as B
        where A.course_01=B.course_01 and A.course_02=B.course_02 and A.course_03=B.course_03
    )
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 02   | 钱电   | 1990-12-21 00:00:00 ||
    | 03   | 孙风   | 1990-05-20 00:00:00 ||
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    +------+--------+---------------------+------+
    3 rows in set (0.00 sec)
    

    8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

    和第9题基本一致,还是原作者写的好一些

    select * from Student where Sid in(
        select distinct Sid from SC where Cid in(
            select Cid from SC where Sid='01'
        )
    )
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 01   | 赵雷   | 1990-01-01 00:00:00 ||
    | 02   | 钱电   | 1990-12-21 00:00:00 ||
    | 03   | 孙风   | 1990-05-20 00:00:00 ||
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    | 05   | 周梅   | 1991-12-01 00:00:00 ||
    | 06   | 吴兰   | 1992-03-01 00:00:00 ||
    | 07   | 郑竹   | 1989-07-01 00:00:00 ||
    +------+--------+---------------------+------+
    7 rows in set (0.00 sec)
    

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

    一般涉及到"任意"的都会用到not in这样的取反的结构:

    select sname from student
    where sname not in (
        select s.sname
        from student as s, course as c, teacher as t, sc
        where s.sid = sc.sid
            and sc.cid = c.cid
            and c.tid = t.tid
            and t.tname = '张三'
    )
    
    +--------+
    | sname  |
    +--------+
    | 吴兰   |
    | 王菊   |
    +--------+
    2 rows in set (0.00 sec)
    

    11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

    select s.sid, s.sname, avg(score)
    from student as s, sc
    where s.sid = sc.sid and score<60
    group by s.sid
    having count(score)>=2
    
    +------+--------+------------+
    | sid  | sname  | avg(score) |
    +------+--------+------------+
    | 04   | 李云   |   33.33333 |
    | 06   | 吴兰   |   32.50000 |
    +------+--------+------------+
    2 rows in set (0.00 sec)
    

    12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息

    select s.* ,score
    from student as s, sc
    where cid = 01
      and score < 60
      and s.sid=sc.sid
    order by score desc
    
    +------+--------+---------------------+------+-------+
    | Sid  | Sname  | Sage                | Ssex | score |
    +------+--------+---------------------+------+-------+
    | 04   | 李云   | 1990-08-06 00:00:00 ||  50.0 |
    | 06   | 吴兰   | 1992-03-01 00:00:00 ||  31.0 |
    +------+--------+---------------------+------+-------+
    2 rows in set (0.00 sec)
    

    13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

    select sid,
        sum(case when cid=01 then score else null end) as score_01,
        sum(case when cid=02 then score else null end) as score_02,
        sum(case when cid=03 then score else null end) as score_03,
        avg(score)
    from sc group by sid
    order by avg(score) desc
    
    +------+----------+----------+----------+------------+
    | sid  | score_01 | score_02 | score_03 | avg(score) |
    +------+----------+----------+----------+------------+
    | 07   |     NULL |     89.0 |     98.0 |   93.50000 |
    | 01   |     80.0 |     90.0 |     99.0 |   89.66667 |
    | 05   |     76.0 |     87.0 |     NULL |   81.50000 |
    | 03   |     80.0 |     80.0 |     80.0 |   80.00000 |
    | 02   |     70.0 |     60.0 |     80.0 |   70.00000 |
    | 04   |     50.0 |     30.0 |     20.0 |   33.33333 |
    | 06   |     31.0 |     NULL |     34.0 |   32.50000 |
    +------+----------+----------+----------+------------+
    7 rows in set (0.00 sec)
    

    14. 查询各科成绩最高分、最低分和平均分,以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率(及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90)。
    要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

    这道题熟练掌握case和sum的用法就没什么问题

    select c.cid as 课程号, c.cname as 课程名称, count(*) as 选修人数,
        max(score) as 最高分, min(score) as 最低分, avg(score) as 平均分,
        sum(case when score >= 60 then 1 else 0 end)/count(*) as 及格率,
        sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(*) as 中等率,
        sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(*) as 优良率,
        sum(case when score >= 90 then 1 else 0 end)/count(*) as 优秀率
    from sc, course c
    where c.cid = sc.cid
    group by c.cid
    order by count(*) desc, c.cid asc
    
    +-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
    | 课程号    | 课程名称      | 选修人数      | 最高分     | 最低分    | 平均分     | 及格率    | 中等率    | 优良率     | 优秀率     |
    +-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
    | 01        | 语文         |            6 |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
    | 02        | 数学         |            6 |      90.0 |      30.0 |  72.66667 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
    | 03        | 英语         |            6 |      99.0 |      20.0 |  68.50000 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
    +-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
    3 rows in set (0.00 sec)
    

    原作者的写法本质上和我是相同的,但是用了很多left join看起来有些冗余

    select distinct A.Cid,Cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 from SC A
    left join Course on A.Cid=Course.Cid
    left join (select Cid,MAX(score)最高分,MIN(score)最低分,AVG(score)平均分 from SC group by Cid)B on A.Cid=B.Cid
    left join (select Cid,(convert(decimal(5,2),(sum(case when score>=60 then 1 else 0 end)*1.00)/COUNT(*))*100)及格率 from SC group by Cid)C on A.Cid=C.Cid
    left join (select Cid,(convert(decimal(5,2),(sum(case when score >=70 and score<80 then 1 else 0 end)*1.00)/COUNT(*))*100)中等率 from SC group by Cid)D on A.Cid=D.Cid
    left join (select Cid,(convert(decimal(5,2),(sum(case when score >=80 and score<90 then 1 else 0 end)*1.00)/COUNT(*))*100)优良率 from SC group by Cid)E on A.Cid=E.Cid
    left join (select Cid,(convert(decimal(5,2),(sum(case when score >=90 then 1 else 0 end)*1.00)/COUNT(*))*100)优秀率
    from SC group by Cid)F on A.Cid=F.Cid
    

    15. 按平均成绩进行排序,显示总排名和各科排名,Score 重复时保留名次空缺

    原题目是按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺。但是我没看明白什么意思,各科成绩如何排序?语文分数和数学分数有可比性吗?作者的写法是select *,RANK()over(order by score desc)排名 from SC,把所有的成绩都放到一块儿排序了,这没有意义,不可比。于是我修改了一下题目。

    select s.*, rank_01, rank_02, rank_03, rank_total
    from student s
    left join (select sid, rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
    left join (select sid, rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
    left join (select sid, rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
    left join (select sid, rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
    order by rank_total asc
    
    +------+--------+---------------------+------+---------+---------+---------+------------+
    | Sid  | Sname  | Sage                | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
    +------+--------+---------------------+------+---------+---------+---------+------------+
    | 08   | 王菊   | 1990-01-20 00:00:00 ||    NULL |    NULL |    NULL |       NULL |
    | 07   | 郑竹   | 1989-07-01 00:00:00 ||    NULL |       2 |       2 |          1 |
    | 01   | 赵雷   | 1990-01-01 00:00:00 ||       1 |       1 |       1 |          2 |
    | 05   | 周梅   | 1991-12-01 00:00:00 ||       3 |       3 |    NULL |          3 |
    | 03   | 孙风   | 1990-05-20 00:00:00 ||       1 |       4 |       3 |          4 |
    | 02   | 钱电   | 1990-12-21 00:00:00 ||       4 |       5 |       3 |          5 |
    | 04   | 李云   | 1990-08-06 00:00:00 ||       5 |       6 |       6 |          6 |
    | 06   | 吴兰   | 1992-03-01 00:00:00 ||       6 |    NULL |       5 |          7 |
    +------+--------+---------------------+------+---------+---------+---------+------------+
    8 rows in set (0.00 sec)
    

    15.1 按平均成绩进行排序,显示总排名和各科排名,Score 重复时合并名次

    同样修改了一下题目。15题和15.1题的指向很明确了,就是rank()和dense_rank()的区别,也就是两个并列第一名之后的那个人是第三名(rank)还是第二名(dense_rank)的区别。

    select s.*, rank_01, rank_02, rank_03, rank_total
    from student s
    left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
    left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
    left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
    left join (select sid, dense_rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
    order by rank_total asc
    
    +------+--------+---------------------+------+---------+---------+---------+------------+
    | Sid  | Sname  | Sage                | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
    +------+--------+---------------------+------+---------+---------+---------+------------+
    | 08   | 王菊   | 1990-01-20 00:00:00 ||    NULL |    NULL |    NULL |       NULL |
    | 07   | 郑竹   | 1989-07-01 00:00:00 ||    NULL |       2 |       2 |          1 |
    | 01   | 赵雷   | 1990-01-01 00:00:00 ||       1 |       1 |       1 |          2 |
    | 05   | 周梅   | 1991-12-01 00:00:00 ||       2 |       3 |    NULL |          3 |
    | 03   | 孙风   | 1990-05-20 00:00:00 ||       1 |       4 |       3 |          4 |
    | 02   | 钱电   | 1990-12-21 00:00:00 ||       3 |       5 |       3 |          5 |
    | 04   | 李云   | 1990-08-06 00:00:00 ||       4 |       6 |       5 |          6 |
    | 06   | 吴兰   | 1992-03-01 00:00:00 ||       5 |    NULL |       4 |          7 |
    +------+--------+---------------------+------+---------+---------+---------+------------+
    8 rows in set (0.00 sec)
    

    17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比

    select c.cid as 课程编号, c.cname as 课程名称, A.*
    from course as c,
    (select cid,
        sum(case when score >= 85 then 1 else 0 end)/count(*) as 100_85,
        sum(case when score >= 70 and score < 85 then 1 else 0 end)/count(*) as 85_70,
        sum(case when score >= 60 and score < 70 then 1 else 0 end)/count(*) as 70_60,
        sum(case when score < 60 then 1 else 0 end)/count(*) as 60_0
    from sc group by cid) as A
    where c.cid = A.cid
    
    +--------------+--------------+------+--------+--------+--------+--------+
    | 课程编号     | 课程名称      | cid  | 100_85 | 85_70  | 70_60  | 60_0   |
    +--------------+--------------+------+--------+--------+--------+--------+
    | 01           | 语文         | 01   | 0.0000 | 0.6667 | 0.0000 | 0.3333 |
    | 02           | 数学         | 02   | 0.5000 | 0.1667 | 0.1667 | 0.1667 |
    | 03           | 英语         | 03   | 0.3333 | 0.3333 | 0.0000 | 0.3333 |
    +--------------+--------------+------+--------+--------+--------+--------+
    3 rows in set (0.00 sec)
    

    18. 查询各科成绩前三名的记录

    这是我比较喜欢的一道题目,非常经典。

    select * from (select *, rank() over(partition by cid order by score desc) as graderank from sc) A 
    where A.graderank <= 3
    
    | Sid  | Cid  | score | graderank |
    +------+------+-------+-----------+
    | 01   | 01   |  80.0 |         1 |
    | 03   | 01   |  80.0 |         1 |
    | 05   | 01   |  76.0 |         3 |
    | 01   | 02   |  90.0 |         1 |
    | 07   | 02   |  89.0 |         2 |
    | 05   | 02   |  87.0 |         3 |
    | 01   | 03   |  99.0 |         1 |
    | 07   | 03   |  98.0 |         2 |
    | 02   | 03   |  80.0 |         3 |
    | 03   | 03   |  80.0 |         3 |
    +------+------+-------+-----------+
    10 rows in set (0.00 sec)
    

    20. 查询出只选修两门课程的学生学号和姓名

    select s.sid, s.sname, count(cid)
    from student s, sc
    where s.sid = sc.sid
    group by s.sid
    having count(cid)=2
    
    +------+--------+------------+
    | sid  | sname  | count(cid) |
    +------+--------+------------+
    | 05   | 周梅   |          2 |
    | 06   | 吴兰   |          2 |
    | 07   | 郑竹   |          2 |
    +------+--------+------------+
    3 rows in set (0.00 sec)
    

    22. 查询名字中含有「风」字的学生信息

    select * from student where sname like '%风%'
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 03   | 孙风   | 1990-05-20 00:00:00 ||
    +------+--------+---------------------+------+
    1 row in set (0.00 sec)
    

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

    select * from student where year(sage) = 1990
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 01   | 赵雷   | 1990-01-01 00:00:00 ||
    | 02   | 钱电   | 1990-12-21 00:00:00 ||
    | 03   | 孙风   | 1990-05-20 00:00:00 ||
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    | 08   | 王菊   | 1990-01-20 00:00:00 ||
    +------+--------+---------------------+------+
    5 rows in set (0.00 sec)
    

    33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

    select s.*, max(score)
    from student s, teacher t, course c, sc
    where s.sid = sc.sid
        and sc.cid = c.cid
        and c.tid = t.tid
        and t.tname = '张三'
    
    +------+--------+---------------------+------+------------+
    | Sid  | Sname  | Sage                | Ssex | max(score) |
    +------+--------+---------------------+------+------------+
    | 01   | 赵雷   | 1990-01-01 00:00:00 ||       90.0 |
    +------+--------+---------------------+------+------------+
    1 row in set (0.00 sec)
    

    34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

    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
    
    +------+------+-------+---+
    | Sid  | Cid  | score | A |
    +------+------+-------+---+
    | 01   | 02   |  90.0 | 1 |
    +------+------+-------+---+
    1 row in set (0.00 sec)
    

    40. 查询各学生的年龄,只按年份来算

    select sname, year(now())-year(sage) as age from student
    
    +--------+------+
    | sname  | age  |
    +--------+------+
    | 赵雷   |   28 |
    | 钱电   |   28 |
    | 孙风   |   28 |
    | 李云   |   28 |
    | 周梅   |   27 |
    | 吴兰   |   26 |
    | 郑竹   |   29 |
    | 王菊   |   28 |
    +--------+------+
    8 rows in set (0.00 sec)
    

    41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一

    select sname, timestampdiff(year, sage, now()) as age from student
    
    +--------+------+
    | sname  | age  |
    +--------+------+
    | 赵雷   |   28 |
    | 钱电   |   27 |
    | 孙风   |   28 |
    | 李云   |   27 |
    | 周梅   |   26 |
    | 吴兰   |   26 |
    | 郑竹   |   29 |
    | 王菊   |   28 |
    +--------+------+
    8 rows in set (0.00 sec)  
    

    42. 查询本周过生日的学生

    select * from student where week(now()) = week(sage)
    
    Empty set (0.00 sec)
    

    43. 查询下周过生日的学生

    select * from student where (week(now())+1) = week(sage)
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    +------+--------+---------------------+------+
    1 row in set (0.00 sec)
    

    44. 查询本月过生日的学生

    select * from student where month(now()) = month(sage)
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 07   | 郑竹   | 1989-07-01 00:00:00 ||
    +------+--------+---------------------+------+
    1 row in set (0.00 sec)
    

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

    select * from student where (month(now())+1) = month(sage)
    
    +------+--------+---------------------+------+
    | Sid  | Sname  | Sage                | Ssex |
    +------+--------+---------------------+------+
    | 04   | 李云   | 1990-08-06 00:00:00 ||
    +------+--------+---------------------+------+
    1 row in set (0.00 sec)
    


    展开全文
  • SQL练习题.sql

    2021-07-05 12:52:33
    SQL练习题.sql
  • SQL练习题

    千次阅读 2021-01-06 15:11:39
    第一 用一条SQL语句获得全家门店销量表(Sales)中饮料产品(Product)销量(Qty)均小于50瓶的门店(Store)(没有出现在销量表中的产品可以忽略)。 解题: select Store,Product,Qty from Sales where Qty<50 ...

    第一题
    用一条SQL语句获得全家门店销量表(Sales)中饮料产品(Product)销量(Qty)均小于50瓶的门店(Store)(没有出现在销量表中的产品可以忽略)。
    在这里插入图片描述
    解题:

    select Store,Product,Qty from Sales where Qty<50

    结果:
    在这里插入图片描述

    第二题
    请使用SQL语句获得门店销量表(Sales2)中每个产品(Product)销量(Qty)最小的门店(Store)。
    在这里插入图片描述
    解题:

    select Sales2.Store as Store,Sales2.Product as Product ,Sales2.Qty as Qty from Sales2 , (select min(Qty) as Qty , Product from Sales2 group by Product ) Sales1 where Sales1.Qty = Sales2.Qty and Sales1.Product=Sales2.Product;

    结果:
    在这里插入图片描述

    第三题
    有两个销售人员负责全家泰兴路店和石门二路店的饮料销售。
    销售A负责白天,销售B负责晚上,个人指标见SalesPlan表,门店销售业绩见Sales4表。
    门店实际销售按照SalesRate中的比例分别算给这2位销售。
    要求算出销售A和B在两个门店的业绩完成率,结果以XX%显示(四舍五入, 无需小数位):完成率公式:销售额/指标*100%
    在这里插入图片描述
    在这里插入图片描述
    解题:

    SELECT salesman ,store, concat(LEFT (qtysalesrate/salesplan100,5),’%’) AS 完成率 FROM (
    SELECT a.area,a.store,b.salesman, a.qty ,c.salesrate,b.salesplan FROM sales4 a INNER JOIN salesplan b ON a.store =b.store
    INNER JOIN salesrate c ON a.store=c.store and b.salesman=c.salesman )tt;
    结果:
    在这里插入图片描述

    第四题
    请用下面3张数据表计算出各医院(Hospital)的所有月份的YTD销量(SalesQty)YTD销量(SalesQty)定义为:从当年第一个月开始,一直到当年的当前月份的累计销量
    例如:2013年1月的YTD销量为201301-201301的累计销量 ,2013年12月的YTD销量为201301-201312的累计销量。
    解题:

    select q.store , q.ym ,q.qty ,(select SUM(qty) from sales3 as i where i.ym<=q.ym and i.store=q.store) as res from (select a.store , b.ym ,a.qty from sales3 a left join Calendar b on a.ym=b.ym) q;
    结果:
    在这里插入图片描述

    展开全文
  • SQL练习题共50道附答案(MySQL)

    千次阅读 2020-08-31 19:33:52
    SQL练习题共50道附答案(MySQL) 数据库表名称和字段 1.学生表 student(s_id,s_name,s_birth,s_sex) -- 学生编号,学生姓名, 出生年月,学生性别 2.课程表 course(c_id,c_name,t_id) -- 课程编号, 课程名称,...
  • SQL数据库多表查询练习题附加数据库
  • SQL练习习题_20211207_162002.sql
  • Hive SQL练习题

    2020-07-10 10:01:36
  • 数据库SQL练习题

    2021-01-19 16:35:06
    2013年(下)全国信息技术水平考试数据库应用系统设计技术水平证书(SQL Server)考试试卷 数据库应用(SQL Server)练习题 一题:单选 1. 在Transact-SQL中,下列命令能
  • sql练习题+答案.doc

    2021-10-10 22:17:22
    sql练习题+答案.doc
  • 高级Mysql强化训练题库
  • sql练习题.md

    2021-04-23 14:42:18
    sql练习题.md
  • sql练习题及答案.docx

    2020-05-10 22:40:08
    sql练习题及答案 篇一sql习题集及答案 问题描述 为管理岗位业务培训信息建立3个表 s (s,sn,sd,sa)s,sn,sd,sa分别代表学号学员姓名所属单位 学员年龄 c (c,cn ) c,cn分别代表课程编号.课程名称 sc ( s,c,g ) s,c,g...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,124
精华内容 16,849
关键字:

sql练习题