linking

Please can you help:

+--------------------------------------+
|              DEPARTMENT              |
+----------+----------------+----------+
| LOCATION |  DESCRIPTION   |  DEPT_NO |
+----------+----------------+----------+
| Bedrock  | Administration |     1    |
| Bedrock  | Quarry         |     2    |
| Redcliff | Stockpile      |     3    |
+----------+----------------+----------+

+-----------------------------+
|         EMPLOYEE            |
+---------+------------+------+
| EMPL_ID | NAME_LAST  | DEPT |
+---------+------------+------+
|    1    | Slate      |  1   |
|    4    | Flintstone |  2   |
|    5    | Rubble     |  2   |
|    7    | Rockhead   |  3   |
|   11    | Gravel     |  1   |
+---------+------------+------+

create sequence increment start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;

create table DEPARTMENT (
LOCATION varchar(30),
DESCRIPTION varchar(30),
DEPT_NO int unique not null default nextval('increment') primary key
);

create table EMPLOYEE (
EMPL_ID int unique not null primary key,
NAME_LAST varchar(20),
DEPT int references DEPARTMENT (DEPT_NO)
);

Insert into DEPARTMENT values ('Bedrock','Administration');

Insert into Employee values ('1','Slate','????');

How do I replace ???? to automatically get the DEPT_NO value?

e.g. In theory I need to replace ???? with (select DEPT_NO from DEPARTMENT where location = 'Administration' and
DESCRIPTION= 'Bedrock';) but I am not sure how to do this all in 1 statement. 

The DEPT_NO is automatically created if no value is given, which is why I also need to create the DEPT relation
automatically.

Thanks,
Si

_____________________________________________________________
Pick up your email anywhere in the world ---> http://www.remail.net