Some important Queries

n-th salary:

Select distinct sal from emp a where &n = (select count (distinct sal) from emp where a.sal<= sal);

First ‘n’ maximum salaries

Select sal from emp where

Sal >= (select distinct sal from emp a where &n = (select count(distinct sal) from emp

Where a.sal <= sal));

Selecting columns without duplicate

Select sal from emp a where rowid in (select min(rowid) from emp where a.sal = sal);

Deletion of Dulipcate Rows

Delete emp a where rowid not in (select min(rowid) from emp where a.sal = sal);

n-th row selection :

Select * from table_name x where &n = (select count(rowid) from table_name where x.rowid >= rowid;

Adding 10 minites to sysdate:

Select to_char(sysdate+(10/1440),’dd mm yyyy hh:mi’) from dual;

2-nd maximum salary:

Select max(sal) from emp where sal not in (select max(sal) from emp);

+

Count of occurrence of a particular character in a sentence/paragraph:

Select length(translate(‘saravana is a software programmer.’,’abcdefghijklmnopqrstuvwxyz., ‘,’a’)) from dual;

Query for Outer join:

Select emp.deptno,emp.dname,emp.ename from emp,dept where emp.deptno(+) = dept.deptno;

Example for Start with & Connect by Prior:

Select * from emp start with job = ‘PRESIDENT’ –other conditions also –

connect by prior empno = mgr – other conditons also –