数据库回顾

CREATE DATABASE kcdb
ON PRIMARY
(NAME = 'kcdb_data',
FILENAME = '\\vmware-host\Shared Folders\桌面\kcdb_data.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 10%)
LOG ON
(NAME = 'kcdb_log',
FILENAME = '\\vmware-host\Shared Folders\桌面\kcdb_log.ldf',
SIZE = 3MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB)  --建立数据库kcdb

create table ZZB
(
zzh char(4) primary key,
zzm nchar(10) 
)

create table QKB
(
qkh char(4) primary key,
qkm nchar(20) not null,
lb char(2) check (lb='是' or lb='否'),
bmf int 
)

create table TGB
(
zzh char(4) not null,
qkh char(4) not null,
tgrq date not null default getdate(),
wzmc char(50),
sgjg char(6) check (sgjg='通过' or sgjg='未通过') default '未通过',
primary key (zzh,qkh,tgrq),
foreign key (zzh) references ZZB,
foreign key (qkh) references QKB
)

--（1）查询2006年以后（包括2006年）的投稿情况，列出作者名、期刊名、文章名称和投稿日期。
select zzm,qkm,wzmc,tgrq
from QKB,TGB,ZZB
where tgrq>='2006-01-01' and TGB.zzh=ZZB.zzh and QKB.qkh = TGB.qkh

--（2）查询姓哪个姓的作者最多，列出这个姓氏和姓这个姓氏的作者人数，包含并列的情况。
select left(zzm,1) 姓,count(*) 人数
from ZZB
group by left(zzm,1)
having count(*)=(select top 1 count(*) 人数
from ZZB
group by left(zzm,1)
order by 人数 desc
)

--（3）查询作者“杨伍华”没有投过稿的核心期刊的名称。
select qkm
from QKB
where qkh not in(
 select QKB.qkh
 from QKB,TGB,ZZB
 where ZZB.zzh=TGB.zzh and QKB.qkh=TGB.qkh and zzm='杨伍华')


--（4）查询“杨伍华”投过稿的期刊的投稿情况，显示期刊号，审稿通过总次数，版面费总金额（说明:只有审稿结果为"通过"时,作者才需向期刊缴纳相应版面费)
--，要求只列出投稿次数超过5次(不包括5次)且版面费总金额大于7000的情况。
select TGB.qkh,count(case when sgjg='通过' then 1 end) 次数,sum(bmf) 总金额
from TGB,ZZB,QKB
where TGB.zzh=ZZb.zzh and zzm='杨伍华' and TGB.qkh=QKB.qkh
group by TGB.qkh
having sum(case when sgjg='通过' then bmf end)>7000 and count(*)>5


--（5）	查询全体作者（包括还没有投过稿的作者）的人均投稿次数，列出总投稿次数、总人数和人均投稿次数。要求平均投稿次数保存到小数点后2位。
select cast(1.0*count(*)/count(distinct ZZB.zzh) as numeric(3,2)) 人均投稿次数,count(*) 总投稿次数,count(distinct ZZB.zzh) 总人数
from TGB,ZZB
where TGB.zzh=ZZB.zzh

--（6）	向投稿表中插入一条记录,作者号为“zz01”,期刊号为“qk02”,文章名称为“地铁限界系统的研究”，投稿日期和审稿结果为默认值。
insert into TGB(zzh,qkh,wzmc)
values('zz01','qk02','地铁限界系统的研究')

--（7）	删除投稿表中2000年以前（包括2000年），期刊名为"哲学研究"中审稿未通过的的投稿记录。
delete TGB
where qkh=(select qkh
from QKB
where qkm='哲学研究'
) and tgrq<='2000-12-31'

--（8）	统计每位作者的投稿情况，列出作者名，投稿通过率（保留到小数点后2位），评价。
--其中，当投稿通过率高于0.7，评价为“高”；当投稿通过率在0.4到0.7之间，评价为“中”；当投稿通过率低于0.4，评价为“低”。（可分步骤实现）
create view tgtgl(zzh,tgtgll) as
select zzh, cast(1.0*count(case when sgjg='通过' then 1 end)/count(*) as numeric(3,2)) tgtgll
from TGB
group by zzh

select zzm,tgtgll 通过率,(case when  tgtgll<0.4 then '低' when  tgtgll>=0.4 and tgtgll<=0.7 then '中' when tgtgll>0.7 then '高' end) 评价
from TGB,ZZB,tgtgl
where TGB.zzh=ZZB.zzh
group by zzm,tgtgll


--（9）	查询投了作者‘杨伍华’所投所有期刊的作者名。
select zzm
from ZZB z1
where not exists(
	select *
	from ZZB z2,TGB t1
	where z2.zzh=t1.zzh and z2.zzm='杨伍华' and not exists (
		select *
		from TGB
		where TGB.zzh=z1.zzh and t1.qkh=TGB.qkh
	)
)

--（10）	使用游标实现将“计算机学报”审稿未通过的最后一个作者的审稿结果改为“通过”。
declare scxf scroll cursor for
select * from TGB,QKB
where TGB.qkh=QKB.qkh and qkm='计算机学报' and sgjg='未通过'
open scxf
fetch absolute -1 from scxf
update TGB
set sgjg='通过'
where current of scxf


--三．建立教师表teacher(tid(教师编号)， tname(教师姓名)，职称（job），salary(工资))，
 create table teacher(
    tid char(10) primary key,
    tname nchar(10) not null,
    job nchar(10),
    salary int
 )
--请为teacher表设计一触发器，实现完整性规则“教授的工资不得低于8000元，如果低于8000元，自动改为8000元”，
--并显示“※※（教师姓名）教师工资改为※※（修改后工资）元”的提示信息。输入若干数据，验证触发器的执行。
create trigger t on teacher
for update
as 
if update(salary)
	begin 
	declare @tid char(4),@tna nchar(10),@job nchar(10),@sal int
	select @tid=teacher.tid,@tna=teacher.tname,@job=teacher.job,@sal=teacher.salary
	from teacher,inserted
	where teacher.tid=inserted.tid
	if(@job='教授' and @sal<8000)
		begin
			update teacher
			set salary=8000
			where teacher.tid=@tid
		print @tna+'教师工资改为'
		print 8000
		end
	end
--四．在数据库中建立如下三张表：
--借书表：lend（学号，索书号，借书日期，应还日期，是否续借）
--欠款表：student(学号，日期，欠款金额)
--还书表：return(学号，索书号，还书日期)
--并输入若干数据。
create table lend(
    学号 char(10) primary key,
    索书号 char(20),
    借书日期 datetime,
    还书日期 datetime,
    是否续借 char(4)
 )
 create table student(
    学号 char(10) primary key,
    欠款金额 float,
    日期 datetime
 )
 create table returnl(
    学号 char(10) primary key,
    索书号 char(20),
    还书日期 datetime
 )

--请设计一个存储过程实现还书操作，要求还书时删除lend表内的借阅记录，并向还书表中插入一条还书记录，
--注意还书日期为当前日期，并且根据应还日期和当前日期来判断该书是否超期，如果超期按照超期的天数计算出罚款金额（每天每本书罚款0.1元），
--并将罚款信息插入到student表中。
create procedure rep @sno char(10),@bno char(20)
as
insert into returnl
values(@sno,@bno,GETDATE())
declare @reday datetime,@brday datetime,@money int,@Realday datetime
select @reday=还书日期,@brday=借书日期,@Realday=GETDATE()
from lend
where @sno=学号 and 索书号=@bno
if(@reday>@Realday)
	begin
	@money=(@Realday-@reday)*0.1
	insert into student
	values(@sno,@money,@realday)
	end