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

Use the SQL Command Line in Oracle to create the following indexes and views:

1. Create an index on the employee name column of the employee table.

2. Create an index on the combination of department name and location columns of the employee table.

3. Create a view Emp_vw containing the employee number, the employee name, the department number, and salary. Ensure that the view allows users only to view, and not to update, the data. Use the view in a select command to display all data.

4. Create a view emp_dept_view containing the employee name, job description, hiredate, and department name columns from the employee and department tables. Use the view in a select command to list the name, job, and hiredate for all employees in the ACCOUNTING department.

For the following commands you must be logged in as user system. You will need to do some research on the commands CREATE USER; GRANT CREATE SESSION; GRANT CREATE..; GRANT ALTER ., GRANT SELECT.; REVOKE ; and EXECUTE ..

5. Create two database users: ? The first is a concatenation of your first and last name (e.g. johndoe). ? The second is a concatenation of your instructors first and last name (e.g. sallysmith)

6. Assign the two users privileges to connect to the database.

7. Assign the user with your first and last name the privilege to select data from the employees table.

8. Assign the user with your instructors first and last name all privileges to the Departments table.

9. Assign the user with your first and last name the privilege to execute any procedure.

10. Take away the instructors privilege to execute any SQL commands on the Departments table.