《数据库原理与应用》实验题目及答案.doc

数据库原理与应用实验作业布置实验1 数据库的建立修改与删除完成以下实验报告：数据库原理与应用实验报告院名专业年级班级学号姓名教师成绩一、实验题目实验1 数据库的建立修改与删除二、实验环境操作系统：Windows XP。数据库管理系统：MS SQL Server 2000 或MS SQL Server 2008。三、实验目的 1熟悉SQL Server 2000上机环境，学会SQL Server 2000“企业管理器”以及“查询分析器”的启动与退出；或熟悉MS SQL Server 2008上机环境，学会SQL Server Management Studio的启动与退出。2掌握建立数据库的两种方法。3掌握查看和修改数据库的两种方法。4掌握删除数据库的两种方法。四、实验内容 (一) 规定内容1. 在用户方便的存储空间上建立存放用户数据库的文件夹。本实验在D盘根目录下建立文件夹：D:SQL2000DB，再建立存放T_SQL源代码的文件夹：D:SQL2000DBT_SQL2利用企业管理器创建学生课程数据库ST，参数如表1.1所示。表1.1 ST数据库属性设置文件组文件类型逻辑文件名物理文件名初始大小自动增量最大容量主文件组PRIMARY主数据文件ST_DataD:SQL2000DBST_Data.mdf3M1M不限制增长日志文件ST_logD:SQL2000DBST_log.ldf2M10%10M3利用企业管理器查看ST数据库的属性。4使用T-SQL语句建立学生-课程数据库ST，参数如表1.1所示。5使用T-SQL语句建立数据库JXDB，参数如表1.2所示。表1.2 数据库JXDB属性设置文件组文件类型逻辑文件名物理文件名初始大小自动增量最大容量主文件组PRIMARY主数据文件JXDB_DataD:SQL2000DBJXDB_Data.mdf3M10%不限制增长次数据文件JXDB_Data1D:SQL2000DBJXDB_Data1.ndf2M10%30M自定义文件组JX_Group1次数据文件JXDB_Data2D:SQL2000DBJXDB_Data2.ndf1M1M20M次数据文件JXDB_Data3D:SQL2000DBJXDB_Data3.ndf1M1M10M日志文件JXDB_logD:SQL2000DBJXDB_log.ldf2M10%不限制增长6使用T-SQL语句为数据库JXDB添加文件组JX_Group2，并为此文件组添加1个数据文件 JXDB_Data4；添加1个日志文件JXDB_log1。参数由用户自己设定。7使用T-SQL语句修改数据库JXDB中次数据文件JXDB_Data4的属性：文件初始大小改为5M，增长方式改为每次按20%增长。删除日志文件JXDB_log1。8使用T-SQL语句删除数据库JXDB。(二) 自定内容五、出现的问题及解决方法实验2 表结构的建立修改删除及完整性约束条件定义完成以下实验报告：数据库原理与应用实验报告院名专业年级班级学号姓名教师成绩一、实验题目实验2 表结构的建立修改删除及完整性约束条件定义二、实验环境操作系统：Windows XP。数据库管理系统：MS SQL Server 2000或MS SQL Server 2008。三、实验目的 1掌握建立、修改与删除表结构的两种方法。2掌握定义完整性约束条件的方法。3掌握利用企业管理器和使用T-SQL语句向表中插入记录的方法。四、实验内容 (一) 规定内容1对学生课程数据库ST进行完整性约束条件规划设计。设计结果如表2.1所示。表2.1 学生课程数据库ST完整性约束条件表名完整性约束条件约束类型学生(1) 学号取值唯一且不能为空值主健(2) 姓名不能为空值Not null(3) 性别只能为“男”或“女”。默认值为男Check;default课程(4) 课号取值唯一且不能为空值主健(5) 课名取值唯一unique(6) 先修课号只能是学校已开设的课号修改课号时，与之对应的先修课号自动级联修改若某课程有先修课，则其先修课程删除时该课程的先修课号置空值当插入或修改“先修课号”时，若该课号在表中不存在，则递归插入外键触发器触发器触发器选修(7) 学号、课号取值唯一且不能为空值主健(8) 成绩采用百分制check(9) 学号只能是学生表中已有的学号。当修改某学生学号时，该生所有选课记录的学号自动级联修改；当删除某学生时，该生所有选课记录自动级联删除。外键级联更改级联删除(10) 课号只能是课程表中已有的课号；当修改课程表中的课号时，选修表中所有选修该课程的课号自动级联修改；当删除课程表中某课程时，若选修表中该课程有学生选修，则拒绝删除。外键级联更改拒绝删除2对学生课程数据库ST中各个表结构进行详细设计。学生、课程及选修表结构设计结果分别如表2.2、2.3和2.4所示。表2.2 “student”表结构列名数据类型允许Null值约束条件sno char(9)primary keysnamechar(8)Not nullssexchar(2)check(性别 in(男,女)default 男 sage smallintsdeptchar(20)表2.3 “course”表结构列名数据类型允许Null值约束条件cnochar(4)primary keycnamechar(16)uniqueccreditsmallintcpnochar(4)references 课程(课号)级联更改,置空值删除 触发器递归插入触发器表2.4 “sc”表结构列名数据类型允许Null值约束条件snochar(9)references 学生(学号)on delete cascade on update cascadecnochar(4)references 课程(课号)on update cascadegradedecimal(5,1)check(成绩=0 and成绩=100)表级约束：primary key(学号, 课号) 3使用企业管理器在数据库ST中建立学生、课程及选修表结构。4使用企业管理器为学生、课程及选修表录入若干行数据。5使用企业管理器删除学生、课程及选修表6使用T_SQL语言在数据库ST中建立学生、课程及选修表结构。7使用T_SQL语言为学生、课程及选修表插入若干行数据。(二) 自定内容五、出现的问题及解决方法实验3 数据查询与更新完成以下实验报告：数据库原理与应用实验报告院名专业年级班级学号姓名教师成绩一、实验题目实验3 数据查询与更新二、实验环境操作系统：Windows XP。数据库管理系统：MS SQL Server 2000或MS SQL Server 2008。三、实验目的 1掌握SELECT语句的语法、语义及语用。2掌握基于单表的查询方法。3掌握基于多表的查询方法。4掌握相关与不相关的嵌套查询。5掌握集合查询。6掌握插入(INSERT)、修改(UPDATE)和删除(DELETE)语句的使用。四、实验内容 (一) 规定内容1. 单表查询USE ST-3_1 查询全体学生的详细记录。select *from student;运行结果：sno sname ssex sage sdept- - - - -201313101 李勇 男 20 CS 201313102 刘晨 女 19 CS 201313103 王敏 女 18 MA 201313104 张立 男 19 IS -3_2 查询选修了课程的学生学号。select distinct sno from sc;运行结果：sno-201313101201313102-3_3 将学生表中的sno、sname这2列合并为1列snosname输出，其余列不变。select sno+sname snosname,ssex,sage,sdeptfrom student;运行结果：snosname ssex sage sdept- - - -201313101李勇 男 20 CS 201313102刘晨 女 19 CS 201313103王敏 女 18 MA 201313104张立 男 19 IS -3_4 查询年龄不在2023岁之间的学生姓名、系别和年龄。select sname,sdept,sagefrom studentwhere sage23;运行结果：sname sdept sage- - -刘晨 CS 19王敏 MA 18张立 IS 19-3_5 查询计算机科学系(cs)、数学系(ma)和信息系(is)学生的姓名和性别。SELECT sname,ssexFROM studentWHERE sdept IN ( cs,ma,is );运行结果：sname ssex- -李勇 男刘晨 女王敏 女张立 男-3_6 查询所有姓刘学生的姓名、学号和性别。SELECT sname,sno,ssexFROM studentWHERE sname LIKE 刘%;运行结果：sname sno ssex- - -刘晨 201313102 女-3_7 查询名字中第2个字为阳字的学生的姓名和学号。SELECT sname,snoFROM studentWHERE sname LIKE _阳%;运行结果：sname sno- -3_8 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。 -查询缺少成绩的学生的学号和相应的课程号。SELECT sno,cno FROM scWHERE grade IS NULL;运行结果：sno cno- -3_9 查询计算机系年龄在20岁以下的学生姓名。SELECT snameFROM studentWHERE sdept=cs AND sage=3 运行结果：sno-201313101-3_19 查询有2门以上课程是90分以上的学生的学号及(90分以上的)课程数SELECT sno 学号,COUNT(*) 90分以上的课程数FROM scWHERE grade=90GROUP BY sno HAVING COUNT(*)=2 运行结果：学号 90分以上的课程数- -2. 连接查询-自然连接-3_20 查询每个学生及其选修课程的情况。SELECT sname,cno,grade FROM student,sc WHERE student.sno = sc.sno运行结果：sname cno grade- - -李勇 1 92.0李勇 2 35.0李勇 3 88.0刘晨 2 90.0刘晨 3 50.0-左外连接-3_21 查询每个学生及其选修课程的情况(包括没有选修课程的学生)。SELECT student.sno,sname,cno,gradeFROM student LEFT JOIN sc ON student.sno = sc.sno运行结果：sno sname cno grade- - - -201313101 李勇 1 92.0201313101 李勇 2 35.0201313101 李勇 3 88.0201313102 刘晨 2 90.0201313102 刘晨 3 50.0201313103 王敏 NULL NULL201313104 张立 NULL NULL-自身连接: 一个表与其自己进行连接，称为表的自身连接-3_22 查询每一门课的间接先修课（即先修课的先修课）SELECT FIRST.cno 课号,SECOND.cpno 间接先修课FROM course FIRST,course SECONDWHERE FIRST.cpno = SECOND.cno运行结果：课号 间接先修课- -1 7 3 5 4 NULL5 6 7 NULL-复合条件连接-3_23 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。SELECT student.sno, sname FROM student,sc WHERE student.sno = sc.sno AND o=2 AND sc.grade =90 运行结果：sno sname- -201313102 刘晨 -多表连接-3_24 查询每个学生的学号、姓名、选修的课程名及成绩。SELECT student.sno,sname,cname,gradeFROM student,sc,courseWHERE student.sno = sc.sno and o = o 运行结果：sno sname cname grade- - - -201313101 李勇 数据库 92.0201313101 李勇 数学 35.0201313101 李勇 信息系统 88.0201313102 刘晨 数学 90.0201313102 刘晨 信息系统 50.0 3 嵌套查询-3_25 查询与“刘晨”在一个系学习的学生。select sno,sname,sdeptfrom student where sdept in -当内查询结果最多只有一个值时可用=代替in (select sdept from student where sname=刘晨)运行结果：sno sname sdept- - -201313101 李勇 CS 201313102 刘晨 CS -3_26 查询所有姓名相同的学生select *from studentwhere sname in (select sname from student group by sname having count(*)1)order by sname,sno 运行结果：sno sname ssex sage sdept- - - - -3_27 查询选修了课程名为“信息系统”的学生学号和姓名。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=信息系统)运行结果：sno sname- -201313101 李勇 201313102 刘晨 -3_28 找出每个学生超过他选修课程平均成绩的学号、课号及成绩。SELECT x.* FROM sc x WHERE grade=(SELECT AVG(grade) FROM sc y WHERE y.sno=x.sno)运行结果：sno cno grade- - -201313101 1 92.0201313101 3 88.0201313102 2 90.0-3_29 查询其他系中比计算机科学系某一学生年龄小的学生学号、姓名和年龄。 select sname,sage from student where sageany (select sage from student where sdept=cs) and sdeptcs 运行结果：sname sage- -王敏 18张立 19-3_30 查询其他系中比计算机科学系所有学生年龄都小的学生学号、姓名和年龄。select sname,sage from student where sageall (select sage from student where sdept=cs) and sdeptcs 运行结果：sname sage- -王敏 18-3_31 查询所有选修了1号课程的学生学号、姓名。SELECT sno,sname FROM student WHERE EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno=1);运行结果：sno sname- -201313101 李勇 -3_32 查询没有选修1号课程的学生学号、姓名。SELECT sno,sname FROM student WHERE NOT EXISTS (SELECT * -此处*可换为sno FROM sc WHERE sno=student.sno AND cno=1); 运行结果：sno sname- -201313102 刘晨 201313103 王敏 201313104 张立 -3_33 查询选修了全部课程的学生姓名。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自然连接运行结果：sname-3_34 查询至少选修了学生201313102选修的全部课程的学生号码。SELECT DISTINCT sno -查询这样的学生的xFROM sc SCXWHERE NOT EXISTS -不存在这样的课程y (SELECT * FROM sc SCY WHERE SCY.sno=201313102 AND -学生选修了yNOT EXISTS -学生x没有选修y (SELECT * FROM sc SCZ WHERE SCZ.sno=SCX.sno AND SCZ.cno=SCY.cno)运行结果：sno-201313101201313102 4 集合查询-3_35 查询计算机科学系的学生及年龄不大于19岁的学生。SELECT *FROM studentWHERE sdept=csUNION SELECT *FROM student WHERE sage=19运行结果：sno sname ssex sage sdept- - - - -201313101 李勇 男 20 CS 201313102 刘晨 女 19 CS 201313103 王敏 女 18 MA 201313104 张立 男 19 IS -3_36 查询选修了课程1或者选修了课程2的学生。SELECT snoFROM scWHERE cno=1UNION SELECT snoFROM scWHERE cno=2运行结果：sno-201313101201313102-3_37 查询计算机科学系的学生与年龄不大于19岁的学生的交集。SELECT *FROM studentWHERE sdept=cs INTERSECTSELECT *FROM studentWHERE sage19运行结果：sno sname ssex sage sdept- - - - -201313102 刘晨 女 19 CS 5综合-3_40 学士学位授予条件为：至少选修了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)运行结果：sno sname ssex sage sdept- - - - -3_41 学士学位授予条件为：至少选修了数据库,操作系统,数据结构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)运行结果：sno sname ssex sage sdept- - - - -3_42 统计每个学生(含未选修课程的学生)的学分。注意：不及格课程无学分。SELECT student.sno 学号,sname 姓名,sum(ccredit) 学分FROM sc JOIN course ON o=o JOIN student ON student.sno = sc.snoWhere grade=60Group by student.sno,sname运行结果：学号 姓名 学分- - -201313101 李勇 8201313102 刘晨 25 插入(INSERT)、修改(UPDATE)和删除(DELETE)语句的使用-3_43 插入一条选课记录( 201313103,1)。if not exists(select * from sc where sno=201313103 and cno=1) INSERT INTO sc(sno,cno) VALUES (201313103,1) -新插入的记录在Grade列上取空值运行结果：(1 行受影响)-3_44 对每一个系，求学生的平均年龄，并把结果存入数据库。 -第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) -学生平均年龄运行结果：命令已成功完成。-3_45 复制表 先建立与sc表结构完全相同的表sc1,sc2 再将sc复制到sc1中。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = sc1 AND type = U) DROP TABLE sc1 GOSelect * Into sc1 from sc -复制表use STIF EXISTS(SELECT name FROM sysobjects WHERE name = sc2 AND type = U) DROP TABLE sc2 GOSelect * into sc2 from sc -复制表结构Where not exists (select * from sc)运行结果：(6 行受影响)(0 行受影响)-3_46 合并表 先向表sc2输入数据 再将sc2合并到sc1中。insert into sc2 values(201313103,2,92);insert into sc2 values(201313102,5,75);insert into sc2 values(201313104,3,68);insert into sc2 values(201313102,7,80);insert into sc2 values(201313101,6,47);select *from sc1 union allselect *from sc2运行结果：sno cno grade- - -201313101 1 92.0201313101 2 35.0201313101 3 88.0201313102 2 90.0201313102 3 50.0201313103 2 92.0201313102 5 75.0201313104 3 68.0201313102 7 80.0201313101 6 47.0-3_47 将信息系所有学生的年龄增加1岁。UPDATE studentSET sage=sage+1where sdept=IS;运行结果：(1 行受影响)-3_48 对计算机科学系(cs)全体学生选修2号课程分数=36的按10*sqrt(grade)计算。 update sc set grade=10*sqrt(