Oracle- SQL interview Question Answer Part -5 - ETL- iNFORMATICA DEVELOPER

Friday, July 5, 2019

Oracle- SQL interview Question Answer Part -5

                                                           

www.ktbluehost.com
                         
                                            
          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';

No comments:

Post a Comment