东华大学数据库系统原理上机实验答案-

1、数据库系统原理上机实验答案实验三SQL语言的DDL3.CREATE TABLE aa(Bb1 VARCHAR(30,Bb2 INT,Bb3 DECIMAL(6,24.ALTER TABLE aa ADD Bb4 varchar(205. DROP TABLE AA6.CREATE VIEW ReaderView(借阅者AS SELECT DISTINCT 读者编号FROM 借阅7.Drop View ReaderView。8. CREATE INDEX INDEX1 ON 读者(姓名ASC,单位ASCCREATE INDEX INDEX3 ON 借阅(借阅日期DESC9.DROP INDEX

2、读者.INDEX1CREATE 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-1EX

3、EC sp_bindrule lenddate_rule, 借阅.归还日期3.用INSERT语句对“图书”表插入6条记录INSERT INTO 图书(书号,类别,出版社,作者,书名,定价VALUES(0001,计算机类,清华出版社,严蔚敏,数据结构, 20.11 INSERT INTO 图书(书号,类别,出版社,作者,书名,定价VALUES(0002,计算机类,清华出版社,苗雪兰,数据库技术及应用,29.00INSERT INTO 图书(书号,类别,出版社,作者,书名,定价VALUES(0003,计算机类,清华出版社,李建中,软件工程,48.00 INSERT INTO 图书(书号,类别,出版

4、社,作者,书名,定价VALUES(0004,计算机类,电子工业出版社,汤惟,WEB技术,32.00INSERT INTO 图书(书号,类别,出版社,作者,书名,定价VALUES(0005,自动化类,机械工业出版社,胡寿松,自动控制原理,52.00INSERT INTO 图书(书号,类别,出版社,作者,书名,定价VALUES(0006,自动化类,机械工业出版社,郑大钟,线性控制理论,32.00用INSERT语句对“读者”表插入4条记录INSERT INTO 读者(读者编号,姓名,单位,性别,电话VALUES(10001,张三,东华大学,男,67792312INSERT INTO 读者(读者编号,

5、姓名,单位,性别,电话VALUES(10002,李四,东华大学,女,67792312INSERT INTO 读者(读者编号,姓名,单位,性别,电话VALUES(10003,王五,东华大学,男,67792312INSERT INTO 读者(读者编号,姓名,单位,性别,电话VALUES(10004,李明,东华大学,女,67792312用INSERT语句对“借阅”表插入7条记录INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期VALUES(0001,10001,2006-04-19 09:58:03,2006-05-19 8:38:23INSERT INTO 借阅(书号,读者编号,借阅

6、日期,归还日期VALUES(0006,10002,2006-07-01 15:28:12,2006-07-18 9:14:02INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期VALUES(0002,10002,2006-07-01 15:28:30,2006-07-17 19:10:32INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期VALUES(0002,10003,2006-07-03 15:28:30,2006-08-09 15:28:30INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期VALUES(0004,10002,2007-05-0

7、1 11:28:24,2007-08-01 8:09:04INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期VALUES(0005,10002,2007-11-01 10:43:12,2007-12-01 14:09:56INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期VALUES(0003,10004,2007-12-01 16:06:02,2007-12-29 13:17:094.UPDATE 借阅SET 借阅日期=2006-08-03 15:28:30WHERE 读者编号=10003 AND借阅日期 BETWEEN 2006-07-03 AND 2006-0

8、7-04 AND 书号=0002 5.DELETE FROM 借阅WHERE 借阅日期20order by 借阅时间8.SELECT * FROM 读者WHERE NOT EXISTS(SELECT * FROM 图书WHERE 类别=自动化类 and NOT EXISTS(SELECT * FROM 借阅WHERE 读者编号=读者.读者编号and 书号=图书.书号1.SELECT 类别,COUNT(* AS 数量, AVG(定价 AS 平均价格,SUM(定价 AS 总价FROM 图书GROUP BY 类别ORDER BY 类别2.SELECT YEAR(借阅日期 as 年份,书号,COUNT

9、(* 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 A ON 读者.读者编号=A.读者编号GROUP BY 读者.读者编号4.SELECT 读者编号,COUNT(* AS 次数FROM 借阅,图书WHERE 借阅.书号=图书.书号AND 书名=数据库技术及应用GROUP BY 读者编号ORDER BY 次数DESC5.方法1:

10、SELECT MAX(次数 AS 最多,MIN(次数 AS 最少,A VG(次数*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.06.SELECT 类别FROM 图书GROUP BY 类别

11、HA VING MAX(定价=ALL(SELECT 2*AVG(定价 FROM 图书GROUP BY 类别 7.SELECT 书号,书名,定价,出版社FROM 图书WHERE 类别=计算机类ORDER BY 出版社DESCCOMPUTE COUNT(书号 BY 出版社COMPUTE COUNT(书号实验七存储过程、触发器和数据库恢复1.(1CREATE PROCEDURE 借阅情况Readerno V ARCHAR(8ASSELECT 读者.读者编号,姓名,图书.书号,书名,借阅日期, 借出否AS 归还否FROM 读者,借阅,图书WHERE 读者.读者编号=借阅.读者编号AND 图书.书号=借阅.书号AND 读者.读者编号=ReadernoEXEC 借阅情况10004(2CREATE PROCEDURE 借出情况Bookno V ARCHAR(10ASSELECT 书名,姓名,借阅日期,归还日期FROM 读者,借阅,图书WHERE 读者.读者编号=借阅.读者编号AND 图书.书号=借阅.书号AND 图书.书号=BooknoEXEC 借出情况00042.CREATE TRIGGER lendbooki