##########################################################
#
# s'exécute par :
#
#  mysql -v -v -v --user root test < elf.dmd > elf.rep
#
# à faire après l'exercice sur titanic
#
# ==> commenter catdbf elf et cf elf M
#
##########################################################

# 1. donner le matricule (champ iden) et l'age des trois premières
#    femmes de la base de données

# cette question n'a aucun sens car la notion d'ordre ("les trois premières femmes")
# n'a pas été définie. en triant sur le matricule,

SELECT iden, age FROM elf WHERE sexe=1 ORDER BY iden LIMIT 3 ;

# 2. donner en entier les pourcentages des effectifs
#    pour les divers niveaux d'etude

SELECT etud, ROUND(100*COUNT(iden)/99) FROM elf GROUP BY etud ;

#
# solution postgresSQL :
#   SELECT 100.0*COUNT(*) / (SELECT COUNT(*) FROM elf)  FROM elf WHERE sexe=1;
# une autre solution :
#
# SELECT 100.0*femmes / total * 100
#        FROM (SELECT COUNT(*) AS femmes FROM elf WHERE sexe=1) T1,
#             (SELECT COUNT(*) AS total  FROM elf) T2 ;
#


# 3. donner la moyenne d'age des divers niveaux d'etude

SELECT etud, AVG(age) FROM elf GROUP BY etud ;

# pour afficher les labels des niveaux d'étude et non pas
# les codes, la technique du if(etud=0,"NR",if(etud=1,"primaire",if(...
# est inapplicable et il vaut mieux créer une table de décodage
# des niveaux d'étude, soit :

DROP TABLE IF EXISTS elfetud ;

CREATE TABLE elfetud (
  code  INT(3) ,
  label VARCHAR(50)
) ;

INSERT INTO elfetud (code,label) VALUES (0,"non-réponse") ;
INSERT INTO elfetud (code,label) VALUES (1,"primaire") ;
INSERT INTO elfetud (code,label) VALUES (2,"secondaire (bepc)") ;
INSERT INTO elfetud (code,label) VALUES (3,"baccalauréat") ;
INSERT INTO elfetud (code,label) VALUES (4,"supérieur") ;

# il est alors possible d'effectuer une jointure sur les
# tables :

 SELECT elfetud.label, ROUND(COUNT(elf.etud)*100/99) AS eff, "%"
        FROM     elf, elfetud
        WHERE    elfetud.code=elf.etud
        GROUP BY etud
        ORDER BY eff DESC ;


# 4. idem par moyenne décroissante

SELECT etud, AVG age) AS moya FROM elf GROUP BY etud ORDER BY moya DESC ;

# 5. idem par moyenne décroissante pour plus de 45 ans en moyenne seulement

SELECT etud,
       AVG(age) AS moya
       FROM       elf
       GROUP  BY  etud
       HAVING     moya > 45
       ORDER  BY  moya       DESC
;

# 6. idem par moyenne décroissante pour les femmes
#    on filtre toujours sur plus de 45 ans en moyenne

SELECT etud, AVG(age) AS moya
       FROM       elf
       WHERE      sexe=1
       GROUP  BY  etud
       HAVING     moya > 45
       ORDER  BY  moya       DESC
;


# 7. commenter l'ajout de limit 10
#    dans la commande précédente,
#    à savoir :

SELECT etud, AVG(age) AS moya
       FROM       elf
       WHERE      sexe=1
       GROUP  BY  etud
       HAVING     moya > 45
       ORDER  BY  moya       DESC
       LIMIT 10
;

# 8. dans les 50 premières lignes de la table,
#    combien y a-t-il de femmes ?

# cette question n'a aucun sens car la notion d'ordre ("les 50 premières lignes")
# n'a pas été définie. en triant sur le matricule, on peut écrire  en utilisant
# une table temporaire pour stocker le code sexe des 50 premières lignes triées par matricule


 DROP TABLE IF EXISTS tmpElf ;

 CREATE TABLE tmpElf ( sexe INT(1) ) ;

 INSERT INTO      tmpElf
        SELECT    sexe
        FROM      elf
        ORDER BY  iden
        LIMIT     50 ;

 SELECT COUNT(sexe)
        FROM tmpElf
        WHERE sexe=1 ;

# surtout pas l'instruction suivante qui renvoie 64 (!) :

  SELECT " SURTOUT PAS " AS incorrect , COUNT(*) FROM elf WHERE sexe=1 LIMIT 50 ;

# il est aussi possible de mettre un SELECT dans le FROM (alias obligatoire) :

  SELECT COUNT(*) FROM (SELECT * FROM elf LIMIT 50 ) AS tmp WHERE sexe=1 ;

# les solutions

  SET @nbl = 0 ;
  SELECT @nbl := @nbl + 1 AS num , COUNT(*) FROM elf WHERE @nbl < 50  AND sexe= 1 ORDER BY age ;
  SELECT @nbl := @nbl + 1 AS num , COUNT(*) FROM elf WHERE num  < 50  AND sexe= 1 ORDER BY age ;

# ne fonctionnent pas non plus

# 9. y a-t-il une différence significative entre la moyenne d'age
#    des hommes et celle des femmes ?

# c'est une question de statistiques. il faudrait utiliser un test t de Student
# pour comparer les moyennes, calculer la différence limite au seuil alpha
# de 5 % et donner ensuite la "p-value". aucune implémentation de SQL ne
# permet de le faire (en 2008). il faudrait utiliser un logiciel de statistiques
# pour avoir la réponse qui est ici "au seuil de 5 % on ne refuse pas l'hypothèse
# nulle d'égalité des moyennes" (sous réserve de normalité des distributions).