SQL练习题及答案

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 OrderItemwhere 商品号='0001'3. 检索定购商品号为…0001‟或…0002‟的顾客号。

select distinct 顾客号from OrderItemwhere 商品号='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 Ywhere 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,Custommerwhere 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,OrderItemwhere Custommer.顾客号=OrderItem.顾客号and Article.商品号=OrderItem.商品号and 年龄between 30 and 40;14. 创建一个视图GM，字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额=数量*单价）。

指定用内连接方式做。

create view GM asselect Custommer.顾客号,顾客名,商品名,日期,单价*数量as 金额from Custommer,Article,OrderItemwhere Custommer.顾客号=OrderItem.顾客号and Article.商品号=OrderItem.商品号create view GM1 asselect Custommer.顾客号,顾客名,商品名,日期,单价*数量as 金额from (Custommer inner join OrderItem on Custommer.顾客号=OrderItem.顾客号) inner join Article on Article.商品号=OrderItem.商品号15. 检索购买的商品的单价至少有一次高于或等于1000元的顾客号和顾客名。

select Custommer.顾客号,顾客名from Custommer,OrderItem,Articlewhere Custommer.顾客号=OrderItem.顾客号and Article.商品号=OrderItem.商品号and 单价>100016. 检索购买的购买价都高于或等于1000元的顾客号和顾客名。

select Custommer.顾客号,顾客名from Custommer where 顾客号in(select 顾客号from OrderItem where 顾客号not in(select 顾客号from OrderItem,Articlewhere 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.顾客号18. 检索所有的顾客号和顾客名以及它们所购买的商品号。

（包括没买商品的顾客）select Custommer.顾客号,顾客名from Custommer where not exists(select * from Article where not exists(select * from OrderItemwhere OrderItem.顾客号=Custommer.顾客号and OrderItem.商品号=Article.商品号))20. 检索这样的顾客号，他们至少订购了顾客号为“0002”所订购的所有商品（除法）select distinct 顾客号from OrderItem X where not exists(select * from OrderItem Y where 顾客号='0002' and not exists(select * from OrderItem Z where Z.顾客号=X.顾客号and Z.商品号=Y.商品号))21. 向Article表插入一条纪录。

删除无人购买的商品。

（检验一下刚插入的记录是否已被删除）delete from Article where 商品号not in(select 商品号from OrderItem )22. 降低已售出的数量总合超过10件的商品单价为原价的95%。

update Article set 单价=单价*0.95 where 商品号in(select 商品号from OrderItem group by 商品号having sum(数量)>10)23.建立断言：顾客的年龄必须大于18岁。

Create ASSERTION A1 check( not exists (select * from Custommer where 年龄<=18))24. 把修改商品单价的权限授给用户Wang, 用户Wang可以转授该权限。

Grant update (单价) on Article to Wang with grant option25. 把修改商品单价的权限用户Wang收回，转授出去的也级联收回。

revoke update (单价) on Article from Wang cascade。