网上看到第一周的实验居然有个人和我们一模一样啊...不会是我们班的吧哈哈。拿来粘贴一下以便日后复习用。
--网上有好多这套题的答案,但是经过我的验证,有很多都是错的,误人子弟
--这是我自己纠正以后的版本 然后呢如果我写的还有不对的欢迎批评指正!
--(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