Exercices de SQL écrits

Exercice 1

(d'après Prépabac NSI, Terminale, G.CONNAN, V.PETROV, G.ROZSAVOLGYI, L.SIGNAC, éditions HATIER.)

On veut créer une base de données baseHopital.db qui contiendra les trois tables suivantes :

	Patients
id	Int
nom	Text
prenom	Text
genre	Text
annee_naissance	Int

	Ordonnances
code	Int
id_patient	Int
matricule_medecin	Int
date_ord	Text
medicaments	Text

	Medecins
matricule	Int
nom_prenom	Text
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.

Solution

Ordonnaces (code : Int, #id_patient : Int, #matricule_medecin : Int, date_ord : Text, medicaments : Text)

Q1. (Hors programe BAC) Donner les commandes SQL permettant de créer ces tables.

Solution

CREATE TABLE Patients(
id INTEGER PRIMARY KEY,
nom TEXT,
prenom TEXT,
genre TEXT,
annee_naissance INTEGER
);

CREATE TABLE Ordonnances(
code INTEGER PRIMARY KEY,
id_patient INTEGER,
matricule_medecin INTEGER,
date_ord TEXT,
medicaments TEXT,
FOREIGN KEY(id_patient) REFERENCES Patients(Id),
FOREIGN KEY(matricule_medecin) REFERENCES Medecins(matricule)
);

CREATE TABLE Medecins(
matricule INTEGER  PRIMARY KEY,
nom_prenom TEXT,
specialite TEXT,
telephone TEXT
);

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.

Solution

INSERT INTO Patients VALUES (1, "Wizeunit", "Anne", "F", 2000);

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.

Solution

UPDATE Patients SET prenom = 'Alice' WHERE id = 100 ;

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.

Solution

DELETE FROM Medecins WHERE specialite = "épidémiologie";

Q5. Donner la liste des patient(e)s ayant été examiné(e)s par un(e) psychiatre en avril 2020.

Solution

SELECT p.nom, p.prenom FROM Patients AS p
JOIN Ordonnances AS o ON p.id = o.id_patient
JOIN Medecins AS m ON o.matricule_medecin = m.matricule
WHERE m.specialite = "psychiatrie" AND o.date_ord LIKE "%04-2020%";

Exercice 2

basé sur le travail de G.Viateau (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» ?

Solution

SELECT prix_achat FROM Produits WHERE nom_court = 'Liq_Vaiss_1L' ;

Q2. Quelle requête donne l'adresse, le code postal et la ville du fournisseur dont le nom est «Avenir_confiseur» ?

Solution

SELECT adresse, cp, ville FROM Fournisseurs WHERE nom = 'Avenir_confiseur';

Q3. Quelle requête donne les produits étant en rupture de stock ?

Solution

SELECT Produits.nom FROM Produits
JOIN Stocks ON Produits.id = Stocks.produit
WHERE Stocks.quantite = 0;

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»

Solution

SELECT nom FROM Produits WHERE nom LIKE "%ampoule%";

Q5. Quelle requête permet d'avoir le prix moyen de ces ampoules ?

Solution

SELECT AVG(prix_vente) FROM Produits WHERE nom LIKE "%ampoule%";

Q6. Quelle requête permet d'identifier le produit le plus cher du magasin ?

Solution

SELECT nom_court FROM Produits ORDER BY prix_vente DESC LIMIT 1;

ou

SELECT nom FROM Produits WHERE prix_vente = (SELECT MAX(prix_vente) FROM Produits);

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 )

Solution

SELECT p.nom FROM Produits AS p
JOIN Stocks AS s ON s.produits = p.id
WHERE s.date_peremption < NOW();

Exercice 3

On donne le schéma relationnel suivant :

1. Donner la clé primaire de la table Inscriptions

Solution

la clé primaire de la table Inscriptions est le couple (#id_etudiant, #id_cours)

2. Écrire la requête qui permet de donner le nom, prénom, date d'inscription de tous les étudiants inscrits au cours "Informatique"

Solution

SELECT e.nom, e.prenom, i.date_inscription
FROM Etudiants as e
JOIN Inscriptions as i ON e.id_etudiant = i.id_etudiant
JOIN Cours as c ON i.id_cours = c.id_cours
WHERE c.titre = "Informatique";

Exercice 4

Exercice 1 du sujet Amérique du Sud J1 2022

Solution

Correction Q1.a.

La relation Sport a pour clé primaire le couple NomSport et nomStation, et pour clé étrangère l'attribut nomStation, clé primaire de la relation Station.

Correction Q1.b.

• Contrainte d'intégrité de domaine : l'attribut Prix doit être un nombre entier.

• Contrainte d'intégrité de relation : le couple (nomSport, nomStation) ne peut pas se retrouver deux fois dans la table (car il forme une clé primaire)

• Contrainte d'intégrité de référence : l'attribut nomStation ne peut pas être un nom n'apparaissant pas dans la relation Station.

Correction Q2.a.

La commande INSERT ne sert que pour insérer de nouveaux enregistrements, or le couple ("planche à voile" , "La tramontane catalane") existe déjà dans la relation (et c'est une clé primaire donc on ne peut pas la retrouver deux fois). Il faut donc utiliser :

UPDATE Sports SET prix = 1350 
WHERE nomSport = "planche à voile" AND nomStation = "La tramontane catalane";      

Correction Q2.b.

INSERT INTO Station VALUES ("Soleil Rouge", "Bastia", "Corse");
INSERT INTO Sport VALUES ("plongée", "Soleil Rouge", 900);        

Correction Q3.a.

SELECT mail FROM Client;        

Correction Q3.b.

SELECT nomStation FROM Sport
WHERE nomSport = "plongée";      

Correction Q4.a.

SELECT Station.ville, Station.nomStation FROM Station
JOIN Sport ON Sport.nomStation = Station.nomStation
WHERE Sport.nomSport = "plongée";        

Correction Q4.b.

SELECT COUNT(*) FROM Sejour
JOIN Station ON Station.nomStation = Sejour.nomStation
WHERE Sejour.annee = 2020 AND Station.region = "Corse";

Exercice 5

Exercice 4 du sujet Centres Étrangers J1 2022

Solution

Correction Q1.a.

L'attribut id_mesure semble une clé primaire acceptable car elle semble spécifique à chaque enregistrement.

Correction Q1.b.

L'attribut id_centres semble être une clé primaire de la relation Centres. On le retrouve aussi (sous le même nom) dans la relation Mesures. C'est donc un attribut qui permettra de faire une jointure entre les deux relations.

Correction Q2.a.

Cette requête va afficher tous les renseignements disponibles sur les centres dont l'altitude est strictement supérieure à 500m.

Correction Q2.b.

SELECT nom_ville FROM Centres 
WHERE altitude >= 700 AND altitude <= 1200;

Correction Q2.c.

SELECT longitude, nom_ville FROM Centres
WHERE longitude > 5
ORDER BY nom_ville;

Correction Q3.a.

Cette requête va afficher tous les renseignements sur les mesures datées du 30 octobre 2021.

Correction Q3.b.

INSERT INTO Mesures VALUES (3650, 138, 2021-11-08, 11, 1013, 0);

Correction Q4.a.

Cette requête va renvoyer tous les renseignements sur les centres dont la latitude est la latitude minimum de tous les centres.

Correction Q4.b.

SELECT DISTINCT Centres.nom_ville FROM Centres
JOIN Mesures ON Mesures.id_centre = Centres.id_centre
WHERE Mesures.temperature < 10
AND Mesures.date <= 2021-10-31
AND Mesures.date >= 2021-10-01;

Exercice 6

Exercice 4 du sujet Métropole J2 2022

Solution

Correction Q1.a.

Text Only

Hey Jude
I Want To Hold Your Hand

Correction Q1.b.

SELECT nom FROM interpretes
WHERE pays = 'Angleterre';

Correction Q1.c.

Text Only

I Want To Hold Your Hand, 1963
Like a Rolling Stone, 1965
Respect, 1967
Hey Jude, 1968
Imagine, 1970
Smells Like Teen Spirit, 1991

Correction Q1.d.

SELECT COUNT(*) FROM morceaux;

Correction Q1.e.

SELECT titre FROM morceaux
ORDER BY titre;

Correction Q2.a.

La clé étrangère de la table morceaux est l'attribut id_interprete qui fait référence à la clé primaire id_interprete de la table interpretes.

Correction Q2.b.

morceaux : (id_morceau : Int, titre : Text, annee : Int, #id_interprete : Int)
interpretes : (id_interprete : Int, nom : Text : pays : Text)

Correction Q2.c.

La requête va renvoyer une erreur car la clé primaire 1 est déjà présente dans la table : il s'agit d'une violation de la contrainte de relation.

Correction Q3.a.

UPDATE morceaux
SET annee = 1971
WHERE titre = 'Imagine';

Correction Q3.b.

INSERT INTO interpretes
VALUES (6, "The Who", "Angleterre");

Correction Q3.c.

INSERT INTO morceaux
VALUES (7, "My Generation", 1965, 6);

Correction Q4.

SELECT morceaux.titre
FROM morceaux
JOIN interpretes ON interpretes.id_interprete = morceaux.id_interprete
WHERE interpretes.pays = "États-Unis";

Exercice 7

Exercice 2 du sujet La Réunion J2 2022

Solution

Correction Q1.

Le couple (NumClient, NumChambre) ne pouvait pas être une clé primaire car un même client peut revenir dans l'hôtel et avoir la même chambre qu'à un précédent séjour. Le couple (NumClient, NumChambre) ne serait donc pas unique et ne peut donc pas servir de clé primaire pour la relation Reservations.

Correction Q2.a.

SELECT Nom, Prenom FROM Clients;            

Correction Q2.b.

SELECT Telephone FROM Clients
WHERE Prenom = "Grace" AND Nom = "Hopper";

Correction Q3.

SELECT NumChambre FROM Reservations
WHERE date(DateArr) <= date('2024-12-28')
AND date(DateDep) > date('2024-12-28');

Correction Q4.a.

UPDATE Chambres
SET prix = 75
WHERE NumChambre = 404;

Correction Q4.b

SELECT Reservations.NumChambre FROM Reservations
JOIN Clients ON Clients.NumClient = Reservations.NumClient
WHERE Clients.Nom = 'Codd' AND Clients.Prenom = 'Edgar';

Exercice 8

D'après la partie A de l'exercice 3 du sujet de bac 25-NSIJ1JA1 Sujet Asie Pacifique jour 1 en 2025

Le club de Judo souhaite proposer à ses adhérents une location de kimonos. Pour cela, il décide de mettre en place une base de données contenant les relations adherent, kimono et location.

Le schéma relationnel, où les clés primaires sont soulignées et les clés étrangères sont précédées du symbole #, est le suivant :

adherent( numero-licence , taille-adherent, nom, prenom) 
kimono( id-kimono , taille-kimono) 
location( #numero-licence , #id-kimono , debut, fin) 

Pour bien comprendre

Dans le schéma relationnel de la table location les deux attributs numero-licence et id-kimono sont soulignés, mais il y a une seule clé primaire. Cela signifie que le couple (#numero-licence, #id-kimono) est la clé primaire de la table location.

L’attribut id-kimono est un nombre entier. Les attributs taille-adherent et taille-kimono sont des nombres entiers dont l’unité est le centimètre et qui sont tous multiples de 10 (100, 110, 120, 130,…).

Les attributs debut et fin sont des dates au format chaîne de caractères ‘AAAA-MMJJ’. Tous les attributs doivent être renseignés et valides mais l’attribut fin peut éventuellement être égal à la chaîne de caractères vide '' pour les kimonos en cours de location.

On rappelle qu’en langage SQL la fonction d’agrégation COUNT permet de compter un nombre d’enregistrements. Par exemple, pour déterminer le nombre d’adhérents du club, on peut utiliser la requête suivante :
SELECT COUNT(numero-licence) FROM adherent

Question 1: Écrire une requête SQL permettant de connaître le nombre de kimonos de taille 130 cm possédés par le club.

Solution

SELECT id-kimono 
FROM location 
WHERE fin = '';

Question 2 : Écrire une requête SQL permettant de connaître le nombre de kimonos de taille 130 cm possédés par le club.

Solution

SELECT COUNT(id-kimono) 
FROM kimono 
WHERE taille-kimono = 130;

Question 3 : Écrire la requête SQL permettant de connaître le nom et le prénom de l’adhérent qui possède le kimono 42 uniquement si ce kimono est en cours de location. Cette requête ne doit renvoyer que le nom et le prénom de l’adhérent à qui il est actuellement loué et pas celui de tous ceux qui l’ont éventuellement loué par le passé.

Solution

SELECT nom, prenom 
FROM adherent JOIN location ON adherent.numero-licence = location.numero-licence 
WHERE id-kimono = 42 AND fin = '';

Question 4 : À la fin de l’année, pour anticiper les locations de l’année à venir, le club de judo modifie arbitrairement la taille de tous ses adhérents mesurant strictement moins de 160 cm en leur rajoutant 10 cm. Écrire une requête SQL permettant de réaliser cette opération.

Solution

UPDATE adherent 
SET taille-adherent = taille-adherent + 10 
WHERE taille-adherent < 160;

Question 5 : Le kimono numéro 25 a été déchiré lors d’un combat. Écrire les requêtes SQL permettant de le supprimer de la base de données.

Solution

À cause de l'intégrité de contrainte de clé étrangère, on ne peut pas supprimer les enregistrements de la table kimono, si certains enregistrements de la table location y font référence. En effet id-kimono est une clé étrangère de la table location faisant référence à la clé primaire id-kimono de la table kimono. Il faut donc écrire les commandes suivantes, dans cet ordre :

DELETE FROM location 
WHERE id-kimono = 25;

DELETE FROM kimono 
WHERE id-kimono = 25;

Exercice 9

D'après l'exercice 1 du sujet de bac 25-NSIJ2G11 Sujet Centres étrangers Groupe 1 jour 2 en 2025

La ville de Bois-Plage a décidé d’organiser, pendant un mois de juillet, un tournoi sportif de volley-ball par équipes de 4. Elle met à disposition des personnes intéressées un site d’inscription en ligne qui utilise un système de gestion de base de données.

Le schéma de la base de données utilisée est donné ci-dessous, en figure 1. Sur ce schéma, les clés primaires ont été soulignées et les clés étrangères indiquées par un croisillon (symbole #).

À la clôture des inscriptions, de nombreuses équipes sont inscrites. Sur la page suivante sont présentés des extraits des tables joueur et equipe obtenues à l’issue de la phase d’inscription.

Question 1: Expliquer, dans les relations précédentes, le rôle des clés primaires.

Solution

Le rôle des clés primaires données par les attributs id_joueur, id_equipe et id_match est de garantir le respect de ces trois critères :

• Unicité : 2 enregistrements ne peuvent pas avoir la même valeur de cet attribut
• Existence : Cet attribut est obligatoire, il a une valeur non nulle pour tous les enregistrements.
• Stabilité : La valeur de cet attribut n'est jamais modifiée.

Question 2: Expliquer quelles données n’auraient pas pu être stockées dans la table match si le champ id_match n’avait pas été introduit dans cette table.

Solution

Sans le champ id_match, on n'aurait pas pu stocker dans la table match deux matchs entre les mêmes équipes, dans le même ordre, avec la même équipe gagnante et le même score, ce qui peut éventuellement se produire.

Question 3: Donner le résultat de la requête suivante en l’appliquant à l’extrait de la table joueur donné dans l’énoncé.

SELECT prenom FROM joueur WHERE ann_naiss < 1985 

Solution

prenom
Henri
Laure
Brigitte
Laure

Question 4: Modifier la requête précédente afin d’éviter les éventuels doublons.

Solution

SELECT DISTINCT prenom FROM joueur WHERE ann_naiss < 1985;

Question 5: Écrire une requête SQL permettant d’obtenir tous les noms, années de naissance et numéros de téléphone portable des personnes qui habitent à Bois-Plage.

Solution

SELECT nom, ann_naiss, num_port 
FROM joueur 
WHERE commune = 'Bois-Plage';

L’organisateur souhaite obtenir l’identité du premier joueur de l’équipe “les Kangourous”. L’équipe “Les Kangourous” n’apparaît pas dans l’extrait.

Question 6: Écrire une requête SQL permettant d’obtenir le nom et le prénom du joueur j_1 de l’équipe “Les Kangourous”.

Solution

SELECT joueur.nom, joueur.prenom 
FROM joueur 
JOIN equipe ON joueur.id_joueur = equipe.j_1 
WHERE equipe.nom = 'Les Kangourous';

L’équipe “Volley Warriors” a terminé le tournoi avec un total de 5 points.

Question 7: Écrire une requête SQL permettant de mettre à jour la table equipe avec le nombre de points gagnés par l’équipe “Volley Warriors”.

Solution

UPDATE equipe 
SET points = 5 
WHERE nom = 'Volley Warriors';

Question 8: Écrire une requête SQL permettant de supprimer de la table joueur le joueur ayant pour identifiant le numéro 35.

Solution

Les clés étrangères j_1, j_2, j_3 et j_4 de la table equipe font référence à la clé primaire id_joueur de la table joueur. À cause de l'intégrité de contrainte de clé étrangère, Il faudrait d'abord supprimer le joueur 35 de la table equipe. Supprimer les équipes contenant le joueur 35 ne semble pas réaliste. Nous allons donc commencer par remplacer dans la table equipe le joueurs 35 par NULL. Ensuite nous pourrons supprimer le joueur 35 de la table joueur.

UPDATE equipe 
SET j_1 = NULL WHERE j_1 = 35;

UPDATE equipe 
SET j_2 = NULL WHERE j_2 = 35;

UPDATE equipe 
SET j_3 = NULL WHERE j_3 = 35;

UPDATE equipe 
SET j_4 = NULL WHERE j_4 = 35;

DELETE FROM joueur WHERE id_joueur = 35;

À la clôture du tournoi, la table match est totalement complétée. Un extrait de cette table est donné ci-dessous.

Question 9: Écrire une requête SQL permettant d’obtenir la liste des identifiants de matchs auxquels a participé l’équipe ayant pour identifiant 12.

Solution

SELECT id_match
FROM match 
WHERE eq_1 = 12 OR eq_2 = 12;

Question 10: Écrire une requête SQL permettant d’obtenir la liste des identifiants des matchs pour lesquels le joueur 1 de l’équipe 1 du match vient de la commune de Bois-Plage.

Solution

SELECT id_match 
FROM match 
JOIN equipe ON match.eq_1 = equipe.id_equipe
JOIN joueur ON equipe.j_1 = joueur.id_joueur
WHERE commune = 'Bois-Plage';

Question 11: Écrire une requête SQL permettant d’obtenir la liste, classée par ordre alphabétique, des noms et prénoms des joueurs ayant gagné au moins un match en tant que joueur 1 de l’équipe 1 du match.

Solution

SELECT DISTINCT joueur.nom, joueur.prenom 
FROM joueur JOIN equipe ON joueur.id_joueur = equipe.j_1 
JOIN match ON match.eq_1 = equipe.id_equipe 
WHERE equipe.id_equipe = match.eq_gagnante
ORDER BY joueur.nom, joueur.prenom;

Crédits

Gilles Lassus