Temps estimé de réalisation : 1h30
Objectifs
Question à laquelle cette activité va permettre de répondre :
Objectifs pédagogiques visés :
Consultez la page Accès base de données pour savoir comment accéder à la base de données.
Pour information, le script SQL utilisé pour créer la base de données de cette activité :
Script permettant la création de la bases de données
Avertissement
Pour les requêtes SQL utilisez la documentation suivante et aucune autre : https://docs.postgresql.fr/10/index.html (il s’agit de la bonne version pour la base de données installée sur JupyterHub)
Les requêtes SQL à exprimer sont relatives à la base de données SOINS constituée des relations dont le schéma est décrit de manière linéaire ci-dessous : - les clefs primaires des relations sont soulignées d’un trait plein, - les clés référentielles (ou étrangères) sont soulignées en pointilllés.
MEDECIN (medecin_id, rpss, nom, prenom, adresse, telephone, specialite)
PATIENT (patient_id, numsecu, genre, date_naissance, nom, prenom, rattachement, medecin_referent) où rattachement référence PATIENT (patient_id) et où medecin_referent référence MEDECIN (medecin_id)
VISITE (medecin, patient, date_visite, prix) où medecin référence MEDECIN (medecin_id) et où patient référence PATIENT (patient_id)
PRESCRIPTION (ordonnance_id, medicament, medecin, patient, date_visite, duree, posologie, modalites) où le triplet d’attributs (medecin, patient, date_visite) référence VISITE (medecin, patient, date_visite)
Notez que :
L’attribut rattachement de la relation PATIENT est renseigné quand une personne (appelée ayant-droit) bénéficie de l’assurance maladie, non pas en son nom propre, mais par sa relation avec une autre personne (appelée assuré) qui en bénéficie. Par exemple, les enfants sont rattachés à l’un de leurs parents, ce qui leur donne droit à l’assurance maladie.
L’attribut medecin_referent de la relation PATIENT désigne le médecin qu’un patient a choisi pour suivre son dossier médical.
Les attributs duree et posologie seront traités de manière très simple dans cet exemple : la durée désigne un nombre de jours de traitement et la posologie le nombre de prises par jour.
Plusieurs lignes de la relation PRESCRIPTION peuvent se regrouper en une ordonnance dont l’identifiant est ordonnance_id
Question 1 : Commencez par représenter graphiquement le schéma logique décrit précédemment.
Les requêtes SQL respectent la structure générale présentée ci-dessous.
SELECT colonnes
FROM table
[WHERE condition]
[GROUP BY colonnes [HAVING condition]]
[ORDER BY colonnes [ASC|DESC]];
Remarque : les expressions entre crochets [ … ] signifient que le mot-clef est optionnel !
Les mots-clefs AND
et OR
dans une clause WHERE
vous permettent d’enchainer les conditions en appliquant les règles de parenthésage usuelles.
Si l’on veut comparer deux chaînes de caractères nous devons utiliser le mot-clef LIKE
suivi d’une chaîne de caractères pouvant contenir des jokers :
%
pour remplacer n’importe quelle suite de caractères_
pour remplacer un seul caractèreLe mot-clef ILIKE
peut être utilisé à la place de LIKE
pour rendre la correspondance insensible à la casse en fonction de la locale active. Ce n’est pas dans la norme SQL mais c’est une extension PostgreSQL (qui est la base de données recommandée pour cette activité).
Avant de commencer, prenez le temps d’observer les enregistrements des différentes tables. Par exemple, la requête suivante SELECT distinct medicament FROM prescription
vous permettra de comprendre comment les médicaments sont orthographiés afin d’effectuer la requête adéquate.
Question 2 : Affichez les ordonnances qui contiennent du “doliprane”.
Question 3 : Affichez toutes les prescriptions de “ventoline” ordonnées par durée de traitement décroissante.
Question 4 : Affichez les prescriptions de “ventoline” ayant une durée supérieure à 90 jours.
Afin d’exprimer une contrainte sur les dates vous pouvez utiliser les opérateurs habituels (=
, !=
, <=
, <
, >
, >=
), cependant vous devez exprimer la date avec la syntaxe suivante : to_date('31/12/2019', 'dd/mm/yyyy')
pour le 31 décembre 2019, par exemple.
Question 5 : Affichez les prescriptions de “ventoline” effectuées après le 15 janvier 2018 et ayant une durée supérieure à 90 jours.
Vous pouvez commencer par lire un rappel sur les jointures.
Lorsque vous réalisez des requêtes avec des jointures, utilisez le plus petit nombre possible de tables nécessaires. On parle généralement de critère de minimalité.
Parfois il est nécessaire d’utiliser plusieurs fois la même table. On dit alors que cette table a des rôles différents. Utiliser la notion d’alias afin de renommer les tables.
Question 6 : Affichez le nom et prénom des patients et les identifiants des médecins qu’ils ont visités le 15 janvier 2018.
Question 7 : Affichez pour chaque ayant-droit, les numéros de sécurité sociale, nom et prénom de l’assuré auquel il est rattaché.
Question 8 : Affichez le nom et prénom des patients et le nom de leur médecin référent.
Question 9 : Affichez le nom et prénom des personnes qui ont un ayant-droit (en français d’usage courant on veut dire qui ont au moins un ayant-droit).
Un attribut peut être différent d’une valeur : attribut NOT LIKE chaine
ou attribut != valeur
Un attribut peut ne pas avoir de valeur : attribut IS NULL
Un attribut peut ne pas être présent dans une liste (donné par un SELECT
imbriqué par exemple) : attribut NOT IN (...)
Question 10 : Affichez les médecins qui ne sont pas généralistes.
Question 11 : Affichez les patients qui n’ont pas de médecin référent.
Avant de débuter, exécutez et comparez le résultat rendu par l’ensemble de requêtes suivant :
SELECT COUNT(*) AS "Nombre" FROM PATIENT;
SELECT COUNT(patient_id) AS "Nombre" FROM PATIENT;
SELECT COUNT(rattachement) AS "Nombre" FROM PATIENT;
SELECT count(DISTINCT rattachement) AS "Nombre" FROM PATIENT;
Remarquez que le mot-clef AS
suivi d’une chaîne de caractères permet de renommer le nom d’une colonne. Choisissez toujours un nom clair plutôt que de laisser une instruction COUNT
peu compréhensible.
Dans ces exemples à quoi correspondent ces nombres ?
Question 12 : Affichez le nombre total de médecins.
Question 13 : Affichez le nombre de médecins par spécialité.
Après avoir réalisé le TP vous pouvez consulter la correction.