实验十-SQL综合大作业

--(1)创建‘借阅’基本表，并同时指定主码和外码。（注：证号为字符型，
-- 宽度为 6 ；书号为字符型，宽度为 10 ；借书日期为日期时间型）
create table Berrow(
rno char(6),  -- 证号
bno char(10), --书号
time date,    --借书日期
primary key(rno,bno),
foreign key(rno) references Reader(rno),
foreign key(bno) references Book(bno),
);

--（2）创建‘图书’、‘读者’基本表，类型自己定但要恰当，同时指定主码；输入一些模拟数据。
create table Book(
bno char(10) primary key,
bname char(20),
author char(20),
publish char(20),
price Decimal(4,1),
);

create table Reader(
rno char(6) primary key,
rname char(20),
sex char(2),
unit char(10),
zhicheng char(10),
number smallint,
);

--（3）给‘读者’表增加约束ck_1,其含义为‘性别只能为男或女
alter table Reader
add constraint ck_1 check(sex in ('男','女'));

--（4）为‘图书’表按书号降序创建唯一索引idx_1
create unique index idx_1 on Book(bno desc);

--（5）查找图书作者以 张 打头的所有图书信息，结果按单价降序排列。
select * from Book
where author like '张%'
order by price desc;

--（6）统计藏书中各个出版单位的册数和价值总和，显示价值总和在100万元以上的出版单位、册数和价值总和
select publish,count(bno),sum(price)
from Book
group by publish
having sum(price) > 100;

--（7）查询单位是‘计算机系’，且借阅册书超过 3 本的读者信息。
select * from Reader
where unit ='计算机系' and number >= 3;

-- （8）删除所有‘高等教育出版社’的相关图书。
delete from Book
where publish = '高等教育出版社';

--（9）在‘借阅’表中插入证号为‘123456’,书号为‘z123456789’的一条借阅记录
insert into Berrow
values('123456','z123456789',null);

--（10）创建单位为‘计算机系’,性别为女的读者视图view1。
create view view1
as select * from Reader
where unit = '计算机系' and sex = '女';

--（11）授予张三对‘借阅’表有 SELECT 的权力，对该表借书日期有更新的权力
grant select,update(time)
on Berrow to 张三;

--（12）创建插入触发器 Tri1，在‘借阅’表中插入一条借阅记录时，将读者表的对应的借阅册数加 1。
create trigger Tri1
on Berrow for insert
as update Reader set number += 1
where Reader.rno in(select rno from inserted);

/*（13）创建一个存储过程 proc1，该存储过程既有输入参数也有输出参数。 
通过参数输入指定出版单位名称，查询该出版单位藏书的册数和价值
总和，并将结果赋给输出参数。定义该存储过程，并调用该存储过程，
观察运行结果。*/
create procedure proc1
@x char(20),@y smallint output,@z char(10) output
as select @y=count(publish),@z=sum(price) from Book
where publish=@x
go
declare @u1 smallint,@u2 char(20)
exec proc1'起点中文网',@u1 output, @u2 output
select @u1 as 总册数,@u2 as 价值总和;


/*（14）定义一个事务，向图书表中插入一条新图书信息，插入后验证,同出
版单位、同作者、同书名的图书是否超过 10 本。如果不超过，插入数
据有效并提交该事务；否则，插入数据无效，回滚事务撤销插入操作。*/
create procedure proc2	
@a char(10),@b char(20),@c char(20),@d char(20),@e char(10) output
as insert into Book(bno,bname,author,publish)
values(@a,@b,@c,@d)
select @e=sum(bno)
from Book
where (bname = @b and author = @c and publish = @d)
return
go
Begin transaction tr1
declare @a char(10),@b char(20),@c char(10),@d char(20),@e char(10)
select @a='z123456789',@b='三国演义',@c='罗贯中',@d='高等教育出版社'
exec proc2 @a,@b,@c,@d,@e output
if(@e >= 10)
	Begin
	rollback transaction tr1
	end
else
	Begin
	commit transaction tr1
	end