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;