ORACLE QUERIES

>>PREVIOUS>>

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

>>NEXT>>