某保险公司数据开发岗笔试题

有三张表 Consultant 表、BaseOrder 表、OrderDetail 表

Consultant：

BaseOrder：

OrderDetail：

 

建表以及插入数据：

(1)用SQL语句查出所有 SubsidiarylD 为28的订单，按照订单号(OrderlD)降序排序

select a.consultantid
    ,a.statusid
    ,a.subsidiaryid
    ,a.name
    ,b.orderid
    ,b.orderdate
from consultant a join baseorder b
on a.consultantid=b.consultantid
where subsidiaryid=28
order by orderid desc;

(2)用 SQL 语句查询出所有 11月份每个 ConsultantiD 的订单总额

select b.*,c.*
from BaseOrder b join OrderDetails c
on b.orderid=c.orderid
where substr(orderdate,4,2) like '11%';

这里在使用substr对日期进行截取之后，为什么后面要使用一次like '11%'而不直接写成where substr(orderdate,4,2) =11呢，因为直接使用substr截取出来的8月和9月会自动补个月出来，先对日期进行to_char的转换再使用substr得到的结果是一样的，最后都是有个月

​可以直接使用另外一个用法，直接使用to_char(orderdate,'MM')就能截取到正确月份了。所以也可直接使用to_char('','MM')这个用法，直接把日期转换成字符类型并截取日期中的月份 

select b.*,c.*
from BaseOrder b join OrderDetails c
on b.orderid=c.orderid
where to_char(orderdate,'MM')=11;

(3)在 Consultant 表加入一条记录，ConsultantlD=5，StatuslD=50，SubsidiaryiD=29.Name=Jone

这个题so easy：

insert into consultant values(5,50,29,'Jone');

(4)删除 Consultant 表中 Name 含有 Gary 的记录

so easy,too

delete from consultant 
where name like '%Gary%';

(5)将 BaseOrder 表中 ConsultantD=2 的最新创建的订单的订单时间 (OrderDate) 改为当前日期

--最简单的就是这个方法，不对日期做任何处理：

UPDATE BaseOrder1  
SET OrderDate = sysdate WHERE ConsultantID = 2;

--也可以修改成统一格式：
update BaseOrder1
SET OrderDate = to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
WHERE ConsultantID=2;

2、SQL进阶题目：使用SQL算出2022年11月总销售额的环比与同比增长率：
同比就是比去年同期，环比就是这个月比上个月
同比计算方式:同比增长率 =(今年本期数-去年同期数)/同期数X100%
环比计算方式:环比增长率=(同年本期数-同年上期数)/上期数X100%
订单表 Order

Order
PRD ID	Nums	Sale_date
C1001	12	2021-09-03
C1002	11	2021-10-29
C1003	6	2021-10-30
C1001	3	2021-11-21
C1002	4	2021-11-23
C1003	12	2022-10-01
C1001	5	2022-10-04
C1002	3	2022-11-07
C1003	24	2022-11-30

产品表 Product

Product
PRD ID	Name	Price
C1001	12	100
C1002	11	200
C1003	6	300

先建表：

create table Order1(
       PRD_ID varchar(10)
       ,Nums number
       ,Sale_date date
);
insert into Order1 values('c1001',12,to_date('2021-09-03','yyyy-mm-dd'));
insert into Order1 values('c1002',11,to_date('2021-10-29','yyyy-mm-dd'));
insert into Order1 values('c1003',6,to_date('2021-10-30','yyyy-mm-dd'));
insert into Order1 values('c1001',3,to_date('2021-11-21','yyyy-mm-dd'));
insert into Order1 values('c1002',4,to_date('2021-11-23','yyyy-mm-dd'));
insert into Order1 values('c1003',12,to_date('2022-10-01','yyyy-mm-dd'));
insert into Order1 values('c1001',5,to_date('2022-10-04','yyyy-mm-dd'));
insert into Order1 values('c1002',3,to_date('2022-11-07','yyyy-mm-dd'));
insert into Order1 values('c1003',24,to_date('2022-11-30','yyyy-mm-dd'));

create table Product(
       PRD_ID varchar(10)
       ,Name varchar(5)
       ,Price number
      );
insert into Product values('c1001',12,100);      
insert into Product values('c1002',11,200);    
insert into Product values('c1003',6,300); 

select * from Order2;
select * from Product;

with last_month_total_price as
    (select a.PRD_ID PRD_ID,a.sale_date,a.nums nums,b.price,sum(nums*price) over() total_price
     from order2 a join Product b
     on a.PRD_ID=b.PRD_ID
     where to_char(a.sale_date,'yyyy-mm') = '2022-10'
  )
  ,this_month_total_price as
     (select a.PRD_ID PRD_ID,a.sale_date,a.nums nums,b.price,sum(nums*price) over() total_price
     from order2 a join Product b
     on a.PRD_ID=b.PRD_ID
     where to_char(a.sale_date,'yyyy-mm') = '2022-11')
  ,last_year_total_price as
     (select a.PRD_ID PRD_ID,a.sale_date,a.nums nums,b.price,sum(nums*price) over() total_price
     from order2 a join Product b
     on a.PRD_ID=b.PRD_ID
     where to_char(a.sale_date,'yyyy-mm') = '2021-11')
     select round((this_month_total_price.total_price - last_month_total_price.total_price)/this_month_total_price.total_price,4)*100||'%'
     同比
     from this_month_total_price,last_month_total_price,last_year_total_price;