5. 数据库题(以个人熟悉数据库为准、按要求写出sql) (1) 计算每个人的总成绩并排名(要求显示字段:学号,姓名,总成绩) (2) 计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_42321963/article/details/83476844

5. 数据库题(以个人熟悉数据库为准、按要求写出sql)

(1) 计算每个人的总成绩并排名(要求显示字段:学号,姓名,总成绩)
(2) 计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)
(3) 列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,课程,成绩)
  [分值:5]

 

use db2;

create table t_student_score(
stuid int(11),
name varchar(50),
subject varchar(50),
score int(11)
) 
delete from t_student_score;
select * from t_student_score;

insert into t_student_score values(10001,'张三','语文',89);
insert into t_student_score values(10001,'张三','数学',95);
insert into t_student_score values(10001,'张三','外语',70);
insert into t_student_score values(10001,'李四','语文',95);
insert into t_student_score values(10001,'李四','数学',80);
insert into t_student_score values(10001,'李四','外语',75);
insert into t_student_score values(10001,'王五','语文',85);
insert into t_student_score values(10001,'王五','数学',90);
insert into t_student_score values(10001,'王五','外语',70);

select  stuid 学号, name 姓名,sum(score) 总成绩 from t_student_score group by name order by 总成绩 ;

select  t1.stuid 学号,t1.name 姓名,t1.subject 课程,t1.score 成绩 from t_student_score t1,
  (select name,max(score) 最高成绩 from t_student_score group by name)t2
  where t1.name=t2.name and t1.score=t2.最高成绩;


select t1.stuid 学号,t1.name 姓名,t1.subject 课程,t1.score 成绩 from t_student_score t1,
 (select subject,max(score) 最高成绩 from t_student_score group by subject) t2
 where t1.subject=t2.subject and t1.score=t2.最高成绩;




 

展开阅读全文

没有更多推荐了,返回首页