Oracle SQL 面试题（整理）

jiaxp

　　1、关于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 varchar(10),shengfu nchar(1));
　　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 a.rq,a.胜,b.负 from
　　(select rq,count(shengfu) as 胜 from tmp where shengfu='胜' group by rq) a,
　　(select rq,count(shengfu) as 负 from tmp where shengfu='负' group by rq) b
　　where a.rq=b.rq
　　方法二：高级查询---使用case表达式
　　case表达式可以在sql中实现if-then-else型的逻辑，而不用去使用PL/SQL。case工作方式与decode类似，但是我们应该使用case，因为它与ansi兼容。
　　有两种类型的case表达式
　　a 简单case表达式，使用case表达式确定返回值
　　b 搜索case表达式，使用条件确定返回值
　　select rq,sum(case when shengfu='胜' then 1 else 0 end) as 胜,
　　sum(case when shengfu='负' then 1 else 0 end) as 负
　　from tmp
　　group by rq
　　方法三：通过表的内连接与子查询联合实现，可以说是另一种方式，思想类似于方法一。
　　select a.rq,a.胜,b.负 from
　　(select rq,count(shengfu) 胜 from tmp where shengfu='胜'group by rq) a innerjoin
　　(select rq,count(shengfu) 负 from tmp where shengfu='负'group by rq) b
　　on a.rq=b.rq
　　2.表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
　　create table tmp2（a int,b int ,c int）;
　　insert into tmp values(10,20,30);
　　insert into tmp values(5,20,30);
　　insert into tmp values(10,7,30);
　　select (case when a>b then a else b end),(case when b>c then b else c end) from tmp2;
　　3.一个日期判断的sql语句
　　请取出tab5表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
　　select * from table t where to_char(t.SendTime,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
　　此题关键在于转换sendtime字段的格式，转换成日期格式。
　　4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：  
　　大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
　　显示格式：
　　语文              数学                英语
　　及格              优秀                不及格
　　-------------------------------------------------------------------------------------------
　　select
　　(case when语文>=80 then '优秀' when语文>60 then '及格' else '不及格' end) as 语文,
　　(case when 数学>=80 then '优秀' when数学>60 then '及格' else '不及格' end) as数学,
　　(case when英语>=80 then '优秀' when英语>60 then '及格' else '不及格' end) as 英语
　　from table
　　想到利用case表达式是关键。
　　5.请用一个sql语句得出结果
　　从table1,table2中取出如table3所列格式数据
　　table1
　　月份mon 部门dep   业绩yj
　　-------------------------------
　　一月份      01        10
　　一月份      02        10
　　一月份      03         5
　　二月份      02         8
　　二月份      04         9
　　三月份      03         8
　　table2
　　部门dep      部门名称depname
　　01      国内业务一部
　　02      国内业务二部
　　03      国内业务三部
　　04      国际业务部
　　table3 （result）
　　部门dep     一月份      二月份      三月份
　　01      10        null         null
　　02      10         8           null
　　03      5          null          8
　　04      null      9           null
　　1)
　　select t.depname,
　　(select yj from table1 where mon='一月份' and dep=t.dep) 一月份,
　　(select yj from table1 where mon='二月份' and dep=t.dep) 二月份,
　　(select yj from table1 where mon='三月份' and dep=t.dep) 三月份
　　from table1 t
　　2)求总销售额
　　select
　　sum(case when t1.mon='一月份' then t1.yj else 0 end) 一月份,
　　sum(case when t1.mon='二月份' then t1.yj else 0 end) 二月份,
　　sum(case when t1.mon='三月份' then t1.yj else 0 end) 三月份
　　from tab7 t,tab6 t1 where t.dep=t1.dep
　　6.一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
　　select id,count(*) from tab8 group by id having count(*)>1
　　select * from (select tab8,count(id) as num from tab8 group by id) t where t.num>1
　　7.用一条SQL语句 查询出每门课都大于80分的学生姓名
　　name   kecheng   fenshu
　　张三     语文       81
　　张三     数学       75
　　李四     语文       76
　　李四     数学       90
　　王五     语文       81
　　王五     数学       100
　　王五     英语       90
　　a): select distinct name from tab9 where name not in (select distinct name from tab9 where fengshut1.bh这个是不分的
　　9.怎么把这样一个表儿
　　year   month amount
　　1991   1     1.1
　　1991   2     1.2
　　1991   3     1.3
　　1991   4     1.4
　　1992   1     2.1
　　1992   2     2.2
　　1992   3     2.3
　　1992   4     2.4
　　查成这样一个结果
　　year m1   m2   m3   m4
　　1991 1.1 1.2 1.3 1.4
　　1992 2.1 2.2 2.3 2.4
　　a):
　　select t.year,
　　(select a.amout from tab11 a where a.month=1 and a.year=t.year) m1,
　　(select b.amout from tab11 b where b.month=2 and b.year=t.year) m2,
　　(select c.amout from tab11 c where c.month=3 and c.year=t.year) m3,
　　(select d.amout from tab11 d where d.month=4 and d.year=t.year) m4
　　from tab11 t group by t.year
　　10.拷贝表(拷贝数据,源表名：a 目标表名：b)
　　SQL: insert into b(a, b, c) select d,e,f from b;
　　create table test as select * from dept; --从已知表复制数据和结构
　　create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据
　　11.显示文章、提交人和最后回复时间
　　select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
　　13.两张关联表，删除主表中已经在副表中没有的信息
　　delete from fubiao a where a.fid not in(select id from zhubiao)
　　14.有两个表tab12和tab13，均有key和value两个字段，如果tab13的key在tab12中也有，就把tab13的value换为tab12中对应的value
　　update tab13 set value=(select value from tab12 where tab12.key=tab13.key)