Demography: Answers

SELECT E1.NumindEnfant, I.Sexe, Count(*) AS "Brother or Sister" FROM INDIVIDU I, ENFANT E, ENFANT E1 WHERE I.numind=E.numindEnfant AND (E.NumindPere=E1.NumindPere) AND (E.NumindMere=E1.NumindMere) AND (E.NumindEnfant<>E1.NumindEnfant) GROUP BY E1.NumindEnfant, I.Sexe;

SELECT I.Nom, I1.Nom AS "Grandfather", I2.Nom AS "Grandmother" FROM ENFANT E, ENFANT E1, INDIVIDU I, INDIVIDU I1, INDIVIDU I2 WHERE E.NumindPere=E1.numindenfant AND E.NumindEnfant=I.numind AND E1.NumindPere=I1.numind AND E1.NumindMere=I2.numind;

SELECT Avg(DATEDIFF(Dtmariage,Dtnaiss)/365) AS "Age at the date of marriage" FROM INDIVIDU I, MARIAGE M WHERE I.Numind=M.numindfemme;

SELECT I2.Numind, I2.Nom FROM INDIVIDU I, INDIVIDU I1, INDIVIDU I2, ENFANT E WHERE I.Numind=E.Numindpere AND I1.Numind=E.Numindmere AND I.Nation<>I1.Nation AND I2.Numind=E.Numindenfant;

SELECT I.Nom AS "Father", I1.Nom AS "Mother", count(*) AS "Nb of children" FROM INDIVIDU I, INDIVIDU I1, ENFANT E WHERE E.Numindpere=I.Numind AND E.Numindmere=I1.Numind GROUP BY I.Nom, I1.Nom HAVING count(*)>2;

SELECT count(*) FROM INDIVIDU I, INDIVIDU I1, ENFANT E, ENFANT E1 WHERE E.Numindpere=E1.Numindpere AND E.Numindmere=E1.Numindmere AND I.numind=E.Numindenfant AND I1.Numind=E1.Numindenfant AND I.Dtnaiss<=I1.Dtnaiss AND E.Numindenfant=11;