实训数据库开发应用——DB上机练习

一．创建满足如下的表（16分）

1．图书表（TShB）包括如下字段：

书号（ShH）：普通编码定长字符型，6位长，主码

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

价格（JG）：定点小数，整数部分3位，小数部分1位

出版日期（ChBRQ）：小日期时间型，大于1980年1月1日

createtableTShB(

ShHchar(6)primarykey,

ShMvarchar(20)notnull,

JGnumeric(4,1),

ChBRQsmalldatetimecheck(ChBRQ>'1980/1/1')

)

2．书店表（ShDB）包括如下字段：

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

店名(DM)：普通编码可变长字符型，20位长，

地址（DZ）：普通编码可变长字符型，20位长，

电话（DH）：普通编码定长字符型，6位长，每一位必须是0-9数字

createtableShDB(

ShDHchar(4)primarykey,

DMvarchar(20),

DZvarchar(20),

DHchar(6)check(DHlike'[0-9][0-9][0-9][0-9][0-9][0-9]')

)

3．销售表（XShB）包括如下字段：

书号（ShH）：普通编码定长字符型，6位长，非空

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

销售日期（XShRQ）：小日期时间型，非空

销售数量（XSSL）：小整型，允许空，大于0

其中：（书号，书店号，销售日期）为主码

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

createtableXShB(

ShHchar(6)notnull,

ShDHchar(4)notnull,

XShRQsmalldatetimenotnull,

XSSLsmallintcheck(XSSL>0),

primarykey(ShH,ShDH,XShRQ),

foreignkey(ShH)referencesTShB(ShH),

foreignkey(ShDH)referencesShDB(ShDH)

)

二．写出实现如下要求的SQL语句。（35分）

1.查询1999年以后出版的价格在20到30之间的图书的书名。

SELECTShMFROMTShBWHEREJGBETWEEN20AND30AND

YEAR(ChBRQ)>1999

2.查询书店名为“海淀图书城”的书店所销售的图书的书名和图书的销售日期，

并对销售日期进行如下处理：如果销售日期早于1999年，则显示’早期’；

如果销售日期在1999到2003年之间，则显示’中期’；如果销售日期晚于2003年，则显示’近期’。

SELECTShM,CASE

WHENYEAR(XShRQ)<1999THEN'早期'

WHENYEAR(XShRQ)BETWEEN1999AND2003THEN'中期'

WHENYEAR(XShRQ)>2003THEN'近期'

END

FROMShDBajoinXShBbona.ShDH=b.ShDH

joinTShBconb.ShH=C.ShH

WHEREDM='海淀图书城'

3.查询2002年6月1日之后每种图书的销售情况，要求列出图书的书号、销售总量，

并且只列出销售总量前三名的情况。

SELECTTOP3ShH,SUM(XSSL)FROMXShB

WHEREXShRQ>'2002/6/1'

GROUPBYShH

ORDERBYSUM(XSSL)DESC

4.查询每个书店的图书销售总额，要求只列出销售总额高于1000的书店的店号和销售总额，

并将查询结果按销售总额从高到底排列。

SELECTShDH,SUM(JG*XSSL)FROMXShBaJOINTShBb

ona.ShH=b.ShH

GROUPBYShDH

HAVINGSUM(JG*XSSL)>1000

OrderbySUM(JG*XSSL)desc

5.查询价格高于总平均价格的图书的书名。

SELECTShMFROMTShB

WHEREJG>(SELECTAVG(JG)FROMTShB)

5．删除1990年之前的全部销售记录。

DELETEFROMXShB

WHEREYEAR(XShRQ)<1990

6.将书号为‘B10’，书名为‘计算机基础’的图书记录插入到图书表中。

INSERTINTOTShB(ShH,ShM)VALUES('b10','计算机基础')

7．将书店名为“西单”的书店所销售的图书的价格增加5%。

UPDATETShBSETJG=JG+JG*0.05

FROMTShBaJOINXShBbona.ShH=b.ShH

JOINShDBconc.ShDH=b.ShDH

WHEREDM='西单'

三．建立统计指定书名的图书的销售次数的存储过程，要求书名为输入参数，统计结果为输出参数。（10分）

CREATEPROCP1

@XCHAR(20),@YINTOUTPUT

AS

SELECT@Y=COUNT(*)FROMXShBaJOINTShBbona.ShH=b.ShH

whereShM=@X

四．建立限制销售数量不能超过100的触发器。（9分）

CREATETRIGGERT1

ONXShBFORINSERT,UPDATE

AS

IFEXISTS(SELECT*FROMINSERTEDWHEREXSSL>100)

ROLLBACK

<!--EndFragment-->