Aller au contenu

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 : deb_ex1.png

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
SQL
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
SQL
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
SQL
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
SQL
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
SQL
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é :

exo3_schema.png

Q1. Quelle requĂȘte SQL donne le prix d'achat du produit dont le nom_court est «Liq_Vaiss_1L» ?

Solution
SQL
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
SQL
SELECT adresse, cp, ville FROM Fournisseurs WHERE nom = 'Avenir_confiseur';

Q3. Quelle requĂȘte donne les produits Ă©tant en rupture de stock ?

Solution
SQL
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
SQL
SELECT nom FROM Produits WHERE nom LIKE "%ampoule%";

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

Solution
SQL
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

SQL
SELECT nom_court FROM Produits ORDER BY prix_vente DESC LIMIT 1;
ou

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

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

Correction Q2.b.
SQL
INSERT INTO Station VALUES ("Soleil Rouge", "Bastia", "Corse");
INSERT INTO Sport VALUES ("plongée", "Soleil Rouge", 900);        
Correction Q3.a.
SQL
SELECT mail FROM Client;        
Correction Q3.b.
SQL
SELECT nomStation FROM Sport
WHERE nomSport = "plongée";      
Correction Q4.a.
SQL
SELECT Station.ville, Station.nomStation FROM Station
JOIN Sport ON Sport.nomStation = Station.nomStation
WHERE Sport.nomSport = "plongée";        
Correction Q4.b.
SQL
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

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.
SQL
SELECT nom_ville FROM Centres 
WHERE altitude >= 700 AND altitude <= 1200;
Correction Q2.c.
SQL
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.
SQL
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.
SQL
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

Solution
Correction Q1.a.
Text Only
Hey Jude
I Want To Hold Your Hand
Correction Q1.b.
SQL
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.
SQL
SELECT COUNT(*) FROM morceaux;
Correction Q1.e.
SQL
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.
SQL
UPDATE morceaux
SET annee = 1971
WHERE titre = 'Imagine';
Correction Q3.b.
SQL
INSERT INTO interpretes
VALUES (6, "The Who", "Angleterre");
Correction Q3.c.
SQL
INSERT INTO morceaux
VALUES (7, "My Generation", 1965, 6);
Correction Q4.
SQL
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

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.
SQL
SELECT Nom, Prenom FROM Clients;            
Correction Q2.b.
SQL
SELECT Telephone FROM Clients
WHERE Prenom = "Grace" AND Nom = "Hopper";
Correction Q3.
SQL
SELECT NumChambre FROM Reservations
WHERE date(DateArr) <= date('2024-12-28')
AND date(DateDep) > date('2024-12-28');
Correction Q4.a.
SQL
UPDATE Chambres
SET prix = 75
WHERE NumChambre = 404;
Correction Q4.b
SQL
SELECT Reservations.NumChambre FROM Reservations
JOIN Clients ON Clients.NumClient = Reservations.NumClient
WHERE Clients.Nom = 'Codd' AND Clients.Prenom = 'Edgar';

CrĂ©dits⚓

Gilles Lassus