Oracle- SQL interview Question Answer Part -9

161) Display employee name and department name for each employee.
SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno

162)Display employee number,name and location of the department in which he is working.

SQL>select empno,ename,loc,dname from emp,dept where
         emp.deptno=dept.deptno;

163) Display ename,dname even if there are no employees working in a  particular department(use outer join).

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno(+)

164) Display employee name and his manager name.

SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;

165) Display the department name and total number of employees in each department.

SQL>select dname,count(ename) from emp,dept where

         emp.deptno=dept.deptno group by dname;

166)Display the department name along with total salary in each department.

SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname;

167) Display itemname and total sales amount for each item.

SQL>select itemname,sum(amount) from item group by itemname;

168) Write a Query To Delete The Repeted Rows from emp table;

SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename)         

169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL> select ename from emp
         where rowid in(select rowid from emp where rownum<=7
         minus
        select rowid from empi where rownum<5)

170)  DISPLAY  TOP N ROWS FROM TABLE?

SQL>SELECT * FROM (SELECT *  FROM EMP ORDER BY ENAME DESC)
                 WHERE ROWNUM <10;

171) DISPLAY   TOP 3 SALARIES FROM EMP;

SQL>SELECT SAL FROM ( SELECT  * FROM EMP ORDER  BY SAL DESC )WHERE ROWNUM <4

172) DISPLAY  9th FROM THE EMP TABLE?

SQL>SELECT ENAME FROM EMP
                WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10
                MINUS
                SELECT ROWID FROM EMP WHERE ROWNUM <10)
                select second max salary from emp;
                select max(sal) fromemp where sal<(select  max(sal) from emp);