Project 6: Creating Tables, Inserting Data

Objectives

After completing this lab you should be able to:

Create a table.
Create table constraints.
Insert data into your table.
Update data in your table.
Write hierarchical queries.

Due for Project 6

Turn in two SQL files and one output file. Questions 1- 3 (which create your tables) will be saved in one file, while the 2^nd script will insert data and then perform the queries asked for in 5-10. SPOOL the output produced by the 2^nd script only. Upload Project6_1.sql, Project6_2.sql, Project 6_2.lst into the Drop Box by 11:55 PM on its due date.

Project 6 Assignment, Script 1 Steps

1. Create a table for employees (EMPLOYEE). Create the following columns for this table. Define employee_id as a primary key.

Employee_id      varchar2(8) not null
last_name        varchar2(40)
first_name          varchar2(40)
middle_initial   varchar2(1)
dept_id          varchar2(4)
title            varchar2(40)
supervisor_id    varchar2(8)

Create a department table (DEPARTMENT) for our company. This table will have the following columns. Define dept_id as a primary key.

Dept_id varchar2(4) not null
Division_name varchar2(20)
Department_name varchar2(20)

Now add referential integrity or foreign key constraints to EMPLOYEE. Since supervisors must be employees, the supervisor ID column will reference the employee ID column. And since we have a table for departments, employee.dept_id will reference the department.dept_id. Name these foreign keys FK_emp_emp and FK_emp_dept.

Project 6 Assignment, Script 2 Steps

Remove all the data from your EMPLOYEE and DEPARTMENT tables. (The first time you run the script of course no data will be deleted, but on subsequent runs you will need an empty table.)
Insert the following data into the department table.

Dept_id	Division_name	Department_Name
EBX	Pharma	R&D
CP1	Corporate	Finance
CP2	Corporate	Marketing

NOTE: Inserting the value "R&D" will cause some problems. The & sign indicates to SQL*Plus that the following string is a SQL*Plus variable. You need to define an escape character temporarily, % for example. So for example a portion of your final lab5part2.sql file would look like this:

           SET ESCAPE %
     INSERT .... VALUES (...., 'R%&D'’)
     /
     SET ESCAPE OFF

Now insert the following data into the employee table.

Emp ID	Employee Name	Dept.	Title	Supervisor
EB001	Richard Abbott	EBX	Director
EB002	Fred Johnson	EBX	Manager	EB001
EB103	Jan Searle	EBX	Technician	EB002
EB104	Fred W. Lambert		Technician	EB002
CP001	Judith H. Roche	CP1	Director
CP002	Smithy K. Glaxo	CP2	Director
CP003	Amerigus H. Product	CP1	Manager	CP001
CP004	Helmut Bayer	CP1	Dept. Head	CP003
CP005	Trent Merck	CP1	Team Leader	CP004
CP006	Ted Baxter	CP1	clerk	CP005
CP007	John Underling	CP1	clerk	CP005
CP008	Susan H. Helper		AA	CP003
CP009	Joe Dilbert	CP1	clerk	CP005

NOTE: some dept. values intentionally left null.

Insert all the workers from the WORKER table using an INSERT INTO table (columns,...) SELECT statement. For employee_id, be creative and derive an employee_id from data found within the worker table (eg. String concatenates, numeric functions, etc.). Assign all these workers into department CP2, with title ‘Salesperson’ and supervisor = CP002.

Here is one way to implement #7. There would be several ways of doing essentially the same thing. Note however that some "names" in the worker table are not just first and last name. So this would not be a complete solution but good enough for lab5.

INSERT INTO EMPLOYEE (employee_id, first_name,

                      last_name, dept_id, title, supervisor_id)

SELECT substr(name,1,3)||

       ltrim(to_char(nvl(age,1),'009')) as employee_id,

       initcap(substr(name,1,instr(name,' ',1)-1)) as first_name,

       initcap(substr(name,instr(name,' ',1)+1)) as last_name,

       'CP2', 'Salesperson', 'CP002'

  FROM WORKER;

8. Transactional Control

a) Commit your changes to the database. Also select COUNT(*) from employee and department.

b) Now delete all rows from EMPLOYEE. Select COUNT(*) from employee. There should be no rows.

c) Rollback the deletes from EMPLOYEE. Select COUNT(*) from employee. The rows should all be back.

9. Smithy wants to change his name. Update his last name from Glaxo to Beecham. Select just this row to show that it was changed.

10. Your boss wants some reports on the company. Write the following queries:

a) All employee names (formatted as last, first initial) with their division and department if any. (See Outer Joins).

b) Show all employee names in one column and their manager’s name in the second column. Fomat the name as last_name, first_name. If the employee has no manager, print ‘none’ instead (DECODE).

c) Starting with each Director print their name (last_name, first_name middle_initial) and title. Indent the name by 4 spaces and list any people they manage using the same format as directors. Indent the name by 4 more spaces and list any people they supervise, and so on and so on. This will produce a hierarchical listing of all employees and who they report to. (See CONNECT BY / START WITH).

d) Similar to previous question, write the same query but also show the employee’s department. Showing department ID is easy, show the department’s name.