sql笔试题

如图，在微信群中遇到一个人求助，闲来无事，就试着看了下，居然发现自己不是很会。于是记录下来了。

首先我按照图片中的４个表，在oracle数据库中建了４张对应的表

第一题：

查询销售量最高产品的前两名：

我的思路：首先根据产品分组，然后求得每一组的总和，最后按照总和从大到小排序。然后将得到的结果作为一张子表　查询子表中的前两行

sql如下：

select a.pno
  from (select sum(volume) totalnumber, pno
          from cdm_deal
         group by pno
         order by totalnumber desc) a
 where rownum < 3;

得到的结果如图：

第二题：

--查询每种产品销量最高的销售人员

我的思路：

首先根据产品分组，得到每组中的最大值，然后根据最大值去找销售人员的id

select sname
  from cdm_salas
 where sno in (select sno
                 from cdm_deal
                where volume in (select volume
                                   from (select max(volume) volume, pno
                                           from cdm_deal
                                          group by pno)))

第三题：

查询产品编号为２且销售量超过１００的销售人员姓名以及所在公司

思路：根据条件查询出销售人员编号然后关联代理商表，展示查询结果

第四题：

我的思路：三个表关联，然后分组查询

问题五:

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

select pno,count(*) from cdm_deal group by pno;

问题6：

-- 查询名称包含BBB的代理商中的所有销售人员
select * from cdm_salas cs where  cs.ano in(

select ca.ano from cdm_agent ca where ca.aname like'%BBB%');

问题7：

-- 查询总销售量最差的产品
select *
  from cdm_production
 where pno in
       (select aa.pno
          from (select pno, sum(volume) total from cdm_deal group by pno) aa
         where rownum < 2)

问题8：

-- 查询2017年生产的产品的总销量
select sum(volume) from cdm_deal where pno in (
select pno from cdm_production where pdate >= TO_DATE('2017/1/1', 'YYYY-MM-DD'))

附录（表的创建以及插入的数据）：

create table CDM_AGENT
(
  ano   VARCHAR2(6),
  aname VARCHAR2(8)
)


create table CDM_DEAL
(
  sno    VARCHAR2(6),
  pno    VARCHAR2(6),
  volume VARCHAR2(6)
)


create table CDM_PRODUCTION
(
  pno   VARCHAR2(6),
  pname VARCHAR2(6),
  pdate DATE
)


create table CDM_SALAS
(
  sno   VARCHAR2(6),
  sname VARCHAR2(6),
  ano   VARCHAR2(6)
)




insert into CDM_AGENT (ano, aname)
values ('1', 'AAA公司');
insert into CDM_AGENT (ano, aname)
values ('2', 'BBB公司');
insert into CDM_AGENT (ano, aname)
values ('3', 'CCC公司');
commit;
prompt 3 records loaded
prompt Loading CDM_DEAL...
insert into CDM_DEAL (sno, pno, volume)
values ('1', '1', '21');
insert into CDM_DEAL (sno, pno, volume)
values ('1', '2', '56');
insert into CDM_DEAL (sno, pno, volume)
values ('1', '3', '33');
insert into CDM_DEAL (sno, pno, volume)
values ('2', '2', '120');
insert into CDM_DEAL (sno, pno, volume)
values ('2', '1', '32');
insert into CDM_DEAL (sno, pno, volume)
values ('2', '3', '26');
insert into CDM_DEAL (sno, pno, volume)
values ('3', '1', '73');
insert into CDM_DEAL (sno, pno, volume)
values ('3', '3', '45');
insert into CDM_DEAL (sno, pno, volume)
values ('4', '2', '111');
insert into CDM_DEAL (sno, pno, volume)
values ('4', '3', '84');
insert into CDM_DEAL (sno, pno, volume)
values ('5', '3', '52');
insert into CDM_DEAL (sno, pno, volume)
values ('5', '1', '38');
insert into CDM_DEAL (sno, pno, volume)
values ('5', '2', '91');
insert into CDM_DEAL (sno, pno, volume)
values ('6', '2', '36');
insert into CDM_DEAL (sno, pno, volume)
values ('6', '3', '48');
insert into CDM_DEAL (sno, pno, volume)
values ('6', '1', '15');
commit;


insert into CDM_PRODUCTION (pno, pname, pdate)
values ('1', '产品A', to_date('06-07-2015', 'dd-mm-yyyy'));
insert into CDM_PRODUCTION (pno, pname, pdate)
values ('2', '产品B', to_date('06-07-2016', 'dd-mm-yyyy'));
insert into CDM_PRODUCTION (pno, pname, pdate)
values ('3', '产品C', to_date('06-07-2017', 'dd-mm-yyyy'));
commit;


insert into CDM_SALAS (sno, sname, ano)
values ('1', '小赵', '2');
insert into CDM_SALAS (sno, sname, ano)
values ('2', '小钱', '2');
insert into CDM_SALAS (sno, sname, ano)
values ('3', '小孙', '3');
insert into CDM_SALAS (sno, sname, ano)
values ('4', '小李', '1');
insert into CDM_SALAS (sno, sname, ano)
values ('5', '小石', '3');
insert into CDM_SALAS (sno, sname, ano)
values ('6', '小刘', '1');
commit;