SQL面试题1

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

CARD     借书卡:   (CNO 卡号，NAME  姓名，CLASS 班级)

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

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

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

要求实现如下处理：

1. 写出自定义函数，要求输入借书卡号能得到该卡号所借书金额的总和

 

CREATE FUNCTION getSUM

(

@CNO int

)

RETURNS int

AS

BEGIN

    declare @sum int

select @sum=sum(price) from BOOKS where bno in (select bno from BORROW where cno=@CNO)

 

return @sum

END

GO

 

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

select CNO,count(BNO) as 借书数量from BORROW group by CNO having count(BNO)>3

 

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

 

select name,class from card where cno in( select cno from borrow where bno in(

select bno from BOOKS where bname='水浒'))

 

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

 

select CNO,BNO,RDATE from borrow where getdate()>RDATE

 

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

select bno,bname,author from books where bname like '网络%'

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

select bname,author from books where price in(select max(price) from books  )

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

select cno from borrow where bno in (select bno from books where bname='计算方法') and cno not in ( select cno from borrow where bno in(select bno from books where bname='计算方法习题集')) order by cno desc

 

或

 

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 borrow set rdate=dateadd(day,7,rdate) from BORROW where cno in(select cno from card where class='一班')

 

 

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

DELETE A FROM BOOKS a

WHERE NOT EXISTS(

    SELECT * FROM BORROW

    WHERE BNO=a.BNO)

 

 

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

(这个不确定对不 90%应该是对的 自己看了下书写的)

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 name,bname

from  books,card,borrow

where borrow.cno=card.cno and borrow.bno=books.bno and class='一班'

 

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

 

select a.cno from borrow a,borrow b

where a.cno=b.cno and

 a.bno in(select bno from books where bname='计算方法') and

b.bno in(select bno from books where bname='组合数学')

order by a.cno desc

 

或

 

SELECT a.CNO

FROM BORROW a,BOOKS b

WHERE a.BNO=b.BNO

  AND b.BNAME IN('计算方法','组合数学')

GROUP BY a.CNO

HAVING COUNT(*)=2

ORDER BY a.CNO DESC

 

  14、用事务实现如下功能：一个借书卡号借走某书号的书，则该书的库存量减少1，当某书的库存量不够1本的时候，该卡号不能借该书

 

alter PROCEDURE pro_jieshu

 @cno int,

 @bno int,

 @date datetime

AS

BEGIN

 

begin tran

 

declare @quantity int

 

select @quantity=quantity from books where bno=@bno

  insert into borrow values(@cno,@bno,@date)

     update books set quantity=@quantity-1 where bno=@bno

if(@quantity>0)

  begin

   commit tran

  end

else

  begin

   print '已无库存'

   rollback

  end

END

GO

 

  15、用游标实现将书号为‘A001’的书本的价格提高10元

 

declare @bno int

declare @bname nvarchar(50)

declare @author nvarchar(50)

declare @price int

declare @quantity int

 

declare mycursor cursor for select * from books

 

open mycursor

 

fetch next from mycursor into @bno,@bname,@author,@price,@quantity

 

while(@@fetch_status=0)

  begin

      if(@bno=2)

       begin

     update books set price=@price+10 where current of mycursor

       end

     fetch next from mycursor into @bno,@bname,@author,@price,@quantity

  end

 

close mycursor

 

deallocate mycursor