SQL练习题及答案1

SQL练习题：商品销售数据库

商品销售数据库

Article(商品号char(4)，商品名char(16)，单价Numeric(8,2)，库存量int)

Customer(顾客号char (4)，顾客名char (8)，性别char(2)，年龄int)

OrderItem(顾客号char(4)，商品号char(4)，数量int, 日期date)

1. 用SQL建立三个表，须指出该表的实体完整性和参照完整性，对性别和年龄指出用户自定义的约束条件。（性别分成男女，年龄从10到100）。顾客表的数据用插入语句输入数据，其它两表可用任意方式输入数据。

create table OrderItem (顾客号char(4),商品号char(4),日期datetime,数量smallint,

primary key (顾客号,商品号,日期),

foreign key (商品号) references Article (商品号),

foreign key (顾客号) references Custommer(顾客号) );

2. 检索定购商品号为…0001‟的顾客号和顾客名。

select distinct 顾客号,顾客名from OrderItem

where 商品号='0001'

3. 检索定购商品号为…0001‟或…0002‟的顾客号。

select distinct 顾客号from OrderItem

where 商品号='0001' or 商品号='0002';

4. 检索至少定购商品号为…0001‟和…0002‟的顾客号。

select 顾客号from OrderItem where 商品号='0001' and 顾客号in

( select 顾客号from OrderItem where 商品号='0002');

5. 检索至少定购商品号为…0001‟和…0002‟的顾客号。(用自表连接方法)

select X. 顾客号from OrderItem X,OrderItem Y

where X.顾客号=Y.顾客号and X.商品号='0001' and Y. 商品号='0002';

6. 检索没定购商品的顾客号和顾客名。

select 顾客号,顾客名from Custommer where 顾客号not in

(select 顾客号from OrderItem);

7. 检索一次定购商品号…0001‟商品数量最多的顾客号和顾客名。

select 顾客号,顾客名from Custommer where 顾客号in

(select 顾客号from OrderItem where 商品号='0001'and 数量=

(select MAX(数量)from OrderItem where 商品号='0001'));

8. 检索男顾客的人数和平均年龄。

select count(*) 人数,avg(年龄) 平均年龄from Custommer where 性别='男';

9. 检索至少订购了一种商品的顾客数。

select count(distinct 顾客号) from OrderItem;

10. 检索订购了商品的人次数。

select count( 顾客号) from OrderItem;

select count(distinct 顾客号) from OrderItem;

11. 检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。

select sum( 数量),MAX(数量)-MIN(数量) from OrderItem,Custommer

where OrderItem.顾客号=Custommer.顾客号and 顾客名='张三';

12 . 检索至少订购了3单商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并按商品总数量降序排序。

select Custommer.顾客号,顾客名,count( *),Sum(数量) from OrderItem,Custommer where OrderItem.顾客号=Custommer.顾客号 group by Custommer.顾客号,顾客名

having count( *)>3 order by 4 desc;

13. 检索年龄在30至40岁的顾客所购买的商品名及商品单价。

select 商品名,单价from Custommer,Article,OrderItem

where Custommer.顾客号=OrderItem.顾客号and Article.商品号=OrderItem.商品号

and 年龄between 30 and 40;

14. 创建一个视图GM，字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额=数量*单价）。指定用内连接方式做。

create view GM as

select Custommer.顾客号,顾客名,商品名,日期,单价*数量as 金额

from Custommer,Article,OrderItem

where Custommer.顾客号=OrderItem.顾客号and Article.商品号=OrderItem.商品号

create view GM1 as

select Custommer.顾客号,顾客名,商品名,日期,单价*数量as 金额

from (Custommer inner join OrderItem on Custommer.顾客号=OrderItem.顾客号) inner join Article on Article.商品号=OrderItem.商品号

15. 检索购买的商品的单价至少有一次高于或等于1000元的顾客号和顾客名。

select Custommer.顾客号,顾客名

from Custommer,OrderItem,Article

where Custommer.顾客号=OrderItem.顾客号and Article.商品号=OrderItem.商品号and 单价>1000

16. 检索购买的购买价都高于或等于1000元的顾客号和顾客名。

select Custommer.顾客号,顾客名from Custommer where 顾客号in

(select 顾客号from OrderItem where 顾客号not in

(select 顾客号from OrderItem,Article

where OrderItem.商品号=Article.商品号and 单价<=1000))

17. 检索女顾客购买的商品号，商品名和数量合计。

select Article.商品号,商品名,sum(数量) from Custommer,Article,OrderItem where OrderItem.顾客号=Custommer.顾客号and OrderItem.商品号=Article.商品号and 性别='女'

group by Article.商品号,商品名

18. 检索所有的顾客号和顾客名以及它们所购买的商品号。（包括没买商品的顾客） select Custommer.顾客号,顾客名,商品号

from Custommer left join OrderItem on Custommer.顾客号=OrderItem.顾客号