Requêtes simples

Temps estimé de réalisation : 1h30

Objectifs

Question à laquelle cette activité va permettre de répondre :

  • Suis-je capable de mobiliser mes connaissances sur la syntaxe SQL pour écrire des requêtes répondant à la question posée et vérifier que le résultat est juste ?

Objectifs pédagogiques visés :

  • Lire un schéma logique
  • Écrire un schéma logique sous son format graphique
  • Vérifier la validatité de requêtes simples

Connexion à la base de données

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)

Description de la base de données SOINS

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.

Structure générale d’une requête

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 !

Pattern matching et tris

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ère

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

Jointures

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

Négation et listes

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.

Comptages et agrégations

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.