Oracle 查询 包括高级查询案例

商品销售数据库

1--用SQL建立三个表，须指出该表的实体完整性和参照完整性，对性别和年龄指出用户自定义的约束条件。（性别分成男女，年龄从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  (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);
select * from Customer;


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'));
select * from OrderItem;


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


3.--检索定购商品号为‘S001’或‘S002’的顾客号和顾客名。
 select distinct o.cno,cname from Customer c ,OrderItem o
 where  c.cno=o.cno and (goodsno='s001' or goodsno='s002');
 
4.--检索至少定购商品号为‘S001’和‘S002’的顾客号。( 选购了商品s001于s002，选购商品大于等于2可以省略)
 select o.cno from Orderitem o;
 
  
 select * from Orderitem o where o.goodsno='s001';
 select * from Orderitem o where o.goodsno='s002';
                      
                       
 select o.cno, count(o.goodsno)
   from Orderitem o
  group by o.cno
 having count(o.goodsno) >= 2;
 
 
 
 --答案  第一种方法 
 select distinct o.cno
   from Orderitem o
  where o.cno in (select  o.cno from Orderitem o where o.goodsno = 's001')
    and o.cno in (select  o.cno from Orderitem o where o.goodsno = 's002')
    and o.cno in (select  o.cno  --count(o.goodsno)
   from Orderitem o
  group by  o.cno
 having count(o.goodsno) >=2);
 
 --第二种方法
select *
  from Orderitem o
 where o.goodsno = 's001'
   and exists (select *
          from orderitem o2
         where o2.goodsno = 's002'
           and o.cno = o2.cno);
           
5.--检索至少定购商品号为‘S001’和‘S002’的顾客号。(用自表连接方法) 
select *
  from Orderitem o
 where o.goodsno = 's001'
   and exists (select *
          from orderitem o2
         where o2.goodsno = 's002'
           and o.cno = o2.cno);


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




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




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




9.--检索至少订购了一种商品的顾客数。
select distinct o.cno from Orderitem o
where o.goodsno is not null ;




10.--检索订购了商品的人次数。
select count( o.cno) from OrderItem o where o.goodsnum is not null;




11.--检索顾客张三订购商品的总数量及每次购买最多数量商品和最少数量商品的数量之差。  
select sum(o.goodsnum), (max(o.goodsnum) - min(o.goodsnum))
  from Orderitem o, Customer c
 where o.cno = c.cno
   and c.cname = '张三';




12.--检索至少订购了3件商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并--按商品总数量降序排序。
--(*_*)商品次数用goodsno 算作商品次数
      --select * from emp e order by e.sal asc; --[desc]
      
-- 第一步      记录顾客号购买了 多少次商品 商品总数量 分组 以顾客号
select count(o.goodsno) ,sum(o.goodsnum), o.cno from Orderitem o group by o.cno;


--第二步       购买三件及以上商品的顾客号
select count(o.goodsno) ,sum(o.goodsnum),o.cno from Orderitem o group by o.cno
having count(o.goodsno)>=3;


--第三步      查询顾客名 顾客号
select c.cno,c.cname from customer c;


--最终合并 答案为
select c.cno, c.cname
  from customer c
 where c.cno in (select o.cno--count(o.goodsno), sum(o.goodsnum), 
                   from Orderitem o
                  group by o.cno
                 having count(o.goodsno) >= 3);




13.--检索年龄在30至40岁的顾客所购买的商品名及商品单价。 
       select a.goodsno, a.price
         from Article a, Customer c, Orderitem o
        where a.goodsno = o.goodsno
          and c.cno = o.cno
          and c.cage between 30 and 40;





14.--检索购买的商品的单价至少有一次高于或等于1000元的顾客号和顾客名。  
select distinct c.cno, c.cname ,o.goodsno from Orderitem o,Customer c,Article a
where a.goodsno = o.goodsno
   and c.cno = o.cno
and a.price>=1000;


15.--检索购买的购买价都高于或等于1000元的顾客号和顾客名。 
select distinct c.cno, cname ,o.goodsno from Orderitem o,Customer c,Article a
where a.goodsno = o.goodsno
   and c.cno = o.cno
and o.buyprice>=1000;




16.--检索女顾客购买的商品号，商品名和数量合计。 
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;


17.--检索所有的顾客号和顾客名以及它们所购买的商品号。（包括没买商品的顾客）
select distinct c.cno, c.cname, o.goodsno
  from Customer c, Orderitem o
 where c.cno = o.cno;


--18.检索这样的顾客号,顾客名,他们定购了所有的商品 （* 。*）


 --第一步   查询所有的商品种类
  select  count(1) from Article;


 --第二步   查看顾客所购买的商品的记录
  select count(distinct o.goodsno), o.cno  from Orderitem o group by o.cno;


 --第三步    查出购买所有商品的顾客号
  select count(distinct o.goodsno), o.cno  from Orderitem o group by o.cno
        having count(distinct (o.goodsno))=(select  count(a.goodsno)from Article a);
       
 --最后   查出顾客名所在的信息     答案为：  
 select *
   from Customer c
  where c.cno in
        (select  o.cno--count(distinct o.goodsno),
           from Orderitem o
          group by o.cno
         having count(distinct(o.goodsno)) = (select count(a.goodsno)
                                               from Article a));


20.--检索这样的顾客号，他们至少订购了顾客号为“G002”所订购的所有商品  （*——*）  暂时不会    


21.--向Article表插入一条纪录。删除无人购买的商品。（检验一下刚插入的记录是否已被--删除）
insert into Article  (goodsno，goodsname，price，goodsnum) values ('s005','计算机2',10000,10);
delete from Article  a where a.goodsno= 's005';


22.--降低已售出的数量总合超过10件的商品单价为原价的95%。
  update article set price=price*0.95 where goodsnum>10;
  select * from article;
23.--修改约束：顾客的年龄必须大于18岁。
alter table customer drop constraint c_cage;
alter table customer  add constraint c_cage check (cage >18);