sql 面试题

来源：互联网发布：基金数据库编辑：程序博客网时间：2024/06/03 21:41

drop table if exists STU_SCORE;create table STU_SCORE(   name                 varchar(50) comment '姓名',   subject              varchar(50) comment '课程',   score                numeric(11,2) comment '分数',   stuid                int comment '学号')ENGINE = MYISAM DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;alter table STU_SCORE comment '学生成绩表'; INSERT into STU_SCORE VALUES('张三','数学',89,1);INSERT into STU_SCORE VALUES('张三','语文',90,1);INSERT into STU_SCORE VALUES('张三','英语',50,1);INSERT into STU_SCORE VALUES('李四','数学',90,2);INSERT into STU_SCORE VALUES('李四','语文',93,2);INSERT into STU_SCORE VALUES('李四','英语',80,2);INSERT into STU_SCORE VALUES('王五','数学',95,3);INSERT into STU_SCORE VALUES('王五','语文',59,3);INSERT into STU_SCORE VALUES('王五','英语',88,3);INSERT into STU_SCORE VALUES('赵六','数学',98,4);INSERT into STU_SCORE VALUES('赵六','语文',69,4);INSERT into STU_SCORE VALUES('赵六','英语',78,4);

1.1. 计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

#1.1.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)SELECT stuid AS '学号', name AS '姓名',SUM(score) AS '总成绩' FROM STU_SCORE GROUP BY stuid ORDER BY '总成绩' DESC

1.2. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，最高分

#1.2.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，最高分SELECT stuid AS '学号', name AS '姓名' ,`subject` AS '科目',MAX(score) AS '最高分' FROM STU_SCORE  GROUP BY SUBJECT  ORDER BY '最高分' DESC

1.3. 统计如下：学号姓名语文数学英语总分平均分

#1.3.统计：学号     姓名     语文     数学     英语     总分     平均分SELECT stuid as '学号',name as '姓名',sum(case when subject='语文' then score else 0 end) as '语文',sum(case when subject='数学' then score else 0 end) as '数学',sum(case when subject='英语' then score else 0 end) as '英语',sum(score) as '总分',(sum(score)/count(1)) as '平均分' FROM STU_SCORE GROUP BY stuid ORDER BY 总分 DESC

1.4. 统计如下：课程不及格（0-59）良（60-80）优（81-100）

#1.4.统计如下：课程     不及格（0-59）    良（60-80）    优（81-100）SELECT subject AS '课程', (SELECT count(1) FROM STU_SCORE WHERE score<60 AND subject=t1.subject) AS '不及格',(SELECT count(1) FROM STU_SCORE WHERE score BETWEEN 60 AND 80 AND subject = t1.subject) AS '良',(SELECT count(1) FROM STU_SCORE WHERE score >80 AND subject=t1.subject) AS '优'FROM STU_SCORE t1 GROUP BY subject

1.5. 统计如下

本帖转载于：http://www.360doc.com/content/14/0513/15/16319058_377247264.shtml

1 0