Aller au contenu

Exercices SQL débranchés⚓︎

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 : image

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.

Correction

Ordonnances ((code, Int), (id_patient#, Int), (matricule_medecin#, Int), (date_ord, Text), (medicaments, Text))

Q1. (HP) Donner les commandes SQL permettant de créer ces tables.

Correction 
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.

Correction 
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.

Correction 
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.

Correction 
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.

Correction 
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» ?

Correction 
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» ?

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

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

Correction 
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»

Correction 
SELECT nom 
FROM Produits 
WHERE nom LIKE '%ampoule%';

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

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

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

Correction 

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 )

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

Exercice 3

Exercice 1 du sujet Amérique du Nord J1 2022

Correction
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 = 'plongee'
Correction Q4.a. 
SELECT Station.ville, Station.nomStation FROM Station
JOIN Sport ON Sport.nomStation = Station.nomStation
WHERE Sport.nomSport = 'plongee'
Correction Q4.b. 
SELECT COUNT(*) FROM Sejour
JOIN Station ON Station.nomStation = Sejour.nomStation
WHERE Sejour.annee = 2020 AND Station.region = 'Corse'

Exercice 4

Exercice 4 du sujet Centres Étrangers J1 2022

Correction
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 5

Exercice 4 du sujet Métropole J2 2022

Correction
Correction Q1.a. 
Hey Jude
I Want To Hold Your Hand
Correction Q1.b. 
SELECT nom FROM interpretes
WHERE pays = 'Angleterre';
Correction Q1.c. 
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 6

Exercice 2 du sujet La Réunion J2 2022

Correction
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 7

Exercice 3 du sujet Amérique du Nord J1 2024

Partie A

Correction Q1

Le séparateur utilisé est le point-virgule ;.

Correction Q2

Ce choix a été fait pour avoir la possibilité d'utiliser la virgule à l'intérieur des champs, comme dans Allemagne, Italie, Japon.

Correction Q3 

1
2
3
4
def charger(nom_fichier):
    with open(nom_fichier,'r') as fichier:
        donnees = list(csv.DictReader(fichier,delimiter=';'))
    return donnees
(la fonction renvoie une liste de dictionnaires et non un dictionnaire comme dit dans l'énoncé)

Correction Q4

La méthode utilisée est la méthode sleep, à la ligne 37.

Correction Q5

donnees[i] est un dictionnaire.

Correction Q6 
1
2
3
4
flashcard = charger('flashcards.csv')
d = choix_discipline(flashcard)
c = choix_chapitre(flashcard, d)
entrainement(flashcard, d, c)

Si vous souhaitez jouer avec ces flashcards, téléchargez le fichier flashcards.csv et utilisez le code ci-dessous :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import csv
import time

def charger(nom_fichier):
    with open(nom_fichier, 'r') as fichier:
        donnees = list(csv.DictReader(fichier, delimiter=';'))
    return donnees

def choix_discipline(donnees):
    disciplines = []
    for i in range(len(donnees)):
        disc = donnees[i]['discipline']
        if not disc in disciplines:
            disciplines.append(disc)
    for i in range(len(disciplines)):
        print(i + 1, disciplines[i])
    num_disc = int(input('numéro de la discipline ? '))
    return disciplines[num_disc - 1]


def choix_chapitre(donnees, disc):
    chapitres = []
    for i in range(len(donnees)):
        if flashcard[i]['discipline'] == disc:
            ch = flashcard[i]['chapitre']
            if not ch in chapitres:
                chapitres.append(ch)
    for i in range(len(chapitres)):
        print(i + 1, chapitres[i])
    num_ch = int(input('numéro du chapitre ? '))
    return chapitres[num_ch - 1]


def entrainement(donnees, disc, ch):
    for i in range(len(donnees)):
        if donnees[i]['discipline'] == disc and donnees[i]['chapitre'] == ch:
            print(donnees[i])
            print('QUESTION : ', donnees[i]['question'])
            time.sleep(5)
            print(donnees[i]['réponse'])
            time.sleep(1)


flashcard = charger('flashcards.csv')
d = choix_discipline(flashcard)
c = choix_chapitre(flashcard, d)
entrainement(flashcard, d, c)

Partie B

Correction Q7 
INSERT INTO boite
VALUES (5, 'tous les quinze jours', 15)
Correction Q8 
UPDATE flashcard
SET reponse = '7 décembre 1941'
WHERE id = 5
Correction Q9 
SELECT lib
FROM discipline
Correction Q10 
SELECT chapitre.lib
FROM chapitre
JOIN discipline ON discipline.id = chapitre.id_disc
WHERE discipline.lib = 'histoire'
Correction Q11 
SELECT flashcard.id
FROM flashcard
JOIN chapitre ON chapitre.id = flashcard.id_ch
JOIN discipline ON discipline.id = chapitre.id_disc
WHERE discipline.lib = 'histoire'
Correction Q12 
DELETE FROM flashcard
WHERE id_boite = 3

Exercice 8

Exercice 2 du sujet Amérique du Nord J2 2024

Correction Q1

Le résultat de la requête est : 

Dufour, Marc
Martin, Sophie

Correction Q2 
SELECT nom_medic
FROM medicament
WHERE prix < 3
Correction Q3 
INSERT INTO Clients
VALUES (3, 'DURAND', 'Nathalie', '269054958815780')
Correction Q4

Les attributs de la table ordonnance devant être déclarés clés étrangères sont :

  • id_client : qui fait référence à l'attribut id_client de la table client
  • id_medic : qui fait référence à l'attribut id_medic de la table medic

Exercice 9

Exercice 3 Partie B du sujet Centres Étrangers J2 2024

Correction Q6

Un clé primaire identifie de manière uniquement un enregistrement dans une table. Une clé étrangère d'une table est une clé primaire d'une autre table. Elle permet de relier ces deux tables.

Correction Q7

Cette requête pose problème car la valeur 1 de l'attribut idagres n'existe pas dans la table agres.

Correction Q8 
UPDATE intervention
SET heure = '10:44:06'
WHERE jour = '2024-02-15' AND heure = '01:44:06'
Correction Q9

Le résultat de cette requête est 

Charlot
Red
Kevin

Correction Q10 
SELECT nom
FROM personnel
WHERE qualif >= 16 AND actif = 1
Correction Q11
  • Requête A : compte le nombre d'agrès mobilisés le 27 mars. La réponse est 2.
  • Requête B : compte le nombre d'agrès mobilisés le 27 mars mais dont l'id est dans la table moyen. La réponse est 1.

Pour info, INNER JOIN signifie la même chose que JOIN (mais le programme officiel de NSI ne parle que de JOIN)

Correction Q12 
SELECT DISTINCT nom
FROM personnel
JOIN agres ON idchefagres = matricule
WHERE jour = '2024-02-15'

Pour info, DISTINCT n'a pas besoin de parenthèses (comme COUNT), ce n'est pas une fonction.

Correction Q13 
SELECT DISTINCT nom
FROM personnel
JOIN agres ON agres.idchefagres = personnel.matricule
JOIN moyen ON moyen.idagres = agres.id
JOIN intervention ON intervention.id = moyen.idinter        
WHERE intervention.jour = '2024-06-11'