SQL考试练习题及全部答案2

四、应用题（20分）

某网上书店后台数据库的部分关系模式如下：

会员（会员编号，用户名，密码，姓名，地址，邮编，电话，消费额，积分）

图书（图书编号，类型名称，图书名称，作者，出版社，出版日期，ISBN，价格）

订单（订单编号，会员编号，销售额，订购日期，出货日期）

订单明细（订单明细编号，订单编号，图书编号，数量）

1. 创建订单表，订单编号唯一识别一个订单，会员编号为外码。要求销售额大于0。
2. 在会员表的积分列上建立降序索引Index_point。
3. 查询名称中包含“数据库”的图书的图书名称，作者，出版社和出版日期。
4. 查询每个会员的订购图书的情况，显示用户名、图书名、作者、订购日期。
5. 查询提供销售（图书表中有）但没有销售过（没在订单明细表中出现）的图书名称和出版社。
6. 查询已销售的每种图书的销售数量，显示图书编号、销售数量。
7. 查询订购图书数量最多的用户名及其订购的数量。

8、求书店总的销售额。

9、删除没有销售过的图书。

10、将每位会员的积分增加10分。

创建订单表，订单编号唯一识别一个订单，会员编号为外码。要求订购日期不能大于出货日期。

create  table  order (

订单编号  char(9)  primary key,

会员编号  char(10),

销售额   double  check(销售额>0),

订购日期  datetime  ,

出货日期  datetime,

foreign key(会员编号)  references 会员 (会员编号)

)

在会员表的积分列上建立降序索引Index_point。

create index Index_point on 会员(积分 desc)

查询名称中包含“数据库”的图书的图书名称，作者，出版社和出版日期。

select 图书名称，作者，出版社，出版日期

from  图书

where 图书名称 like ‘%数据库%’

查询每个会员的订购图书的情况，显示用户名、图书名、作者、订购日期。

select 用户名、图书名、作者、订购日期

from会员 A, 图书 B, 订单 C, 订单明细 D

where  A. 会员编号 = C. 会员编号 and

          B. 图书编号 = D. 图书编号 and

          C. 订单编号 = D. 订单编号

查询提供销售（图书表中有）但没有销售过（没在订单明细表中出现）的图书名称和出版社。

select 图书名称、出版社

from 图书

where图书编号 not in (

       select  distinct 图书编号

       from订单明细

)

查询已销售的每种图书的销售数量，显示图书编号、销售数量。

select 图书编号,sum(数量)

from 订单明细

group by图书编号

查询订购图书数量最多的用户名及其订购的数量。

select 用户名, sum(数量)

from会员 A, 订单 B, 订单明细 C

where A. 会员编号 = B. 会员编号 and

        B. 订单编号 = C. 订单编号 and

group by会员编号, 用户名

having sum(数量) >=all(

     select  sum(数量)

from会员 A, 订单 B, 订单明细 C

where A. 会员编号 = B. 会员编号 and

        B. 订单编号 = C. 订单编号 and

group by会员编号, 用户名

)

求书店总的销售额。

select sum(销售额)

from 订单

删除没有销售过的图书。

delete

from 图书

where 图书编号 not in (

       select  distinct 图书编号

       from订单明细

)

将每位会员的积分增加10分

upate 会员

set 积分= 积分+10

五、应用题（每题2分，共20分）

某书店后台数据库的部分关系模式如下：

图书类别（类别代号，类别名）

图书（书号，书名，ISBN，作者，单价，类别代号）

订单（订单号，顾客编号，订购日期，出货日期）

订单明细（订单号，书号，数量，总价）

按要求实现下列操作：

1.显示ISBN为“9787302163305”、“7560922171”或“9787810097987”的图书的书号、书名和ISBN。

2.显示单价高于40元的图书的书号、书名和单价。

3.在图书表中显示所有没有类别代号的图书的书号和书名。

4.统计有多少图书的价格高于30元。

5.统计每本图书的销售数量总和。

6.显示所有图书的书号、书名和单价以及图书对应的类别代号和类别名。

7.显示书名中包含字符串“ASP”的图书的销售订单号和销售总价。

8.创建一个名为ViewBookSale的视图，该视图包含所有图书的销售信息，显示图书的编号、书名以及销量总册数和销售总金额。

9.向图书表中插入一条图书记录：书号为“9”，书名为“SQL Server 2005实现与维护”, ISBN为“9787302163350”, 作者为“Solid”, 单价为79.00, 类别代号为“CO01”。

10.将书号为1的图书的单价打9折。

答案：

1.显示ISBN为“9787302163305”、“7560922171”或“9787810097987”的图书的书号、书名和ISBN。

SELECT 书号, 书名, ISBN

FROM 图书

WHERE ISBN IN('9787302163305', '7560922171', '9787810097987')

2.显示单价高于40元的图书的书号、书名和单价。

SELECT 书号, 书名, 单价

FROM 图书

WHERE 单价>40

3.在图书表中显示所有没有类别代号的图书的书号和书名。

SELECT 书号, 书名

FROM 图书

WHERE 类别代号 IS NULL

4.统计有多少图书的价格高于30元。

SELECT COUNT(*) AS 图书册数

FROM 图书

WHERE 单价 >30

5.统计每本图书的销售数量总和。

SELECT SUM(数量) AS 销售总册数

FROM 订单明细

GROUP BY 书号

6.显示所有图书的书号、书名和单价以及图书对应的类别代号和类别名。

SELECT b.书号, b.书名, b.单价, c.类别代号, c. 类别名

FROM 图书 b  LEFT OUTER JOIN 图书类别 c

ON b.类别代号=c.类别代号

7.显示书名中包含字符串“ASP”的图书的销售订单号和销售总价。

SELECT 订单号, 总价

FROM 订单明细

WHERE 书号 IN ( SELECT 书号

                 FROM 图书

                 WHERE 书名 LIKE '%ASP%')

8.创建一个名为ViewBookSale的视图，该视图包含所有图书的销售信息，显示图书的编号、书名以及销量总册数和销售总金额。

CREATE VIEW ViewBookSale

AS

SELECT b.书号, b.书名, SUM(数量) AS 销量总册数, SUM(总价) AS 销售总金额

FROM 图书 b LEFT JOIN订单明细i ON b.书号=i.书号

GROUP BY b.书号, b.书名

9.向图书表中插入一条图书记录：书号为“9”，书名为“SQL Server 2005实现与维护”, ISBN为“9787302163350”, 作者为“Solid”, 单价为79.00, 类别代号为“CO01”。

INSERT INTO 图书

VALUES(‘9’,'SQL Server 2005实现与维护','9787302163350','Solid',79.00,'CO01')

10.将书号为1的图书的单价打9折。

UPDATE 图书

SET 单价=单价*0.9

WHERE 书号=‘1’