SQL实验报告内容

例1：定义院系表，其中编号为主关键字，名称应该惟一、并且不允许为空值。代码：--E1createtableDepartment(编号char(8)primarykey,名称char(40)uniquenotnull,负责人char(20),办公地点char(20));例2：定义学生表，其中规定：学号列是主关键字；院系列为计算列（取学号列的第3和第4个字符），并且院系值参照院系表的编号值（院系表是被参照表，主关键字是编号；参照表是学生表，外部关键字是院系），此约束说明一名学生一定属于某个院系；姓名列不允许为空值；性别必须取值“男”或“女”；学生的学籍状态为正常、留级、休学或退学。代码：--E2createtableStudent(学号char(10)primarykey,院系asconvert(char(8),substring(学号,3,2))persistednotnullforeignkey(院系)referencesDepartment(编号),姓名char(20)notnull,性别char(2)constraintc1check(性别in('男','女')),生源char(10),状态char(4)constraintc2check(状态in('正常','留级','休学','退学')));例3：定义教师表，其中规定：教师编号是主关键字；院系列为计算列（取教师编号的第1和第2个字代码：--E5createtableSC(学号char(10),课程编号char(8),考试成绩smallintconstraintc6check(考试成绩between0and100)defaultnull,primarykey(学号,课程编号),foreignkey(学号)referencesStudent(学号),foreignkey(课程编号)referencesCourse(课程编号));例6：规定学生表的性别字段不允许为空值代码：--E6altertableStudentaltercolumn性别char(2)notnull;例7：删除课程表中的责任教师列代码：--E7altertableCoursedropconstraintDuty_TeacheraltertableCoursedropcolumn责任教师;例8：为课程表增加责任教师列，列描述如下：字符类型，长度为10；允许空值；代码：--E8altertableCourseadd责任教师char(10)nullconstraintnewdtforeignkeyreferencesTeacher(教师编号);例9:插入院系表的第1个元组(记录)代码：--E9insertintoDepartment(编号,名称,负责人,办公地点)values(01,'信息管理学院','李嘉','教一楼层');例10省略列名、插入院系表的第2个元组代码：--E10insertintoDepartmentvalues(02,'经济管理学院','吴鸿忠','教三楼层');例14:插入学生表的第1个元组代码：--E14insertintoStudent(学号,姓名,性别,生源,状态)values('04014101','曹波','男','湖北','正常');例15插入学生表的第2条记录（指定正确的院系值）代码：--E15insertintoStudent(学号,姓名,性别,生源,状态)values('04014111','程鸣','女','湖南','正常');例27删除考试成绩为NULL的记录。代码：--E27deletefromSCwhere考试成绩isnull;例30将04014122号学生的B00101课程的考试成绩修改为78分代码：--E30updateSCset考试成绩=78where学号='04014122'and课程编号='B00101';例31：查询学生的全部信息代码：--E31select*fromStudent;例32：查询所有课程名称和学时信息。代码：--E32select课程名称,学时fromCourse;例33：查询学时大于等于40的课程编号和课程名称。代码：--E33select课程编号,课程名称fromCoursewhere学时>=40;例34：查询课时在50到60（含）之间的课程信息。代码：--E34select*fromCoursewhere学时>=50and学时<=60;例35：使用between查询课时在50到60（含）之间的课程信息。代码：--E35select*fromCoursewhere学时between50and60;例36：查询具有教授和副教授职称的教师信息。代码：--E36select*fromTeacherwhere职称='教授'or职称='副教授';或者：select*fromTeacherwhere职称like'%教授';例37：查询责任教师字段为空值的记录。代码：--E37select*fromCoursewhere责任教师isnull;例38：查询职称为讲师或副教授的教师信息。代码：select*fromTeacherwhere职称='讲师'or职称='副教授';例39：查询或浏览课程性质和学时信息代码：select课程性质,学时fromCourse;例40：查询“专业基础”课程的信息，并将结果存储到Student模式下、表名为“专业基础”。代码：select*into专业基础fromCoursewhere课程性质='专业基础';例41：按教师编号升序列出所有教师信息。代码：select*fromTeacherorderby教师编号;例42：查询所有学生信息，先按生源升序排序、再按学号升序排序。代码：select*fromStudentorderby生源,学号;例43：从选课表中查询考试成绩最高的3条选课信息。代码：selecttop3*fromSCorderby考试成绩desc;例45：从选课表中查询考试成绩在前15%的选课记录信息，如果随后有成绩并列的记录也一起列出。代码：selecttop15percent*fromSCorderby考试成绩desc;例54：查询考试成绩在90分以上（含）的学生的学号、姓名、相应的课程名称和考试成绩(使用CROSSJOIN连接完成查询)。代码：selectStudent.学号,姓名,课程名称,考试成绩fromStudentleftjoinSCon(Student.学号=SC.学号)leftjoinCourseon(SC.课程编号=Course.课程编号)where考试成绩>=90;例55：查询目前有多少责任教师。代码：selectcount(distinct责任教师)fromCourse;例56：查询数据库课程的平均成绩。代码：selectavg(考试成绩)fromSC,CoursewhereSC.课程编号=Course.课程编号and课程名称='数据库';例57：查询各院系教师的人数（显示院系名称和教师人数）。代码：select名称,count(*)教师人数fromDepartment,TeacherwhereDepartment.编号=Teacher.院系groupby名称;例58：查询各门课程考试成绩的平均分、最高分和最低分。代码：select课程名称,avg(考试成绩)平均分,max(考试成绩)最高分,min(考试成绩)最低分fromCourseleftjoinSCon(Course.课程编号=SC.课程编号)groupby课程名称;例59：查询专业基础课平均分大于等于60分的各门课程考试成绩的平均分、最高分和最低分。代码：select课程名称,avg(考试成绩)平均分,max(考试成绩)最高分,min(考试成绩)最低分fromCourseleftjoinSCon(Course.课程编号=SC.课程编号)groupby课程名称havingavg(考试成绩)>=60;例60：查询所有专业基础课的信息，并计算其总学时。代码：select*fromCoursewhere课程性质='专业基础'computesum(学时);例61：查询学生的考试成绩（要求查询学号、姓名、课程名称和考试成绩），同时计算每个学生的平均成绩、最高成绩和最低成绩。代码：selectStudent.学号,姓名,课程名称,考试成绩fromStudent,Course,SCwhereStudent.学号=SC.学号andCourse.课程编号=SC.课程编号orderbyStudent.学号computeavg(考试成绩),max(考试成绩),min(考试成绩)byStudent.学号;例62：查询学生的考试成绩（要求查询学号、姓名、课程名称和考试成绩），同时计算每个学生的平均成绩、最高成绩和最低成绩。同时计算所有学生的平均成绩、最高成绩和最低成绩。代码：selectStudent.学号,姓名,课程名称,考试成绩fromStudent,Course,SCwhereStudent.学号=SC.学号andCourse.课程编号=SC.课程编号orderbyStudent.学号computeavg(考试成绩),max(考试成绩),min(考试成绩)byStudent.学号computeavg(考试成绩),max(考试成绩),min(考试成绩);例63：查询由教授负责的课程信息（课程编号、课程名称、学时和课程性质）代码：select课程编号,课程名称,学时,课程性质fromCourseleftjoinTeacheron(Course.责任教师=Teacher.教师编号)where职称='教授';或代码：select课程编号,课程名称,学时,课程性质fromCoursewhere责任教师in(select教师编号fromTeacherwhere职称='教授');例64：查询责任教师的有关信息（姓名、性别、职称、专业）。代码：select姓名,性别,职称,专业fromTeacherwhere教师编号=any(select责任教师fromCoursewhere责任教师isnotnull);或者：select姓名,性别,职称,专业fromTeacherwhere教师编号in(select责任教师fromCoursewhere责任教师isnotnull);例65：查询有考试成绩大于数据库课程全部考试成绩的学生信息。代码：select*fromStudentwhere学号in(select学号fromSCwhere考试成绩>=any(select考试成绩fromSCleftjoinCourseon(SC.课程编号=Course.课程编号)where课程名称='数据库'and考试成绩isnotnull));例66：查询“系统分析与设计”课程的责任教师的姓名、职称，以及所负责的其他课程（课程编号和课程名称）。代码：select姓名,职称,课程编号,课程名称fromTeacherleftjoinCourseon(Teacher.教师编号=Course.责任教师)where教师编号in(select责任教师fromCoursewhere课程名称='系统分析与设计');例68：查询哪些学