上机试题3及答案.docx

数据库上机练习3

一、 建立数据库TGDB ；

二、 为描述作者向期刊的投稿情况，建立了三张表：作者表、期刊表和投稿表。期刊表 用于描述期刊的基木信息，作者表用于描述作者的基木信息，投稿表用于描述作者向期刊的 投稿情况。在查询分析器中写出创建这三张表的语句。三张表的结构如下：

作者表（ZZB ）

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

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

2. 期刊表（QKB ）

期刊号（qkh ） 普通字符编码定长字符型，长度为4,主码； 期刊名（qkm ） 普通字符编码可变长字符型，最大长度为20,非空； 类别（lb ）

（说明：“是”表示此期

刊为核心期刊，“否”表示此期刊为非核心期刊）。 版面费（bmf ） 整型。

普通字符编码定长字符型，长度为4,非空； 普通字符编码定长字

符型，长度为4,非空； 小日期时间型,非空,默认值为系统当前时间; 普通字符编码定长字符型，长度为50； 普通字符编码定长字符型，长度为6,取值范围为｛通过，未通过｝,

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

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

码; “期刊号”为引用期刊表的''期刊号”的

外码。

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

四、利用第二题的三张表，在杳询分析器屮写出实现如下要求的SQL 语句（注：要求 下述语句均使用一条SQL 语句完成）

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

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

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

4. 杏询投稿次数超过2次（包括2次）且版面费总金额大于100（）的期刊的投稿情况，显 示作者号，期刊号，审稿通过总次数，版面费总金额（说明：只有审稿结果为“通普通字符编码定长字符型，长度为2,取值为“是”或“否”

3.投稿表（TGB ）

作者号（zzh ） 期

刊号（qkh ） 投稿日期（tgrq ） 文章名称

（wzmc ） 审稿结果

（sgjg ） 默认值为“未

通过”。

过”时, 作者才需向期刊缴纳相应版面费）。

5.杳询全体作者（包括还没有投过稿的作者）的总投稿次数、总人数。

6.向投稿表屮插入一条记录，作者号为“zzOl”，期刊号为“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('是J否'))，

bmf int

)

CREATE TABLE TGB (

zzh char(4),

qkh char(4),

tgrq smalldatetime,

wzmc char (50),

sgjg char (6) CHECK(lb ir)C通过；味通过'))，

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

SELECTqkm

FROM QKB

WHERE qkh NOT IN (SELECT qkh

FROM TGB,ZZB

WHERE TGB.zzh=ZZB.zzh AND zzm-杨伍华')

AND LB='是'

4

SELECTTGB.ZZH,TGB.QKH,COUNT(TGB.qkh)通过次数,SUM(bmf)版面费

FROM TGB,ZZB,QKB

WHERE TGB.zzh=ZZB.zzh AND TGB.qkh二QKB.qkh AND TGB.zzh IN (SELECT zzh FROM TGB GROUP BY zzh HAVING COUNT(zzh)>=3)

GROUP BY TGB.zzh,TGB.qkh,bmf HAVING COUNT(TGB.qkh)>=2 AND SUM(bmf)> 1000

5

SELECT COUNT(wzmc)总投稿次数,COUNT(DISTINCT ZZB.zzh)总人数

FROM ZZB LEFT JOIN TGB ON (TGB.zzh=ZZB.zzh)