Zadanie8
select '' as Zadanie_1 from dual;
select * from PRACOWNICY;
insert into PRACOWNICY (ID_PRAC, NAZWISKO, ETAT, ZATRUDNIONY, PLACA_POD, ID_ZESP)
values (250, 'KOWALSKI', 'ASYSTENT', TO_DATE('2015-01-13', 'YYYY-MM-DD'), 1500, 10);
insert into PRACOWNICY (ID_PRAC, NAZWISKO, ETAT, ZATRUDNIONY, PLACA_POD, ID_ZESP)
values (260, 'ADAMSKI', 'ASYSTENT', TO_DATE('2014-09-10', 'YYYY-MM-DD'), 1500, 10);
insert into PRACOWNICY (ID_PRAC, NAZWISKO, ETAT, ZATRUDNIONY, PLACA_POD, PLACA_DOD, ID_ZESP)
values (270, 'NOWAK', 'ADIUNKT', TO_DATE('1990-05-01', 'YYYY-MM-DD'), 2050, 540, 20);
select * from PRACOWNICY;
select '' as Zadanie_2 from dual;
update PRACOWNICY
set PLACA_POD = PLACA_POD * 1.10,
PLACA_DOD = NVL(PLACA_DOD, 100) * 1.20;
select '' as Zadanie_3 from dual;
insert into ZESPOLY (ID_ZESP, NAZWA, ADRES)
values (60, 'BAZY DANYCH', 'PIOTROWO 2');
select '' as Zadanie_4 from dual;
update PRACOWNICY
set ID_ZESP = (select ID_ZESP from ZESPOLY where NAZWA = 'BAZY DANYCH')
where ID_PRAC in (250, 260, 270);
select '' as Zadanie_5 from dual;
update PRACOWNICY
set ID_SZEFA = (select ID_PRAC from PRACOWNICY where NAZWISKO = 'MORZY')
where ID_ZESP = (select ID_ZESP from ZESPOLY where NAZWA = 'BAZY DANYCH');
select '' as Zadanie_6 from dual;
delete from ZESPOLY where NAZWA = 'BAZY DANYCH';
select '' as Zadanie_7 from dual;
delete from PRACOWNICY where ID_ZESP = (select ID_ZESP from ZESPOLY where NAZWA = 'BAZY DANYCH');
delete from ZESPOLY where NAZWA = 'BAZY DANYCH';
select '' as Zadanie_8 from dual;
select NAZWISKO, PLACA_POD, ROUND(0.10 * AVG(PLACA_POD) OVER (PARTITION BY ID_ZESP), 2) as PODWYZKA
from PRACOWNICY;
select '' as Zadanie_9 from dual;
update PRACOWNICY
set PLACA_POD = PLACA_POD + ROUND(0.10 * (select AVG(PLACA_POD) from PRACOWNICY group by ID_ZESP), 2);
select '' as Zadanie_10 from dual;
select * from PRACOWNICY
where PLACA_POD = (select MIN(PLACA_POD) from PRACOWNICY);
select '' as Zadanie_11 from dual;
update PRACOWNICY
set PLACA_POD = ROUND((select AVG(PLACA_POD) from PRACOWNICY), 2)
where PLACA_POD = (select MIN(PLACA_POD) from PRACOWNICY);
select '' as Zadanie_12 from dual;
update PRACOWNICY
set PLACA_DOD = (select AVG(PLACA_POD) from PRACOWNICY where ID_SZEFA = (select ID_PRAC from PRACOWNICY where NAZWISKO = 'MORZY'))
where ID_ZESP = 20;
select '' as Zadanie_13 from dual;
update PRACOWNICY p
set PLACA_POD = PLACA_POD * 1.25
where ID_ZESP = (select ID_ZESP from ZESPOLY where NAZWA = 'SYSTEMY ROZPROSZONE');
select '' as Zadanie_14 from dual;
delete from PRACOWNICY
where ID_SZEFA = (select ID_PRAC from PRACOWNICY where NAZWISKO = 'MORZY');
select '' as Zadanie_15 from dual;
select * from PRACOWNICY;
select '' as Zadanie_16 from dual;
create sequence PRAC_SEQ start with 300 increment by 10;
select '' as Zadanie_17 from dual;
insert into PRACOWNICY (ID_PRAC, NAZWISKO, ETAT, PLACA_POD)
values (PRAC_SEQ.NEXTVAL, 'Trąbczyński', 'STAZYSTA', 1000);
select '' as Zadanie_18 from dual;
update PRACOWNICY
set PLACA_DOD = PRAC_SEQ.CURRVAL
where NAZWISKO = 'Trąbczyński';
select '' as Zadanie_19 from dual;
delete from PRACOWNICY where NAZWISKO = 'Trąbczyński';
select '' as Zadanie_20 from dual;
create sequence MALA_SEQ start with 1 increment by 1 maxvalue 10;
select '' as Zadanie_21 from dual;
drop sequence MALA_SEQ;