2022年数据库系统原理与设计设计实验参考 .pdf

实验 D02: 单表查询实验七、设计性实验1实验要求（1）查询计算机系学生的学号和姓名。Select sno,sname from s where dept=计算机系 （2）查询选修了课程的学生学号。Select distinct sno from sc （3）查询选修 C01 课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。select sno,grade from sc where cno= C01order by grade desc, sno （4）查询每门课程的平均分。Select cno,avg(grade) from sc group by cno （5）查询学校开设的课程总数。Select count(distinct cno) from sc （6）查询选修两门及两门以上课程的学生学号。Select sno from sc group by sno Having count(cno)=2 （7）查询年龄在20-22 之间的男生的学号、姓名、系部。Select sno,sname, dept from s where sex= 男 and age between 20 and 22 （8）查询选修C01课程的学生人数。Select count(sno) from sc where cno= C013思考题（1）使用 ORDER BY 子句后 SELECT 后的数据列有什么限制吗？使用GROUP BY 呢？答：使用 ORDER BY 子句后 SELECT 后的数据列没有限制，而使用GROUP BY 字句后 SELECT 后的数据列只能是 group by 后跟的数据列和聚集函数。（2）执行 select max(grade) from sc, 写出执行结果；执行 select sno, max(grade) from sc group by sno,写出执行结构。思考：使用GROUP BY子句后，语句中的统计函数的运行结果有什么不同?执行 select max(grade) from sc 只有一个结果， 整个选课表的平均成绩，而 select sno, max(grade) from sc group by sno显示的是每个学生的选课平均成绩，使用group by字句后，根据其后的字段对数据表进行分组，再对每一个组进行分组统计。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - 实验 D03： 多表查询实验七、设计性实验1实验要求（1）查询“计算机系“学生所选课程的成绩表。Select sno,sname, cno,cname ,grade from s,sc,c where s.sno=sc.sno and o=o and dept= 计算机系 （2）查询成绩比该课程平均成绩低的同学的成绩表。Select sno,cno,grade from sc sc1 where grade=2 （5）查询年龄在20-22 之间的男生的选修的课程号。Select cno from sc where sno in(select sno from s where sex= 男 and age between 20 and 22) （6）查询选修数据结构课程的学生人数。Select count(sno) from sc where cno in (select cno from c where cname=数据结构 ) （7）查询每门选课成绩在80分以上的学生学号、姓名。Select sno,sname from s where sno in (select sno from sc group by sno having min(grade)=80) 2思考题（1）多表的连接查询是否都可以转换成嵌套查询？什么情况下连接查询不能用嵌套查询表示？答：当查询结果涉及多张表中的字段时，多表连接查询不能转换成嵌套查询；当涉及左外连接或者右外连接时多表连接查询也不能转换成嵌套查询。（2）多表连接查询和IN嵌套子查询从查询效率上来说，哪种查询的效果更好？为什么? 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 答：若嵌套子查询为无关嵌套查询时，IN嵌套子查询的查询效率比多表连接查询查询要高。因为无关嵌套查询只对子查询涉及的表扫描1次，而多表连接要做笛卡儿乘积运算，子表要查询多次。若嵌套子查询为相关嵌套查询时，则和多表连接查询的查询效率差不多。实验 D04 参考答案七、设计性实验1实验要求（1） 在 S表中添加完整性约束：SNO设置为主键， SEX 的却省值为 女,AGE 的有效值为16-25。Alter table s add primary key(sno), default 女for sex, check (age between 16 and 25) （2）创建 1张与 S相同的表 S1，在创建的同时将SNO 设置为主键， SEX 的却省值为女,AGE 的有效值为 16-25 ，并将 S表中的数据插入到S1中，插入不同的记录来严整设置的完整性。Create table s1(SNO VARCHAR (10) primary key ,SNAME VARCHAR(20),DEPA VARCHAR(20), AGE INT check (age between 16 and 25), SEX CHAR(4) default 女 ) Insert into s1 select sno,sname, depa, age, sex from s Insert into s1 (sno,sname) values( 200512 , zxx ) Insert into s1(sno,sname,age) values( 200618 , zxx ,26) Insert into s1(sno,sname,age) values( 200618 , zxx ,18) （3）创建 1张与 SC相同的表 SC1，将（ SNO ，CNO ）设置为主键，SNO 和CNO 设置为外键，并将SC表中的数据复制到SC1中，插入不同的记录来严整设置的完整性。create table SC1(SNO VARCHAR(10) NOT NULL, Cno VARCHAR(10) NOT NULL, GRADE DECIMAL) alter table sc1 add primary key(sno,cno), foreign key(sno) references s(sno), foreign key(cno)references c(cno) insert into sc1 values( 200620 , c01 ,90) insert into sc1 values(null,null,80) insert into sc1 values( 200512 , c06 ,80) （4）创建 1张与 C表相同的表C1，将 C中数据插入到 C1后，在 C1上添加完整性约束：将（CNO ）设置为主键， CPNO 引用 CNO ，CREDIT 值为 1-6 。create table C1(CNO VARCHAR(10) NOT NULL, CNAME VARCHAR(20),cpno varchar(10),credit int) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - alter table c1 add primary key (cno), foreign key (cpno) references c(cno), check (credit between 1 and 6) 2思考题SQL Server 中有哪些完整性功能？保证数据完整性还需要注意哪些方面的问题？答： SQL Server 有 primary key ， foreign key, unique, check, default 等完整性约束功能。在定义完整性约束要考虑表之间的联系，特别是定义foreign key 时要确保外键在别的表中已经定义主键。实验 D05： 数据操作与索引实验一、实验名称和性质所属课程数据库原理实验名称数据操作与索引实验学时2 实验性质? 验证综合? 设计必做 /选做? 必做选做二、实验目的1掌握数据操作-插入、删除、修改；2掌握数据表的索引方法；3掌握视图的创建机制及其上的操作。七、设计性实验1实验要求（1）将 S表系部为“计算机系”学生对应的系部改为CS 。Update s Set depa= CS where depa= 计算机系 （2） 创建一个与 SC表相同新数据表SCNEW， 查询 SC表中成绩为空的记录并将其复制到SCNEW表中。Select * into SCNEW from sc where grade is not null （3）删除 SC 表中成绩为空的选课记录。Delete from sc where grade is null （4）根据 S表中姓名字段建立唯一性索引。Create unique index ui_s_sname on s(sname) 提示：因为 S中有重名学生，所以不能建立姓名字段的唯一索引（5）建立 S表上 SNO 的聚族索引。Create clustered index ci_s_sno on s(sno) （6）建立 SC 表上根据 SNO 升序， CNO 降序的复合索引。Create index i_sc_snocno on sc(sno, cno desc) （7）建立每门课程的课程号、课程名、选课人数、平均分的视图。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - Create view vCourse As Select o, cname, count(sno) selectNum, avg(grade) avgGrade from sc, c Where o=o Group by o,cname 2思考题（1）是否任何可以对任意的视图进行修改？什么样的视图上不能进行修改操作？答：不是任何试图都可以进行修改，当视图中存在聚集函数或者其他函数计算时不能修改视图。（2）如果表中某个字段重复值比较多，要不要在该字段上建立索引？为什么？答：若表中某个字段的重复值比较多，则不要在该字段上建立索引。因为， 重复值多时建立索引后根据索引找到该字段值还有多个，则要到表中查找多次记录，影响查找速度。实验 D06： SQL 编程及存储过程实验一、实验名称和性质所属课程数据库原理实验名称SQL 编程及存储过程实验学时2 实验性质? 验证综合? 设计必做 /选做? 必做选做二、实验目的1了解 T-SQL 的基本数据类型、函数；2掌握局部变量的定义和赋值；3掌握存储过程的定义及调用七、设计性实验1实验要求（1）调用帮助系统来查找系统存储过程或函数来显示SQL SERVER 的版本号及当前系统时间。（提示：到帮助中根据关键字等查询到对应的函数或存储过程来完成任务）Print Getdate() Print version 或者 xp_msver （2） 编写存储过程：完成1！+2！+,+n！的计算。（提示：传入参数n，根据 N用循环控制来求对应的值）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - Create proc SumFactor n int As Declare sum, i,factor int Set sum=0 Set factor=1 Set i=1 While (i=n) Begin factor*=i sum+=factor i+=1 end print 1!+2!+ + +cast(n as varchar(4)+ = +cast(sum as varchar(10) （3）将S表中的 DEPA 字段内容改为中文：IS- 信息系， CS-计算机系， PH-物理系， MA-数学系。（提示： 用UPDATE 语句来修改 DEPA 字段，DEPA 的值根据不同英文缩写要修改为不同的中文系名，用 CASE 函数来完成）Update s Set depa=case depa When 信息系 then IS When 计算机系 then CS When 物理系 then PH When 数学系 then MA end From s （4） 将C表上增加一个字段seleNUM，并编写存储过程：传入学号和课程号后，完成在 SC表中插入相应的选课记录，并在C表中对应课程的seleNUM加1。Alter table c add seleNUM int Update c Set selenium=(select count(sno) from sc where cno=o) Create proc addSeleNum sno varchar(10), cno varchar(10) As Insert into sc(sno,cno) values(sno,cno) Update c Set seleNUM=seleNUM+1 where cno=cno 调用 : exec addSeleNUM 200901 , c032思考题（1）简单描述存储过程的使用步骤？答：存储过程的使用步骤：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - 创建存储过程，用create proc 语句编写存储过程代码；运行存储过程，将代码编译为机器可执行代码；调用存储过程，用EXEC 存储过程名来调用存储过程。（2）带参数的存储过程定义时参数定义语句可以在AS后面吗？答：存储过程创建时参数是在存储过程名与AS之间，而不是在AS后面。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -