sql练习题

SQL练习题

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

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

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

select s.name, g.scorefrom student sinner join grade g on g.id = s.idwhere g.kemu = "数学"  order by g.score desc

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

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

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

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

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

select s.id,s.name,g.kemu,g.scorefrom student s,(-- 各门课程最好的成绩和名称select kemu,max(score) as score from gradegroup by kemu) t, grade gwhere 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 sinner join grade g on g.id =s.idgroup 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 sinner join grade g on g.id =s.idgroup by s.id,s.name

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

select kemu,avg(score)from gradegroup 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.idORDER BY g.score DESC-- 以上的语句，会出现分数一致，但是名次不一致-- 设置两个变量，一个是排名，一个是用来比较分数大小，设置排名的序号SELECTs.id,s.NAME,g.kemu,g.score ,(CASEWHEN @param = g.score THEN @row_number -- 在when中赋值，那这个条件判断是true还是false？，在实验中发现如果第一个when为false，这个when是必定true的，所以说是赋值的操作会认为是条件为truewhen @param := g.score THEN @row_number := @row_number + 1 -- 基于以上的实验，when的赋值永为true，所以=0的情况就没有必要写了-- WHEN @param = 0 THEN @row_number := @row_number + 1 END ) AS 排名 FROMstudent 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.scorefrom student s,grade gwhere g.kemu = '数学' and s.id = g.idorder by g.score desclimit 3

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

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

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

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

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

select s.id,s.name,g.kemu,g.scorefrom student s,grade gwhere 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 aWHERE-- 跟排序号的成绩进行比较统计，如果大于2，就说明改成绩已经是第三名了，然后就过滤(SELECT count(*) FROM grade t2WHERE t1.kemu=t2.kemu AND t2.score>=t1.score)<=2and a.id = t1.idORDER BY t1.kemu,t1.score DESC

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

-- 第一种，三门都大于80select s.id,s.namefrom student swhere -- 统计分数都大于80的科数(select count(*) from grade gwhere g.id = s.idand score >80)-- 三门都大于80=3-- 查询的是最小分数科目都大于80，可能会出现的情况是有的同学的成绩只有2门select s.id ,s.namefrom student s,grade g where s.id =g.idgroup by s.idhaving min(g.score) > 80

来源地址：https://blog.csdn.net/zhurigeng/article/details/127272285