Bases de Données 1 2025-2026

Modèle relationnel, langage S.Q.L.

Gestion du schéma relationnel

Exercice 1 L’hôpital.
Reprendre l’exercice sur l’hôpital de la feuille précédente.

1. Écrire en SQL les requêtes :

(a) créant la table MEDECIN,

(b) créant la table ACTE,
(c) rajoutant une colonne NumSalle à la table ACTE.

2. Écrire en SQL les requêtes (à faire après quelques exercices portant sur les requêtes
SELECT) :

(a) insérant le médecin (1233,’Rabelais’,’Cardiologie’,12);

(b) multipliant par 2 le tarif de tous les actes qui sont des "Ponction".
(c) supprimant tous les actes sur le patient 100, sauf s’ils sont pratiqués par le médecin 11.

Exercice 2 Une compagnie aérienne.

Pour les exercices suivants, nous considérons le schéma relationnel d’une compagnie aérienne
(tous les vols ont lieu le même jour) :
• TYPES = (N_Type, capacite, rayonAction). Un type d’avion est identifié par son numéro,
caractérisé par sa capacite (nombre de passagers qu’il peut embarquer) et son rayon d’action.
• AVIONS = (N_Avion, N_Type , dateRevision). Un avion est identifié par son numéro ;
caractérisé par son type et la date de sa dernière révision.
N_Type est une clé étrangère qui référence TYPES(N_Type).
• VOLS = (N_vol, N_Avion, N_Commandant, heureDep, villeDep, heureArr, villeArr).
Un vol est identifié par son numéro ; caractérisé par le numéro de l’avion sur lequel il se déroule, le
numéro du commandant de bord ainsi que par les heures/villes de départ et arrivée.
N_Commandant est une clé étrangère qui référence PILOTES(N_Pilote).
N_Avion est une clé étrangère qui référence AVIONS(N_Avion).
• PILOTES = (N_Pilote, Nom, N_Qualification, dateNaissance). Un pilote est identifié
par son numéro ; caractérisé par le type d’avion qu’il a le droit de piloter (N_Qualification) et sa date
de naissance.
N_Qualification est une clé étrangère qui référence TYPES(N_Type)
• COPILOTES = (N_vol, N_copilote). indique qui est copilote sur quel vol.
N_copilote est une clé étrangère qui référence PILOTES(N_Pilote).
N_vol est une clé étrangère qui référence VOLS(N_Vol).

1. Écrire les requêtes créant les tables types, avions et copilotes de la base. (Bien
prendre en compte les clés, qu’elles soient primaires ou étrangères ...).

2. Écrire la requête rajoutant un champ Marque de 20 caractères à la table types, ce champ

doit avoir ’airbus’ comme valeur par défaut.

1
 3. Contraintes d’intégrité.
Donner la liste de toutes les contraintes d’intégrité que doit respecter la base de données.
Remarque : on ne s’interesse pas ici aux simples restrictions de validité de valeurs comme :
un pilote doit être né après 1950 ni aux contraintes de clés primaires ou étrangères.

4. Écrire les requêtes (à faire après quelques exercices sur les requêtes select) :

(a) Ajouter le pilote de nom “Niki Lauda”, né le “22/02/1949”, sa qualification n’est pas
précisée.
(b) Supprimer les vols “Paris-Nice” du “lundi”.
(c) Qualifier tous les pilotes du “Paris-Nice” du “lundi” sur “airbus A320”.
(d) Diminuer de 10% le rayon d’action de tous les avions qui font “Paris-Nice” sauf si ce
sont des “airbus”.

Requêtes SELECT simples

Exercice 3 Une compagnie aérienne.
Reprendre l’exercice sur la compagnie aérienne. Écrire les requêtes calculant :

1. les numéros des vols “Paris-Nice”.

2. les types d’avion de capacité supérieure à 300 passagers.

3. les numéros des avions de capacité supérieure à 300 passagers.

4. les numéros des vols “Paris-Nice” où l’on peut mettre plus de 300 passagers.

5. les noms des pilotes qui sont commandant au moins une fois.

6. les numéros des vols où (par erreur) un pilote est à la fois commandant et copilote.

7. la liste des vols pour lesquels le type de l’avion utilisé a un rayon d’action trop court (la
fonction dist(A, B) donne la distance entre les villes A et B).

8. les numéros des vols sur lesquels le type d’avion ne correspond pas à la qualification du
commandant de bord.

9. les numéros des vols V1 et V2 utilisant un même avion alors que ces vols sont simultanés.

Exercice 4 Les fleuves.

Reprendre l’exercice sur les fleuves de la feuille précédente.

1. Écrire en SQL les requêtes a) à g) de la question 2.

2. Écrire les requêtes calculant :

(a) pour chaque pays, sa densité de population.

(b) pour chaque pays X et chaque continent C, la surface du pays X dans le continent C.

Exercice 5 L’hôpital. Reprendre l’exercice sur l’hôpital de la feuille précédente.

Écrire en SQL les requêtes de la question 4.

2
 Requêtes SELECT avec agrégrats
Exercice 6 Une compagnie aérienne. Reprendre l’exercice sur la compagnie aérienne.
Écrire les requêtes calculant :

1. le nombre de pilotes qui sont commandants de bord.

2. pour chaque numéro de pilote, nombre de fois où il est copilote.

3. pour chaque pilote, moyenne des capacité des appareils sur lesquels il vole en tant que
commandant.

4. numéros des pilotes qui sont plus de 5 fois copilotes.

5. pour chaque pilote, maximum des capacité des appareils sur lesquels il vole.

6. le type d’appareil ayant la plus grande capacité.

7. afficher pour chaque pilote le nombre de vols où il est commandant sur un vol vers Paris,
à condition qu’il soit par ailleurs plus de 3 fois copilotes (même sur des vols n’allant pas
à Paris).

Exercice 7 L’hôpital. Reprendre l’exercice sur l’hôpital de la feuille précédente.

Écrire en SQL les requêtes calculant :

1. pour chaque patient, le nombre de ses séjours à l’hopital.

2. pour chaque patient, le nombre total de jours où il a séjourné à l’hopital (on considère
que la différence entre 2 dates donne un nombre de jours).

3. pour chaque médecin, le nombre d’actes auxquels il a participé en 2015 (la fonction
YEAR(d) retourne le millésime de la date d).

4. le numero du médecin ayant effectué l’acte de coût le plus élevé.

5. pour chaque séjour, le total des frais engagés (on affichera, numéro et nom du patient,
date du départ et d’arrivée, cout du séjour). Le coût d’une journée d’hospitalisation est
de 1000 euros, le coût d’un séjour comprend les journées d’hospitalisation, ainsi que le
coût des actes pendant ce séjour.

6. la moyenne du coût d’un séjour. Remarque : créer une ou plusieurs vues peut aider à
l’écriture de cette requête et des suivantes.

7. pour chaque mèdecin, la moyenne des coûts des séjours dont il est responsable.

8. le mèdecin qui est responsable du séjour de coût maximum.

9. la liste des mèdecins dont la moyenne des coûts de séjour dépasse de 20% la moyenne des
coûts de tous les séjours.

Exercice 8 Les fleuves. Reprendre l’exercice sur les fleuves de la feuille précédente.
Écrire en SQL les requètes calculant :

1. le nombre de pays stockés dans la base.

3
 2. le nombre de pays traversés par un fleuve de plus de 1000 km.
3. le nombre de pays qui ne sont pas traversés par un fleuve de plus de 100km.
4. le pays dont la superficie est la plus grande.
5. pour chaque pays, la somme des longueurs des fleuves qui le traversent.
6. pour chaque pays, le nombre de continents auxquels il appartient.
7. le ou les pays appartenant au plus grand nombre de continents.
8. pour chaque continent, la différence entre la superficie indiquée dans la table Continent
et la superficie calculée en faisant la somme des superficies des pays apartennant à ce
continent (au prorata du pourcentage indiqué dans la table Appartient.
9. écrire en SQL les requêtes h à j de la question 2 sur la feuille précédente.
10. le continent comportant le plus de pays,
11. le nombre de fleuves dont la longueur dépasse la longueur moyenne des fleuves.

Requêtes SELECT avec INNER/LEFT/RIGHT/OUTER JOIN

Exercice 9 L’hôpital. Reprendre l’exercice sur l’hôpital de la feuille précédente.
Écrire en SQL, en utilisant une jointure, les requêtes calculant :
1. pour chaque acte (nom du soin, date et coût) les noms des patients et médecins (un
médecin ou un patient n’ayant jamais participé à aucun acte ne doit pas apparaitre).
2. pour chaque patient (nom et numéro), la liste de tous les actes auxquel il a participé. Si
le patient TOTO de numéro 11100 :
- n’a jamais subi d’acte, on affichera seulement TOTO 11100 ou TOTO 11100 NULL.
- a subi plusieurs actes, il y aura autant de lignes (commençant par TOTO 11100) que
d’actes concernant ce patient.
3. Pour tous les médecins (numéro seulement), la liste de tous les actes auxquels il a participé.
Comme précédemment, on affichera le numéro des médecins n’ayant participé à aucun
acte.

Gestion des droits

Exercice 10 Gestion des droits.
Trois personnes, M. PAIE, M. ORDONNANCEUR et M. SRH utilisent la base de données sur
la compagnie aérienne : M. PAIE s’en sert pour calculer la paie et les frais de déplacement des
pilotes ; M. ORDONNANCEUR, organise les vols : affectation des pilotes, choix des avions,
des horaires, des lignes, etc (seuls les numéros et qualification des pilotes lui sont utiles, il ne
doit donc pas pouvoir accéder aux autres champs de la table pilote) ; M. SRH est responsable
des ressources humaines, embauche, licencie, etc... les pilotes.
Il y a aussi, comme toujours, un DBA (Administrateur de la Base de Données). cette
personne a tous les droits sur la base.
Donner la liste des commandes SQL (création de vues et octroi de droits) que doit lancer
le DBA pour que chacun de ces 3 utilisateurs n’accède qu’aux, et ne puisse modifier que les
données qui lui sont nécéssaires.