Oracle 经典面试题分享

create table test(id number(10) primary key,
   type number(10) ,
   t_id number(10),
   value varchar2(6)
);

insert into test values(100,1,1,'张三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');

insert into test values(101,1,2,'刘二');
insert into test values(201,2,2,'男');
insert into test values(301,3,2,'30');

insert into test values(102,1,3,'刘三');
insert into test values(202,2,3,'女');
insert into test values(302,3,3,'10');

select * from test;

/*
根据表格可以分析出 type 列中 1 代表姓名、2 代表性别、3 代表年龄，而 t_id 中 id 一样的为同一个人的属性
查询结果中列依次为姓名、性别、年龄，而 type 列决定姓名、性别、年龄
*/

/* 使用分组, 先对 t_id 进行分组，然后用 decode 函数过滤数据，例：decode(type, 1, value) type= 1 就显示为 value
由于分组后 select 后面的列字段只能是分组的字段或者组函数，所有使用 max()。同一个人的 type 没有重复数值所以 decode(type, 1, value)返回的值只有一个，最大值也就是这个值
*/
select max(decode(type, 1, value)) "姓名",
       max(decode(type, 2, value)) "性别",
       max(decode(type, 3, value)) "年龄"
  from test
 group by t_id;

/* 使用连表，通过 where 过滤生成 3 张 type 分别等于 1(姓名)、2(性别)、3(年龄)的 3 张虚拟表 如：再通过 where 连接条件 三张表 t_id 相等的为同一个人或者说同一条记录（行）*/
select t1.value "姓名",t2.value "性别",t3.value "年龄" from 
(select value,t_id from test where type=1) t1,
(select value,t_id from test where type=2) t2,
(select value,t_id from test where type=3) t3
where t1.t_id=t2.t_id and t1.t_id=t3.t_id;

/*

2. 一道 SQL 语句面试题，关于 group by
表内容：2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写 sql 语句?

          胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-10','胜');
insert into tmp values('2005-05-10','负');
insert into tmp values('2005-05-10','负');

select * from tmp;
*/

-- 使用分组
-- 按日期分组，用 conut 函数计算次数
select rq "日期",
       count(decode(shengfu, '胜', 1)) "胜",
       count(decode(shengfu, '负', 1)) "负"
  from tmp
 group by rq
 order by rq;

-- 使用连表
-- 这道题本身就需要分组，不建议使用连表做
-- 以下使用的是 SQL1999 的连表方式，语法不一样效果与第一题使用的 SQL1992 的一样
select t1.rq,t1. 胜, t2. 负 from
(select count(decode(shengfu, '胜', 1)) "胜", rq from tmp group by rq) t1
join
(select count(decode(shengfu, '负', 1)) "负", rq from tmp group by rq) t2
on t1.rq=t2.rq;

/*3. 生成题目所需的表

create table STUDENT_SCORE
(name    VARCHAR2(20),
  subject VARCHAR2(20),
  score   NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

3.1 得到类似下面的结果
姓名   语文  数学  英语
王五    89    56    89
李四    xx    xx    xx
select * from STUDENT_SCORE;

3.2 有一张表，里面有 3 个字段：语文，数学，英语。其中有 3 条记录分别表示语文 70 分，数学 80 分，英语 58 分，请用一条 sql 语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：大于或等于 80 表示优秀，大于或等于 60 表示及格，小于 60 分表示不及格。显示格式：语文              数学                英语  
       及格              优秀                不及格    
------------------------------------------
*/

--3.1
-- 使用分组
select name "姓名",
       max(decode(subject, '语文'，score)) "语文",
       max(decode(subject, '数学'，score)) "数学",
       max(decode(subject, '英语'，score)) 英语
  from STUDENT_SCORE
 group by name;

-- 使用连表

select t1.name 姓名, t1.score 语文, t2.score 数学, t3.score 英语 from
(select name,score from STUDENT_SCORE  where subject='语文') t1
join
(select name,score from STUDENT_SCORE  where subject='数学') t2
on t1.name=t2.name
join
(select name,score from STUDENT_SCORE  where subject='英语') t3
on t1.name=t3.name;

--3.2
-- 在 3.1 的基础上使用 case when then esle end
select t. 姓名,
(case when t. 语文 >=80 then '优秀'
            when t. 语文 >=60 then '及格'
            else '不及格'  end) 语文,
(case when t. 数学 >=80 then '优秀'
            when t. 数学 >=60 then '及格'
            else '不及格'  end) 数学,
(case when t. 英语 >=80 then '优秀'
            when t. 英语 >=60 then '及格'
            else '不及格'  end) 英语
 from 
(select t1.name 姓名, t1.score 语文, t2.score 数学, t3.score 英语 from
(select name,score from STUDENT_SCORE  where subject='语文') t1
join
(select name,score from STUDENT_SCORE  where subject='数学') t2
on t1.name=t2.name
join
(select name,score from STUDENT_SCORE  where subject='英语') t3
on t1.name=t3.name
) t;

/*4. 请用一个 sql 语句得出结果
从 table1,table2 中取出如 table3 所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。table1

月份 mon 部门 dep 业绩 yj
-------------------------------
一月份      01      10
一月份      02      10
一月份      03      5
二月份      02      8
二月份      04      9
三月份      03      8

table2

部门 dep      部门名称 dname
--------------------------------
     国内业务一部
     国内业务二部
     国内业务三部
     国际业务部

table3（result）部门 dep 一月份      二月份      三月份
--------------------------------------
     10        null      null
     10         8        null
     null       5        8
     null      null      9

------------------------------------------

create table yj01(month varchar2(10),
       deptno number(10),
       yj number(10)
)

insert into yj01(month,deptno,yj) values('一月份',01,10);
insert into yj01(month,deptno,yj) values('二月份',02,10);
insert into yj01(month,deptno,yj) values('二月份',03,5);
insert into yj01(month,deptno,yj) values('三月份',02,8);
insert into yj01(month,deptno,yj) values('三月份',04,9);
insert into yj01(month,deptno,yj) values('三月份',03,8);

create table yjdept(deptno number(10),
       dname varchar2(20)
)

insert into yjdept(deptno,dname) values(01,'国内业务一部');
insert into yjdept(deptno,dname) values(02,'国内业务二部');
insert into yjdept(deptno,dname) values(03,'国内业务三部');
insert into yjdept(deptno,dname) values(04,'国际业务部');
*/
select * from yj01;
select * from yjdept;
-- 使用分组
select deptno,
max(decode(month,'一月份',yj)) 一月份, 
max(decode(month,'二月份',yj)) 二月份, 
max(decode(month,'三月份',yj)) 三月份 
from yj01 group by deptno
order by deptno;

-- 这道题给出了两张表，而用分组做，使用 yj01 表就能做出来了，所以这道题考察的应该是连表的知识
/* 这两张表中有的月份有的部门业绩是空的，而用前几道题的做法, 不匹配条件的值会被过滤掉，例如 month= 一月份的只有 1 部门，形成的表里 deptno 只有 1 和二月份、三月份形成的表中的 deptno 无法匹配
而 yjdept 表中包含了所有部门编号 deptno，这时就可以用到外连接的特性（在满足一张表的内容都显示的基础上，连接另外一张表，如果连接匹配则正常显示，连接不匹配，另外一张表补 null）*/
select t1.deptno, t1.yj 一月份, t2.yj 二月份, t3.yj 三月份
from
(select y2.deptno,y1.yj from
(select yj, deptno from yj01  where month='一月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t1
join
(select y2.deptno,y1.yj from
(select yj, deptno from yj01  where month='二月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t2
on t1.deptno=t2.deptno
join
(select y2.deptno,y1.yj from
(select yj, deptno from yj01  where month='三月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t3
on t1.deptno=t3.deptno
order by t1.deptno;