Base de données 4 STI
Requêtes de sélection avec jointures
Soit la base de données "MaisonEdition" qui sert à gérer les abonnements aux revues électroniques
publiées par une maison d’édition. Cette base est décrite par la représentation textuelle suivante :
ABONNE (Cin, NomPr, Tel, Email)
PrixMons : Prix monsuel
REVUE (Code, Titre, PrixMons, CodeType#)
Duree : Nombre de mois
…………………………………………………… Revenu total = PrixMons x Duree
ABONNEMENT (Cin #, Code #, DateAbon, Duree)
Partie A :
Modification de la structure de la base de données :
1) Lors de la saisie des abonnements, l’administrateur de la base a remarqué qu’il a commis
une erreur lors de la définition de la clé primaire de la table ABONNEMENT.
Ecrire les deux requêtes SQL qui permettent de modifier la clé primaire de la table
ABONNEMENT pour qu’elle soit formée par les trois champs : Cin, Code et DateAbon.
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
2) L’administrateur se propose de définir une contrainte pour garantir la non-redondance des
numéros téléphoniques.
Ecrire une requête SQL qui permet de définir la contrainte d’unicité sur la colonne Tel.
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
3) Puisqu’il existe une grande variété de revues, l’administrateur se propose d’améliorer la base
en ajoutant la table TYPE, ayant la représentation textuelle suivante : Type(CodeType, Libelle).
Sachant que la colonne CodeType est une lettre majuscule et le champ Libelle est une chaine
non nulle de taille maximale égale à 50.
Ecrire une requête SQL qui permet de créer la table TYPE.
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
4) Puisque chaque revue doit avoir un type, l’administrateur doit ajouter un champ à la table
REVUE qui renseigne le type.
Ecrire une requête SQL qui permet d’ajouter le champ CodeType à la table REVUE.
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
5) Pour achever cette modification dans la structure de la base, le champ CodeType de la table
REVUE doit être une clé étrangère reliée avec la clé primaire CodeType de la table TYPE.
Ecrire une requête SQL qui permet de définir une contrainte d’intégrité référentielle entre les
deux tables TYPE et REVUE.
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………………………………
Base de données 4 STI
Partie B :
Manipulation des données de la base de données :
6) Afficher les abonnements de l’abonné ayant le numéro de carte d’identité '12345'.
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
7) Lister les abonnés qui sont abonnés au revue de Code 'R01'. Afficher les champs Cin et NomPr.
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
8) Calculer le revenu total généré par le type de revue avec CodeType = 'T01'.
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
9) Trouver les abonnés (Cin, NomPr) qui ont un abonnement à une revue de type = 'T02'
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
10) Afficher les abonnées (Cin, NomPr) qui n’ont pas encore abonnés au revue de code 'R01'.
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
11) Afficher les abonnements (Cin, Code) expirés ( date d’abonnement ¿à la date courante).
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
12) Afficher pour chaque titre de revue, le nombre d’abonné[Link] résultat sera affiché sous cette
forme :
Titre NbAbonnees
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
………………………………………………………………………………………………………………………..……………………………
Base de données 4 STI