1 - Ent Paris 13

Faire des recherches dans
une base de données
(requêtes)
84
Requêtes
●
●
Les bases de données relationnelles permettent de
rechercher des données très rapidement dans un très
grand volume de données
Pour effectuer une requête (= recherche) dans une
base de données en SQL :
SELECT attribut_1, attribut_2, attribut_3...
FROM table_1, table_2, table_3...
WHERE condition;
●
WHERE condition est optionnel
●
SELECT retourne une table avec les résultats
85
Requêtes
●
Exemple : pour afficher la totalité d'une table :
SELECT * FROM patient;
86
Requêtes
●
Exemple : pour afficher un attribut (= une
colonne) d'une table :
SELECT nom FROM patient;
87
Requêtes
●
●
Exemple : pour rechercher seulement certaines
lignes, il faut utiliser WHERE condition :
SELECT * FROM patient WHERE nom = "Lamy";
Pour avoir seulement certains attributs :
SELECT taille, poids FROM patient WHERE nom = "Lamy";
88
Requêtes
●
●
Exemple : pour rechercher les noms des patients de plus de 70 kg :
SELECT nom FROM patient WHERE poids > 70;
Pour rechercher les noms des patients en surpoids (IMC > 27) :
SELECT nom FROM patient WHERE poids / (taille * taille) > 0.0027;
89
Requêtes
●
Pour rechercher la valeur NULL, on utilise IS NULL
●
Pour rechercher des valeurs autres que NULL, on utilise IS NOT NULL
●
Exemple : pour rechercher les noms des patients en ambulatoire
(= sans lit) :
SELECT nom FROM patient WHERE ID_lit IS NULL;
90
Requêtes
●
●
LIKE permet de rechercher des motifs dans des chaînes de caractères :
●
% remplace zéro, un ou plusieurs caractères
●
_ remplace un et un seul caractère
●
[…] restreint à certains caractères (ex : [0-9] pour un chiffre)
Exemple : pour rechercher les patients dont le nom commence par Ben :
SELECT * FROM patient WHERE nom LIKE "Ben%";
●
LIKE fonctionne aussi sur les dates !
●
Exemple : pour rechercher les patients nés en Février :
91
Requêtes
●
●
IN permet de rechercher une valeur parmi un ensemble
de valeurs possibles (plusieurs noms,...)
Exemple : pour rechercher les patients dont le nom est
X ou Y :
SELECT * FROM patient WHERE nom IN ("X", "Y");
92
Requêtes
●
Pour éliminer les doublons : SELECT DISTINCT
93
Requêtes
●
Pour trier les résultats : ORDER BY attribut
●
ASC et DESC permettent d'indiquer l'ordre :
ascendant (par défaut) ou descendant
94
Requêtes
95
Requêtes
●
Opérations :
●
+, -, * (multiplication), / (division)
●
+ permet aussi de concaténer des chaînes de caractères
–
●
Comparaisons :
●
●
Ex : "La" + "my"
=, >, <, >= (supérieur ou égal),
<= (inférieur ou égal), != (différent de)
LIKE
IN
Opérateurs logiques :
●
AND, OR, NOT
96
Requêtes
●
SQL propose aussi plusieurs fonctions :
●
AVG() : moyenne (d'une colonne)
●
MIN() : minimum (d'une colonne)
●
MAX() : maximum (d'une colonne)
●
SUM() : somme (d'une colonne)
●
COUNT() : compte le nombre de ligne
97
Requêtes
●
●
Lorsque l'on utilise les fonctions COUNT(x), AVG(x),
MIN(x), MAX(x) ou SUM(x), GROUP BY permet de
grouper ensembles les lignes ont un attribut en commun
Exemple : rechercher le nombre de lit par service :
SELECT service, COUNT(ID) FROM lit
GROUP BY service;
98
Requêtes
Autres fonctions SQL :
●
●
●
●
●
●
●
●
ROUND(x) : arrondi un nombre à l'entier le plus proche
CURRENT_DATE() : retourne la date d'aujourd'hui
GREATEST(x, y, z,...) : retourne la plus grande valeur parmi x, y,
z,...
LEAST(x, y, z,...) : retourne la plus petite valeur parmi x, y, z,...
LENGTH(texte) : retourne le nombre de caractères d'une chaîne
de caractère
UPPER(texte) : retourne une chaîne de caractères en majuscule
LOWER(texte) : retourne une chaîne de caractères en minuscule
SUBSTR(texte, debut, longueur) : retourne une partie d'une
chaîne de caractères, en commençant à debut et en prenant
longueur caractères
–
NB cela marche aussi sur les dates : ex pour afficher les années de
naissance :
SELECT SUBSTR(date_de_naissance, 1, 4) FROM patient;
99
Requêtes
●
Quel est le poids moyen des patients ?
Table patient
Identifiant
Nom
Date de
naissance
Taille
Poids
1
LAMY
18/06/1979
170
62
2
X
25/03/1957
165
54,5
3
Y
04/01/1982
180
90
100
Requêtes
●
Quel est le poids moyen des patients ?
SELECT AVG(poids) FROM patient;
Table patient
Identifiant
Nom
Date de
naissance
Taille
Poids
1
LAMY
18/06/1979
170
62
2
X
25/03/1957
165
54,5
3
Y
04/01/1982
180
90
101
Requêtes
●
Quel est le poids moyen des patients, en
séparant les hommes et les femmes ?
Table patient
Identifiant
Nom
Sexe
Date de
naissance
Taille
Poids
1
LAMY
Homme
18/06/1979
170
62
2
X
Femme
25/03/1957
165
54,5
3
Y
Homme
04/01/1982
180
90
102
Requêtes
●
Quel est le poids moyen des patients, en
séparant les hommes et les femmes ?
SELECT sexe, AVG(poids) FROM patient
GROUP BY sexe;
Table patient
Identifiant
Nom
Sexe
Date de
naissance
Taille
Poids
1
LAMY
Homme
18/06/1979
170
62
2
X
Femme
25/03/1957
165
54,5
3
Y
Homme
04/01/1982
180
90
103
Requêtes avec jointure
●
●
●
Pour l'instant nous n'avons fait que des recherches à
l'intérieur d'une seule table !
Pour faire des recherches sur deux tables (ou plus) qui
ont une relation, il faut faire des jointures entre les
tables
Exemple : dans quel service est monsieur X ?
●
=> on a besoin de regarder les tables patient et lit
Table lit
Table patient
ID
nom
date_de_
naissance
ID_lit
1
X
18/06/1979
2
2
Y
25/03/1957
7
3
Z
04/01/1982
1
ID
service
1
Cardiologie
2
Pneumologie
104
Requêtes avec jointure
Table lit
Table patient
ID
nom
date_de_
naissance
ID_lit
1
X
18/06/1979
2
2
Y
25/03/1957
7
3
Z
04/01/1982
1
●
ID
service
1
Cardiologie
2
Pneumologie
Exemple : dans quel service est monsieur X ?
SELECT ID_lit FROM patient WHERE nom = "X";
=> 2
SELECT service FROM lit WHERE ID = 2;
=> Pneumologie
105
Requêtes avec jointure
Table lit
Table patient
ID
nom
date_de_
naissance
ID_lit
1
X
18/06/1979
2
2
Y
25/03/1957
7
3
Z
04/01/1982
1
●
ID
service
1
Cardiologie
2
Pneumologie
Exemple : dans quel service est monsieur X ?
SELECT lit.service
FROM patient, lit
WHERE patient.nom = "X" AND lit.ID = patient.ID_lit;
106
Requêtes avec jointure
Table lit
Table patient
ID
nom
date_de_
naissance
ID_lit
1
X
18/06/1979
2
2
Y
25/03/1957
7
3
Z
04/01/1982
1
●
ID
service
1
Cardiologie
2
Pneumologie
Exemple : dans quel service est monsieur X ?
SELECT lit.service
On a besoin des tables patient et lit
FROM patient, lit
WHERE patient.nom = "X" AND lit.ID = patient.ID_lit;
107
Requêtes avec jointure
Table lit
Table patient
ID
nom
date_de_
naissance
ID_lit
1
X
18/06/1979
2
2
Y
25/03/1957
7
3
Z
04/01/1982
1
●
ID
service
1
Cardiologie
2
Pneumologie
Exemple : dans quel service est monsieur X ?
SELECT lit.service
On recherche le service du lit
FROM patient, lit
WHERE patient.nom = "X" AND lit.ID = patient.ID_lit;
108
Requêtes avec jointure
Table lit
Table patient
ID
nom
date_de_
naissance
ID_lit
1
X
18/06/1979
2
2
Y
25/03/1957
7
3
Z
04/01/1982
1
●
ID
service
1
Cardiologie
2
Pneumologie
Exemple : dans quel service est monsieur X ?
SELECT lit.service
FROM patient, lit
Le nom du patient est X
ET
Le lit est celui du patient
WHERE patient.nom = "X" AND lit.ID = patient.ID_lit;
109
Requêtes avec jointure
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Rechercher toutes les séquences de gènes dans le
génome humain
110
Requêtes avec jointure
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Rechercher toutes les séquences de gènes dans le génome humain
SELECT gene.sequence
FROM espece, gene
WHERE espece.nom = "Homme" AND gene.ID_espece = espece.ID;
111
Requêtes avec jointure
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Rechercher à quelles espèces appartient le gène
BMP2
112
Requêtes avec jointure
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Rechercher à quelles espèces appartient le gène
BMP2
SELECT espece.nom
FROM espece, gene
WHERE gene.nom = "BMP2" AND gene.ID_espece = espece.ID;
113
Requêtes avec jointure
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Rechercher le nom latin du poisson zèbre
114
Requêtes avec jointure
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Rechercher le nom latin du poisson zèbre
SELECT nom_latin
FROM espece
WHERE nom = "Poisson zèbre";
115
Requêtes avec jointure
Table espèce
Table environnement
Table habite
ID
nom
nom_latin
ID_espece
ID_environnement
ID
nom
1
Homme
Homo sapiens
1
1
1
2
Poisson
zèbre
Danio rerio
1
2
Forêt
tropicale
2
2
2
Mangrove
●
Pour les relations *-*, il faut faire la jointure sur 3 tables
●
Dans quels environnements vit l'être humain ?
116
Requêtes avec jointure
Table espèce
●
Table environnement
Table habite
ID
nom
nom_latin
ID_espece
ID_environnement
ID
nom
1
Homme
Homo sapiens
1
1
1
2
Poisson
zèbre
Danio rerio
1
2
Forêt
tropicale
2
2
2
Mangrove
Dans quels environnements vit l'être humain ?
SELECT environnement.nom
FROM espece, environnement, habite
WHERE espece.nom = "Homme"
AND habite.ID_espece = espece.ID
AND environnement.ID = habite.ID_environnement;
117
Requêtes avec jointure
Table espèce
Table environnement
Table habite
ID
nom
nom_latin
ID_espece
ID_environnement
ID
nom
1
Homme
Homo sapiens
1
1
1
2
Poisson
zèbre
Danio rerio
1
2
Forêt
tropicale
2
2
2
Mangrove
●
Quelles sont les espèces qui vivent dans la
mangrove ?
118
Requêtes avec jointure
Table espèce
Table environnement
Table habite
ID
nom
nom_latin
ID_espece
ID_environnement
ID
nom
1
Homme
Homo sapiens
1
1
1
2
Poisson
zèbre
Danio rerio
1
2
Forêt
tropicale
2
2
2
Mangrove
●
Quelles sont les espèces qui vivent dans la mangrove ?
SELECT espece.nom
FROM espece, habite, environnement
WHERE environnement.nom = "Mangrove"
AND habite.ID_environnement = environnement.ID
AND habite.ID_espece = espece.ID;
119
Requêtes avec jointure
Table espèce
Table environnement
Table habite
ID
nom
nom_latin
ID_espece
ID_environnement
ID
nom
1
Homme
Homo sapiens
1
1
1
2
Poisson
zèbre
Danio rerio
1
2
Forêt
tropicale
2
2
2
Mangrove
●
Quel est le nombre d'espèces qui vivent dans
chaque environnement ?
120
Requêtes avec jointure
Table espèce
Table environnement
Table habite
ID
nom
nom_latin
ID_espece
ID_environnement
ID
nom
1
Homme
Homo sapiens
1
1
1
2
Poisson
zèbre
Danio rerio
1
2
Forêt
tropicale
2
2
2
Mangrove
●
Quel est le nombre d'espèces qui vivent dans chaque environnement ?
SELECT environnement.nom, COUNT(habite.ID_espece)
FROM habite, environnement
WHERE habite.ID_environnement = environnement.ID
GROUP BY environnement.ID;
121
Requêtes avec jointure
Table patient
ID
Nom
Date de
naissance
Taille
ID_conjoint
1
Lamy
18/06/1979
170
NULL
2
Juliette
25/03/1957
165
3
3
Roméo
04/01/1955
180
2
1
est_marié_à
1
●
Cas des relations au sein d'une même table
●
Quel est le nom de la femme de monsieur Roméo ?
●
Il y a une seule table, mais la recherche porte sur deux patients :
Roméo et sa femme !
122
Requêtes avec jointure
ID
Nom
Date de
naissance
Taille
ID_conjoint
1
Lamy
18/06/1979
170
NULL
2
Juliette
25/03/1957
165
3
3
Roméo
04/01/1955
180
2
1
1
est_marié_à
Table mari
ID
Nom
Date de
naissance
Taille
ID_conjoint
1
Lamy
18/06/1979
170
NULL
2
Juliette
25/03/1957
165
3
3
Roméo
04/01/1955
180
2
Table femme
●
Cas des relations au sein d'une même table
●
Quel est le nom de la femme de monsieur Roméo ?
●
Il y a une seule table, mais la recherche porte sur deux patients :
Roméo et sa femme !
123
ID
Nom
Date de
naissance
Taille
ID_conjoint
1
Lamy
18/06/1979
170
NULL
2
Juliette
25/03/1957
165
3
3
Roméo
04/01/1955
180
2
1
1
est_marié_à
Table mari
ID
Nom
Date de
naissance
Taille
ID_conjoint
1
Lamy
18/06/1979
170
NULL
2
Juliette
25/03/1957
165
3
3
Roméo
04/01/1955
180
2
Table femme
●
Quel est le nom de la femme de monsieur Roméo ?
SELECT femme.nom
On travaille sur deux lignes de la table patient :
on appelle ces deux lignes « mari » et « femme »
FROM patient mari, patient femme
WHERE mari.nom = "Roméo"
AND mari.ID_conjoint = femme.ID;
124
Requêtes avec jointure
Table parasitisme
Table parasite
ID
nom
nom_latin
ID_parasite
ID_hote
1
Homme
Homo sapiens
2
1
2
Palludisme
Plasmodium falciparum
2
3
3
Anophèle
Anopheles gambiae
3
1
*
parasite
*
●
ID
nom
nom_latin
1
Homme
Homo sapiens
2
Palludisme
Plasmodium falciparum
3
Anophèle
Anopheles gambiae
Table hôte
Quels sont les parasites de l'homme ?
125
Table parasitisme
Table parasite
ID
nom
nom_latin
ID_parasite
ID_hote
1
Homme
Homo sapiens
2
1
2
Palludisme
Plasmodium falciparum
2
3
3
Anophèle
Anopheles gambiae
3
1
*
parasite
*
●
ID
nom
nom_latin
1
Homme
Homo sapiens
2
Palludisme
Plasmodium falciparum
3
Anophèle
Anopheles gambiae
Table hôte
Quels sont les parasites de l'homme ?
SELECT parasite.nom
FROM espece parasite, espece hote, parasitisme
WHERE hote.nom = "Homme"
AND parasitisme.ID_hote = hote.ID
AND parasitisme.ID_parasite = parasite.ID;
126
Requêtes avec jointure
Table parasitisme
Table parasite
ID
nom
nom_latin
ID_parasite
ID_hote
1
Homme
Homo sapiens
2
1
2
Palludisme
Plasmodium falciparum
2
3
3
Anophèle
Anopheles gambiae
3
1
*
parasite
*
●
ID
nom
nom_latin
1
Homme
Homo sapiens
2
Palludisme
Plasmodium falciparum
3
Anophèle
Anopheles gambiae
Table hôte
Lister les noms des espèces parasites ?
127
Table parasitisme
Table parasite
ID
nom
nom_latin
ID_parasite
ID_hote
1
Homme
Homo sapiens
2
1
2
Palludisme
Plasmodium falciparum
2
3
3
Anophèle
Anopheles gambiae
3
1
*
parasite
*
●
ID
nom
nom_latin
1
Homme
Homo sapiens
2
Palludisme
Plasmodium falciparum
3
Anophèle
Anopheles gambiae
Table hôte
Lister les noms des espèces parasites ?
SELECT DISTINCT parasite.nom
FROM espece parasite, parasitisme
WHERE parasitisme.ID_parasite = parasite.ID;
128
Sous-requêtes
●
●
Il est possible d'imbriquer plusieurs requêtes
●
Les requêtes imbriquées sont placées entre parenthèses
●
Elles sont remplacées par la valeur qu'elles retournent
Exemple :
●
Quel est le patient le plus gros ?
SELECT MAX(poids) FROM patient;
=> 90
SELECT * FROM patient WHERE poids = 90;
=>
129
Sous-requêtes
●
Exemple :
●
Quel est le patient le plus gros ?
SELECT MAX(poids) FROM patient;
SELECT * FROM patient WHERE poids = 90;
SELECT * FROM patient WHERE poids = (SELECT MAX(poids) FROM patient);
●
Attention, la sous-requête ne doit retourner qu'une seule valeur !
130
Sous-requêtes
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Quel est la longueur de la plus longue séquence d'ADN ?
131
Sous-requêtes
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Quel est la longueur de la plus longue séquence d'ADN ?
SELECT MAX(LENGTH(sequence)) FROM gene;
132
Sous-requêtes
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Quel est le nom du gène le plus long ?
133
Sous-requêtes
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Quel est le nom du gène le plus long ?
SELECT nom FROM gene WHERE LENGTH(sequence) =
(SELECT MAX(LENGTH(sequence)) FROM gene);
134
Sous-requêtes
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Quel est l'espèce qui a le gène le plus long ?
135
Sous-requêtes
Table gène
Table espèce
ID
nom
nom latin
ID
nom
sequence
ID_espece
1
Homme
Homo sapiens
1
atggcactca...
1
2
Poisson
zèbre
Danio rerio
Aldostérone
synthase
2
Enveloppe
du VIH
tgtacaagaccc...
1
3
BMP2
gtccgctaa...
2
●
Quel est l'espèce qui a le gène le plus long ?
SELECT espece.nom
FROM gene, espece
WHERE gene.ID_espece = espece.ID
AND LENGTH(gene.sequence) =
(SELECT MAX(LENGTH(sequence)) FROM gene);
136
Mettre à jour des lignes
●
●
●
●
Pour mettre à jour ou modifier des lignes dans une table :
UPDATE nom_de_la_table
SET attribut_1 = valeur_1, attribut_2 = valeur_2...
WHERE conditions;
Le WHERE fonctionne de la même manière que pour la
commande SELECT
Exemple : Mettre à jour le poids et la taille d'un enfant dans
la table patient :
UPDATE patient
SET taille = 154, poids = 51
WHERE nom = "Calvin";
Attention, si les conditions sont vérifiées pour plusieurs
lignes, toutes ces lignes sont modifiées !
137
Supprimer des lignes
●
●
●
●
Pour supprimer des lignes dans une table :
DELETE FROM nom_de_la_table
WHERE conditions;
Le WHERE fonctionne de la même manière que pour la
commande SELECT
Exemple : Supprimer monsieur X de la table patient :
DELETE FROM patient
WHERE nom = "X";
Attention, si les conditions sont vérifiées pour plusieurs
lignes, toutes lignes vérifiant les conditions sont
supprimées !
138
Permissions
●
Les SGBDR gèrent les permissions :
●
●
●
●
●
Pour chaque utilisateur
Pour chaque colonne, chaque table et chaque base de
données
Pour chaque type d'opération : lecture (SELECT), mise à
jour (UPDATE), création de table (CREATE TABLE),...
GRANT permet de donner des permissions et REVOKE de
les retirer
GRANT ALL PRIVILEGES ON base_de_données TO
utilisateur@machine;
Généralement, l'administrateur système a tous les droits sur
la base de données, et il accorde les permissions aux
différents utilisateurs
139
Sauvegarder une base en SQL
●
●
●
●
Le programme mysqldump permet de convertir une base de
données en un (gros !) fichier SQL
mysqldump nom_de_la_base_de_données > fichier.sql
Ce fichier SQL peut ensuite être exécuté par MySQL
mysql < fichier.sql
Attention, ce sont des programmes à utiliser en ligne de
commande Unix, ce ne sont pas des commandes SQL !
Idéal pour :
●
faire une sauvegarde (un « dump ») d'une base de données
●
transférer une base d'un ordinateur à un autre
●
transférer une base d'un logiciel de SGBDR à un autre logiciel
140
Exercice 1
●
●
Nous allons reprendre la base de
données sur les dinosaure
Écrire les requêtes SQL pour
répondre aux questions
suivantes :
●
●
●
●
●
Quelle est la taille du tyranosaure ?
Quelle est la taille moyenne des
dinosaures ?
Table famille
Table vecu_a_lepoque
id
nom
id_dinosaure
id_epoque
1
Pterodactylidae
1
1
2
...
...
...
Table dinosaure
id
nom
taille
id_famille
1
Ptéranodon
7,5
1
2
...
Table epoque
id
nom
début
fin
1
Jurassique supérieur
-161
-145
2
...
Quelle sont les dinosaures de plus
de 20 mètres ?
Combien y a-t-il de dinosaures
dans la base ?
Quelle est la date de début et de fin
du crétacé ?
141
Exercice 1
●
Écrire les requêtes SQL pour
répondre aux questions
suivantes :
●
●
●
●
●
●
Quelle est la famille du ptéranodon ?
Quelle est la taille moyenne des
pterodactylidae ?
À quelle(s) époque(s) a vécu le
ptéranodon ?
Quels dinosaures ont vécu au
jurassique supérieur ?
Table famille
Table vecu_a_lepoque
id
nom
id_dinosaure
id_epoque
1
Pterodactylidae
1
1
2
...
...
...
Table dinosaure
id
nom
taille
id_famille
1
Ptéranodon
7,5
1
2
...
Table epoque
id
nom
début
fin
1
Jurassique supérieur
-161
-145
2
...
À quelle(s) époque(s) ont vécu les
pterodactylidae ?
Quelle est la taille du plus grand
dinosaure de la même famille que le
ptéranodon ?
142
Exercice 2
●
●
●
Nous avons construit une base de données pour un
laboratoire d'analyses médicales. Ce laboratoire
emploie plusieurs biologistes, qui effectuent des
prélèvements chez des patients, et ensuite analysent
ces prélèvements.
Les biologistes sont identifiés par leur nom et leur
prénom, et les patients par leur nom, leur prénom et leur
date de naissance.
Pour chaque analyse, la base doit indiquer le patient, le
ou les biologistes, la date d'analyse, le nom de l'analyse
et le résultat (sous forme de chiffre).
146
Exercice 2
Table biologiste
id
nom
prenom
1
Durand
Leïla
2
...
Table analyse_réalisé_par
id_biologiste
id_analyse
1
1
2
...
Table analyse
id
date
nom
resultat
id_patient
1
2011/01/31
Glycémie
0,9
1
2
...
Table patient
id
nom
prenom
date_de_naissance
1
Martin
Lefèbre
1958-04-05
2
...
147
Exercice 2
●
●
La base de données a été remplie avec les données du
laboratoire.
Écrire la requête SQL pour afficher la liste des glycémies du
patient Maurice Lefèbre, avec la date de chaque glycémie,
et en triant par date.
148
Exercice 2
●
●
Écrire la requête SQL pour afficher la liste des glycémies du
patient Maurice Lefèbre, avec la date de chaque glycémie,
et en triant par date.
SELECT analyse.date, analyse.resultat
FROM patient, analyse
WHERE (patient.prenom = "Maurice") AND
(patient.nom="Lefèbre") AND (analyse.nom = "glycémie")
AND (analyse.id_patient = patient.id)
ORDER BY analyse.date;
149
Exercice 3
●
●
●
●
On a créé une base de données des différentes espèces
d'orchidées (plus de 25 000 !) avec MySQL.
La base de données contient des informations sur les
espèces d'orchidées, les genres d'orchidées, et les milieux
dans lesquels les orchidées poussent.
Pour chaque espèce d'orchidée, on a indiqué son nom, ainsi
que les critères suivants utilisés pour les
distinguer : couleur des fleurs, nombre
d'étamine, présence de rhizome.
Les milieux peuvent être humides ou secs,
ensoleillés ou non.
150
Exercice 3
Table espece
id
nom
rhyzome
nb_etamines
couleur
id_genre
1
Épipactis
des marais
1
1
pourpre
1
2
...
Table pousse_dans
Table genre
id_espece
id_milieu
id
nom
1
1
1
Épipactis
2
...
2
...
Table milieu
id
nom
humide
ensoleille
1
Marais
1
0
2
...
151
Exercice 3
●
●
La base de données a ensuite été remplie avec des données
issues de différents livres de botanique.
Écrire les requêtes SQL pour répondre aux questions
suivantes:
● La vanille a-t-elle un rhizome ?
●
●
●
Quel est(sont) le(s) nom(s) d'une orchidée
pourpre avec un rhizome et 2 étamines ?
Combien y a-t-il d'orchidées dans la base ?
Quel est le plus grand nombre d'étamine
parmi les orchidées ?
152
Exercice 3
●
●
●
●
●
●
●
●
La vanille a-t-elle un rhizome ?
SELECT rhizome FROM espece WHERE nom = "vanille";
Quel est(sont) le(s) nom(s) d'une orchidée pourpre avec un
rhizome et 2 étamines ?
SELECT nom FROM espece WHERE (couleur = "pourpre")
AND (rhizome = 1) AND (nb_etamine = 2);
Combien y a-t-il d'orchidées dans la base ?
SELECT COUNT(id) FROM espece;
Quel est le plus grand nombre d'étamine parmi les
orchidées ?
SELECT MAX(nb_etamines) FROM espece;
153
Exercice 3
●
Écrire les requêtes SQL pour répondre aux questions
suivantes:
●
À quel genre appartient la vanille ?
●
Quels sont les noms des orchidées du genre épipactis ?
●
Quels sont les milieux ensoleillés et secs ?
●
Dans quel(s) milieu(x) pousse la vanille ?
154
Exercice 3
●
●
●
●
●
●
●
●
À quel genre appartient la vanille ?
SELECT genre.nom FROM espece, genre WHERE (espece.nom =
"vanille") AND (espece.id_genre = genre.id);
Quels sont les noms des orchidées du genre épipactis ?
SELECT espece.nom FROM espece, genre WHERE (genre.nom =
"epipactis") AND (espece.id_genre = genre.id);
Quels sont les milieux ensoleillés et secs ?
SELECT milieu.nom FROM milieu WHERE (humide = 0) AND
(ensoleille = 1);
Dans quel(s) milieu(x) pousse la vanille ?
SELECT milieu.nom FROM espece, milieu, pousse_dans WHERE
(espece.nom = "vanille") AND (pousse_dans.id_espece =
espece.id) AND (pousse_dans.id_milieu = milieu.id);
155