| INSIA - ING 1 Bases de données Piscine MySQL – Cours et TP n° 08 SQL 2 et les jointuresJointure externe LA BIBLE : MySQL 5.0 Reference Manual Site officiel MySql : Documentation MySQL : La Base de Données Open Source la plus Populaire au Monde Bertrand LIAUDET |
SOMMAIRE
| SOMMAIRE SQL : CONSULTATION DE LA BD - SUITE |
1
3
1. Produits cartésiens et jointures en SQL 2 3
Les produits cartésiens 3
Les jointures naturelles 3
Exemples avec plusieurs tables 4
Remarque sur le natural join 4
2. Jointures externes 5
Définition de la jointure externe 5
1er usage : s’intéresser à tous les tuples de la table maître : left join 7
2ème usage : s’intéresser aux tuples éliminés de la table jointe : right join 8
3ème usage : soustraction ensembliste à partir d’une jointure artificielle : left join 9
| TP N°8 : JOINTURES EXTERNES ET TOUT TYPES DE REQUETES |
11
Travail à faire 11
Méthode de travail 11
Ordre de projection des attributs 11
Exercice 1 : jointures externes 11
Exercice 2 : BD ecoling 13
Présentation 13
Interrogation de la BD 13
Première édition : septembre 2007
Deuxième édition : décembre 2008
Troisième édition : novembre 2009
SQL : CONSULTATION DE LA BD - SUITE
PRINCIPALES NOTIONS
Left join Right join
Outer join Inner join
Join Natural join
On Using
| 1. | Produits cartésiens et jointures en SQL 2 |
Syntaxe classique :
Select * fromemp, dept;
Le from peut être remplacé par :
Select * fromemp join dept;
ou
Select *
fromemp cross join dept;
ou
Select * fromemp inner join dept;
Syntaxe classique :
| Selecte.ne, e.nom, e.nd, d.nomd fromemp e, dept dwheree.nd = d.nd; |
Le from peut être remplacé par :
fromemp e join dept d
ou
fromemp e cross join dept d
ou
fromemp e inner join dept d
Le where peut être remplacé par :
one.nd = d.nd;
ou
using (nd);
Le from et le where peuvent être remplacés par :
Syntaxe classique :
| Selecte.datemp, e.nl, l.editeur, a.na, a.nom fromemprunter e, adherents a, livres l wheree.na = a.na ande.nl = l.nl ande?dateRet is null; |
| Selectdatemp, nl, editeur, na, nom fromemprunter inner join adherents using (na) inner join livres using(nl)where e?dateRet is null; |
| Selectdatemp, nl, editeur, na, nom fromemprunter natural join adherents natural join livreswhere e?dateRet is null; |
Dans l’exemple avec emp et dept, on a pris soin de différencier le code du nom de l’employé (nom) et le code du nom du département (nomd).
C’est obligatoire avec un « natural join ».
En effet, le natural join fait des restrictions de jointure avec tous les attributs de même nom des deux tables proposées. Donc avec nd, mais aussi avec nom le nom est le même dans les tables emp et dept. Dans ce cas, le résultat est vide ! Le principe mathématique abstrait de lajointure naturelle n’est donc pas adaptée aux relations concrètes qui peuvent avoir des attributs de même noms sans que ces attributs soient identiques.
On évitera donc toujours les « natural join » car l’ajout d’un nouvel attribut dans une table pourrait engendrer des erreurs sur des requêtes existant déjà.
| 2. | Jointures externes |
Toutes les possibilités syntaxiques qu’on vient d’aborder n’apportent rien de plus par rapport à la syntaxe standard du SQL 1.
L’intérêt de cette syntaxe est d’ouvrir à la notion de jointure externe : outer join (par opposition à inner join).
Une jointure externe est une jointure qui permet de rajouter au résultat les tuples éliminés d’une des deux tables de la jointure.
Après la jointure, on rajoute les tuples de la première table (celle de gauche) :
Select *
fromt1 left outer join t2 on= ;
ou encore, le « outer » étant facultatif :
Select * fromt1 left join t2 on= ;
Après la jointure, on rajoute les tuples de la deuxième table (celle de doite) :
Select *
fromt1 right outer join t2 on= ;
ou encore, le « outer » étant facultatif :
Select *
fromt1 right outer join t2 on= ;
A la différence de la jointure interne et du produit cartésien, la jointure externe n’est pas commutative :
Select * fromt1 right join t2 on= ;n’est pas équivalent à :
Select * fromt1 right join t2 on= ;
Le « right join » est équivalent au « left join » si on inverse l’ordre des tables.
Select * fromt1 right join t2 on= ;
est équivalent à :
Select * fromt2 left join t1 on= ;
Select * fromt1 outer join t2 // BUG !!! il faut préciser gauche ou droite on= ;
La requête précédente ne veut rien dire! Il faut nécessairement préciser right ou left pour que la requête soit correcte.
Select * fromt1 right join t2 on= where= valeur ;
L’ordre de traitements des opérations élémentaires est le suivant : 1) produit cartésien : from
2) restriction de jointure : on
3) jointure externe (ajout des tuples éliminés) : right (ou left)
4) restriction spécifique : where
Les deux premiers concernent des jointures naturelles avec une table maître et une table jointe.
Le troisième concerne les jointures artificielles.
Le premier usage consiste à rajouter les tuples éliminés de la table maître et à s’intéresser à tous les tuples de la table maître.
Le second usage consiste à rajouter les tuples éliminés de la table jointe et à s’intéresser uniquement au tuples éliminés de la table jointe.
Le troisième usage reprend le second dans le sens ou il s’agit d’une soustraction ensembliste, mais à partir d’une jointure artificielle et avec jointure externe à gauche.
La forme générale d’une jointure externe à gauche est :
Select, , , fromtable_maitre TM left jointable_jointe TJ on= ;
Avec CS : clé significative.
En mettant la table maître en premier, on a une jointure à gauche.
Question traitée : Afficher les employés avec leur supérieurs hiérarchiques et les employés sans supérieurs hiérarchique :
Selecte.ne, e.nom, , fromemp eleft joinemp echef one.nechef = ;
Résultats :
+------+--------+------+-------+
| ne | nom | ne | nom |
+------+--------+------+-------+
| 7369 | SMITH | 7902 | FORD |
| 7499 | ALLEN | 7698 | BLAKE |
| 7521 | WARD | 7698 | BLAKE |
| 7566 | JONES | 7839 | KING |
| 7654 | MARTIN | 7698 | BLAKE |
| 7698 | BLAKE | 7839 | KING |
| 7782 | CLARK | 7839 | KING |
| 7788 | SCOTT | 7566 | JONES |
| 7839 | KING | NULL | NULL |
| 7844 | TURNER | 7698 | BLAKE |
| 7876 | ADAMS | 7788 | SCOTT |
| 7900 | JAMES | 7698 | BLAKE |
| 7902 | FORD | 7566 | JONES |
| 7934 | MILLER | 7782 | CLARK |
+------+--------+------+-------+
14 rows in set (0.00 sec)
Selecte.ne, e.nom, ,
fromemp ejoinemp echef
one.nechef =
union
Selectne, nom, null, null // on ajoute deux attributs à à emp fromemp
wherenechef is null
;
Clé primaire = Clé Primaire (Table Maître)
On retombe forcément sur la clé primaire de la table maître.
Nb Tuples = Nb Tuples (Table Maître)
On retombe forcément sur le nombre de tuples de la table maître.
La forme générale d’une jointure externe à droite est :
SelectTJ.*
fromtable_maitresse TM right join table_jointe TJ on= where is NULL;
En mettant la table maître en premier, on a une jointure à droite.
Question traitée : quels sont les départements vides :
| Selectd.nd, d.nom, d.ville fromemp eright joindept d one.nd = d.nd wheree.ne is NULL; |
Résultats :
+----+------------+--------+
| nd | nomd | ville |
+----+------------+--------+
| 40 | OPERATIONS | BOSTON |
+----+------------+--------+
1 row in set (0.01 sec)
La jointure externe à droite classique permet de remplacer les opérations ensemblistes MINUS et INTERSECT, et leurs équivalent en NOT IN et NOT EXISTS
Version MINUS ( ne marche pas avec MySQL)
| Select * from dept minus select dept.* from emp e, dept d where e.ND = d.ND ; |
Version imbriquée NOT IN
Select * from dept where ND not in (
select ND from emp
);
Select *
Version imbriquée NOT EXISTS
from dept
where not exists ( select * from emp
where =
);
Nb Tuples(Table jointe) <= Nb Tuples <= Nb Tuples(Table Maître) + Nb Tuples(Table jointe)-1
Si aucun tuple de la table maître ne pointe sur un tuple de la table jointe, la jointure naturelle donne un ensemble vide et la jointure externe rajoute tous les tuples de la table jointe.
Si tous les tuples de la table maître pointe sur le même tuple de la table jointre, la jointure naturelle donne tous les tuples de la table maître sur-informés. La jointure externe ajoute tous les tuples de la table jointe moins 1.
Question traitée : tous les employés ayant un job différent de ceux du département 20
Selecte1.*
fromemp e1 left join emp e2 on= and =20 whereis NULL;
Principe :
On inverse la question : tous les employés moins ceux qui ont le même job que ceux du département 20.
On croise tous les employés e1 avec ceux du département 20 (e2) et on garde ceux qui ont un job identique à un employé du département 20
La restriction de jointure et la restriction spécifique sont faites dans le « on ».
Ensuite, le « left » rajoute les employés de e1 qui ont été éliminés : ceux qui ont un job différent de ceux du département 20.
Il faut donc faire bien attention à mettre toutes les restrictions souhaités dans le « on » avant que le « left » s’applique et rajoute les tuples éliminés.
La jointure externe à droite classique permet de remplacer les opérations ensemblistes MINUS et INTERSECT, et leurs équivalent en NOT IN et NOT EXISTS
Version imbriquée NOT IN
Select * from emp
where job not in ( select job from emp where nd=20 );
TP N°8 : JOINTURES EXTERNES ET TOUT TYPES DE
REQUETES
• Après chaque requête, on met, en commentaire, le nom de l’attribut clé primaire de la table résultat
• Mettez le résultat obtenu dans le fichier (copier-coller).
A partir du fichier « rapport de TP », faire des copier-coller dans la calculette SQL ou dans un fichier de test et lancer le script de test.
Requête sans fonction de groupe
Attributs de tri, Cle Primaire, Clé Significative, Attributs demandés, Attributs de restriction
Attributs de tri, attributs du group by, fonctions de groupe demandées, fonctions de groupe de restriction (du having).
En utilisant les BD des TP précédents, répondre aux questions en utilisant des jointures externes :
1. Faire une jointure entre « emp » et « dept » en projetant tous les attributs et en utilisant un « join » puis un « inner join ». Essayer avec un « using », puis un « on », puis un « natural join ».
2. Faire une jointure à gauche entre « emp » et « dept » en projetant tous les attributs.
3. Faire une jointure à droite entre « emp » et « dept » en projetant tous les attributs.
4. Faire une jointure entre « emp » et « emp » (pour avoir les chefs) en projetant tous les attributs et en utilisant un « join » puis un « inner join ». Essayer avec un « using », puis un « on », puis un « natural join ».
5. Faire une jointure à gauche entre « emp » et « emp » (pour avoir les chefs) en projetant tous les attributs.
6. Faire une jointure à droite entre « emp » et « emp » (pour avoir les chefs) en projetant tous les attributs.
7. Quels sont les départements vides ? (empdept)
8. Quels sont les employés qui ne sont pas supérieurs hiérarchiques ? (empdept)
9. Quels sont les adhérents qui n’ont jamais emprunté de livres ? (biblio)
11. Tous les employés n’ayant pas le même chef que ALLEN (empdept)
12. Combien d’exemplaires du titre : « Narcisse et Goldmund » sont disponibles ? (biblio)
Etudiants (NET, nom, prenom, email)
Examens (NEX, matiere, prof, session, annee, niveau, type, duree)
Groupe (NGR, (promo, annee, groupe))
Epreuves (NEP, (#NGR, dateheure), salle, #NEX)
Evaluer (#NET, #NEP, note)
Participer (#NET, #NGR)
Dans la table Epreuves, (#NGR, dateheure) est une clé secondaire.
Dans la table Groupes, (promo, annee, groupe) est une clé secondaire.
Les Epreuves sont des instances d’un Examen
Les étudiants sont évalués dans une épreuve (qui correspond à un examen): il y a une note. En cas d’absence, ils ne sont pas dans la table.
1. Télécharger le script de création de la BD :
2. Faire le graphe des tables.
3. Lancer ce script de création de la BD.
4. Quelles sont les épreuves qui n’ont pas donné lieu à des évaluations ?
5. Quels sont les étudiants qui n’ont pas été évalués pour l’épreuve n°2 et qui auraient du l’être (autrement dit qui participent au groupe correspondant à l’épreuve.
On a intérêt à traiter d’abord les questions suivantes :
• Tous les étudiants ayant passé l’épreuve 2
• Tous les étudiants du groupe correspondant à l’épreuve n°2
6. Quels sont les étudiants du groupe B, promo 2011 n’ayant pas passé toutes les épreuves de leur groupe ?
On a intérêt à traiter d’abord les questions suivantes :
1-a : Liste des étudiants de étudiants du groupe B, promo 2011
1-b : Liste des épreuves auxquels les étudiants du groupe B, promo 2011 ont participé 1-c : Nombre d’épreuves auxquels les étudiants du groupe B promo 2011 ont participé Puis :
2-a : La liste des épreuves du groupe B, promo 2011
2-c : Nombre d’épreuves par étudiants
7. Quels sont tous les étudiants n’ayant pas passé les épreuves de leur groupe ?
Consultation de la BD
8. Pour chaque table verbes, afficher son contenu en sur-informant au maximum la table. Pour faciliter la lecture, on projettera uniquement les clés primaires et significatives en faisant les tris judicieux.
9. Quel est le parcours d’un étudiant donné (le 30): quelles années, quelles promos, quels groupes ?
10. Quelles sont toutes les notes d’un étudiant donné (le 30), classé par année, matière et date ? Ajouter éventuellement quelques tuples pour mieux exemplifier la requête.
Questions statistiques
11. Quels sont les étudiants ayant plus que la moyenne des étudiants pour l’épreuve n°1 ?
12. Quelles sont les moyennes générales des étudiants par année ?
13. Quels sont les étudiants ayant plus que la moyenne des étudiants pour l’épreuve n°1 ?
14. Quelle est la moyenne des groupes pour les examens 4, 5 et 6. On affichera le numéro d’examen, le numéro du groupe, le nombre de notes, la moyenne des notes et la liste des notes.
15. Quelle est l’épreuve avec sa date et sa matière dans laquelle on a la meilleure moyenne ?
16. Pour chaque épreuve avec le nom de l’examen, quels sont les élèves qui ont la meilleure note ?
17. Par promo, quels sont les élèves dont la moyenne par matière est toujours supérieure à 10 ?
On commencera par récupérer les moyennes <10 par matières.
