Données SQL Interrogation

Identification

Infoforall

26 - SQL : SELECT FROM WHERE


Prerequis : la première activité sur les Bases de Données.

Documents de cours : open document ou pdf

1 - DB Brownser

Intro-01° Télécharger et installer le logiciel DB Brownser si ce n'est pas fait.

Il s'agit d'un Système de Gestion de Bases de Données (SGBD) léger, portable (on peut l'installer sur une clé USB) mais ne gèrant que SQLite.

https://sqlitebrowser.org/

Logo :

Logo DB Brownser

Intro-02° Télécharger la base de données contenant les films, les distributions et les réalisateurs.

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.

Intro-03° Ouvrir ce fichier via DB Browser. Vous pouvez soit juste cliquer dessus, soit ouvrir le logiciel puis ouvrir votre fichier à l'intérieur du logiciel.

Vous devriez aboutir à cela :

DB Brownser

2 - Projection : SELECT FROM

SQL

SQL veut dire Structured Query Language.

Cela veut dire Langage de Requête Structurée.

C'est un langage déclaratif qui permet d'interroger la base de données qui fournit alors une réponse et nous la transmet.

SQL est donc un langage où nous allons devoir placer des informations à certains endroits particuliers. Le tout est donc de connaître les mots-clés et de savoir ce qu'on doit y placer derrière.

Nous avions vu dans l'activité précédente les instructions permettant de créer une relation dans la base de données. Vous n'avez pas à les connaître pour l'épreuve du BAC . J'en parle pour que vous sachiez que c'est possible : on ne crée que rarement des relations en cliquant sur les boutons d'un logiciel mais surtout en fournissant les instructions. L'épreuve porte sur l'utilisation de tables déjà existantes.

Nous allons travailler aujourd'hui sur une base de données créée à partir de fichiers CSV d'un site de notation de films, vidéos et séries : https://www.imdb.com/

Le lien vers les fichiers CSV mis à jour est https://www.imdb.com/interfaces/.

Subsets of IMDb data are available for access to customers for personal and non-commercial use. You can hold local copies of this data, and it is subject to our terms and conditions. Please refer to the Non-Commercial Licensing and copyright/license and verify compliance.

Voici à titre d'exemple, la commande SQL qui permet de créer la table titre.

Déclaration du schéma relationnel de la table (implémentation SQL)

1 2 3 4 5 6 7 8 9 10 11 12
CREATE TABLE titre ( id INTEGER, tconst VARCHAR(9) UNIQUE, 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(tconst) );

Comme on le voit, la commande SQL est très proche de la façon dont nous avons présenté le schéma d'une table.

Remarque 1 : UNIQUE implique que la BDD ne va pas accepter une nouvelle entrée qui aurait la même valeur. Cela permet de respecter l'unicité des entrées.

Remarque 2 : VARCHAR(40) veut dire qu'on crée un attribut de type texte qui pourra prendre jusqu'à 40 caractères. Noter juste CHAR(40) implique que l'attribut fera exactement 40 caractères. De toutes manières, ici SQLite transformera le tout en TEXT à l'interne...

Déclaration du schéma relationnel de la table (version abstraite)

1 2 3 4 5 6 7 8 9 10 11
titre ( id INTEGER, tconst VARCHAR(9) UNIQUE, title_type VARCHAR(15), primary_title VARCHAR(40), original_title VARCHAR(40), start_year INTEGER, end_year INTEGER, runtime_minutes INTEGER, genres TEXT )

Remarque 1 : on souligne souvent la clé primaire dans les schémas relationnels.

Les noms sont relativement explicites mais voici quelques précisions :

  1. id : un numéro d'enregistrement qui aurait pu servir également de clé primaire
  2. tconst : un string servant de clé primaire au titre.
  3. title_type : un string permettant de savoir si c'est un film, une série...
  4. primary_title : un string qui est le titre sous lequel cette oeuvre est connu (la base de données est celle d'un site américain)
  5. original_title : le titre originel de l'oeuvre
  6. start_year et end_year : ce sont les dates de début de production et parfois de fin. Si la fin intervient la même année, le champ est vide.
  7. runtime_minutes : la durée en minutes.
  8. genres : un texte donnant le ou les genres de l'oeuvre. Exemple : Action,Crime,Drama.
Projection avec SELECT FROM

L'opération de projection consiste à récupérer un sous-ensemble des n-uplets : on ne sélectionne que certains attributs.

Choix des attributs

Imaginons qu'on veuille récupérer les noms, les durées et les genres des titres présents dans la base de données.

Table titre avec nom, durée et genres sélectionnés
Sélection du nom, de la durée et des genres

Nous allons donc sélectionner les attributs de la bonne table :

Avec DBBrownser :

  1. On va dans EXECUTER LE SQL
  2. On tape la requête SQL suivante :
  3. 1 2
    SELECT primary_title, runtime_minutes, genres FROM titre;
  4. On exécute en appuyant sur le bouton FLECHE.
Résultat : table titre avec nom, durée et genres sélectionnés

Ordre des attributs dans SELECT

Attention, l'ordre fourni à une influence sur la projection. Si on inverse le genre et la durée, on obtient bien ces attributs dans l'ordre fourni.

Résultat : table titre avec nom, genres et durée sélectionnés

01° Fournir la requête SQL permettant de récupérer le nom, le numéro d'enregistrement et la date de création.

...CORRECTION...

SELECT primary_title, id, start_year FROM titre

On donne le code SQL permettant de créer la table personne qui contient les informations sur les différentes personnes travaillant sur ces réalisations.

1 2 3 4 5 6 7 8 9 10
CREATE TABLE personne ( id INTEGER, nconst VARCHAR(12) UNIQUE, name VARCHAR(50), birth_year INTEGER CHECK(birth_year = 0 OR (birth_year > 1000 AND birth_year < 3000)), death_year INTEGER CHECK(death_year = 0 OR (death_year > 1000 AND death_year < 3000)), primary_profession TEXT, known_for_titles TEXT, PRIMARY KEY(nconst) );

La même chose en version schéma relationnel :

1 2 3 4 5 6 7 8 9
personne ( id INTEGER, nconst VARCHAR(12), name VARCHAR(50), birth_year INTEGER, death_year INTEGER, primary_profession TEXT, known_for_titles TEXT )

02° Fournir la requête SQL permettant de récupérer le nom, la date de naissance et la date de mort de la personne.

...CORRECTION...

SELECT name, birth_year, death_year
FROM personne
Clause DISTINCT

Une opération assez commune consiste à trouver les différentes valeurs attribuées à un attribut.

Pour cela, on peut faire une requête demandant de ramener les différentes valeurs différentes qu'on trouve dans la colonne :

1 2
SELECT DISTINCT title_type FROM titre;
SELECT DISTINCT title_type
FROM titre

3 - Condition avec WHERE

Passons maintenant à des sélections plus fines.

Pour cela, il faut falloir dire :

  1. Ce qu'on veut récupérer (SELECT)
  2. Depuis quelle table (FROM)
  3. Sous quelles conditions (WHERE)
Clause WHERE

La clause WHERE permet de restreindre la sélection en précisant des conditions de sélection.

Nous allons retrouver les opérateurs de comparaisons habituels avec la syntaxe suivante :

  • Egalité : = (contrairement à == en Python)
  • Différence : <>
  • Autres comparaisons : <, <=, >, >=
  • Opérateurs logiques : AND, OR, NOT
  • Opérateurs arithmétiques : +, -, *, /, %
1 2 3
SELECT primary_title, start_year, runtime_minutes FROM titre WHERE start_year = 2020;

1er exemple

On veut récupérer les titres et les durées des titres de 2020.

x

2e exemple

On veut récupérer les titres qui n'ont pas commencé en 2020 et dont la durée est inférieure à 2h.

1 2 3
SELECT primary_title, start_year, runtime_minutes FROM titre WHERE start_year <> 2020 AND runtime_minutes < 120;

Le résultat :

x

Deux remarques :

  1. On voit que le fait que certaines entrées soient "vides" pose problème : Noblesse Oblige apparaît alors que le titre n'a pas de date de création !
  2. On peut très bien ne pas mettre dans le SELECT un élément qui sert à la clause WHERE. La requête ci-dessous est légitime et ne renvoie que la liste des titres :
  3. 1 2 3
    SELECT primary_title FROM titre WHERE start_year <> 2020 AND runtime_minutes < 120;

03° Fournir la requête permettant d'obtenir les titres ayant commencé entre entre 2010 et 2012.

...CORRECTION...

1 2 3
SELECT primary_title FROM titre WHERE start_year >= 2010 AND start_year <= 2012;

Le résultat visuel :

x

04° Fournir la requête permettant d'obtenir les séries ayant commencé après 2018.

Rappel d'une des réponses précédentes :

SELECT DISTINCT title_type
FROM titre

...CORRECTION...

1 2 3
SELECT primary_title, start_year, end_year FROM titre WHERE title_type = "tvSeries";

Le résultat visuel :

x

05° Fournir la requête permettant d'obtenir les séries finies ayant duré au moins 10 ans.

Comme les attributs de date sont des entiers, une date non fournie équivaut à un 0.

...CORRECTION...

1 2 3
SELECT primary_title, start_year, end_year FROM titre WHERE title_type = "tvSeries" AND end_year <> 0 AND (end_year - start_year) >= 10;

Le résultat visuel :

x
Priorités du AND et du OR

Nous avions vu en 1er que a AND b est équivalent à a . b dans l'algèbre de Bool.

Nous avions vu en 1er que a OR b est équivalent à a + b dans l'algèbre de Bool.

On en déduit alors que le AND est prioritaire sur le OR.

06° Voici deux requêtes permettant de récupérer les titres qui correspondent à une sortie entre 2010 et 2015.

Les deux requêtes sont-elles similaires ?

Sans les parenthèses :

1 2 3
SELECT primary_title, start_year, end_year FROM titre WHERE start_year >= 2010 AND start_year <= 2015 OR end_year >= 2010 AND end_year <= 2015;

Avec les parenthèses :

1 2 3
SELECT primary_title, start_year, end_year FROM titre WHERE (start_year >= 2010 AND start_year <= 2015) OR (end_year >= 2010 AND end_year <= 2015);

...CORRECTION...

Les deux requêtes sont bien similaires puisque le AND est prioritaires sur le OR : même sans les parenthèses, on commence par évaluées les AND et ensuite on utilise le OU.

4 - Commande ORDER BY

Trier les résultats avec ORDER BY

La commande ORDER BY permet de trier les lignes dans un résultat d’une requête SQL. On pourra trier les données sur une ou plusieurs colonnes, et par ordre croissant ou decroissant.

Tri simple croissant

1 2 3 4
SELECT primary_title, start_year FROM titre WHERE title_type = "movie" ORDER BY start_year;
x

On remarquera que cette commande est équivalente à la suivante où on utilise le suffixe ASC : ASC (ascendant) est l'option par défaut.

1 2 3 4
SELECT primary_title, start_year FROM titre WHERE title_type = "movie" ORDER BY start_year ASC;

Tri en ordre décroissant

On précise alors le suffixe DESC.

1 2 3 4
SELECT primary_title, start_year FROM titre WHERE title_type = "movie" ORDER BY start_year DESC;
x

Zut ! Les entrées vides sont ici considérées comme supérieures aux autres ! Pourquoi ? Car les zones "vides" ne sont pas bien définies. Nous verrons plus tard comment les modifier avec UPDATE.

Nous allons rajouter une petite condition pour supprimer les entrées incorrectes dont les dates sont non renseignées :

1 2 3 4
SELECT primary_title, start_year FROM titre WHERE title_type = "movie" AND start_year <> '' ORDER BY start_year DESC;
x

Tri multiple

Comme vous pouvez le voir ci-dessous, les n-uplets sont triés par date mais apparaissent dans l'ordre d'inscription dans la base de données. Si vous voulez les trier par date puis par ordre alphabétique, il suffit de rajouter l'attribut primary_title après celui de la date.

1 2 3 4
SELECT primary_title, start_year FROM titre WHERE title_type = "movie" AND start_year <> '' ORDER BY start_year DESC, primary_title ASC;
x

07° Reprendre la table personne. Fournir la requête permettant de recupérer les personnes nées entre 1970 et 1980 en les triant par ordre alphabétique.

1 2 3 4 5 6 7 8 9 10
CREATE TABLE personne ( id INTEGER, nconst VARCHAR(12) UNIQUE, name VARCHAR(50), birth_year INTEGER CHECK(birth_year = 0 OR (birth_year > 1000 AND birth_year < 3000)), death_year INTEGER CHECK(death_year = 0 OR (death_year > 1000 AND death_year < 3000)), primary_profession TEXT, known_for_titles TEXT, PRIMARY KEY(nconst) );

On notera qu'ici, on utilise 0 pour signaler l'absence d'information. Encore une fois, NULL aurait sans doute était préférable.

...CORRECTION...

1 2 3 4
SELECT name FROM personne WHERE birth_year >= 1970 AND birth_year <= 1980 ORDER BY name;

On aurait alors juste un affichage de ce type :

x

08° Reprendre la table titre. Fournir la requête permettant de récupérer les films de 2020 (et uniquement les films) en les triant par durée, du plus long au moins long.

1 2 3 4 5 6 7 8 9 10 11 12
CREATE TABLE titre ( id INTEGER, tconst VARCHAR(9) UNIQUE, 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(tconst) );

...CORRECTION...

1 2 3 4
SELECT primary_title, runtime_minutes, title_type FROM titre WHERE start_year = 2020 and title_type = 'movie' ORDER BY runtime_minutes DESC;

On aurait alors juste un affichage de ce type :

x

5 - Fonctions d'agrégation

MIN, MAX, SUM et AVG

On peut également facilement réaliser des calculs sur l'ensemble des données.

Recherche de maximum

C'est assez étrange au premier abord : on ne place pas cette recherche de maximum dans le WHERE mais directement dans le SELECT. On ramène alors le nuplet qui correspond à ce maximum. Attention, avec cette façon d'agir, on récupère juste la première entrée en cas d'égalité.

Pour trouver la durée de film la plus longue :

1 2 3
SELECT primary_title, MAX(runtime_minutes) FROM titre WHERE title_type = 'movie';
x

Recherche de minimum

Pour trouver la durée de film la plus courte et le premier puplet correspondant :

1 2 3
SELECT primary_title, MIN(runtime_minutes) FROM titre WHERE title_type = 'movie';
x

Recherche de moyenne

Pour trouver la durée moyenne des films :

1 2 3
SELECT AVG(runtime_minutes) FROM titre WHERE title_type = 'movie';
x

Recherche de la somme des valeurs d'un attribut

Pour trouver la durée totale des films produits en 2020 :

1 2 3
SELECT SUM(runtime_minutes) AS total_en_minutes FROM titre WHERE title_type = 'movie' and start_year = 2020;
x

09° Fournir la requête permettant de récupérer la durée moyenne des sorties des années 2010 à 2020.

1 2 3 4 5 6 7 8 9 10 11 12
CREATE TABLE titre ( id INTEGER, tconst VARCHAR(9) UNIQUE, 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(tconst) );

...CORRECTION...

1 2 3
SELECT AVG(runtime_minutes) AS moyenne FROM titre WHERE start_year >= 2010 AND start_year <= 2020;

On aurait alors juste un affichage de ce type :

x
COUNT

Cette fonction permet de compter le nombre de puplet renvoyé par notre requête.

Pour l'exemple, voici le nombre de films parus en 2000.

1 2 3
SELECT start_year, COUNT(*) AS nombre FROM titre WHERE start_year = 2000;
x

10° Fournir la requête permettant de compter le nombre de titres de plus de 180 minutes.

...CORRECTION...

1 2 3
SELECT COUNT(*) AS nombre FROM titre WHERE runtime_minutes > 180;

On aurait alors juste un affichage de ce type :

x
Pour se faire plaisir : SELECT de SELECT

Ce n'est pas une compétence attendue au BAC.

On peut également utiliser le résultat d'une première projection pour en obtenir une deuxième.

Voici un exemple où on recherche TOUS les puplets dont la durée est la durée minimale.

1 2 3
SELECT primary_title, runtime_minutes, start_year FROM titre WHERE runtime_minutes = (SELECT MIN(runtime_minutes) AS minimum FROM titre WHERE title_type = 'movie');
x

6 - LIKE

Dernière chose que nous allons voir : comment retrouver les nuplets dont l'un des champs "ressemble" à ce qu'on cherche.

Cherchons par exemple les films qui contiennent le mot 'alien'.

LIKE

On utilise LIKE dans la clause WHERE. Cela permet de rechercher un modèle de réponse plutôt qu'une recherche précise.

Le caractère % est le caractère joker qui remplace tous les autres : % peut être n'importe quelle chaîne de caractère.

1 2 3
SELECT * FROM titre WHERE primary_title LIKE '%alien %' or '%aliens%';
x

11° Fournir la requête permettant de récupérer les puplets de films dont le genre contient le genre "Sci-Fi".

1 2 3 4 5 6 7 8 9 10 11 12
CREATE TABLE titre ( id INTEGER, tconst VARCHAR(9) UNIQUE, 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(tconst) );

...CORRECTION...

1 2 3
SELECT * FROM titre WHERE genres LIKE '%Sci-Fi%' and title_type = 'movie';

On aurait alors juste un affichage de ce type :

x

12° Fournir la requête permettant de récupérer les puplets de films dont le genre contient le genre "Fantasy" du plus récent au plus ancien et dans l'ordre alphabétique.

1 2 3 4 5 6 7 8 9 10 11 12
CREATE TABLE titre ( id INTEGER, tconst VARCHAR(9) UNIQUE, 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(tconst) );

...CORRECTION...

1 2 3 4
SELECT * FROM titre WHERE genres LIKE '%Fantasy%' and title_type = 'movie' and start_year <> '' ORDER BY start_year DESC, primary_title;

On aurait alors juste un affichage de ce type :

x

7 - FAQ

Il reste encore des choses à apprendre ?

Il existe un très grand nombre de possibilités en SQL. Le but de la découverte de SQL en NSI n'est pas de faire de vous des spécialistes du langage et des bases de données. A titre d'exemple, voici une sorte de requête comportant un très grand nombre de clauses :

1 2 3 4 5 6 7 8 9
SELECT * FROM table WHERE condition GROUP BY expression HAVING condition { UNION | INTERSECT | EXCEPT } ORDER BY expression LIMIT count OFFSET start;

On retiendra simplement la structure de base à base de

1 2 3 4
SELECT * FROM table WHERE condition(s) ORDER BY expression
  1. SELECT : on choisit les attributs qu'on veut réellement récupérer dans les nuplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces nuplets avec MIN, MAX, AVG, SUM ou COUNT. L'utilisation de l'étoile * veut dire de récupérer l'intégralité des attributs disponibles sur les nuplets obtenus.
  2. FROM : on indique la table dans laquelle on veut récupèrer des nuplets
  3. WHERE : on fournit les conditions à respecter pour qu'un nuplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
  4. ORDER BY : on fournit l'attribut de tri avec un suffixe ASC ou DESC qui permettra d'ordonner les nuplets obtenus

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