output.sql

-- create tables
create table Departement (
	id		number generated by default on null as identity constraint Departement_id_pk primary key constraint Departement_id_pk primary key,
	name		varchar(20 char) constraint Departement_name_cc check (name in ('IT','Finance','Accounting'))
)
;

create table Employee (
	id		number generated by default on null as identity constraint Employee_id_pk primary key constraint Employee_id_pk primary key,
	name		varchar(20 char),
	CIN		varchar(10 char) constraint Employee_CIN_unq unique,
	hireDate		date,
	salary		integer,
	departement_id		number constraint Employee_departement_id_fk references Departement on delete cascade
)
;

--comments
comment on column Employee.CIN is 'numéro de la carte d'identité nationale ';
create table Project (
	id		number generated by default on null as identity constraint Project_id_pk primary key constraint Project_id_pk primary key,
	description		varchar(200 char),
	Employee_id		number constraint Project_Employee_id_fk references Employee on delete cascade
)
;


-- table index

-- create views
create or replace view V1 as 
select
	Employee.name						Employee_name,
	Employee.CIN						Employee_CIN,
	Employee.hireDate						Employee_hireDate,
	Employee.salary						Employee_salary,
	Employee.departement_id						Employee_departement_id,
	Departement.name						Departement_name
from 
	Employee,	Departement
where 

/

-- load data
select
	name,
	CIN,
	hireDate,
	salary,
	departement_id
from Employee;