Frequently Asked Sql Queries

How to display 1 to 10 numbers

select level from dual connect by level<=10;

How to delete duplicates records

Delete emp where rowid not in (select min(rowid) from emp group by sal);

How to find highest salary in a table

select * from emp where sal in (select  max(sal) from emp);

How to find second highest salary

1. Select * from emp where sal=(select max(sal) from emp where sal <(select max(sal) from emp));

2. Select * from (select sal,rank() over (order by sal desc) as rnk from (select  distinct sal from emp)) where rnk=2;

3. Select level,max(sal) from emp where level=&levelno connect by prior sal> sal group by level;

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

5. select sal from(select sal from  (select distinct sal from emp order by sal desc) where rownum<=2 order by sal asc) where rownum=1;

To find highest and lowest values

select max(sal) from emp

union

select min(sal) from emp;

How to get fist and last record from a table in oracle?

select * from EMP where rownum=1

union

select * from EMP where rowid=(select max(rowid) from EMP);

To find the count of duplicate rows

Select ename, count (*) from emp group by ename having count(*) >= 1;

To see the duplicate records

SELECT * FROM  EMP WHERE (SAL IN (SELECT sal FROM emp GROUP BY sal HAVING COUNT(sal) > 1));

Query to find a_b format names

select * from emp where ename like '%A_B%';

Explain set operators