上机练习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。