----- JOIN ----------
---------------------
-- Images et mesures associées à un accession (HM020 par exemple)
SELECT b.id_pl, b.repetition, b.num_boite, b.inoculee, m.*
FROM boite b
JOIN image_boite i ON (b.id=i.id_boite)
JOIN mesures_plante m
ON (i.fichier=m.fichier AND i.position=m.position_boite)
WHERE b.id_pl='HM020';
-- Distribution du poids des plantes résistantes inoculées
SELECT DISTINCT b.id, poids_matiere_fraiche
FROM boite b
JOIN mesures_plante m ON (b.id=m.id_boite)
WHERE m.brunissement_pc<100;
-- Générer le fichier de départ
DROP VIEW IF EXISTS plantes;
CREATE VIEW plantes AS
SELECT
pl.hapmap_id, pl.lignee, pl.population,
mo.nom AS pathogene,
b.repetition, b.id_manip AS manip, b.num_boite AS boite,
b.inoculee AS inoculee, b.contamination AS contaminee,
m15.num_plant AS plante, m15.brunissement AS brun_15, m15.R1 AS R1_15,
m15.brunissement_pc AS pc_brun_15, m15.cotyledons AS cotyledons_15,
m15.plantes_mortes AS morts_15, m15.R2 AS R2_15,
m21.brunissement AS brun_21, m21.R1 AS R1_21,
m21.brunissement_pc AS pc_brun_21, m21.cotyledons AS cotyledons_21,
m21.plantes_mortes AS morts_21, m21.R2 AS R2_21,
b.poids_matiere_fraiche AS poids
FROM organisme_pl pl
JOIN boite b ON (pl.hapmap_id = b.id_pl)
JOIN manip ON (b.id_manip = manip.id)
JOIN organisme_mo mo ON (manip.id_mo = mo.id)
JOIN mesures_plante m15 ON (b.id = m15.id_boite)
JOIN image_boite i15
ON (m15.fichier = i15.fichier AND m15.position_boite = i15.position)
JOIN mesures_plante m21 ON (b.id = m21.id_boite)
JOIN image_boite i21
ON (m21.fichier = i21.fichier AND m21.position_boite = i21.position)
WHERE i15.jour=15 AND i21.jour=21 AND m15.num_plant=m21.num_plant
ORDER BY pl.hapmap_id, b.repetition, b.id_manip, b.num_boite, m15.num_plant;
-- Afficher les plantes mortes (accession, n° répétition, n° boite, n° plante, inoculée, contaminée) qui ont leurs cotylédons complètement jaunis (sans la vue ''plantes'').
SELECT b.id_pl, b.repetition, b.num_boite, m.num_plant, b.inoculee, b.contamination, m.plantes_mortes
FROM boite b JOIN mesures_plante m ON (b.id=m.id_boite)
WHERE cotyledons=2 AND plantes_mortes>0;
-- Afficher les plantes mortes qui ont leurs cotylédons complètement jaunis
SELECT hapmap_id, repetition, boite, plante, inoculee, contaminee
FROM plantes
WHERE cotyledons_15=2 AND morts_15=1 OR cotyledons_21=2 AND morts_21=1;
-- pareil mais avec le tri d'abord les boites contaminées et en triant par accession et répétition croissantes.
SELECT hapmap_id, repetition, boite, plante, inoculee, contaminee
FROM plantes
WHERE cotyledons_15=2 AND morts_15=1 OR cotyledons_21=2 AND morts_21=1
ORDER BY contaminee DESC, hapmap_id, repetition;
----- COUNT ----------
----------------------
-- Afficher le nombre de mesures effectuées.
SELECT count(*)
FROM mesures_plante;
-- Afficher le nombre de mesures effectuées à 21 jours.
SELECT count(*)
FROM mesures_plante m JOIN image_boite i ON (m.fichier=i.fichier AND m.position_boite=i.position)
WHERE jour=21;
-- Afficher le nombre maximum de répétitions (fonction MAX).
SELECT max(repetition)
FROM boite;
-- Afficher la moyenne (fonction AVG pour average) du brunissement à 15 jours pour les boites inoculées.
SELECT AVG(brun_15)
FROM plantes
WHERE inoculee=1 AND contaminee=0;
----- GROUP BY ----------
-------------------------
-- le nombre d'accession par pays en triant par ordre décroissant le nombre d'accession et en triant les noms des pays par ordre alphabétique
SELECT pays, COUNT(*)
FROM organisme_pl
GROUP BY pays
ORDER BY 2 DESC, 1;
-- Afficher le nombre de boites par accession
SELECT id_pl, count(*)
FROM boite
GROUP BY id_pl;
-- Afficher la moyenne du pourcentage de brunissement par boite
SELECT hapmap_id, repetition, boite, AVG(pc_brun_15), AVG(pc_brun_21)
FROM plantes
WHERE inoculee=1 AND contaminee=0
GROUP BY hapmap_id, repetition, boite
ORDER BY 1,2,3;
----- HAVING ----------
-----------------------
-- Afficher la moyenne du pourcentage de brunissement
-- par boite des plantes résistantes uniquement
SELECT hapmap_id, repetition, boite, AVG(pc_brun_15), AVG(pc_brun_21)
FROM plantes
WHERE inoculee=1 AND contaminee=0
GROUP BY hapmap_id, repetition, boite
HAVING AVG(pc_brun_15)<100 AND AVG(pc_brun_21)<100
ORDER BY 1,2,3;
-- Afficher les informations sur la boite, et le brunissement
-- à 15 et 21 jours en triant sur le pourcentage de brunissement
-- en n'affichant que les boites inoculées non contaminées
SELECT hapmap_id, manip, repetition, boite, AVG(pc_brun_15), AVG(pc_brun_21)
FROM plantes
WHERE inoculee=1 AND contaminee=0
GROUP BY hapmap_id, manip, repetition, boite
ORDER BY 5,6;
-- Le nombre de racines secondaires par accession résistant
-- (dont le pourcentage de brunissement est inférieur à 100%)
SELECT hapmap_id, AVG(R2_15), AVG(R2_21)
FROM plantes
WHERE inoculee=1 AND contaminee=0
GROUP BY hapmap_id
HAVING AVG(pc_brun_21)<100
ORDER BY 3 DESC;
