SQL笔试经典50题及答案解析（题⽬21-30）
前⾔
最近博主在准备数据分析岗位的笔试和⾯试，其中的考察重点就是SQL。听说练了这50道题⽬（题⽬和数据来源参考：），什么现场⼿撕SQL统统都不怕，我就赶紧来练习了！壮⼠们，练完以后就什么都不怕啦~
我看了原博主的查询语句，有些不够简洁，有些或许存在问题。下⾯都是我⾃⼰写的查询语句，保证在Navicat上跑得通，但是不排除存在写的不对不好的地⽅，欢迎⼤家提出来，⼀起学习，共同进步。
环境：
Navicat Premium 12
说明：
本篇练习的是题⽬21-30
其余题⽬请参考链接：
⽂章⽬录
创建表
表说明
本次创建的表有四张：
学⽣表（student）： 包含 学⽣号，学⽣姓名，出⽣年⽉，性别 四个字段
教师表（teacher）： 包含教师号，教师姓名 两个字段
成绩表（sc）： 包含学⽣号，课程号，成绩 三个字段
课程表（course）： 包含课程号，课程名，教师号 三个字段
表创建
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(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','⼥');
# 创建课程表
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');
# 创建教师表
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01','张三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');
# 创建成绩表
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);
表显⽰
教师表如下：
成绩表如下：
课程表如下：
题⽬
查询不同⽼师所教不同课程平均分从⾼到低显⽰
SELECT tid,AVG(score)AS avg_score
FROM sc
LEFT JOIN course AS c
ON sc.cid = c.cid
GROUP BY c.tid
ORDER BY avg_score DESC
运⾏结果：
题⽬22
查询所有课程的成绩第2名到第3名的学⽣信息及该课程成绩
# 我习惯考虑并列的情况，所以此处使⽤密集排名dense_rank
SELECT*FROM
(
SELECT*,dense_rank()over(PARTITION BY cid ORDER BY score DESC)AS srank
FROM sc
)
AS t
每天学点sql经典句子WHERE t.srank=2OR t.srank=3
运⾏结果：
题⽬23
统计各科成绩各分数段⼈数：课程编号，课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分⽐
SELECT
sc.cid
,cname
,SUM(CASE WHEN score>=85AND score<=100THEN1ELSE0END)/COUNT(score)AS'[85-100]'
,SUM(CASE WHEN score>=70AND score<85THEN1ELSE0END)/COUNT(score)AS'[70-85 )'
,SUM(CASE WHEN score>=60AND score<70THEN1ELSE0END)/COUNT(score)AS'[60-70 )'
,SUM(CASE WHEN score>=0AND score<60THEN1ELSE0END)/COUNT(score)AS'[0 -60 )'
FROM sc
LEFT JOIN course AS c
ON sc.cid = c.cid
GROUP BY sc.cid
题⽬24
查询学⽣平均成绩及其名次
SELECT
sid
,AVG(score)AS avg_score
,dense_rank()over(ORDER BY AVG(score)DESC)AS srank
FROM sc
GROUP BY sid
运⾏结果：
题⽬25
查询各科成绩前三名的记录
# 我习惯考虑并列的情况，所以此处使⽤密集排名dense_rank
SELECT cid,sid,score,srank FROM
(
SELECT*,dense_rank()over(PARTITION BY cid ORDER BY score DESC)AS srank FROM sc
)AS t
WHERE t.srank<=3
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论