oracle中等面试题1

 

--（表是：t524-1、2、3）： select * from t524-1;

--1)查询各个年龄段的人数，结果输出年龄段及对应的人数，按年龄段升序排序 1)select count(b1.年龄段),b1.年龄段

from bm241 b1
group by 年龄段
order by b1.年龄段 asc;

--2)查询每个工龄区间的人数，结果输出工龄区间及对应的人数，按人数降序排序 --三年以下（工龄3） --三年七年（3<=工龄7） --七年十年（7<=工龄10） --十年十五年（10<=工龄15） --十五年及以上（15<=工龄）

--方法1:
select count(1),
       (case
         when 工龄 < 3 then
          '三年以下'
         when 工龄 >= 3 and 工龄 < 7 then
          '三年七年'
         when 工龄 >= 7 and 工龄 < 10 then
          '七年十年'
         when 工龄 >= 10 and 工龄 < 15 then
          '十年十五年'
         when 工龄 >= 15 then
          '十五年及以上'
       end) as 工龄区间
  from table1 t1
 group by (case
            when 工龄 < 3 then
             '三年以下'
            when 工龄 >= 3 and 工龄 < 7 then
             '三年七年'
            when 工龄 >= 7 and 工龄 < 10 then
             '七年十年'
            when 工龄 >= 10 and 工龄 < 15 then
             '十年十五年'
            when 工龄 >= 15 then
             '十五年及以上'
          end)
 order by count(1) desc;
--方法2:
select case
         when 工龄 < 3 then
          '三年以下'
         when 工龄 between 3 and 7 and 工龄 <> 7 then
          '三年七年'
         when 工龄 between 7 and 10 and 工龄 <> 10 then
          '七年十年'
         when 工龄 between 10 and 15 and 工龄 <> 15 then
          '十年十五年'
         else
          '十五年及以上'
       end 工龄阶段,
       sum(人数)
  from (select 工龄, count(1) 人数 from table1 T1 group by 工龄)
 group by case
            when 工龄 < 3 then
             '三年以下'
            when 工龄 between 3 and 7 and 工龄 <> 7 then
             '三年七年'
            when 工龄 between 7 and 10 and 工龄 <> 10 then
             '七年十年'
            when 工龄 between 10 and 15 and 工龄 <> 15 then
             '十年十五年'
            else
             '十五年及以上'
          end 
order by sum(人数) desc;

3)查询姓名重复三次的人员ID，结果以人员ID降序排序

select t1.人员姓名, t1.ID
  from n1 t1
 inner join (select count(1), 人员姓名
               from n1
              group by 人员姓名
             having count(1) = 3) t2
    on t1.人员姓名 = t2.人员姓名
 order by ID desc;
​
select ID
  from table1
 where 人员姓名 in
       (select 人员姓名 from table1 group by 人员姓名 having count(1) = 3)
 order by ID desc; 

4)查询每个公司岗位类别为‘一般管理’的人员中，各个学历的占比，结果输出，公司、学历、占比

--1:内连接：
select m1.学历,
       m1.公司简称,
       m1.l1,
       m2.l2,
       round(m1.l1 / m2.l2 * 100, 2) || '%' as "占比"
  from (select 学历, 公司简称, count(学历) l1
          from table1
         where 岗位类别 = '一般管理'
         group by 学历, 公司简称) m1
 inner join (select count(1) as l2, 公司简称
               from table1
              where 岗位类别 = '一般管理'
              group by 公司简称) m2
    on m1.公司简称 = m2.公司简称;
--2:左连接：
select t1.公司简称,
       t1.学历,
       round(t1.人数 / t2.人数 * 100, 2) || '%' as 占比
  from (select 公司简称, 学历, count(1) as 人数
          from table1
         where 岗位类别 = '一般管理'
         group by 公司简称, 学历) t1 
  left join (select 公司简称, count(1) as 人数
               from table1
              where 岗位类别 = '一般管理'
              group by 公司简称) t2
    on t1.公司简称 = t2.公司简称
--3:相关子查询：
select t.*,
       (select count(1)
          from table1
         where 岗位类别 = '一般管理'
           and 公司简称=t.公司简称 
           and 学历=t.学历  ) as 学历人数,  --select dname from dept where deptno=t.deptno
       (select count(1)
          from table1
         where 岗位类别 = '一般管理'
           and t.公司简称 = 公司简称) as 总人数
  from (select * from table1 T1 where 岗位类别 = '一般管理') t
    
--示例：   
select count(1)
  from table1
 where 岗位类别 = '一般管理'
   and 公司简称 = '广西分公司'
   and 学历 = '研究生'
​
select distinct
       公司简称 公司,
       学历,
       ( 
         (select count(1) from table1 where 岗位类别 = '一般管理' group by 公司简称, 学历 having t.公司简称=公司简称 and t.学历=学历 ) 
         / 
         (select count(1) from table1 where 岗位类别 = '一般管理' group by 公司简称 having t.公司简称=公司简称)
       ) 占比
from (select * from table1 T1 where 岗位类别 = '一般管理') t

5)查询去掉最大年龄，最小年龄后人员的平均年龄

--1:
select avg(年龄)
  from table1
 where 年龄 != (select max(年龄) from table1)
   and 年龄 != (select min(年龄) from table1);
--2:     
select avg(年龄) as平均年龄
  from table1
 where 年龄 not in
       ((select max(年龄) from table1), (select min(年龄) from table1));
--3:
select trunc(avg(b1.年龄),0)
from table1 b1
left join (select max(年龄) as 最大 from table1) b2
on b1.年龄=b2.最大
left join (select min(年龄) as 最小 from table1) b3
on b1.年龄=b3.最小
where b2.最大 is null and b3.最小 is null;

--面试题t524-2： select * from t524-2;

--1)查询新进类型中，不同原因的人数以及人数排名，结果输出原因、人数、排名

--1:
select t.人数, t.变动原因, rownum 排名
from(select count(变动原因) 人数,变动原因 
from table2
where 类型='新进'
group by 变动原因
order by count(变动原因) asc
) t;
--2:
select t.*, row_number() over(order by rs) as pm
  from (select count(1) as rs, 变动原因
          from table2
         where 类型 = '新进'
         group by 变动原因) t;
​
--3：
select t1.变动原因, t1.人数, count(t2.人数) + 1 as 排名
  from (select 变动原因, count(1) as 人数
          from table2
         where 类型 = '新进'
         group by 变动原因) t1
  left join (select 变动原因, count(1) as 人数
               from table2
              where 类型 = '新进'
              group by 变动原因) t2
    on t1.人数 < t2.人数
 group by t1.变动原因, t1.人数
 order by 排名;

--2)查询009年度，每个月的公司变化人数（新进人数-离职人数），结果输出年度、月度、变化人数，结果根据年度、月度升序排序

--1.多表连接-全连接实现：
select n3.*, (nvl(新进, 0) - 离职) as "变化人数"
  from (select t2.年度, t2.月度, t1.新进, t2.离职
          from (select 年度, 月度, count(1) as "新进"
                  from table2
                 where 年度 = 2009
                   and 类型 = '新进'
                 group by 年度, 月度) t1
          full join (select 年度, 月度, count(1) as "离职"
                      from table2
                     where 年度 = 2009
                       and 类型 = '离职'
                     group by 年度, 月度) t2
            on t1.月度 = t2.月度) n3
union
select 2009, 12, 0, 0, 0 from dual;
 --order by 年度, 月度;
​
 --左连接实现：
select 2009 as 年度 ,b1.月度,nvl(b2.xjrs,0)-b1.lzrs as 变化数
from
(select t2.月度,count(t2.变动原因) as lzrs from table2 t2
 where t2.年度=2009 and t2.类型='离职'
 group by t2.年度,t2.月度 
 ) b1
 left join
(select t1.月度,count(t1.变动原因) as xjrs from table2 t1
 where t1.年度=2009 and t1.类型='新进'
 group by t1.年度,t1.月度
 )b2
 on b1.月度 =b2.月度
 order by b1.月度;
​
--2.case when 判断：
SELECT   
    2009 AS 年度,  
    月度, count(1),COUNT(CASE WHEN 类型 = '新进' THEN 1 ELSE NULL END),COUNT(CASE WHEN 类型 = '离职' THEN 1 ELSE NULL END),
    COUNT(CASE WHEN 类型 = '新进' THEN 1 ELSE NULL END) -
    COUNT(CASE WHEN 类型 = '离职' THEN 1 ELSE NULL END) AS 变化人数
FROM TABLE2  
WHERE 年度 = 2009  
GROUP BY 月度
ORDER BY 月度;
​
--3：相关联子查询：
select t6.*, t6.新进人数 - t6.离职人数
  from (select distinct t5.年度,
                        t5.月度,
                        (select count(t1.变动原因)
                           from table2 t1
                          where t1.年度 = 2009
                            and t1.类型 = '新进'
                            and t1.月度 = t5.月度) as 新进人数,
                        (select count(t2.变动原因)
                           from table2 t2
                          where t2.年度 = 2009
                            and t2.类型 = '离职'
                            and t2.月度 = t5.月度
                         
​
                         ) as 离职人数
          from table2 t5
         where 年度 = 2009) t6
​
select * from table2;

--3)查询累计到每个年度的离职人数结果输出年度、累计离职人数（注意是累计不是合计）

--1: 
select 年度, sum(nd) over(order by 年度)
  from (select distinct 年度, count(1) over(partition by 年度) as nd
          from table2
         where 类型 = '离职');
--2:
select distinct 年度,count(1)over(order by 年度)
from table2
where 类型='离职'
order by 年度;
--3:
select t1.*, sum(离职人数) over(order by 年度)
  from (select distinct *
          from (select t.年度,
                       t.类型,
                       count(1) over(partition by t.年度 order by t.类型) as 离职人数
                  from table2 t
                 where 类型 = '离职')) t1;                  

--4)查询009年度，每个月的离职人数以及环比增长率，结果根据年度、月度升序排序

--1: 
select 月度,
       r,
       round((r - lag(r) over(order by 月度)) / lag(r) over(order by 月度)*100,2)||'%' as 环比增长率
  from (select distinct 月度,
                        count(1) over(partition by 月度) as r
          from table2
         where 年度 = 2009 and 类型 = '离职'
         order by 月度);
--2:
select 月度, 离职人数, (离职人数 - a) / a
  from (select 月度, 离职人数, lag(离职人数) over(order by 月度) a
          from (select distinct 月度,
                                count(1) over(partition by 月度) 离职人数
                  from table2
                 where 年度 = 2009
                   and 类型 = '离职'))
--3:
SELECT T.*,
       ROUND(((T.COUNT_1 - LAG(T.COUNT_1,
                               1,
                               (SELECT COUNT(1)
                                  FROM TABLE2
                                 WHERE 年度 = 2008
                                   AND 月度 = 12
                                   AND 类型 = '离职')) OVER(ORDER BY T.月度)) /
             LAG(T.COUNT_1,
                  1,
                  (SELECT COUNT(1)
                     FROM TABLE2
                    WHERE 年度 = 2008
                      AND 月度 = 12
                      AND 类型 = '离职')) OVER(ORDER BY T.月度)) * 100,

                2) || '%' AS 环比增长率
                   FROM (SELECT 月度, COUNT(1) AS COUNT_1
                             FROM TABLE2
                            WHERE 年度 = 2009
                              AND 类型 = '离职'
                            GROUP BY 月度) T;

--4:
select t1.*,
       lag(rs) over(order by 月度),
       (t1.rs - (lag(rs) over(order by 月度))) / (lag(rs) over(order by 月度)) as 环比
  from (select 年度, 月度, 类型, count(1) as rs
          from table2
         group by 类型, 年度, 月度
        having 类型 = '离职' and 年度 = 2009) t1

--面试题t524-3： select * from t524-3;

--1)随机建个含有主键的表，输出建表语句
create table table3 
(
 sno number(30) primary key,
 sname varchar2(100),
 age number(30)
);

--2)插入条数据，输出插入语句
insert into table3 values(1001,'学员1',24);
insert into table3 values(1002,'学员2',22);
insert into table3 values(1003,'学员3',23);
insert into table3 values(1004,'学员4',23);

--3)删除两条输出，输出删除语句
delete from table3 where sno = 1003 or sno = 1004;

--4)根据主键更新两条数据，输出更新语句
update table3 set sname = '老学员1',age = age + 1 where sno = 1001;
update table3 set sname = '老学员2',age = age - 1 where sno = 1002;

--5)查询一条数据，建成视图，输出建视图语句
create or replace view V_t3 as select * from table3 where sno = 1001;
select * from V_t3;