数据库与软件工程上机实验答案.doc

数据库与软件工程上机实验答案实验三 SQL语言的DDL3CREATE TABLE aa( Bb1 VARCHAR(30), Bb2 INT, Bb3 DECIMAL(6,2)4 ALTER TABLE aa ADD Bb4 varchar(20)5. DROP TABLE AA6 CREATE VIEW ReaderView(借阅者)AS SELECT DISTINCT 读者编号 FROM 借阅7 Drop View ReaderView。8. CREATE INDEX INDEX1 ON 读者(姓名 ASC，单位 ASC)CREATE INDEX INDEX3 ON 借阅(借阅日期 DESC)9 DROP INDEX 读者.INDEX1 CREATE INDEX INDEX1 ON 读者(姓名 ASC)实验四 SQL语言的DML初步1 CREATE DEFAULT ZEROVALUE AS 0sp_bindefault ZEROVALUE, 图书.借出否CREATE DEFAULT CURTIME AS getdate()sp_bindefault CURTIME, 借阅.借阅日期2 CREATE RULE gender_ruleAS value in (男,女)EXEC sp_bindrule gender_rule, 读者.性别 CREATE RULE lenddate_ruleAS value 2004-1-1EXEC sp_bindrule lenddate_rule, 借阅.归还日期3 用INSERT语句对“图书”表插入6条记录INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)VALUES(0001,计算机类,清华出版社,严蔚敏,数据结构,20.11)INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)VALUES(0002,计算机类,清华出版社,苗雪兰,数据库技术及应用,29.00)INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)VALUES(0003,计算机类,清华出版社,李建中,软件工程,48.00)INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)VALUES(0004,计算机类,电子工业出版社,汤惟,WEB技术,32.00)INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)VALUES(0005,自动化类,机械工业出版社,胡寿松,自动控制原理,52.00)INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)VALUES(0006,自动化类,机械工业出版社,郑大钟,线性控制理论,32.00)用INSERT语句对“读者”表插入4条记录INSERT INTO 读者(读者编号,姓名,单位,性别,电话)VALUES(10001,张三,东华大学,男,67792312)INSERT INTO 读者(读者编号,姓名,单位,性别,电话)VALUES(10002,李四,东华大学,女,67792312)INSERT INTO 读者(读者编号,姓名,单位,性别,电话)VALUES(10003,王五,东华大学,男,67792312)INSERT INTO 读者(读者编号,姓名,单位,性别,电话)VALUES(10004,李明,东华大学,女,67792312)用INSERT语句对“借阅”表插入7条记录INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)VALUES(0001,10001,2006-04-19 09:58:03,2006-05-19 8:38:23)INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)VALUES(0006,10002,2006-07-01 15:28:12,2006-07-18 9:14:02)INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)VALUES(0002,10002,2006-07-01 15:28:30,2006-07-17 19:10:32)INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)VALUES(0002,10003,2006-07-03 15:28:30,2006-08-09 15:28:30)INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)VALUES(0004,10002,2007-05-01 11:28:24,2007-08-01 8:09:04)INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)VALUES(0005,10002,2007-11-01 10:43:12,2007-12-01 14:09:56)INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)VALUES(0003,10004,2007-12-01 16:06:02,2007-12-29 13:17:09)4 UPDATE 借阅SET 借阅日期=2006-08-03 15:28:30WHERE 读者编号=10003 AND 借阅日期 BETWEEN 2006-07-03 AND 2006-07-04 AND 书号=00025 DELETE FROM 借阅WHERE 借阅日期20order by 借阅时间8 SELECT * FROM 读者WHERE NOT EXISTS( SELECT * FROM 图书 WHERE 类别=自动化类 and NOT EXISTS( SELECT * FROM 借阅 WHERE 读者编号=读者.读者编号 and 书号=图书.书号 )实验六 SQL语言的数据查询之二1 SELECT 类别,COUNT(*) AS 数量, AVG(定价) AS 平均价格,SUM(定价) AS 总价 FROM 图书GROUP BY 类别ORDER BY 类别2 SELECT YEAR(借阅日期) as 年份,书号,COUNT(*) AS 借阅次数 FROM 借阅GROUP BY YEAR(借阅日期),书号ORDER BY 借阅次数 DESC3 SELECT 读者.读者编号,COUNT(书号) AS 借阅次数 FROM 读者LEFT JOIN (SELECT * FROM 借阅 WHERE 借阅日期 BETWEEN 2006-7-1 AND 2007-10-1) AON 读者.读者编号=A.读者编号GROUP BY 读者.读者编号4 SELECT 读者编号,COUNT(*) AS 次数 FROM 借阅,图书WHERE 借阅.书号=图书.书号 AND 书名=数据库技术及应用GROUP BY 读者编号ORDER BY 次数 DESC5 方法1：SELECT MAX(次数) AS 最多,MIN(次数) AS 最少,AVG(次数*1.0) AS 平均FROM( SELECT 图书.书号,COUNT(*) AS 次数 FROM 图书,借阅 WHERE 图书.书号=借阅.书号 AND 类别=计算机类 GROUP BY 图书.书号 ) A方法2：SELECT 图书.书号,COUNT(*)*1.0 AS 次数 FROM 图书,借阅WHERE 图书.书号=借阅.书号 AND 类别=计算机类GROUP BY 图书.书号COMPUTE MAX(COUNT(*)*1.0),MIN(COUNT(*)*1.0),AVG(COUNT(*)*1.0)6 SELECT 类别 FROM 图书GROUP BY 类别HAVING MAX(定价)=ALL(SELECT 2*AVG(定价) FROM 图书 GROUP BY 类别)7 SELECT 书号,书名,定价,出版社 FROM 图书WHERE 类别=计算机类ORDER BY 出版社 DESCCOMPUTE COUNT(书号) BY 出版社COMPUTE COUNT(书号)实验七 存储过程、触发器和数据库恢复1（1）CREATE PROCEDURE 借阅情况Readerno VARCHAR(8)AS SELECT 读者.读者编号,姓名,图书.书号,书名,借阅日期, 借出否 AS 归还否 FROM 读者,借阅,图书 WHERE 读者.读者编号=借阅.读者编号 AND 图书.书号=借阅.书号 AND 读者.读者编号=ReadernoEXEC 借阅情况 10004（2）CREATE PROCEDURE 借出情况Bookno VARCHAR(10)AS SELECT 书名,姓名,借阅日期,归还日期 FROM 读者,借阅,图书 WHERE 读者.读者编号=借阅.读者编号 AND 图书.书号=借阅.书号 AND 图书.书号=BooknoEXEC 借出情况 00042CREATE TRIGGER lendbookinsert ON 借阅FO