上机练习2及答案

上机练习题2

一、建立数据库TwoDB；

二、为描述书店对图书的进书情况，创建了三张表：图书表，书店表和书店进书表。图书表用于表示图书的基本信息，书店表用于描述书店的基本信息，书店进书表描述了书店的图书进书情况。三张表的结构如下：

1．图书表（TShB）（记录出版社所印的每种图书的情况，一个图书编号对应一种图书，而不是一本图书）

图书ISBN（ISBN）：普通编码定长字符型，4位长，主码

书名（ShM）：普通编码可变长字符型，30位长，非空

类别（LB）：普通编码定长字符型，10位长，允许空，取值范围为{计算机、通讯、数学、外语}

印刷数量(ShL)，小整型，允许空，取值：大于等于1000

单价（DJ）：定点小数，小数点后保留2位，小数点前4位，允许空

出版日期（ChBRQ）：小日期时间型，允许空

2．书店（ShDB）

书店编号（ShDBH）：普通编码定长字符型，4位长，主码

书店名称（DM）：普通编码定长字符型，20位长，非空

地址（DZh）：普通编码可变长字符型，30位长，允许空

电话（DH):普通编码定长字符型，12位长,取值形式为：“(3位数字)-6位数字”

3．书店进书表（JShB）

图书ISBN（ISBN）：普通编码定长字符型，4位长，非空

书店编号（ShDBH）：普通编码定长字符型，4位长，非空

进书日期（JShRQ）：小日期时间型，非空,默认值为插入记录时的当前时间

进书数量（JShL）：小整型，允许空，取值：大于等于10

其中：（图书ISBN，书店编号，进书日期）为主码

图书ISBN为引用图书表的外码，书店编号为引用书店表的外码。

假设一个书店对一种图书一天最多进书一次。

三、利用SQL Server的DTS工具，将“图书表及书店表数据.XLS”文件中的数据分别导入到“图书

表”和“书店表”中，将“书店进书表数据.txt”文件中的数据导入到“书店进书表”中。

四、利用第二题的三张表，写出实现如下要求的SQL语句（用一条语句完成）。

1. 查询单价在10到20之间、印刷数量大于5000的“外语”类图书的书名、单价和印刷数量。

select shm,dj,shl from tshb

where dj between 10 and 20 and shl > 5000

and lb = '外语'

2. 查询店名为“王府井书店”的进书情况，列出图书的书名、进书数量及进书日期。

select shm, jshl, jshrq from tshb a join jshb b on a.isbn = b.isbn

join shdb c on b.shdbh = c.shdbh

where dm = '王府井书店'

3．查询地址在“海淀区”的各书店2001年1月1日以后的详细进书情况，列出书店的名称、每次进书的书名、进书日期和进书数量，要求查询结果按每次进每本书的数量从多到少的顺序排列。

select dm, shm, jshrq, jshl

from tshb a join jshb b on a.isbn = b.isbn

join shdb c on b.shdbh = c.shdbh

where dzh like '%海淀区%' and jshrq > '2001/1/1'

order by jshl desc

4. 查询哪些类别的图书在“王府井书店”从没有进过，列出图书的类别。

select distinct lb from tshb

where isbn not in(

select isbn from jshb a join shdb b

on a.shdbh = b.shdbh

where dm ='王府井书店')

5. 新筹建一个书店，编号为“S111”，书店名为“当代书城”，地址和电话还没有确定，请将此

书店信息插入到书店表中。

insert into shdb(shdbh, dm) values('s111','当代书城')

6. 将“计算机”类图书的单价高于“计算机”类图书的平均单价超过50元的图书的单价减10

元。

update tshb set dj = dj - 10

where lb = '计算机'

and dj -50 >(

select avg(dj) from tshb where lb = '计算机')

五、查询“计算机”类别的图书的书名、单价，进书日期、进书数量及进书的总数量（可分步骤实

现）。

SELECT ShM,DJ,ShL

FROM TShB

WHERE DJ>=10 AND DJ<=20 AND LB='外语' AND ShL>=5000

SELECT TShB.ISBN,ShL,JShRQ

FROM TShB,JShB,ShDB

WHERE TShB.ISBN=JShB.ISBN AND JShB.ShDBH=ShDB.ShDBH AND DM='王府井书店'

SELECT ShDB.DM,JShB.*

FROM JShB,ShDB

WHERE JShB.ShDBH=ShDB.ShDBH AND DZh LIKE '%海淀区%'

SELECT DISTINCT LB

FROM TShB

WHERE LB NOT IN(SELECT DISTINCT LB

FROM TShB

WHERE ISBN IN (SELECT ISBN

FROM JShB,ShDB

WHERE JShB.ShDBH=ShDB.ShDBH AND DM='王府井书店')) INSERT INTO ShDB(ShDBH,DM) VALUES ('S111','当代书城')

SELECT ShM,DJ,JShRQ,JShL

FROM TShB,JShB

WHERE TShB.ISBN=JShB.ISBN AND LB='计算机'

SELECT SUM(JShL) 总数量

FROM TShB,JShB

WHERE TShB.ISBN=JShB.ISBN AND LB='计算机'

GROUP BY LB