Oracle advanced query (1)

topN query

select  * from emp where rownum=1   or   rownum=2;
select * from   (select * from emp order by    sal desc) where rownum<5;
  • 1
  • 2

Paging query

  1. select * from (select rownum no,e.* from(select * from emp order by sal desc) e where rownum<=5) where no>=3;

  2. select * from (select rownum no,e.* from(select * from emp order by sal desc)e) where no>3 and no<=5;


select * from t1 where exists (select null from t2 where y=x);

for x in (select * from t1)
    if(exist(select null from t2 where y=x.x))

    output the record
     end if;
end loop;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

select 1 from dual where null in (0,1,2,null);

Multiline subquery

select * from emp   where sal>any(select avg(sal) from emp group by deptno);
  • 1
select * from emp   where sal>all(select avg(sal) from emp group by deptno);
  • 1
select * from emp   where  job in (select job from emp where ename='MARTIN' or ename='SMITH');
  • 1

Random return of 6 records

select * from (select ename,job from emp order by dbms_random.value())      where rownum<=6;
  • 1

Process null sort last(first)

select * from emp order by comm desc nulls last;

Query skip even rows in table

select ename from (select row_number() over (order by ename) rn,ename
from emp) x where mod(rn,2)=1;

Find employee information and the employee with the highest and lowest salary

select ename,sal,max(sal) over(),min(sal) over() from emp;
  • 1

Continuous summation

select ename,sal,sum(sal) over(),sum(sal) over(order by ename) from emp;
  • 1

sum(sal) over(order by ename)
It refers to continuous summation, which is sorted by ename. If there are two such window functions, they are mainly sorted later.

Continuous sum of sub sectors

select deptno,sal,sum(sal) over (partition by deptno order by ename) as s from emp;
  • 1

Get data from previous or next row

select ename,sal,lead(sal) over (order by sal) aaa,lag(sal) over(order by sal) bbb from emp;
  • 1

Statistics monthly, last month and next month's total income month person income

select [month] ,sum([income]),lead(sum ([income])) over(order by sum([income]))   from [table] group by [month];
  • 1

Group by substring

select to_char(hiredate,'yyyy'),avg(sal) from emp group by to_char(hiredate,'yyyy');
  • 1

Determine the number of days in a year

select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')  as Days from dual;
  • 1

Query the information of the top two employees in each department under emp employee table


select deptno,ename,sal from emp e1 where (select count(1) from emp e2 where e2.deptno=e1.deptno and e2.ename!=e1.ename and e2.sal>e1.sal)<2 order by deptno, sal desc;
  • 1


select * from (select deptno ,ename,sal,row_number() over (partition by deptno order by sal desc) rn  from emp)  where rn<3;
  • 1

data dictionary

Query all tables under a user

select table_name from all_tables where owner='SCOTT';
  • 1

Query all fields (columns) in emp table

select * from all_tab_columns where table_name='TEMP';
  • 1

List the index columns of the table

select *from  sys.all_ind_columns where table_name='TEMP';
select * from sys.all_ind_columns where upper(table_name)='CAREUSERHAM';
  • 1
  • 2

List constraints in table

select  * from all_constraints where table_name='TEMP';
  • 1

Describe data dictionary view in oracle

select table_name,comments from dictionary where table_name like '%TABLE%';
  • 1
Copyright notice: This is the original article of the blogger. It can't be reproduced without the permission of the blogger.

Keywords: Oracle

Added by infid3l on Sat, 02 May 2020 23:53:05 +0300