13123213213213

-- zad1
create table employees1 as
  select * from hr.employees;
create table departaments1 as
  select * from hr.departaments;
create table jobs1 as
  select * from hr.jobs;
create table locations1 as
  select * from hr.locations;

alter table employees1 add primary key (employee_id);
alter table departaments add primary key (departament_id);
alter table jobs1 add primary key (job_id);
alter table locations1 add primary key (locations_id);

select * from departaments;

-- zad 2
create view v_emplyees60 as
select * from employees1 where departament_id=60;



-- zad 3
select * from v_employees60;
update v_employees1 set salary =1.1*salary where departament_id=60;
update employee60 set salary=1.1*salary where departament_id=90;

--zad 4
create or replace view v_imiona as
select first_name, last_name, salary
from employees1 join departaments1 using(departament_id)
where job_title like 'Shipping';

select * from v_imiona;

-- zad 5
create view v_elo as
select first_name, last_name, 12*salary as "roczna"
from employes1;

select * from v_elo;

--zad6
drop view v_elo;

-- zad7
create view v_prac as
select employee_id, first_name, job_title
from employees1 join jobs1 using (job_id);

select * from v_prac;

update v_prac set first_name = 'jakub' where  first_name='William';

select * from v_prac;


create or replace view v_prac as
select employee_id, first_name, job_title
from employees1 join jobs1 using (job_id)
with read only;


update v_prac set first_name = 'jakub' where  first_name='William';

select * from v_prac;,

-- zad8
create view v_id as
select job_title, count(employee_id) as "liczba pracownikow"
from employees1 join jobs1 using (job_id)
group by jobs1.job_title;

select * from v_id;

-- zad 9
create view v_oddzial as
select departament_name, count(employee_id) as ilosc, max(salary)
as "maksymalna pensja", min(salary) as "minimalna pensja" from
from departaments1 join employees1 using (departament_id)
group by departament_name;

select * from v_oddzial;

-- zad 10
create view v_panstwa as
select country_id, count(departament_id) as "liczba oddzialow",
count(employee_id) as "liczba pracownikow"
from employees1 join departments1 using(departament_id)
join location1 using(location_id) group by country_id;

select * from v_panstwa;

--zad11
create or replace view oddzial_pensja as
select min(salary) as "minimalna", max (salary) as "maksymalna" ,
avg(salary) as "srednia" from employees1 join departaments1 using(departament_id)
group by departament_name;

select * from oddzial_pensja;

update oddzial_pensja set departament_name = 'new it' where departament_name = 'it';


-- zad 12
create view v_pracownik2 as
select first_name, last_name, job_title
from emploees1 join jobs1 using (job_id);

insert into v_pracownik2 (first_name, last_name, job_title)
values ('jakub','jakubiak', '123');

update v_pracownik set first_name = 'kuba' where first_name = 'jakub'
delete from v_pracownik2 where first_name ='kuba';

select *from v_pracownik2;

-- zad13
create view v_prac_zarob as select first_name, last_name, salary
from employees where salary<7000 order by salary asc;
select * from v_prac_zarob;

update v_prac_zarob set salary=8000 where first_name='jan';

--zad 14
create or replace view v_prac_zarob as select first_name, last_name, salary
from employees where salary<7000 order by salary asc
with check option;

update v_prac_zarob set salary=8000 where first_name='steven';

select * from v_prac_zarob;