
CREATE TABLE Personne
( personneId serial,
  nom varchar(25) NOT NULL,
  prenom varchar(25) NOT NULL,
  dateNaissance date NOT NULL,
  sexe varchar(1) NOT NULL,
  CONSTRAINT PK_Personne PRIMARY KEY (personneId),
  CONSTRAINT UNE_Personne UNIQUE (nom,prenom,dateNaissance),
  CONSTRAINT CK_Sexe CHECK (sexe IN ('M','F'))
 );

CREATE TABLE Parente
( parentId int NOT NULL,
  enfantId int NOT NULL,
  CONSTRAINT PK_Parente PRIMARY KEY (parentId,enfantId),
  CONSTRAINT FK_Parente_1 FOREIGN KEY (parentId) REFERENCES Personne (personneId),
  CONSTRAINT FK_Parente_2 FOREIGN KEY (enfantId) REFERENCES Personne (personneId),
  CONSTRAINT CK_Parente CHECK (parentId!=enfantId)
 );
          
-- Fonction contenant le corps du trigger                
 CREATE OR REPLACE Function FonctionVerifDate() returns trigger as
'Begin
  IF (SELECT  Personne.dateNaissance FROM Personne WHERE PersonneID=NEW.parentID) > 
     (SELECT  Personne.dateNaissance FROM Personne WHERE PersonneID=NEW.enfantID)
  THEN 
        RAISE exception ''Un parent ne peut pas avoir une date de naissance > a celle de son enfant! '' ;
  ELSE 
       return NEW; 
END IF;
END;'
LANGUAGE 'plpgsql';

-- code SQL de creation du trigger                     
CREATE TRIGGER  VerifDate BEFORE INSERT ON Parente
FOR EACH ROW 
EXECUTE procedure FonctionVerifDate();  

-- Insertions
INSERT INTO Personne(Nom,Prenom,dateNaissance,sexe) VALUES ('Gamotte','Albert','1973-05-04','M'), 
('Debece','Aude','1971-10-06','F'), ('Debece','Gill','2000-01-12','M'), 
('Gamotte','Albertine','2005-05-05','F'), ('SONN','Eric','1971-12-12','M'),
('SONN','Pepito','2008-12-31','M'),('SONN','Nicolas','2008-12-31','M'),
('Debece','Eve','1999-11-05','F'), ('Onette','Camille','1942-01-06','F'), ('Onette','Marion','1960-07-31','F'); 
                           
-- Albert Gamotte est le pere d'Albertine Gamotte 
-- Aude Debece est la mere d'Albertine Gamotte et de Gill Debece et Eve Debece
-- Eric SONN est le pere de Pepito et Nicolas SONN 
-- Camille Onette est la mere de Aude Debece et d'Eric SONN  et Marion Onette                                                    
INSERT INTO Parente (parentId,enfantId) VALUES(1,4), (2,4),(2,3), (5,6),(5,7),(2,8),(9,2),(9,5),(9,10);

-- Requete a
SELECT DISTINCT p.Nom AS nomParent, p.Prenom AS prenomParent, e.Nom AS nomEnfant, e.Prenom AS prenomEnfant
 FROM personne e, personne p, parente par 
 WHERE p.personneid=par.parentid and e.personneid=par.enfantid 
 AND p.Nom = 'Gamotte' AND p.Prenom = 'Albert'; 

--Requete b
SELECT nom AS nomParent,  prenom AS prenomParent, COUNT (personneId) AS nbEnfants 
FROM Personne, Parente 
WHERE personneId = parentID 
GROUP BY (personneId);

--Requete c
SELECT Nom AS nomPersonne,  Prenom AS prenomPersonne, COUNT (EnfantId) AS nbEnfants 
FROM Personne LEFT OUTER JOIN Parente ON (personneId=parentId)
GROUP BY (personneId);

--Requete d
SELECT DISTINCT e.Nom AS nomEnfant, e.Prenom AS prenomEnfant, p1.Nom AS nomParent1, p1.Prenom AS prenomParent1, p2.Nom AS nomParent2, p2.Prenom AS prenomParent2
 FROM personne e LEFT OUTER JOIN (SELECT * FROM personne, parente WHERE personne.personneid=parente.parentid) p1  ON e.personneid=p1.enfantid 
LEFT OUTER JOIN (SELECT * FROM personne, parente WHERE personne.personneid=parente.parentid) p2 ON e.personneid=p2.enfantid and p1.parentid!=p2.parentid 
WHERE e.Nom = 'Gamotte' AND e.Prenom = 'Albertine' and (p2.parentid IS NULL OR p1.parentid<p2.parentid); 

--Requete e
SELECT  E.Nom AS NomEnfant, E.Prenom AS PrenomEnfant,count(DISTINCT F.personneId) AS NbFreres, count(DISTINCT S.personneId) AS NbSoeurs   
FROM Personne E
LEFT JOIN Parente PE ON PE.EnfantId= E.PersonneId
LEFT JOIN Parente PF ON PE.parentId= PF.parentId AND  PF.EnfantId <> E.PersonneId
LEFT JOIN Personne F ON PF.EnfantId = F.personneId AND F.personneId <> E.PersonneId  AND F.Sexe ='M'
LEFT JOIN Parente PS ON PS.parentId= PF.parentId AND  PS.EnfantId <> E.PersonneId
LEFT JOIN Personne S ON PS.EnfantId = S.personneId AND S.personneId <> E.PersonneId  AND S.Sexe ='F'
Group by E.Nom,E.Prenom;

-- Requete f
SELECT DISTINCT p.Nom AS nomParent, p.Prenom AS prenomParent
 FROM personne p, personne e, parente par
  WHERE p.personneid=par.parentid and e.personneid=par.enfantid and e.sexe='F'

-- Requete g
SELECT DISTINCT p.Nom AS nomParent, p.Prenom AS prenomParent
 FROM personne p, personne e, parente par
  WHERE p.personneid=par.parentid and e.personneid=par.enfantid 
and p.personneid NOT IN (SELECT parentid FROM personne e, parente par
  WHERE p.personneid=par.parentid and e.personneid=par.enfantid and e.sexe='M')

-- Requete h
SELECT DISTINCT p.Nom AS nomParent, p.Prenom AS prenomParent
 FROM personne p, personne g, parente par1, personne f, parente par2
  WHERE p.personneid=par1.parentid and g.personneid=par1.enfantid and g.sexe='F'
and p.personneid=par2.parentid and f.personneid=par2.enfantid and f.sexe='M' 
and g.datenaissance < f.datenaissance;

-- Requete i
SELECT DISTINCT gp.Nom AS nomGParent, gp.Prenom AS prenomGParent
 FROM personne gp, personne e, parente pe, parente pgp
  WHERE e.personneid=pe.enfantid and pe.parentid=pgp.enfantid  
   AND gp.personneid=pgp.parentid
   AND e.Nom = 'Gamotte' AND e.Prenom = 'Albertine'; 

-- Requete j
SELECT DISTINCT t.Nom AS nomTante, t.Prenom AS prenomTante
 FROM personne gp, personne e, parente pe, parente pgp, personne t, parente tgp
  WHERE e.personneid=pe.enfantid and pe.parentid=pgp.enfantid  
   AND gp.personneid=pgp.parentid and pgp.parentid=tgp.parentid
   AND tgp.enfantid!=pe.parentid and t.personneid=tgp.enfantid
   AND t.sexe='F'
   AND e.Nom = 'Gamotte' AND e.Prenom = 'Albertine'; 
