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;

exist

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

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

then
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

1.

`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

2.

`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