数据库实验报告第一次.doc

数据库实验姓名： 冯林峰 学号： 12084217 班级： 12080211 实验名称： 列表创建与查询1、实验二：新建数据库student_test，用Transcat-SQL语言创建课本P82给出的3张表（student表增加一列属性：班级），用Transcat-SQL语言完成数据插入。第一个表格use student_testcreate table student(学号 char (10) NOT NULL primary key,姓名 char (8) NULL,性别 char (2) NULL,年龄 int NULL ,所在系 char (20) NULL,)use student_testinsert studentvalues(200215121, 李勇, 男,20,CS)insert studentvalues(200215122, 刘晨, 女,19,CS)insert student values(200215123, 王敏, 女,18,MA)insert studentvalues(200215125, 张立, 男,19,IS)GO第二个表格use student_testcreate table Course(课程号 int NULL,课程名 char (10) NULL,先行课 int NULL,学分 int NULL ,)use student_testinsert Coursevalues(1,数据库,5,4)insert coursevalues(2,数学,null,2)insert coursevalues(3，信息系统,1,4)insert coursevalues(4，操作系统,6,3)insert coursevalues(5，数据结构,7,4)insert coursevalues(6，数据处理,null,2)insert coursevalues(7，PASCAL,6,4)GO第三个表格use student_testcreate table SC(学号 char (10) NULL,课程号 int NULL,成绩 int NULL ,)use student_testinsert SCvalues(200215121,1,92)insert SCvalues(200215121,2,85)insert SCvalues(200215121,3,90)insert SCvalues(200215122,2,90)insert SCvalues(200215122,3,80)GO2、 完成下列4项查询操作。1） 查询数学系姓王的学生姓名及年龄，并按学生年龄降序排序；2） 查询1班的选修课平均成绩 80 的学生姓名和学号；/没有班级一项用所在系CS代替3） 查询每个班级每门课程的选课人数和平均分；/没有班级一项用所在系代替4） 查询李爽同学没选修的课程名；1)查询代码：结果：select 姓名,年龄from Student where 所在系=MA and 姓名like 王% order by 年龄desc 2) 查询代码：结果：select 姓名,Student.学号from Student,SC where Student.学号=SC.学号and Student.班级=3班 group by 姓名,Student.学号having avg(成绩)80;3)查询代码：结果：select 班级,课程名,count(a.学号) as 选课人数,avg(成绩) as 平均分from Student a join SC b on a.学号=b.学号join Course c on b.课程号=c.课程号group by 班级,课程名;4)查询代码：结果：select 课程名 from Course where 课程号not in(select 课程号from SC where 学号in(select 学号 from Studentwhere 姓名=李勇)实验三：1、设计若干张表，合理地表示如下信息：学号，姓名，性别，生日，班级，系科，选修课程，课程名，课程编号，学分，课程容量，选课人数，成绩，任课教师（注意：同一门课有多名任课教师）。实验想法：在建立表格的时候首先要考虑吧查询的问题，可以先把属性有联系的连接在一起，于是便有了student，course，和SC表格，而要通过表格中的属性将三张表格联系起来，于是便有了一下表格。use student_12084217create table Student(学号int not null,姓名char(20),性别char(40) default(男),生日datetime check(生日1980-01-01 and 生日2020-01-01),班级char(40),系科char(80);use student_12084217create table Course(课程号int not null,课程名varchar(80),学分int ,选课人数int,课程容量int);use student_12084217create table SC(学号int,课程号int,任课教师varchar(40),成绩int);use student_12084217insert Coursevalues(1, 数据库, 4, 56,120)insert Coursevalues(2, 数学, 2,45,100)insert Coursevalues(3, 信息系统, 3, 80,100)insert Coursevalues(4, 操作系统, 3, 67,120)insert Coursevalues(5, 数据结构, 4, 78,100)insert Coursevalues(6, 信息处理, 2, 45,110)insert Coursevalues(7, PASCAL语言, 3, 45,120);use student_12084217insert Studentvalues(200215121,李勇,男,1993-02-02,2班,CS)insert Studentvalues(200215122,刘晨,女,1994-02-04,3班,CS)insert Studentvalues(200215123,王敏,女,1993-09-11,1班,MA)insert Studentvalues(200215125,张立,男,1990-12-12,1班,IS);use student_12084217insert SCvalues(200215121,1,王老师,92)insert SCvalues(200215121,2,李老师,85)insert SCvalues(200215121,3,张老师,88)insert SCvalues(200215122,2,刘老师,90)insert SCvalues(200215122,3,谢老师,80);设计查询语句，完成以下查询任务1） 查询给李勇同学授课的所有任课教师；解法一：查询代码查询结果use student_12084217 select 任课教师from SCwhere SC.学号in(select 学号from Studentwhere 姓名=李勇);解法二：查询代码查询结果use student_12084217select 任课教师from SC,Studentwhere SC.学号=student.学号AND student.姓名=李勇2） 查询某任课教师所授的每门课程中单科成绩最高的学生学号、姓名（任课教师可指定，如“李爽”）。查询代码查询结果Use student_12084217select 学号,姓名from Studentwhere 学号=(select 学号from SCwhere 成绩in (select max(成绩)from SCwhere 课程号in (select 课程号from SCwhere 任课教师=刘老师);实验问题： 首先老师说了三张表格有问题，于是我仔细思考了，发现最大的问题其实是数据冗余，因为在查询是，若是查询某门课的任课老师时会出现大量的数据重复，因为这任课老师是放在学生学号后面的，一个老师可能会教很多学生，这给信息查询和修改都带来了不方便，于是，我想的是出现第四章表格，即把课程号和任课老师拎出来，重新建立一张表格。即SC表格变为以下两张表格：这样多了两张表格之后可以有效地解决数据冗余问题，并且使根据课程查询任课老师更为便捷，不过还是没有解决数据