oracle 上机操作题整理

每个题目都包含了一些知识点，认真做题慢慢领悟吧！

 

查找出当前用户模式下，每张表的记录数，以scott用户为例，结果应如下：
DEPT...................................4
EMP...................................14
BONUS.................................0
SALGRADE.............................5 

declare
    type tab_name is table of varchar2(80) index by binary_integer;
    t_name tab_name;
    coun number;
    str varchar(200);
begin
    select table_name bulk collect into t_name from user_tables;
    for i in t_name.first .. t_name.last loop
        str := 'select count(*) from '|| t_name(i);
        execute immediate str into coun;
        dbms_output.put_line(t_name(i)||'.....'||coun);
    end loop;
end;

某cc表数据如下：

c1 c2 

--------------

1 西

1 安

1 的

2 天

2 气

3 好

„„

转换为

1 西安的

2 天气

3 好 

要求：不能改变表结构及数据内容，仅在最后通过SELECT显示出这个查询结果

with temp as 
(select c1,c2,rownum c3 from cc )
select listagg(c2,'') within group (order by c3 ) from temp group by c1

请用一条sql语句查询出scott.emp表中每个部门工资前三位的数据，显示结果如下：
DEPTNO SAL1 SAL2 SAL3
------ ---------- ---------- -------------------------------------
10 5000 2450 1300
20 3000 2975 1100
30 2850 1600 1500
则，该语句为：

select deptno,max(decode(rn,1,sal)) sal1,
max(decode(rn,2,sal)) sal2,
max(decode(rn,3,sal)) sal3  from 
(
select deptno,sal,
row_number() over (partition by deptno order by sal desc nulls last) rn
from emp
 )
where rn<=3 
group by deptno

表nba记录了nba(team VARCHAR2(10),y NUMBER(4))夺冠球队的名称及年份：
TEAM Y
-------------------- ------------------------------
活塞 1990
公牛 1991
公牛 1992
公牛 1993
火箭 1994
火箭 1995
公牛 1996
公牛 1997
公牛 1998
马刺 1999
湖人 2000
湖人 2001
湖人 2002
马刺 2003
活塞 2004
马刺 2005
热火 2006
马刺 2007
凯尔特人 2008
湖人 2009
湖人 2010
请写出一条SQL语句，查询出在此期间连续获得冠军的有哪些，其连续的年份的起止时间是多少，结果如下：
TEAM B E
-------------------- ---------- --------------------------------
公牛 1991 1993
火箭 1994 1995
公牛 1996 1998
湖人 2000 2002
湖人 2009 2010

select max(team) team,min(y) B,min(y)+count(*) E 
from 
(select a.team,a.y,rownum rn from nba a, nba b where a.y=b.y-1 and a.team=b.team) t 
group by (y-rn) order by min(y) 

 

查询当前员工前一个入职的员工和后一个入职的员工与当前员工的薪资差异：

select ename,hiredate,sal,deptno,sal-pre_sal,sal-nex_sal from 
(
select ename,hiredate,sal,deptno,
lag(sal,1,0) over (order by hiredate) pre_sal,
lead(sal,1,0) over (order by hiredate ) nex_sal 
from emp
)

 

表 B 

c1                   c2

------------   ------

2005-01-01     1

2005-01-01     3

2005-01-02     5　　

要求处理数据结果为：

2005-01-01     4

2005-01-02     5

合计                 9   用一句sql完成：

select nvl(c1,'合计') , sum(c2) from B group rollup(c1)

 

有一个表 a ( x number(20) , y number(20) )

用最快高效的sql向该表插入 1 开始的连续的1000万记录

declare
    num number;
begin
    insert into a select rownum,rownum from dual connect by level < = 100000 ;
    for i in 1..10 loop
        select max (x) into num from a;
        insert into a select rownum+num,rownum+num from dual 
         connect by level < = 100000*i  ;
   end loop ;
end;