数据库练习题一
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' , '赵雷' , '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
