SQL Query
SQL Query
Here i will give you all sql query which is frequently ask in any interview. Below all queries are most important for interview.
1. How to get nth max salaries ?
Syntax
select distinct sal from emp e1 where &n = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
2. How to get nth min salaries ?
Syntax
select distinct sal from emp e1 where &n = (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);
3. Find the 3rd MAX salary in the emp table.
Syntax
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.
Syntax
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);
5. Select FIRST n records from a table.
Syntax
select * from emp where rownum <= &n;
6. Select LAST n records from a table
Syntax
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. How to get 3 Max salaries ?
Syntax
select distinct sal from emp e1 where 3 >= (select count(distinct sal) from emp e2 where e1.sal <= e2.sal) order by e1.sal desc;
8. How to get 3 Min salaries ?
Syntax
select distinct sal from emp e1 where 3 >= (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);
9. Select DISTINCT RECORDS from emp table.
Syntax
select * from emp e1 where rowid = (select max(rowid) from emp e2 where e1.empno=e2.empno);
10. How to delete duplicate rows in a table ?
Syntax
delete from emp e1 where rowid != (select max(rowid) from emp e2 where e1.empno=e2.empno);
11. Count of number of employees in department wise.
Syntax
select count(empno), d.deptno, dname from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno,dname;
12. Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
Syntax
select ename,sal/12 as monthlysal from emp;
13. Select all record from emp table where deptno =10 or 40.
Syntax
select * from emp where deptno=30 or deptno=10;
14. Select all record from emp table where deptno=30 and sal>1500.
Syntax
select * from emp where deptno=30 and sal>1500;
15. Select all record from emp where job not in SALESMAN or CLERK.
Syntax
select * from emp where job not in ('SALESMAN','CLERK');
16. Select all record from emp where ename in 'BLAKE','SCOTT','KING' and 'FORD'.
Syntax
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
17. Select all records where ename starts with 'S' and its lenth is 6 char.
Syntax
select * from emp where ename like'S______';
18. Select all records where ename may be any no of character but it should end with 'R'.
Syntax
select * from emp where ename like'%R';
19. Count MGR and their salary in emp table.
Syntax
select count(MGR),count(sal) from emp;
20. In emp table add comm+sal as total sal
Syntax
select ename,(sal+nvl(comm,0)) as totalsal from emp;