Td corrigé TD 4 : Langage algébrique et Langage SQL CORRECTION pdf

TD 4 : Langage algébrique et Langage SQL CORRECTION

TD 4 : Langage algébrique et Langage SQL CORRECTION. I La base .... Pour rappel, le schéma relationnel de la base sur les plantes botaniques est : PLANTE  ...




part of the document



OR age > 8;


6 - Donner les races des animaux dont le numéro de menu est 1 et l'âge est supérieur à 10.

( race (( N_menu = "1M" ( 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 légume?

( 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 - Numéro 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 a.race = f.race AND a.origine = f.origine
AND m.N_M = a.N_menu AND AMI = "Oui";

10 - Donner les noms des animaux qui sont grands parents.

( nom (ANIMAL È"N_A=E1.parent ENFANT E1 È"E1.enfant = E2.parent ENFANT E2)

SELECT nom FROM ANIMAL, ENFANT E1, ENFANT E2
WHERE N_A = E1.parent AND E1.enfant = E2.parent;

11 - Quantité de viande totale pour tous les menus.

SELECT SUM(qte_viande) FROM MENU;

12 - Nombre d'enfants pour chaque numéro d'animal.

SELECT COUNT(*) FROM ENFANT GROUP BY parent;

13 - Lister les animaux qui dont on connaît les deux parents.

( enfant (ENFANT E1 È"E1.enfant = E2.enfant '" E1.parent != E2.parent ENFANT E2)

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 numéros et la composition des menus plus riches en viande que la moyenne (se servir de la requête précédente pour faire cette requête).

SELECT N_M, qte_viande FROM MENU WHERE qte_viande > (SELECT AVG(qte_viande) FROM MENU);


II La base de données Plantes botaniques

Pour rappel, le schéma 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 caractéristiques des plantes présentes dans la base de données.

( nom_botanique, floraison, couleur_fleur, toxicite, hauteur, particularite, photographie (PLANTE)

SELECT * FROM PLANTE;

2 – Donner les libellés des symptômes présents dans la base de données.

( 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 l origine 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*" ;

5  Donner le nom et l origine des plantes provenant d Amérique du Nord et dont le nom contient la chaîne de caractère aster.

( nom, origine (Ã nom comme "*aster*"( origine = "Amérique du Nord" (PLANTE))

SELECT nom, origine FROM PLANTE
WHERE nom LIKE "*aster*" AND origine = "Amérique du Nord";

6  Donner le nom des plantes provenant d Europe et dont la hauteur est généralement supérieure à 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 ";

9 – Donner le nom des plantes toxiques qui ne sont pas originaires d’Europe.


( 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 classées par ordre croissant de hauteur.

SELECT nom FROM PLANTE ORDER BY hauteur;

11 – Donner le nom des espèces 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 symptômes associés.

( 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 symptômes observables chez l homme.

( 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 l aster amellus  Blue King  ?
15  Donner la hauteur des plantes qui ont des fleurs.

( hauteur (Ã floraison = "Oui" (PLANTE))

SELECT hauteur FROM PLANTE WHERE floraison = "Oui";

16  Combien compte-t-on de plantes dont on ne connaît 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 apparentées à 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 supérieure à la moyenne ?

SELECT nom, origine FROM PLANTE WHERE hauteur > SELECT AVG(hauteur) FROM PLANTE);

19 – Quelles sont les plantes que l’on ne trouve que sur le continent américain ?

SELECT * FROM PLANTE WHERE origine LIKE "Amérique" OR origine LIKE "Canada";

20 – Quel est le nombre de plantes qui provoquent une paralysie respiratoire chez l’homme ?

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" ( 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 symptômes différents ?

SELECT Pl.nom FROM PLANTE Pl, PROVOQUE Pr
WHERE Pl.N° = Pr.N°
GROUP BY Pr.N° HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM PROVOQUE
GROUP BY N° ) ;



MSBM 2002-2003


 :