You can see part 6 here.
91) Display ename who are working in sales dept.
SQL>select ename from emp where deptno=(select deptno from dept where dname=’SALES’);
92) Display employee name,deptname,salary and comm for those sal in between 2000 to 5000 while location is Chicago.
SQL>select ename,dname,sal,comm from emp,dept where sal between 2000 and 5000 and loc=’CHICAGO’ and emp.deptno=dept.deptno;
93)Display those employees whose salary greater than his manager salary.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal;
94) Display those employees who are working in the same dept where their manager is working.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.deptno=e.deptno;
95) Display those employees who are not working under any manager.
SQL>select ename from emp where mgr is null;
96) Display grade and employees name for the dept no 10 or 30 but grade is not 4 while joined the company before 31-dec-82.
SQL>select ename,grade from emp,salgrade where sal between losal and hisal and deptno in(10,30) and grade<>4 and hiredate<’31-DEC-82′;
97) Update the salary of each employee by 10% increment who are not eligible for commission.
SQL>update emp set sal=sal+sal*10/100 where comm is null;
98) SELECT those employee who joined the company before 31-dec-82 while their dept location is Newyork or Chicago.
SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT WHERE (EMP.DEPTNO=DEPT.DEPTNO)AND HIREDATE <’31-DEC-82′ AND DEPT.LOC IN(‘CHICAGO’,’NEW YORK’);
99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING AS MANAGER?
SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not null;
100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? –[AND ALSO DISPLAY THEIR MANAGER NAME]?
SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND E.ENAME=’JONES’;
101) Display name and salary of ford if his salary is equal to high sal(hisalcolumn) of his grade
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 everyone 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;
