sql练习题

SQL练习题

学生表（student）：id，name，age

科目和分数表（grade）：no，id，kemu（科目），score（分数）

1、查询所有学生的数学成绩，显示学生名字name，分数，由高到低

select s.name, g.score
from student s
inner join grade g on g.id = s.id
where g.kemu = "数学"  
order by g.score desc

2、统计每个学生的总成绩，显示字段：姓名，总成绩

select s.name,sum(g.score) as sum_score
from student s
inner join grade g on g.id = s.id
-- 必须使用聚合函数
group by s.name
order by sum_score desc

3、统计每个学生的总成绩（由于学生可能有重复名字），显示字段：学生id，姓名，总成绩

select s.id ,s.name,sum(g.score) as sum_score
from student s
inner join grade g on g.id = s.id
-- 通过id分组，预防名字重复
group by s.id
order by sum_score desc

4、列出各门课程成绩最好的学生，要求显示字段：学号，姓名，科目，成绩

select s.id,s.name,g.kemu,g.score
from student s,(
-- 各门课程最好的成绩和名称
select kemu,max(score) as score from grade
group by kemu
) t, grade g
where g.id = s.id and g.kemu = t.kemu and g.score = t.score 

5、计算每个人的平均成绩，要求显示字段：学号，姓名，平均成绩

select s.id,s.name,avg(g.score)
from student s
inner join grade g on g.id =s.id
group by s.id,s.name

6、计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学， 英语，总分，平均分，使用case when … else…分支语句

-- 使用case when...else语句 设置不同科目的 成绩
select s.id,s.name,
sum(case when g.kemu ="语文" then g.score else 0 end ) as '语文',
sum(case when g.kemu ="数学" then g.score else 0 end ) as '数学',
sum(case when g.kemu ="英语" then g.score else 0 end ) as '英语',
sum(g.score),avg(g.score)
from student s
inner join grade g on g.id =s.id
group by s.id,s.name

7、列出各门课程的平均成绩，要求显示字段：课程，平均成绩

select kemu,avg(score)
from grade
group by kemu

8、列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名

-- 查询列添加一个新的变量，作为排名
select s.id,s.name,g.kemu,g.score,@row_number:=@row_number +1 as 排名
-- 这里使用的是多表查询，没有使用连接的方式，使用内连接会出现语法错误
from student s,(select @row_number:=0) t, grade g 
where g.kemu = '数学' and  g.id =s.id
ORDER BY g.score DESC

-- 以上的语句，会出现分数一致，但是名次不一致
-- 设置两个变量，一个是排名，一个是用来比较分数大小，设置排名的序号
SELECT
	s.id,s.NAME,g.kemu,g.score 
	,(CASE
			WHEN @param = g.score THEN @row_number 
-- 		在when中赋值，那这个条件判断是true还是false？，在实验中发现如果第一个when为false，这个when是必定true的，所以说是赋值的操作会认为是条件为true
			when @param := g.score THEN @row_number := @row_number + 1 
-- 			基于以上的实验，when的赋值永为true，所以=0的情况就没有必要写了
-- 			WHEN @param = 0 THEN @row_number := @row_number + 1 
		END ) AS 排名 
	FROM
		student s,
		(SELECT @row_number := 0,@param := 0 ) t,
-- 		重新构建一个新的数学成绩排名临时表
		( SELECT id, kemu, score FROM grade WHERE kemu = '数学' ORDER BY score DESC ) g 
	WHERE g.id = s.id 

9、列出数学成绩前 3 名的学生（要求显示字段：学号，姓名, 科目，成绩）

select s.id,s.name,g.kemu,g.score
from student s,grade g
where g.kemu = '数学' and s.id = g.id
order by g.score desc
limit 3

10、查询数学成绩第 2 和第 3 名的学生

select s.id,s.name,g.kemu,g.score
from student s,grade g
where g.kemu = '数学' and s.id = g.id
order by g.score desc
-- 2,3名，就是从索引1开始，向后取2位
limit 1,2

11、查询第 3名到后面所有的学生数学成绩

	select s.id,s.name,g.kemu,g.score
	from student s,grade g
	where g.kemu = '数学' and s.id = g.id
	order by g.score desc
	-- 填写一个很大的值，相当于后面全部
	limit 2,10000

12、统计英语课程少于 80 分的，显示 学号 id, 姓名，科目，分数

select s.id,s.name,g.kemu,g.score
from student s,grade g
where s.id = g.id and g.kemu="英语" and g.score < 80

13、查找每科成绩前 2 名，显示 id, 姓名，科目，分数

SELECT t1.id, a.name, t1.kemu,t1.score 
FROM grade t1, student a
WHERE
-- 跟排序号的成绩进行比较统计，如果大于2，就说明改成绩已经是第三名了，然后就过滤
(SELECT count(*) FROM grade t2
WHERE t1.kemu=t2.kemu AND t2.score>=t1.score
)<=2
and a.id = t1.id
ORDER BY t1.kemu,t1.score 
DESC

14、查询学生表每门课都大于80分的学生姓名

-- 第一种，三门都大于80
select s.id,s.name
from student s
where 
-- 统计分数都大于80的科数
(select count(*) from grade g
where g.id = s.id
and score >80)
-- 三门都大于80
=3


-- 查询的是最小分数科目都大于80，可能会出现的情况是有的同学的成绩只有2门
select s.id ,s.name
from student s,grade g 
where s.id =g.id
group by s.id
having min(g.score) > 80