滴滴数据分析实习SQL笔试题

一、有两个数据表，请通过SQL实现下面题目

学生表（tb_student）

学生姓名（name）	学号（id)	班级（class）	入学时间（in_time）	年龄（age）	性别（sex）	专业（major）
张三	2017C3301001	2017C3301	2017	18	男	计算机
李四	2017C3301002	2017C3301	2017	19	男	计算机

学生成绩表（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
LIMIT 20

2.统计每个班同学各科成绩平均分大于80分的人数和人数占比

方法一：

SELECT c.class,
SUM(CASE WHEN c.avg_score > 80 THEN 1 ELSE 0 END) AS '统计每个班同学各科成绩平均分大于80分的人数',
SUM(CASE WHEN c.avg_score > 80 THEN 1 ELSE 0 END)/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

方法二：

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-100	10
80-90	15
……	……
30-40	5
……	……

SELECT c.level AS '成绩分布区间', COUNT(c.level) AS '人数'
FROM(
SELECT (CASE 
			WHEN score>=90 AND score<=100 THEN '90-100'
			WHEN score>=80 AND score<90 THEN '80-90'
			WHEN score>=70 AND score<80 THEN '70-80'
			WHEN score>=60 AND score<70 THEN '60-70'
			WHEN score>=50 AND score<60 THEN '50-60'
			WHEN score>=40 AND score<50 THEN '40-50'
			WHEN score>=30 AND score<40 THEN '30-40'
			WHEN score>=20 AND score<30 THEN '20-30'
			WHEN score>=10 AND score<20 THEN '10-20'
			WHEN score>=0 AND score<10 THEN '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