24 - 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.
Logo :

Intro-02° Télécharger la base de données contenant trois relations :
- Une relation titre qui contient des informations sur les films, les séries et autres productions
- Une relation personne qui contient des informations sur personnes ayant travaillé sur les productions précédentes
- Une relation emploi qui contient des informations sur les emplois occupés et qui possède donc des clés secondaites menant à des nuplets de titre et personne.
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 :

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.
Pas de CREATE TABLE au BAC
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 |
|
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. Il y a un cas particulier qui n'a pas à respecter cette unicité : la valeur NULL (qui veut dire "non connue" ou "non renseignée") peut se trouver présente plusieurs fois pour cet attribut (cette "colonne").
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 |
|
Remarque 1 : on souligne souvent la clé primaire dans les schémas relationnels.
Les noms sont relativement explicites mais voici quelques précisions :
- id : un numéro d'enregistrement que j'ai rajouté et qui aurait pu servir également de clé primaire
- tconst : le texte servant de clé primaire pour la table titre sur le site Web.
- title_type : un texte permettant de savoir si c'est un film, une série...
- primary_title : un texte qui est le titre sous lequel cette oeuvre est connu (la base de données est celle d'un site américain)
- original_title : le titre originel de l'oeuvre
- start_year et end_year : ce sont la date de sortie et la date d'arrêt. end_year est surtout utile pour les séries. Un film se termine normalement la même année que sa sortie...
- runtime_minutes : la durée en minutes.
- 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 récupère que certains attributs des n-uplets.
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.

Nous allons donc sélectionner les attributs de la bonne table :
Avec DBBrownser :
- On va dans EXECUTER LE SQL
- On tape la requête SQL suivante :
- On exécute en appuyant sur le bouton FLECHE.
1
2 |
|
TRADUCTION
Ramène le titre, la durée et le genre
des n-uplets trouvés dans la table titre.

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

01° Fournir la requête SQL permettant de récupérer le nom, le numéro d'enregistrement et la date de création pour tous les n-uplets présents dans la relation titre.
...CORRECTION...

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 |
|
La même chose en version schéma relationnel :
1
2
3
4
5
6
7
8
9 |
|
02° Fournir la requête SQL permettant de récupérer le nom, la date de naissance et la date de mort pour tous les n-uplets présents dans la relation personne.
...CORRECTION...

Clause DISTINCT (Hors programme je pense)
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 |
|
TRADUCTION
Ramène un unique exemplaire des genres
des n-uplets trouvés dans la table titre.

Comme vous pouvez le voir, chaque valeur d'attribut n'apparaît qu'une seule fois. Sans le mot-clé DISTINCT nous aurions eu chaque entrée plusieurs fois, et autant de lignes que de n-uplets.
Projection ?
Pourquoi parler de projetions pour cette récupération d'attributs ?
Ce vocabulaire est liée à l'algèbre relationnelle et à la logique mathématique.
La fonction projection Pni veut dire d'aller récupérer l'élément d'indice i dans notre n-uplet de n valeurs.
Vous avez déjà réalisé très souvent des projections sans savoir qu'on pouvait appeler cela comme ça :
>>> nuplet = (5, "Bob", 25)
>>> nuplet[0] <-- PROJECTION !
5
Bref, lire la case i d'une séquence de valeurs, c'est une projection.
3 - Filtrage avec WHERE
Passons maintenant à des requêtes plus fines.
Vous savez projeter uniquement certains attributs ("colonnes") avec SELECT.
Vous allez maintenant voir comment ne récupérer que certains n-uplets ("lignes") avec WHERE.
Pour cela, il faut falloir dire (dans cet ordre, attention) :
- Quels sont les attributs qu'on veut récupérer (SELECT)
- Depuis quelle table (FROM)
- Quels sont les n-uplets qu'on veut filtrer parmi tous les n-uplets disponibles (WHERE)
Clause WHERE
La clause WHERE permet de restreindre la projection en précisant des conditions de sélection des n-uplets.
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 : +, -, *, /, %
1er exemple
On veut récupérer les titres, l'année de sortie et les durées des titres de 2020.
1
2
3 |
|
TRADUCTION
Ramène le titre, l'année de sortie et la durée
de tous les n-uplets trouvés dans la table titre
qui sont sortis en 2020.

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 |
|
TRADUCTION
Ramène le titre, l'année de sortie et la durée
de tous les n-uplets trouvés dans la table titre
qui sont sortis après 2020 et dont la durée est inférieure à 120 minutes.
Le résultat :

Deux remarques :
- 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 !
- On peut très bien ne pas mettre dans le SELECT qu'une partie des attributs du n-uplets qu'on va ramener avec FROM, même s'ils n'interviennent pas dans la clause WHERE. La requête ci-dessous est légitime et ne renvoie que la liste des titres :
1
2
3 |
|
03° Fournir la requête permettant d'obtenir les titres ayant commencé entre 2010 et 2012.
...CORRECTION...
1
2
3 |
|
Le résultat visuel :

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 :

...CORRECTION...
1
2
3 |
|
Le résultat visuel (sans la date de début, j'avais oublié):

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 |
|
Le résultat visuel :

Priorités du AND et du OR
Nous avions vu en 1er que a AND b est plus ou moins équivalent à une multiplication.
Nous avions vu en 1er que a OR b est plus ou moins équivalent à une addition.
On en avait déduit 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 |
|
Avec les parenthèses :
1
2
3 |
|
...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 (Hors programme je pense)
La commande ORDER BY permet de trier les n-uplets (lignes) d’une réponse. On pourra trier les données sur un ou plusieurs attributs (colonnes), et par ordre croissant ou decroissant.
Tri simple croissant
1
2
3
4 |
|

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 |
|
Tri en ordre décroissant
On précise alors le suffixe DESC.
1
2
3
4 |
|

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

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

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 |
|
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 |
|
On aurait alors juste un affichage de ce type :

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 |
|
...CORRECTION...
1
2
3
4 |
|
On aurait alors juste un affichage de ce type :

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 n-uplet qui correspond à ce maximum. Attention, avec cette façon d'agir, on récupère juste la première occurrence en cas d'égalité.
Pour trouver la durée de film la plus longue :
1
2
3 |
|
TRADUCTION
Ramène le premier titre qui possède la plus grande durée (ainsi que cette durée maximale)
parmi les n-uplets de la table titre
qui sont des films.

Recherche de minimum
Pour trouver la durée de film la plus courte et le premier n-uplet correspondant :
1
2
3 |
|
TRADUCTION
Ramène le premier titre qui possède la plus petite durée, ainsi que cette durée minimale
parmi les n-uplets de la table titre
qui sont des films.

Recherche de moyenne
Pour trouver la durée moyenne des films :
1
2
3 |
|
TRADUCTION
Ramène la durée moyenne
des n-uplets de la table titre
qui sont sont des films.

Recherche de la somme des valeurs d'un attribut
Pour trouver la durée totale des films produits en 2020 :
1
2
3 |
|
TRADUCTION
Ramène la somme
des n-uplets de la table titre
qui sont des films sortis en 2020.

REMARQUE IMPORTANTE
Dès qu'on utilise l'une des fonctions d'agrégation, SQL ne va vous renvoyer qu'un unique n-uplet. Si vous cherchez la valeur minimale, vous récupérer cette valeur et le premier p-uplet ayant cette valeur. Le n-uplet récupéré n'a donc pas spécialement d'importance, sauf s'il est vraiment l'unique plus petit. Avec les films, ça ne marche pas par exemple : il y a plusieurs films de 35 minutes.
Si vous voulez récupérer la liste de tous les films les plus courts, il faut le faire en deux fois :
- D'abord, trouver la valeur minimale
- Ensuite, l'utiliser pour faire une requête pour récupérer tous les films qui ont cette durée.
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 |
|
...CORRECTION...
1
2
3 |
|
On aurait alors juste un affichage de ce type :

COUNT
Cette fonction permet de compter le nombre de n-uplets renvoyés par notre requête.
Pour l'exemple, voici le nombre de films parus en 2000.
1
2
3 |
|
TRADUCTION
Ramène l'année de départ cherchée et le nombre
de n-uplets de la table titre
qui sont sortis en 2000.

10° Fournir la requête permettant de compter le nombre de titres de plus de 180 minutes.
...CORRECTION...
1
2
3 |
|
On aurait alors juste un affichage de ce type :

Pour se faire plaisir : SELECT de SELECT (hors programme, c'est certain)
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.
On peut ainsi faire ceci en une seule requête :
- D'abord, trouver la valeur minimale
- Ensuite, l'utiliser pour faire une requête pour récupérer tous les films qui ont cette durée.
Voici un exemple où on recherche TOUS les puplets dont la durée est la durée minimale.
1
2
3
4
5
6
7 |
|

Vous auriez également pu mettre tout ceci sur une seule ligne, mais est-ce plus claire ?
1
2
3 |
|
6 - LIKE et IN
Dernière chose que nous allons voir : comment retrouver les n-uplets
- dont l'un des champs "ressemble" à ce qu'on cherche (LIKE) ou
- dont les valeurs sont dans un ensemble bien précis (IN)
Pourquoi le mettre ici alors que c'est bien pratique ? Simplement car ces deux mots ne sont pas présent dans les choses qu'il faut nécessairement connaître en NSI sur SQL.
A connaître pour l'épreuve de NSI
- SELECT, FROM, WHERE (cette activité)
- JOIN ON (la prochaine)
- UPDATE, INSERT, DELETE (la dernière)
On peut sans doute rajouter AND et OR puisqu'ils se comportent comme leurs équivalents en Python.
Cherchons par exemple les films qui contiennent le mot 'alien'.
Ressemblance avec 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ères.
1
2
3 |
|
TRADUCTION
Ramène les n-uplets parmi
les n-uplets de la table titre
dont le titre contient le texte "alien " ou le texte "aliens".

11° Fournir la requête permettant de récupérer les p-uplets de films dont le genre contient le genre "Sci-Fi".
1
2
3
4
5
6
7
8
9
10
11
12 |
|
...CORRECTION...
1
2
3 |
|
On aurait alors juste un affichage de ce type :

12° Fournir la requête permettant de récupérer les p-uplets 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 |
|
...CORRECTION...
1
2
3
4 |
|
On aurait alors juste un affichage de ce type :

Appartient avec IN
On utilise IN pour vérifier l'appartenance d'un élément à une liste définie d'éléments.
Par exemple, pour obtenir les distributions qui sont soit un film ou une série, on peut utiliser un OR ou un IN :
Version avec un OR
1
2
3 |
|
TRADUCTION
Ramène le titre, la date de sortie et la date d'arrêt
des n-uplets de la table titre
dont le genre est film ou dont le genre est série.
Version avec un IN
1
2
3 |
|
TRADUCTION
Ramène le titre, la date de sortie et la date d'arrêt
des n-uplets de la table titre
dont le genre est parmi la sélection suivante : (film, série).
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 |
|
Activité publiée le 18 01 2021
Dernière modification : 24 01 2021
Auteur : ows. h.