27 - SQL : BILAN pour la NSI

⏪

⏬

⏩

1 - Description

2 - Interrogation

3 - JOIN ON

4 - modification

5 - Agrégation

6 - FAQ

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

MODE COURS (cacher les questions)

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

CREATE TABLE "proposition" (
    "id"    INTEGER,
    "intitule"  TEXT NOT NULL DEFAULT 'Pas de réponse',
    PRIMARY KEY("id" AUTOINCREMENT)
);

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

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

SELECT *
FROM table
WHERE condition
ORDER BY expression

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.
FROM : on indique la table dans laquelle on veut récupérer des n-uplets
WHERE : on fournit les conditions à respecter pour qu'un n-uplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
ORDER BY : on fournit l'attribut de tri avec un suffixe ASC ou DESC qui permettra d'ordonner les n-uplets obtenus

Exemple :

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

SELECT nom, prenom, naissance
FROM eleve
WHERE prenom LIKE '%i%'
ORDER BY nom;

Correction ?

...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	Charlie	30/01/2001	12	10
4	Wonderwoman	Diana	15/03/2001	18	18

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

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

Correction ?

...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	Charlie	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 ?

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

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

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 :

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 :

SELECT *
FROM table_a
JOIN table_b ON table_a.cle_etrangere = table_b.cle_primaire
WHERE condition(s)
ORDER BY expression

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.
FROM : on indique la table dans laquelle on veut récupérer des n-uplets
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
WHERE : on fournit les conditions à respecter pour qu'un n-uplet soit sélectionné. On pourra utiliser AND, OR, LIKE...
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 ?

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

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

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 ?

...CORRECTION...

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 :

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

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 ?

...CORRECTION...

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 :

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 ?

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

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.

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 :

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 ?

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

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

...CORRECTION...

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 ?

...CORRECTION...

On compte les voeux de type IUT.

14° Que fait cette requête ?

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

Correction ?

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

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

...CORRECTION...

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 :

D'abord, il faut trouver les clés primaires disponibles pour titre :

`1`	`SELECT titre.id FROM titre`

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 :

`1`	`WHERE emploi.id_titre NOT IN (SELECT titre.id FROM titre);`

On batît la commande de destruction autour de cela :

1
2
3

DELETE
FROM emploi
WHERE emploi.id_titre NOT IN (SELECT titre.id FROM titre);

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

⏪

⏫

⏩