--1
create table employees as select * from HR.EMPLOYEES;
create table departments as select * from HR.DEPARTMENTS;
create table countries as select * from HR.COUNTRIES;
create table jobs as select * from HR.JOBS;
create table locations as select * from HR.LOCATIONS;

alter table employees
add primary key (employee_id);

alter table jobs
add primary key (job_id);

alter table employees
add primary key (employee_id);

alter table locations
add primary key (location_id);

alter table departments
add primary key (department_id);

alter table employees
add foreign key (department_id); reference departments (department_id;)

alter table employees
add foreign key (jobs_id); reference jobs (job_id;)

alter table locations
add foreign key (department_id); reference locations (location_id;)

--2
create view dane60 as select * from employees
where department_id=60;

--3
select * from dane60;
update dane60 set salary= salary *1.1;
update dane60 set salary= salary *1.1 where department_id=90;

--4
create view DaneShiping as select first_name, last_name, salary
from employees join departments using (department_id)
where department_name= 'Shipping';
--5
create view danePracow as select first_name, last_name, nvl2(commision_pct,(commision_pct+1)*salary*12,salary*12)
as wynagrodzenie
from employees;
--6
drop view





--7
create view dane as select employee_id, first_name, last_name, job_title
from employees join jobs using (job_id);
select * from danePrac;
update danePrac set first_name = 'Jan' where employee_id=206;
drop view danePrac;
create view dane as select employee_id, first_name, last_name, job_title
from employees join jobs using (job_id);
with read only;

--8

create view stanowiska as select job_id, count(employee_id) as liczba
from employees
group by job_id;

--9

create view oddzial as select count(employee_id) as liczbapracow, min(salary) as minimalna, max(salary) as maxymalna
from employees;
group by department_id;

--10
create view liczbaOddzialow as select country_name, count(department_id) as liczbaOddz, count(employee_id) as liczbaprac
from employees join departments usig (department_id)
join locations using (location_id)
join counties using (country_id)
group by country_name;

select * from liczbaOddzialow
frop view liczbaOddzialow;

--11

create or replace view Pensje as select department_name, min(salary) as minimalna, max(salary) as maksymalna
avg(salary) as srednia
from employees join departments using (department_id)
group by department_name;
select * from Pensje;
update Pensje set department_name = 'New IT' from department_name = 'IT';

--12

create view danePracow as select first_name,last_name,job_title
from employees join jobs using (job_id)
group by job_title;