Oracle的四道经典面试题分享

前言

本文整理了4道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;

代码生成表格如：

根据以上代码生成的表写出一条查询语句，查询结果如下：

select max(decode(type, 1, value)) "姓名",
  max(decode(type, 2, value)) "性别",
  max(decode(type, 3, value)) "年龄"
 from test
 group by 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;

第二题

--使用分组
--按日期分组，用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.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;

第四题（这道题难度相对较高）

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表就能做出来了，所以这道题考察的应该是连表的知识

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;

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对考高分网的支持。