SQL Query

Google Advertisements

Prev Tutorial Next Tutorial

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;

Prev Tutorial Next Tutorial

Google Advertisements

Buy This Ad Space @$50 per Month, Ad Size 600X200 Contact on: hitesh.xc@gmail.com or 8076671483

Pure VPN Privide Lowest Price VPN Just @ $1.65. Per Month with Non Detected IP Lowest Price Non Detected IP VPN

Magenet is best Adsense Alternative here we earn $2 for single link, Here we get links ads. Magenet

Cloud computing is the on demand availability of computer system resources, especially data storage and computing power, without direct active management by the user. Cloud Computing Tutorial

College Projects Related to Java, AWT, C Projects for College, C++ Projects for College, Android Projects. Download Java C C++ Projects

Download Projects

Adsense Advertisements


Buy This ads Space 8076671483

Buy This Ad Space @$120 per Month, Ad Size 300X600 Contact on: hitesh.xc@gmail.com or 8076671483 Try this Keyword C++ Programs