-- DESCRIPTION -- This script creates the SQL*Plus demonstration tables in the -- current schema. It should be STARTed by each user wishing to -- access the tables. SET TERMOUT ON PROMPT Building demonstration tables. Please wait. SET TERMOUT OFF DROP TABLE EMPLOYEES; DROP TABLE DEPARTMENTS; DROP TABLE BONUS; DROP TABLE SALGRADE; CREATE TABLE EMPLOYEES (EMPLOYEE_ID NUMBER(2) NOT NULL, EMPLOYEE_NAME VARCHAR2(20), JOB_ID VARCHAR2(2), JOB_DESCRIPTION VARCHAR2(20), HOD NUMBER(2), HIREDATE DATE, SALARY NUMBER(10, 2), DEPARTMENT_ID NUMBER(2)); INSERT INTO EMPLOYEES VALUES (1, 'SMITH', 'J1', 'CLERK', 5, TO_DATE('17-DEC-2007', 'DD-MON-YYYY'), 5800, 20); INSERT INTO EMPLOYEES VALUES (2, 'ALLEN', 'J2', 'SALESMAN', 3, TO_DATE('20-FEB-2008', 'DD-MON-YYYY'), 7600, 30); INSERT INTO EMPLOYEES VALUES (3, 'WARD', 'J2', 'SALESMAN', 5, TO_DATE('22-FEB-2008', 'DD-MON-YYYY'), 8250, 30); INSERT INTO EMPLOYEES VALUES (4, 'JONES','J3', 'MANAGER', 7, TO_DATE('2-APR-2008', 'DD-MON-YYYY'), 4900, 20); INSERT INTO EMPLOYEES VALUES (5, 'JACK', 'J2','SALESMAN', 7, TO_DATE('28-SEP-2008', 'DD-MON-YYYY'), 6700, 10); INSERT INTO EMPLOYEES VALUES (6, 'BLAKE', 'J3', 'MANAGER', 7, TO_DATE('1-MAY-2008', 'DD-MON-YYYY'), 12850,30); INSERT INTO EMPLOYEES VALUES (7, 'CLARK', 'J3', 'MANAGER', NULL, TO_DATE('9-JUN-2008', 'DD-MON-YYYY'), 22450, 10); INSERT INTO EMPLOYEES VALUES (8, 'SCOTT', 'J4', 'ANALYST', 5, TO_DATE('09-DEC-2008', 'DD-MON-YYYY'), 13000,20); INSERT INTO EMPLOYEES VALUES (9, 'TURNER', 'J2','SALESMAN', 3, TO_DATE('8-SEP-2007', 'DD-MON-YYYY'), 5500, 30); INSERT INTO EMPLOYEES VALUES (10, 'ADAMS', 'J1','CLERK', 5, TO_DATE('12-JAN-2007', 'DD-MON-YYYY'), 9100, 20); CREATE TABLE DEPARTMENTS (DEPARTMENT_ID NUMBER(2), DEPARTMENT_NAME VARCHAR2(20), LOCATION VARCHAR2(20) ); INSERT INTO DEPARTMENTS VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPARTMENTS VALUES (20, 'PRODUCTION','MINNESOTA'); INSERT INTO DEPARTMENTS VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPARTMENTS VALUES (40, 'OPERATIONS', 'BOSTON'); CREATE TABLE BONUS (ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER, COMM NUMBER); CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE VALUES (1, 3000, 8000); INSERT INTO SALGRADE VALUES (2, 8001, 14000); INSERT INTO SALGRADE VALUES (3, 14001, 20000); INSERT INTO SALGRADE VALUES (4, 20001, 25000); INSERT INTO SALGRADE VALUES (5, 25001, 30000); COMMIT; SET TERMOUT ON PROMPT Demonstration table build is complete.

1. Use a join to display the employee identification number, employee name, job description and department name of all employees of the SALES department.

2. Use a subquery to display employee identification number, employee name, job description and department name of all employees of the SALES department.

3. Use a subquery to display the details of the employees who have been hired after the employee JONES was hired.

4. Use a subquery to display the employee name, job description, and salary of all employees who have the same job title as you (E.g. the employee you created with your name).

5. Display the average, maximum, and minimum salary of all the employees.

6. For each department, display the department number, the average, maximum, and minimum salary earned by the employees of the department. Sort the data by maximum salary.

7. Find the lowest salary earned by any employee in each department. Display only those departments where the minimum basic salary is less than $8,000. Display both the department ID and highest salary.

8. Find the total number of employees working in each department. Display only those departments that have a count greater than 3.

9. Display the HOD and number of employees working for each HOD.

10. Display the department name and average salary of all the employees in each department. Sort the output by average salary.