Oracle Practice SQL Queries 04:

Oracle Practice SQL Queries 04:
    
51) Display those employees whose manager name is Jones?

        SELECT e.ename superior, e1.ename subordinate
          FROM emp e, e1
         WHERE e.empno = e1.mgr AND e.ename = 'JONES'

52) Display those employees whose salary is more than 3000 after giving 20% increment?

        SELECT ename, sal, (sal + (sal * 0.20))
          FROM emp
         WHERE (sal + (sal * 0.20)) > 3000;
        
53) Display all employees with their department names?
    Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno


54) Display ename who are working in sales department?

        SELECT e.ename, d.dname
          FROM emp e, dept d
         WHERE e.deptno = d.deptno AND d.dname = 'SALES'


55) Display employee name, dept name, salary, and commission for those sal in between 2000 to 5000 while location is Chicago?

        SELECT e.ename, d.dname, e.sal, e.comm
          FROM emp e, dept d
         WHERE e.deptno = d.deptno AND sal BETWEEN 2000 AND 5000
        
56) Display those employees whose salary is greater than his manager’s salary?
             SELECT e.ename, e.sal, e1.ename, e1.sal
              FROM emp e, e1
             WHERE e.mgr = e1.empno AND e.sal > e1.sal
            
57) Display those employees who are working in the same dept where his manager is work?

        SELECT e.ename, e.deptno, e1.ename, e1.deptno
          FROM emp e, e1
         WHERE e.mgr = e1.empno AND e.deptno = e1.deptno
        
58) Display those employees who are not working under any Manager?
        Select ename from emp where mgr is null;
       
       
59) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?

                SELECT ename, grade, deptno, sal
                  FROM emp, salgrade
                 WHERE (grade, sal) IN (SELECT grade, sal
                                          FROM salgrade, emp
                                         WHERE sal BETWEEN losal AND hisal)
                   AND grade ! = 4
                   AND deptno IN (10, 30)
                   AND hiredate < '31-Dec-82'
                  
60) Update the salary of each employee by 10% increment that are not eligible for commission?
            Update emp set sal= (sal+(sal*0.10)) where comm is null

61) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
            SELECT e.ename, e.hiredate, d.loc
              FROM emp e, dept d
             WHERE e.deptno = d.deptno
               AND hiredate < '31-Dec-82'
               AND d.loc IN ('NEW YORK', 'CHICAGO')
              
62) Display employee name, job, deptname, and loc for all who are working as manager?

            SELECT e.ename, e.job, d.dname, d.loc
              FROM emp e, dept d
             WHERE e.deptno = d.deptno AND e.empno IN (SELECT mgr
                                                         FROM emp
                                                        WHERE mgr IS NOT NULL)

63) Display those employees whose manager name is Jones and also display their manager name?

                    SELECT e.ename sub, e1.ename
                      FROM emp e, emp e1
                     WHERE e.mgr = e1.empno AND e1.ename = 'JONES'
                    
64) Display name and salary of ford if his salary is equal to hisal of his grade?
            Select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;
            OR
            Select grade, sal, hisal from emp, salgrade where ename='FORD' and sal between losal and hisal;
            OR
             SELECT ename, sal, hisal, grade
              FROM emp, salgrade
             WHERE ename = 'FORD' AND (grade, sal) IN (SELECT grade, hisal
                                                         FROM salgrade, emp
                                                        WHERE sal BETWEEN losal AND hisal);
                                           
                                           
65) Display employee name, job, deptname, his manager name, his grade and make an under department wise?

                SELECT   e.ename sub, e1.ename sup, e.job, d.dname, grade
                    FROM emp e1, salgrade, dept d
                   WHERE e.mgr = e1.empno
                     AND e.sal BETWEEN losal AND hisal
                     AND e.deptno = d.deptno
                GROUP BY d.deptno, e.ename, e1.ename, e.job, d.dname, grade
                OR
                
                SELECT e.ename sub, e1.ename sup, e.job, d.dname, grade
                  FROM emp e, e1, salgrade, dept d
                 WHERE e.mgr = e1.empno
                   AND e.sal BETWEEN losal AND hisal
                   AND e.deptno = d.deptno
                  
66) List out all the employee names, job, salary, grade and deptname for every one in a company except ‘CLERK’. Sort on salary display the highest salary?

            SELECT   e.ename, e.job, e.sal, d.dname, grade
                FROM emp e, salgrade, dept d
               WHERE (e.deptno = d.deptno AND e.sal BETWEEN losal AND hisal)
            ORDER BY e.sal DESC
           
           
67) Display employee name, job and his manager. Display also employees who are with out managers?

        Select e.ename, e1.ename, e.job, e.sal, d.dname from emp e, emp e1, dept d where e.mgr=e1.empno (+) and e.deptno=d.deptno

68) Display Top 5 employee of a Company?

69) Display the names of those employees who are getting the highest salary?
            Select ename, sal from emp where sal in (select max (sal) from emp)

70) Display those employees whose salary is equal to average of maximum and minimum?
            Select * from emp where sal=(select (max (sal)+min (sal))/2 from emp)