LAB (2) - DBMS
More on Oracle

Objectives:
To specify and apply queries
To write queries in SQL

In Lab (1), you learned to create all tables for the COMPANY database.  In this lab, you will deal with the constraints.  To begin the lab, first source the .cshrc file, then move to the directory where you have the SQL files, and run sql+.  At the SQL prompt type DESCRIBE EMPLOYEE.  This will display the following information regarding the EMPLOYEE table.
 

SQL> describe employee
 Name                            Null?            Type
 ------------------------------- -------- ----
 FNAME                                              VARCHAR2(20)
 MINIT                                                CHAR(1)
 LNAME                                              VARCHAR2(20)
 SSN                             NOT NULL    NUMBER(9)
 BDATE                                                DATE
 ADDRESS                                          VARCHAR2(50)
 SEX                                                    CHAR(1)
 SALARY                                            NUMBER(8,2)
 SUPERSSN                                        NUMBER(9)
 DNO                                                   NUMBER(2)

We wish to have the following modifications made in the above table definition.
    1) We wish to have FNAME and LNAME such that they do not accept NULL,
    2) We want to set a default department (1) for DNO.

1) There are several ways to do this.  One simple way is to drop the current table and put the new one in place.  But, if you drop the table, you will loose all the data with it.  The better option is to modify the schema alone.  In order to do this, you will use the ALTER, MODIFY and ADD commands.

SQL>  ALTER TABLE employee MODIFY FNAME NOT NULL;

Use a similar command to make the change for LNAME.

Use DESCRIBE to make sure the new change is in place.

2) Changing the default value of the department to (1).
SQL> ALTER TABLE employee MODIFY (DNO DEFAULT 1);

Now, let's try another set of modifications.  In order to do this, we will drop all tables and we will recreate them again.  To drop all your tables, run the following script:

drop table dependent;
drop table dept_locations;
drop table works_on;
drop table project;
drop table employee cascade constraints;
drop table department
/

We actually are going to make changes in employee table only but that table is referenced by other tables.  Thus, for now we will drop all tables and recreate them again with the new employee table in place.  Note the way we have dropped the employee table.  We have used cascade constraints option.  If we do not use that due to references made by the department table (other tables are already deleted) we cannot remove the employee table.

Here is the new definition for EMPLOYEE table:
CREATE TABLE employee (
  fname varchar2(20) NOT NULL,
  minit char,
  lname varchar2(20) NOT NULL,
  ssn number(9),
  bdate date,
  address varchar2(50),
  sex char,
  salary number(8,2),
  superssn number(9),
  dno INT NOT NULL,
  CONSTRAINT  employee_ssn_pk
   PRIMARY KEY (ssn),
  CONSTRAINT employee_superssn_fk
  FOREIGN KEY (superssn) REFERENCES employee(ssn)
);

Replace the employee creation part in your SQL file with the new one given above, save the file and create all tables again.  Now view the description of the employee table.  What are the changes?

Now that you have created all tables, we will populate them.  Copy the pop_comp.sql file from my directory:  At SQL prompt type:
SQL> !cp ~rt/oracle/pop_comp.sql  .     (don't forget the . ), then run it
SQL> @ pop_comp

ADDING A NEW COLUMN
To add the new column, we will use the ALTER command again.  Suppose we are adding a new column (job) to the employee's table.

Before you execute the following command, see if there is anything wrong with it.

SQL> ALTER TABLE employee ADD JOB VARCHAR(12) NOT NULL;

Can we have the NOT NULL constraint for the new attribute? Explain your answer and write the correct command..

Now execute the corrected version, then use the describe command to make sure the new column is created.   If you run:
SELECT JOB
FROM EMPLOYEE;

You will see that there is nothing there.  From now on, every tuple that you wish to add will include the job column.

Insert a new tuple (make up a tuple).  First write your command below, then execute it.
 

Update one or more of the elements of the newly added tuple (make up something).  First write your command below, then execute it.
 

By now, you know that you may violate the integrity constraint during the Update and Insert.

ALIASING
Consider the following query:

select E.fname, S.fname
from employee E, employee S
where E.superssn = S.ssn;

What did we do here?
We actually have used two copies of employee table (aliases) as E and S.  We have used the aliases of employee table to carry out the query.

Practice: Use aliasing to write the following query:
Retrieve the name and address of all employees who work for the 'Research' department.
 
 

DISTINCT
To appreciate the importance of the DISTINCT command, try the following query.
select salary
from employee;

What did you get?

Now try this:
select DISTINCT salary
from employee;

What is the difference?