Oracle 经典面试题

2019-09-10 21:23 作者 :佚名 围观 : TAG标签: 面试题 Oracle

Oracle 经典面试题
第一题

copyright qzm4

复制代码
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'); qzm4.com

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

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

select * from test;
复制代码
  代码生成表格如: 求职面试网

   求职面试网

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

求职面试网

姓名 性别 年龄
张三 男 50
刘二 男 30
刘三 女 10 copyright qzm4

复制代码
/*
根据表格可以分析出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)) "姓名",

copyright qzm4

   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;
复制代码 求职面试网

第二题

求职面试网

复制代码
/* copyright qzm4

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 负

copyright qzm4

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

qzm4.com

      胜 负 内容来自求职面试网 

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','负');

copyright qzm4

select * from tmp;
*/ 求职面试网

--使用分组
--按日期分组,用conut函数计算次数
select rq "日期", 本文来自求职面试网

   count(decode(shengfu, '胜', 1)) "胜",
   count(decode(shengfu, '负', 1)) "负" 

copyright qzm4

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;
复制代码

copyright qzm4

第三题 内容来自求职面试网

复制代码
/*3.生成题目所需的表 qzm4.com

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); copyright qzm4
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);

copyright qzm4

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

qzm4.com

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

   显示格式:  
   语文              数学                英语  
   及格              优秀                不及格     copyright qzm4 

*/

求职面试网

--3.1
--使用分组
select name "姓名", 本文来自求职面试网

   max(decode(subject, '语文' ,score)) "语文",
   max(decode(subject, '数学' ,score)) "数学",
   max(decode(subject, '英语' ,score)) 英语 
qzm4.com

from STUDENT_SCORE
group by name;

本文来自求职面试网

--使用连表 copyright qzm4

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 '优秀' qzm4.com

        when t.语文>=60 then '及格'
        else '不及格'  end) 语文, 

qzm4.com

(case when t.数学>=80 then '优秀'

内容来自求职面试网

        when t.数学>=60 then '及格'
        else '不及格'  end) 数学, qzm4.com 

(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;
复制代码 本文来自求职面试网

第四题(这道题难度相对较高)

copyright qzm4

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

qzm4.com

table1

本文来自求职面试网

月份mon 部门dep 业绩yj

一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8

求职面试网

table2 求职面试网

部门dep 部门名称dname

  01      国内业务一部
  02      国内业务二部
  03      国内业务三部
  04      国际业务部
 

qzm4.com

table3 (result)

qzm4.com

部门dep 一月份 二月份 三月份

  01      10        null      null
  02      10         8        null
  03      null       5        8
  04      null      null      9
 求职面试网 

create table yj01(

本文来自求职面试网

   month varchar2(10),
   deptno number(10),
   yj number(10) qzm4.com 

)

内容来自求职面试网

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) copyright qzm4 

) 内容来自求职面试网

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)) 二月份, copyright qzm4
max(decode(month,'三月份',yj)) 三月份
from yj01 group by deptno
order by deptno;

copyright qzm4

--这道题给出了两张表,而用分组做,使用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;
复制代码 本文来自求职面试网

相关文章