SQL笔试题

   一套SQL笔试题。 这算第一份正式工作吧，还是值得纪念的。整理在此，普惠有心人！

                                                                                                                                                            2017-07-14    于杭州     为值得的人付出，做一个有价值的人

一、表结构：

    

create Table tjjrflbm                   --客户经理分类编码

(

ID number(12) not null,             --ID

FLBM varchar(10) not null，    --分类编码

FLMC varchar(30) not null，    --分类名称

NOTE varchar(50)                    --说明

);  

create table tjjr                         --客户经理信息表

(

IDnumber(12) not null,           --客户经理ID

JJRBH varchar(12) not null,    --客户经理编号

JJRXM varchar(12) not null,    --客户经理姓名

JJRLb number(8) not null,        --客户经理分类（对应Tjjrflbm.ID）

RZRQ number(8) ,                 --入职日期

LZRQ number(8) ,                 --离职日期

ZHZT number(8),                  --账户状态（0|正常;1|销户）

JBGZ number(12,2)              --基本工资

);  

create table tkhgx                 --客户关系表

(

  id     NUMBER(16) not null,

  khjl   NUMBER(22),           --客户经理 Tjjr.id

  khgxlx NUMBER(12),         --客户关系类型（1|营销；2|服务；3|签约）

  fwkhh  NUMBER(22),         --服务客户号

  sxrq   NUMBER(8),             --关系生效日期

  jzrq   NUMBER(8),             --关系截止日期

  gxzt   NUMBER(8)             --0|正常;1|截止

)

二、题目：

1、写出表Tjjrflbm增加一条记录和更新一条记录的 SQL语句； 

    insert into tjjrflbm(id, flbm, flmc, note) values(func_nextid('tjjrflbm'),'10','客户经理','10');
    update tjjrflbm set (flbm,flmc,note)=(select '12','营销人员','12' from dual) where flbm='10';                --简单题，送分    

2、查找入职日期大于2014年1月1日且账户状态为正常，同时该客户经理名下所有有效的客户关系的客户超过10个客户的客户经理的编号、姓名、对应分类编码、分类名称，入职日期，并按客户经理id升序排列。

   select t1.jjrbh,t1.jjrxm,t2.flbm,t2.flmc,t1.rzrq from tjjr t1,tjjrflbm t2
   where t1.rzrq>20140101 and t1.zhzt=0 and t1.id in
   (select khjl from tkhgx where gxzt=0 group by khjl having count(distinct fwkhh)>10)
   and t1.jjrlb=t2.id;                                                                                                                                     --难度适中

  

3、查看每类客户经理分类的编号、名称和账户状态为正常的客户经理人数，按人数倒序排列。

    select t2.flbm 经理分类的编号,t2.flmc 经理分类的名称,count(distinct t1.jjrbh) 人数  
    from tjjr t1,tjjrflbm t2 where t1.zhzt=0 
   and t1.jjrlb=t1.id
   group by t1.jjrlb,t2.flbm,t2.flmc order by count(distinct t1.jjrbh) desc;                                                     --低难度

4、写一个SQL语句给入职年限超过24个月，且基本工资低于3000，同时名下当前生效的关系为签约关系的客户超过5个客户的客户经理提高15%的基本工资。

   update tjjr set jbgz=round(jbgz*1.5,2) where jbgz<3000 and months_between(sysdate,to_date(rzrq,'yyyymmdd'))>24
   and id in(select khjl from tkhgx where khgxlx=3 group by khjl having count(distinct fwkhh)>5)             --中等难度

   months_between 函数的使用比较简便，不用也行。返回两个日期之间间隔的月份。    

   两个日期之间间隔天数直接用to_date - to_date就行；

5、编写一个存储过程，传入参数为营业部、月份。

     处理逻辑为：将离职日期在传入月份内、同时营业部为参入营业部范围内的客户经理的名下如果有未解除客户关系的客户关系，将该数据自动更新为截止状态，

     截止日期为离职日期。

create or replace procedure pro_jckhgx(o_result out number,                                       o_note out varchar2,                                       i_yyb in number,                                       i_yf in number)asv_gxzt number(1);begin  --取客户经理  for v_khjl in (select id,lzrq from tjjr where yyb=i_yyb and substr(lzrq,1,6)=to_char(i_yf)) loop    --方法一：效率低    /*for v_kh in(select fwkhh from tkhgx where khjl=v_khjl.id) loop   --取该客户经理服务的客户        select gxzt into v_gxzt from tkhgx where fwkhh=v_kh.fwkhh;        if v_gxzt=0 then          update tkhgx set gxzt=1,jzrq=v_khjl.lzrq where fwkhh=v_kh.fwkhh;        end if;    end loop;*/        --方法二：更好    update tkhgx set gxzt=1,jzrq=v_khjl.lzrq where khjl=v_khjl.id and gxzt=0;  end loop;o_result:=1;o_note:='处理成功！';commit;exception  when others then  o_result:=-1;  o_note:=sqlerrm;  rollback;end pro_jckhgx;

三、个人评价：

      这5套题目，只涉及到了3张表，难度不大。但是考试时间有限，熟练度要求高