数据库系统实验解答.docx

数据库系统实验解答实验一 用SQL语句完成实验内容2-5的各种操作。 * 实验内容2： 1)CREATE DATABASE students 具体格式： CREATE DATABASE students ON PRIMARY ( NAME=students_Data, FILENAME=E: students_Data.MDF, SIZE=10, MAXSIZE=300, FILEGROWTH=5) LOG ON ( NAME=students_Log, FILENAME=E: students_Log.MDF, SIZE=10, MAXSIZE=200, FILEGROWTH=2) ) 2)DROP DATABASE Students 3)CREATE DATABASE bookdb 4) sp_helpdb bookdb 项目 内容 数据库所有者 8C62E96C274F451Administrator 数据库大小 1.24 MB 可用空间 0.51MB 数据文件名 Bookdb_data 数据文件物理存放位置 C:Program FilesMicrosoft SQL ServerMSSQLdatabookdb_data.mdf 日志文件名 Bookdb_log 日志文件物理存放位置 C:Program FilesMicrosoft SQL ServerMSSQLdatabookdb_log.LDF * 实验内容3： 1) USE bookdb create table book (Book_id int primary key,Book_name char (50) not null,Price float not null,Publisher char (26) not null,City char (20) not null) 2) create table Orderform (Order_id int primary key,Book_id int not null,Book_number int not null,Order_date datetime not null,Client_id int not null) 3) create table Authors (Author_id int primary key,Author_name char (8) not null,Address char (50) ,Telphone char (15) ) 4) create table Clients (Client_id int primary key,Client_name char (8) not null,Address char (50) not null) * 实验内容4： 1)alter table book add author_id int not null default '' 2) alter table book drop city 3) alter table book add constraint unqBkname UNIQUE(Book_name), constraint chkPrice check 添加约束，要求Book_name 取值不能重复，Price的取值在0100之间。 * 实验内容5： create index ix_authors on authors(author_name) Create index ix_Orderform on Orderform (Order_id asc,Order_date desc) with (FILLFACTOR=10) 创建所有学生的基本信息和选课信息的视图S_C_all。 CREATE VIEW S_C_all AS SELECT student.*, o,sc.score FROM student,sc WHERE student.sno=sc.sno 基于上述视图S_C_all查询选修C2课程的学生姓名及其所在系。 select sno,sdept from S_C_all where cno=C2 创建一个视图CS，包含选课成绩及格的学生编号、所选课程号和该课程成绩。 create view CS as select sno,cno,score from sc where score>=60 创建一个基于视图的视图S_CS，基于中建立的视图，定义一个包CS含学生编号、学生所选课程数目和平均成绩的视图。 create view S_CS (sno,cnt,avg_sc) as select sno,count(*),avg(score) from CS group by sno 创建带表达式的视图，由学生姓名、所选课程名、所有课程成绩再多5分这几个属性组成。 create view BT_SC(sname,cname,newscore) as select student.sname, ame,sc.score+5 from student,course,sc where student.sno=sc.sno and o=o 插入元组到视图CS中。 insert into CS values 删除视图CS。 drop view CS 实验二 1、student表中插入元组('99006', '郭启芳', '女', 15, 'CS') ，给出操作结果，分析原因并进行修改。 INSERT Student(sno,sname,ssex,sage,sdept) VALUES('99006', '郭启芳', '女', 15, 'CS') 显示： 消息2627，级别14，状态1，第1 行 违反了PRIMARY KEY 约束'PK_STUDENT'。不能在对象'dbo.STUDENT' 中插入重复键。 语句已终止。 修改：将学号改成与已有学号不会重复的值。 2、求学生的选课人数和学生平均成绩，并把结果存入数据表tot_score。使用SELECT INTO和INSERT INTO两种方法实现。 SELECT INTO语句 SELECT cno,count(*) as cnt_sc,avg(score) as avg_sc INTO tot_score FROM SC GROUP BY cno INSERT INTO语句 create table tot_score (cno char(2),cnt_sc int,avg_sc float) INSERT INTO tot_score SELECT cno,count(*),avg(score) FROM SC GROUP BY cno 3、Management Studio：右击SC表，在菜单中选择“打开表”，在列出的数据中直接修改 用T-SQL语句： UPDATE SC SET score=score+3 WHERE sno='99002' AND cno='C2' 4、 1）查询IS系所有学生的姓名，并按学号升序排列。 select sname from student where sdept=IS order by sno 2）查询学分为2或3，且课程名称以”数”开头的课程编号。 select cno from course where cname like '数%'and (credit='2' or credit='3'); 3）查询所有学生的编号及其所选课程的平均成绩，按平均成绩降序排列。 select sno,avg(score) from sc group by sno order by avg(score) desc 4）查询至少选修了三门课程的学生编号。 select sno from sc group by sno having count(*)>=3 5）查询所有选了“信息系统”的学生编号。 select sno from sc,course where o=o and ame= '信息系统' 6）求出选择了同一课程的学生对。 select distinct x.sno,y.sno from sc x, sc y where o=o and x.sno<y.sno 7）查询选修了学号99002的学生所选的某门课程的学生编号。 select distinct y.sno from sc x, sc y where o=o and x.sno='99002' and y.sno<>'99002' 8）分别用等值连接和谓词IN两种方式查询姓名为张立的学生所选课程的编号和成绩。 等值连接： select o,sc.score from student, sc where sname=张立 and sc.sno=student.sno 谓词IN： select cno,score from sc where sno in ( select sno from student where sname=张立) 9）查询选修“数据库”课程的成绩比姓名为“张立”学生高的所有学生的编号和姓名。 select sno,sname from student where sno in ( select c1.sno from sc as c1,sc as c2 where c1.score>c2.score and o=o and c2.sno=(select sno from student where sname=张立) and o=(select cno from course where cname=数据库) ) 10）查询学分比课程“数学”多的课程的名称。 select ame from course as c1, course as c2 where c1.credit>c2.credit and ame=数学 5、用T-SQL语句完成下面的要求： 用WHILE语句实现计算10000减1、减2、减3，一直减到50的结果，并显示最终结果。 DECLARE x int,y int SELECT x=10000,y=1 WHILE (y<=50) BEGIN SET x=x-y SET y=y+1 END PRINT x 6、用T-SQL语句完成下面的要求，并保存为脚本文件。 使用CASE语句实现： 声明变量x，y为字符型，长度均为6，为x赋初值abc，分情况判断： 当x=a时，y =1 + abc: 当x=b时，y =2 + bc: 当x=c时，y = 3 + abc: 否则，y =no 用函数显示y去掉尾部空格的结果。 解法一： DECLARE x char(6),y char(6) SET x='abc' SELECT y = CASE x WHEN 'a' THEN '1'+'abc: ' WHEN 'b' THEN '2'+'bc: ' WHEN 'c' THEN '3'+'abc: ' ELSE 'no ' END SELECT y=rtrim(y) PRINT y 解法二： DECLARE x char(6),y char(6) SET x='c' SELECT y = CASE WHEN x='a' THEN '1'+'abc: ' WHEN x='b' THEN '2'+'bc: ' WHEN x='c' THEN '3'+'abc: ' ELSE 'no ' END SELECT y=rtrim(y) PRINT y 7、在Management Studio中查看查询分析器创建的默认对象dept_defa。 8、通过T-SQL语句解除 student表的SDEPT字段与默认对象dept_defa的绑定关系，然后删除对象dept_defa。 sp_unbindefault 'student.sdept' drop default dept_defa 9、运用规则对象score_rul实现对sc表score字段的值约束，使score值>=0且<=100。 CREATE RULE score_rul AS val>=0 AND val<=100 GO EXEC sp_bindrule score_rul,SC.score GO 10、在查询分析器中定义一数据类型为八位字符型，且非空，用于描述学生姓名。 sp_addtype typ_ch_name ,'char(8)','not null' 实验三 用T-SQL语句完成下面的查询要求： 1）列出所有课程的名称和选修学生的编号。 右外连接： Select ame, sc.sno from sc right outer join course on o=o 左外连接： Select ame, sc.sno from course left outer join sc on o=o 2）查询学分最高的课程的编号和选修该课程的学生学号。 select cno,sno from sc where cno in ( select cno from course where credit = (select max(credit) from course) ) 或： select o,sno from sc where not exists( 不是>= select * from course where credit> ( select credit from course where o=o) 3）查询没有学生选的课程名称。 select cname from course where cno not in ( select cno from sc ) 4）找出选修课程“数据库”的学生选修的所有课程名称。 select cname from course where cno in ( select cno from sc where sno in ( select sno from course,sc where cname='数据库' o=o) ) 5）查询选修了99002学生选修的所有课程的学生编号。 select distinct sname from student where not exists ( select * from sc as c1 where c1.sno= '99002' and not exists ( and select * from sc as c2 where c2.sno=student.sno and o=o and student.sno<>c1.sno) ) 6）实现集合交运算，查询既选修课程“数学”又选修课程“信息系统”的学生编号。 select x.sno from sc as x,sc as y where (o=(select cno from course where cname='数学') and o=(select cno from course where cname='信息系统') and x.sno=y.sno ) 参见实验指导书的“安全性管理”。 用触发器T1实现表间的参照完整性，当向sc表插入或修改一条记录时，检查记录sno字段的值在student表是否存在，若不存在，则取消插入或修改操作。 CREATE TRIGGER T1 on SC FOR INSERT,UPDATE AS BEGIN IF ( (SELECT sno FROM inserted) NOT IN (SELECT sno FROM student) ROLLBACK END 建立一个在student表上的触发器，当向student表插入一条记录时，自动显示student表中的记录。 CREATE TRIGGER T_Stu on STUDENT FOR INSERT AS SELECT sno,sname FROM inserted 建立一个在student表上的触发器T3，要求插入记录的sage值必须比表中已记录的最小sage值大。 CREATE TRIGGER T3 on STUDENT FOR INSERT AS IF ( (SELECT sage FROM inserted)<= ( SELECT min(sage) FROM student ) ) BEGIN PRINT 'Insert Fail!' ROLLBACK TRANSACTION END 演示触发器作用： INSERT INTO STUDENT(Sno,Sname,Ssex,Sage,Sdept,SMajor,Phone,Native) VALUES ('99019','钱群','男',14,'CS','软件工程',2680916,'山东') 创建递归触发器，删除STUDENT表里的学生时，将该学号的学生也从其选课表SC里头全部删除，并给出删除是否执行的报告(假设表之间未建关系约束)。 CREATE TRIGGER Tt_SC on SC FOR DELETE AS PRINT delete SC is executing! DECLARE num char(5) ,msg char(30) SELECT num=sno FROM deleted GROUP BY sno IF rowcount=0 BEGIN PRINT no rows affected! RETURN END SELECT msg=deleting SC for sno=+num PRINT msg GO 再建立另一个触发器： CREATE TRIGGER Tr_S on STUDENT FOR DELETE AS PRINT delete STUDENT is executing! DECLARE num char(5) ,msg char(30) SELECT num=sno FROM deleted SELECT msg=deleting STUDENT for sno=+num PRINT msg DELETE SC WHERE sno=num GO 演示触发器作用： DELETE FROM STUDENT WHERE sno=99003 在基本表student中创建一个游标，按学号排序取出男生的学号和姓名。 DECLARE Sno_cur CURSOR FOR SELECT sno,sname FROM STUDENT WHERE ssex=男 ORDER BY sno GO OPEN sno_cur DECLARE sno char(5),sname char(8) FETCH NEXT FROM sno_cur INTO sno,sname WHILE fetch_status=0 BEGIN PRINT sno+sname FETCH NEXT FROM sno_cur INTO sno,sname END CLOSE sno_cur DEALLOCATE sno_cur 在基本表course中创建一个游标，先取出表的第三条记录，再取出后两条记录。 DECLARE C_cur CURSOR SCROLL FOR SELECT * FROM COURSE OPEN C_cur FETCH ABSOLUTE 3 FROM C_cur FETCH RELATIVE 2 FROM C_cur CLOSE C_cur DEALLOCATE C_cur 在基本表SC中创建一个游标，若学生成绩低于平均成绩，则成绩增加10%。 DECLARE upSC_cur CURSOR DYNAMIC FOR SELECT score FROM SC FOR UPDATE GO DECLARE avg_score float,score int SELECT avg_score=avg(score) FROM SC OPEN upSC_cur FETCH NEXT FROM upSC_cur INTO score WHILE fetch_status=0 BEGIN IF (score<avg_score) UPDATE SC SET score=score*1.10 WHERE CURRENT OF upSC_cur FETCH NEXT FROM upSC_cur INTO score END CLOSE upSC_cur DEALLOCATE upSC_cur GO 题目有误，答案略。 说明：CREATE PROC过程中不能包含CREATE DEFAULT、CREATE RULE等语句 创建向表student进行插入操作的存储过程，并通过过程调用将下列数据添加到student表中 ('99008','郭凌雨','女',19,'MA','计算数学',2680076,'江苏') 创建存储过程： USE SCMIS GO CREATE PROC student_add (sSno char(5),sSname char(8),sSsex char(2), sSdept varchar(10), sSMajor varchar(10), sSage int,sPhone char(7),sNative varchar(30) AS BEGIN INSERT INTO student VALUES (sSno,sSname,sSsex, sSdept,sSMajor, sSage, sPhone,sNative) END RETURN GO 过程调用： ('99008','郭凌雨','女', 'MA','计算数学', 19,2680076,'江苏') EXEC student_add '99008','郭凌雨','女', 'MA','计算数学', 19, '2680076','江苏' 创建一个存储过程，按照给定的系名，返回该系学生的最大年龄和平均年龄。如果指定系有学生记录，则返回1;否则，返回0。 USE SCMIS GO CREATE PROC age_proc (ins_sdept varchar(10),age_max int OUTPUT, age_avg float OUTPUT) AS IF EXISTS (SELECT * FROM STUDENT WHERE Sdept=ins_sdept) BEGIN SELECT age_max =MAX(sage),age_avg=AVG(sage) FROM STUDENT WHERE Sdept=ins_sdept RETURN(1) END ELSE RETURN(0) GO 过程调用： DECLARE return_status int, agemax int,ageavg float EXEC return_status= age_proc 'IS',agemax OUTPUT,ageavg OUTPUT SELECT return_status SELECT agemax,ageavg 实验四 创建一个返回table的函数，函数的参数是学生姓名ins_sname，返回该学生的信息。 /* 定义一个带输入参数 ins_cname 的视图，存储选修指定课程的学生选课情况 */ USE SCMIS GO CREATE FUNCTION StuByName (ins_sname char(8) RETURNS TABLE AS RETURN (SELECT * FROM Student S WHERE S.Sname=ins_sname) /* 执行 */ SELECT * FROM StuByName ('李勇') 创建一个内嵌表值函数fn02_sname，函数的参数是学生姓名ins_sname，返回的表存储了指定学生的具体信息。通过函数调用查看“王名”的情况，写出调用语句。 CREATE FUNCTION fn02_sName (ins_sname char(8) RETURNS TABLE AS RETURN (SELECT * FROM Student S WHERE S.Sname=ins_sname) /* 执行 */ SELECT * FROM fn02_sName ('王名') 创建一个多语句表值函数fn03_sname，完成题(2)的要求。 CREATE FUNCTION fn03_sname (ins_sname char(8) RETURNS tb_Student TABLE ( Stu_NO char(5) PRIMARY KEY, Stu_NAME char(8), Stu_SEX char(2), Stu_DEPT varchar(10),Stu_MAJOR varchar(10), Stu_AGE int,Stu_NATIVE varchar(30),Stu_PHONE char(7) ) AS BEGIN INSERT tb_student SELECT * FROM Student WHERE Sname=ins_sname RETURN END /* 执行 */ SELECT * FROM fn03_sName ('王名') 创建一个事务，为课程号C2的所有学生成绩加10%。 BEGIN TRAN UPDATE SC SET score=score*1.1 WHERE Cno=C2 COMMIT TRAN 创建一个事务，先将学生表中专业以“电子”开头的学生学号前三位号码990改为992，然后回滚。再将专业以“信息”开头的学生学号前三位号码990改为996。 BEGIN TRAN UPDATE STUDENT SET sno=992+right(sno,2) WHERE smajor LIKE电子% ROLLBACK TRAN BEGIN TRAN UPDATE STUDENT SET sno=996+right(sno,2) WHERE smajor LIKE信息% COMMIT TRAN GO 建立一个在SC表上的Update触发器，其中包含事务Up_Tran。当发现更新学号或者更新分数小于60分时，让事务回滚，不允许更新。 CREATE TRIGGER tr_SC04 ON SC FOR UPDATE AS DECLARE score int BEGIN TRAN IF UPDATE(sno) BEGIN PRINT can not change Sno! ROLLBACK TRAN END IF UPDATE(score) BEGIN SELECT score=score FROM inserted IF score<60 BEGIN PRINT can not be updated! ROLLBACK TRAN END END GO 测试： UPDATE SC SET SCORE=36 WHERE score<=70