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

《数据库原理与应用实验作业参考答案.docx》由会员分享，可在线阅读，更多相关《数据库原理与应用实验作业参考答案.docx（21页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库原理及应用试验报告参考答案试验1 数据库的建立修改及删除试验2 表构造的建立修改删除及完整性约束条件定义试验3 数据查询及更新完成以下试验报告：数据库原理及应用试验报告院名专业年级班级学号姓名老师成果一, 试验题目试验3 数据查询及更新二, 试验环境操作系统：Windows XP。数据库管理系统：MS SQL Server 2000或MS SQL Server 2021。三, 试验目的 1驾驭SELECT语句的语法, 语义及语用。2驾驭基于单表的查询方法。3驾驭基于多表的查询方法。4驾驭相关及不相关的嵌套查询。5驾驭集合查询。6驾驭插入(INSERT), 修改(UPDATE)和删除(DE

2、LETE)语句的运用。四, 试验内容 (一) 规定内容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 st

3、udentWHERE sage NOT BETWEEN 20 AND 23; 方法2： SELECT sname,sdept,sage FROM student WHERE sage23;-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个字为阳字的学生的姓名和学号。S

4、ELECT 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=3 -也可为:COUNT(cno)=3-3_19 查询有2门以上课程是80分以上的学生的学号及(80分以上的)课程数SELECT sno 学号,COUNT(

5、*) 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 sn

6、ame 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 查询每一门课的间接先修课即先修课的

7、先修课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

8、 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 s

9、name 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

10、 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 sageany (select sage from student where sdept=cs

11、) and sdeptcs /* 留意这是父查询块中的条件*/-3_31 查询其他系中比计算机科学系全部学生年龄都小的学生学号, 姓名和年龄。select sname,sage from student where sageall (select sage from student where sdept=cs) and sdeptcs-3_32 查询全部选修了1号课程的学生学号, 姓名。SELECT sno,sname FROM student WHERE EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno=1); -相关子查询-3_

12、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 sn

13、o=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

14、.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 -复制表构造wh

15、ere sno is null-或Select * into sc2 from sc -复制表构造Where not exists (select * from sc) 4 集合查询-3_38 查询计算机系的学生及年龄不大于19岁的学生，并按年龄降序排列。-方法1：并操作(UNION)SELECT *FROM studentWHERE sdept=csUNION SELECT *FROM student WHERE sage=19Order by sage desc-3_39 查询选修了课程1或者选修了课程2的学生学号。SELECT snoFROM scWHERE cno=1UNION SEL

16、ECT 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：交操作

17、SELECT *FROM studentWHERE sdept=cs INTERSECTSELECT *FROM studentWHERE sage=19-方法2：标准SQL中没有供应集合交操作，但可用其他方法间接实现。-本例事实上是：查询计算机系年龄不大于19岁的学生SELECT *FROM studentWHERE sdept=cs AND sage19-方法2：标准SQL中没有供应集合差操作，但可用其他方法间接实现。-本例事实上是：查询计算机系年龄不大于19岁的学生SELECT *FROM studentWHERE sdept=cs AND sage=60Group by student

18、.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分

19、以上。查询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 exist

20、s(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 C

21、REATE 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

22、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 grade45

23、or 月工资45; UNION Select 姓名,年龄,月工资 from 职工 Where 月工资=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,

24、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 考号,姓名,语文,数学,英语,综合,语文+数学+英语+综

25、合 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