1.Query to find Second Highest Salary of Employee?
Select distinct sal from emp e1 where 2=(Select count(distinct sal) from emp e2 where e1.sal<=e2.sal);
or
select min(sal) from (Select distinct(Sal) from Emp order by sal desc) where rownum<=2;
or
select * from (Select Dense_Rank() over (order by sal desc) as Rnk,E.* from Emp E) where Rnk=2;
2.Query to find duplicate rows in table?
select *from emp where rowid not in(select min(rowid) from emp group by sal);
or
Select * from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);
3.How to fetch monthly Salary of Employee if annual salary is given?
Select ename,Sal/12 as "MonthlySalary" from emp;
4.What is the Query to fetch first record from Employee table?
Select * from Emp where Rownum =1;
5.What is the Query to fetch last record from the table?
Select * from Emp where Rowid =(select max(Rowid) from Emp);
6.What is Query to display first 5 Records from Employee table?
Select * from emp where Rownum <= 5;
7.What is Query to display last 5 Records from Employee table?
select * from (Select * from emp order by rowid desc) where rownum <=5;
8.What is Query to display Nth Record from Employee table?
Select * from emp where rownum = &5;
9.How to get 3 Highest salaries records from Employee table?
select distinct sal from emp a where 3 >= (select count(sal) from emp b where a.sal <= b.sal) order by a.sal desc;
10.How to Display Odd rows in Employee table?
Select * from(Select rownum as rno,E.* from Emp E) where Mod(rno,2)=1;
11.How to Display Even rows in Employee table?
Select * from(Select rownum as rno,E.* from Emp E) where Mod(rno,2)=0;
12.How Can i create table with same structure of Employee table?
Create table employee2 as Select * from emp where 1!=2;
13.How Can i create table with same structure with data of Employee table?
Create table Employee3 as select * from Employee1 where 1=2;
14.How do i fetch only common records between 2 tables.
Select * from Emp
intersect
Select * from Employee1
15.Find Query to get information of Employee where Employee is not assigned to the department.
Select * from Employee1 where Deptno Not in(Select Deptno from Employee1);
16.How to get distinct records from the table without using distinct keyword.
select * from Employee1 a where rowid = (select max(rowid) from Employee1 b where a.empno=b.Empno);
17.Select all records from Employee1 table whose name is 'SMITH' and 'SCOTT'
Select * from Employee1 where ename in('SMITH' ,'SCOTT');
18.Select all records from Employee1 table whose name not in 'SMITH' and 'SCOTT'
Select * from Employee1 where ename not in('SMITH' ,'SCOTT');
