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)。

顾客表的数据用插入语句输入数据，其它两表可用任意方式输入数据。

createtableOrderltem(顾客号char(4),商品号char(4),日期datetime,数量smallint,primarykey(顾客号，商品号，日期),
foreig nkey(商品号)refere ncesArticle(商品号),
foreig nkey(顾客号)refere ncesCustommer(顾客号));
2. 检索定购商品号为,0001?的顾客号和顾客名。

selectdistinct 顾客号,顾客名fromOrderltem
where 商品号='000 1 '
3. 检索定购商品号为,0001?或,0002?的顾客号。

selectdistinct 顾客号fromOrderltem
where 商品号='0001'or 商品号二'0002：
4. 检索至少定购商品号为,0001?和,0002?的顾客号。

select 顾客号fromOrderltemwhere 商品号='0001'and 顾客号in
(select 顾客号fromOrderltemwhere 商品号='0002');
5. 检索至少定购商品号为,0001?和,0002?的顾客号。

(用自表连接方法)
selectX顾客号fromOrderltemX,OrderltemY
whereX.顾客号二Y顾客号andX商品号二'0001'andY商品号二'0002：
6. 检索没定购商品的顾客号和顾客名。

select 顾客号,顾客名fromCustommerwhere 顾客号notin
（select 顾客号fromOrderltem）;
7. 检索一次定购商品号,0001?商品数量最多的顾客号和顾客名。

select 顾客号,顾客名fromCustommerwhere 顾客号in
（select 顾客号fromOrderItemwhere 商品号='0001'and 数量二
（selectMAX（数量fromOrderItemwhere 商品号='0001'））;
8. 检索男顾客的人数和平均年龄。

selectcount（*）人数,avg（年龄）平均年龄fromCustommerwhere 性别='男'；
9 .检索至少订购了一种商品的顾客数。

selectcount（distinct 顾客号）fromOrderItem;
10. 检索订购了商品的人次数。

selectco unt（顾客号）fromOrderItem;
selectcount（distinct 顾客号）fromOrderItem;
11. 检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。

select sum（数量）,MAX（数量）-MIN（数量）fromOrderltem,Custommer whereOrderItem.顾客号二Custommer顾客号and顾客名二'张三'；
12 .检索至少订购了3单商品的顾客号和顾客名及他们定购的商品次数和商品
总数量，并按商品总数量降序排序。

selectCustommer.顾客号，顾客名,count（*）,Sum（数
量）fromOrderltem,CustommerwhereOrderltem.顾客号二Custommer顾客号groupbyCustommer.顾客号，顾客名havingcount（*）＞3orderby4desc;
13. 检索年龄在30 至40 岁的顾客所购买的商品名及商品单价。

select 商品名,单价fromCustommer,Article,Orderltem
whereCustommer.顾客号=Orderltem.顾客号andArticle.商品号=Orderltem.商品号and 年龄between30and40;
14. 创建一个视图GM,字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额=数量* 单价）。

指定用内连接方式做。

createviewGMas
selectCustommer顾客号，顾客名,商品名，日期，单价*数量as金额
fromCustommer,Article,Orderltem
whereCustommer.顾客号=Orderltem.顾客号andArticle.商品号=Orderltem.商品号createviewGM1as
selectCustommer顾客号，顾客名,商品名，日期，单价*数量as金额
from（CustommerinnerjoinOrderltemonCustommer.顾客号=OrderItem.顾客号）inn erjo in Article on Article.商品号=OrderItem .商品号
15. 检索购买的商品的单价至少有一次高于或等于1000 元的顾客号和顾客名。

selectCustommer.顾客号，顾客名
fromCustommer,Orderltem,Article
where Custommer.顾客号=Orderltem.顾客号and Article.商品号=OrderItem.商品号and 单价＞1000
16. 检索购买的购买价都高于或等于1000 元的顾客号和顾客名。

selectCustommer.顾客号，顾客名fromCustommerwhere 顾客号in
（select 顾客号fromOrderItemwhere 顾客号notin
（select 顾客号fromOrderItem,Article
whereOrderltem.商品号二Article.商品号and 单价＜=1000））
17. 检索女顾客购买的商品号，商品名和数量合计。

selectArticle.商品号，商品名,sum（数量）fromCustommer,Article,OrderItem whereOrderltem.顾客号二Custommer顾客号andOrderItem.商品号二Article.
商
品号and性别二'女’
groupbyArticle.商品号，商品名
18. 检索所有的顾客号和顾客名以及它们所购买的商品号。

（包括没买商品
的顾客）selectCustommer顾客号，顾客名,商品号
fromCustommerleftjoinOrderltemonCustommer.顾客号=OrderItem.顾客号
18.检索所有的顾客号和顾客名以及它们所购买的商品号。

（包括没买商品
的顾客）selectCustommer顾客号，顾客名fromCustommerwherenotexists （select*fromArticlewherenotexists
（select*fromOrderItem
whereOrderItem.顾客号二Custommer顾客号andOrderItem.商品号二Article.商品号））
20. 检索这样的顾客号，他们至
少订购了顾客号为“0002所”订购的所有商品
（除法）selectdistinet 顾客号fromOrderItemXwherenotexists
（select*fromOrderltemYwhere 顾客号='0002'andnotexists
（select*fromOrderltemZwhereZ.顾客号=X顾客号andZ商品号=Y商品号））
21. 向Article 表插入一条纪录。

删除无人购买的商品。

（检验一下刚插入的
记录是否已被删除）
deletefromArticlewhere 商品号notin
（select 商品号fromOrderItem）
22. 降低已售出的数量总合超过10 件的商品单价为原价的95%。

updateArticleset 单价二单价*0.95where 商品号in
（select 商品号fromOrderltemgroupby 商品号havingsum（数量）>10）
23. 建立断言：顾客的年龄必须大于18 岁。

CreateASSERTlONA1check
（notexists（select*fromCustommerwhere 年龄<=18））
24•把修改商品单价的权限授给用户Wang用户Wang可以转授该权限。

Gran tupdate（单价）on ArticletoWa ngwithgra ntoptio n
25.把修改商品单价的权限用户Wang收回，转授出去的也级联收回。

revokeupdate（单价）on ArticlefromWa ng cascade。