SQL数据库课程设计第一周

网上看到第一周的实验居然有个人和我们一模一样啊...不会是我们班的吧哈哈。拿来粘贴一下以便日后复习用。

--网上有好多这套题的答案，但是经过我的验证，有很多都是错的，误人子弟
--这是我自己纠正以后的版本 然后呢如果我写的还有不对的欢迎批评指正！
--（1）查询2006年以后（包括2006年）的投稿情况，列出作者名、期刊名、文章名称和投稿日期。
select zzm,qkm,wzmc,tgrq from tgb,qkb,zzb
where tgrq>year(2006)
--（2）查询姓哪个姓的作者最多，列出这个姓氏和姓这个姓氏的作者人数，包含并列的情况。
select top 1 with ties left(zzm,1) xingshi,count(*) num from zzb
WHERE zzh IN (SELECT zzh FROM TGB) 
group by left(zzm,1)
order by num desc
--with ties 保留并列第一
SELECT SUBSTRING(zzm,0,2) 姓氏, COUNT(SUBSTRING(zzm,0,2)) 人数 
FROM ZZB 
WHERE zzh IN (SELECT zzh FROM TGB) 
GROUP BY SUBSTRING(zzm,0,2) 
ORDER BY 人数 DESC
--（3）查询作者“杨伍华”没有投过稿的核心期刊的名称。
use kcdb
--投稿表里面并不包含所有核心期刊所以下面这个是错的
select distinct qkm from tgb,qkb
where lb='是' and tgb.qkh=qkb.qkh and tgb.qkh not in(
    select qkh from tgb
    where zzh=(
        select zzh from zzb
        where zzm='杨伍华'
    )
)
--这个是对的 = =
select qkm from qkb
where lb='是' and qkh not in (select qkh from tgb  
where zzh=(select zzh from zzb
where zzm='杨伍华'))
--（4）查询“杨伍华”投过稿的期刊的投稿情况，显示期刊号，审稿通过总次数，版面费总金额（说明:只有审稿结果为"通过"时,作者才需向期刊缴纳相应版面费)，要求只列出投稿次数超过5次(不包括5次)且版面费总金额大于7000的情况。
select tgb.qkh,sum(case when sgjg='通过' then 1 when sgjg='未通过' then 0 end),sum(case when sgjg='通过' then bmf when sgjg='未通过' then 0 end) money from tgb,qkb
where zzh=(select zzh from zzb
where zzm='杨伍华')
group by tgb.qkh having sum(case when sgjg='通过' then bmf when sgjg='未通过' then 0 end)>7000 and count(*)>5

/*select sum(case when zzh in(select zzh from tgb) then 1 else 0 end) from zzb*/
--（5）查询全体作者（包括还没有投过稿的作者）的人均投稿次数，列出总投稿次数、总人数和人均投稿次数。要求平均投稿次数保存到小数点后2位。
select count(tgb.zzh),count(distinct zzb.zzh),cast(1.0*count(tgb.zzh)/count(distinct zzb.zzh) 
as numeric(3,2)) from tgb,zzb where tgb.zzh=zzb.zzh
alter table tgb
--（6）向投稿表中插入一条记录,作者号为“zz01”,期刊号为“qk02”,文章名称为“地铁限界系统的研究”，投稿日期和审稿结果为默认值。
add constraint DF_SGJG default('未通过')  for sgjg
insert into tgb(zzh,qkh,wzmc)
values('zz01','qk02','地铁限界系统的研究')
--（7）删除投稿表中2000年以前（包括2000年），期刊名为"哲学研究"中审稿未通过的的投稿记录。
select * from tgb
where qkh=(
    select qkh from qkb
    where qkm='哲学研究'
)
delete tgb where qkh=(
    select qkh from qkb
    where qkm='哲学研究'
) and sgjg='通过' and datepart(yyyy,tgrq)<=2007
select * from tgb
where qkh=(
    select qkh from qkb
    where qkm='哲学研究'
)
--这样就真的删了一条记录
--（8）统计每位作者的投稿情况，列出作者名，投稿通过率（保留到小数点后2位），评价。其中，当投稿通过率高于0.7，评价为“高”；当投稿通过率在0.4到0.7之间，评价为“中”；当投稿通过率低于0.4，评价为“低”。（可分步骤实现）
go 
if (OBJECT_ID('tgzs','V') is not null)
drop view tgzs
if(OBJECT_ID('tgtgzs','V') is not null)
drop view tgtgzs
if(object_id('tgtgl','V') is not null)
drop view tgtgl
go
create view tgzs(zzh,tgzsn) as
select zzh,count(zzh) from tgb
group by zzh
go
create view tgtgzs(zzh,tgtgzsn) as
select zzh,count(zzh) from tgb
where sgjg='通过'
group by zzh
go
create view tgtgl(zzh,tgtgln) as
select tgzs.zzh,cast(tgtgzsn*1.0/tgzsn as numeric(3,2)) from tgzs,tgtgzs
where tgzs.zzh=tgtgzs.zzh
go
select zzm 作者名,tgtgln 投稿通过率,(case when tgtgln>0.7 then '优' when tgtgln>=0.4 and tgtgln<=0.7 then '中' when tgtgln<0.4 then '良' end) as 评价 from tgtgl,zzb
where zzb.zzh=tgtgl.zzh
select sgjg from tgb
where zzh=(
    select zzh from zzb
    where zzm='田令洋'
)--验证一下结果
--（9）查询投了作者‘杨伍华’所投所有期刊的作者名。
use kcdb
 select distinct zzm from zzb,tgb tgbx
 where zzb.zzh=tgbx.zzh and not exists(
    select * from tgb tgby,zzb
    where tgby.zzh=zzb.zzh and zzm='杨伍华'
    and not exists(
        select * from tgb tgbz,zzb
        where tgbz.zzh=zzb.zzh and tgbz.zzh=tgbx.zzh and tgbz.qkh=tgby.qkh
    )
 )
 go
 select * from tgb
 where qkh=(
    select qkh from qkb
    where qkm='计算机应用研究'
 )
 go
 declare xg scroll cursor for --key
 select * from tgb,qkb
 where tgb.qkh=qkb.qkh and qkm='计算机应用研究' and sgjg='通过'
 open xg
 fetch absolute -1 from xg --key
 update tgb
 set sgjg = '未通过'
 where current of xg --key
 close xg
 deallocate xg
 go
  select * from tgb
 where qkh=(
    select qkh from qkb
    where qkm='计算机应用研究'
 )
 go
 --三．建立教师表teacher(tid(教师编号)， tname(教师姓名)，职称（job），salary(工资))，请为teacher表设计一触发器，实现完整性规则“教授的工资不得低于8000元，如果低于8000元，自动改为8000元”，并显示“※※（教师姓名）教师工资改为※※（修改后工资）元”的提示信息。输入若干数据，验证触发器的执行。
 create table teacher(
    tid char(10) primary key,
    tname nchar(10) not null,
    job nchar(10),
    salary int
 )
 go
 if(OBJECT_ID('tgtc','TR')is not null)--注意参数类型和对象类型
 drop trigger tgtc
 go
 create trigger tgtc on teacher
 for update
 as
 declare @tid nchar(4)
 declare @tnm nchar(10)
 declare @gz int
 if update(salary)
    select @tid=tid,@tnm=tname,@gz=salary from inserted
    if(@gz<8000) 
    begin
        set @gz=8000
        update teacher
        set salary = 8000
        where teacher.tid=@tid
    end
 print @tnm+'教师工资改为'
 print @gz

 go
 insert into teacher
 values(002,'黄俊','教授',8500)
 update teacher 
 set salary=6000
 where tid=002
 --ctrl+k+c 注释快捷键 
-- 四．在数据库中建立如下三张表：
--借书表：lend（学号，索书号，借书日期，应还日期，是否续借）
--欠款表：student(学号，日期，欠款金额)
--还书表：return(学号，索书号，还书日期)
--并输入若干数据。
--请设计一个存储过程实现还书操作，要求还书时删除lend表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，并且根据应还日期和当前日期来判断该书是否超期，如果超期按照超期的天数计算出罚款金额（每天每本书罚款0.1元），并将罚款信息插入到student表中。
 create table lend(
    学号 char(10) primary key,
    索书号 char(20),
    借书日期 datetime,
    还书日期 datetime,
    是否续借 char(4)
 )
 create table student(
    学号 char(10) primary key,
    欠款金额 float,
    日期 datetime
 )
 create table rreturn(
    学号 char(10) primary key,
    索书号 char(20),
    还书日期 datetime
 )
 go
 insert into lend values(100,234,'2014-08-09',null,null) --日期是有引号的
 insert into lend values(040,334,'2014-09-06','2014-10-8',null)
 --select * from lend where 学号=032
 go
 if (object_id('pro_1', 'P') is not null)
    drop proc pro_1
go
select * from lend
select *from rreturn
select * from student
 go
 create proc pro_1 (@sno char(10),@lendno char(20),@returntime datetime)
 as
 insert into rreturn values(@sno,@lendno,@returntime)
 declare @hsrq datetime select @hsrq=还书日期 from lend where 学号=@sno and 索书号=@lendno
 delete lend where  学号=@sno and 索书号=@lendno
 declare @yqts float  set @yqts=0.0    --变量初始化
 declare @temp datetime     set @temp=@hsrq --变量初始化
 while(@temp<@returntime)
 begin
    set @temp=dateadd(dd,1,@temp)
    print @temp
    set @yqts=@yqts+0.1
 end
 --set @yqts=cast((datepart(dd,@returntime)-datepart(dd,@hsrq)+(datepart(yyyy,@returntime)-datepart(yyyy,@hsrq))*365+(datepart(mm,@returntime)-datepart(mm,@hsrq))*30)
 --*0.1 as numeric(10,2))
 insert into student values(@sno,@yqts,@returntime)
 go
  exec pro_1 40,334,'2015-11-20'
 select * from lend
select *from rreturn
select * from student