SQL - Sélectionner des données

Solution

create table canton (
  nom text not null,
  abr text not null,
  chef_lieu text not null,
  nb_communes int not null,
  population int not null,
  superficie decimal(6,2) not null
);
insert into canton values
  ('Fribourg', 'FR', 'Fribourg', 126, 334465, 1670.7),
  ('Genève', 'GE', 'Genève', 45, 514114, 282.48),
  ('Berne', 'BE', 'Berne', 335, 1051437, 5959.44),
  ('Zurich', 'ZH', 'Zurich', 160, 1579967, 1729),
  ('Tessin', 'TI', 'Bellinzone', 106, 354023, 2812.2),
  ('Grison', 'GR', 'Coire', 101, 202538, 7105.44),
  ('Uri', 'UR', 'Altdorf', 19, 37317, 1076.57);

SQL - Sélectionner des données#

Sélectionner des données#

L'instruction SELECT ... FROM ... permet de rechercher des données dans une table. On fait suivre le mot-clef SELECT du nom de(s) colonne(s) que l'on souhaite afficher, et le FROM de la table contenant ces données. Ainsi, la requête suivante nous permet d'afficher tous les titres et auteurs dans notre table Livre.

SELECT nom, prenom FROM Utilisateur

Si on souhaite ne pas avoir de lignes "doublons" dans les résultats, on peut faire suivre SELECT du mot-clef DISTINCT afin de les retirer du résultat de la recherche et n'avoir ainsi que des lignes uniques.

SELECT DISTINCT auteur FROM Livre

On peut également utiliser SELECT * pour sélectionner toutes les colonnes d'un seul coup.

SELECT * FROM Livre

Trier les données#

Les requêtes SELECT peuvent être suivies des mots-clef ORDER BY afin de trier les résultats par ordre croissant/ascendant (ASC) ou décroissant/descendant (DESC). Pour cela, il faut faire suivre le ORDER BY de la colonne selon laquelle trier les données, ainsi que de ASC ou DESC pour donner l'ordre de tri. La requête suivant permet ainsi d'afficher tous les livres du plus cher au moins cher.

SELECT titre, prix FROM Livre
ORDER BY prix DESC

Filtrer les données#

Les résultats obtenus à l'aide d'une requête SELECT ... FROM ... peuvent être filtrés en faisant suivre cette requête d'un WHERE. Ce mot-clef est suivi d'une condition qui s'écrit de manière similaire à Python en utilisant les opérateurs de comparaisons =, !=, >, >=, <, <=. La requête suivante permet par exemple de sélectionner toutes les lignes où le prix est inférieur ou égal à 10CHF.

SELECT * FROM Livre WHERE prix <= 10

Celle ci-dessous permet de sélectionner les titres de livre écrit par J.K. Rowling.

SELECT titre FROM Livre WHERE auteur = 'J.K. Rowling'

Opérateurs logiques#

Comme en Python, il est possible de chaîner plusieurs conditions avec les opérateurs logiques AND et OR. La requête suivante permet d'afficher tous les livres écrits par Alexandre Dumas ou par Gustave Flaubert.

SELECT titre FROM Livre WHERE auteur = 'Alexandre Dumas' OR Auteur = 'Gustave Flaubert'

La requête suivante permet d'afficher toutes les infos des livres écrits par J.K. Rowling après 2003.

SELECT titre FROM Livre WHERE auteur = 'J.K. Rowling' AND date_pub > 2003

Opérateur LIKE#

Le mot-clef LIKE peut s'utiliser comme un opérateur de comparaison sur du texte, de manière similaire à un =. Il permet de vérifier qu'une colonne soit semblable à une valeur que l'on définit. Ces similitudes peuvent se décliner de trois manières

  • La valeur commence par un certain texte. Par exemple, pour trouver tous les prénoms d'utilisateur qui commencent par "M" ou pour trouver tous les livres dont le titre commence par "Harry". Pour cela, il faut ajouter le signe % (qui peut être compris par "n'importe quel texte") après la valeur commençant le mot.

    SELECT * FROM Livre WHERE titre LIKE 'Harry%'

  • La valeur se termine par un certain texte. Par exemple pour trouver tous les livres dont la date de publication se termine par "2". Cette fois, le signe % doit précéder la valeur terminant le mot.

    SELECT * FROM Livre WHERE date_pub LIKE '%2'

  • La valeur contient un certain texte. Par exemple pour trouver tous les livres dont le titre contient "le". Le signe % doit ici entourer la valeur à contenir.

    SELECT * FROM Livre WHERE titre LIKE '%le%'

Joindre plusieurs tables#

Les requêtes SELECT précédentes ont permis de rechercher des informations dans une seule table à la fois. Si toutefois on souhaite rechercher tous les titres de livres qu'un certain utilisateur a emprunté, les trois tables devront être mises à contribution dans la même requête.

Pour joindre deux tables entre elles, on utilise JOIN ... ON ... dans une requête SELECT. On fait suivre le JOIN de la table à rajouter à la requête, et le ON des champs qui permettent de lier ces deux tables, avec un signe d'égalité. Ces deux champs sont simplement la clef étrangère et la clef primaire référencée. Par exemple, la requête suivante me permet de lier la table des Utilisateurs avec la table des Emprunts.

SELECT *
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur

Comme vous pouvez le constater avec le résultat de cette requête, tous les emprunts de livre ont été collés à leur utilisateur.

On peut utiliser autant de JOIN que souhaiter pour coller plusieurs tables ensemble. La requête suivante nous permet de coller les 3 tables ensemble :

SELECT *
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur
JOIN Livre ON Emprunt.livre = Livre.numero_isbn

Cette requête peut être simplement complétée par un WHERE et affinée après le SELECT pour trouver tous les noms de livres empruntés par l'utilisateur dont le prénom est Catherine

SELECT Livre.titre
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur
JOIN Livre ON Emprunt.livre = Livre.numero_isbn
WHERE Utilisateur.prenom = 'Catherine'

Exercices#

Exercice 6#

En dessous des requêtes se trouve une table canton. Avant d'exécuter ces requêtes ci-dessous, lisez-les attentivement et prédites leur résultat. La requête est-elle correcte ou produira-t-elle une erreur ? Si elle est correcte, quelle sera la ou les lignes affichées ?

  1. select * from canton where nb_communes = 45;

  2. select * from canton where chef_lieu = Coire;

    Explication

    Cette requête produit une erreur, car il manque les guillemets simples autour de Coire.

  3. select nom, superficie from canton where nom = 'Fribourg';

  4. select * from canton where population > 500000;

  5. select * from canton where abr < 'GR';

    Explication

    Les opérateurs de comparaison pour du texte utilisent l'ordre alphabétique.
    Exemples: 'a' < 'b' ou 'p' > 'd'

  6. select * from canton order by superficie asc;

Exercice 7#

En vous basant sur la table canton ci-dessus, écrivez les requêtes SQL répondant aux critères suivants.

  1. Écrire une requête SQL qui retourne toutes les colonnes du canton dont le chef-lieu est Bellinzone.

Solution

select * from canton where chef_lieu = 'Bellinzone';

  1. Écrire une requête SQL qui retourne toutes les colonnes des cantons dont la population est inférieure à 300'000 habitants.

Solution

select * from canton where population < 300000;

  1. Écrire une requête SQL qui retourne toutes les colonnes des cantons dans l'ordre alphabétique des abréviations.

Solution

select * from canton order by abr asc;

  1. Écrire une requête SQL qui retourne le nom, l'abréviation et le chef-lieu des cantons.

Solution

select nom, abr, chef_lieu from canton;

  1. Écrire une requête SQL qui retourne le nom, l'abréviation et le chef-lieu des cantons ordonnés selon le nombre d'habitants du plus grand au plus petit.

Solution

select nom, abr, chef_lieu from canton order by population desc;

  1. Écrire une requête SQL qui retourne toutes les colonnes des cantons qui ont plus de 100 communes et une population inférieure à 500'000 habitants.

Solution

select * from canton where nb_communes > 100 and population < 500000;

  1. Écrire une requête SQL qui retourne toutes les colonnes des cantons dont le chef-lieu est Altdorf ou le nombre de communes supérieur ou égal à 150.

Solution

select * from canton where chef_lieu = 'Altdorf' or nb_communes >= 150;

  1. Écrire une requête SQL qui retourne le nom des cantons dont l'abréviation n'est pas FR.

Solution

select nom from canton where abr != 'FR';

  1. Écrire une requête SQL qui retourne le nom et l'abréviation des cantons dont la population se trouve entre 300'000 et 500'000 habitants.

Solution

select nom, abr from canton where population > 300000 and population < 500000;
Contenu