Exercice : 802.1.Access Les requêtes projection et sélection avec jointure [Exercices

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 access
Ouvrir 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

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

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

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];

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

Question

Testez le code SQL suivant : quelle est la cardinalité (nombre de lignes)

SELECT * FROM Tb_etudiant, Tb_matiere ;

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

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

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;

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

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

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 ='Du'

SELECT * FROM tb_etudiant WHERE nom_et ='Durant'

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 '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 '?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*'

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

Testez la requête selon l'ébauche ci-dessous

Solution

Question

Déplacez le <10 sur la ligne du dessous intitulée OU : que constatez vous ?

Solution

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

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

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

Exercices Ordonnés

802.1.Access Les requêtes projection et sélection avec jointure

Base de données à utiliser

A. Visualisation des tables et relation

Question

Indice

Solution

B. Requête Projection liste de notes avec nom de l'étudiant et de la matière

Question

Solution

Le langage SQL

Question

Solution

Question

Solution

Question

Solution

Question

Solution

Question

Question

Solution

Requêtes Sélection

Question

Critères de Tri

Question

Solution

Question

Solution

Requêtes paramétrées

Question

Jointure externe.