SQLnPLSQLGuru

These questions asked in most of the interviews: 1 . To FETCH ALTERNATE records FROM a table . ( EVEN NUMBERED ) 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 ) SELECT * FROM emp WHERE rowid IN ( SELECT decode ( mod ( rownum , 2 ), 0 , NULL , rowid ) FROM emp ); 3 . Find the 3 rd MAX salary IN the emp table . SELECT DISTINCT sal FROM emp e1 WHERE 3 = ( SELECT count ( DISTINCT sal ) FROM emp e2 WHERE e1 . sal <= e2 . sal ); 4 . Find the 3 rd MIN salary IN the emp table . 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 . SELECT * FROM emp WHERE rownum <= n ; 6 . Select LAST n records FR

211) List the empno,ename,loc,dname of all the depts.,10 and 20. A) s elect e.empno,e.ename,e.deptno,d.loc,d.dname from emp e ,dept d where e.deptno = d.deptno and e.deptno in (10,20); 212) List the empno, ename, sal, loc of the emps working at Chicago dallas with an exp>6ys. A) s elect e.empno,e.ename,e.deptno,e.sal,d.loc from emp e ,dept d where e.deptno = d.deptno and d.loc in ('CHICAGO','DALLAS') and (months_between(sysdate,hiredate)/12)> 6 ; 213) List the emps along with loc of those who belongs to dallas ,newyork with sal ranging from 2000 to 5000 joined in 81. A) s elect e.empno,e.ename,e.deptno,e.sal,d.loc from emp e ,dept d where e.deptno = d.deptno and d.loc in ('NEW YORK','DALLAS') and to_char(e.hiredate,'YY') = '81' and e.sal between 2000 and 5000; 214) List the empno,ename,sal,grade of all emps. A) s elect e.empno,e.ename,e.sal,s.grade from emp e ,salgrade s where e.sal between s.losal and s.hisal ;

181) List the empno,ename,sal,TA30%,DA 40%,HRA 50%,GROSS,LIC,PF,net,deduction,net allow and net sal in the ascending order of the net salary. 182) List the emps who are working as managers. A) s elect * from emp where job = ‘MANAGER’; 183) List the emps who are either clerks or managers. A) s elect * from emp where job in (‘CLERK’,’MANAGER’); 184) List the emps who have joined on the following dates 1 may 81,17 nov 81,30 dec 81 A) select * from emp where to_char(hiredate,’DD-MON-YY’) in (’ 01-MAY-81’,’17-NOV-81’,’30-DEC-81’); 185) List the emps who have joined in the year 1981. A) select * from emp where to_char(hiredate,’YYYY’) = ‘1981’; 186) List the emps whose annual sal ranging from 23000 to 40000. A) s elect * from emp where (12* sal) between 23000 and 40000; 187) List the emps working under the mgrs 7369,7890,7654,7900. A) select * from emp where mgr in ( 7369,7890,7654,7900); 188) List the emps who joined in the second half of 82. A)select * from emp where hi

151) List all the emps by name and number along with their Manager’s name and number. Also List KING who has no ‘Manager’. A) s elect w.empno,w.ename,m.empno,m.ename from emp w,emp m where w.mgr= m.empno(+); 152) Find all the emps who earn the minimum Salary for each job wise in ascending order. A) s elect * from emp where sal in (select min(sal) from emp group by job) order by sal asc; 153) Find out all the emps who earn highest salary in each job type. Sort in descending salary order. A) s elect * from emp where sal in (select max(sal) from emp group by job) order by sal desc; 154) Find out the most recently hired emps in each Dept order by Hiredate. A) s elect * from emp e where hiredate in (select max(hiredate) from emp where e.deptno = deptno ) order by hiredate; 155) List the employee name,Salary and Deptno for each employee who earns a salary greater than the average for their department order by Deptno. A) s elect * from emp e where sal > (select avg(sa

121. List THE Name of dept where highest no.of emps are working. A) select dname from dept where deptno in (select deptno from emp group by deptno having count(*) in (select max(count(*)) from emp group by deptno) ); 122. Count the No.of emps who are working as ‘Managers’(using set option). A)se lect count(*) from(select * from emp minus select * from emp where job != 'MANAGER') 123. List the emps who joined in the company on the same date. A) s elect * from emp e where hiredate in (select hiredate from emp where e.empno <> empno); 124. List the details of the emps whose Grade is equal to one tenth of Sales Dept. A) select * from emp e,salgrade s where e.sal between s.losal and s.hisal and s.grade = 0.1* (select deptno from dept where dname = 'SALES'); 125. List the name of the dept where more than average no. of emps are working. A) select d.dname from dept d, emp e where e.deptno = d.deptno group by d.dname having count(*) > (select avg