Oracle- SQL interview Question Answer Part -6

                 

                

                                           SQL QUERIES -Part 6                      

101) Display name and salary of ford if his salary is equal to hisal of his

        Grade

a)select ename,sal,grade from emp,salgrade where sal between losal and

      hisal and ename ='FORD' AND HISAL=SAL;

102) Display employee name,job,depart name ,manager name,his grade and make

      out an under department wise?

SQL>SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP

E,SALGRADE,DEPT

WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR

 AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME

103) List out all employees name,job,salary,grade and depart name for every

one in the company  except 'CLERK'.Sort on salary display the highest salary?

SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE

       SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB

       NOT IN('CLERK')ORDER BY SAL ASC;

104) Display the employee name,job and his manager.Display also employee who

      are without manager?

SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where

         emp.empno(+)=e.mgr

105) Find out the top 5 earners of company?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL)

          FROM EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;

106) Display name of those employee who are getting the highest salary?

SQL>select ename from emp where sal=(select max(sal) from emp);

107) Display those employee whose salary is equal to average of maximum and

          minimum?

SQL>select ename from emp where sal=(select max(sal)+min(sal)/2 from emp);

108) Select count of employee in each department  where count greater than 3?

SQL>select count(*) from emp group by deptno having count(deptno)>3

109) Display dname where at least 3 are working and display only department

        name?

SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno

         and 3>any(select count(deptno) from emp group by deptno)

110) Display name of those managers name whose salary is more than average

salary of his company?

SQL>SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E

       WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);

111)Display those managers name whose salary is more than average salary of

        his employee?

SQL>SELECT DISTINCT EMP.ENAME FROM EMP,EMP E WHERE

            E.SAL <(SELECT AVG(EMP.SAL) FROM EMP

            WHERE EMP.EMPNO=E.MGR GROUP BY EMP.ENAME) AND

            EMP.EMPNO=E.MGR;

112) Display employee name,sal,comm and net pay for those employee

whose net pay is greter than or equal to any other employee salary of

the company?

SQL>select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp

          where sal+nvl(comm,0) >any (select sal from emp)

113) Display all employees names with total sal of company with each

        employee name?

SQL>SELECT ENAME,(SELECT SUM(SAL)  FROM EMP) FROM EMP;

114) Find out last 5(least)earners of the company.?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=

      (SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL<=E.SAL)

        ORDER BY SAL DESC;

115) Find out the number of employees whose salary is greater than their

    manager salary?

SQL>SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR

        AND EMP.SAL<E.SAL;

116) Display those department where no employee working?

SQL>select dname from emp,dept where emp.deptno not in(emp.deptno)

117) Display those employee whose salary is ODD value?

SQL>select * from emp where sal<0;

118) Display those employee whose salary contains alleast 3 digits?

SQL>select * from emp where length(sal)>=3;

119) Display those employee who joined in the company in the month of Dec?

SQL>select ename from emp where to_char(hiredate,'MON')='DEC';

120) Display those employees whose name contains "A"?

SQL>select ename from emp where instr(ename,'A')>0;

                                or

SQL>select ename from emp where ename like('%A%');