L2 AADB Requetes SQL
From silico.biotoul.fr
(Difference between revisions)
Barriot (Talk | contribs)
(Created page with '<source lang='SQL'> ----- JOIN ---------- --------------------- -- Images et mesures associées à un accession (HM020 par exemple) SELECT b.id_pl, b.repetition, b.num_boite, b…')
(Created page with '<source lang='SQL'> ----- JOIN ---------- --------------------- -- Images et mesures associées à un accession (HM020 par exemple) SELECT b.id_pl, b.repetition, b.num_boite, b…')
Current revision as of 13:58, 18 April 2012
----- 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;