SQL练习3：商品销售数据库
商品销售数据库
Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int)
Customer(顾客号char (4)，顾客名 char (8)，性别 char(2)，年龄 int)
OrderItem(顾客号 char(4)，商品号 char(4)，数量 int, ⽇期 date)
--检索⾄少定购商品号为‘0001’和‘0002’的顾客号。(⽤交的⽅法)
select顾客号from OrderItem where商品号='0001'and顾客号in
( select顾客号from OrderItem where商品号='0002');
--检索⾄少定购商品号为‘0001’和‘0002’的顾客号。(⽤⾃表连接⽅法)
select X. 顾客号from OrderItem X,OrderItem Y
where X.顾客号=Y.顾客号and X.商品号='0001'and Y. 商品号='0002';
-
-创建⼀个视图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.商品号
下⾯是总体的练习题及答案：
--S Q L  习题
--商品销售数据库
--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）。顾客表的数据⽤插⼊语句输⼊数据，其它两表可⽤任意⽅式输⼊数据。
sql约束条件大于0create 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'or商品号='0002';
--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( *)>3order by4desc;
--13 检索年龄在30⾄40岁的顾客所购买的商品名及商品单价。
select商品名，单价from Custommer,Article,OrderItem
where Custommer.顾客号=OrderItem.顾客号and Article.商品号=OrderItem.商品号
and年龄between30and40;
--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.顾客号
--19 检索这样的顾客号，顾客名，他们定购了所有的商品（除法）
select Custommer.顾客号，顾客名from Custommer where not exists
(select*from Article where not exists
(select*from OrderItem
where 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.95where商品号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 option
--25. 把修改商品单价的权限⽤户Wang收回，转授出去的也级联收回。
revoke update (单价) on Article from Wang  cascade

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。