SQL实验报告.docx
《SQL实验报告.docx》由会员分享，可在线阅读，更多相关《SQL实验报告.docx（17页珍藏版）》请在冰豆网上搜索。
SQL实验报告
SQL实验报告
例1：
定义院系表，其中编号为主关键字，名称应该惟一、并且不允许为空值。
代码：
--E1
createtableDepartment
（编号char（8）primarykey,
名称char（40）uniquenotnull,
负责人char（20）,
办公地点char（20）
）;
例2：
定义学生表，其中规定：
⏹学号列是主关键字；
⏹院系列为计算列（取学号列的第3和第4个字符），并且院系值参照院系表的编号值（院系表是被参照表，主关键字是编号；参照表是学生表，外部关键字是院系），此约束说明一名学生一定属于某个院系；
⏹姓名列不允许为空值；
⏹性别必须取值“男”或“女”；
⏹学生的学籍状态为正常、留级、休学或退学。
代码：
--E2
createtableStudent
（学号char（10）primarykey,
院系asconvert（char（8）,substring（学号,3,2））
persistednotnull
foreignkey（院系）referencesDepartment（编号）,
姓名char（20）notnull,
性别char
（2）constraintc1check（性别in（'男','女'））,
生源char（10）,
状态char（4）constraintc2check（状态in（'正常','留级','休学','退学'））
）;
例3：
定义教师表，其中规定：
⏹教师编号是主关键字；
⏹院系列为计算列（取教师编号的第1和第2个字符），并且院系值参照院系表的编号值，此约束说明一名教师一定属于某个院系；
⏹姓名列不允许为空值；
⏹性别必须取值“男”或“女”；
⏹职称的取值为教授、副教授、讲师或助教。
代码：
--E3
createtableTeacher
（教师编号char（10）primarykey,
院系asconvert（char（8）,substring（教师编号,1,2））
persistednotnull
foreignkey（院系）referencesDepartment（编号）,
姓名char（20）notnull,
性别char
（2）constraintc3check（性别in（'男','女'））,
职称char（6）constraintc4check（职称in（'教授','副教授','讲师','助教'））,
专业char（40）
）;
例4：
定义课程表，其中规定：
⏹课程编号是主关键字；
⏹课程名称不允许为空值；
⏹责任教师参照教师表的教师编号值，此约束说明责任教师一定是已经在编的教师；
⏹学时不允许为空值；
⏹课程性质分为公共基础、专业基础、专业选修和任意选修。
代码：
--E4
createtableCourse
（课程编号char（8）primarykey,
课程名称char（30）notnull,
责任教师char（10）constraintDuty_Teacherforeignkey（责任教师）referencesTeacher（教师编号）,
学时smallintnotnull,
课程性质char（10）constraintc5check（课程性质in（'公共基础','专业基础','专业选修','任意选修'））,
）;
例5：
定义选课表，其中规定：
⏹学号和课程编号两列构成主关键字（需要表级完整性约束）；
⏹学号参照学生表的学号（约束选课的学生必须存在）；
⏹课程编号参照课程表的课程编号（约束被选的课程必须存在）；
⏹考试成绩取值在0~100之间，并且默认为空值。
代码：
--E5
createtableSC
（学号char（10）,
课程编号char（8）,
考试成绩smallintconstraintc6check（考试成绩between0and100）defaultnull,
primarykey（学号,课程编号）,
foreignkey（学号）referencesStudent（学号）,
foreignkey（课程编号）referencesCourse（课程编号）
）;
例6：
规定学生表的性别字段不允许为空值
代码：
--E6
altertableStudentaltercolumn性别char
（2）notnull;
例7：
删除课程表中的责任教师列
代码：
--E7
altertableCoursedropconstraintDuty_Teacher
altertableCoursedropcolumn责任教师;
例8：
为课程表增加责任教师列，列描述如下：
⏹字符类型，长度为10；
⏹允许空值；
代码：
--E8
altertableCourseadd责任教师char（10）null
constraintnewdtforeignkeyreferencesTeacher（教师编号）;
例9:
插入院系表的第1个元组（记录）
代码：
--E9
insertintoDepartment（编号,名称,负责人,办公地点）
values（01,'信息管理学院','李嘉','教一楼层'）;
例10省略列名、插入院系表的第2个元组
代码：
--E10
insertintoDepartmentvalues（02,'经济管理学院','吴鸿忠','教三楼层'）;
例14:
插入学生表的第1个元组
代码：
--E14
insertintoStudent（学号,姓名,性别,生源,状态）
values（'04014101','曹波','男','湖北','正常'）;
例15插入学生表的第2条记录（指定正确的院系值）
代码：
--E15
insertintoStudent（学号,姓名,性别,生源,状态）
values（'04014111','程鸣','女','湖南','正常'）;
例27删除考试成绩为NULL的记录。
代码：
--E27
delete
fromSC
where考试成绩isnull;
例30将04014122号学生的B00101课程的考试成绩修改为78分
代码：
--E30
updateSC
set考试成绩=78
where学号='04014122'and课程编号='B00101';
例31：
查询学生的全部信息
代码：
--E31
select*
fromStudent;
例32：
查询所有课程名称和学时信息。
代码：
--E32
select课程名称,学时
fromCourse;
例33：
查询学时大于等于40的课程编号和课程名称。
代码：
--E33
select课程编号,课程名称
fromCourse
where学时>=40;
例34：
查询课时在50到60（含）之间的课程信息。
代码：
--E34
select*
fromCourse
where学时>=50and学时<=60;
例35：
使用between查询课时在50到60（含）之间的课程信息。
代码：
--E35
select*
fromCourse
where学时between50and60;
例36：
查询具有教授和副教授职称的教师信息。
代码：
--E36
select*
fromTeacher
where职称='教授'or职称='副教授';
或者：
select*
fromTeacher
where职称like'%教授';
例37：
查询责任教师字段为空值的记录。
代码：
--E37
select*
fromCourse
where责任教师isnull;
例38：
查询职称为讲师或副教授的教师信息。
代码：
select*
fromTeacher
where职称='讲师'or职称='副教授';
例39：
查询或浏览课程性质和学时信息
代码：
select课程性质,学时
fromCourse;
例40：
查询“专业基础”课程的信息，并将结果存储到Student模式下、表名为“专业基础”。
代码：
select*
into专业基础
fromCourse
where课程性质='专业基础';
例41：
按教师编号升序列出所有教师信息。
代码：
select*
fromTeacher
orderby教师编号;
例42：
查询所有学生信息，先按生源升序排序、再按学号升序排序。
代码：
select*
fromStudent
orderby生源,学号;
例43：
从选课表中查询考试成绩最高的3条选课信息。
代码：
selecttop3*
fromSC
orderby考试成绩desc;
例45：
从选课表中查询考试成绩在前15%的选课记录信息，如果随后有成绩并列的记录也一起列出。
代码：
selecttop15percent*
fromSC
orderby考试成绩desc;
例54：
查询考试成绩在90分以上（含）的学生的学号、姓名、相应的课程名称和考试成绩（使用CROSSJOIN连接完成查询）。
代码：
selectStudent.学号,姓名,课程名称,考试成绩
fromStudentleftjoinSCon（Student.学号=SC.学号）leftjoinCourseon（SC.课程编号=Course.课程编号）
where考试成绩>=90;
例55：
查询目前有多少责任教师。
代码：
selectcount（distinct责任教师）
fromCourse;
例56：
查询数据库课程的平均成绩。
代码：
selectavg（考试成绩）
fromSC,Course
whereSC.课程编号=Course.课程编号and课程名称='数据库';
例57：
查询各院系教师的人数（显示院系名称和教师人数）。
代码：
select名称,count（*）教师人数
fromDepartment,Teacher
whereDepartment.编号=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*
fromCourse
where课程性质='专业基础'
computesum（学时）;
例61：
查询学生的考试成绩（要求查询学号、姓名、课程名称和考试成绩），同时计算每个学生的平均成绩、最高成绩和最低成绩。
代码：
selectStudent.学号,姓名,课程名称,考试成绩
fromStudent,Course,SC
whereStudent.学号=SC.学号andCourse.课程编号=SC.课程编号
orderbyStudent.学号
computeavg（考试成绩）,max（考试成绩）,min（考试成绩）
byStudent.学号;
例62：
查询学生的考试成绩（要求查询学号、姓名、课程名称和考试成绩），同时计算每个学生的平均成绩、最高成绩和最低成绩。
同时计算所有学生的平均成绩、最高成绩和最低成绩。
代码：
selectStudent.学号,姓名,课程名称,考试成绩
fromStudent,Course,SC
whereStudent.学号=SC.学号andCourse.课程编号=SC.课程编号
orderbyStudent.学号
computeavg（考试成绩）,max（考试成绩）,min（考试成绩）
byStudent.学号
computeavg（考试成绩）,max（考试成绩）,min（考试成绩）;
例65：
查询有考试成绩大于数据库课程全部考试成绩的学生信息。
代码：
select*
fromStudent
where学号in
（select学号
fromSC
where考试成绩>=any
（select考试成绩
fromSCleftjoinCourseon（SC.课程编号=Course.课程编号）
where课程名称='数据库'and考试成绩isnotnull）
）;
例66：
查询“系统分析与设计”课程的责任教师的姓名、职称，以及所负责的其他课程（课程编号和课程名称）。
代码：
select姓名,职称,课程编号,课程名称
fromTeacherleftjoinCourseon（Teacher.教师编号=Course.责任教师）
where教师编号in
（select责任教师
fromCourse
where课程名称='系统分析与设计'）;
例68：
查询哪些学生（学号和姓名）有未完成考试的课程（考试成绩为NULL）
代码：
select学号,姓名
fromStudent
whereexists
（select*
fromSC
where考试成绩isnullandSC.学号=Student.学号）;
例69：
查询哪些学生（学号和姓名）没有未完成考试的课程（考试成绩为NULL）
代码：
select学号,姓名
fromStudent
wherenotexists
（select*
fromSC
where考试成绩isnullandSC.学号=Student.学号）;
升级会员 