Oracle- SQL interview Question Answer Part -5

                                                           

                         
                                            
          SQL QUERIES -Part 5                


81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.

SQL>select distinct(job) from emp where deptno=10 or deptno=20

           (or)

SQL>select distinct(job) from emp where deptno in(10,20);

82) Display the jobs which are unique to department 10.

SQL>select distinct(job) from emp where deptno=10

83) Display the details of those who do not have any person working under them.

SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by

         e.ename having count(*)=1;

84) Display the details of those employees who are in sales department and

       grade is 3.

SQL>select * from emp where deptno=(select deptno from dept where

           dname='SALES')and sal between(select losal from salgrade where grade=3)and

       (select hisal from salgrade where grade=3);

85) Display those who are not managers and who are managers any one.

i)display the managers names

SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;

ii)display the who are not managers

SQL>select ename from emp where ename not in(select distinct(m.ename)

         from emp e,emp m where m.empno=e.mgr);

86) Display those employee whose name contains not less than 4 characters.

SQL>select ename from emp where length(ename)>4;

87) Display those department whose name start with "S" while the location

name ends with "K".

SQL>select dname from dept where dname like 'S%' and loc like '%K';

88) Display those employees whose manager name is JONES.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and

         e.ename='JONES';

89) Display those employees whose salary is more than 3000 after giving 20%

       increment.

SQL>select ename,sal from emp where (sal+sal*.2)>3000;

90) Display all employees while their dept names

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno

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 greter 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 his

       manager is work.

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

     High sal 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

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