滴滴数据分析实习SQL笔试题
⼀、有两个数据表，请通过SQL实现下⾯题⽬
学⽣表（tb_student）
学⽣姓名（name）学号（id)班级（class）⼊学时间（in_time）年龄（age）性别（sex）专业（major）张三2017C33010012017C3301201718男计算机
李四2017C33010022017C3301201719男计算机
学⽣成绩表（tb_score）大连奥迪
学号（id）课程（course）分数（score）2017C3301001数据库75
武汉油价
2017C3301002数据库85
2017C3301001应⽤数学80
建表语句：
create table tb_student (
name varchar(40)not null,
id varchar(40)not null,摇车牌
class varchar(40)not null,
in_time integer not null,
age integer not null,
sex varchar(40)not null,
major varchar(40)not null,
unique(id)
);
create table tb_score (
id varchar(40)not null,
course varchar(40)not null,
score integer not null
);
插⼊数据：
insert into tb_student values('张三','2017C3301001','2017C3301',2017,18,'男','计算机');
insert into tb_student values('李四','2017C3301002','2017C3301',2017,19,'男','计算机');
沧州车展insert into tb_score values('2017C3301001','数据库',75);
insert into tb_score values('2017C3301002','数据库',85);
insert into tb_score values('2017C3301001','应⽤数学',80);
1.筛选出2017年⼊学的”计算机”专业年龄最⼩的20位同学名单（姓名、学号、班级、年龄）
SELECT name,id,class,age
FROM tb_student
WHERE in_time =2017
AND major ='计算机'
ORDER BY age ASC
LIMIT20
2.统计每个班同学各科成绩平均分⼤于80分的⼈数和⼈数占⽐
⽅法⼀：
SELECT c.class,
SUM(CASE WHEN c.avg_score >80THEN1ELSE0END)AS'统计每个班同学各科成绩平均分⼤于80分的⼈数',
SUM(CASE WHEN c.avg_score >80THEN1ELSE0END)/COUNT(c.id)AS'统计每个班同学各科成绩平均分⼤于80分的⼈数占⽐'
FROM(
-- 先取出每个同学各科平均成绩
SELECT a.id,a.class,avg(b.score)AS avg_score
FROM tb_student a
长城m1INNER JOIN tb_score b
ON a.id=b.id
GROUP BY a.id
) c
GROUP BY c.class
⽅法⼆：
SELECT c.class,
COUNT(IF(c.avg_score >80,1,NULL))AS'统计每个班同学各科成绩平均分⼤于80分的⼈数',
COUNT(IF(c.avg_score >80,1,NULL))/COUNT(c.id)AS'统计每个班同学各科成绩平均分⼤于80分的⼈数占⽐'
FROM(
-- 先取出每个同学各科平均成绩
SELECT a.id,a.class,avg(b.score)AS avg_score
FROM tb_student a
INNER JOIN tb_score b
ON a.id=b.id
GROUP BY a.id
) c
GROUP BY c.class
3.统计出班级“2017C3301”的同学中“数据库”课程的成绩分布数据（结果如下图，成绩按照10分分段，例如：成绩在(90-100)分的有10⼈）
成绩分布区间⼈数
90-10010
80-9015
…………
30-405
…………
SELECT c.level AS'成绩分布区间',COUNT(c.level)AS'⼈数'
FROM(
SELECT(CASE
WHEN score>=90AND score<=100THEN'90-100'
WHEN score>=80AND score<90THEN'80-90'
WHEN score>=70AND score<80THEN'70-80'
WHEN score>=60AND score<70THEN'60-70'
威极
WHEN score>=50AND score<60THEN'50-60'
WHEN score>=40AND score<50THEN'40-50'
WHEN score>=30AND score<40THEN'30-40'
WHEN score>=20AND score<30THEN'20-30'
WHEN score>=10AND score<20THEN'10-20'
WHEN score>=0AND score<10THEN'0-10'
END)AS level
FROM tb_student a
INNER JOIN tb_score b
ON a.id = b.id
WHERE class ='2017C3301'AND course ='数据库'
) c
GROUP BY c.level
⼆、统计以下表中每科成绩均⼤于80分的同学姓名，并输出他们的总成绩排名score表
姓名(name)课程(course)成绩(score)李三语⽂75
李三数学80
李四语⽂80
李四数学81
李四英语90
李五语⽂87
李五化学92
建表语句：
create table score (
name varchar(40)not null,
course varchar(40)not null,
score integer not null
);
插⼊数据：
insert into score values('李三','语⽂',75);
insert into score values('李三','数学',80);
insert into score values('李四','语⽂',80);
insert into score values('李四','数学',81);
insert into score values('李四','英语',90);
insert into score values('李五','语⽂',87);
insert into score values('李五','化学',92);
题解：
SELECT name, RANK()OVER(ORDER BY total DESC)AS rank
FROM(
--3.取出符合条件的同学的姓名和总分数
SELECT name,SUM(score)AS total
FROM(
-
-2.取出符合条件的同学的姓名和分数
SELECT name, score
FROM score WHERE name IN
-- 1.先取出每科成绩都⼤于80的同学姓名
(SELECT name
FROM score
GROUP BY name
HAVING MIN(score)>80)
) a
GROUP BY name) b