SQL - Sélectionner des données

SQL - Sélectionner des données#

Schéma relationnel

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 de notre table Livre.

SELECT titre, auteur FROM Livre

Et celle-ci affiche le nom et le prénom de tous les utilisateurs de la bibliothèque.

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 les titres des livres écrits par J.K. Rowling après 2003.

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

Important

Une date s'écrit entre guillemets simples et se compare comme du texte, caractère par caractère. Il faut donc toujours donner une date complète au format 'AAAA-MM-JJ'. Écrire date_pub > 2003 (sans guillemets et sans mois ni jour) ne produit aucune erreur, mais donne un résultat faux : SQL compare alors un nombre à du texte et retourne tous les livres.

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%'

Exercices#

Exercice 15#

Voici la table canton, qui servira de base aux deux prochains exercices.

Avant d'exécuter les requêtes ci-dessous, lisez-les attentivement et prédisez leur résultat. La requête est-elle correcte ou produira-t-elle une erreur ? Si elle est correcte, combien de lignes affichera-t-elle ? Indiquez ce nombre dans la case à droite (ou le mot erreur si la requête ne fonctionne pas), puis exécutez la requête pour vérifier.

  1. select * from canton where nb_communes = 45;


  2. select * from canton where chef_lieu = Coire;


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


  4. select * from canton where population > 500000;


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


  6. select * from canton order by superficie asc;


  7. select * from canton where nom like 'F';


  8. select * from canton where chef_lieu = 'coire';


  9. select nom, superficie from canton order by nb_communes desc;


  10. select * from canton
  where nb_communes > 100 and population > 300000 and superficie < 3000;


  11. select nom from canton where nom = 'Fribourg' and nom = 'Genève';

Explications

  1. Un seul canton a 45 communes : Genève.

  2. Cette requête produit une erreur, car il manque les guillemets simples autour de Coire. Sans eux, SQL cherche une colonne appelée Coire.

  3. Un seul canton s'appelle Fribourg. Notez que seules deux colonnes sont affichées, mais cela ne change rien au nombre de lignes.

  4. Genève, Berne et Zurich dépassent les 500'000 habitants.

  5. Les opérateurs de comparaison pour du texte utilisent l'ordre alphabétique.
    Exemples: 'a' < 'b' ou 'p' > 'd'. Les abréviations placées avant GR dans l'ordre alphabétique sont BE, FR et GE.

  6. Un ORDER BY ne filtre rien : il ne fait que changer l'ordre des lignes. Les 7 cantons sont donc affichés.

  7. Aucune ligne. LIKE 'F' cherche un nom exactement égal à F. Pour trouver les noms qui commencent par F, il faut écrire LIKE 'F%'.

  8. Aucune ligne. Contrairement au LIKE, l'opérateur = distingue les majuscules des minuscules : 'coire' n'est pas 'Coire'.

  9. Sept lignes également. On peut parfaitement trier selon une colonne qui n'est pas affichée : le tri se fait avant la sélection des colonnes.

  10. Rien n'empêche de chaîner plus de deux conditions avec and. Une ligne n'est retenue que si toutes les conditions sont vraies : Fribourg, Zurich et Tessin remplissent les trois.

  11. Aucune ligne : un canton ne peut pas s'appeler à la fois Fribourg et Genève. Quand on veut plusieurs valeurs possibles pour une même colonne, c'est un or qu'il faut utiliser.

Exercice 16#

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;

Exercice 17#

Chacune des requêtes ci-dessous, écrite sur la base de données de la bibliothèque, comporte une erreur. Parfois l'erreur produit un message rouge, parfois la requête s'exécute mais ne retourne pas ce qui était demandé. Corrigez-les.

  1. On souhaite afficher le titre des livres coûtant moins de 10 CHF.

    SELECT titre WHERE prix < 10

  2. On souhaite afficher tous les livres de Victor Hugo.

    SELECT * FROM Livre WHERE auteur = Victor Hugo

  3. On souhaite afficher tous les livres du plus cher au moins cher.

    SELECT * FROM Livre ORDER prix BY DESC

  4. On souhaite afficher tous les livres de la saga Harry Potter.

    SELECT * FROM Livre WHERE titre LIKE 'Harry'

  5. On souhaite afficher les livres qui coûtent moins de 10 CHF ou plus de 20 CHF.

    SELECT * FROM Livre WHERE prix < 10 AND prix > 20

Solution

  1. Le FROM a été oublié : SQL ne sait pas dans quelle table chercher.

    SELECT titre FROM Livre WHERE prix < 10

  2. Il manque les guillemets simples autour du texte recherché.

    SELECT * FROM Livre WHERE auteur = 'Victor Hugo'

  3. Les deux mots-clefs ORDER BY vont ensemble et se placent avant le nom de la colonne.

    SELECT * FROM Livre ORDER BY prix DESC

  4. La requête ne produit aucune erreur, mais retourne zéro ligne : sans %, le LIKE cherche un titre exactement égal à Harry.

    SELECT * FROM Livre WHERE titre LIKE 'Harry%'

  5. Aucune erreur non plus, mais aucun résultat : un prix ne peut pas être à la fois inférieur à 10 et supérieur à 20. Il fallait un OR.

    SELECT * FROM Livre WHERE prix < 10 OR prix > 20

Exercice 18#

Les exercices suivants utilisent la table jeu ci-dessous, qui recense quelques jeux vidéo.

Pour commencer, où faut-il placer le signe % ? On cherche ici des auteurs dans une table Livre.

  1. Le nom de l'auteur commence par Zola

  2. Le nom de l'auteur se termine par Zola

  3. Le nom de l'auteur contient Zola

Écrivez maintenant les requêtes suivantes sur la table jeu. Toutes nécessitent un LIKE.

  1. Afficher tous les jeux dont le titre commence par Super.

Solution

select * from jeu where titre like 'Super%';

  1. Afficher tous les jeux dont le titre contient Mario.

Solution

select * from jeu where titre like '%Mario%';

  1. Afficher le titre et le studio des jeux dont le nom du studio se termine par o et qui coûtent plus de 50 CHF.

Solution

select titre, studio from jeu where studio like '%o' and prix > 50;

  1. Afficher les jeux dont le genre se termine par ure et dont la note dépasse 9.2.

Solution

select * from jeu where genre like '%ure' and note > 9.2;

  1. Afficher les jeux dont le titre contient la lettre a, qui coûtent moins de 20 CHF et dont la note est supérieure ou égale à 8.8.

Solution

select * from jeu where titre like '%a%' and prix < 20 and note >= 8.8;

Rien n'empêche d'enchaîner trois conditions avec deux and : la ligne n'est retenue que si les trois sont vraies en même temps. Cette requête retourne 3 jeux. Notez au passage que le LIKE ne fait pas la différence entre majuscules et minuscules : Portal 2 est bien trouvé.

Exercice 19#

Toujours sur la table jeu, écrivez les requêtes suivantes. Elles nécessitent toutes un DISTINCT, un ORDER BY, ou les deux.

  1. Afficher la liste des genres présents dans la table, sans doublon.

Solution

select distinct genre from jeu;

Sans le DISTINCT, la requête afficherait 12 lignes (une par jeu) au lieu des 8 genres réellement différents.

  1. Afficher la liste des studios, sans doublon, par ordre alphabétique.

Solution

select distinct studio from jeu order by studio asc;

  1. Afficher les années de sortie, sans doublon, de la plus récente à la plus ancienne.

Solution

select distinct annee from jeu order by annee desc;

  1. Afficher le titre et la note des jeux d'aventure ou de plateforme, du mieux noté au moins bien noté.

Solution

select titre, note from jeu where genre = 'Aventure' or genre = 'Plateforme' order by note desc;

  1. Afficher le titre et le prix des jeux qui coûtent moins de 20 CHF, sont sortis après 2014 et ont une note supérieure à 8.5, du moins cher au plus cher.

Solution

select titre, prix from jeu where prix < 20 and annee > 2014 and note > 8.5
order by prix asc;

Les trois conditions sont chaînées par deux and, et le order by se place toujours après l'ensemble du where.

Exercice 20#

Cet exercice fonctionne dans l'autre sens : les requêtes sont données, à vous de dire ce qu'elles font. Répondez en une phrase, en français, sans les exécuter. Elles portent sur la base de données de la bibliothèque.

  1. SELECT DISTINCT auteur FROM Livre ORDER BY auteur ASC

  2. SELECT titre, prix FROM Livre WHERE prix > 15 OR auteur = 'Victor Hugo'

  3. SELECT nom FROM Utilisateur WHERE role != 'élève'

  4. SELECT * FROM Livre WHERE titre LIKE '%Potter%' AND prix < 10 AND date_pub > '2000-01-01'

  5. SELECT titre, date_pub FROM Livre WHERE date_pub < '1900-01-01' ORDER BY date_pub ASC

Solution

  1. Affiche la liste de tous les auteurs de la bibliothèque, sans doublon, par ordre alphabétique.

  2. Affiche le titre et le prix des livres qui coûtent plus de 15 CHF, ainsi que ceux écrits par Victor Hugo (même s'ils coûtent moins de 15 CHF).

  3. Affiche le nom de tous les utilisateurs qui ne sont pas des élèves, c'est-à-dire les enseignants et les bibliothécaires.

  4. Affiche toutes les informations des livres de la saga Harry Potter qui coûtent moins de 10 CHF et qui ont été publiés après le 1er janvier 2000. Un seul livre remplit les trois conditions.

  5. Affiche le titre et la date de publication des livres publiés avant 1900, du plus ancien au plus récent.