Évaluation : SQL - Le Langage des Données

Base de données : baseHopital.db

Présentation du contexte

On veut créer une base de données baseHopital.db qui contiendra trois tables permettant de gérer les patients, les médecins et les ordonnances d'un hôpital.

📋 Structure de la base de données

La base de données est composée de trois tables liées entre elles :

Patients : informations sur les patients
Medecins : informations sur les médecins
Ordonnances : prescriptions médicales reliant patients et médecins

Table : Patients

Attribut	Type	Description
num_patient	INTEGER	Identifiant unique du patient (clé primaire)
nom	TEXT	Nom du patient
prenom	TEXT	Prénom du patient
annee_naissance	INTEGER	Année de naissance
adresse	TEXT	Adresse complète du patient

Table : Medecins

Attribut	Type	Description
num_medecin	INTEGER	Identifiant unique du médecin (clé primaire)
nom	TEXT	Nom du médecin
prenom	TEXT	Prénom du médecin
specialite	TEXT	Spécialité médicale (ex: psychiatre, cardiologue...)

Table : Ordonnances

Attribut	Type	Description
num_ordonnance	INTEGER	Identifiant unique de l'ordonnance (clé primaire)
num_patient	INTEGER	Référence au patient (clé étrangère → Patients)
num_medecin	INTEGER	Référence au médecin (clé étrangère → Medecins)
date_consultation	DATE	Date de la consultation (format AAAA-MM-JJ)
medicaments	TEXT	Liste des médicaments prescrits

📊 Schéma relationnel

Patients(num_patient, nom, prenom, annee_naissance, adresse)

Medecins(num_medecin, nom, prenom, specialite)

Ordonnances(num_ordonnance, #num_patient, #num_medecin, date_consultation, medicaments)

Légende : souligné = clé primaire, # = clé étrangère

Consignes

📋 Format de rendu

Votre devoir sera un document Word ou PDF nommé NOM_PRENOM_B3_SQL.docx (ou .pdf) qui contiendra pour chaque question :

Le code SQL : Une capture d'écran de votre requête SQL.
Le résultat : Une capture d'écran du résultat de la requête, affiché par votre client SQL.

⚠️ Consignes importantes

Utilisez SQLite comme SGBD
Testez vos requêtes avant de les capturer
Pour la Q0, vous pouvez rédiger le schéma à la main ou au clavier
Les captures d'écran doivent être lisibles
Respectez les conventions de nommage (clés primaires soulignées, # pour clés étrangères)

Questions à traiter

Question 0

Schéma relationnel : Écrire le schéma relationnel de la table Ordonnances. On soulignera les clés primaires et marquera d'un # les clés étrangères.

Exemple de notation : TableExemple(id, #cle_etrangere, attribut1, attribut2)

Question 1

Création des tables : Donner les commandes SQL permettant de créer ces trois tables (Patients, Medecins, Ordonnances) avec leurs contraintes appropriées.

N'oubliez pas de définir les clés primaires (PRIMARY KEY) et les clés étrangères (FOREIGN KEY).

Question 2

Insertion de données : 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 SQL correspondante.

Question 3

Mise à jour : Le patient numéro 100 a changé de prénom et s'appelle maintenant "Alice". Donner la commande SQL modifiant en conséquence ses données.

Question 4

Suppression : 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.

Attention : Cette suppression peut être bloquée s'il existe des ordonnances liées à ces médecins (contrainte d'intégrité référentielle).

Question 5

Requête avec jointure : Donner la liste des patient(e)s ayant été examiné(e)s par un(e) psychiatre en avril 2020.

Vous devrez utiliser une jointure entre les tables Patients, Ordonnances et Medecins, et filtrer sur la spécialité et la date.

Question 6

Agrégation : Pour chaque médecin, afficher son nom, son prénom et le nombre total d'ordonnances qu'il/elle a prescrites. Trier les résultats par nombre d'ordonnances décroissant.

Utilisez GROUP BY et COUNT(), puis ORDER BY.

Question 7

Filtrage avec agrégation : Afficher uniquement les médecins qui ont prescrit plus de 5 ordonnances. Afficher leur nom, prénom et le nombre d'ordonnances.

Utilisez HAVING après GROUP BY pour filtrer sur le résultat d'une fonction d'agrégation.

Question 8

LEFT JOIN : Afficher tous les patients avec le nombre d'ordonnances qu'ils ont reçues. Les patients n'ayant jamais eu d'ordonnance doivent aussi apparaître avec un compte de 0.

Utilisez LEFT JOIN pour inclure tous les patients, même ceux sans ordonnance.

Question 9

Sous-requête : Afficher les noms et prénoms des patients qui ont été examinés par le médecin ayant prescrit le plus grand nombre d'ordonnances.

Utilisez une sous-requête pour d'abord identifier le médecin le plus actif.

Question 10

Requête complexe avec dates : Afficher le nom et prénom des patients nés après 1990 qui ont eu au moins une consultation en 2020, avec le nombre total de leurs consultations cette année-là.

Combinez plusieurs conditions WHERE avec AND, utilisez YEAR() ou LIKE sur les dates, et GROUP BY.

Question 11

Analyse comparative : Pour chaque spécialité médicale, afficher le nombre de médecins et le nombre total d'ordonnances prescrites par cette spécialité. Trier par nombre d'ordonnances décroissant.

Jointure + double agrégation avec GROUP BY sur la spécialité.

Question 12 (Bonus avancé)

Identification des patients "fidèles" : Afficher les patients qui ont été examinés par au moins 3 médecins différents. Afficher leur nom, prénom et le nombre de médecins distincts consultés.

Utilisez COUNT(DISTINCT num_medecin) et HAVING.

Barème indicatif

Question	Points	Compétence évaluée
Q0	1.5 pts	Compréhension du modèle relationnel
Q1	3 pts	CREATE TABLE avec contraintes (bonus)
Q2	2 pts	INSERT INTO
Q3	2 pts	UPDATE avec WHERE
Q4	2 pts	DELETE avec WHERE
Q5	3 pts	Jointures multiples avec filtres
Q6	2.5 pts	GROUP BY avec COUNT et ORDER BY
Q7	2.5 pts	GROUP BY avec HAVING
Q8	2.5 pts	LEFT JOIN avec agrégation
Q9	3 pts	Sous-requête
Q10	3 pts	Requête complexe multi-critères avec dates
Q11	3 pts	Jointure avec double agrégation
Q12 (Bonus)	2 pts	COUNT DISTINCT avec HAVING (bonus)
Total (sans bonus)		23.5 points → 20/20
Total (avec tous les bonus)		28.5 points → 24/20

Conseils pour réussir

✅ Pour Q0 : Respectez la notation conventionnelle du schéma relationnel
✅ Pour Q2 : Vérifiez l'ordre des colonnes et des valeurs dans l'INSERT
✅ Pour Q3 et Q4 : N'oubliez JAMAIS la clause WHERE, sinon vous modifiez/supprimez toutes les lignes !
✅ Pour Q5 : Pensez à la structure de jointure : Patients ← Ordonnances → Medecins
✅ Pour Q5 : Utilisez LIKE '%2020-04%' ou BETWEEN pour filtrer sur avril 2020
✅ Testez toujours vos requêtes avant de les capturer
✅ Vérifiez la cohérence de vos résultats

Exemple de données pour tester vos requêtes

Vous pouvez utiliser ces données d'exemple pour créer et tester votre base :

Patients

INSERT INTO Patients VALUES (1, 'Wizeunid', 'Anne', 2000, '3 rue des Pignons Verts 12345 Avonelit');
INSERT INTO Patients VALUES (100, 'Dupont', 'Jean', 1985, '15 avenue de la République 75001 Paris');
INSERT INTO Patients VALUES (101, 'Martin', 'Sophie', 1992, '8 boulevard Victor Hugo 69002 Lyon');
INSERT INTO Patients VALUES (102, 'Dubois', 'Lucas', 1995, '22 rue de la Paix 75002 Paris');
INSERT INTO Patients VALUES (103, 'Moreau', 'Emma', 1988, '10 place de la Mairie 69003 Lyon');
INSERT INTO Patients VALUES (104, 'Laurent', 'Léa', 1998, '5 avenue des Champs 75008 Paris');

Médecins

INSERT INTO Medecins VALUES (1, 'Durand', 'Marie', 'psychiatre');
INSERT INTO Medecins VALUES (2, 'Bernard', 'Pierre', 'cardiologue');
INSERT INTO Medecins VALUES (3, 'Petit', 'Claire', 'épidémiologie');
INSERT INTO Medecins VALUES (4, 'Roux', 'Thomas', 'psychiatre');
INSERT INTO Medecins VALUES (5, 'Blanc', 'Julie', 'généraliste');

Ordonnances

INSERT INTO Ordonnances VALUES (1, 100, 1, '2020-04-15', 'Anxiolytiques');
INSERT INTO Ordonnances VALUES (2, 101, 1, '2020-04-22', 'Antidépresseurs');
INSERT INTO Ordonnances VALUES (3, 100, 2, '2020-05-10', 'Bêta-bloquants');
INSERT INTO Ordonnances VALUES (4, 102, 1, '2020-04-05', 'Anxiolytiques');
INSERT INTO Ordonnances VALUES (5, 103, 4, '2020-04-18', 'Antidépresseurs');
INSERT INTO Ordonnances VALUES (6, 104, 5, '2020-03-12', 'Paracétamol');
INSERT INTO Ordonnances VALUES (7, 100, 5, '2020-06-08', 'Ibuprofène');
INSERT INTO Ordonnances VALUES (8, 101, 2, '2020-04-30', 'Aspirine');
INSERT INTO Ordonnances VALUES (9, 102, 4, '2020-07-14', 'Somnifères');
INSERT INTO Ordonnances VALUES (10, 100, 4, '2020-08-20', 'Anxiolytiques');
INSERT INTO Ordonnances VALUES (11, 104, 1, '2020-09-03', 'Antidépresseurs');
INSERT INTO Ordonnances VALUES (12, 103, 5, '2020-10-25', 'Vitamines');

💡 Analyse des données de test

Ces données permettent de tester efficacement toutes les questions :

Aspect testé	Détails
Consultations avril 2020	Ordonnances 1, 2, 4, 5, 8 (avec 2 psychiatres)
Médecin le plus actif	Dr Durand (4 ordonnances) et Dr Roux (3 ordonnances)
Patient avec plusieurs médecins	Patient 100 : consulté par 4 médecins différents
Patient sans ordonnance	Patient 1 (Anne Wizeunid) - pour tester LEFT JOIN
Patients nés après 1990	Patients 101, 102, 104 avec consultations en 2020
Spécialités variées	2 psychiatres, 1 cardiologue, 1 généraliste, 1 épidémiologiste