-- drop schema "20141103" cascade;
-- create schema "20141103";
set search_path to 20141103;
-- drop table afferenza;
create table afferenza(
   impiegato character(10) primary key,
   reparto character (2) not null
);
--- drop table direzione;
create table direzione(
   reparto character (2) primary key, 
   capo character(10)  not null
);
insert into afferenza values ('Rossi','A');
insert into afferenza values ('Neri','A');
insert into afferenza values ('Verdi','B');
insert into afferenza values ('Bruni','C');
insert into afferenza values ('Mori','D');
insert into direzione values ('A','Del Neri');
insert into direzione values ('B','De Rossi');
insert into direzione values ('C','Del Neri');

--Trovare gli immpiegati che hanno lo stesso capo

select a1.impiegato, a2.impiegato, d1.capo
from (afferenza a1 join direzione d1 on a1.reparto = d1.reparto)
     join 
     (afferenza a2 join direzione d2 on a2.reparto = d2.reparto)
     on d1.capo = d2.capo
where a1.impiegato > a2.impiegato;

create view supervisione as 
	select impiegato, capo
	from afferenza join direzione on afferenza.reparto = direzione.reparto

select s1.impiegato, s2.impiegato, s1.capo
from supervisione s1 join supervisione s2 on s1.capo=s2.capo 
where s1.impiegato > s2.impiegato;

------- 


SET search_path to public;

DROP TABLE paternita CASCADE;
CREATE TABLE paternita
(
  padre character(20),
  figlio character(20) PRIMARY KEY
);
INSERT INTO paternita VALUES ('Sergio','Franco');
INSERT INTO paternita VALUES('Luigi','Olga');
INSERT INTO paternita VALUES('Luigi','Filippo');
INSERT INTO paternita VALUES('Nicola','Andrea');

--Trovare il numero medio di figli per padre

select padre , count(distinct Figlio)
from Paternita
group by Padre;

--drop view contafigli;
create view contafigli as
    select padre , count(distinct Figlio) as numerofigli
    from Paternita
    group by Padre;

select avg(numerofigli)::numeric(4,1) as numeromediofigli
from contafigli
