CHAP 5-4 COMPLEMENT SUR LES REQUETES SELECTION

I/ Requêtes imbriquées

Une requête imbriquée consiste à inclure une requête comme valeur d’une condition dans une clause WHERE. On utilise l’opérateur IN (dans)

Principe :

SELECT attribut1, attribut2…

WHERE attributx in (select …FROM … WHERE)

L’utilisation de requêtes imbriquées permet de réunir plusieurs requêtes en une seule

Attention : le résultat de la requête ne doit comporter qu’un seul champ

Exemple : Le bibliothécaire d’Aubencheul au Bac souhaite connaître les liens de parenté pouvant exister au sein des adhérents.

Soit le MCD suivant

Le schéma de la relation est le suivant

ADHERENT(N°Adh, NomAdh, PrenAdh, RueAdh, VilleAdh, #N°AdhParent)

Rédigez la requête SQL permettant de connaître le prénom et le nom des parents ayant des enfants

SELECT PrenAdh, nomAdh

FROM ADHERENT

WHERE N°Adh in (select NumAdhParent from ADHERENT)

Résultat :

PrenAdh	NomAdh	N°Adh
Myriam	CROFT-LARA	3
Virginie	GINOLA	14
Mylène	JULIANA	18
Alexandre	KILOURIEN	21
Grand	MANITOU	22

II/ Jointures externes

Situation : Vous disposez à présent du SLD relationnel complet relatif à la gestion de la bibliothèque d’Aubencheul au Bac (la base de données correspondante s’appelle bibliotheque.mdb)

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)

On voudrait avoir la liste des livres (titres) complète et, pour les livres empruntés les numéros des adhérents les ayants empruntés.

1) quelles sont les tables concernées par la requête ?

EMPRUNT et LIVRES

2) Une jointure simple (appelé aussi équijointure) permet elle de répondre à la requête ?

	Résultat

Seules les lignes de la table livre pour lesquels N°Livre a une valeur identique dans EMPRUNT et LIVRES seront repris

La solution est donc de faire une jointure externe :

Double clic sur la jointure

En QBE :

La table LIVRE est la table « père » et constitue ainsi la table de gauche alors que la table emprunt et la table « Fils » et est donc considérée comme table de droite

On parlera donc ici de jointure externe gauche

En SQL la syntaxe est la suivante :

SELECT DISTINCT TitreLivre, N°Adh

FROM LIVRES LEFT JOIN EMPRUNT ON LIVRES.N°Livre = EMPRUNT.N°Livre;

Table père (« gauche »)

Table fils (« droite »)

Exemple 2 : jointure externe droite :

On veut afficher le titre des livres empruntés

SELECT DISTINCT TitreLivre

FROM LIVRES RIGHT JOIN EMPRUNT ON LIVRES.N°Livre = EMPRUNT.N°Livre;

Remarque : comme les numéros de livre figurant dans la table emprunt sont inclus dans la table livre, une équijointure aurait produit exactement le même résultat.

SELECT DISTINCT TitreLivre

FROM LIVRES

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

III/ Les opérations ensemblistes

A/ Le produit cartésien

Il consiste à associer chaque enregistrement d’une relation A avec chaque enregistrement d’une relation B

Relation A Relation B

Produit cartésien :

XA

XB

YA

YB

ZA

ZB

Le nombre de n-uplets obtenus s’obtient en multipliant le nombre de n-uplets des différentes relations sources.

Pour réaliser un produit cartésien, il suffit de faire une projection avec au moins deux tables mais sans définir de jointure.

Exemple (à partir de la base GESNOTES.MDB)

Le SLD relationnel est le suivant :

SLD Relationnel de l’application GESNOTES

EPREUVE(Code épreuve, Désignépreuve, coeff)

ZONE(CodeZone, libelléZone)

CANDIDAT(Num candidat, Non candidat, #CodeEtab)

ETABLISSEMENT(Codeétab, Nométab, #CodeZone)

NOTATION(#Codeépreuve+#NumCandidat, note)

Afin de préparer des bordereaux de saisie de notes, on souhaiterait disposer de la liste des candidats (n° et nom) et de toutes les épreuves (code et nom d’épreuve) qu’ils sont susceptibles de passer :

SELECT NumCandidat, NomCandidat, Codeépreuve, Désignépreuve

FROM CANDIDAT, EPREUVE;

REMARQUE : le produit cartésien est très utilisé pour préparer la saisie d’une table (comme dans l’exemple, chaque année le service de l’académie peut préparer de cette manière la table notation. Il ne restera alors plus qu’à saisir les notes obtenues par les candidats)

B/ L’union

L’union consiste à créer une relation C en fusionnant le contenu de deux relations A et B

Les relations A et B doivent avoir une structure identique

Union

Notation en SQL :

Sélection A UNION Selection B

Remarque : ACCESS ne gère pas l’union en QBE.

Exemple :

On souhaite avoir la liste des candidats (numcandidat, nom candidat et code étab) provenant des établissements scolaires 330 et 350 :

En SQL

SELECT numcandidat, nomcandidat, codeétab

FROM CANDIDAT

Where codeétab=330

UNION

SELECT numcandidat, nomcandidat, codeétab

FROM CANDIDAT

Where codeétab=350;

Proposer une autre solution plus rapide :

SELECT numcandidat, nomcandidat, codeétab

FROM CANDIDAT

Where codeétab=330 OR Codeétab=350

C/ L’intersection

Elle consiste à retenir les enregistrements figurant à la fois dans la relation A et dans la relation B. Les relations doivent avoir une structure identique

Notation SQL :

Sélection A INTERSECT Sélection B

Exemple : On veut obtenir le nom des candidats ayant eu moins de 10 à l’épreuve 3 et à l’épreuve 6

En SQL :

SELECT Nomcandidat, codeépreuve

FROM CANDIDAT, NOTATION

WHERE codeépreuve=3 and note<10

and CANDIDAT.numcandidat=NOTATION.Numcandidat

INTERSECT

SELECT Nomcandidat, codeépreuve

FROM CANDIDAT, NOTATION

WHERE codeépreuve=6 and note<10

and CANDIDAT.numcandidat=NOTATION.Numcandidat;

Sachant qu’Access ne connaît pas la clause INTERSECT, la solution est la suivante en QBE :

- La table NOTATION est reprise deux fois (NOTATION_1 représente un alias)

- On est obligé de recourir à un alias car on ne peut pas poser la condition suivante :
NOTATION.Codeépreuve=3 AND NOTATION.Codeépreuve=6, un codeépreuve ne peut pas être simultanément égal à 3 et à 6

Le résultat est alors le suivant :

NomCandidat	NOTATION.Codeépreuve	NOTATION.Note	NOTATION_1.Codeépreuve	NOTATION_1.Note
GERS	3	3	6	1
ROSI	3	6	6	5

Solution proposée par Serge Vial – Académie de Grenoble

D/ La différence

Elle consiste à sélectionner les lignes figurant dans une relation A mais pas dans une relation B.

La structure des relations doit être identiques

Notation SQL :

Selection A MINUS Selection B

Les relations doivent avoir la même structure. Cet opérateur n’est pas reconnu dans ACCESS

Exemple :

A partir de la base de données gesnotes.MDB, on veut connaître la liste des élèves (nom) n’ayant pas eu de notes à une épreuve

SELECT Nomcandidat

FROM CANDIDAT

MINUS

SELECT DISTINCT NomCandidat

FROM CANDIDAT

WHERE CANDIDAT.NumCandidat=NOTATION.NumCandidat

La différence entre 1 et 2 donne bien les candidats n’ayant aucune note

Autre version dans ACCESS

SELECT Distinct NomCandidat

FROM NOTATION, CANDIDAT

WHERE CANDIDAT.Numcandidat NOT IN (SELECT NumCandidat FROM NOTATION

WHERE CANDIDAT.NumCandidat=NOTATION.NumCANDIDAT);

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 :

R1	Quels sont les livres (titres) qui n’ont jamais été empruntés ?
R2	Quels sont les auteurs n’ayant aucun livre dans la base de données
R3	A quels dates les livres « A travers le temps » et « Zéro absolus » ont il été empruntés ?
R4	Quels sont les livres ayant été empruntés à la fois par « Eric JULIANA » et « Alexia KILOURIEN » ? (A faire directement en QBE dans ACCESS)
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é ?
REVISIONS SUR LES REQUETES OPERATIONS
R6	Quel est le nombre de livres empruntés par chaque adhérent
R7	Quels sont les adhérents ayant emprunté au moins deux fois le même livre
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)
R9	Quels sont les emprunteurs ayant conservé un ouvrage plus de 10 jours ?
R10	Pour chaque genre de roman, combien y a t’il de livre au total