Exercices SQL
Exercice 1:
Partie B
On considère maintenant la base de données relationnelle suivante :
Figure 1. Schéma
relationnel La table personnel est composée :
• du matricule unique du pompier ;
• de son nom ;
• de sa qualification (selon le codage vu avant) ;
• d’un attribut qui précise s’il est actif (1) ou inactif (0).
La table agres est composée :
• de son identifiant ;
• du jour où l’agrès se tient prêt à intervenir ;
• du type de véhicule ;
• de l’identifiant du chef d’agrès ;
• de l’identifiant du conducteur ;
• de l’identifiant du chef d’équipe si le véhicule le nécessite, sinon le
champ est à NULL ;
• de l’identifiant de l’équipier si le véhicule le nécessite, sinon le
champ est à NULL.
La table moyen est composée :
• de l’identifiant de l’agrès appelé sur intervention ;
• de l’identifiant de l’intervention.
La table intervention est composée :
• de son identifiant ;
• du jour de début d’intervention (sauf pour les longues
interventions où il correspond au jour de l’agrès) ;
• de l’heure de début d’intervention.
On rappelle que COUNT(*) permet de compter le nombre de lignes
extraites lors d’une requête. Par exemple, pour afficher le nombre de
personnes dans la table personnel, on exécute la requête :
SELECT COUNT(*) FROM personnel;
DISTINCT permet de retirer les doublons des réponses. Par exemple, pour
afficher tous les noms distincts de la table personnel, on exécute la
requête :
SELECT DISTINCT(nom) FROM personnel;
On considère l’extrait de la base de données ci-dessous :
personnel
matricule nom qualif actif
10 ‘Sam’ 3 1
16 ‘Charlot’ 1 0
31 ‘Red’ 23 0
83 ‘Vaillante’ 7 1
2501 ‘Marco’ 1 1
2674 ‘Aicha’ 23 1
3004 ‘Fatou’ 7 1
4044 ‘Abdel’ 19 1
4671 ‘Mamadou’ 17 1
personnel
5301 ‘Zoe’ 17 1
7450 ‘Medhi’ 3 1
8641 ‘Gaia’ 1 1
8678 ‘Kevin’ 17 0
8682 ‘Marie’ 1 1
9153 ‘Fred’ 23 1
moyen
idagres idinter
0 0
2 3
2 4
3 4
4 4
9 5
17 6
22 7
23 8
24 8
24 9
intervention
id jour heure
0 ‘2023-11-21’ ‘12:32:21’
1 ‘2023-11-22’ ‘22:20:00’
2 ‘2023-12-17’ ‘23:17:30’
3 ‘2024-02-15’ ‘01:44:06’
4 ‘2024-02-15’ ‘12:15:00’
5 ‘2024-03-02’ ‘04:58:12’
intervention
6 ‘2024-03-27’ ‘13:07:18’
7 ‘2024-05-31’ ‘05:17:12’
8 ‘2024-06-11’ ‘05:38:17’
9 ‘2024-06-11’ ‘15:08:56’
10 ‘2024-06-18’ ‘07:42:33’
agres
idconducteu
id jour vehicule idchefagres idchefA idequipierA
r
‘2023-
0 ‘VSAV’ 83 9153 NULL 10
11-21’
‘2024-
2 ‘VSAV’ 2674 4044 NULL 8641
02-15’
‘2024-
3 ‘FPT’ 9153 5301 8682 2501
02-15’
‘2024-
4 ‘VSAV’ 83 4671 NULL 7450
02-15’
‘2024-
7 ‘VSAV’ 9153 3004 NULL 2501
02-29’
‘2024-
9 ‘FPT’ 2674 5301 8682 8641
03-02’
‘2024-
12 ‘VTU’ 3004 5301 NULL NULL
03-21’
‘2024-
17 ‘VSAV’ 3004 8682 NULL 10
03-27’
‘2024-
18 ‘VSAV’ 9153 5301 NULL 10
03-27’
‘2024-
22 ‘FPT’ 9153 4044 7450 8641
05-31’
‘2024-
23 ‘VTU’ 83 2674 NULL NULL
06-11’
‘2024-
24 ‘VSAV’ 3004 4044 NULL 7450
06-11’
2. Expliquer la différence entre une clé primaire et une clé étrangère.
3. Expliquer pourquoi la requête suivante génère une erreur pour
l’extrait de données.
INSERT INTO moyen (idagres, idinter) VALUES (1,5);
4. Proposer une requête SQL qui met à jour l’heure de
l’intervention du « 15 février 2024 de 01 heure 44 minutes et
06 secondes » à « 10 heures 44 minutes et 06 secondes ».
5. Préciser le résultat de la requête suivante pour l’extrait de
données.
SELECT nom FROM personnel
WHERE actif = 0;
6. Proposer une requête SQL qui permet d’afficher les noms des
personnels conducteurs actifs. On notera qu’un conducteur
possède un attribut qualif supérieur ou égal à 16.
7. Écrire l’affichage obtenu après exécution des deux requêtes
ci-dessous sur l’extrait de la base de données. Expliquer ce
que chacune des requêtes affiche en général.
Requête A
SELECT COUNT(*) FROM agres
WHERE jour = T2024-03-27T;
Requête B
SELECT COUNT(*) FROM moyen AS m
INNER JOIN agres AS a ON [Link] = [Link]
WHERE [Link] = T2024-03-27T;
8. Proposer une requête qui renvoie sans répétition tous les
noms des chefs d’agrès assignés à un véhicule le 15 février
2024.
9. Proposer une requête qui renvoie sans répétition tous les
noms des chefs d’agrès engagés en intervention le 11 juin
2024.
Exercice 2
Cet exercice porte sur le langage SQL et les bases de données.
Un pharmacien nouvellement installé décide de créer son propre système
de gestion des médicaments qu’il délivre à ses clients.
Pour sa base de données relationnelle, il a déjà élaboré la première
relation à l’aide des données indiquées sur les cartes vitales de ses deux
premiers clients :
client (id_client : INT, nom_client : VARCHAR(30),
prenom_client : VARCHAR(30), num_secu_sociale : VARCHAR(15))
client
id_client nom_client prenom_client num_secu_sociale
1 Martin Sophie 202103812326129
2 Dufour Marc 105073817009595
1. Écrire le résultat de l’exécution de la requête SQL suivante :
SELECT nom_client, prenom_client
FROM client
ORDER BY nom_client;
Pour écrire la relation medicament, il doit utiliser les informations fournies
par la notice des médicaments. En voici une ci-dessous :
Figure 1. Informations extraites de la notice du médicament Paracétamol
1 gramme CP.
La relation medicament suivante a été obtenue à l’aide de ces notices :
medicament (id_medic : INT, nom_medic : VARCHAR(30),
categorie : VARCHAR(20), conditionnement : INT,
quantite : INT, prix : FLOAT)
La table des médicaments de son officine est
présentée ci-dessous.
medicament
id_medic nom_medic categorie condition quantite prix
nement
1 Paracétamol antalgique 8 50 3,50
1 gramme
CP
2 Acide antalgique 8 20 2,30
acétylsalicyliq
ue
3 Gel désinfecta 1 300 2,30
hydroalcooliq nt
ue 100 ml
4 Acide vitamine 10 450 5,50
ascorbique
2. Écrire une requête SQL permettant d’afficher les noms de tous les
médicaments dont le prix est strictement inférieur à 3 euros.
Madame Martin présente au pharmacien une
nouvelle ordonnance :
Figure 2. Ordonnance de Madame Sophie Martin.
Il saisit les informations de cette ordonnance dans la relation ordonnance,
chaque médicament prescrit correspondant à un enregistrement dans la
table ci-dessous.
ordonnance
id_ordo id_client date_ordo id_medic nb_boites
6 2 2023-11- 2 2
29
7 1 2023-12- 1 …
13
8 1 2023-12- 4 …
13
3. Ecrire une requête SQL permettant d’ajouter les informations de la
carte vitale de sa troisième cliente présentée ci-dessous :
Figure 3. Image de la carte vitale extraite de la page wikipédia
Source : d’après [Link] ([Link])
4. Donner les attributs qui doivent être déclarés comme clés
étrangères de la relation ordonnance et en préciser l’utilité.
5. Indiquer, pour les lignes 7 et 8 de la table ordonnance, le nombre
de boites prescrites.
6. Écrire la requête SQL mettant à jour la quantité du médicament
Acide ascorbique en stock dans l’officine du pharmacien suite au
passage de Madame Martin.
7. Calculer le coût total des médicaments fournis à Madame Martin (on
ne demande pas d’écrire une requête ici, mais de calculer le coût
total en justifiant le calcul).
8. Écrire la requête SQL permettant d’afficher le nom du médicament
pour l’ordonnance ayant l’id_ordo numéro 6.
Exercice 3
Partie B
Pour améliorer sa mémorisation sur le long terme, l’étudiante décide de
mettre en œuvre le concept des boites de Leitner. Dans cette méthode, il
s’agit d’espacer dans le temps la révision des flashcards si l’étudiante
répond correctement. Elle imagine donc une base de données qui lui
permettra de conserver pour chaque question la date à laquelle elle doit
de nouveau être posée. Elle décide que les questions seront réparties en 5
boites. Initialement, tous les questions seront placées dans la boite 1. Les
questions de la boite 1 sont posées tous les jours, celles de la boite 2 tous
les deux jours, celles de la boite 3 tous les quatre jours, celles de la boite 4
tous les huit jours et celles de la boite 5 tous les quinze jours. Si l’étudiante
donne la bonne réponse à une question et que la question n’appartient pas
à la boite 5, son numéro de boite est incrémenté (augmenté de 1). Si
l’étudiante ne donne pas la bonne réponse, la question revient dans la
boite 1.
Elle met en œuvre une base de données relationnelle contenant 4 tables
discipline, chapitre, boite et question.
La table discipline contient la liste des disciplines étudiées. Elle a deux
attributs :
id, de type INT, l’identifiant de la discipline qui est une clé primaire
pour cette table ;
lib, de type TEXT, le libellé de la discipline.
La table chapitre contient la liste des chapitres des disciplines étudiées.
Elle a trois attributs :
id, de type INT, l’identifiant du chapitre qui est une clé primaire
pour cette table ;
lib, de type TEXT, le libellé du chapitre ;
id_disc, de type INT, l’identifiant de la discipline à laquelle
appartient ce chapitre.
La table boite contient l’ensemble des cinq boites existantes. Elle a trois
attributs :
id, de type INT, l’identifiant numéro de la boite qui est une clé
primaire pour cette table ;
lib, de type TEXT, le libellé de la boite ;
frequence, de type INT, indiquant le nombre de jours séparant
deux interrogations d’une question appartenant à cette boite.
La table flashcard contient les questions-réponses. Elle a six attributs :
id, de type INT, l’identifiant de la flashcard qui est une clé primaire
pour cette table ;
id_ch, de type INT, l’identifiant du chapitre auquel appartient la
flashcard ;
id_boite, de type INT, l’identifiant numéro de la boite de la flashcard ;
question, de type TEXT, le texte au recto de la flashcard ;
reponse, de type TEXT, le texte au verso de la flashcard ;
date_interro, de type DATE, la date de la prochaine interrogation
pour cette question.
Initialement date_interro sera la date d’insertion de la question dans la
base de données.
Table boite
Id lib frequence
1 tous les jours 1
2 tous les deux jours 2
3 tous les quatre jours 4
4 tous les huit jours 8
2. Écrire une requête SQL qui complète la table boite et insère la
boite 5 de libellé ‘tous les quinze jours’ et de fréquence 15.
Une requête sur la table flashcard affiche l’enregistrement suivant :
5, 2, 1, Pearl Harbor – date, 6 décembre 1941
3. Écrire une requête SQL pour mettre à jour la date de Pearl Harbor
renvoyée. La bonne date est le 7 décembre 1941.
4. Écrire une requête SQL qui permet d’obtenir la liste des libellés des
disciplines.
5. Écrire une requête SQL qui permet d’obtenir la liste des libellés des
chapitres de la discipline ‘histoire’.
6. Écrire une requête SQL qui permet d’obtenir la liste des
identifiants des flashcards de la discipline ‘histoire’.
7. Écrire une requête SQL pour supprimer toutes les flashcards de
la boite d’identifiant 3.
Exercice 4
(d'après Prépabac NSI, Terminale, [Link], [Link], [Link],
[Link], éditions HATIER.)
On veut créer une base de données [Link] qui contiendra les trois
tables suivantes :
Patients
id Int
nom Text
prenom Text
genre Text
annee_naissa Int
nce
Ordonnan
ces
code Int
Patients
id_patient Int
matricule_me Int
decin
date_ord Text
medicaments Text
Medecin
s
matricule Int
nom_preno Text
m
specialite Text
telephone Text
On suppose que les dates sont données sous la forme jj-mm-aaaa.
On donne le diagramme relationnel de cette base :
Q0. Écrire le schéma relationnel de la table Ordonnances. On soulignera
les clés primaires et marquera d'un # les clés étrangères.
Q1. (HP) Donner les commandes SQL permettant de créer ces tables.
Q2. Mme Anne Wizeunid, née en 2000 et demeurant 3 rue des Pignons
Verts 12345 Avonelit doit être enregistrée comme patiente numéro 1.
Donner la commande SQLite correspondante.
Q3. Le patient numéro 100 a changé de prénom et s'appelle maintenant
"Alice". Donner la commande SQLite modifiant en conséquence ses
données.
Q4. Par souci d'économie, la direction décide de se passer des médecins
spécialisés en épidémiologie. Donner la commande permettant de
supprimer leurs fiches.
Correction
Q5. Donner la liste des patient(e)s ayant été examiné(e)s par un(e)
psychiatre en avril 2020.
Exercice 5 :
basé sur le travail de [Link] (Bayonne)
On considère ci-dessous le schéma de la base de données du stock d'un
supermarché :
Q1. Quelle requête SQL donne le prix d'achat du produit dont
le nom_court est «Liq_Vaiss_1L» ?
Q2. Quelle requête donne l'adresse, le code postal et la ville du fournisseur
dont le nom est «Avenir_confiseur» ?
Q3. Quelle requête donne les produits étant en rupture de stock ?
Q4. Quelle requête donne la liste de toutes les ampoules vendues en
magasin ? On pourra faire l'hypothèse que le nom du produit contient le
mot «ampoule»
Q5. Quelle requête permet d'avoir le prix moyen de ces ampoules ?
Q6. Quelle requête permet d'identifier le produit le plus cher du magasin ?
Q7. Quelle requête renvoie les noms des produits dont la date de
péremption est dépassée ? (on pourra utiliser la fonction SQL NOW() qui
renvoie la date actuelle )