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

实验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=C01 order 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 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）使用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字句后，根据其后的字段对数据表进行分组，再对每一个组进行分组统计。实验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嵌套子查询从查询效率上来说，哪种查询的效果更好？为什么?答：若嵌套子查询为无关嵌套查询时，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 sInsert 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 sc1add 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)alter table c1add 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 sSet 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）建立每门课程的课程号、课程名、选课人数、平均分的视图。Create view vCourseAsSelect o, cname, count(sno) selectNum, avg(grade) avgGrade from sc, cWhere o=oGroup by o,cname2思考题（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用循环控制来求对应的值）Create proc SumFactorn intAsDeclare sum, i,factor intSet sum=0Set factor=1Set i=1While (i=n)Begin factor*=i sum+=factor i+=1endprint 1!+2!+cast(n as varchar(4)+=+cast(sum as varchar(10)（3）将S表中的DEPA字段内容改为中文：IS-信息系，CS-计算机系，PH-物理系，MA-数学系。（提示：用UPDATE语句来修改DEPA字段，DEPA的值根据不同英文缩写要修改为不同的中文系名，用CASE函数来完成）Update sSet depa=case depa When 信息系 then IS When 计算机系 then CS When 物理系 then PH When 数学系 then MA endFrom 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)AsInsert into sc(sn