802.1.Access Les requêtes projection et sélection avec jointure
Base de données à utiliser
Les documents sont présents dans le dossier 0800 : ressources fichiers application aux bases de données[1]
Le fichier ci-dessous donne accès aux 3 tables de la base de données :
version access : 802_1.mdb
version libreoffice : 802_2.odb
A. Visualisation des tables et relation
L'objectif est de visualiser la table de relation tb_notes en affichant pour chaque note le nom de l'étudiant et le nom de la matière.
Ouvrir le fichier
802_1.mdb
avec accessOuvrir chaque table pour en consulter les données (clic.droit) en mode affichage ou feuille de données
Editer (ou modifier) chaque table pour consulter la structure de la table (attributs) en mode création
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger le média.
Ouvrir chaque table pour en consulter les données (clic.droit dans la liste des objets)
Editer (ou modifier) chaque table pour consulter la structure de la table (attributs)
Nous ne modifions pas la structure des tables pour le moment : identifiez bien les attributs clé primaire de chaque table, et les clés étrangères dans la table tb_note.
Question
Nous allons ajouter les 2 associations de notre application en ouvrant la vue de gestion des relations
Menu.Outils de base de données.Relations
Choisir les tables à placer dans la vue (on prendra les 3 tables),
Pour chaque association, faire glisser la clé primaire du côté 1 vers la clé étrangère du côté n.
L'indice vous donne l'image à obtenir et la solution la vidéo pour le faire
Indice

Solution
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger le média.
B. Requête Projection liste de notes avec nom de l'étudiant et de la matière
Création d'une requête avec l'assistant
Sélectionner dans les 3 tables les attributs qui identifient le mieux l'entité, à savoir le nom de l'étudiant, la matière et la note
Question

Créer une requête permettant d'afficher pour chaque note, le nom de l'étudiant et la matière.
Enregistrez la requête sous le nom rq_notes_liste
Solution
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger le média.
Le langage SQL
A l'aide du menu en bas à droite de la requête précédente, cliquez sur l'icône SQL
SELECT Tb_etudiant.nom_et, Tb_matiere.nom_mat, Tb_note.Note
FROM Tb_matiere INNER JOIN (Tb_etudiant INNER JOIN Tb_note ON Tb_etudiant.[id_et] = Tb_note.[id_et]) ON Tb_matiere.[id_mat] = Tb_note.[id_mat];
Les clauses importantes sont : SELECT, FROM et INNER JOIN
Pour ce faire, suivre les vidéos suivantes pour mettre en oeuvre quelques requêtes simples
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger le média.
Impossible d'accéder à la ressource audio ou vidéo à l'adresse :
La ressource n'est plus disponible ou vous n'êtes pas autorisé à y accéder. Veuillez vérifier votre accès puis recharger le média.
Question
Testez le code SQL suivant : quelle est la cardinalité (nombre de lignes)
SELECT * FROM Tb_etudiant, Tb_matiere ;
Enregistrez cette requête rq_sql_01
Solution
On obtient 15 lignes, soit le même nombre de lignes que la table note : c'est un cas particulier, car nous avons saisi une note pour chaque étudiant dans chaque matière.
En effet, à chaque note ajoutée, si le nombre d'étudiant ou de matières ne change pas, cette requête donnera toujours le même résultat.
En spécifiant dans la requête uniquement deux tables sans spécifier une liaison ou relation entre les attributs de ces deux tables, la cardinalité de la requête restera égale au produit des cardinalités des 2 tables concernées.
Concrètement, ce genre de requête est utile pour lister toutes les combinaisons des couples (matière, étudiant). Elle peut aussi servir à générer un fichier qui propose la liste de toutes les notes à remplir.
A l'aide de ces remarques, la question suivante vous semblera évidente ....
Question
Ajoutez un étudiant nommé toto, puis exécutez à nouveau rq_sql_01
. Est-ce normal d'avoir 18 lignes alors que le nombre de notes est toujours de 15
Solution
Oui, c'est normal, car il n'y a aucune jointure demandée dans la requête SQL, nous avons simplement demandé d'afficher tous les attributs des tables Tb_matiere et Tb_etudiant.
Nous n'avons pas demandé d'affiche la liste des notes, celle ci n'a pas évolué (vérifiable en exécutant à nouveau la requête rq_notes_liste)
Question
Testez le code SQL suivant et nommez la requête te rq_sql_02_aff_notes_etudiant
SELECT Tb_etudiant.nom_et, Tb_note.Note FROM Tb_note, Tb_etudiant
Nous obtenons 75 lignes (ou 90 avec l'étudiant ajouté ci-dessus), alors que nous n'avons toujours que 15 notes dans la table tb_notes
Il faut restreindre ce nombre de lignes en ajoutant la clause inner join qui va permettre de respecter l'association 1 - n entre tb_etudiant et tb_note. C'est que l'on appelle la condition de Jointure.
SELECT Tb_etudiant.nom_et, Tb_note.Note FROM Tb_note INNER JOIN Tb_etudiant ON Tb_note.id_et =Tb_etudiant.id_et
Interprétez les noms de table et d'attribut derrière les clauses
Le nom de la table suivi d'un . devant le nom de l'attribut n'est pas obligatoire quand il n'y a pas d'ambiguité.
Dans la clause SELECT, on peut juste se contenter de préciser seulement les attributs, dans la clause INNER JOIN, on veut l'égalité entre le même nom attribut mais dans les tables "tb_note" et "tb_etudiant" : il est donc nécessaire d'utiliser la syntaxe nom_table.nom_attribut.
Solution
SELECT : colonnes à afficher dans la requête. Par sécurité, on utilise la syntaxe table.attribut
FROM : table principale qui va déterminer la cardinalité de la requête. Ici, ce sont les notes et c'est donc la table Tb_note qui est précisée.
INNER JOIN : nom de la table associée. Comme nous voulons afficher la colonne Tb_etudiant.nom_et, la jointure doît être faite avec cette table.
ON (complémentaire à INNER JOIN) : c'est la condition qui va permettre de cherche le nom de l'étudiant via son identifiant qui doît être le même des deux côtés.
Question
Si vous avez compris, écrire la requête SQL permettant d'afficher les notes avec 2 colonnes (nom_mat et Note) : attention bien sûr à spécifier correctement la clause INNER JOIN concernant la relation entre les tables Tb_matiere et Tb_note.
Si vous n'y parvenez pas, utilisez le mode ébauche et recopiez le code SQL correspondant
Vous nommerez cette troisième requête rq_sql_03_note_par_matiere_sql.
Solution
SELECT Tb_matiere.nom_mat, Tb_note.Note
FROM Tb_matiere INNER JOIN Tb_note ON Tb_matiere.id_mat = Tb_note.id_mat;
Question

Grâce au mode ébauche, puis en mode SQL de vos requêtes, expliquez le rôle des mots clés AS, ORDER BY, ASC, DESC.
Question
Interprétez et testez le code SQL dans une requête vierge. Sa cardinalité est de 15 (notes) sauf si vous avez ajouté d'autre notes depuis le chargement de votre base de données.
SELECT Tb_etudiant.nom_et FROM Tb_note INNER JOIN Tb_etudiant ON Tb_note.id_et =Tb_etudiant.id_et
On ajoute la clause DISTINCT derrière SELECT
SELECT DISTINCT Tb_etudiant.nom_et FROM Tb_note INNER JOIN Tb_etudiant ON Tb_note.id_et =Tb_etudiant.id_et
Quelle est la cardinalité ? interprétez
Solution
Seules les lignes différentes sont affichées, donc, si un étudiant a plusieurs notes, son nom n'apparaîtra qu'une fois.
Cette requête donne donc la liste des étudiants qui ont au moins une note
SELECT nom_et FROM tb_etudiant
donne la liste complète des étudiants qu'ils aient été notés ou non.
Requêtes Sélection
SELECT * FROM tb_etudiant WHERE nom_et ='Du'
SELECT * FROM tb_etudiant WHERE nom_et ='Durant'
SELECT * FROM tb_etudiant WHERE nom_et like 'Du*'
SELECT * FROM tb_etudiant WHERE nom_et like '*u*'
SELECT * FROM tb_etudiant WHERE nom_et like '?u*'
SELECT * FROM tb_etudiant WHERE nom_et like '*a*'
SELECT * FROM tb_etudiant WHERE nom_et like ' ???a*'
Question
Testez les requêtes ci-dessus : en déduire la différence entre l'égalité (=) et le mot clé like avec ses masques ? et *
Critères de Tri
En mode ébauche, vous avez les lignes critères suivies de lignes Ou.
Les expressions de recherche sur la ligne critère sont reliées par des ET.
En ajoutant des expressions de recherche sur les lignes OU, c'est la fonction logique OU qui sera utilisée.
Ajoutez une requête en mode ébauche basée sur les 3 tables permettant d'afficher les notes avec le nom des étudiants et des matières ainsi que le coefficient de la matière.
Question
Question
Déplacez le <10 sur la ligne du dessous intitulée OU : que constatez vous ?
Requêtes paramétrées
Avec :[Critère], on peut demander l'ouverture d'une boite de dialogue afin que l'utilisateur puisse changer ses critères de recherche sans pour autant être obligé de modifier la requête ou de créer une multitude de requête identiques avec des critères différents.
Question
Testez la requête en mode ébauche ci-dessous

Lorsque l'on veut un nom d'étudiant qui contienne les caractères ur, on utilisera bien sûr le masque * en mettant *ur* dans la zone de recherche sur le nom de l'étudiant.
Jointure externe.
Dans notre application, il est intéressant de connaître les étudiants qui n'ont pas encore de notes.
Dans la clause INNER JOIN, on peut placer placer une condition de jointure qui va renvoyer les tuples dont la valeur des clés correspondent. Avec LEFT OUTER JOIN, on va demander à ce que la condition de jointure spécifié par ON soit bien réalisée, mais en plus on demandera que les tuples de la table de gauche (ici tb_etudiant) soient ajoutés même si ils n'ont pas de correspondance de clé : ce qui veut dire ici que l'on veut les noms d'étudiant même si ils n'ont pas de notes.
SELECT * FROM Tb_etudiant LEFT OUTER JOIN Tb_note ON Tb_etudiant.id_et = Tb_note.id_et
Bien sûr, les étudiants ajoutés sont ceux qui n'ont pas de notes, donc les colonnes de la table tb_note sont vides (en SQL vide est équivalent à NULL).
En spécifiant uniquement la colonne nom_et, et avec une clause WHERE Tb_note.id_et IS NULL
, on ne va garder que les étudiants qui n'ont pas de note. La requête au final devient
SELECT Tb_etudiant.nom_et FROM Tb_etudiant LEFT OUTER JOIN Tb_note ON Tb_etudiant.id_et = Tb_note.id_et WHERE Tb_note.id_et IS NULL