【数据库】 SQL语言练习

这学期开database的课程设计，开课前先复习一波sql语言。

平台：sql server 2014

----------------------------------------------------------------------------------------------

第一周内容

一．建立数据库kcdb(下面涉及所有数据都存放于该数据库下)

二．为描述作者向期刊的投稿情况，建立了三张表：作者表、期刊表和投稿表。期刊表用于描述期刊的基本信息，作者表用于描述作者的基本信息，投稿表用于描述作者向期刊的投稿情况。在查询分析器中写出创建这三张表的语句，并将语句以文件形式保存下来，文件名为“学号.sql”，存放在新建的文件夹下。（可以将后续所有的SQL语句均保存在此文件中，在文件中注明题号）。

注:括号中为表名和列名的拼音表示，建表时用拼音表示表名和列名，不区分大小写。

三张表的结构如下：

作者表（ZZB）

作者号（zzh）  普通字符编码定长字符型，长度为4，主码；

作者名（zzm）  统一字符编码可变长字符型，最多5个汉字，取值不重复。

 

 期刊表（QKB）

期刊号（qkh）  普通字符编码定长字符型，长度为4，主码；

期刊名（qkm）  普通字符编码可变长字符型，最大长度为20，非空；

类别（lb）      普通字符编码定长字符型，长度为2，取值为“是”或“否”

（说明：“是”表示此期刊为核心期刊，“否”表示此期刊为非核心期刊）。

版面费(bmf)     整型。

 

投稿表（TGB）

作者号（zzh）    普通字符编码定长字符型，长度为4，非空；

期刊号（qkh）    普通字符编码定长字符型，长度为4，非空；

投稿日期（tgrq）  小日期时间型，非空，默认值为系统当前时间；

文章名称（wzmc） 普通字符编码定长字符型，长度为50；  

审稿结果（sgjg）  统一字符编码定长字符型，长度为3，取值范围为{通过，未通过},默认值为“未通过”。

其中，主码为（作者号，期刊号，投稿日期），

“作者号”为引用作者表的“作者号”的外码；

“期刊号”为引用期刊表的“期刊号”的外码。

1.运行exam.exe程序解压缩数据文件（可解压到任何地方），解压缩的密码为“dbok”。利用SQL Server的DTS工具，将“期刊表.txt”文件中的数据导入到“期刊表”中，将解压后的 “作者与投稿表.xls”文件中的数据分别导入到 “作者表”和“投稿表”中。

2.利用建立好的三张表，在查询分析器中写出实现如下要求的SQL语句，注明题号。

（1）查询2006年以后（包括2006年）的投稿情况，列出作者名、期刊名、文章名称和投稿日期。

（2）查询姓哪个姓的作者最多，列出这个姓氏和姓这个姓氏的作者人数，包含并列的情况。

（3）查询作者“杨伍华”没有投过稿的核心期刊的名称。

（4）查询“杨伍华”投过稿的期刊的投稿情况，显示期刊号，审稿通过总次数，版面费总金额（说明:只有审稿结果为"通过"时,作者才需向期刊缴纳相应版面费)，要求只列出投稿次数超过5次(不包括5次)且版面费总金额大于7000的情况。

（5）   查询全体作者（包括还没有投过稿的作者）的人均投稿次数，列出总投稿次数、总人数和人均投稿次数。要求平均投稿次数保存到小数点后2位。

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

（7）  删除投稿表中2000年以前（包括2000年），期刊名为"哲学研究"中审稿未通过的的投稿记录。

（8）       统计每位作者的投稿情况，列出作者名，投稿通过率（保留到小数点后2位），评价。其中，当投稿通过率高于0.7，评价为“高”；当投稿通过率在0.4到0.7之间，评价为“中”；当投稿通过率低于0.4，评价为“低”。（可分步骤实现）

（9）      查询投了作者‘杨伍华’所投所有期刊的作者名。

（10）   使用游标实现将“计算机学报”审稿未通过的最后一个作者的审稿结果改为“通过”。

三．建立教师表teacher(tid(教师编号)， tname(教师姓名)，职称（job），salary(工资))，请为teacher表设计一触发器，实现完整性规则“教授的工资不得低于8000元，如果低于8000元，自动改为8000元”，并显示“※※（教师姓名）教师工资改为※※（修改后工资）元”的提示信息。输入若干数据，验证触发器的执行。

 

四．在数据库中建立如下三张表：

借书表：lend（学号，索书号，借书日期，应还日期，是否续借）

欠款表：student(学号，日期，欠款金额)

还书表：return(学号，索书号，还书日期)

并输入若干数据。

请设计一个存储过程实现还书操作，要求还书时删除lend表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，并且根据应还日期和当前日期来判断该书是否超期，如果超期按照超期的天数计算出罚款金额（每天每本书罚款0.1元），并将罚款信息插入到student表中。

--------------------------------------------------------------------------------------------------------------------------------------------

SQL文件内容如下

use kcdbgocreate table ZZB(zzh char(4) primary key,zzm nvarchar(5) unique,)gocreate table QKB(qkh char(4) primary key,qkm varchar(20) not null,lb char(2) check(lb='是' or lb='否'),bmf int,)gocreate table TGB(zzh char(4) not null,qkh char(4) not null,tgrq smalldatetime not null default(getdate()),wzmc char(50),sgjg nchar(6) check(sgjg='通过' or sgjg='未通过') default('未通过'),primary key(zzh,qkh,tgrq),foreign key (zzh) references ZZB(zzh),foreign key (qkh) references QKB(qkh), )--（1）查询2006年以后（包括2006年）的投稿情况，列出作者名、期刊名、文章名称和投稿日期。goselect zzm,qkm,wzmc,tgrqfrom zzb,qkb,tgbwhere ZZB.zzh=TGB.zzh and QKB.qkh=TGB.qkh and tgrq>=2006--（2）查询姓哪个姓的作者最多，列出这个姓氏和姓这个姓氏的作者人数，--包含并列的情况。goselect SUBSTRING(zzm,0,2) 姓氏,COUNT(SUBSTRING(zzm,0,2)) 人数from ZZBgroup by SUBSTRING(zzm,0,2)order by 人数 desc--（3）查询作者“杨伍华”没有投过稿的核心期刊的名称。goselect qkm from QKBwhere qkh not in(select qkh from TGB,ZZB where ZZB.zzh=TGB.zzh and zzm='杨伍华')--（4）查询“杨伍华”投过稿的期刊的投稿情况，显示期刊号，审稿通过总次数，版面费总金额（说明:只有审稿结果为"通过"时,作者才需向期刊缴纳相应版面费)，要求只列出投稿次数超过5次(不包括5次)且版面费总金额大于7000的情况。goselect TGB.qkh,COUNT(TGB.qkh) 通过次数,SUM(bmf) 版面费 from QKB,TGB,ZZBwhere TGB.zzh=ZZB.zzh and QKB.qkh=TGB.qkh and TGB.zzh in(select TGB.zzhfrom TGB,ZZB where ZZB.zzh=TGB.zzh and zzm='杨伍华' and sgjg='通过'group by TGB.zzhhaving COUNT(TGB.zzh)>5)group by TGB.zzh,TGB.qkh,bmf having SUM(bmf)>7000--（5）查询全体作者（包括还没有投过稿的作者）的人均投稿次数，列出总投稿次数、总人数和人均投稿次数。要求平均投稿次数保存到小数点后2位。goselect count(TGB.zzh) 总投稿次数,count(distinct ZZB.zzh) 总人数,cast(count(TGB.zzh)*1.0/count(distinct ZZB.zzh) as numeric(3,2)) 人均投稿次数from ZZB,TGBwhere ZZB.zzh=TGB.zzh--（6）向投稿表中插入一条记录,作者号为“zz01”,期刊号为“qk02”,文章名称为“地铁限界系统的研究”，投稿日期和审稿结果为默认值。goinsert into TGB(zzh,qkh,wzmc)values('zz01','qk02','地铁限界系统的研究')--（7）删除投稿表中2000年以前（包括2000年），期刊名为"哲学研究"中审稿未通过的的投稿记录。delete from TGBwhere tgrq<=2000 and qkh in(select TGB.qkhfrom QKB,TGB where TGB.qkh=QKB.qkh and qkm='哲学研究' and sgjg='未通过')--（8）统计每位作者的投稿情况，列出作者名，投稿通过率（保留到小数点后2位），评价。--      其中，当投稿通过率高于0.7，评价为“高”；当投稿通过率在0.4到0.7之间，评价为“中”；当投稿通过率低于0.4，评价为“低”。（可分步骤实现）gocreate view zs(zzh,tgcs) asselect zzh,count(zzh)from TGBgroup by zzhgocreate view cg(zzh,cgcs) asselect zzh,count(zzh)from TGBwhere sgjg='通过'group by zzhgocreate view tj(zzh,tgl) asselect zs.zzh,cast((cgcs*1.0/tgcs) as numeric(3,2)) 投稿通过率from zs,cgwhere zs.zzh=cg.zzhgoselect zzm 作者名,tgl 投稿通过率,(case when tgl>0.7 then '高' when tgl>=0.4 and tgl<=0.7 then '中' when tgl<0.4 then '低'end)as 评价from tj,ZZBwhere tj.zzh=ZZB.zzh--（9）查询投了作者‘杨伍华’所投所有期刊的作者名。goselect distinct zzmfrom TGB TGBX,ZZBwhere TGBX.zzh=ZZB.zzh and not exists(select *from TGB TGBY,ZZBwhere ZZB.zzm='杨伍华' and TGBY.zzh=ZZB.zzh and not exists(select *from TGB TGBZ,ZZBwhere TGBZ.zzh=ZZB.zzh and TGBZ.qkh=TGBX.qkh and TGBZ.zzh=TGBY.zzh   )  )--（10）使用游标实现将“计算机应用研究”审稿未通过的最后一个作者的审稿结果改为“通过”。godeclare xg scroll cursor forselect * from TGB,QKBwhere TGB.qkh=QKB.qkh and QKB.qkm='计算机应用研究' and sgjg='未通过'open xgfetch absolute -1 from xgupdate TGBset sgjg='通过'where current of xgclose xgdeallocate xg--建立教师表teacher(tid(教师编号)， tname(教师姓名)，职称（job），salary(工资))。--请为teacher表设计一触发器，实现完整性规则“教授的工资不得低于8000元，如果低于8000元，自动改为8000元”。--并显示“※※（教师姓名）教师工资改为※※（修改后工资）元”的提示信息。--输入若干数据，验证触发器的执行。gocreate table teacher(tid char(10) primary key,tname nchar(10) not null,job nchar(10),salary int,)gocreate trigger gzxg on teacher for update,insertasdeclare @xm nchar(10)declare @gz intif update(salary)update teacher set salary=8000 where exists(select tname=@xm,salary=@gzfrom inserted where inserted.tid=teacher.tid and teacher.job='教授' and inserted.salary<8000)print @xm+'教师工资改为'+@gz+'元'--示例goupdate teacherset salary=6000where tid='001'--请设计一个存储过程实现还书操作，要求还书时删除lend表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，--并且根据应还日期和当前日期来判断该书是否超期，如果超期按照超期的天数计算出罚款金额（每天每本书罚款0.1元），--并将罚款信息插入到student表中。create procedure rebook(@ssh int)declare @xh int,@yhrq datetime,@qkje floatselect @xh=学号,@yhrq=应还日期 from lend,student where lend.学号=student.学号 @ssh='索书号'delete from lend where @ssh='索书号'insert into return values(@xh,@ssh, getdate())if(getdate()>@yhrq)begin@qkje=cast((getdate()-@yhrq)*0.1 as float)insert into student values(@xh,getdate(),@qkje)end

------------------------------------------------------------------------------
2016.3.3