Bases de données

  • production correspond à la porduction mondiale annuelle du fruit (en tonnes),
  • departement correspond au département français ayant la plus forte production
    du fruit,
  • paysUE correspond au plus grand pays producteur de l’UE,
  • paysMonde au plus grand pays producteur mondial du fruit.

Schéma de tables :

Donne les relation entre les différentes tables. Précise en particulier les clés primaires, les clés étrangères et les liens entre clés primaires et clés étrangères (une clé étrangère référence toujours une clé primaire d’une autre table).

Le domaine correspond à l’ensemble des valeurs possibles pour un champ de l’attribut.

Par exemple, les attributs emoji et drapeau attendent des chaînes de caractères
d’un seul caractère.

Remarque : une clé primaire n’est pas nécessairement associée à un unique attribut.

Requêtes SQL

Projection + sélection

Vocabulaire :

  • projection : filtrage des colonnes/attributs (via SELECT)
  • sélection : filtrage des lignes/enregistrements (via WHERE)

Requête pour afficher toute une table :

SELECT * 
FROM prodfruits;

* joue le rôle d’un joker

Requête pour afficher seulement
une sélection d’attributs (= projection) :


SELECT nom, departement 
FROM prodfruits;

Requête pour filtrer les enregistrements
(= selection) :


SELECT * 
FROM prodfruits
WHERE production > 100000;

Le WHERE est suivi d’une condition.

Opérateurs utilisables dans les conditions :

=, <>, <, <=, >, >=, AND, OR, NOT, IN

Exemple, pour un encadrement :

SELECT * 
FROM prodfruits
WHERE production > 50000 AND production < 100000 ;

On peut aussi réaliser des opérations mathématiques sur les attributs grâce à : +, -, *, /.

On obtient par exemple le même résultat
que précédemment en écrivant :

SELECT * 
FROM prodfruits
WHERE production*2+50000 > 150000 AND production*2+50000 < 250000 ;

🤨🤨🤨

Écrire une requête pemettant d’afficher uniquement les enregistrements où la chine
n’est pas le premier producteur mondial.

SELECT * 
FROM prodfruits
WHERE paysMonde <> "Chine" ;

On peut bien sûr combiner projection et sélection :


SELECT nom,Departement
FROM prodfruits
WHERE paysUE = "Italie" ;

Pour se limiter aux n premiers résultats,
on utilise LIMIT n, et grâce à OFFSET m,
on peut sauter les m premiers.


SELECT *
FROM drapeaux
LIMIT 3
OFFSET 2 ;

On peut aussi combiner des projections/sélections à partir de différentes tables.

Lorsque des attributs de tables différentes
ont le même nom, il faut préciser leur table d’origine via un point (table.attribut) :


SELECT departement, emoji
FROM prodfruits, emojifruits
WHERE prodfruits.nom = emojifruits.nom AND paysUE = "Espagne" ;

Sans la condition
prodfruits.nom = emojifruits.nom,
on aurait obtenu le produit cartésien
des deux sous-tables…


SELECT departement, emoji
FROM prodfruits, emojifruits
WHERE paysUE = "Espagne" ;

WHERE prodfruits.nom = emojifruits.nom
a donc permi d’opérer une jointure interne
entre les tables.

On verra plus loin une méthode plus naturelle.

Le mot clé DISTINCT permet d’éliminer les doublons dans les enregistrements.

Montrons-le sur l’exemple précédent :

SELECT DISTINCT departement
FROM prodfruits, emojifruits
WHERE paysUE = "Espagne" ;

Tri des résultats

Pour trier suivant un attribut, on utilise
ORDER BY attribut (et pour que le tri
soit descendant, on ajoute DESC)


SELECT nom,production
FROM prodfruits
ORDER BY production DESC
LIMIT 3 ;

Utilisation d’un alias

Un alias, introduit par AS permet
de renommer un attribut dans l’affichage :


SELECT nom AS "fruits espagnols", production
FROM prodfruits
WHERE paysUE = "Espagne" ;

Un alias permet aussi de donner un nom à une table obtenue par une requête, ce qui rend possible l’enchassement des requêtes
les unes dans les autres.

La requête suivante réalise par exemple le produit cartésien des deux premiers départements
avec les 3 premiers emojis.


SELECT tab1.departement, tab2.emoji
FROM (SELECT *
      FROM prodfruits
      LIMIT 2) AS tab1,
     (SELECT *
      FROM emojifruits
      LIMIT 3) AS tab2 ;

Opérateurs ensemblistes

Produit cartésien

On l’a déjà croisé par deux fois.

On obtient un produit cartésient en combinant
des SELECT de tables différentes
sans condition de jointure.

Union

Permet de réunir deux tables ou deux sous-tables.


SELECT *
FROM emojifruits
UNION
SELECT *
FROM flags ;

Rq : il faut que les deux tables
aient le même nombre de colonnes.

INTERSECT

Ne garde que les colonnes communes.

Dans cet exemple, on obtient les noms de fruits
de la table prodfruits ayant un emoji
dans la table emojifruits.

SELECT nom
FROM prodfruits
INTERSECT
SELECT nom
FROM emojifruits ;

Rq : il faut que les deux tables
aient le même nombre de colonnes.

EXCEPT

Revient à retirer ce qu’il y a en commun
entre deux tables.


SELECT nom
FROM prodfruits
EXCEPT
SELECT nom
FROM emojifruits ;

Rq : il faut que les deux tables
aient le même nombre de colonnes.

Attention, l’ordre compte :


SELECT nom
FROM emojifruits
EXCEPT
SELECT nom
FROM prodfruits ;

Agrégation

On peut utiliser les fonctions suivantes pour réaliser des calculs sur des attributs
(et ainsi agréger ses valeurs) :

  • MIN : donne la valeur minimale
  • MAX : donne la valeur maximale
  • SUM : donne la somme des valeurs
  • AVG : donne la moyenne des valeurs
  • COUNT : donne le nombre de valeurs
SELECT AVG(production) AS "production moyenne"
FROM prodfruits ;

🤨🤨🤨

Écrire une requête donnant le nombre de pays différents, autre que la Chine, plus gros producteurs mondiaux pour au moins un fruit.

SELECT COUNT(paysMonde)
FROM (SELECT DISTINCT paysMonde
      FROM prodfruits
      WHERE paysMonde <> "Chine") ;

GROUP BY

Grâce à la clause GROUP BY, on peut grouper ensemble (partitionner) des lignes qui ont les mêmes valeurs dans une ou plusieurs colonne.

Mais c’est l’application d’une fonction sur chaque agregat obtenu qui en fait tout son intérêt.

Cela va permettre par exemple de compter
le nombre de positions dominantes
d’un département dans la production d’un fruit :


SELECT departement,COUNT(departement)
FROM prodfruits
GROUP BY departement ;

Demandons maintenant la production totale par département (production totale mondiale pour
les fruits où le département arrive en tête) :

SELECT departement, SUM(production) AS "production totale"
FROM prodfruits
GROUP BY departement

On peut filtrer (sélection) l’agrégat obtenu par un GROUP BY à l’aide du mot clé HAVING.

Cela permet par exemple de ne conserver que les départements dominant la production française pour au moins deux fruits.


SELECT departement,COUNT(departement) AS "nombre de fruits"
FROM prodfruits
GROUP BY departement
HAVING "nombre de fruits" > 1 ;

HAVING fonctionne comme WHERE
en demandant une condition.

Les deux opèrent une sélection
(un filtrage sur les lignes).

La différence réside dans leur ordre d’utilisation : WHERE filtre une projection (après une SELECT)
et HAVING filtre un agrégat (après un GROUP BY).

La requête suivante sélectionne parmi les départements ayant une production mondiale supérieure à 10000 tonnes, ceux qui dominent
le marché avec au moins deux fruits.


SELECT departement,COUNT(departement) AS "nombre de fruits"
FROM prodfruits
WHERE production > 100000
GROUP BY departement
HAVING "nombre de fruits" > 1 ;

JOINTURE

Nous n’étudierons que les équi-jointures internes.

  • interne signifie qu’on ne garde que
    ce qu’il y a en commun entre deux tables.
  • equi signifie que la jointure se fait
    là où il y a égalité.

On utilise la structure
table1 JOIN table2
ON condition d'égalité.

Lorsqu’on joint deux tables, le plus souvent
la jointure se fait entre une clé primaire
et la clé étrangère référant cette clé primaire.

Exemple :

Supposons que l’on veuille remplacer les noms des fruits par leur emoji dans la table prodfruits.


SELECT emoji AS fruit, production, departement, paysUE, paysMonde
FROM prodfruits
JOIN emojifruits
ON prodfruits.nom = emojifruits.nom ;

Remarque : les fruits sans emoji sont éliminés.

Remplaçons maintenant aussi
les noms des pays par leurs drapeaux.

Commençons par les pays européens :


SELECT emoji AS fruit, production, departement, drapeau AS paysUE, paysMonde 
FROM prodfruits
JOIN emojifruits
ON prodfruits.nom = emojifruits.nom
JOIN flags
ON prodfruits.paysUE = flags.pays ;

Si on veut aussi remplacer les champions mondiaux par leurs drapeaux, on se heurte à un problème : l’attribut drapeau devient ambigü…

On peut s’en sortir en créant
des sous-tables intermédiaires :

SELECT emoji AS fruit, production, departement, drapUE AS paysUE, drapMonde AS paysMonde 
FROM prodfruits
JOIN emojifruits
ON prodfruits.nom = emojifruits.nom
JOIN (SELECT pays, drapeau AS drapUE 
      FROM flags) AS tabdrapmonde
ON prodfruits.paysUE = tabdrapmonde.pays
JOIN (SELECT pays, drapeau AS drapMonde 
      FROM flags) AS tabdrapue
ON prodfruits.paysMonde = tabdrapue.pays ;

Retour site