ORACLE QUERIES

>>PREVIOUS>>

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

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;>

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%');

>>NEXT>>