SQL经典例子

问题描述：

 本题用到下面三个关系表：

 CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS班级

 BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR作者，PRICE 单价，QUANTITY 库存册数

 BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE还书日期

 备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

 要求实现如下15个处理：

1．    写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。

CREATE TABLE BORROW_DENNIS

(

CNO INT,

BNO VARCHAR(30),

RDATE DATETIME,

PRIMARY KEY(CNO,BNO),

FOREIGN KEY (CNO) REFERENCESCARD_DENNIS(CNO),

FOREIGN KEY (BNO) REFERENCESBOOKS_DENNIS(BNO)

)

 

 2． 找出借书超过5本的读者,输出借书卡号及所借图书册数。

SELECT CNO,COUNT(BNO) AS NUMBER FROMBORROW_DENNIS

GROUP BY (CNO) HAVING COUNT(BNO)>=5

 

 3． 查询借阅了"水浒"一书的读者，输出姓名及班级。

SELECT NAME,CLASS FROM CARD_DENNIS

INNER JOIN

BORROW_DENNIS ON CARD_DENNIS.CNO=BORROW_DENNIS.CNO

INNER JOIN

BOOKS_DENNIS ON BORROW_DENNIS.BNO=BOOKS_DENNIS.BNO

WHERE BOOKS_DENNIS.BNAME='水浒'

 

 4． 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

SELECT CNO,BNO,RDATE FROMBORROW_DENNIS

WHERE GETDATE()>RDATE

 

 5． 查询书名包括"网络"关键词的图书，输出书号、书名、作者。

SELECT BNO,BNAME,AUTHOR FROMBOOKS_DENNIS

WHERE BNAME LIKE '%网络%'

 

 6． 查询现有图书中价格最高的图书，输出书名及作者。

SELECT TOP 1 BNAME,AUTHOR FROMBOOKS_DENNIS

ORDER BY PRICE DESC

 

 7． 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

SELECT CNO FROM BORROW_DENNIS INNER JOIN

BOOKS_DENNIS ON BORROW_DENNIS.BNO=BOOKS_DENNIS.BNO

WHERE BNAME='计算方法' EXCEPT SELECT BNO FROMBOOKS_DENNIS WHERE BNAME='计算方法习题集'

 

 8． 将"C01"班同学所借图书的还期都延长一周。

UPDATE BORROW_DENNIS

SET RDATE=DATEADD(WEEK,1,RDATE)

WHERE CNO=(SELECT CNO FROM CARD_DENNIS WHERE CLASS='C01')

 9． 从BOOKS表中删除当前无人借阅的图书记录。

DELETE FROM BOOKS_DENNIS

WHERE BNO NOT IN(SELECT BNO FROM BORROW_DENNIS)

 

 10．如果经常按书名查询图书信息，请建立合适的索引。

CREATE INDEX BNO_INDEX ON BOOKS_DENNIS(BNAME)

 

 11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。

CREATE TRIGGER save_trigger ON BORROW_DENNIS

FOR INSERT

AS

BEGIN

      IF EXISTS(SELECT * FROM INSERTED INNER JOINBOOKS_DENNIS ON INSERTED.BNO=BOOKS_DENNIS.BNO WHERE BNAME='数据库技术及应用')

      BEGIN

            INSERTINTO BORROW_SAVE_DENNIS

            SELECT* FROM INSERTED

      END

END

 12．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。

CREATE VIEW INFO01

AS

SELECT NAME,BNAME FROM CARD_DENNIS

INNER JOIN

BORROW_DENNIS ON CARD_DENNIS.CNO=BORROW_DENNIS.CNO

INNER JOIN

BOOKS_DENNIS ON BOOKS_DENNIS.BNO=BORROW_DENNIS.BNO

WHERE CLASS='力01'

 

 13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。

SELECT CNO FROM CARD_DENNIS

INNER JOIN

BORROW_DENNIS ON CARD_DENNIS.CNO=BORROW_DENNIS.CNO

INNER JOIN

BOOKS_DENNIS ON BOOKS_DENNIS.BNO=BORROW_DENNIS.BNO

WHERE  BNAME='计算方法' ANDBNAME='组合数学'

ORDER BY CNO

 

 14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。

alter table BOOKS_DENNIS add constraint pk_bno

   primary key (BNO);

 

 15．对CARD表做如下修改：

 a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。

ALTER TABLE CARD_DENNIS ALTER COLUMN NAME VARCHAR(20)

 b. 为该表增加1列NAME（系名），可变长，最大20个字符。

 ALTER TABLECARD_DENNIS ADD 系名VARCHAR(20)