25 - SQL : JOIN ON

⏪

⏬

⏩

1 - Principe de la jointure

2 - job-title

3 - job-person

4 - double

5 - Autres exercices

6 - FAQ

Prerequis : l'activité précédente sur SQL.

Logiciel nécessaire : DB Brownser

Fichier SQLite : la base de données est à télécharger. Si vous n'avez pas de compte, vous pouvez me contacter avec le bouton en bas de page.

Il faut vous connecter pour voir les liens de téléchargement.

Sinon, vous pouvez me contacter via le bouton CONTACT en bas de page.

Documents de cours : open document ou pdf

MODE COURS (cacher les questions)

1 - Principe de la jointure avec clé étrangère [rappel ?]

⇩ ⇧ ⤊

Nous allons maintenant voir une autre raison à l'existence de la clé primaire.

En plus d'un jeu et de la console qui faisait tourner ce jeu, on rajoute maintenant sur chaque ligne des informations sur la console elle-même (visualisables en rouge ci-dessous) : la date de sortie de la console, sa mémoire et la puissance du microprocesseur.

On rajoute les infos sur la console en plus des infos sur le jeu lui-même

On voit que les jeux 10 et 11 tournaient sur la même console. Les mêmes informations sont donc présentes plusieurs fois. Ce n'est pas être optimum.

Pour limiter ce problème, la solution envisagée par les bases de données relationnelles est de créer deux relations (jeu et console) contenant au total les mêmes informations qu'avant et de relier ces 2 relations entre elles.

Chaque table a sa propre clé primaire (voir les colonnes rouges) qu'on décide de nommmer id dans les deux cas.

La clé étrangère permet de faire le lien entre la table jeu et la table console

Nouveauté : c'est la colonne bleue de la relation jeu qui permet de relier les deux relations.

L'attribut id_support est une clé étrangère. Chaque valeur de clé étrangère doit être :

soit une valeur valide de clé primaire de la table console;
soit NULL.

Regardons maintenant comment utiliser cela :

01-A° Quatre choses :

Localiser le p-uplet de la relation jeu dont la clé primaire id vaut 10.
Quelle est la valeur de la clé étrangère id_support pour ce p-uplet ?
Que peut-on en tirer comme informations sur la console de ce jeu ?
On présente ci-dessous le p-uplet virtuel obtenu en concaténant les attributs de la relation jeu et les attributs de la relation console . Dans l'en-tête, une formule expliquant comment on a fait la liaison entre les deux relations. Question : Comment pourrait-on décrire cette équation en français : mêmes valeurs de clés primaires ? mêmes valeurs de clés étrangères ? Une clé étrangère valant une clé primaire ?

id	jeu	description	sortie	editeur	fichier_image	genre	jeu_retro.id_support = console.id	nom	RAM	date_sortie
10	PITFALL!	Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle.	1983	Activision		Plate-formes	3	C64	64 ko	1982-08-01

Correction ?

...CORRECTION...

Localiser le p-uplet de la relation jeu_retro dont la clé primaire vaut 10.

Il s'agit de l'avant dernière ligne, le PITFALL ! sorti en 1983.

Quelle est la valeur du champ id_support de ce p-uplet ?

On lit 3 dans le champ de l'atttribut id_support.

Que peut-on en tirer comme informations sur la console de ce jeu ?

En allant voir dans la relation console, on peut voir qu'il s'agit d'une version du jeu pour le C64, ordinateur personnel sorti en 1982 et disposant de 64 ko de mémoire.

Comment pourrait-on décrire cette équation en français : mêmes valeurs de clés primaires ? mêmes valeurs de clés étrangères ? Une clé étrangère valant une clé primaire ?

On voit bien qu'on a une égalité entre une clé étrangère et une clé primaire.

01-B° L'avantage de réaliser cette jointure en place mémoire est évident si le nombre de jeux est bien plus grand que le nombre de consoles : nous n'aurions pas à recopier en mémoire de multiples fois les mêmes informations, il suffit de suivre la liaison entre clé étrangère vers clé primaire. Quel risque cela annule-t-il ?

Correction ?

...CORRECTION...

Il n'existe plus qu'une unique source d'information pour chaque colonne. Cela évite les erreurs bêtes comme le fait qu'on indique une fois que sa RAM est de 64 ko et ailleurs 67 ko à cause d'une erreur de frappe.

1.1 Contrainte de REFERENCE (liée à la valeur de la clé étrangère, foreign key)

Voir

A - Liaison entre relations

Le système de liaison entre les relations est donc basé sur :

des attributs nommés clés primaires respectant la contrainte d'UNICITE : (UNIQUE et NOT NULL) en SQL, elles identifient un n-uplet avec certitude.
des attributs nommés clés étrangères dont la valeur permet de faire la liaison vers un autre n-uplet dont on connait la valeur de clé primaire.

B - Contrainte de REFERENCE

La contrainte de REFERENCE impose que la valeur d'une clé étrangère :

soit NULL si on ne connait pas l'information;
soit correspond à une valeur de clé primaire d'un autre enregistrement.

C - Conventions

On identifie les clés étrangères :

en plaçant un caractère dièse # devant leur nom dans un schéma relationnel
parfois en plaçant le nom de la table visée en suffixe : id_console

Exemple

Dans l'exemple des consoles et des jeux retro, la clé étrangère id_support doit être affectée à NULL, 1, 2 ou 3.

02-A° On fournit ci-dessous les schémas relationnels des relations jeu et console.

Pour distinguer les attributs des deux relations, on utilise souvent la notation pointée de la POO relation.attribut.

Par exemple : jeu.id désigne un attribut id présent dans la relation jeu.

jeu (
    id:int,
    jeu:text,
    description:text;
    sortie:date,
    editeur:text,
    fichier_image:blob,
    genre:text,
    #id_support:int
)

console (
    id:int,
    nom:text,
    ram:text;
    sortie:date,
)

Question : Donner les noms des clés présentes dans les deux tables. Pour chacune, préciser son type primaire ou étrangère en justifiant votre choix.

Correction ?

...CORRECTION...

jeu.id désigne une clé PRIMAIRE car l'attribut est souligné.

jeu.id_support désigne une clé ETRANGERE car l'attribut est précédé d'un #.

console.id désigne une clé PRIMAIRE car l'attribut est souligné.

02-B° Les valeurs de l'attribut id de la relation jeu (la première "colonne" à gauche) doivent-elles être toutes différentes ? Quelle contrainte doit respecter cet attribut ?

Correction ?

...CORRECTION...

Oui : nous avions vu qu'il s'agit de la clé primaire de la relation.

Elle doit respecter la contrainte d'intégrité d'UNICITE (UNIQUE et NOT NULL).

02-C° Les valeurs de l'attribut id_support de la relation jeu (la dernière "colonne" de la table) doivent-elles être toutes différentes ? Quelle contrainte doit respecter cet attribut ?

Correction ?

...CORRECTION...

Non, on peut avoir plusieurs fois la même valeur : id_support est une clé étrangère, plusieurs jeux peuvent mener à la même console.

La contrainte d'intégrité de REFERENCE impose par contre que toutes les valeurs correspondent à une vraie valeur de clé primaire présente dans l'autre table, ou soit NULL.

Nous avons deux tables mais nous voudrions créer un affichage ressemblant à ceci pour notre site Web parlant de jeux vidéo retro de types plateformes : on veut filter uniquement les jeux de plateformes mais obtenir un seul "tableau".

Le principe : faire une jointure.

Qu'est-ce que cela veut dire ?

1.2 Mécanisme de la jointure (interne)

Voir

Principe du JOIN, nommé aussi INNER JOIN

On veut projeter tous les attributs des deux relations en utilisant ce mécanisme :

Pour chaque n-uplet de jeu dont la clé étrangère n'est pas NULL :
Localise le n-uplet de console qui a la bonne valeur de clé primaire.
Concatène les deux p-uplets pour former un nouveau p-uplet.
Ajoute cette concaténation à la projection

Exemple

On obtient alors ceci :

id	jeu	description	sortie	editeur	genre	id_support	id	nom	RAM	date_sortie
7	Flight Simulator	Simulation de piotage d'avion avec des pixels gros comme des camions !	1980	subLOGIC	Simulateur	1	1	Apple II	4 ko	1977-06-10
9	PITFALL!	Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle.	1982	Activision	Plate-formes	2	2	Atari 2600	4 ko	1977-10-14
10	PITFALL!	Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle.	1983	Activision	Plate-formes	3	3	C64	64 ko	1982-08-01
11	Boulder Dash	Rockford, mineur téméraire, ramasse des diamants et tente de ne pas se faire écraser par les énormes pierres instables.	1983	First Star Software	Plate-formes	3	3	C64	64 ko	1982-08-01

n-uplets manquants

Notez que dans une jointure interne, on décide de ne jamais inclure les n-uplets de jeu dont la clé étrangère est NULL (les jeux sans console) ou les n-uplets de console jamais référencés par une clé étrangère (les consoles qui n'ont pas de jeux !).

Une jointure interne ne construit donc que les concaténations où il existe bien une liaison entre un jeu et une console.

On perd donc ici Donkey Kong car sa clé étrangère vaut NULL.

Par contre, chaque console est bien présente car toutes possèdent au moins un jeu.

1.3 Clause JOIN table ON cle_primaire = cle_etrangere

Voir

Principe

On va écrire quelque chose qui voudra dire

REALISE UNE JOINTURE entre un n-uplet de A et un n-uplet de B sous condition d'égalité entre la clé étrangère et la clé primaire.

Exemple avec les relations jeu et console

1
2
3

SELECT *
FROM jeu
JOIN console ON jeu.id_support = console.id

L'ordre dans lequel on fournit les clés n'a pas d'importance :

1
2
3

SELECT *
FROM jeu
JOIN console ON console.id = jeu.id_support

De même, savoir par quelle table on commence n'a pas d'importance :

1
2
3

SELECT *
FROM console
JOIN jeu ON jeu.id_support = console.id

Syntaxe SQL

Pour formuler cela en SQL, il faut utiliser deux nouveaux mots-clés :

FROM a
JOIN b
ON a.cle_1 = b.cle_2

Notez bien que la syntaxe est totalement symétrique : il suffit d'identifier les deux tables et les deux clés qui permettent de faire la liaison.

Requête plus complète

On peut préciser ce qu'on veut projeter (quelles colonnes) ou filtrer (quelles lignes) avec la syntaxe classique :

SELECT jeu.jeu, console.nom
FROM jeu
JOIN console ON jeu.id_support = console.id
WHERE jeu.genre = "Plateforme"

03° Même si c'est possible, pourquoi est-il préférable d'éviter d'utiliser un même nom pour une table et l'un de attributs ?

SELECT jeu.jeu, console.nom
FROM jeu
JOIN console ON jeu.id_support = console.id
WHERE jeu.genre = "Plateforme"

Correction ?

...CORRECTION...

Tout simplement car c'est source de confusion.

En python, on n'utilise pas non plus une variable locale portant le même nom que sa fonction par exemple.

C'est d'ailleurs l'une des raisons qui peut justifier d'utiliser une Majuscule comment première lettre d'une table. La confusion disparaîtrait.

Sauf si, on décide de passer un jour sur un système SQL ne gérant pas la casse...

Bref : autant ne JAMAIS faire cela. Pas de nom identique, pas de problème.

2 - Jointure entre les tables job-title

⇩ ⇧ ⤊

Schéma relationnel de la base de données

On fournit, cette fois, le schéma de la base sous forme de graphique. Les clés primaires sont soulignées, les clés étrangères préfixées par # (qui n'est pas dans son nom, c'est juste un indice visuel) et les liaisons entre clés étrangères et primaires représentées par une flèche.

Remarquez bien que ce schéma comporte donc le schéma relationnel de chacune des tables ainsi que d'indications sur la manière d'utiliser les clés.

Création des tables en SQL

CREATE TABLE person (
    id  INTEGER,
    name    VARCHAR(50),
    birth_year  INTEGER,
    death_year  INTEGER,
    primary_profession  TEXT,
    PRIMARY KEY(id AUTOINCREMENT)
;

CREATE TABLE title (
    id INTEGER,
    title_type  VARCHAR(15),
    primary_title   VARCHAR(40),
    original_title  VARCHAR(40),
    start_year  INTEGER,
    end_year  INTEGER,
    runtime_minutes INTEGER,
    genres  TEXT,
    PRIMARY KEY(id AUTOINCREMENT)
);

CREATE TABLE job (
    idtitle  INTEGER,
    ordering    INTEGER,
    idperson  INTEGER,
    category VARCHAR(40),
    precision VARCHAR(40),
    characters  VARCHAR(100),
    PRIMARY KEY(idtitle, ordering, idperson),
    FOREIGN KEY (idtitle) REFERENCES title(id),
    FOREIGN KEY (idperson) REFERENCES person(id)
);

04° Un élève fournit ce schéma relationnel de la relation job (sous forme abstraite). Cette représentation est incomplète. Que manque-t-il pour avoir tous les points ?

job (
    idtitle  INTEGER,
    ordering  INTEGER,
    idperson  INTEGER,
    category   VARCHAR(40),
    precision   VARCHAR(40),
    characters   VARCHAR(100)
 )

Correction ?

...CORRECTION...

Il manque le soulignement des trois attributs servant à définir la clé prmimaire.

Il manque également les # pour indiquer que idtitle et idperson sont des clés étrangères vers les tables title et person.

job (
    #idtitle  INTEGER,
    ordering  INTEGER,
    #idperson  INTEGER,
    category   VARCHAR(40),
    precision   VARCHAR(40),
    characters   VARCHAR(100)
 )

05° Comment doit-on formuler précisement JOIN table ON cle_primaire = cle_etrangere si on veut réaliser la jointure entre les tables title et job ?

Correction ?

...CORRECTION...

FROM job
JOIN titre ON title.id = job.idtitle

ou ceci, en inversant l'ordre des deux clés :

FROM job
JOIN titre ON job.idtitle = title.id

Ou encore

FROM title
JOIN job ON title.id = job.idtitle

ou ceci, en inversant l'ordre des deux clés :

FROM title
JOIN job ON job.idtitle = title.id

06° Ouvrir la base de données avec DB Browser si ce n'est pas encore fait. Projeter, pour chaque emploi dans jobi :

la catégorie de l'emploi,
le vrai nom du titre (primary_title, pas juste la valeur de la clé étrangère) et
l'identifiant idperson de l'employé (pas son vrai nom).

Correction ?

...CORRECTION...

On réalise la jointure en comparant la clé étrangère job.idtitle à la clé primaire title.id.

1
2
3

SELECT job.category, title.primary_title, job.idperson
FROM job
JOIN title ON title.id = job.idtitle

07° Quelle requête doit-on faire pour obtenir la même chose mais uniquement avec les films de 2016 et plus ? Trier le résultat par année de sortie, du plus récent au plus ancien. On rajoutera l'année dans la réponse de façon à vérifier que la requête fonctionne bien.

Correction ?

...CORRECTION...

SELECT job.category, title.primary_title, job.idperson, title.start_year
FROM job
JOIN title ON title.id = job.idtitle
WHERE title.start_year > 2015
ORDER BY title.start_year DESC

3 - Jointure job-person

⇩ ⇧ ⤊

Nous allons maintenant faire le lien entre la relation job et la relation person.

08° Projeter la clé primaire id et le nom de la personne occupant un emploi ainsi que le type de poste (category) qu'elle occupe pour chaque emploi qu'on peut trouver dans la base de données.

Correction ?

...CORRECTION...

1
2
3

SELECT person.id, person.name, job.category
FROM job
JOIN person ON job.idperson = person.id

4 - Double jointure

⇩ ⇧ ⤊

On remarquera que la table job est particulière puisqu'elle contient deux clés étrangères. Elle joue un rôle central dans les jointures entre les titres et les personnes qui y ont participé : title - job - person.

En réalité, on peut utiliser plusieurs JOIN ...ON ... = ... dans une requête. Nous allons donc pouvoir faire la liaison entre un titre et les gens qui ont travaillé sur ce projet.

09° Expliquer clairement ce que fait cette requête, ligne par ligne.

SELECT title.primary_title, person.name, job.category, job.characters
FROM job
JOIN person ON job.idperson = person.id
JOIN title ON job.idtitle = title.id
WHERE title.start_year = 2024

Correction ?

...CORRECTION...

On va projeter des nuplets en utilisant les trois tables à la fois.

L2 : On commence par récupérer les attributs de la table job.

L3 : On rajoute ensuite les attributs de la relation person (en cherchant une liaison de type valeur de clé étrangère = valeur de clé primaire).

L4 : On rajoute finalement les attributs de la relation title (en cherchant une liaison de type valeur de clé étrangère = valeur de clé primaire).

L5 : On filtre uniquement les titres sortis en 2024.

10° Projeter la même chose mais uniquement pour le film "Die Hard".

Correction ?

...CORRECTION...

SELECT title.primary_title, person.name, job.category, job.characters
FROM job
JOIN person ON job.idperson = person.id
JOIN title ON job.idtitle = title.id
WHERE title.primary_title = "Die Hard"

AS

Le nom des tables sont parfois assez longues à taper. On peut utiliser des alias de façon à simplifier les notations.

SELECT t.primary_title, p.name, j.category
FROM job as j
JOIN person AS p ON j.idperson = p.id
JOIN title AS t ON j.idtitle = t.id
WHERE t.title_type = "tvSeries"

En réalité, cela ne sert pas qu'à cela car changer de nom permet de gérer plus les requêtes imbriquées. Mais tout cela est hors programme.

5 - Autres exercices

⇩ ⇧ ⤊

11° Projeter le nombre d'emplois liés aux productions de type film (type "movie").

Il suffit donc de faire la jointure entre deux tables et filtrer sur les types de productions.

Une fois que cela marche, il suffit d'utiliser un COUNT sur les n-uplets récupérés.

Correction ?

...CORRECTION...

SELECT COUNT(*)
FROM job
JOIN title ON job.idtitle = title.id
WHERE title.title_type = "movie"

12° Comptabiliser le nombre de films de 180 minutes et plus pour lesquels on dispose d'employés : il suffit de chercher les valeurs DISTINCTES au niveau du titre de l'un des attributs de cette jointure titre-emploi et de les compter.

Correction ?

...CORRECTION...

SELECT COUNT(DISTINCT title.primary_title)
FROM job
JOIN person ON job.idperson = person.id
JOIN title ON job.idtitle = title.id
WHERE title.title_type = "movie" AND title.runtime_minutes >= 180

13° Obtenir la liste des titres dans lesquels Bruce Willis a participé d'une façon ou d'une autre. Vous pourriez utiliser une recherche de ce type :

`5`	`WHERE person.name LIKE '%Bruce Willis%'`

Pensez à utiliser DISTINCT pour ne pas compter deux fois les films où il intervient à la fois en tant qu'acteur et que réalisateur par exemple.

Correction ?

...CORRECTION...

SELECT DISTINCT title.primary_title, title.start_year
FROM job
JOIN person ON job.idperson = person.id
JOIN title ON job.idtitle = title.id
WHERE person.name LIKE '%Bruce Willis%'

14° Obtenir la liste des films dans lesquels Bruce Willis a participé en tant qu'acteur. On affichera également les rôles qu'il incarne dans ces films.

Correction ?

...CORRECTION...

SELECT title.primary_title, title.start_year, job.characters
FROM job
JOIN person ON job.idperson = person.id
JOIN title ON job.idtitle = title.id
WHERE person.name LIKE '%Bruce Willis%'
AND job.category = 'actor'
AND title.title_type = 'movie';

6 - FAQ

⇩ ⇧ ⤊

Rien pour le moment

Activité publiée le 20 01 2021
Dernière modification : 24 01 2021
Auteur : ows. h.

⏪

⏫

⏩