Oracle Sql - Questions and Answers 5

 


Oracle Sql - Questions and Answers


                           Back                                                                                      Next 

60. Display the ENAME, JOB who are Managers ( Use Exists Operator )

SELECT E.ENAME,E.JOB FROM EMP E WHERE EXISTS (SELECT E1.ENAME FROM EMP E1 WHERE E.EMPNO = E1.MGR);

ENAME      JOB
---------- ---------
JONES      MANAGER
BLAKE      MANAGER
CLARK      MANAGER
SCOTT      ANALYST
KING       PRESIDENT
FORD       ANALYST

6 rows selected.



61. Display the employees who earn less than the least salary of DEPTNO 10. (Use ALL Operator )

SELECT * FROM EMP WHERE SAL < ALL (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=10);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30



62. Display the employees who have the same DEPTNO and MGR of a given employee, excluding that employee (Use Nested Sub Query )

 SELECT * FROM EMP WHERE (DEPTNO,MGR) IN (SELECT DEPTNO,MGR FROM EMP WHERE EMPNO=&EMPLOYEENUMBER);
Enter value for employeenumber: 7844
old   1: SELECT * FROM EMP WHERE (DEPTNO,MGR) IN (SELECT DEPTNO,MGR FROM EMP WHERE EMPNO=&EMPLOYEENUMBER)
new   1: SELECT * FROM EMP WHERE (DEPTNO,MGR) IN (SELECT DEPTNO,MGR FROM EMP WHERE EMPNO=7844)

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1900        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30



63. Write a query that displays the employee number and name of all employees who work in a department with any employee whose name contains a R.

SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = ANY (SELECT DISTINCT DEPTNO FROM EMP WHERE ENAME LIKE '%R%');
     EMPNO ENAME
---------- ----------
      7934 MILLER
      7782 CLARK
      7839 KING
      7876 ADAMS
      7369 SMITH
      7902 FORD
      7788 SCOTT
      7566 JONES
      7900 JAMES
      7844 TURNER
      7654 MARTIN
      7521 WARD
      7499 ALLEN
      7698 BLAKE

14 rows selected.



64. The HR department needs a report that displays the ename, deptno, job of all employees whose work in NEW YORK. 

SELECT ENAME, DEPTNO, JOB FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

ENAME          DEPTNO JOB
---------- ---------- ---------
KING               10 PRESIDENT
CLARK              10 MANAGER
MILLER             10 CLERK



65. Modify the query so that the user is prompted for a LOC.

SELECT ENAME, DEPTNO, JOB FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC='&LOCATION');
Enter value for location: DALLAS
old   1: SELECT ENAME, DEPTNO, JOB FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC='&LOCATION')
new   1: SELECT ENAME, DEPTNO, JOB FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC='DALLAS')

ENAME          DEPTNO JOB
---------- ---------- ---------
JONES              20 MANAGER
SCOTT              20 ANALYST
FORD               20 ANALYST
SMITH              20 CLERK
ADAMS              20 CLERK



66. Create a report for HR that displays the name and salary of every employee who reports to king.

 SELECT ENAME, SAL FROM EMP WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME='KING');

ENAME             SAL
---------- ----------
BLAKE            3000
CLARK            2450
JONES            2975



67. Write a query to display all the employees working with JAMES.

SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='JAMES');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       3000                    30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1900        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

                                                                    ( OR )

SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME='JAMES');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10



68. Display all the employees who earn less than  the average salaries of their respective departments.

SELECT * FROM EMP E WHERE SAL < (SELECT AVG(E1.SAL) FROM EMP E1 WHERE E.DEPTNO = E1.DEPTNO);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

8 rows selected.



69. Write a query to display the LOC and average salary of Each location.

SELECT LOC, (SELECT AVG(SAL) FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO ) "AVERAGE SALARY" FROM DEPT;

   LOC           AVERAGE SALARY
------------- --------------
NEW YORK          3083.33333
DALLAS                  2175
CHICAGO           1641.66667
BOSTON
HYD
PUNE



70. Write a query to display the employees with the least N salaries. Use In-Line Views.

SELECT * FROM (SELECT * FROM EMP ORDER BY SAL ASC) WHERE ROWNUM <= &N;
Enter value for n: 3
old   1: SELECT * FROM (SELECT * FROM EMP ORDER BY SAL ASC) WHERE ROWNUM <= &N
new   1: SELECT * FROM (SELECT * FROM EMP ORDER BY SAL ASC) WHERE ROWNUM <= 3

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20



71. Display the last N rows from the employees table (Use Correlated Sub Queries).

SELECT * FROM EMP MINUS SELECT * FROM EMP WHERE ROWNUM <= (SELECT COUNT(*)-&N FROM EMP);
Enter value for n: 5
old   1: SELECT * FROM EMP MINUS SELECT * FROM EMP WHERE ROWNUM <= (SELECT COUNT(*)-&N FROM EMP)
new   1: SELECT * FROM EMP MINUS SELECT * FROM EMP WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM EMP)

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10



72. Display the employees from employees table and sort only employees working in DALLAS. (Scalar Sub Query).

select * from emp order by (select deptno from dept where loc='dallas');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5500                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       3000                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1900        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

14 rows selected.



73. Display the employees who earn a salary less than avgsal of their respective department. Also display the avgsal. ( Use Inline Views).

SELECT * FROM EMP E, (SELECT DEPTNO, AVG(SAL) "AVERAGE_SALARY" FROM EMP GROUP BY DEPTNO) E1 WHERE E.DEPTNO=E1.DEPTNO AND E.SAL < E1.AVERAGE_SALARY;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     DEPTNO AVERAGE_SALARY
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- --------------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10         10     3083.33333
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20         20           2175
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30         30     1641.66667
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30         30     1641.66667
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30         30     1641.66667
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20         20           2175
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30         30     1641.66667
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10         10     3083.33333

8 rows selected.




74. Write a query that display the LOC of  those DEPTS that have a sum of sal less than the avgsal of all employees in DEPT table. (Use WITH Clause).

with deptot as (select sum(sal) as depsal from emp group by deptno)
  2  select loc from dept where deptno IN (select deptno from emp having sum(sal) < (select avg(depsal) from deptot) group by deptno);

LOC
-------------
CHICAGO
NEW YORK