数据库练习题一

创建表表名和字段–1.学生表Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别–2.课程表Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号–3.教师表Teacher(t_id,t_name) –教师编号,教师姓名–4.成绩表Score(s_id,c_id,s_score) –学生编号,课程编号,分数

创建语句学生表CREATE TABLE `Student`(    `s_id` VARCHAR(20),    `s_name` VARCHAR(20) NOT NULL DEFAULT '',    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',    PRIMARY KEY(`s_id`));--课程表CREATE TABLE `Course`(    `c_id`  VARCHAR(20),    `c_name` VARCHAR(20) NOT NULL DEFAULT '',    `t_id` VARCHAR(20) NOT NULL,    PRIMARY KEY(`c_id`));--教师表CREATE TABLE `Teacher`(    `t_id` VARCHAR(20),    `t_name` VARCHAR(20) NOT NULL DEFAULT '',    PRIMARY KEY(`t_id`));--成绩表CREATE TABLE `Score`(    `s_id` VARCHAR(20),    `c_id`  VARCHAR(20),    `s_score` INT(3),    PRIMARY KEY(`s_id`,`c_id`));

插入数据--插入学生表测试数据insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-03-01' , '女');insert into Student values('07' , '郑竹' , '1989-07-01' , '女');insert into Student values('08' , '王菊' , '1990-01-20' , '女'); 课程表测试数据insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03'); 教师表测试数据insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');

成绩表测试数据insert into Score values('01' , '01' , 80);insert into Score values('01' , '02' , 90);insert into Score values('01' , '03' , 99);insert into Score values('02' , '01' , 70);insert into Score values('02' , '02' , 60);insert into Score values('02' , '03' , 80);insert into Score values('03' , '01' , 80);insert into Score values('03' , '02' , 80);insert into Score values('03' , '03' , 80);insert into Score values('04' , '01' , 50);insert into Score values('04' , '02' , 30);insert into Score values('04' , '03' , 20);insert into Score values('05' , '01' , 76);insert into Score values('05' , '02' , 87);insert into Score values('06' , '01' , 31);insert into Score values('06' , '03' , 34);insert into Score values('07' , '02' , 89);insert into Score values('07' , '03' , 98);

1、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90select  Course.c_id,Course.c_name,max(Score.s_score) as '课程最高分' ,        min(Score.s_score) as '课程最低分',       AVG(Score.s_score) as '课程平均分',       CAST(count(case when Score.s_score>=90 then 1 end) as float)/CAST(count(Score.s_score)as float) as '优秀率',      CAST(count(case when Score.s_score>=80 and Score.s_score<=90 then 1 end) as float)/CAST(count(Score.s_score)as float) as '优良率',     CAST(count(case when Score.s_score>=70 and Score.s_score<=80 then 1 end) as float)/CAST(count(Score.s_score)as float) as '中等率',      CAST(count(case when Score.s_score>=60 then 1 end ) as float)/CAST(count(Score.s_score)as float) as '及格率'from  Course left join  Score  on Score.c_id=Course.c_idgroup  by Course.c_id,Course.c_name

2、查询学生的总成绩并进行排名Select  Student.s_id,Student.s_name,sum(Score.s_score) as  ‘总成绩’From  Student,ScoreWhere Student.s_id=Score.s_idGroup by Student.s_id,Student.s_nameOrder by  sum(Score.s_score)DESC(降序排列默认升序排列)

3、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比select Course.c_id,Course.c_name,(count(case when Score.s_score>=85 and Score.s_score<=100 then 1 end)*0.1)/((select count(student.s_id)from student)*0.1) as '100-85的人数的百分比',(count(case when Score.s_score>=70 and Score.s_score<85 then 1 end)*0.1)/((select count(student.s_id)from student)*0.1) as '85-70的总人数百分比',(count(case when Score.s_score>=60 and Score.s_score<70 then 1 end)*0.1)/((select count(student.s_id)from student)*0.1) as '70-60百分比',(count(case when Score.s_score<=60 and Score.s_score>0 then 1 end)*0.1)/((select count(student.s_id)from student)*0.1) as '60-0百分比'from score left  join course  on Score.c_id = Course.c_idgroup by Course.c_id,Course.c_name这里我是用的乘以0.1.大家可以先用函数转换，在算百分比

4、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select  Student.s_id,Student.s_name,avg(Score.s_score)as '平均分',max(case when Course.c_id='01' then s_score end ) as '语文',max(case when Course.c_id='02' then s_score end ) as '数学',max(case when Course.c_id='03' then s_score end ) as '英语'from  student left join Score on Student.s_id=Score.s_id   left join  Course  on Score.c_id=Course.c_id  group by  Student.s_id,Student.s_name order by avg(Score.s_score) desc