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
------------------------------- -------- ----
SSN NOT NULL NUMBER(9)
We wish to have the following modifications made in the above table
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,
lname varchar2(20) NOT NULL,
dno INT NOT NULL,
PRIMARY KEY (ssn),
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:
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.
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
Retrieve the name and address of all employees who work for the 'Research' department.
To appreciate the importance of the DISTINCT command, try the following query.
What did you get?
Now try this:
select DISTINCT salary
What is the difference?