不错的sql面试题(2)

  问题描述：
本题用到下面三个关系表：
CARD     借书卡。   CNO 卡号，NAME  姓名，CLASS 班级
BOOKS    图书。     BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW   借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：
  1 ． 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
  2 ． 找出借书超过5本的读者,输出借书卡号及所借图书册数。
  3 ． 查询借阅了"水浒"一书的读者，输出姓名及班级。
  4 ． 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
  5 ． 查询书名包括"网络"关键词的图书，输出书号、书名、作者。
  6 ． 查询现有图书中价格最高的图书，输出书名及作者。
  7 ． 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。
  8 ． 将"C01"班同学所借图书的还期都延长一周。
  9 ． 从BOOKS表中删除当前无人借阅的图书记录。
  10 ．如果经常按书名查询图书信息，请建立合适的索引。
  11 ．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
  12 ．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。
  13 ．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
  14 ．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
  15 ．对CARD表做如下修改：
    a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。
    b. 为该表增加1列NAME（系名），可变长，最大20个字符。


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))

2 . 找出借书超过5本的读者,输出借书卡号及所借图书册数
-- 实现代码：
SELECT CNO,借图书册数 = COUNT ( * )
FROM BORROW
GROUP BY CNO
HAVING COUNT ( * ) > 5

3 . 查询借阅了"水浒"一书的读者，输出姓名及班级
-- 实现代码：
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)

4 . 查询过期未还图书，输出借阅者（卡号）、书号及还书日期
-- 实现代码：
SELECT * FROM BORROW
WHERE RDATE < GETDATE ()

5 . 查询书名包括"网络"关键词的图书，输出书号、书名、作者
-- 实现代码：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N ' %网络% '

6 . 查询现有图书中价格最高的图书，输出书名及作者
-- 实现代码：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE = (
    SELECT MAX (PRICE) FROM BOOKS)

7 . 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出
-- 实现代码：
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

8 . 将"C01"班同学所借图书的还期都延长一周
-- 实现代码：
UPDATE b SET RDATE = DATEADD ( Day , 7 ,b.RDATE)
FROM CARD a,BORROW b
WHERE a.CNO = b.CNO
    AND a.CLASS = N ' C01 '

9 . 从BOOKS表中删除当前无人借阅的图书记录
-- 实现代码：
DELETE A FROM BOOKS a
WHERE NOT EXISTS (
    SELECT * FROM BORROW
    WHERE BNO = a.BNO)

10 . 如果经常按书名查询图书信息，请建立合适的索引
-- 实现代码：
CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

11 . 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）
-- 实现代码：
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 ' 数据库技术及应用 '

12 . 建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）
-- 实现代码：
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 '

13 . 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出
-- 实现代码：
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

14 . 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句
-- 实现代码：
ALTER TABLE BOOKS ADD PRIMARY KEY (BNO)

15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）
-- 实现代码：
ALTER TABLE CARD ALTER COLUMN NAME varchar ( 10 )

15.2 为该表增加1列NAME（系名），可变长，最大20个字符
-- 实现代码：
ALTER TABLE CARD ADD 系名 varchar ( 20 )