-- 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.

Enter SQL commands to display the following information:

5. The employee identification number, employee name, and department name for all employees whose identification number is less than 33 or greater than 77

6. The employee name, hiredate, and salary for all employees whose salary is not in the range of $8,000$12,900

7. The employee name, job identification number, job description, and joining date of employees who have been hired between December 31, 2007 and August 01, 2008 or those hired today.

8. The complete employee detail for all employees whose last names start with J