Exemple : Pokémon#

Diagramme de la base de donnée :

Voici une commande pour afficher le schéma de chaque table :

.schema

Les clés primaires sont indiquées avec PRIMARY KEY.
Dans la table evolue_en, FOREIGN KEY(pokemon_evol_id) REFERENCES pokemons(id) indique que pokemon_evol_id est une clé étrangère de evolue_en et qu’elle fait référence à la clé primaire id de la table pokemons.

Opérations ensemblistes#

Union : UNION#

Pour obtenir une table contenant les pokémons et les dresseurs :

SELECT * FROM pokemons UNION SELECT * FROM dresseurs

Intersection : INTERSECT#

Il n’y a aucun pokémon qui est aussi un dresseur :

SELECT * FROM pokemons INTERSECT SELECT * FROM dresseurs

Différence : EXCEPT#

Comme aucun pokémon n’est un dresseur, prendre la différence renvoie la même table que pokemons :

SELECT * FROM pokemons EXCEPT SELECT * FROM dresseurs

Produit cartésien#

Un produit cartésien de deux tables R1 et R2 donne toutes les façons de combiner un enregistrement de R1 et un enregistrement de R2 :

SELECT * FROM types, dresseurs

Remarque : La requête ci-dessus n’a aucun intérêt, c’est juste un exemple de produit cartésien…

Ambigüité#

Si la même colonne est présente dans plusieurs tables, il faut lever l’ambigüité en préfixant par le nom de la table :

SELECT nom FROM pokemons, dresseurs -- nom est un attribut des 2 tables

SELECT pokemons.nom FROM pokemons, dresseurs -- ok

Jointure simple#

Exercice

Écrire une requête SQL pour afficher le nom de chaque pokémon avec son niveau d’évolution.

Renommage#

Il est possible de renommer un attribut avec AS :

SELECT libelle as nom, pp as point_de_pouvoir
FROM attaques

Remarque : Le as est facultatif.

Si on renomme une colonne, il faut ensuite y faire référence avec le nouveau nom :

SELECT libelle nom, pp point_de_pouvoir
FROM attaques
WHERE pp > 35

On peut aussi renommer une table :

SELECT d.niveau FROM detient_pokemons d

Exercice

Écrire une requête SQL pour afficher le nom de chaque attaque avec son type.

Jointures multiples#

Si on a besoin de \(3\) tables (ou plus), on peut enchaîner plusieurs JOIN :

FROM ... 
JOIN ... ON ... = ... 
JOIN ... ON ... = ...

Exercice

Afficher chaque dresseur avec chacun de ses pokémons.

Auto-jointure#

Parfois, il est nécessaire d’utiliser plusieurs fois la même table, donc de joindre une table avec elle-même.

Exercice

Afficher chaque pokémon avec son évolution et son niveau d’évolution.

LEFT JOIN#

Exercice

Écrire une requête SQL pour afficher chaque pokémon avec son évolution. S’il n’a pas d’évolution, on affichera null.

GROUP BY#

Exercice

Afficher chaque dresseurs avec son nombre de pokémons et leur niveau moyen

Exercice

Afficher chaque pokémon avec son dresseur et le nombre de pokémons de ce dresseur

Exercice

Afficher les dresseurs qui possèdent au moins 4 pokémons de niveau au moins 30