sql常见的借书卡表_表_借书记录表类面试题及答案
问题描述:
本题用到下面三个关系表:
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))
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
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)
<