SQL Queries ask in Interviews Part 8

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 al least 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%’);

<<<Previous    Continue>>>