SQL习题2007

S Q L 习题
姓名：彭亚川班级：1305172 学号：16
2检索定购商品号为‘S001’的顾客号和顾客名。

select Customer.顾客号,顾客名from Customer,OrderItem where Customer.顾客号=OrderItem.顾客号and OrderItem.商品号='S001'
3检索定购商品号为‘S001’或‘S002’的顾客号。

select distinct顾客号from OrderItem where商品号='S001'or商品号='S002';
4检索至少定购商品号为‘S001’和‘S002’的顾客号。

(用交的方法)
select顾客号from OrderItem where商品号='S001'and顾客号in(select顾客号from OrderItem where商品号='S002')
5检索至少定购商品号为‘S001’和‘S002’的顾客号。

(用自表连接方法)
select distinct A.顾客号from OrderItem A,OrderItem B
where A.顾客号=B.顾客号and A.商品号='0001' and B. 商品号='0002';
6 检索没定购商品的顾客号和顾客名。

select distinct Customer.顾客号,顾客名from OrderItem, Customer
where Customer.顾客号 not in(select顾客号from OrderItem);
7 检索一次定购商品号‘S001’商品数量最多的顾客号和顾客名。

select distinct Customer.顾客号,顾客名from Customer,Article where顾客号in
(select顾客号from OrderItem where商品号='S001'and数量=
(select MAX(数量)from OrderItem where商品号='S001'));
8 检索男顾客的人数和平均年龄。

select count(*)人数,avg(年龄)from Customer where性别='男';
9 检索至少订购了一种商品的顾客数。

select count(distinct 顾客号) from OrderItem;
10 检索订购了商品的人次数。

select count( 顾客号) from OrderItem;
11 检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。

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

指定用内连接方式做。

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

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

s elect distinct Customer.顾客号,顾客名from Customer,Article,OrderItem where Customer.顾客号=OrderItem.顾客号and单价>=1000
16检索女顾客购买的商品号，商品名和数量合计。

select distinct Article.商品号,商品名,OrderItem.数量from OrderItem,Article,Customer where Article.商品号=
OrderItem.商品号and OrderItem.顾客号=Customer.顾客号and Customer.性别='女'
17 检索所有的顾客号和顾客名以及它们所购买的商品号。

（包括没买商品的顾客）
select distinct Customer.顾客号,顾客名,商品号from Customer left join OrderItem on Customer.顾客号=OrderItem.顾客号
18 检索这样的顾客号,顾客名,他们定购了所有的商品（除法）select distinct Customer.顾客号,顾客名,商品号
from OrderItem right join Customer on Customer.顾客号=OrderItem.顾客号
19 检索这样的顾客号，他们至少订购了顾客号为“S002”所订购的所有商品（除法）
select distinct顾客号from OrderItem a where not exists(select*from OrderItem b where顾客号='S002'and not exists (select*from OrderItem c where c.顾客号=a.顾客号and c.商品号=b.商品号))
20 向Article表插入一条纪录。

删除无人购买的商品。

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

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

select Customer.顾客号,顾客名from Customer,OrderItem,Article where Customer.顾客号=OrderItem.顾客号and Article.商品=OrderItem.商品号and 单价>1000
23. 建立断言：顾客的年龄必须大于18岁。

Create assertion A1check
(not exists(select*from Customer where年龄<=18))
以下不做
24. 把修改商品单价的权限授给用户Wang, 用户Wang可以转授该权限。

25. 把修改商品单价的权限用户Wang收回，转授出去的也级联收回。