数据库练习题一
1、创建表表名和字段–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) –学生编号,课程编号,分数
2、创建语句学生表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`));
3、插入数据--插入学生表测试数据insert into Student values('01' , '赵雷刻八圄俏9; , '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' , '王五');
4、成瑗礼佛夜绩表测试数据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);
5、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90selectCourse.c_id,Course.c_name,max(Score.s_score)as'课程最高分', min(Score.s_score)as'课程最低分', AVG(Score.s_score)as'课程平均分', CAST(count(casewhenScore.s_score>=90 then1 end)asfloat)/CAST(count(Score.s_score)asfloat)as'优秀率', CAST(count(casewhenScore.s_score>=80 andScore.s_score<=90 then1 end)asfloat)/CAST(count(Score.s_score)asfloat)as'优良率', CAST(count(casewhenScore.s_score>=70 andScore.s_score<=80 then1 end)asfloat)/CAST(count(Score.s_score)asfloat)as'中等率', CAST(count(casewhenScore.s_score>=60 then1 end)asfloat)/CAST(count(Score.s_score)asfloat)as'及格率'fromCourseleftjoinScoreonScore.c_id=Course.c_idgroupbyCourse.c_id,Course.c_name
6、查询学生的总成绩并进行排名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(降序排列默认升序排列)
7、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比selectCourse.c_id,Course.c_name,(count(casewhenScore.s_score>=85 andScore.s_score<=100 then1 end)*0.1)/((selectcount(student.s_id)fromstudent)*0.1)as'100-85的人数的百分比',(count(casewhenScore.s_score>=70 andScore.s_score<85 then1 end)*0.1)/((selectcount(student.s_id)fromstudent)*0.1)as'85-70的总人数百分比',(count(casewhenScore.s_score>=60 andScore.s_score<70 then1 end)*0.1)/((selectcount(student.s_id)fromstudent)*0.1)as'70-60百分比',(count(casewhenScore.s_score<=60 andScore.s_score>0 then1 end)*0.1)/((selectcount(student.s_id)fromstudent)*0.1)as'60-0百分比'fromscoreleftjoincourseonScore.c_id=Course.c_idgroupbyCourse.c_id,Course.c_name这里我是用的乘以0.1.大家可以先用函数转换，在算百分比
8、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩selectStudent.s_id,Student.s_name,avg(Score.s_score)as'平均分',max(casewhenCourse.c_id='01'thens_scoreend)as'语文',max(casewhenCourse.c_id='02'thens_scoreend)as'数学',max(casewhenCourse.c_id='03'thens_scoreend)as'英语'fromstudentleftjoinScoreonStudent.s_id=Score.s_idleftjoinCourseonScore.c_id=Course.c_idgroupbyStudent.s_id,Student.s_nameorderbyavg(Score.s_score)desc
