Oracle数据库高级查询案例



/*********************商品销售数据库***********************/

1--用SQL建立三个表Article、Customer、OrderItem，其中Article表主键为goodsno,Customer主键为cno,
--对Customer表中性别和年龄指出用户自定义的约束条件。（性别分成男女，年龄从10到100）。

create table Article(
       goodsno  varchar2(4) constraint pk_goodsno primary key,
       goodsname  varchar2(16),--商品名
       price    number(8,2),--单价
       goodsnum  int --数量
)
drop table Article;
insert into Article values('s005','电磁炉',1200,5);
insert into Article  (goodsno，goodsname，price，goodsnum) values ('s001','计算机',5000,10);
insert into Article  (goodsno，goodsname，price，goodsnum) values ('s002','打印机',1000,12);
insert into Article  (goodsno，goodsname，price，goodsnum) values ('s003','洗衣机',800,10);
insert into Article  (goodsno，goodsname，price，goodsnum) values ('s004','电冰箱',1100,20);
select* from Article;

create table Customer(
       cno  varchar2(4) constraint pk_cno primary key,
       cname  varchar2(8) not null,
       csex   varchar2(2) check (csex in('男','女')),
       cage   int constraint c_cage check (cage between 10 and 100)
)
drop table Customer;
insert into Customer(cno,cname,csex,cage) values('G001','张三','男',29);
insert into Customer(cno,cname,csex,cage) values('G002','李四','女',25);
insert into Customer(cno,cname,csex,cage) values('G003','王五','女',31);
insert into Customer(cno,cname,csex,cage) values('G004','赵六','男',25);
insert into Customer(cno,cname,csex,cage) values('G005','小酒','女',20);
insert into Customer(cno,cname,csex,cage) values('G006','小猪','女',22);
insert into Customer(cno,cname,csex,cage) values('G007','小爱','女',22);
select * from Customer;

/*
create table OrderItem(
 cno varchar2(4) ,
 goodsno varchar2(4),
 goodsnum int,
 buyprice number(8,2),
 buytime date,
 constraints fk_cno foreign key(cno) references  Customer(cno),
 constraints fk_sno foreign key(goodsno) references Article(goodsno)
)
*/
create table OrderItem(
       cno  varchar2(4) constraint fk_manno references Customer(cno）,
       goodsno  varchar2(4) constraint fk_goodno references Article (goodsno),
       goodsnum   int, --数量
       buyprice number(8,2),--购买价
       buytime   date --购买日期
)
select * from Orderitem;
drop table OrderItem;

insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G001','s001',1,5000,to_date('2016-05-25','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G001','s002',2,900,to_date('2016-05-25','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G001','s002',2,900,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G001','s003',1,800,to_date('2016-05-25','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G001','s004',1,1100,to_date('2016-05-25','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G002','s001',1,4900,to_date('2016-05-25','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G002','s004',1,1100,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G003','s001',1,4800,to_date('2016-05-25','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G004','s001',1,5000,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G004','s002',3,950,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G005','s002',1,1000,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G006','',0,0,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G007','',0,0,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G007','',0,0,to_date('2016-05-26','yyyy-mm-dd'));
insert into OrderItem(cno,goodsno,goodsnum,buyprice,buytime) values('G002','s003',1,1050,to_date('2016-05-26','yyyy-mm-dd'));
select * from OrderItem;

--检索定购商品号为‘S001’的顾客号和顾客名。         --用o.cno 也可以查到 （* —— *）
select c.cno,cname from customer c,orderItem o where c.cno=o.cno and goodsno='s001';

--检索定购商品号为‘S001’或‘S002’的顾客号和顾客名
select distinct c.cno,cname from customer c,orderItem o where c.cno=o.cno and (goodsno='s001' or goodsno='s002');
select distinct c.cno,cname from customer c,orderItem o where c.cno=o.cno and  goodsno in( 's001' ,'s002' );

--检索至少定购商品号为‘S001’和‘S002’的顾客号。( 选购了商品s001和s002，选购商品大于等于2可以省略)
select distinct c.cno from customer c,orderItem o where c.cno=o.cno and
o.cno in (select cno from OrderItem where goodsno='s001') and goodsno='s002';

select * from Orderitem o where o.goodsno = 's001' and exists (select * from orderitem o2 where o2.goodsno = 's002' and o.cno = o2.cno);

--检索至少定购商品号为‘S001’和‘S002’的顾客号。(用自表连接方法)
select distinct O1.cno from orderItem o1,orderItem o2 where O1.cno=O2.cno
and O1.cno in( select cno from OrderItem where goodsno='s001') and O1.goodsno='s002';

--检索没定购商品的顾客号和顾客名。
select c.cno,cname from customer c, OrderItem o where c.cno=o.cno and goodsno is null;
select c.cno，cname from Customer  c,Orderitem o where c.cno=o.cno and o.goodsnum=0;

--检索一次定购商品号‘S001’商品数量最多的顾客号和顾客名。
select c.cno,cname ,max(goodsnum) from customer c,orderItem o  where goodsno='s001' and c.cno=o.cno group by c.cno,c.cname;

 
--检索男顾客的人数和平均年龄。
select count(*) ,avg(cage) from Customer where csex='男';

--检索至少订购了一种商品的顾客数
select count(*) from (select count(cno) from orderItem where goodsno is not null group by cno);

--检索至少订购了一种商品的顾客号
select cno, count( cno) from orderItem where goodsno is not null group by cno;
select distinct o.cno from Orderitem o where o.goodsno is not null ;

--检索顾客张三订购商品的总数量及每次购买最多数量商品和最少数量商品的数量之差。 
select sum(goodsnum),max(goodsnum)-min(goodsnum) as goodCnum from orderItem o,customer c WHERE
c.cno=o.cno AND cname='张三';

.--检索至少订购了3件商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并--按商品总数量降序排序。
--(*_*)商品次数用goodsno 算作商品次数
select cno,cname,nums from
(select count(*) nums,c.cno ,cname from orderItem o,customer c where c.cno=o.cno group by c.cno,cname order by nums desc) where nums>=3;

--检索年龄在30至40岁的顾客所购买的商品名及商品单价。
select goodsname,price from  Article a,orderItem o,customer c WHERE
a.goodsno=o.goodsno and c.cno=o.cno and (cage BETWEEN 30 and 40);

 

--检索购买的商品的购买价至少有一次高于或等于1000元的顾客号和顾客名。
select c1.cno,c1.cname from (select c.cno,c.cname ,max(buyprice) as price from customer c,orderItem o WHERE
c.cno=o.cno  group by c.cno,c.cname) c1 where price>=1000;

--检索购买的购买价都高于或等于1000元的顾客号和顾客名。
select c1.cno,c1.cname from (select c.cno,c.cname ,min(buyprice) as price from customer c,orderItem o WHERE
c.cno=o.cno  group by c.cno,c.cname) c1 where price>=1000;

--检索女顾客购买的商品号，商品名和数量合计
select o.goodsno,a.goodsname ,sum(o.goodsnum) from Orderitem o,Customer c,Article a --count(o.数量)
where a.goodsno = o.goodsno  and c.cno = o.cno  and c.csex='女' group by o.goodsno,a.goodsname;

--检索所有的顾客号和顾客名以及它们所购买的商品号。（包括没买商品的顾客）
select o.cno,c.cname ,WM_CONCAT(o.goodsno) from customer c,orderitem o
where c.cno=o.cno group by o.cno,c.cname;

--检索这样的顾客号,顾客名,他们定购了所有的商品
select Customer.cno,cname from Customer where not exists
(select * from Article where not exists
(select * from OrderItem
where OrderItem.cno=Customer.cno and OrderItem.goodsno=Article.goodsno));

--检索这样的顾客号，他们至少订购了顾客号为“G002”所订购的所有商品
select DISTINCT goodsno from orderItem where cno='G002';  --顾客号为'G002'订购的所有商品
select c.cno,c.cname from customer c where not EXISTS
(select * from (select DISTINCT goodsno from orderItem where cno='G002') temptable where not EXISTS
(select * from orderItem o where o.cno=c.cno and o.goodsno=temptable.goodsno));

--降低已售出的数量总合超过5件的商品单价为原价的95%。
update Article set price=price*0.95 where goodsno  in
(select goodsno from orderItem having sum(goodsnum)>5 group by goodsno);
select * from Article;

--修改约束：顾客的年龄必须大于18岁。
alter table customer drop constraint c_cage ;  --删除原有约束
alter table customer add constraint c_cage check(cage>18);