SQL语句经典面试题

这是一道经典SQL题

建表语句

 --产品表
create table Production(
   P# int not null,
   Pname varchar2(32),
   Pdate date

);
alter table Production add constraint pk_p# primary key (P#);

insert into Production values(1,'产品A',to_date('2017-6-25','yyyy-mm-dd'));
insert into Production values(2,'产品B',to_date('2017-5-25','yyyy-mm-dd'));
insert into Production values(3,'产品C',to_date('2016-8-10','yyyy-mm-dd'));

--代理商表
create table Agent(
  A# int not null,
  Aname varchar2(32)

);
--添加主键
alter table Agent add constraint pk_a# primary key (A#);

insert into Agent values(1,'AAA有限公司');
insert into Agent values(2,'BBB有限公司');
insert into Agent values(3,'CCC有限公司');


--销售人员表
create table Sales(
  S# int not null,
  Sname varchar2(32),
  A# int not null

);
alter table Sales add constraint pk_s# primary key (S#);
alter table Sales add constraint fk_a# foreign key (A#) references Agent(A#);

insert into Sales values(1,'小赵',2);
insert into Sales values(2,'小钱',2);
insert into Sales values(3,'小孙',3);
insert into Sales values(4,'小李',1);
insert into Sales values(5,'小石',3);
insert into Sales values(6,'小刘',1);

--销售量表
create table Deal(
  S# int not null,
  P# int not null,
  Volume int not null

);
alter table Deal add constraint fk_s# foreign key (S#) references Sales(S#);
alter table Deal add constraint fk_p# foreign key (P#) references Production(P#);

insert into Deal values(1,1,21);
insert into Deal values(1,2,56);
insert into Deal values(1,3,33);
insert into Deal values(2,2,120);
insert into Deal values(2,1,32);
insert into Deal values(2,3,26);
insert into Deal values(3,1,73);
insert into Deal values(3,3,45);
insert into Deal values(4,2,111);
insert into Deal values(4,3,84);
insert into Deal values(5,3,52);
insert into Deal values(5,1,38);
insert into Deal values(5,2,91);
insert into Deal values(6,2,36);
insert into Deal values(6,3,48);
insert into Deal values(6,1,15);

1.查询销量最高的产品的前两名使用 in 关键字

 --查询销量最高的产品的前两名使用 in 关键字
select * from deal
 
select p.* from Production p  where p.p# in (
select d.dp from (
select d.p# dp ,sum(d.volume) from deal d group by d.p#     order by   sum(d.volume) desc
) d
where rownum<=2
 ) 

2.查询每种产品销售量最高的销售人员 （使用 group by 关键字）

 --查询每种产品销售量最高的销售人员 （使用 group by 关键字）
  -- select d.p#       , max(d.volume) from deal d group by d.p#      
 ---  select * from deal d  
  
    select * from sales  s where s.s# in( 
  select d.s# from deal d ,
  ( select d.p# dp , max(d.volume) m from deal d group by d.p#   ) b 
    where d.volume =b.m and  d.p# =b.dp
  )

3.查询产品编号为2 且 销售量超过100的姓名及所在公司

--查询产品编号为2 且 销售量超过100的姓名及所在公司
  
 select a.a#, a.aname ,hh.sn from agent  a ,
 (
   select  s.a# sa , s.sname  sn from sales s where s.s# in
    (
    select d.s# from deal d where d.p#=2 and d.volume>100
    )
 )    hh
 where a.a#  =hh.sa

4.查询所有代理商所有产品的销售量

 --查询所有代理商所有产品的销售量
  
--select d.p# dp ,sum(d.volume) from deal d group by d.p#     order by   sum(d.volume) desc

 --select a.*,s.* from agent a 
 -- left join sales s
 -- on   a.a# =s.a#
--  left join deal d  
--  on s.s#=d.s#
 
  --select a.*,s.* from agent a , sales s   where a.a#=s.a# 
  
  select e.aa,sum(e.bb) from
  ( 
 select a.a# aa ,a.*   , s.* ,    (select  sum(d.volume)  from deal d where d.s#=s.s# ) bb
  from agent a , sales s   
  where a.a#=s.a#  
   ) e  group by e.aa
 

5.查询每个产品有多少个销售人员在销售

 --查询每个产品有多少个销售人员在销售
select * from deal d 
select d.p#,count(d.s#) from deal d  group by d.p#

6.查询名称包含BBB的代理商的所有销售人员

--查询名称包含BBB的代理商的所有销售人员
 select a.*,s.* from agent a 
 left join sales s
  on   a.a# =s.a#
 where a.aname like '%BBB%'

7.查询总销量最差的产品

--查询总销量最差的产品
select  *  from Production p where p.p# in
(
 select d.dp from (
 select d.p# dp ,sum(d.volume) from deal d group by d.p#     order by            sum(d.volume) desc
) d
where rownum<=1
 )

8.查询2017年生产的产品总销量

 --查询2017年生产的产品总销量
 
 select  sum(f.ff)  from  Production p,
(
select d.p# dp ,sum(d.volume) ff from deal d group by d.p#     order by   sum(d.volume) desc
) f
  where  to_char(p.pdate,'yyyy')='2017'  and f.dp=p.p#

祝你幸福
送你一首歌：《Something Just Like This》The Chainsmokers
附图：稻城亚丁 水蓝色星球上的最后一片净土 国家地理 作者: 车可