ORACLE QUERIES

>>PREVIOUS>>

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

>>NEXT>>