Project 6: Creating Tables, Inserting Data

Objectives

After completing this lab you should be able to:

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 2nd script will insert data and then perform the queries asked for in 5-10. SPOOL the output produced by the 2nd 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)

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

 

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

  1. 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.)
  2. 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

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

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