上机试题3及答案.docVIP

数据库上机练习3 一、建立数据库TGDB； 二、为描述作者向期刊的投稿情况，建立了三张表：作者表、期刊表和投稿表。期刊表用于描述期刊的基本信息，作者表用于描述作者的基本信息，投稿表用于描述作者向期刊的投稿情况。在查询分析器中写出创建这三张表的语句。三张表的结构如下： 作者表（ZZB） 作者号（zzh） 普通字符编码定长字符型，长度为4，主码； 作者名（zzm） 普通字符编码可变长字符型，最多5个汉字，取值不重复。 2. 期刊表（QKB） 期刊号（qkh） 普通字符编码定长字符型，长度为4，主码； 期刊名（qkm） 普通字符编码可变长字符型，最大长度为20，非空； 类别（lb） 普通字符编码定长字符型，长度为2，取值为“是”或“否” （说明：“是”表示此期刊为核心期刊，“否”表示此期刊为非核心期刊）。 版面费(bmf) 整型。 3. 投稿表（TGB） 作者号（zzh） 普通字符编码定长字符型，长度为4，非空； 期刊号（qkh） 普通字符编码定长字符型，长度为4，非空； 投稿日期（tgrq） 小日期时间型，非空，默认值为系统当前时间； 文章名称（wzmc） 普通字符编码定长字符型，长度为50； 审稿结果（sgjg） 普通字符编码定长字符型，长度为6，取值范围为{通过，未通过},默认值为“未通过”。 其中，主码为（作者号，期刊号，投稿日期）， “作者号”为引用作者表的“作者号”的外码； “期刊号”为引用期刊表的“期刊号”的外码。 三、运行C:\exam.exe程序解压缩数据文件（可解压到任何地方），解压缩的密码为“dbok”。利用SQL Server的DTS工具，将“期刊表.txt”文件中的数据导入到“期刊表”中，将解压后的 “作者与投稿表.xls”文件中的数据分别导入到 “作者表”和“投稿表”中。 四、利用第二题的三张表，在查询分析器中写出实现如下要求的SQL语句（注：要求下述语句均使用一条SQL语句完成） 1.查询2006年以后（包括2006年）的投稿情况，列出作者名、期刊名、文章名称和投稿日期。 2.查询姓哪个姓的作者最多，列出这个姓氏和姓这个姓氏的作者人数，包含并列的情况。 3.查询作者“杨伍华”没有投过稿的核心期刊的名称。 4. 查询投稿次数超过2次(包括2次)且版面费总金额大于1000的期刊的投稿情况，显示作者号，期刊号，审稿通过总次数，版面费总金额（说明：只有审稿结果为“通过”时，作者才需向期刊缴纳相应版面费)。 5.查询全体作者（包括还没有投过稿的作者）的总投稿次数、总人数。 6.向投稿表中插入一条记录,作者号为“zz01”,期刊号为“qk02”,文章名称为“地铁限界系统的研究”，投稿日期和审稿结果为默认值。 7.删除投稿表中2000年以前（包括2000年），期刊名为哲学研究中审稿未通过的的投稿记录。 二 CREATE TABLE ZZB ( zzh char(4) PRIMARY KEY, zzm char(10) UNIQUE ) CREATE TABLE QKB ( qkh char(4) PRIMARY KEY, qkm varchar(20) NOT NULL, lb char(2) CHECK(lb in(是,否)), bmf int ) CREATE TABLE TGB ( zzh char(4), qkh char(4), tgrq smalldatetime, wzmc char (50), sgjg char (6) CHECK(lb in(通过,未通过)), PRIMARY KEY(zzh, qkh, tgrq), FOREIGN KEY (zzh) REFERENCES ZZB(zzh), FOREIGN KEY (qkh) REFERENCES QKB(qkh), ) 四、1 SELECT zzm,qkm,wzmc,tgrq FROM ZZB,QKB,TGB WHERE ZZB.zzh=TGB.zzh AND QKB.qkh=TGB.qkh AND YEAR(tgrq)=2006 2 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 SELECT qkm FROM QKB WHERE qkh NOT IN (SELECT qkh FROM TGB,ZZB WHERE TGB.zzh=ZZB.zzh AND zzm=杨伍华)