-- create tables
create table departments (
    id                             number generated by default on null as identity 
                                   constraint departments_id_pk primary key,
    name                           varchar2(255 char),
    location                       varchar2(255 char),
    country                        varchar2(255 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(255 char),
    email                          varchar2(255 char),
    job                            varchar2(255 char),
    hiredate                       date
)
;

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

create table skills (
    id                             number generated by default on null as identity 
                                   constraint skills_id_pk primary key,
    employee_id                    number
                                   constraint skills_employee_id_fk
                                   references employees on delete cascade,
    skill                          varchar2(10 char),
    proficiency                    number constraint skills_proficiency_ck
                                   check (proficiency in (1,2,3,4,5))
)
;

-- table index
create index skills_i1 on skills (employee_id);

-- comments
comment on column skills.proficiency is 'with 1 being a novice and 5 being a guru';

-- 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,
    'Real World Testing',
    '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,
    job,
    hiredate
) values (
    1,
    2,
    'Gricelda Luebbers',
    'gricelda.luebbers@aaab.com',
    'Business Applications',
    sysdate - 65
);

insert into employees (
    id,
    department_id,
    name,
    email,
    job,
    hiredate
) values (
    2,
    1,
    'Dean Bollich',
    'dean.bollich@aaac.com',
    'Manufacturing and Distribution',
    sysdate - 95
);

insert into employees (
    id,
    department_id,
    name,
    email,
    job,
    hiredate
) values (
    3,
    2,
    'Milo Manoni',
    'milo.manoni@aaad.com',
    'Facilities Manager',
    sysdate - 2
);

insert into employees (
    id,
    department_id,
    name,
    email,
    job,
    hiredate
) values (
    4,
    1,
    'Laurice Karl',
    'laurice.karl@aaae.com',
    'User Experience Manager',
    sysdate - 51
);

commit;

alter table employees
modify id generated always as identity restart start with 5;
 
-- load data
 
insert into skills (
    id,
    employee_id,
    skill,
    proficiency
) values (
    1,
    4,
    'C++',
    1
);

insert into skills (
    id,
    employee_id,
    skill,
    proficiency
) values (
    2,
    1,
    'JAVA',
    2
);

insert into skills (
    id,
    employee_id,
    skill,
    proficiency
) values (
    3,
    4,
    'APEX',
    3
);

insert into skills (
    id,
    employee_id,
    skill,
    proficiency
) values (
    4,
    3,
    'JSON',
    4
);

insert into skills (
    id,
    employee_id,
    skill,
    proficiency
) values (
    5,
    2,
    'JAVASCRIPT',
    5
);

insert into skills (
    id,
    employee_id,
    skill,
    proficiency
) values (
    6,
    4,
    'PYTHON',
    1
);

commit;

alter table skills
modify id generated always as identity restart start with 7;
 
 
-- Generated by Quick SQL Monday March 13, 2023  23:43:23
 
/*
departments /insert 2
   name
   location vc255
   country vc255
   employees /insert 4
      name
      email
      job vc255
      hiredate
      skills /insert 6
          skill vc255 /values C++, Java, APEX, JSON, Javascript, Python, CSS
          proficiency num /check 1, 2, 3, 4, 5 [with 1 being a novice and 5 being a guru]

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