--网上有好多这套题的答案,但是经过我的验证,有很多都是错的,误人子弟
--这是我自己纠正以后的版本 然后呢如果我写的还有不对的欢迎批评指正!
--(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