这是一道经典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
附图：稻城亚丁 水蓝色星球上的最后一片净土 国家地理 作者: 车可
