-- create tables
create table departments (
    id                             number generated by default on null as identity 
                                   constraint departments_id_pk primary key,
    name                           varchar2(255 char) not null,
    location                       varchar2(4000 char),
    country                        varchar2(4000 char)
)
;

create table employees (
    id                             number generated by default on null as identity 
                                   constraint employees_id_pk primary key,
    department_id                  number
                                   constraint employees_department_id_fk
                                   references departments on delete cascade,
    name                           varchar2(50 char) not null,
    email                          varchar2(255 char),
    cost_center                    number,
    date_hired                     date,
    job                            varchar2(255 char)
)
;

-- table index
create index employees_i1 on employees (department_id);


-- triggers
create or replace trigger employees_biu
    before insert or update 
    on employees
    for each row
begin
    :new.email := lower(:new.email);
end employees_biu;
/


-- create views
create or replace view emp_v as 
select 
    departments.id                                     department_id,
    departments.name                                   department_name,
    departments.location                               location,
    departments.country                                country,
    employees.id                                       employee_id,
    employees.name                                     employee_name,
    employees.email                                    email,
    employees.cost_center                              cost_center,
    employees.date_hired                               date_hired,
    employees.job                                      job
from 
    departments,
    employees
where
    employees.department_id(+) = departments.id
/

-- load data
 
insert into departments (
    id,
    name,
    location,
    country
) values (
    1,
    'EMEA Sales',
    'Tanquecitos',
    'United States'
);

insert into departments (
    id,
    name,
    location,
    country
) values (
    2,
    'Operations',
    'Sugarloaf',
    'United States'
);

commit;

alter table departments
modify id generated always as identity restart start with 3;
 
-- load data
-- load data
 
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    1,
    2,
    'Gricelda Luebbers',
    'gricelda.luebbers@aaab.com',
    30,
    sysdate - 87,
    'Evengilist'
);

insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    2,
    2,
    'Dean Bollich',
    'dean.bollich@aaac.com',
    7,
    sysdate - 86,
    'Sustaining Engineering'
);

insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    3,
    2,
    'Milo Manoni',
    'milo.manoni@aaad.com',
    94,
    sysdate - 22,
    'Consultant'
);

insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    4,
    2,
    'Laurice Karl',
    'laurice.karl@aaae.com',
    74,
    sysdate - 50,
    'Web Developer'
);

commit;

alter table employees
modify id generated always as identity restart start with 5;
 
 
-- Generated by Quick SQL Monday March 13, 2023  23:38:46
 
/*
departments /insert 2
   name /nn
   location
   country
   employees /insert 4
      name /nn vc50
      email /lower
      cost center num
      date hired
      job vc255

view emp_v departments employees

# settings = { semantics: "CHAR", language: "EN", APEX: true }
*/