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;

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

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) t3where t1.t_id=t2.t_id and t1.t_id=t3.t_id;

         胜 负2005-05-09 2 22005-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;

select rq "日期",       count(decode(shengfu, '胜', 1)) "胜",       count(decode(shengfu, '负', 1)) "负"  from tmp group by rq order by rq;

select t1.rq,t1.胜, t2.负 from(select count(decode(shengfu, '胜', 1)) "胜", rq from tmp group by rq) t1join(select count(decode(shengfu, '负', 1)) "负", rq from tmp group by rq) t2on t1.rq=t2.rq;

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

姓名   语文  数学  英语王五    89    56    89李四    xx    xx    xxselect * from STUDENT_SCORE;

大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。         显示格式：         语文              数学                英语         及格              优秀                不及格    ------------------------------------------*/

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='语文') t1join(select name,score from STUDENT_SCORE  where subject='数学') t2on t1.name=t2.namejoin(select name,score from STUDENT_SCORE  where subject='英语') t3on t1.name=t3.name;

t 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='语文') t1join(select name,score from STUDENT_SCORE  where subject='数学') t2on t1.name=t2.namejoin(select name,score from STUDENT_SCORE  where subject='英语') t3on t1.name=t3.name) t;

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 deptnoorder 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)t1join(select y2.deptno,y1.yj from(select yj, deptno from yj01  where month='二月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t2on t1.deptno=t2.deptnojoin(select y2.deptno,y1.yj from(select yj, deptno from yj01  where month='三月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t3on t1.deptno=t3.deptnoorder by t1.deptno;