sql 基础 一

card表:  cno,name,class

books表: bno,bname,author,price,quantity

borrow表:cno,bno,rdate

 

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(bno) from borrow  group by cno having count(bno) >5

 

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

select name,class from card where cno=(select  b.cno from books a,borrow b where a.bno=b.bno and a.bname='水浒')

 

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

select * from borrow where rdate<getDate()

 

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

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

 

 

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

select 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='计算方法' 

and not exists(select * from borrow aa,books bb where aa.bno=bb.bno and bb.bname='计算方法习题集'

and a.cno=aa.cno) order by a.cno desc

 

 

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

update bset b.rdate=dateadd(Day,7,b.rdate) from borrow b,card a where b.cno=a.cno and a.class='C01'

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

 

delete from books a where bno not exists(select * from borrow where bno=a.bno)

 

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

create clustered index in_books_name 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='数据库技术及应用'

 

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='力01'

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

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 asc

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

ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)  

15.对CARD表做如下修改：   

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

ALTER TABLE CARD ALTER COLUMN NAME varchar(10)     

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

ALTER TABLE CARD ADD 系名 varchar(20)