APPLICATION :

 

A partir de la base de données BIBLIOTHEQUE.MDB et du modèle relationnel correspondant, formulez sur papier puis dans access les requêtes SQL permettant d’obtenir les réponses suivantes :

RAPPEL : Modèle relationnel :

GENRE(CodeGenre, LibelleGenre)

AUTEUR(N°Auteur, PrenAut, NomAut)
EDITEUR(N°Editeur, NomEditeur)
ADHERENT(N°Adh, NomAdh, PrenAdh, RueAdh, VilleAdh, #N°AdhParent)
 EMPRUNT(#N°Adh, #N°Livre, #DateEmprunt, DateRetour)
 LIVRES(N°Livre, TitreLivre, annee, #CodeGenre, #N°Editeur, #N°Auteur)

 

R1

Quels sont les livres (titres) qui n’ont jamais été empruntés ?

Il s’agit d’une différence : on selectionne tous les livres puis seulement les livres empruntés et par différence on obtient les livres jamais empruntés

SELECT TitreLivre

from LIVRES

WHERE LIVRES.N°livre NOT IN (SELECT EMPRUNT.N°Livre FROM emprunt);

R2

Quels sont les auteurs n’ayant aucun livre dans la base de données

Comme dans R1, il s’agit d’une différence : on sélectionne tous les auteurs puis tous les auteurs ayant écrit un livre et par différence on obtient les auteurs n’ayant pas écrit de livres

SELECT  NomAut

FROM AUTEUR

WHERE AUTEUR.N°Auteur NOT IN (SELECT N°Auteur FROM  LIVRES);

 

R3

A quels dates les livres « A travers le temps » et « Zéro absolus » ont il été empruntés ?

Il s’agit ici d’une union que l’on peut traiter de deux manières :

Soit par une union select :

SELECT DateEmprunt

FROM EMPRUNT, LIVRES

WHERE TitreLivre="A travers le temps"

AND EMPRUNT.N°Livre=LIVRES.N°Livre ;

UNION

SELECT DateEmprunt

FROM EMPRUNT, LIVRES

WHERE TitreLivre="Zéro absolu"

AND EMPRUNT.N°Livre=LIVRES.N°Livre

Ou avec un critère de sélection incluant le OU logique

SELECT DateEmprunt

FROM EMPRUNT, LIVRES

WHERE TitreLivre="A travers le temps" OR TitreLivre="Zéro absolu"

AND EMPRUNT.N°Livre=LIVRES.N°Livre ;

 

R4

Quels sont les livres ayant été empruntés à la fois par « Eric JULIANA » et « Alexia KILOURIEN » ? (A faire directement en QBE dans ACCESS)

Il s’agit cette fois d’une requête intersection  (on veut connaître les titres de livres empruntés par JULIANA et KILOURIEN)

En langage SQL l’instruction serait la suivante :

SELECT TitreLivre

FROM LIVRE, EMPRUNT, ADHERENT

WHERE LIVRES.N°Livre=EMPRUNT.N°Livre

AND ADHERENT.N°Adh=ADHERENT.N°Adh

AND NomAdh=”JULIANA” AND PrenAdh=”Eric”

INTERSECT

SELECT TitreLivre

FROM LIVRE, EMPRUNT, ADHERENT

WHERE LIVRES.N°Livre=EMPRUNT.N°Livre

AND ADHERENT.N°Adh=ADHERENT.N°Adh

AND NomAdh=”KILOURIEN” AND PrenAdh=”Alexia”

Avec ACCESS, ce type de requête n’est pas réalisable, il faut utiliser une selection utilisant le ET logique. Pbme : la condition NomAdh= « JULIANA » AND NomAdh= »KILOURIEN » est impossible à poser sur la table ADHERENT. On recourt ici à nouveau à une requête imbriquée

Autre solution

SELECT LIVRES.N°Livre, LIVRES.TitreLivre

FROM LIVRES

WHERE N°Livre IN (SELECT EMPRUNT.N°Livre AS Livres1

             FROM EMPRUNT, ADHERENT

             WHERE ADHERENT.N°Adh = EMPRUNT.N°Adh

             AND ADHERENT.NomAdh="KILOURIEN"

             AND ADHERENT.PrenAdh="Alexia")

     AND N°Livre IN (SELECT EMPRUNT.N°Livre AS Livres2

             FROM EMPRUNT, ADHERENT

             WHERE ADHERENT.N°Adh = EMPRUNT.N°Adh

             AND ADHERENT.NomAdh="JULIANA"

             AND ADHERENT.PrenAdh="Eric");

(solution proposée par JL OUDART – Lycée Guy Mollet Arras)

R5

Pour la période du 01/01/02 au 31/05/02 quels sont les journées où aucun emprunt de livres n’a été réalisé ?

Requête non réalisable car on n’a pas de table DATE contenant toutes les dates d’ouverture de la bibliotheque

REVISIONS SUR LES REQUETES OPERATIONS

R6

Quel est le nombre de livres empruntés par chaque adhérent

SELECT NomAdh, count(emprunt.N°livre) as Nbemprunt

from ADHERENT, EMPRUNT

WHERE ADHERENT.N°Adh=EMPRUNT.N°Adh

GROUP BY NomAdh;

R7

Quels sont les adhérents ayant emprunté au moins deux fois le même livre

Cette requête opération est particulière car un critère de sélection est posé sur une donnée calculée. Dans ce cas, le critère ne se définit pas avec une clause WHERE mais avec la clause HAVING  ajoutée obligatoirement A LA FIN de la requête :

SELECT NomAdh, EMPRUNT.N°Livre, count(EMPRUNT.N°Livre) AS Nbemprunt

FROM EMPRUNT, ADHERENT

WHERE EMPRUNT.N°Adh=ADHERENT.N°Adh

GROUP BY NomAdh, EMPRUNT.N°Livre

HAVING COUNT(EMPRUNT.N°Livre)>2;

Le critère de regroupement est double (nomadh et n°livre emprunté)

Remarque : par rapport à l’énoncé strict, la première ligne pouvait se réduire à SELECT NomAdh

R8

Le livre « En pleine lumière » a été dégradé. On voudrait connaître le nom de chaque emprunteur ainsi que la date (résultats triés par date)

SELECT NomAdh, DateEmprunt

FROM ADHERENT, EMPRUNT, LIVRES

WHERE ADHERENT.N°Adh=EMPRUNT.N°Adh

AND EMPRUNT.N°Livre=LIVRES.N°Livre

AND TitreLivre="En pleine lumière"

ORDER BY DateEmprunt;

R9

Quels sont les emprunteurs ayant conservé un ouvrage plus de 10 jours ?

SELECT NomAdh, N°Livre

FROM ADHERENT, EMPRUNT

WHERE ADHERENT.N°Adh=EMPRUNT.N°Adh AND

DateRetour-DateEmprunt>10;

R10

Pour chaque genre de roman, combien y a t’il de livre au total

SELECT LibelleGenre, count(N°Livre) AS NbLivres

FROM GENRE, LIVRES

WHERE GENRE.CodeGenre=LIVRES.CodeGenre

GROUP BY LibelleGenre;