Données SQL Bilan

Identification

Infoforall

27 - SQL : BILAN pour la NSI


Prerequis : les activités précédentes sur SQL.

Logiciel nécessaire : DB Brownser

Fichier SQLite : la base de données est à télécharger. Si vous n'avez pas de compte, vous pouvez me contacter avec le bouton en bas de page.

Fichier des élèves, voeux et propositions

1 - Description des 3 relations de cette activité

Nous allons travailler aujourd'hui avec 3 tables permettant de gérer à minima le système de voeux des étudiants.

Relation proposition

proposition ( id INTEGER, intitule VARCHAR(10) )

Voici un exemple du contenu :

id intitule
1 Pas encore de réponse
2 Non
3 Liste d'attente
4 Oui mais
5 Oui

Relation eleve

eleve ( id INTEGER, nom VARCHAR(20), prenom VARCHAR(20), naissance DATE(20), nsi INTEGER, moyenne INTEGER )

Le type DATE n'existe pas en SQLite mais on peut très bien le noter dans le schéma de relation qui reste une représentation abstraite de ce qu'on veut faire.

Voici un exemple du contenu :

id nom prenom naissance nsi moyenne
1 Inbordeland Alice 05/08/2002 12 14
2 Leponge Bob 15/03/2003 6 13
3 Kent Clark 30/01/2001 12 10
4 Wonderwoman Diana 15/03/2001 18 18

Relation voeu

voeu ( id INTEGER, #eleve_id INTEGER, description VARCHAR(40), #proposition_id INTEGER, reponse INTEGER ) eleve_id est une clé étrangère menant à id de eleve proposition_id est une clé étrangère menant à id de proposition

Voici un exemple du contenu :

id #eleve_id description #proposition_id reponse
1 1 MP2I 3 NULL
2 1 Licence Info 5 NULL
3 2 Licence Océanographie 5 NULL
4 2 BUT Info 4 NULL
5 3 BUT Info 5 NULL
6 3 MP2I 1 NULL
7 4 MP2I 5 NULL
8 4 Licence Info 5 NULL
Création des tables SQL (non exigible)

Rappel : VARCHAR et DATE n'existent pas en SQLite. Il faut les remplacer respectivement par TEXT et REAL.

La syntaxe exacte varie d'un système à l'autre.

1 2 3 4 5
CREATE TABLE "proposition" ( "id" INTEGER, "intitule" TEXT NOT NULL DEFAULT 'Pas de réponse', PRIMARY KEY("id" AUTOINCREMENT) );
1 2 3 4 5 6 7 8 9
CREATE TABLE "eleve" ( "id" INTEGER, "nom" VARCHAR(40) NOT NULL, "prenom" VARCHAR(40) NOT NULL, "naissance" DATE NOT NULL, "nsi" INTEGER, "moyenne" INTEGER, PRIMARY KEY("id" AUTOINCREMENT) );
1 2 3 4 5 6 7 8 9 10
CREATE TABLE "voeu" ( "id" INTEGER, "eleve_id" INTEGER, "description" VARCHAR(40), "proposition_id" INTEGER, "reponse" INTEGER, FOREIGN KEY("eleve_id") REFERENCES "eleve"("id"), FOREIGN KEY("reponse") REFERENCES "proposition"("id"), PRIMARY KEY("id" AUTOINCREMENT) );

2 - Rappels sur l'interrogation d'une BDD en SQL

SELECT a FROM b WHERE c ORDER BY d
1 2 3 4
SELECT * FROM table WHERE condition ORDER BY expression
  1. SELECT : on choisit les attributs qu'on veut réellement récupérer dans les n-uplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces n-uplets 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 n-uplets obtenus.
  2. FROM : on indique la table dans laquelle on veut récupérer des n-uplets
  3. WHERE : on fournit les conditions à respecter pour qu'un n-uplet 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 n-uplets obtenus

Exemple :

1 2 3 4
SELECT nom, prenom FROM eleve WHERE naissance LIKE '%2001%' ORDER BY nom DESC;

On sélectionne donc uniquement certains n-uplets puis on renvoie uniquement certains attributs des n-uplets sélectionnés :

id nom prenom naissance nsi moyenne
1 Inbordeland Alice 05/08/2002 12 14
2 Leponge Bob 15/03/2003 6 13
3 Kent Clark 30/01/2001 12 10
4 Wonderwoman Diana 15/03/2001 18 18

Du coup, la Base de Données va répondre ceci :

01° Fournir la réponse obtenue avec cette requête :

1 2 3 4
SELECT nom, prenom, naissance FROM eleve WHERE prenom LIKE '%i%' ORDER BY nom;

...CORRECTION...

Avec le tableau en explication :

id nom prenom naissance nsi moyenne
1 Inbordeland Alice 05/08/2002 12 14
2 Leponge Bob 15/03/2003 6 13
3 Kent Clark 30/01/2001 12 10
4 Wonderwoman Diana 15/03/2001 18 18

02° Fournir la réponse obtenue avec cette requête :

1 2 3 4
SELECT nom, prenom, naissance FROM eleve WHERE prenom LIKE '%i%' OR naissance LIKE "%/03/%" ORDER BY nom DESC;

...CORRECTION...

Avec le tableau en explication :

id nom prenom naissance nsi moyenne
1 Inbordeland Alice 05/08/2002 12 14
2 Leponge Bob 15/03/2003 6 13
3 Kent Clark 30/01/2001 12 10
4 Wonderwoman Diana 15/03/2001 18 18

03° Fournir la réponse obtenue avec cette requête :

1 2 3
SELECT DISTINCT eleve_id FROM voeu WHERE proposition_id = 5;

...CORRECTION...

Sur la table :

id #eleve_id description #proposition_id reponse
1 1 MP2I 3 NULL
2 1 Licence Info 5 NULL
3 2 Licence Océanographie 5 NULL
4 2 BUT Info 4 NULL
5 3 BUT Info 5 NULL
6 3 MP2I 1 NULL
7 4 MP2I 5 NULL
8 4 Licence Info 5 NULL

04° Quelle va être la différence entre cette requête et la précédente ?

1 2 3
SELECT DISTINCT eleve_id AS "Numéro de l'élève" FROM voeu WHERE proposition_id = 5;

...CORRECTION...

L'intitulé de la réponse. C'est tout.

Requêtes imbriquées

On peut utiliser le résultat d'une requête pour construire une autre requête. On utilise souvent pour cela le mot-clé IN qui veut dire "dans".

On cherche donc une valeur dans une table qui soit dans l'autre requête :

Exemple :

1 2 3 4 5 6
SELECT nom FROM eleve WHERE id IN (SELECT DISTINCT eleve_id AS "Numéro de l'élève" FROM voeu WHERE proposition_id = 5);

Ici la requête des lignes 4-5-6 va renvoyer les numéros de la question précédente : 1, 2, 3 et 4.

La requête des lignes 1-2-3 consiste donc à ramener les noms des personnes dont l'identifiant est dans (1, 2, 3, 4).

C'est donc comme si nous avions fait cette requête :

1 2 3 4
SELECT nom FROM eleve WHERE id IN (1, 2, 3, 4);

Le résultat obtenu :

Cela peut être pratique lorsqu'on veut faire des requêtes assez complexes. Ici, nous aurions pu faire plus simple : faire une simple jonction de tables.

3 - JOIN ON

JOIN ON

La clause JOIN ON permet de créer une table temporaire à l'aide de deux ou plusieurs autres tables.

Le principe est de faire la jonction à l'aide d'une comparaison entre l'attribut faisant office de clé étrangère et la clé primaire de l'autre table.

Pour formuler une requête d'informations :

1 2 3 4 5
SELECT * FROM table_a JOIN table_b ON table_a.cle_etrangere = table_b.cle_primaire WHERE condition(s) ORDER BY expression
  1. SELECT : on choisit les attributs qu'on veut réellement récupérer dans les n-uplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces n-uplets 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 n-uplets obtenus.
  2. FROM : on indique la table dans laquelle on veut récupérer des n-uplets
  3. JOIN ON : on indique la table qu'on veut fusionner avec la précédente et on précise quelle clé primaire comparer à quelle clé étrangère
  4. WHERE : on fournit les conditions à respecter pour qu'un n-uplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
  5. ORDER BY : on fournit l'attribut de tri avec un suffixe ASC ou DESC

05° Remplir intuitivement la table suivante en utilisant les relations fournies en partie I.

id #eleve_id description #proposition_id reponse nom de l'élève proposition en fr
1 1 MP2I 3 NULL ? ?
2 1 Licence Info 5 NULL ? ?
3 2 Licence Océanographie 5 NULL ? ?
4 2 BUT Info 4 NULL ? ?
5 3 BUT Info 5 NULL ? ?
6 3 MP2I 1 NULL ? ?
7 4 MP2I 5 NULL ? ?
8 4 Licence Info 5 NULL ? ?

...CORRECTION...

id #eleve_id description #proposition_id reponse nom de l'élève proposition en fr
1 1 MP2I 3 NULL Inborderland Liste d'attente
2 1 Licence Info 5 NULL Inborderland Oui
3 2 Licence Océanographie 5 NULL Leponge Oui
4 2 BUT Info 4 NULL Leponge Oui mais
5 3 BUT Info 5 NULL Kent Oui
6 3 MP2I 1 NULL Kent Pas encore de réponse
7 4 MP2I 5 NULL Wonderwoman Oui
8 4 Licence Info 5 NULL Wonderwoman Oui

06° Fournir la requête comportant un JOIN ON permettant d'obtenir l'affichage suivant à partir des tables voeu et eleve uniquement.

On veut donc le nom de l'élève, la description de son voeu et la proposition qu'il a reçu sous forme du code d'identification, pas du texte lui-même.

...CORRECTION...

1 2 3
SELECT eleve.nom, voeu.description, voeu.proposition_id FROM voeu JOIN eleve ON voeu.eleve_id = eleve.id;

07° Recopier et compléter la requête ci-dessous pour qu'elle fasse le travail demandé. On remarquera qu'elle possède deux jointures.

1 2 3 4 5 6 7
SELECT eleve.nom as "Nom de l'élève", voeu.description as "Formation", proposition.??? as "Réponse de la formation" FROM voeu JOIN eleve ON voeu.eleve_id = eleve.id JOIN proposition ON voeu.??? = ???.???

On veut donc le nom de l'élève, la description de son voeu et la proposition qu'il a reçu sous forme du code d'identification, pas du texte lui-même.

...CORRECTION...

1 2 3 4 5 6 7
SELECT eleve.nom as "Nom de l'élève", voeu.description as "Formation", proposition.intitule as "Réponse de la formation" FROM voeu JOIN eleve ON voeu.eleve_id = eleve.id JOIN proposition ON voeu.proposition_id = proposition.id

L'ordre dans lequel faire les jointures n'a pas d'importance en soin en terme de résultat pur. Vous verrez les différences effectives plus tard. Mais sachez que ce code donne le même résultat :

1 2 3 4
... FROM eleve JOIN voeu ON voeu.eleve_id = eleve.id JOIN proposition ON voeu.proposition_id = proposition.id

08° Quelle clause WHERE faire si on veut récupérer une description similaire à la précédente mais uniquement pour les voeux licence ?

Visuel

Le début de la requête

1 2 3 4 5 6 7 8
SELECT eleve.nom as "Nom de l'élève", voeu.description as "Formation", proposition.intitule as "Réponse de la formation" FROM voeu JOIN eleve ON voeu.eleve_id = eleve.id JOIN proposition ON voeu.proposition_id = proposition.id WHERE ???

...CORRECTION...

1 2 3 4 5 6 7 8
SELECT eleve.nom as "Nom de l'élève", voeu.description as "Formation", proposition.intitule as "Réponse de la formation" FROM voeu JOIN eleve ON voeu.eleve_id = eleve.id JOIN proposition ON voeu.proposition_id = proposition.id WHERE voeu.description LIKE '%Licence%'

09° Quelqu'un veut obtenir la liste des élèves qui ont demandé une MP2I, n'ont pas été refusés mais n'ont pas encore répondu. Voici sa requête :

1 2 3 4 5 6 7
SELECT eleve.nom FROM voeu JOIN eleve ON voeu.eleve_id = eleve.id JOIN proposition ON voeu.proposition_id = proposition.id WHERE voeu.description LIKE '%MP2I%' AND voeu.proposition_id != 2 AND voeu.reponse = NULL;

Question :

Qu'est-ce que ne fonctionne pas ? Comment formuler correctement la demande ?

...CORRECTION...

Le problème vient de NULL qui indique une information manquante. Rien ne peut être égale à une information manquante puisque, précisement, on ne sait pas ce qu'elle est !

Il ne faut pas utiliser un = mais le mot-clé IS.

1 2 3 4 5 6 7
SELECT eleve.nom FROM voeu JOIN eleve ON voeu.eleve_id = eleve.id JOIN proposition ON voeu.proposition_id = proposition.id WHERE voeu.description LIKE '%MP2I%' AND voeu.proposition_id != 2 AND voeu.reponse IS NULL;
Plusieurs conditions

On retrouve les choses habituelles pour relier plusieurs conditions entre elles et faire une condition plus complexe.

  1. AND
  2. OR
  3. NOT
  4. IS
  5. IN
  6. LIKE

Et d'autres encore. Vous aurez l'occasion de les découvrir plus tard.

4 - Commandes de modification

Nous avons vu qu'on peut créer des tables avec la commande CREATE TABLE.

On peut même les détruire avec DROP TABLE.

1
DROP TABLE x

Cette commande va détruire la table x.

Voyons comment insérer de nouveaux n-uplets dans une relation.

INSERT INTO

Cette commande permet de rajouter des n-uplets dans la table indiquée.

1 2 3
INSERT INTO eleve VALUES (NULL, 'Stark', 'Edward', '20/10/2002', NULL, NULL, NULL);

On notera que comme l'identifiant est défini automatiquement, sa valeur n'est pas à fournir : c'est la base de données qui va lui attribuer un numéro. C'est pour cela qu'on fournit NULL comme premier argument.

On peut également ne donner que quelques éléments, notamment ceux qui ne peuvent pas être NULL et ne possède pas de valeur par défaut.

1 2 3
INSERT INTO eleve (nom, prenom, naissance) VALUES ('Grey', 'Jane', '15/03/2001');

On peut même en mettre plusieurs à la suite :

1 2 3 4
INSERT INTO eleve (nom, prenom, naissance) VALUES ('Sacquet', 'Frodon', '19/07/2003'), ('Grey', 'Jane', '15/03/2001');

Voici le résultat dans la table SQL :

UPDATE - SET

Cette commande permet de modifier les valeurs des n-uplets sélectionnés.

Nous avons rentré deux nouveaux élèves mais ils n'ont pas de notes.

Imaginons qu'on veuille leur mettre 10 de base et ajuster ensuite.

1 2 3
UPDATE eleve SET nsi = 10, moyenne = 10 WHERE nsi IS NULL;

Le résultat en image :

Nous pouvons même augmenter de 1 les moyennes de tous les élèves qui ont déjà au moins 10 en NSI :

1 2 3
UPDATE eleve SET nsi = nsi + 1 WHERE nsi >= 10;
DELETE FROM

Cette commande permet de supprimer les n-uplets qui ont été sélectionnés.

Attention : si vous ne placez pas de WHERE, vous allez supprimer l'intégralité des n-uplets de la table qui va se retrouver totalement vide...

Pour détruire tous les n-uplets d'une relation :

1 2
DELETE FROM voeu;

Plus aucun voeu après ça. Comme quoi, laisser des droits de suppression à quelqu'un peut s'avérer assez dangereux...

Pour détruire les n-uplets d'une relation répondant à certaines conditions :

1 2 3
DELETE FROM voeu WHERE proposition_id = 1

Avec cela, on supprime tous les voeux qui n'ont pas été acceptés par les formations.

10° Quelle requête faire pour rajouter l'élève parfait dans la base ?

...CORRECTION...

1 2
INSERT INTO eleve VALUES (NULL, 'Tropfort', 'Parfait', '01/01/2001', 20, 20);

En image :

11° Quelle requête pour parvenir à augmenter de 1 la moyenne de tous les élèves nés en 2001 ?

...CORRECTION...

1 2 3
UPDATE eleve SET nsi = nsi + 1 WHERE naissance LIKE "%2001%";

12° Comment parvenir à supprimer tous les élèves dont le nom contient un A ?

C'est assez complexe. Pour cela, il faudra :

  • Faire une première requête SELECT permettant de récupérer tous les id d'élèves dont le nom contient un A.
  • Détruire les n-uplets de voeu dont l'identifiant élève est dans (IN) la réponse précédente

...CORRECTION...

1 2 3 4 5 6
DELETE FROM voeu WHERE voeu.eleve_id IN ( SELECT eleve.id FROM eleve WHERE eleve.nom LIKE '%A%');

5 - Agrégation

Fonctions d'agrégation

Lorsqu'on récupère des entrées, on peut les utiliser pour effectuer certaines recherches ou calculs :

  • MAX pour trouver la valeur la plus grande sur cet attribut dans la sélection obtenue
  • MIN pour trouver la valeur la plus petite sur cet attribut dans la sélection obtenue
  • AVG pour trouver la valeur moyenne sur cet attribut dans la sélection obtenue
  • COUNT pour trouver le nombre de n-uplets obtenus dans la sélection

13° Que fait cette requête ?

1 2 3
SELECT COUNT(*) FROM voeu WHERE voeu.description LIKE '%IUT%';

...CORRECTION...

On compte les voeux de type IUT.

14° Que fait cette requête ?

1 2 3 4
SELECT COUNT(DISTINCT eleve.nom) FROM eleve JOIN voeu ON eleve.id = voeu.eleve_id WHERE voeu.description LIKE '%IUT%';

...CORRECTION...

On compte le nombre d'élèves qui ont fait un voeu de type IUT. Mais on ne compte chaque élève qu'une fois. Si l'un d'entre eux a fait 4 voeux IUT, cela ne compte que pour un élève, pas quatre comme à la question précédente.

15° Fournir la requête permettant d'obtenir la moyenne en NSI des élèves de la base.

...CORRECTION...

1 2
SELECT AVG(nsi) FROM eleve

16° Fournir la requête permettant d'obtenir la moyenne en NSI des élèves de la base qui ont été acceptés dans une formation avec une proposition_id valant 5.

Encore une fois, nous avons besoin à la fois de la table eleve et de la table voeu.

...CORRECTION...

1 2 3 4
SELECT AVG(eleve.nsi) FROM eleve JOIN voeu ON eleve.id = voeu.eleve_id WHERE voeu.proposition_id = 5;

6 - FAQ

Comment supprimer les n-uplets qui contiennent des clés étrangères qui ne pointent vers rien de valide ?

Pour détruire les n-uplets qui contiennent des clés étrangères ne correspondant pas à une valeur d'une autre table :

Maintenant que nous avons détruit les titres datant d'avant 1990, nous avons certainement dans la relation emploi des références à certains titres que nous venons de supprimer. Si nous avions un système de gestion efficace, nous pourrions supprimer automatiquement ces n-uplets. Mais on peut aussi le faire à la main. En trois temps :

  1. D'abord, il faut trouver les clés primaires disponibles pour titre :
  2. 1
    SELECT titre.id FROM titre
  3. Ensuite, on utilise cette réponse pour savoir si la clé étrangère d'un n-uplet fait bien référence à une vraie clé primaire :
  4. 1
    WHERE emploi.id_titre NOT IN (SELECT titre.id FROM titre);
  5. On batît la commande de destruction autour de cela :
  6. 1 2 3
    DELETE FROM emploi WHERE emploi.id_titre NOT IN (SELECT titre.id FROM titre);

Pour formuler une requête d'informations :

1 2 3 4 5
SELECT * FROM table_a JOIN table_b ON table_a.cle_etrangere = table_b.cle_primaire WHERE condition(s) ORDER BY expression
  1. SELECT : on choisit les attributs qu'on veut réellement récupérer dans les n-uplets obtenus par FROM WHERE. On peut également faire des calculs (agrégation) sur ces n-uplets 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 n-uplets obtenus.
  2. FROM : on indique la table dans laquelle on veut récupérer des n-uplets
  3. JOIN ON : on indique la table qu'on veut fusionner avec la précédente et on précise quelle clé primaire comparer à quelle clé étrangère
  4. WHERE : on fournit les conditions à respecter pour qu'un n-uplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
  5. ORDER BY : on fournit l'attribut de tri avec un suffixe ASC ou DESC

Pour détruire tous les n-uplets d'une relation :

1 2
DELETE FROM table;

Cela ne détruit pas la table : on supprime juste les n-uplets.

Pour détruire les n-uplets d'une relation répondant à certaines conditions :

1 2 3
DELETE FROM titre WHERE start_year < 1990;

Pour insérer un nouveau n-uplet dans une table :

1 2 3
INSERT INTO personne (id_ifa, id, name, birth_year, death_year, primary_profession, known_for_titles) VALUES (NULL, 'nm0007', 'James Bond', 1930, 2020, 'agent secret', NULL);

Si on fournit toutes les valeurs, autant faire cela (mais il faut les donner dans l'ordre) :

1 2 3
INSERT INTO personne VALUES (NULL, 'nm0042', 'Douglas Admans', 1952, 2021, 'writer', NULL);

Pour modifier (mettre à jour) certaines valeurs d'un ou plusieurs n-uplets :

1 2 3
UPDATE emploi SET job = '-' WHERE job IS NULL ;

Activité publiée le 25 01 2021
Dernière modification : 07 04 2021
Auteur : ows. h.