sql测试3

--一、已知数据库中有三个关系如下，请用SQL语言完成下面各题：
--图书（总编号,分类号,书名,作者,出版单位,单价）
--Books (bookid,classnum,bookname,author,publisher,price)
--读者（借书证号,姓名,性别, 单位,职称,地址，借阅册数）
--Readers (num, name,sex,unit,job title,address,number)
--借阅（借书证号,总编号,借书日期）
--Borrowing (num,bookid,date)
--1.	创建借阅基本表，同时指定主码和外码。（注：借书证号为字符型，
--宽度为 3 ；总编号为字符型，宽度为 6 ；借书日期为日期时间型）
create table books
(bookid char(6) primary key,
classnum char(5),
bookname char(20),
author char(20),
Publisher char(20),
Price int);

create table Readers
(num char(3) primary key,
name char(20),
sex char(2),
unit char(20),
jobtitle char(20),
address char(20),
number int);

create table Borrowing
(num char(3),
bookid char(6),
date datetime,
primary key(num,bookid),
foreign key (num) references Readers(num),
foreign key (bookid) references books(bookid));
--2．给读者表增加约束‘性别只能为男或女’。
alter table Readers 
add constraint ck_1
check (sex in ('男','女'));

--3. 为图书表按总编号降序创建唯一索引。
create unique index idx_1 on books(bookid desc);
--4．查找‘清华大学出版社’的所有图书及单价，结果按单价降序排列。
select bookname,Price
from books
where Publisher = '清华大学出版社'
order by price desc;
--5．查找单价在17元以上已借出的图书。
select books.*
from books,Borrowing
where books.bookid = Borrowing.bookid
and books.Price > 17
and Borrowing.date < getdate();
--6．查找藏书中比‘清华大学出版社’的所有图书单价都高的图书总编号。
select bookid
from books
where Price > all(select price from books where Publisher='清华大学出版社');
--7．统计藏书中各个出版单位的册数和价值总和，显示册数在５本以上的
--出版单位、册数和价值总和。
select Publisher 出版单位,count(*) 册数,sum(price) 价值总和
from books
group by Publisher
having count(*) > 5;
--8  查找借阅了借书证号为‘006’的读者所借所有图书的读者借书证号、姓名和地址。
select Readers.num,Readers.name,Readers.address
from Borrowing,Readers
where Borrowing.num=Readers.num
and bookid = (select bookid from Borrowing where num='006');
--9．在借阅基本表中插入一条借书证号为‘008’,总编号为‘010206’, 
--借书日期为2000年12月16日的记录。
--INSERT 语句与 FOREIGN KEY 约束"FK__Borrowing__num__286302EC"冲突。
--该冲突发生于数据库"test2"，表"dbo.Readers", column 'num'。
insert into Borrowing values('008','010206','2000-12-16');

insert into books(bookid) values('010206');
insert into Readers(num) values('008');
select * from books;
select * from Readers;
insert into Borrowing values('008','010206','2000-12-16');
select * from Borrowing;
--10. 将‘高等教育出版社’的图书单价增加5元。
update books
set price=price+5
where Publisher = '高等教育出版社';
--11．删除所有作者为‘张三’的图书借阅记录。
delete from Borrowing 
where bookid=(select bookid 
				from books 
				where author='张三');
--12. 创建‘计算机系’借阅‘清华大学出版社’图书的读者视图。
go
create view v1
as
select Readers.*
from Readers,Borrowing,books
where Readers.num = Borrowing.num
and books.bookid = Borrowing.bookid
and unit = '计算机系'
and Publisher = '清华大学出版社';
go
--13. 授予张军对借阅表有SELECT的权力，对其中借书日期有更新的权力。
sp_addlogin 'zhangjun';
sp_adduser 'zhangjun';

grant select,update(date)
on borrowing
to zhangjun;
--14. 创建删除触发器，在借阅表中删除借阅记录时，将读者表的对应的借阅册数减1。
go
create trigger Tri1 on borrowing
for delete
as begin
if exists(select 1 from deleted)
	update Readers set number-=1
	where num=(select num from deleted);
end
go
--15. 创建一个带参数的存储过程，完成对指定借书证号的读者在2018年1月1日以后的
--借阅册数的查询。
go
create procedure proc1
@num char(3),@count int output
as 
select @count=count(*) 
from Borrowing
where num = @num
and date > 2018-01-01
go

declare @num char(3),@c int;
exec proc1  '008',@c output;
select '008',@c;