Oracle的四道經(jīng)典面試題分享

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;

/*
根據(jù)表格可以分析出type列中1代表姓名、2代表性別、3代表年齡，而t_id中id一樣的為同一個(gè)人的屬性
查詢結(jié)果中列依次為姓名、性別、年齡，而type列決定姓名、性別、年齡
*/

/*使用分組,先對(duì)t_id進(jìn)行分組，然后用decode函數(shù)過(guò)濾數(shù)據(jù)，例：decode(type, 1, value) type=1就顯示為value
由于分組后select后面的列字段只能是分組的字段或者組函數(shù)，所有使用max()。
同一個(gè)人的type沒(méi)有重復(fù)數(shù)值所以 decode(type, 1, value)返回的值只有一個(gè)，最大值也就是這個(gè)值
*/
select max(decode(type, 1, value)) "姓名",
  max(decode(type, 2, value)) "性別",
  max(decode(type, 3, value)) "年齡"
 from test
 group by t_id;

/*使用連表，通過(guò)where過(guò)濾生成3張type分別等于1(姓名)、2(性別)、3(年齡)的3張?zhí)摂M表 如：

再通過(guò)where 連接條件 三張表t_id相等的為同一個(gè)人或者說(shuō)同一條記錄（行）
*/
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語(yǔ)句面試題，關(guān)于group by
表內(nèi)容：
2005-05-09 勝
2005-05-09 勝
2005-05-09 負(fù)
2005-05-09 負(fù)
2005-05-10 勝
2005-05-10 負(fù)
2005-05-10 負(fù)

如果要生成下列結(jié)果, 該如何寫(xiě)sql語(yǔ)句?

   勝 負(fù)
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','負(fù)');
insert into tmp values('2005-05-09','負(fù)');
insert into tmp values('2005-05-10','勝');
insert into tmp values('2005-05-10','負(fù)');
insert into tmp values('2005-05-10','負(fù)');

select * from tmp;
*/

--使用分組
--按日期分組，用conut函數(shù)計(jì)算次數(shù)
select rq "日期",
  count(decode(shengfu, '勝', 1)) "勝",
  count(decode(shengfu, '負(fù)', 1)) "負(fù)"
 from tmp
 group by rq
 order by rq;

--使用連表
--這道題本身就需要分組，不建議使用連表做
--以下使用的是SQL1999的連表方式，語(yǔ)法不一樣效果與第一題使用的SQL1992的一樣
select t1.rq,t1.勝, t2.負(fù) from
(select count(decode(shengfu, '勝', 1)) "勝", rq from tmp group by rq) t1
join
(select count(decode(shengfu, '負(fù)', 1)) "負(fù)", 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 ('張三', '語(yǔ)文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('張三', '數(shù)學(xué)', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('張三', '英語(yǔ)', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '語(yǔ)文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '數(shù)學(xué)', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英語(yǔ)', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '語(yǔ)文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '數(shù)學(xué)', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英語(yǔ)', 91.0);

3.1得到類似下面的結(jié)果
姓名 語(yǔ)文 數(shù)學(xué) 英語(yǔ)
王五 89 56 89
李四 xx xx xx
select * from STUDENT_SCORE;

3.2有一張表，里面有3個(gè)字段：語(yǔ)文，數(shù)學(xué)，英語(yǔ)。其中有3條記錄分別表示語(yǔ)文70分，數(shù)學(xué)80分，英語(yǔ)58分，
請(qǐng)用一條sql語(yǔ)句查詢出這三條記錄并按以下條件顯示出來(lái)（并寫(xiě)出您的思路）： 
大于或等于80表示優(yōu)秀，大于或等于60表示及格，小于60分表示不及格。 
  顯示格式： 
  語(yǔ)文    數(shù)學(xué)    英語(yǔ) 
  及格    優(yōu)秀    不及格 
------------------------------------------
*/

--3.1
--使用分組
select name "姓名",
  max(decode(subject, '語(yǔ)文' ，score)) "語(yǔ)文",
  max(decode(subject, '數(shù)學(xué)' ，score)) "數(shù)學(xué)",
  max(decode(subject, '英語(yǔ)' ，score)) 英語(yǔ)
 from STUDENT_SCORE
 group by name;

--使用連表

select t1.name 姓名, t1.score 語(yǔ)文, t2.score 數(shù)學(xué), t3.score 英語(yǔ) from
(select name,score from STUDENT_SCORE where subject='語(yǔ)文') t1
join
(select name,score from STUDENT_SCORE where subject='數(shù)學(xué)') t2
on t1.name=t2.name
join
(select name,score from STUDENT_SCORE where subject='英語(yǔ)') t3
on t1.name=t3.name;

--3.2
--在3.1的基礎(chǔ)上使用 case when then esle end
select t.姓名,
(case when t.語(yǔ)文>=80 then '優(yōu)秀'
   when t.語(yǔ)文>=60 then '及格'
   else '不及格' end) 語(yǔ)文,
(case when t.數(shù)學(xué)>=80 then '優(yōu)秀'
   when t.數(shù)學(xué)>=60 then '及格'
   else '不及格' end) 數(shù)學(xué),
(case when t.英語(yǔ)>=80 then '優(yōu)秀'
   when t.英語(yǔ)>=60 then '及格'
   else '不及格' end) 英語(yǔ)
 from 
(select t1.name 姓名, t1.score 語(yǔ)文, t2.score 數(shù)學(xué), t3.score 英語(yǔ) from
(select name,score from STUDENT_SCORE where subject='語(yǔ)文') t1
join
(select name,score from STUDENT_SCORE where subject='數(shù)學(xué)') t2
on t1.name=t2.name
join
(select name,score from STUDENT_SCORE where subject='英語(yǔ)') t3
on t1.name=t3.name
) t;

/*4.請(qǐng)用一個(gè)sql語(yǔ)句得出結(jié)果
從table1,table2中取出如table3所列格式數(shù)據(jù)，注意提供的數(shù)據(jù)及結(jié)果不準(zhǔn)確，
只是作為一個(gè)格式向大家請(qǐng)教。


table1

月份mon 部門(mén)dep 業(yè)績(jī)yj
-------------------------------
一月份  01  10
一月份  02  10
一月份  03  5
二月份  02  8
二月份  04  9
三月份  03  8

table2

部門(mén)dep  部門(mén)名稱dname
--------------------------------
  國(guó)內(nèi)業(yè)務(wù)一部
  國(guó)內(nèi)業(yè)務(wù)二部
  國(guó)內(nèi)業(yè)務(wù)三部
  國(guó)際業(yè)務(wù)部

table3 （result）

部門(mén)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,'國(guó)內(nèi)業(yè)務(wù)一部');
insert into yjdept(deptno,dname) values(02,'國(guó)內(nèi)業(yè)務(wù)二部');
insert into yjdept(deptno,dname) values(03,'國(guó)內(nèi)業(yè)務(wù)三部');
insert into yjdept(deptno,dname) values(04,'國(guó)際業(yè)務(wù)部');
*/
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表就能做出來(lái)了，所以這道題考察的應(yīng)該是連表的知識(shí)
/*這兩張表中有的月份有的部門(mén)業(yè)績(jī)是空的，而用前幾道題的做法,不匹配條件的值會(huì)被過(guò)濾掉，
例如month=一月份的只有1部門(mén)，形成的表里deptno只有1和二月份、三月份形成的表中的deptno無(wú)法匹配
而yjdept表中包含了所有部門(mén)編號(hào)deptno，這時(shí)就可以用到外連接的特性
（在滿足一張表的內(nèi)容都顯示的基礎(chǔ)上，連接另外一張表，如果連接匹配則正常顯示，連接不匹配，另外一張表補(bǔ)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;