Oracle Sql - questions and answers
45. Write a query for the HR department to produce the addresses of all the
departments. Use the EMP and DEPT tables.
Show the EMPNO, ENAME, SAL, DNAME and LOC in the output. Use a
NATURAL JOIN to produce the
results.
SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP NATURAL JOIN DEPT; EMPNO ENAME SAL DNAME LOC ---------- ---------- ---------- -------------- ------------- 7839 KING 5500 ACCOUNTING NEW YORK 7698 BLAKE 3000 SALES CHICAGO 7782 CLARK 2450 ACCOUNTING NEW YORK 7566 JONES 2975 RESEARCH DALLAS 7788 SCOTT 3000 RESEARCH DALLAS 7902 FORD 3000 RESEARCH DALLAS 7369 SMITH 800 RESEARCH DALLAS 7499 ALLEN 1900 SALES CHICAGO 7521 WARD 1250 SALES CHICAGO 7654 MARTIN 1250 SALES CHICAGO 7844 TURNER 1500 SALES CHICAGO 7876 ADAMS 1100 RESEARCH DALLAS 7900 JAMES 950 SALES CHICAGO 7934 MILLER 1300 ACCOUNTING NEW YORK
46. The HR department needs a report of all employees. Write a query to
display the JOB, MGR, SAL,
COMM, DNAME of employees whose JOB is SALESMAN.
SELECT JOB, MGR, SAL, COMM, DNAME FROM EMP JOIN DEPT USING (DEPTNO) WHERE JOB='SALESMAN'; JOB MGR SAL COMM DNAME --------- ---------- ---------- ---------- -------------- SALESMAN 7698 1500 0 SALES SALESMAN 7698 1250 1400 SALES SALESMAN 7698 1250 500 SALES SALESMAN 7698 1900 300 SALES
47. The HR department needs a report of employees in LOC DALLAS.
Display the ENAME, JOB, DEPTNO,
DNAME for all employees who work in DALLAS.
SELECT ENAME, JOB, DEPTNO, DNAME FROM EMP JOIN DEPT USING (DEPTNO) WHERE LOC='DALLAS'; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- JONES MANAGER 20 RESEARCH SCOTT ANALYST 20 RESEARCH FORD ANALYST 20 RESEARCH SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH
48. Create a report to display employees ename and employee number along with
their manager's name and manager number. Label the
columns Employee, Emp#, Manager and Mgr# respectively.
SELECT E.ENAME "EMPLOYEE", E.EMPNO "EMP#", D.ENAME "MANAGER", D.EMPNO "MGR#" FROM EMP E JOIN EMP D ON (E.MGR = D.EMPNO); EMPLOYEE EMP# MANAGER MGR# ---------- ---------- ---------- ---------- FORD 7902 JONES 7566 SCOTT 7788 JONES 7566 ALLEN 7499 BLAKE 7698 JAMES 7900 BLAKE 7698 TURNER 7844 BLAKE 7698 MARTIN 7654 BLAKE 7698 WARD 7521 BLAKE 7698 MILLER 7934 CLARK 7782 ADAMS 7876 SCOTT 7788 BLAKE 7698 KING 7839 CLARK 7782 KING 7839 JONES 7566 KING 7839 SMITH 7369 FORD 7902
49. Modify the previous query to display all employees including King, who has
no manager. Order the results by the employee number.
SELECT E.ENAME "EMPLOYEE", E.EMPNO "EMP#", D.ENAME "MANAGER", D.EMPNO "MGR#" FROM EMP E LEFT OUTER JOIN EMP D ON (E.MGR = D.EMPNO) ORDER BY E.EMPNO; EMPLOYEE EMP# MANAGER MGR# ---------- ---------- ---------- ---------- SMITH 7369 FORD 7902 ALLEN 7499 BLAKE 7698 WARD 7521 BLAKE 7698 JONES 7566 KING 7839 MARTIN 7654 BLAKE 7698 BLAKE 7698 KING 7839 CLARK 7782 KING 7839 SCOTT 7788 JONES 7566 KING 7839 TURNER 7844 BLAKE 7698 ADAMS 7876 SCOTT 7788 JAMES 7900 BLAKE 7698 FORD 7902 JONES 7566 MILLER 7934 CLARK 7782
50. The HR department needs a report on job grades and salaries. To
familiarize yourself with the SALGRADE table, first show the structure of the
SALGRADE table. Then create a query that displays the name, job, department
name, salary and grade for all employees.
DESC SALGRADE; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ GRADE NUMBER LOSAL NUMBER HISAL NUMBER SELECT ENAME, JOB, DNAME, SAL, GRADE FROM EMP JOIN DEPT USING (DEPTNO) JOIN SALGRADE ON (SAL BETWEEN LOSAL AND HISAL); ENAME JOB DNAME SAL GRADE ---------- --------- -------------- ---------- ---------- SMITH CLERK RESEARCH 800 1 JAMES CLERK SALES 950 1 ADAMS CLERK RESEARCH 1100 1 MARTIN SALESMAN SALES 1250 2 WARD SALESMAN SALES 1250 2 MILLER CLERK ACCOUNTING 1300 2 TURNER SALESMAN SALES 1500 3 ALLEN SALESMAN SALES 1900 3 CLARK MANAGER ACCOUNTING 2450 4 JONES MANAGER RESEARCH 2975 4 FORD ANALYST RESEARCH 3000 4 SCOTT ANALYST RESEARCH 3000 4 BLAKE MANAGER SALES 3000 4 KING PRESIDENT ACCOUNTING 5500 5 14 rows selected.
51. Display ENAME, DNAME of all the employees. Also display those department
name which do not have any employees working.
SELECT ENAME, DNAME FROM EMP RIGHT OUTER JOIN DEPT USING (DEPTNO); ENAME DNAME ---------- -------------- KING ACCOUNTING BLAKE SALES CLARK ACCOUNTING JONES RESEARCH SCOTT RESEARCH FORD RESEARCH SMITH RESEARCH ALLEN SALES WARD SALES MARTIN SALES TURNER SALES ADAMS RESEARCH JAMES SALES MILLER ACCOUNTING HR OPERATIONS PAYROLL 17 rows selected.
52. The HR department needs to find the names and hire dates for all employees
who were hired before their managers, along with their managers names and hire
dates.
SELECT E.ENAME, E.HIREDATE, M.ENAME, M.HIREDATE FROM EMP E LEFT OUTER JOIN EMP M ON E.HIREDATE < M.HIREDATE WHERE E.MGR = M.EMPNO; ENAME HIREDATE ENAME HIREDATE ---------- --------- ---------- --------- ALLEN 20-FEB-81 BLAKE 01-MAY-81 WARD 22-FEB-81 BLAKE 01-MAY-81 BLAKE 01-MAY-81 KING 17-NOV-81 CLARK 09-JUN-81 KING 17-NOV-81 JONES 02-APR-81 KING 17-NOV-81 SMITH 17-DEC-80 FORD 03-DEC-81 6 rows selected
53. Display the EMPNO, ENAME, DNAME, LOC of those employees who are working as
CLERK. Use the USING clause.
SELECT EMPNO, ENAME, DNAME, LOC FROM EMP JOIN DEPT USING (DEPTNO) WHERE JOB='CLERK'; EMPNO ENAME DNAME LOC ---------- ---------- -------------- ------------- 7934 MILLER ACCOUNTING NEW YORK 7876 ADAMS RESEARCH DALLAS 7369 SMITH RESEARCH DALLAS 7900 JAMES SALES CHICAGO
54. Display the ENAME, SAL, MGR, DNAME of all employees whose salary is more
than 2000. Use the ON clause.
SELECT E.ENAME, E.SAL, E.MGR, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.SAL>2000; ENAME SAL MGR DNAME ---------- ---------- ---------- -------------- CLARK 2450 7839 ACCOUNTING KING 5500 ACCOUNTING FORD 3000 7566 RESEARCH SCOTT 3000 7566 RESEARCH JONES 2975 7839 RESEARCH BLAKE 3000 7839 SALES 6 rows selected.
55. Display the EMPNO, ENAME, JOB, DEPTNO, DNAME, LOC of employees. Use LEFT
OUTER JOIN.
SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME, D.LOC FROM EMP E LEFT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO); EMPNO ENAME JOB DEPTNO DNAME LOC ---------- ---------- --------- ---------- -------------- ------------- 7934 MILLER CLERK 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 10 ACCOUNTING NEW YORK 7876 ADAMS CLERK 20 RESEARCH DALLAS 7369 SMITH CLERK 20 RESEARCH DALLAS 7902 FORD ANALYST 20 RESEARCH DALLAS 7788 SCOTT ANALYST 20 RESEARCH DALLAS 7566 JONES MANAGER 20 RESEARCH DALLAS 7900 JAMES CLERK 30 SALES CHICAGO 7844 TURNER SALESMAN 30 SALES CHICAGO 7654 MARTIN SALESMAN 30 SALES CHICAGO 7521 WARD SALESMAN 30 SALES CHICAGO 7499 ALLEN SALESMAN 30 SALES CHICAGO 7698 BLAKE MANAGER 30 SALES CHICAGO 14 rows selected.
56. Display the ENAME, DNAME of employees. Use the RIGHT OUTER JOIN.
SELECT ENAME, DNAME FROM EMP RIGHT OUTER JOIN DEPT USING (DEPTNO); ENAME DNAME ---------- -------------- KING ACCOUNTING BLAKE SALES CLARK ACCOUNTING JONES RESEARCH SCOTT RESEARCH FORD RESEARCH SMITH RESEARCH ALLEN SALES WARD SALES MARTIN SALES TURNER SALES ADAMS RESEARCH JAMES SALES MILLER ACCOUNTING HR OPERATIONS PAYROLL 17 rows selected.
57. Display the EMPNO, DNAME, LOC of all employees. Use FULL OUTER JOIN.
SELECT EMPNO,DNAME, LOC FROM EMP FULL OUTER JOIN DEPT USING (DEPTNO); EMPNO DNAME LOC ---------- -------------- ------------- 7839 ACCOUNTING NEW YORK 7698 SALES CHICAGO 7782 ACCOUNTING NEW YORK 7566 RESEARCH DALLAS 7788 RESEARCH DALLAS 7902 RESEARCH DALLAS 7369 RESEARCH DALLAS 7499 SALES CHICAGO 7521 SALES CHICAGO 7654 SALES CHICAGO 7844 SALES CHICAGO 7876 RESEARCH DALLAS 7900 SALES CHICAGO 7934 ACCOUNTING NEW YORK HR HYD OPERATIONS BOSTON PAYROLL PUNE 17 rows selected.
58. Create a report that displays EMPNO, ENAME, DEPTNO of employees who work
with that Employee ( Nested Sub Query ).
i) If it is based on department number, then use this
query:
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO=&EMPNUM); Enter value for empnum: 7788 old 1: SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO=&EMPNUM) new 1: SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO=7788) EMPNO ENAME DEPTNO ---------- ---------- ---------- 7566 JONES 20 7788 SCOTT 20 7902 FORD 20 7369 SMITH 20 7876 ADAMS 20
( OR )
ii) If it is based on job, then use this query:
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=&EMPNUM); Enter value for empnum: 7788 old 1: SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=&EMPNUM) new 1: SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=7788) EMPNO ENAME DEPTNO ---------- ---------- ---------- 7788 SCOTT 20 7902 FORD 20
59. Display the employees who earn more than the average salary of EMP table.
( Nested Sub Query ).
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP); 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 6 rows selected.