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

select * from (select rownum no,e.* from(select * from emp order by sal desc) e where rownum<=5) where no>=3; 
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