SQL Query Inrterview Questions

These questions are the most frequently asked in interviews

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)

          Ans:select * from emp where rowid in (select decode

                  (mod(rownum,2),0,rowid, null) from emp);

2. To select ALTERNATE records from a table. (ODD NUMBERED)

         Ans:select * from emp where rowid in 

                (select decode(mod(rownum,2),0,null ,rowid) from emp);

3. Find the 3rd MAX salary in the emp table.

Ans: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.

          Ans :select distinct sal from emp e1 where 3 = (select

                   count(distinct sal) from emp e2where e1.sal >= e2.sal);

5. Select FIRST n records from a table.

          Ans :select * from emp where rownum <= &n;

6. Select LAST n records from a table

         Ans :select * from emp minus select * from emp where 

                 rownum   <= (select count(*) - &n from emp);

7. List dept no., Dept name for all the departments in which there are no employees in the department.

         Ans select * from dept where deptno not in 

                (select deptno from emp);

8. alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);

         Ans Altertnate solution:  

            select empno,ename,b.deptno,dname from emp a, dept b 

            where a.deptno(+) = b.deptno and empno is null;

9. How to get 3 Max salaries ?

         Ans :select distinct sal from emp a where 3 >= 

                 (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

10. How to get 3 Min salaries ?

         Ans :select distinct sal from emp a  where 3 >= 

                 (select count(distinct sal) from emp b  where a.sal >= b.sal);

11. How to get nth max salaries ?

         Ans :select distinct hiredate from emp a where &n =  

                 (select count(distinct sal) from emp b where a.sal >= b.sal);

12. Select DISTINCT RECORDS from emp table.

         Ans :select * from emp a where  rowid = 

                 (select max(rowid) from emp b where  a.empno=b.empno);

13. How to delete duplicate rows in a table?

        Ans :delete from emp a where rowid != 

                (select max(rowid) from emp b where  a.empno=b.empno);

14. Count of number of employees in  department  wise.

         Ans :select count(EMPNO), b.deptno, dname from emp a, dept b  

                 where a.deptno(+)=b.deptno  group by b.deptno,dname;

15. Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

         Ans :

• select ename,sal/12 as monthlysal from emp;
• Select all record from emp table where deptno =10 or 40.
• select * from emp where deptno=30 or deptno=10;
• Select all record from emp table where deptno=30 and sal>1500.
• select * from emp where deptno=30 and sal>1500;

16. Select  all record  from emp where job not in SALESMAN  or CLERK.

         Ans :select * from emp where job not in ('SALESMAN','CLERK');

17. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

          Ans :select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

18. Select all records where ename starts with ‘S’ and its lenth is 6 char.

         Ans : select * from emp where ename like'S____';

19. Select all records where ename may be any no of  character but it should end with ‘R’.

         Ans : select * from emp where ename like'%R';

20. Count  MGR and their salary in emp table.

         Ans : select count(MGR),count(sal) from emp;

21. In emp table add comm+sal as total sal  .

         Ans : select ename,(sal+nvl(comm,0)) as totalsal from emp;

22. Select  any salary <3000 from emp table.

         Ans :select * from emp  where sal> any(select sal from emp where sal<3000);

23. Select  all salary <3000 from emp table.

          Ans select * from emp  where sal> all(select sal from emp where sal<3000);

24. Select all the employee  group by deptno and sal in descending order.

          Ans select ename,deptno,sal from emp order by deptno,sal desc;

25. How can I create an empty table emp1 with same structure as emp?

         Ans Create table emp1 as select * from emp where 1=2;

26. How to retrive record where sal between 1000 to 2000?

         Ans Select * from emp where sal>=1000 And  sal<2000

27. Select all records where dept no of both emp and dept table matches.

          Ans :select * from emp where exists(select * from dept 

                   where emp.deptno=dept.deptno)

28. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?

         Ans :(Select * from emp) Union (Select * from emp1)

29. How to fetch only common records from two tables emp and emp1?

         Ans :(Select * from emp) Intersect (Select * from emp1)

30. How can I retrive all records of emp1 those should not present in emp2?

        Ans (Select * from emp) Minus (Select * from emp1)

31. Count the totalsa  deptno wise where more than 2 employees exist.

        Ans :

                  SELECT  deptno, sum(sal) As totalsal

                  FROM emp

                  GROUP BY deptno

                  HAVING COUNT(empno) > 2

Facebook Commentbox