Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st CHAP 5-4 COMPLEMENT SUR L

BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st 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 © F. REDONNET Page 1/8 ADHERENT N°Adh NomAdh PrenAdh RueAdh VilleAdh Lien_affiliation 0,n parent Enfant 0,1 BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st 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 ? 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 : En QBE : © F. REDONNET Page 2/8 EMPRUNT LIVRES X X X X X X Y Y équijointure Champ de jointure : N°Livre Résultat X X X Double clic sur la jointure BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st 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; 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 : © F. REDONNET Page 3/8 Table père (« gauche ») Table fils (« droite ») X Y Z A B XA XB YA YB ZA ZB BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st 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 : © F. REDONNET Page 4/8 a1 a2 a3 a4 a5 x y z b1 b2 b3 b4 Relation A Relation B BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st 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 : © F. REDONNET Page 5/8 a1 a2 a3 a4 a5 x y z b1 b2 b3 b4 Relation A Relation B INTERSECTION BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st - 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 © F. REDONNET Page 6/8 BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st 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); © F. REDONNET Page 7/8 a1 a2 a3 a4 a5 x y z b1 b2 b3 b4 Relation A Relation B DIFFERENCE 1) On sélectionne tous les candidats 2)On sélectionne tous les candidats ayant passé au moins une épreuve 1)On sélectionne tous les candidats 2) On fait une jointure sur le numéro de candidat à partir d’une requête imbriquée permettant de connaître les candidats ayant passé l’examen. L’opérateur NOT permet de faire la différence et d’obtenir ainsi les candidats n’ayant pas de note BTS CGO1 P10 Chap 5-4 http://www.brikoboutikstt.fr.st 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 © F. REDONNET Page 8/8 uploads/Litterature/ 5-4complementrequetescorr.pdf