【校招VIP】数据库基础之sql五十道题

最新推荐文章于 2024-07-25 17:00:09 发布

校招VIP

最新推荐文章于 2024-07-25 17:00:09 发布

阅读量995

点赞数

分类专栏：测试专题文章标签：功能测试

本文链接：https://blog.csdn.net/shuize123/article/details/124406757

版权

测试专题专栏收录该内容

92 篇文章 0 订阅

订阅专栏

考点介绍：

数据库在测试工程师的面试过程中也是常问的一项。面试有一定的层次性，如bat级别公司每个点都会深入，而有些公司则只会问到表层，所以每个领域都分为必须掌握和深入了解这两个部分。

本期分享的数据库基础之sql五十道题，分为试题、文章以及视频三部分。

答案详情解析和文章内容可扫下方二维码或链接即可查看！

一、考点题目

测试用数据表

1. 学生表 Student

--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-12-20' , '男');

insert into Student values('04' , '李云' , '1990-12-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-01-01' , '女');

insert into Student values('07' , '郑竹' , '1989-01-01' , '女');

insert into Student values('09' , '张三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2012-06-06' , '女');

insert into Student values('12' , '赵六' , '2013-06-13' , '女');

insert into Student values('13' , '孙七' , '2014-06-01' , '女');

2. 课程表 Cours

--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')

3. 教师表 Teache

--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' , '王五')

4. 成绩表 SC

--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.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

正确答案：

select Cid, Course.Cname,

sum(case when score >= 90 then 1 else 0 end) * 100 / count(score) as '优秀率',

sum(case when score >= 80 and score < 90 then 1 else 0 end) * 100 / count(score) as '优良率',

sum(case when score >= 70 and score < 80 then 1 else 0 end) * 100 / count(score) as '中等率',

sum(case when score >= 60 then 1 else 0 end) * 100 / count(score) as '及格率',

max(score) as '最高分',

min(score) as '最低分',

avg(score) as '平均分'

from SC group by Cid;

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

正确答案：

select Student.*, SC.score from Student, SC, Course, Teacher

where Student.Sid = SC.Sid and SC.Cid = Course.Cid and Course.Tid = Teacher.Tid and Teacher.Tname = '张三'

order by SC.score desc

limit 1;

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

正确答案：

select a.Cid, a.Sid, a.score, count(b.score) + 1 from

(select * from SC group by Cid, Sid, score) a

left join (select * from SC group by Cid, Sid, score) b

on a.Cid = b.Cid and a.score < b.score

group by a.Cid, a.Sid, a.score having count(b.score) < 3

order by a.Cid asc, a.score desc;

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

正确答案：

select SC.Cid, Course.Cname, count(SC.Sid)

sum(case when score >= 85 then 1 else 0 end) * 100 / count(score) as '[100-85]',

sum(case when score >= 70 and score < 85 then 1 else 0 end) * 100 / count(score) as '[85-70]',

sum(case when score >= 60 and score < 70 then 1 else 0 end) * 100 / count(score) as '[70-60]',

sum(case when score >= 0 and score < 60 then 1 else 0 end) * 100 / count(score) as '[60-0]'

from SC, Course

where SC.Cid = Course.Cid

group by Cid;

5.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

正确答案：

select a.cid, a.sid, a.score, count(b.score)+1 as rank

from SC as a

left join SC as b

on a.score<b.score and a.cid = b.cid

group by a.cid, a.sid, a.score

order by a.cid, rank ASC;

（答案点击下方链接或者扫海报二维码查看哦）