Oracle Sql - Questions and Answers
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