数据库原理与应用实验作业参考答案.docx

数据库原理及应用试验报告参考答案试验1 数据库的建立修改及删除试验2 表构造的建立修改删除及完整性约束条件定义试验3 数据查询及更新完成以下试验报告：数据库原理及应用试验报告院名专业年级班级学号姓名老师成果一, 试验题目试验3 数据查询及更新二, 试验环境操作系统：Windows XP。数据库管理系统：MS SQL Server 2000或MS SQL Server 2021。三, 试验目的 1驾驭SELECT语句的语法, 语义及语用。2驾驭基于单表的查询方法。3驾驭基于多表的查询方法。4驾驭相关及不相关的嵌套查询。5驾驭集合查询。6驾驭插入(INSERT), 修改(UPDATE)和删除(DELETE)语句的运用。四, 试验内容 (一) 规定内容1. 单表查询USE ST-3_1 查询全体学生的具体记录。select * from student-3_2 查询选修了课程的学生学号。SELECT DISTINCT sno FROM sc-3_3 将“学生表中的sno, sname这2列合并为1列snosname输出(不变更表中存储的内容)，其余列不变。select sno+sname snosname,ssex,sage,sdeptfrom student-3_4 查询年龄不在2023岁之间的学生姓名, 系别和年龄。方法1：SELECT sname,sdept,sageFROM studentWHERE sage NOT BETWEEN 20 AND 23; 方法2： SELECT sname,sdept,sage FROM student WHERE sage<20 or sage>23;-3_5 查询计算机科学系(cs), 数学系(ma)和信息系(is)学生的姓名和性别。SELECT sname,ssexFROM studentWHERE sdept IN ( 'cs','ma','is' );-3_6 查询全部姓“刘学生的姓名, 学号和性别。SELECT sname,sno,ssexFROM studentWHERE sname LIKE '刘%'-3_7 查询名字中第2个字为"阳"字的学生的姓名和学号。SELECT sname,snoFROM studentWHERE sname LIKE '_阳%'-3_8 某些学生选修课程后没有参与考试，所以有选课记录，但没有考试成果。 -查询缺少成果的学生的学号和相应的课程号。SELECT sno,cno FROM scWHERE grade IS NULL;-3_9 查询计算机系年龄在20岁以下的学生姓名。SELECT snameFROM studentWHERE sdept='cs' AND sage<20;-3_10 查询选修了3号课程的学生的学号及其成果，查询结果按分数降序排列。SELECT sno,gradeFROM scWHERE cno='3'ORDER BY grade DESC;-3_11 查询全体学生状况，查询结果按所在系升序排列，同一系中的学生按性别降序排列。SELECT *FROM studentORDER BY sdept,sage DESC;-运用集函数-3_12 查询学生总人数。SELECT COUNT(*) 学生总人数 FROM student;-3_13 查询选修了课程的学生人数。SELECT COUNT(DISTINCT sno) 选课人数 -注:用DISTINCT以防止重复计算学生人数FROM sc;-3_14 计算1号课程的最高, 最低及平均成果。SELECT MAX(grade) '1号课程最高分', MAX(grade) '1号课程最低分',AVG(grade) '1号课程平均成果'FROM scWHERE cno='1'-3_15 查询学生200215121选修课程的平均成果。 SELECT AVG(grade) 学生200215121平均成果 FROM sc WHERE sno='200215121'-3_16 查询学生200215122选修课程的总学分数。SELECT SUM(ccredit) 学生200215122学分FROM sc,courseWHERE o=o AND sno='200215122'-运用GROUP BY子句分组-3_17 求各个课程号及相应的选课人数。SELECT cno 课程号,COUNT(sno) 选课人数FROM scGROUP BY cno; -运用HAVING短语筛选最终分组结果-3_18 查询选修了3门(含3)以上课程的学生学号。SELECT snoFROM scGROUP BY sno HAVING COUNT(*)>=3 -也可为:COUNT(cno)>=3-3_19 查询有2门以上课程是80分以上的学生的学号及(80分以上的)课程数SELECT sno 学号,COUNT(*) '80分以上的课程数'FROM scWHERE grade>=80GROUP BY sno HAVING COUNT(*)>=2 2. 连接查询-自然连接-3_20 查询每个学生的学号, 姓名, 课号及成果。SELECT student.sno,sname,cno,grade FROM student,sc-左外连接-3_21 查询每个学生的学号, 姓名, 课号及成果(包括没有选修课程的学生)。方法1：SELECT student.sno,sname,cno,gradeFROM student,sc方法2：SELECT student.sno,sname,cno,grade运行结果：sno sname cno grade - - - - 200215123 王敏 NULL NULL200215124 张立 NULL NULL-3_22 查询全部学生选修课程的成果。包括没有选课的学生。列出学号, 姓名, 课号, 课名, 成果。SELECT student.sno,sname,o,cname,grade运行结果：sno sname cno cname grade - - - - - 200215123 王敏 NULL NULL NULL200215124 张立 NULL NULL NULL-自身连接: 一个表及其自己进展连接，称为表的自身连接-3_23 查询每一门课的间接先修课即先修课的先修课SELECT FIRST.cno 课号,SECOND.cpno 间接先修课FROM course FIRST,course SECOND-复合条件连接-3_24 查询选修2号课程且成果在90分以上的全部学生的学号, 姓名。SELECT student.sno, sname FROM student,sc WHERE student.sno = sc.sno /* 连接谓词*/ AND o='2' AND sc.grade > 90 /* 其他限定条件*/-多表连接-3_25 查询每个学生的学号, 姓名, 课名及成果。SELECT student.sno,sname,cname,gradeFROM student,sc,course运行结果：sno sname cname grade - - - - 3 嵌套查询-3_26 查询及“刘晨在一个系学习的学生。-方法1: 不相关子查询(子查询的查询条件不依靠于父查询)select sno,sname,sdeptfrom student where sdept in -当内查询结果最多只有一个值时可用=代替in (select sdept from student where sname='刘晨') -3_27 查询全部姓名一样的学生。-方法1: 不相关子查询(子查询的查询条件不依靠于父查询)select *from studentwhere sname in (select sname from student group by sname having count(*)>1)order by sname,sno -3_28 查询选修了课程名为“信息系统的学生学号和姓名。-方法1：用嵌套查询(不相关子查询)SELECT sno,sname -最终在sudent关系中取出Sno和SnameFROM student WHERE sno IN (SELECT sno -然后在sc中找出选修了“信息系统所在课号的学生学号 FROM sc WHERE cno IN (SELECT cno -首先在curse关系中找出“信息系统的课程号 FROM course WHERE cname='信息系统')-3_29 找出每个学生超过他选修课程平均成果的学号, 课号及成果。SELECT x.* FROM sc x WHERE grade>=(SELECT AVG(grade) FROM sc y WHERE y.sno=x.sno)运行结果：sno cno grade - - - -3_30 查询其他系中比计算机科学系某一学生年龄小的学生学号, 姓名和年龄。-方法1：用any谓词 select sname,sage from student where sage<any (select sage from student where sdept='cs') and sdept<>'cs' /* 留意这是父查询块中的条件*/-3_31 查询其他系中比计算机科学系全部学生年龄都小的学生学号, 姓名和年龄。select sname,sage from student where sage<all (select sage from student where sdept='cs') and sdept<>'cs'-3_32 查询全部选修了1号课程的学生学号, 姓名。SELECT sno,sname FROM student WHERE EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno='1'); -相关子查询-3_33 查询没有选修1号课程的学生学号, 姓名。-方法1：相关子查询 SELECT sno,sname FROM student WHERE NOT EXISTS (SELECT * -此处*可换为sno FROM sc WHERE sno=student.sno AND cno='1'); -3_34 查询选修了全部课程的学生姓名。SELECT sname -查询这样的学生y FROM student WHERE NOT EXISTS -不存在课程x (SELECT * FROM course WHERE NOT EXISTS -学生y不选修x (SELECT * FROM sc WHERE sno=student.sno -表SC及Student自然连接AND cno=o) -表SC及course自然连接-3_35 查询至少选修了学生200215122选修的全部课程的学生号码。SELECT DISTINCT sno -查询这样的学生的xFROM sc SCXWHERE NOT EXISTS -不存在这样的课程y (SELECT * FROM sc SCY WHERE SCY.sno='200215122' AND -学生200215122选修了yNOT EXISTS -学生x没有选修y (SELECT * FROM sc SCZ WHERE SCZ.sno=SCX.sno AND SCZ.cno=SCY.cno)-3_36 将表sc复制到sc1中。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = 'sc1' AND type = 'U') DROP TABLE sc1 GOSelect * Into sc1 from sc -复制表-3_37 将表sc的构造复制到sc2中。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = 'sc2' AND type = 'U') DROP TABLE sc2 GOselect * into sc2 from sc -复制表构造where sno is null-或Select * into sc2 from sc -复制表构造Where not exists (select * from sc) 4 集合查询-3_38 查询计算机系的学生及年龄不大于19岁的学生，并按年龄降序排列。-方法1：并操作(UNION)SELECT *FROM studentWHERE sdept='cs'UNION SELECT *FROM student WHERE sage<=19Order by sage desc-3_39 查询选修了课程1或者选修了课程2的学生学号。SELECT snoFROM scWHERE cno='1'UNION SELECT snoFROM scWHERE cno='2'-3_40 查询既选修了1号又选修了2号课程的学生学号。本查询可表述为：查询至少选修了1, 2号课程的学生学号。-方法1：集合交操作，查询选修课程1的学生集合及选修课程2的学生集合的交集SELECT snoFROM scWHERE cno='1' INTERSECTSELECT snoFROM scWHERE cno='2'-方法2：SELECT snoFROM scWHERE cno='1' AND sno IN (SELECT sno FROM sc WHERE cno='2')-3_41 查询计算机系的学生及年龄不大于19岁的学生的交集。-方法1：交操作SELECT *FROM studentWHERE sdept='cs' INTERSECTSELECT *FROM studentWHERE sage<=19-方法2：标准SQL中没有供应集合交操作，但可用其他方法间接实现。-本例事实上是：查询计算机系年龄不大于19岁的学生SELECT *FROM studentWHERE sdept='cs' AND sage<=19 -3_42 查询计算机系的学生及年龄19岁以上学生的差集。-方法1：差操作，在计算机系的学生中去掉年龄19岁以上的学生SELECT *FROM studentWHERE sdept='cs'EXCEPTSELECT *FROM studentWHERE sage>19-方法2：标准SQL中没有供应集合差操作，但可用其他方法间接实现。-本例事实上是：查询计算机系年龄不大于19岁的学生SELECT *FROM studentWHERE sdept='cs' AND sage<=19 5综合-3_43 统计每个学生的学分，列出学号, 姓名, 学分。留意：不及格课程无学分。SELECT student.sno 学号,sname 姓名,sum(ccredit) 学分Where grade>=60Group by student.sno,sname-3_44 学士学位授予条件为：至少选修了1, 3, 4号3门学位课程，每门学位课必需及格且学位课平均成果在75分以上。查询cs系可授予学士学位的学生名单。select *from studentwhere sdept='cs' and sno in (select sno from sc where cno in('1','3','4') and grade>=60 group by sno having count(sno)=3 and avg(grade)>=75)-3_45 学士学位授予条件为：至少选修了数据构造','数据库','操作系统'3门学位课程，每门学位课必需及格且学位课平均成果在75分以上。查询cs系可授予学士学位的学生名单。select *from studentwhere sdept='cs' and sno in (select sno from sc where grade>=60 and cno in (select cno from course where cname in('数据构造','数据库','操作系统') group by sno having count(sno)=3 and avg(grade)>=75)5 插入(INSERT), 修改(UPDATE)和删除(DELETE)语句的运用-3_46 插入一条选课记录( '200215123','1')。 if not exists(select * from sc where sno='200215123' and cno='1') INSERT INTO sc(sno,cno) VALUES ('200215123','1') -新插入的记录在Grade列上取空值-3_47 对每一个系，求学生的平均年龄，并把结果存入数据库。 -第1步：建表，第2步：插入子查询结果 -第一步：建表 use ST IF EXISTS(SELECT name FROM sysobjects WHERE name = 'DeptAge' AND type = 'U') DROP TABLE DeptAge -假设表Deptage已存在,那么先删除再重建 GO CREATE TABLE DeptAge (sdept CHAR(2), -系名 avgage SMALLINT) -学生平均年龄 -第二步：插入子查询结果 INSERT INTO DeptAge(sdept,avgage) SELECT sdept,AVG(sage) FROM student GROUP BY sdept select * from DeptAge-3_48 将全部学生的年龄增加1岁。 UPDATE student SET sage=sage+1-3_49 对计算机科学系(cs)全体学生选修'2'号课程分数>=36的按10*sqrt(grade)计算。 update sc set grade=10*sqrt(grade) where cno='2' and grade>=36 and 'cs'=(select sdept from student where student.sno=sc.sno) -相关子查询-3_50 删除is系全部学生的选课记录。DELETE FROM sc WHERE 'is'=(SELECT sdept FROM student WHERE student.sno=sc.sno);-3_51 删除只选1门课且成果不及格学生的选课记录。 DELETE FROM sc WHERE sno in (SELECT sno FROM sc WHERE grade<60 GROUP BY sno HAVING COUNT(*)=1)-3_52 删除计算机系只选1门课且成果不及格学生的选课记录。DELETE FROM sc WHERE sno IN (SELECT sno FROM student WHERE sdept='cs' AND sno IN (SELECT sno FROM sc GROUP BY sno HAVING COUNT(*)=1)-3_53 删除未选修课程的学生。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = 'student1' AND type = 'U') DROP TABLE student1 GOSelect * Into student1 from student -先复制表-删除student1中未选修课程的学生DELETEFROM student1WHERE sno NOT IN (SELECT sno FROM sc) (二) 自定内容五, 出现的问题及解决方法试验4 索引及视图完成以下试验报告：数据库原理及应用试验报告院名专业年级班级学号姓名老师成果一, 试验题目试验4 索引及视图二, 试验环境操作系统：Windows XP。数据库管理系统：MS SQL Server 2000 或MS SQL Server 2021。三, 试验目的 1理解索引的目的, 维护及运用，唯一及非唯一索引, 聚簇及非聚簇索引, 单索引及复合索引的区分。2驾驭建立及删除索引的方法。3驾驭建立及删除视图的方法。4驾驭对视图表进展查询及更新的操作方法。四, 试验内容 (一) 规定内容1 建立及删除索引 -4_1 查询姓“李的学生，为其建立相应的索引。if exists (select name from sysindexes where name='index_sname') create index index_sname on student(sname)-4_2 查询年龄20岁的女生，为其建立相应的索引。if exists (select name from sysindexes where name='index_sageSex ') create index index_sageSex ON student(sage,ssex DESC)-4_3 建立“教工(教工号，姓名，性别)表构造，按“教工号为该表建立聚簇索引，按“姓名, “性别为该表建立唯一值复合索引。if exists (select name from sysobjects where name='教工') drop table 教工create table 教工(教工号 char(5), 姓名 char(8), 性别 char(2) ) create clustered index index_tno on 教工(教工号)create unique index index_TnameSex on 教工(姓名, 性别)-4_4 删除“教工表上建立的唯一值复合索引。-4_5 (2004秋数据库系统工程师下午试题) 设有如下关系模式： 职工职工号，姓名，年龄，月工资，部门号， ，办公室 假定分别在“职工关系中的“年龄和“月工资字段上创立了索引，如下的Select查询语句可能不会促使查询优化器运用索引，从而降低查询效率，请写出既可以完成一样功能又可以提高查询效率的SQL语句。 Select 姓名，年龄，月工资 from 职工 Where 年龄>45 or 月工资<1000; 解：Select 姓名,年龄,月工资 from 职工 Where 年龄>45; UNION Select 姓名,年龄,月工资 from 职工 Where 月工资<1000; 2 建立及删除视图-4_6 建立信息系学生视图，含学号, 姓名, 姓别等列。use STIF EXISTS (SELECT name FROM sysobjects WHERE name='V_is') drop view V_is -假设V_is已存在,那么删除goCREATE VIEW V_is AS SELECT sno,sname,ssex -行列子集视图 FROM student WHERE sdept='is'-4_7 建立信息系学生的视图，并要求透过该视图进展的更新操作只涉及信息系学生。use STIF EXISTS (SELECT name FROM sysobjects WHERE name='V_is1') drop view V_is1 -假设V_is已存在,那么删除goCREATE VIEW V_is1 AS SELECT * FROM student WHERE sdept='is' WITH CHECK OPTION -WITH CHECK OPTION的视图-4_8 建立信息系选修了1号课程的学生视图。IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is2') drop view V_is2 -假设V_is已存在,那么删除goCREATE VIEW V_is2(sno,sname,cno,grade) AS SELECT student.sno,sname,cno,grade FROM student,sc -基于多个基表的视图 WHERE student.sno=sc.sno AND sdept='is' AND o='1'-4_9 建立信息系选修了1号课程且成果在90分以上的学生的视图。IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is3') drop view V_is3 -假设V_is已存在,那么删除goCREATE VIEW V_is3 AS SELECT sno,sname,grade FROM V_is2 -基于视图的视图 WHERE grade>=90-4_10 先建立高考(考号，姓名，语文，数学，英语，综合)根本表, 在此根底上建立含总分列的视图。IF EXISTS(SELECT name FROM sysobjects WHERE name = 'GK') drop table GK -假设GK已存在,那么删除 go create table GK (考号 char(5) primary key, 姓名 char(8), 语文 decimal(5,1) check(语文 between 0 and 150), 数学 decimal(5,1) check(数学 between 0 and 150), 英语 decimal(5,1) check(英语 between 0 and 150), 综合 decimal(5,1) check(综合 between 0 and 300)IF EXISTS(SELECT name FROM sysobjects WHERE name = 'V_gk') drop view V_gk -假设GH_view已存在,那么删除 go create view V_gk (考号,姓名,语文,数学,英语,综合,总分) -总分为虚拟列 as select 考号,姓名,语文,数学,英语,综合,语文+数学+英语+综合 from GK运行以上程序建立高考表“GK及视图“V_gk。-4_11 将学生的学号及他的平均成果定义为一个视图。IF EXISTS (SELECT name FROM sysobjects WHERE name='V_avg') drop view V_avg -假设V_avg已存在,那么删除 GO CREATE VIEW V_avg(sno,gavg) AS SELECT sno,AVG(grade) FROM sc GROUP BY sno -分组视图-4_12 将student表中全部女生记录定义为一个视图。方法1：IF EXISTS(SELECT name FROM sysobjects WHERE name ='V_fs') drop view V_fs -假设V_fs已存在,那么删除 GO CREATE VIEW V_fs (sno,sname,sex,age,dept) AS SELECT sno,sname,ssex,sage,sdept FROM student WHERE ssex='女' -当基表Student增加属性列时，不会破坏Student表及V_fs视图的映象关系。-4_13 建立视图V_good (修课成果在平均成果之上的元组)。IF EXISTS(SELECT name FROM sysobj