MSBM
2002-2003
TD4:LANGAGEALGBRIQUEETLANGAGESQL
CORRECTION
I La base de donnes ZOO
Pour rappel, le schma relationnel de la base de donnes Zoo :
ANIMAL (N_A, NOM, AGE, RACE, ORIGINE, N_MENU)
FAMILLE (RACE, ORIGINE, AMI)
MENU (N_M, QTE_VIANDE, QTE_LEGUME)
ENFANT (ENFANT, PARENT)
1 - Lister le nom et l'ge de tous les animaux.
nom, age (ANIMAL)
SELECT nom, age FROM ANIMAL;
2 - Donner les caractristiques de l'animal appel HECTOR.
nom = "HECTOR" (ANIMAL)
SELECT * FROM ANIMAL WHERE nom = "HECTOR";
3 - Donner les origines distinctes de toutes les familles.
origine (FAMILLE)
SELECT DISTINCT origine FROM FAMILLE;
4 - Donner les noms des singes de plus de 10 ans et originaire d'Afrique.
nom ( race = "SINGE"
and age > 10 and origine = "AFRIQUE"
SELECT nom FROM ANIMAL
WHERE race = "SINGE" AND age > 10 AND origine = "AFRIQUE";
5 - Donner les noms des singes ainsi que des animaux de plus de 8 ans.
nom ( race = "SINGE" or age > 8 )
SELECT nom FROM ANIMAL
WHERE race = "SINGE" OR age > 8;
MSBM
2002-2003
6 - Donner les races des animaux dont le numro de menu est 1 et l'ge est suprieur 10.
race ( N_menu = "1M"
and age > 10
SELECT nom FROM ANIMAL
WHERE N_menu = "1M" AND age > 10;
7 - Donner les menus plus 2 fois moins riches en viande qu'en lgume?
qte_legume = 2*qte_viande (MENU)
SELECT * FROM MENU WHERE QTE_LEGUME = 2*QTE_VIANDE;
8 - Donner l'ge et l'origine des animaux qui sont amis de l'homme.
age,origine ( ANIMAL race = race origine = origine ( ami = "oui" FAMILLE) )
SELECT age, origine FROM ANIMAL a , FAMILLE f
WHERE race = race AND origine = origine AND AMI = "Oui";
9 - Numro de menu et quantit de viande pour les animaux qui ont une attitude amicale
envers l'homme.
N_M, qte_viande (MENU N_M = N_menu ANIMAL race = race origine = origine ( ami = "oui"
FAMILLE) )
SELECT age, origine FROM ANIMAL a , FAMILLE f, MENU m
WHERE [Link] = [Link] AND [Link] = [Link]
AND m.N_M = a.N_menu AND AMI = "Oui";
10 - Donner les noms des animaux qui sont grands parents.
nom (ANIMAL N_A=[Link] ENFANT E1 [Link] = [Link] ENFANT E2)
SELECT nom FROM ANIMAL, ENFANT E1, ENFANT E2
WHERE N_A = [Link] AND [Link] = [Link];
11 - Quantit de viande totale pour tous les menus.
SELECT SUM(qte_viande) FROM MENU;
12 - Nombre d'enfants pour chaque numro d'animal.
SELECT COUNT(*) FROM ENFANT GROUP BY parent;
13 - Lister les animaux qui dont on connat les deux parents.
enfant (ENFANT E1 [Link] = [Link] [Link] != [Link] ENFANT E2)
MSBM
2002-2003
SELECT enfant FROM ENFANT GROUP BY parent HAVING COUNT(*) =2;
14 - Donner la moyenne de la quantit de viande servie dans les menus.
SELECT AVG(qte_viande) FROM MENU;
15 - Donner les numros et la composition des menus plus riches en viande que la moyenne
(se servir de la requte prcdente pour faire cette requte).
SELECT N_M, qte_viande FROM MENU WHERE qte_viande > (SELECT
AVG(qte_viande) FROM MENU);
II La base de donnes Plantes botaniques
Pour rappel, le schma relationnel de la base sur les plantes botaniques est :
PLANTE (N, NOM_BOTANIQUE, FAMILLE, FLORAISON, COULEUR_FLEUR,
TOXICITE, HAUTEUR, ORIGINE, PATICULARITE, PHOTOGRAPHIE)
ESPECE (NOM, ORIGINE_MAJORITAIRE, SUPER_FAMILLE)
SYMPTOME (LIBELLE, DESCRIPTION)
PROVOQUE (N, LIBELLE, VICTIME, QUANTITE)
1 Lister les caractristiques des plantes prsentes dans la base de donnes.
nom_botanique, floraison, couleur_fleur, toxicite, hauteur, particularite, photographie (PLANTE)
SELECT * FROM PLANTE;
2 Donner les libells des symptmes prsents dans la base de donnes.
libelle (SYMPTOME)
SELECT libelle FROM SYMPTOME;
3 Donner le nom des plantes ainsi que leur couleur et leur hauteur.
nom,couleur_fleur, hauteur (PLANTE)
SELECT nom,couleur_fleur, hauteur FROM PLANTE;
4 Donner le nom et lorigine des plantes dont le nom commence par la lettre a.
nom, origine ( nom comme "a*" (PLANTE))
SELECT nom, origine FROM PLANTE WHERE nom LIKE "a*" ;
MSBM
2002-2003
5 Donner le nom et lorigine des plantes provenant dAmrique du Nord et dont le nom
contient la chane de caractre aster.
nom, origine ( nom comme "*aster*" origine = "Amrique du Nord" (PLANTE))
SELECT nom, origine FROM PLANTE
WHERE nom LIKE "*aster*" AND origine = "Amrique du Nord";
6 Donner le nom des plantes provenant dEurope et dont la hauteur est gnralement
suprieure 60cm.
nom ( hauteur >= 60 origine comme "*Europe*" (PLANTE))
SELECT nom FROM PLANTE
WHERE origine LIKE "*Europe*" AND hauteur >= 60;
7 Donner le nom des plantes qui sont toxiques.
nom ( toxicite = "Non connue" toxicite = "Non toxique" (PLANTE))
SELECT nom FROM PLANTE
WHERE toxicite = "Toxique non mortelle" OR toxicite = "Mortelle";
Ou bien
SELECT nom FROM PLANTE
WHERE toxicite IN ("Toxique non mortelle ", " Mortelle ");
Ou bien
SELECT nom FROM PLANTE WHERE toxicite = " Toxique non mortelle "
UNION
SELECT nom FROM PLANTE WHERE toxicite = " Mortelle ";
8 Donner le nom et la couleur des plantes qui sont toxiques.
nom, couleur_fleur ( toxicite = "Non connue" toxicite = "Non toxique" (PLANTE))
SELECT nom, couleur_fleur FROM PLANTE
WHERE toxicite = "Toxique non mortelle" OR toxicite = "Mortelle";
Ou bien
SELECT nom, couleur_fleur FROM PLANTE
WHERE toxicite IN ("Toxique non mortelle ", " Mortelle ");
Ou bien
SELECT nom, couleur_fleur FROM PLANTE WHERE toxicite = " Toxique non mortelle "
UNION
SELECT nom, couleur_fleur FROM PLANTE WHERE toxicite = " Mortelle ";
MSBM
2002-2003
9 Donner le nom des plantes toxiques qui ne sont pas originaires dEurope.
nom ( toxicite = "Non connue" toxicite = "Non toxique" origine non comme "*Europe*" (PLANTE))
SELECT nom FROM PLANTE
WHERE toxicite = "Toxique non mortelle" OR toxicite = "Mortelle"
AND origine NOT LIKE "*Europe*";
Ou bien
SELECT nom FROM PLANTE
WHERE toxicite IN ("Toxique non mortelle ", " Mortelle ")
AND origine NOT LIKE "*Europe*";
Ou bien
SELECT nom FROM PLANTE WHERE toxicite = " Toxique non mortelle " AND origine
NOT LIKE "*Europe*"
UNION
SELECT nom FROM PLANTE WHERE toxicite = " Mortelle "
AND origine NOT LIKE "*Europe*";
10 Donner le nom des plantes classes par ordre croissant de hauteur.
SELECT nom FROM PLANTE ORDER BY hauteur;
11 Donner le nom des espces des plantes toxiques.
famille ( toxicite = "Non connue" toxicite = "Non toxique" (PLANTE))
SELECT famille FROM PLANTE
WHERE toxicite IN ("Toxique non mortelle ", " Mortelle ");
12 Donner le nom des plantes toxiques et les symptmes associs.
nom, libelle ( toxicite = "Non connue" toxicite = "Non toxique" (PLANTE Pl
Pl.N = Pr.N
PROVOQUE Pr))
SELECT famille FROM PLANTE Pl, PROVOQUE Pr
WHERE Pl.N = Pr.N AND toxicite IN ("Toxique non mortelle ", " Mortelle ");
13 Donner le libell des symptmes observables chez lhomme.
libelle ( victime = "homme" (PROVOQUE))
SELECT libelle FROM PROVOQUE WHERE victime = "homme";
14 Sur quel type de victime agit le poison de la digitale pourpre ? Et celui de laster
amellus Blue King ?
15 Donner la hauteur des plantes qui ont des fleurs.
MSBM
2002-2003
hauteur ( floraison = "Oui" (PLANTE))
SELECT hauteur FROM PLANTE WHERE floraison = "Oui";
16 Combien compte-t-on de plantes dont on ne connat pas la toxicit dans la base ?
SELECT COUNT(*) FROM PLANTE WHERE toxicite = "Non Connue";
17 Combien y a-t-il de plantes dont les fleurs ont des teintes apparentes la couleur
bleue ?
SELECT COUNT(*) FROM PLANTE WHERE couleur_fleur LIKE "*bleu*";
18 Quelles sont les noms et les origines majoritaires des plantes qui ont une hauteur
moyenne suprieure la moyenne ?
SELECT nom, origine FROM PLANTE WHERE hauteur > SELECT AVG(hauteur) FROM
PLANTE);
19 Quelles sont les plantes que lon ne trouve que sur le continent amricain ?
SELECT * FROM PLANTE WHERE origine LIKE "Amrique" OR origine LIKE "Canada";
20 Quel est le nombre de plantes qui provoquent une paralysie respiratoire chez lhomme ?
SELECT COUNT (DISTINCT *) FROM PROVOQUE WHERE victime ="homme" AND
libelle = "paralysie respiratoire";
21 Quels sont les origines des plantes qui provoquent une asphyxie des animaux ?
origine ( libelle = "asphyxie"
and victime ="animal"
(PROVOQUE))
SELECT origine FROM PROVOQUE WHERE victime ="animal" AND libelle = "asphyxie";
22 Quelles sont les noms des plantes qui provoquent le plus de symptmes diffrents ?
SELECT [Link] FROM PLANTE Pl, PROVOQUE Pr
WHERE Pl.N = Pr.N
GROUP BY Pr.N HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM PROVOQUE
GROUP BY N ) ;