数据库系统原理与设计(万常选)设计实验参考

1、实验 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

2、by cno(5) 查询学校开设的课程总数。Select count(distinct cno) from sc(6) 查询选修两门及两门以上课程的学生学号。Select sno from sc group by snoHaving 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) 使用ORDE

3、R B子句后SELECTS的数据列有什么限制吗？使用GROUP BY呢？答：使用ORDEBY子句后SELECT后的数据列没有限制，而使用GROUBY字句后SELEC后的数据列只能是 group by 后跟的数据列和聚集函数。( 2 )执行 select max(grade) from sc, 写出执行结果；执行 select sno, max(grade) from sc group by sno, 写出执行结构。思考：使用 GROUP BY子句后，语句中的 统计函数的运行结果有什么不同 ?执行 select max(grade) from sc 只有一个结果， 整个选课表的平均成绩， 而 s

4、elect sno, max(grade) from sc group by sno 显示的是每个学生的选课平均成绩，使用 group by 字句后，根据其后的字段对数据表进行分组，再对每一个组进行分组统计。实验 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

5、sc sc1 where grade（select avg（grade） from sc sc2 where o=o）（3）查询选修 C01 课程的学生学号、 课程名、 成绩， 并要求对查询结果按学号的降序排列， 如果学号相同则按成绩的升序排列。Select sno,cname,grade from sc,c where o=o and cno=C01Order by grade desc, sno（4）查询选修两门及两门以上课程的学生学号及姓名。Select sno,sname from s,sc where s.sno=oGroup by

6、 sno,snameHaving count（cno）=2（5）查询年龄在 20-22 之间的男生的选修的课程号。Select cno from sc where sno in（select sno from s where sex=男 and age between20 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

7、in （select sno from sc group by sno having min（grade）=80）2思考题（1）多表的连接查询是否都可以转换成嵌套查询？什么情况下连接查询不能用嵌套查询表示？ 答：当查询结果涉及多张表中的字段时， 多表连接查询不能转换成嵌套查询； 当涉及左外连接或 者右外连接时多表连接查询也不能转换成嵌套查询。2）多表连接查询和 IN 嵌套子查询从查询效率上来说，哪种查询的效果更好？为什么答：若嵌套子查询为无关嵌套查询时，IN嵌套子查询的查询效率比多表连接查询查询要高。因为无关嵌套查询只对子查询涉及的表扫描 1 次，而多表连接要做笛卡儿乘积运算，子表要查询多次。

8、若嵌套子查询为相关嵌套查询时，则和多表连接查询的查询效率差不多。实验 D04 参考答案七、设计性实验1实验要求(1 )在S表中添加完整性约束：SNO设置为主键，SEX的却省值为女，AGE的有效值为16-25。Alter table s add primary key(sno), default女 for sex, check (age between 16 and25)(2) 创建1张与S相同的表S1,在创建的同时将SN设置为主键，SEX的却省值为女,AGE的有效值为16-25 ，并将S表中的数据插入到S仲，插入不同的记录来严整设置的完整性。Create table s1(SNO VARCHA

9、R (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 sInsert into s1 (sno,sname) values(2005 1 2, zxx )Insert into s1(sno,sname,age) values(2006 1 8, zxx ,26)Insert into s1(sno,sname,ag

10、e) values(2006 1 8, zxx ,18)(3) 创建1张与SC相同的表SC1,将(SNO CNO设置为主键，SN刖CNO设置为外键，并将 SC表中的数据复制到SC1中，插入不同的记录来严整设置的完整性。create table SC1(SNO VARCHAR(10) NOT NULL, Cno VARCHAR(10) NOT NULL, GRADE DECIMAL)alter table sc1add primary key(sno,cno), foreign key(sno) references s(sno), foreignkey(cno)references c(cno

11、)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弓I用CNO, CREDI1值为1-6。create table C1(CNO VARCHAR(10) NOT NULL, CNAME VARCHAR(20),cpno varchar(10),creditint)alter table cladd prim

12、ary key (eno), 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:数据操作与索引实验、实验名称和性质所属课程数据库原理实验名称数据操作与索引实验学

13、时2实验性质:? 验证 综合？ 设计必做/选做?必做选做二、实验目的1 掌握数据操作-插入、删除、修改；2 掌握数据表的索引方法；3 掌握视图的创建机制及其上的操作。七、设计性实验1 实验要求(1 )将S表系部为“计算机系”学生对应的系部改为CS。Update sSet depa= CS where depa=计算机系(2) 创建一个与SC表相同新数据表SCNE加询SC表中成绩为空的记录并将其复制到SCNEW 表中。Select * i nto SCNEW from sc where grade is not null(3) 删除SC表中成绩为空的选课记录。Delete from sc whe

14、re grade is n ull(4) 根据S表中姓名字段建立唯一性索引。Create unique index ui_s_sname on s(sname)提示：因为S中有重名学生，所以不能建立姓名字段的唯一索引(5) 建立S表上 SNO勺聚族索引。Create clustered in dex ci_s_s no on s(s no)(6) 建立SC表上根据SN升序，CNOI序的复合索引。Create in dex i_sc_s nocno on sc(s no, eno desc)(7 )建立每门课程的课程号、课程名、选课人数、平均分的视图。Create view vCourseAsS

15、elect o, cname, count(sno) selectNum, avg(grade) avgGrade from sc, cWhere c.c no=sc.c noGroup by c.c no,cn ame2 思考题(1) 是否任何可以对任意的视图进行修改？什么样的视图上不能进行修改操作？答：不是任何试图都可以进行修改，当视图中存在聚集函数或者其他函数计算时不能修改视图。(2) 如果表中某个字段重复值比较多，要不要在该字段上建立索引？为什么？答：若表中某个字段的重复值比较多，则不要在该字段上建立索引。因为，重复值多时建立索引后根据索引找到该字段值还有多个，则要到表中查找多

16、次记录，影响查找速度。实验D06: SQL编程及存储过程实验所属课程数据库原理实验名称SQL编程及存储过程实验学时2实验性质? 验证 综合？ 设计必做/选做?必做选做二、实验目的1了解T-SQL的基本数据类型、函数；2 掌握局部变量的定义和赋值；3 掌握存储过程的定义及调用七、设计性实验1 实验要求(1 )调用帮助系统来查找系统存储过程或函数来显示SQL SERVER勺版本号及当前系统时间。(提示：到帮助中根据关键字等查询到对应的函数或存储过程来完成任务)Print Getdate()Print versio n或者 xp_msver(2)编写存储过程：完成1 ! +2! +n!的计算。(提示

17、：传入参数n,根据N用循环控制来求对应的值)Create proc SumFactorn intAsDeclare sum, i,factor intSet sum=0Set factor=1Set i=1While (i=n)Beginfactor*=isum+=factori+=1endprint 1!+2!+ +cast( n as varchar(4)+=+cast(sum as varchar(IO)(3)将S表中的DEPA字段内容改为中文：IS-信息系，CS-计算机系，PH-物理系，MA微学系。(提示：用UPDAT语句来修改DEPA字段，DEPA勺值根据不同英文缩写要修改为不同的中

18、文系名，用CASE!数来完成)Update sSet depa=case depaWhen信息系 thenISWhen计算机系 thenCSWhen物理系 thenPHWhen数学系 thenMAendFrom s(4)将C表上增加一个字段seleNUM,并编写存储过程：传入学号和课程号后，完成在SC表中插入相应的选课记录，并在C表中对应课程的seleNUM加1。Alter table c add seleNUM intUpdate cSet selenium=(select count(sno) from sc where cno=o)Create proc addSeleNumsno varchar(10), cno varchar(10)AsIn