You can see part 7 here.
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 atleast 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%’);
Questions/Suggestions
Have any question or suggestion for us?Please feel free to post in
Q&A Forum
