databases-training

databases-training2

--select nazwisko
--  from pracownicy
--  where etat != 'asystent'
--  order by PLACA_POD desc
--  ;

--
--6
--select nazwisko, zatrudniony, 
--  zatrudniony - EXTRACT (YEAR FROM DATE '2000-01-01') as staz_w_2000
--  from pracownicy;
--7
--select nazwisko,
--  to_char(ZATRUDNIONY, 'MONTH, MM, YYYY') as DATA_ZATRUDNIENIA
--  from pracownicy
--  where ID_ZESP = 20;
--8
--select to_char(current_date, 'DAY') from dual;

--select * from zespoly;
----9
--select nazwa, adres,
--  (case  
--    when nazwisko in ('Mielżyński', 'Strzelecka') then 'Stare Miasto'
--    when nazwisko in ('Mielżyński', 'Strzelecka') then 'Stare Miasto'
--  as dzielnica)
--  from zespoly;

--4.1
--select
--  min(placa_pod) as minimum,
--  max(placa_pod) as maximum,
--  min(placa_pod) - max(placa_pod) as roznica
--from pracownicy;  

--4.2
--select
--  etat,
--  avg(placa_pod) as srednia
--from pracownicy
--group by etat
--order by srednia desc
--;

--4.3
--select count(etat)
--from pracownicy
--where etat = 'PROFESOR'
--;

--4.4
--select id_zesp, sum(placa_pod + nvl(placa_dod, 0))
--from pracownicy
--group by id_zesp
--;

--4.5 
--select  max(sum(placa_pod + nvl(placa_dod, 0))) as SUMARYCZNE_PLACE
--from pracownicy
--group by id_zesp
--;

--4.6
--select id_szefa,
--  min(placa_pod)
--from pracownicy
--group by id_szefa
--;

--4.7
--select id_zesp,
--  count(*) as ILU_PRACUJE
--from pracownicy
--group by id_zesp
--order by count(*) desc
--;

--4.8
--select id_zesp,
--  count(*) as ILU_PRACUJE
--from pracownicy
--group by id_zesp
--having count(*) > 3
--order by count(*) desc
--;

--4.9
--select ID_PRAC
--from pracownicy
--group by ID_PRAC
--having count(*) > 1
--;

--4.10
--select etat, avg(placa_pod) as srednia
--from pracownicy
--where ZATRUDNIONY < date '1990-01-01'
--group by etat
--;

----4.11
--select
--  id_zesp
--  etat,
--  avg(placa_pod + nvl(placa_dod, 0)) as srednia,
--  max(placa_pod + nvl(placa_dod, 0)) as maxymalna
--from pracownicy
--group by id_zesp, etat
--having etat in ('ASYSTENT', 'PROFESOR')
--order by id_zesp
--;

----4.12
--select EXTRACT (YEAR FROM zatrudniony), count(*)
--from pracownicy
--group by EXTRACT (YEAR FROM zatrudniony)
--order by EXTRACT (YEAR FROM zatrudniony)
--;

--4.13
--select
--  LENGTH(nazwisko) as "ile liter",
--  count(*) as "ilu"
--from pracownicy
--group by LENGTH(nazwisko) 
--order by LENGTH(nazwisko)
--;

--4.14
--
TODO