问题描述:
本题用到下面三个关系表:
CARD 借书卡: CNO 卡号,NAME 姓名,CLASS 班级
BOOKS 图书: BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
BORROW 借书记录: CNO 借书卡号,BNO 书号,RDATE 还书日期
备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求实现如下15个处理:
问题描述:
本题用到下面三个关系表:
CARD 借书卡: CNO 卡号,NAME 姓名,CLASS 班级
BOOKS 图书: BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
BORROW 借书记录: CNO 借书卡号,BNO 书号,RDATE 还书日期
备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求实现如下15个处理:
1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束
--实现代码:CREATE TABLE BORROW(
CNO int FOREIGN KEY REFERENCES CARD(CNO),
BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
RDATE datetime,
PRIMARY KEY(CNO,BNO))
SELECT CNO,借图书册数=COUNT(*)
FROM BORROW
GROUP BY CNO
HAVING COUNT(*)>5
SELECT * FROM CARD c
WHERE EXISTS(
SELECT * FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME=N'水浒'
AND a.CNO=c.CNO)
SELECT * FROM BORROW
WHERE RDATE<GETDATE()
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N'%网络%'
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE=(
SELECT MAX(PRICE) FROM BOOKS)
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'
AND NOT EXISTS(
SELECT * FROM BORROW aa,BOOKS bb
WHERE aa.BNO=bb.BNO
AND bb.BNAME=N'计算方法习题集'
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC
UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
FROM CARD a,BORROW b
WHERE a.CNO=b.CNO
AND a.CLASS=N'C01'
DELETE A FROM BOOKS a
WHERE NOT EXISTS(
SELECT * FROM BORROW
WHERE BNO=a.BNO)
CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)
CREATE TRIGGER TR_SAVE ON BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO
AND b.BNAME=N'数据库技术及应用'
CREATE VIEW V_VIEW
AS
SELECT a.NAME,b.BNAME
FROM BORROW ab,CARD a,BOOKS b
WHERE ab.CNO=a.CNO
AND ab.BNO=b.BNO
AND a.CLASS=N'力01'
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME IN(N'计算方法',N'组合数学')
GROUP BY a.CNO
HAVING COUNT(*)=2
ORDER BY a.CNO DESC
ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)
ALTER TABLE CARD ALTER COLUMN NAME varchar(10)
ALTER TABLE CARD ADD 系名 varchar(20)