Interaction logicielle avec une base de données.

Système de gestion de base de données.

Définition

Dans le cours précédent, le modèle relationnel permet de déterminer la meilleure façon pour représenter les données sous la forme de tables mises en relation.

La mise en oeuvre logicielle de ce modèle relationnel nécessite l'usage d'un SGBD : Système de Gestion de Bases de Données relationnel.

Le SGBD relationnel sert d'interface entre l'utilisateur et la base de données construite selon le modèle relationnel.

Il existe de nombreux SGBD (MySQL, MariaDB, PostgreSQL, SQLite, ...).

La majorité de ces SGBD utilise une architecture client/serveur. Le SGBD SQlite lui ne nécessite pas de serveur, est bien qu'il dispose de fonctionnalités plus limitées, nous utiliserons SQLite .

Définition

L'utilisateur va pouvoir interagir avec la base de données, par l'intermédiaire de requêtes saisies dans le SGBD, notamment pour la consulter et/ou la modifier.

Le SGBD veille alors au respect du modèle relationnel (contraintes de domaine (type), de relation (clé primaire), de référence (clé étrangère)).

Ces requêtes respectent la syntaxe d'un langage nommé langage SQL (Structure Query Language pour Langage de Requête Structuré).

Le cours portera sur les bases de données construites dans le cours précédent dans le respect du modèle relationnel :

• principalement sur la base de données de l'association canine ;
• et aussi sur la base de données de l'historique des stocks de vaccins contre la Covid-19 par département.

Rappel : Schémas de relation de la base de données de l'association canine

Les schémas de relation de la base de données de l'association canine sont les suivants :

    Race(id_race: Entier,
         libelle: Chaîne de caractères)

    Adhérent(id_adherent: Entier,
             nom: Chaîne de caractères,
             prenom: Chaîne de caractères)

    Chien(id_tatouage: Entier,
          nom: Chaîne de caractères,
          annee_naissance: Chaîne de caractères,
          sexe: Chaîne de caractères,
          #id_race: Entier,
          #id_adherent: Entier)

    Concours(id_concours: Entier,
             ville: Chaîne de caractères,
             annee_concours: Entier)

    Participe(#id_tatouage: Entier,
              #id_concours: Entier,
              classement: Entier)

Langage SQL

Définition

L'interaction avec le logiciel de Système de Gestion de Base de Données (SGBD) se fait via la saisie d'une suite de requêtes SQL (ou d'ordres SQL).

Il est alors possible, via le langage SQL, de :

• créer des tables dans la base de données,
• d'interroger la base de données,
• de mettre à jour la base de données.

Pour exemple, la requête SQL suivante permet de créer la table concours :

CREATE TABLE concours(id_concours INTEGER PRIMARY KEY,
                      ville TEXT,
                      annee_concours INTEGER);

Remarque

• Un ordre SQL se termine toujours par un ;.
• Les indentations ne sont ici pas significatives, elles permettent simplement d'améliorer la lisibilité.
• Le langage SQL est insensible à la casse, on aurait tout aussi bien pu écrire CrEaTE TaBle ... mais l'usage veut que les mots clés du langage SQL soient écrits en majuscules (ici CREATE TABLE, INTEGER, ...) et le nom des tables et des attributs en minuscules (ici concours, id_concours, ...).
• Le nom des tables et des attributs ne peuvent pas contenir d'espaces, on a recours au caractère _ si besoin en guise d'espace (on évitera aussi l'usage des caractères accentués).

Création de table et contraintes d'intégrité

Respect de la contrainte de domaine

Définition

La création des tables est basée sur les mots clés CREATE TABLE ... suivi :

• du nom de la table ;
• puis, entre parenthèses, de la liste de définitions des attributs : attribut et son type et optionnellement une contrainte pour l'attribut.
• à noter que des contraintes globales peuvent être ajoutées optionnellement à la fin de la définition des attributs.

Exemple : Création de la table concours :

CREATE TABLE concours(id_concours INTEGER PRIMARY KEY,
                      ville TEXT,
                      annee_concours INTEGER);

autre écriture possible :

CREATE TABLE concours(id_concours INTEGER,
                      ville TEXT,
                      annee_concours INTEGER,
                      PRIMARY KEY(id_concours));

Ici on crée la table concours :

• Le premier attribut se nomme id_concours, son domaine (son type) est INTEGER.
• Ce premier attribut se voit aussi associé à la contrainte PRIMARY KEY (en effet id_concours est clé primaire de la table concours).
• Le second attribut ville a TEXT comme domaine.
• Le troisième annee_concours a INTEGER comme domaine.

L'attribution d'un domaine à chaque attribut de la table permet de satisfaire le respect de la contrainte de domaine imposée par le modèle relationnel.

A noter que le type des attributs décrits dans ce cours sont ceux offerts par le logiciel SQLlite à savoir principalement :

• INTEGER ;
• TEXT ;
• REAL ;

Le standard SQL définit encore plus de types possibles :

Nom du type	Description
SMALLINT	entier 16 bits signé
INTEGER ou INT	entier 32 bits signé
BIGINT	entier 64 bits signé
DECIMAL(n,f)	décimal signé de n chiffres dont f après la virgule
REAL	flottant 32 bits
CHAR(n)	flottant 32 bits
VARCHAR(n)	chaîne de caractères d'au plus n caractères
TEXT	chaîne de caractères de taille quelconque
DATE	date au format AAAA-MM-JJ
TIME	heure au format hh:mm:ss
TIMESTAMP	date et heure au format AAAA-MM-JJ hh:mm:ss

Respect des contraintes de relation et de référence

Définition

L'ajout d'une contrainte supplémentaire dans l'ordre CREATE TABLE ... permet de spécifier les attributs devant faire office de clé primaire ou de clé étrangère.

Clé primaire

Les mots clés PRIMARY KEY permettent d'indiquer qu'un attribut est une clé primaire.

CREATE TABLE concours(id_concours INTEGER PRIMARY KEY,
                    ville TEXT,
                    annee_concours INTEGER);

Ici les mots clés PRIMARY KEY indiquent que l'attribut id_concours est clé primaire de la table concours (respect de la contrainte de relation).

Clé étrangère

Le mot clé REFERENCES permet d'indiquer qu'un attribut est une clé étrangère (référence à une clé primaire présente dans une autre table).

Le mot clé REFERENCES est suivi du nom de la table où se trouve la clé primaire associé et de son nom placé entre parenthèses.

Exemple : Création de tables adherent, race et chien

CREATE TABLE adherent(id_adherent INTEGER PRIMARY KEY,
                      nom TEXT,
                      prenom TEXT);

CREATE TABLE race(id_race INTEGER PRIMARY KEY,
                  libelle TEXT);

CREATE TABLE chien(id_tatouage TEXT PRIMARY KEY,
                   nom TEXT,
                   annee_naissance INTEGER,
                   sexe TEXT,
                   id_adherent INTEGER REFERENCES adherent(id_adherent),
                   id_race INTEGER REFERENCES race(id_race));

Ici l'attribut id_adherent est une clé étrangère dans la table chien de part la présence de la contrainte REFERENCES adherent(id_adherent). id_adherent étant donc clé primaire de la table adherent.

De la même façon, l'attribut id_race est une clé étrangère dans la table chien de part la présence de la contrainte REFERENCES race(id_race). id_race étant donc clé primaire de la table race.

Une contrainte portant sur plusieurs attributs (comme ici REFERENCE) est généralement placée à la fin de la liste de définition des attributs, l'écriture de la requête SQL liée à la création de la table chien peut donc aussi s'écrire :

CREATE TABLE chien(id_tatouage TEXT,
                   nom TEXT,
                   annee_naissance INTEGER,
                   sexe TEXT,
                   id_adherent INTEGER,
                   id_race INTEGER,
                   PRIMARY KEY (id_tatouage),
                   FOREIGN KEY (id_adherent) REFERENCES adherent(id_adherent),
                   FOREIGN KEY (id_race) REFERENCES race(id_race));

Application

Exercice 1

Donner la requête permettant la création de la table de la table participe.

Rappel : Schéma de relation de la table participede la base de données de l'association canine

    Participe(#id_tatouage: Entier,
              #id_concours: Entier,
              classement: Entier)

Respect de contraintes pour l'utilisateur

Définition

Le langage SQL autorise aussi l'ajout de contraintes "utilisateur" lors de la création d'une table.

Pas d'absence de valeur

NOT NULL : contrainte "utilisateur" (placée à la suite de la définition de l'attribut concerné) n'autorisant pas l'attribut à être vide.

Par exemple, la création de la table concours peut être agrémentée de la contrainte NOT NULL :

CREATE TABLE concours(id_concours INTEGER,
                      ville TEXT NOT NULL,
                      annee_concours INTEGER NOT NULL,
                      PRIMARY KEY(id_concours));

A noter qu'un attribut faisant déjà office de clé primaire est nécessairement par défaut NOT NULL.

Respect d'une condition lors d'un ajout

CHECK : contrainte "utilisateur" (placée à la fin de la définition des attributs) permettant d'imposer le respect d'une condition lors de l'ajout futur de données dans la table.

Exemple :

CREATE TABLE concours(id_concours INTEGER PRIMARY KEY,
                      ville TEXT,
                      annee_concours INTEGER,
                      CHECK (annee_concours<=2022));

Ici, la contrainte CHECK n'autorisera que l'ajout de valeurs inférieures ou égales à 2022 pour l'attribut id_concours de la table concours.

Vérification des contraintes

L'ensemble des contraintes (domaine, relation, référence et utilisateur) imposées lors de la création de la table seront vérifiées par le SGBD lors de chaque insertion de données dans la table.

Application

Exercice 2

Donner puis saisir dans le logiciel SQLite, toutes les requêtes SQL permettant la création des tables de la base de données de l'association canine dans le respect de toutes les contraintes du modèle relationnel associé (on ajoutera aussi le mot clé CHECK pour vérifier si le sexe d'un chien vaut f ou m et pour vérifier si l'année de concours est inférieure ou égale à 2022).

Rappel : Schémas de relation de la base de données de l'association canine

Les schémas de relation de la base de données de l'association canine sont les suivants :

    Race(id_race: Entier,
         libelle: Chaîne de caractères)

    Adhérent(id_adherent: Entier,
             nom: Chaîne de caractères,
             prenom: Chaîne de caractères)

    Chien(id_tatouage: Entier,
          nom: Chaîne de caractères,
          annee_naissance: Chaîne de caractères,
          sexe: Chaîne de caractères,
          #id_race: Entier,
          #id_adherent: Entier)

    Concours(id_concours: Entier,
             ville: Chaîne de caractères,
             annee_concours: Entier)

    Participe(#id_tatouage: Entier,
              #id_concours: Entier,
              classement: Entier)

Expliquer si la création des tables doit respecter un certain ordre.

Suppression d'une table

Définition

La suppression d'une table est rendu possible grâce à la requête SQL suivante :

DROP TABLE concours;

La table concours (et donc toutes les données qu'elle comporte) est ici supprimée de la base de données.

Définition

Evidemment la suppression d'une table n'est possible que si les éventuelles clés étrangères qu'elle comporte ne fait pas référence à des clés primaires encore existantes dans d'autres tables.

Ici encore l'ordre des requêtes SQL à une importance.

Insertion de données dans une table

Définition

L'insertion d'enregistrements (également nommés tuples ou lignes ou entités) dans une table est réalisée via les mots clés INSERT INTO ... VALUES ....

Dans le cas où les attributs à insérer sont dans l'ordre imposé lors de la création de la table, les mots clés INSERT INTO sont suivis du nom de la table puis du mot clé VALUES suivi des tuples séparés par des virgules :

INSERT INTO concours VALUES (1,"Orange",2019),
                            (2,"Avignon",2019),
                            (3,"Avignon",2020),
                            (4,"Carpentras",2020),
                            (5,"Bollène",2021);

Dans le cas où les attributs sont dans un ordre différent on utilisera la requête suivante :

INSERT INTO concours(id_concours, annee_concours, ville) VALUES (1,2019,"Orange"),
                                                                (2,2019,"Avignon"),
                                                                (3,2020,"Avignon"),
                                                                (4,2020,"Carpentras"),
                                                                (5,2021,"Bollène");

Les ordres ci-dessus sont équivalents et permettent d'insérer des enregistrements dans la table concours.

Exercice 3

Donner puis saisir les requêtes SQL permettant l'insertion de l'ensemble des données dans chacune des tables de la base de données de l'association canine. Attention à la cohérence dans l'ordre d'insertion des données.

Données à insérer dans les tables

Table Adhérent :

id_adherent	nom	prenom
1	Filo	Jade
2	Cavette	Emma
3	Dupois	Loïc
4	Desnure	Lana
5	Gaullois	Michel

Table Race :

id_race	libelle
1	Beagle
2	Caniche
3	Epagneul
4	Berger Australien
5	Saint-Bernard
6	Labrador Retriver
7	Berger Allemand

Table Concours :

id_concours	ville	annee_concours
1	Orange	2019
2	Avignon	2019
3	Avignon	2020
4	Carpentras	2020
5	Bollène	2021

Table Chien :

id_tatouage	nom	annee_naissance	sexe	id_adhérent	id_race
158det	Pifou	2014	m	1	2
267soi	Rex	2014	m	3	6
172frg	Smoothie	2016	f	1	4
269qzv	Noisette	2018	f	1	3
349jad	Cassoulet	2015	m	5	2
698fri	Némo	2015	m	4	5
523chu	Basta	2016	f	2	1
472koa	Némo	2017	m	4	4

Table Participe :

id_tatouage	id_concours	classement
172frg	3	1
349jad	2	7
523chu	1	3
472koa	2	5

Sélection de données dans une table

Définition

Après avoir créé une table et y avoir inséré des enregistrements, il est possible de consulter cette table notamment en ne cherchant à sélectionner qu'une partie des enregistrements répondant à une certaine condition.

Pour cela on utilise les mots clés SELECT ... FROM ... WHERE ...

Exemple

SELECT * FROM concours WHERE annee_concours >= 2020;

La requête SQL ci-dessus renvoie une table constituée des enregistrements de la table concours pour lesquels l'année du concours est supérieure ou égale à 2020 :

id_concours	ville	annee_concours
3	Avignon	2020
4	Carpentras	2020
5	Bollène	2021

SELECT est suivi ici d'une * qui signifie la volonté d'afficher tous les attributs des enregistrements qui seront sélectionnés.

Puis FROM suivi du nom de la table souhaitée.

Puis WHERE suivi d'une condition portant sur un ou plusieurs attributs de la table.

En absence du mot clé WHERE, l'ordre SQL renvoie la table d'origine.

Exemple

Requête SQL :

SELECT * FROM concours;

Interprétation :

On cherche à afficher tous les enregistrements de la table concours.

Résultat :

id_concours	ville	annee_concours
1	Orange	2019
2	Avignon	2019
3	Avignon	2020
4	Carpentras	2020
5	Bollène	2021

La requête SELECT ... FROM ... WHERE ... peut comporter des conditions plus complexes à l'aide de booléens :

Exemple

Requête SQL :

SELECT * FROM concours WHERE annee_concours > 2016 AND
                             annee_concours < 2020;

Interprétation :

On cherche à afficher les enregistrements pour lesquels les années de concours sont postérieures à l'année 2016 et antérieures à 2020.

Résultat :

id_concours	ville	annee_concours
1	Orange	2019
2	Avignon	2019

Ici on souhaite donc sélectionner les enregistrements de la table concours pour les lesquels l'année du concours est strictement inférieure à 2020 et strictement supérieure à 2016.

On peut aussi faire des requêtes SQL rapprochées.

Exemple

Requête SQL :

SELECT * FROM race WHERE libelle LIKE '%Berger%';

Interprétation :

On cherche à afficher les enregistrements pour lesquels le libellé de la race comporte le terme Berger.

Résultat :

id_race	libelle
4	Berger Australien
7	Berger Allemand

Application

Exercice 4

Donner puis saisir sur SQLite la requête SQL permettant d'afficher la table adherent.

Exercice 5

Donner puis saisir sur SQLite la requête SQL permettant d'afficher les enregistrements de la table chien relatifs aux femelles présentes dans l'association canine.

Projection sur une table

Définition

Les mots clés SELECT ... FROM ... permettent de procéder à une projection sur la table (sélection de certaines colonnes), pour cela il suffit de remplacer le symbole * par les attributs espacés par des virgules que l'on veut voir apparaître dans la table renvoyée par l'ordre SQL :

Exemple

Requête SQL :

SELECT annee_concours, ville FROM concours;

Interprétation :

On cherche à afficher les années des concours et les villes associées.

Résultat :

annee_concours	ville
2019	Orange
2019	Avignon
2020	Avignon
2020	Carpentras
2021	Bollène

Si besoin les colonnes de la table renvoyée peuvent être renommées grâce au mot clé AS.

Exemple

Requête SQL :

SELECT annee_concours AS annee, ville AS lieu FROM concours;

Interprétation :

On cherche à afficher les années des concours (en renommant la colonne en annee) et les villes associées (en renommant la colonne en lieu).

Résultat :

annee	lieu
2019	Orange
2019	Avignon
2020	Avignon
2020	Carpentras
2021	Bollène

On peut donc cumuler les effets de la projection et de la sélection pour n'afficher que certaines lignes et certaines colonnes de la table.

Exemple

Requête SQL :

SELECT annee_concours AS annee, ville FROM concours WHERE annee_concours > 2019;

Interprétation :

On cherche à afficher les années des concours (en renommant la colonne en annee) et les villes associées (en renommant la colonne en lieu) pour lesquelles les années de concours sont stérieures à l'année 2019.

Résultat :

annee	lieu
2020	Avignon
2020	Carpentras
2021	Bollène

Application

Exercice 6

Donner puis saisir sur SQLite la requête SQL permettant d'afficher le nom et l'année de naissance des chiens présent dans l'association canine.

Exercice 7

Donner puis saisir sur SQLite la requête SQL permettant d'afficher l'identifiant de tatouage des chiens mâles.

Agrégation

Définition

On peut réaliser des opérations d'agrégation (en vue d'obtenir un résultat unique issu d'un calcul) sur une table.

Comptage

Exemple

Requête SQL :

SELECT COUNT(id_concours) AS total FROM concours WHERE annee_concours = 2020;

ou

SELECT COUNT(*) AS total FROM concours WHERE annee_concours = 2020;

Interprétation :

On souhaite ici compter le nombre d'enregistrements présent dans la table concours pour lesquels l'année de concours est 2020. Autrement dit on souhaite connaître le nombre de concours auxquels a participé l'association canine en 2020.

Résultat :

total
2

Somme

On peut aussi faire la somme de valeurs.

Exemple

Requête SQL :

SELECT SUM(annee_concours) AS somme FROM concours WHERE ville = 'Avignon';

Interprétation :

On souhaite obtenir la somme des années des concours ayant eu lieu à Avignon.

Résultat :

somme
4039

Moyenne

On peut aussi faire la moyenne de valeurs.

Exemple

Requête SQL :

SELECT AVG(annee_concours) AS moyenne FROM concours;

Interprétation :

On souhaite (bizarrement 🙄 aussi) obtenir a moyenne des années de concours.

Résultat :

moyenne
2019.8

Maximum et minimum

On peut aussi obtenir la valeur maximale ou minimale présente dans la colonne sélectionnée.

Exemple

Requête SQL :

SELECT MAX(annee_concours) AS maximum FROM concours;

Résultat :

maximum
2021

Commande SQL :

SELECT MIN(annee_concours) AS minimum FROM concours;

Résultat :

minimum
2019

Application

Exercice 8

Donner puis saisir sur SQLite la requête SQL permettant d'afficher le nombre de chiens mâles présents dans l'association canine nés après 2014.

Exercice 9

Donner puis saisir sur SQLite la requête SQL permettant d'afficher la moyenne de l'année de naissance des chiens femelles présentes dans l'association canine.

Exercice 10

Donner puis saisir sur SQLite la requête SQL permettant d'afficher l'age moyen des chiens présents dans l'association canine.

Tri

Définition

Le langage SQL offre la possibilité de trier le résultat d'une requête dans l'ordre croissant (avec le mot clé ASC) ou dans l'ordre décroissant (avec le mot clé DESC).

Exemple

Requête SQL :

SELECT annee_concours AS annee, ville FROM concours WHERE annee_concours<2021 ORDER BY ville ASC;

Interprétation :

On souhaite afficher, dans l'ordre alphabétique croissant des noms de villes, les années et les villes de concours pour les années antérieures à 2021 via l'ajout du mot clé clé DISTINCT.

Résultat :

annee	ville
2019	Avignon
2020	Avignon
2020	Carpentras
2019	Orange

Application

Exercice 11

Donner puis saisir sur SQLite la requête SQL permettant d'afficher le nom des chiens de l'association classés dans l'ordre alphabétique.

Suppression de doublons

Définition

Une requête est susceptible de renvoyer des doublons. Le langage SQL permet de ne pas afficher les doublons issus d'une requête SQL.

Exemple

Requête SQL :

SELECT annee_concours FROM concours;

Interprétation :

On souhaite afficher les années de concours présentent dans la table concours.

Résultat :

annee_concours
2019
2019
2020
2020
2021

On voit apparaître des doublons dans le résultat de la requête.

L'ajout du mot clé DISTINCT, permet de ne pas afficher les doublons.

Exemple

Requête SQL :

SELECT DISTINCT annee_concours FROM concours;

Interprétation :

On souhaite afficher, sans doublon, les années de concours présentent dans la table concours.

Résultat :

annee_concours
2019
2020
2021

Application

Exercice 12

Donner puis saisir sur SQLite la requête SQL permettant d'afficher le nom des chiens de l'association sans doublon.

Jointure

Définition

Nous avons vu que les requêtes liées à des sélections et / ou des projections permettent d'interroger la base de données en affichant les données issues de la table souhaitée.

Exemple

Requête SQL :

SELECT * FROM participe;

Interprétation :

On affiche toutes les enregistrements de la table participe.

Résultat :

id_tatouage	id_concours	classement
172frg	3	1
349jad	2	7
523chu	1	3
472koa	2	5

Le résultat difficilement lisible (voire illisible 🙄), en effet la table participe comporte des clés étrangères (id_tatouage, id_concours) qui est très difficile à interpréter.

Il serait pertinent de par exemple :

• remplacer l'attribut id_tatouage par le nom du chien associé ;
• remplacer l'attribut id_concours par l'année et le lieu du concours.

Notion de jointure

L'opération de jointure permet d'obtenir ce résultat.

En effet, la jointure entre deux tables permet de considérer toutes les combinaisons de lignes entre ces deux tables ayant un attribut de même valeur.

Exemple

Rappel du contenu des tables concours, participe et chien

Table Concours :

id_concours	ville	annee_concours
1	Orange	2019
2	Avignon	2019
3	Avignon	2020
4	Carpentras	2020
5	Bollène	2021

Table Chien :

id_tatouage	nom	annee_naissance	sexe	id_adhérent	id_race
158det	Pifou	2014	m	1	2
267soi	Rex	2014	m	3	6
172frg	Smoothie	2016	f	1	4
269qzv	Noisette	2018	f	1	3
349jad	Cassoulet	2015	m	5	2
698fri	Némo	2015	m	4	5
523chu	Basta	2016	f	2	1
472koa	Némo	2017	m	4	4

Table Participe :

id_tatouage	id_concours	classement
172frg	3	1
349jad	2	7
523chu	1	3
472koa	2	5

Requête SQL :

SELECT * FROM participe JOIN concours ON participe.id_concours = concours.id_concours;

Interprétation :

On fait ici la jointure de la table participe avec la table concours selon la valeur de l'attribut id_concours avec la condition de jointure suivant le mot clé ON.

Chacune des lignes des deux tables respectant la condition de jointure sont fusionnées.

Résultat :

id_tatouage	id_concours	classement	id_concours	ville	annee_concours
172frg	3	1	3	Avignon	2020
349jad	2	7	2	Avignon	2019
523chu	1	3	1	Orange	2019
472koa	2	5	2	Avignon	2019

Il est évidemment possible d'ajouter une opération de projection pour ne conserver que les colonnes souhaitées.

Exemple

Requête SQL :

SELECT participe.id_tatouage, participe.classement, concours.ville, concours.annee_concours
    FROM participe JOIN concours ON participe.id_concours = concours.id_concours;

Interprétation :

On fait ici la jointure de la table participe avec la table concours selon la valeur de l'attribut id_concours avec la condition de jointure suivant le mot clé ON.

Chacune des lignes des deux tables respectant la condition de jointure sont fusionnées.

La projection permet de faire apparaître les colonnes souhaitées.

Résultat :

id_tatouage	classement	ville	annee_concours
172frg	1	Avignon	2020
349jad	7	Avignon	2019
523chu	3	Orange	2019
472koa	5	Avignon	2019

L'ajout d'une sélection des enregistrements à l'issue de la jointure est aussi possible.

Exemple

Requête SQL :

SELECT participe.id_tatouage, participe.classement, concours.ville, concours.annee_concours
    FROM participe JOIN concours ON participe.id_concours = concours.id_concours
    WHERE concours.annee_concours < 2020;

Interprétation :

On fait ici la jointure de la table participe_ avec la table concours_ selon la valeur de l'attribut id_concours_ avec la condition de jointure suivant le mot clé ON.

La projection permet de faire apparaître les colonnes souhaitées.

La sélection permet de n'afficher que les enregistrements issus de la jointure pour lesquels l'année de concours est antérieure à 2020.

Autrement dit, avec cette requête on veut connaître les enregistrement constitués de l'identifiant du tatouage, de le classement, de la ville et de l'année de concours pour les années antérieures à 2020.

Résultat :

id_tatouage	classement	ville	annee_concours
349jad	7	Avignon	2019
523chu	3	Orange	2019
472koa	5	Avignon	2019

Définition

On peut ne pas se limiter à une seule jointure.

Il est possible réaliser une jointure entre une table est le résultat d'une jointure.

Exemple

Souhait :

On souhaite ici réaliser la jointure entre les tables concours et participe puis avec la table chien.

Requête SQL :

SELECT * FROM participe JOIN concours ON participe.id_concours = concours.id_concours
                        JOIN chien ON chien.id_tatouage = participe.id_tatouage;

Résultat :

id_tatouage	id_concours	classement	id_concours	ville	annee_concours	id_tatouage	nom	annee_naissance	sexe	id_adhérent	id_race
172frg	3	1	3	Avignon	2020	172frg	Smoothie	2016	f	1	4
349jad	2	7	2	Avignon	2019	349jad	Cassoulet	2015	m	5	2
523chu	1	3	1	Orange	2019	523chu	Basta	2016	f	2	1
472koa	2	5	2	Avignon	2019	472koa	Némo	2017	m	4	4

Application

Exercice 13

Donner puis saisir sur SQLite la requête SQL permettant d'afficher le nom des chiens de l'association avec le nom et le prénom du propriétaire mais uniquement pour les chiens mâles.

Exercice 14

Donner puis saisir sur SQLite la requête SQL permettant d'afficher l'identifiant du tatouage des chiens ayant participé à un concours ainsi que leur nom, la ville du concours et leur classement.

Exercice 15

Donner puis saisir sur SQLite la requête SQL permettant d'afficher sans doublon le nom et le prénom des propriétaires possédant des chiens ayant participé à un concours avant 2021.

Retour sur la base de données du stock de vaccin.

Exercice 16

Donner les requêtes permettant la création des tables de la base de données de l'historique des stocks de vaccins contre la Covid-19 par département à savoir :

• la table vaccin ;
• la table code_dept ;
• la table stock.

Insérer une vingtaine d'enregistrements dans chacune des tables.

Donner les requêtes SQL permettant de :

• Afficher le nom des vaccins ;
• Afficher le nom des vaccins pour lesquels le stock dépasse un certain seuil ;
• Afficher le stock total de vaccins (tous types confondus) dans le département du Vaucluse ;
• Afficher le stock de vaccins Pfizer dans le département du Vaucluse ;
• Afficher le stock de vaccins Pfizer dans le département du Vaucluse avant une date donnée.

Rappel : Schémas de relation de l'historique des stocks de vaccins contre la Covid-19 par département

    Vaccin(id_vaccin: Entier,
           type: Texte,
           dose: Entier)

    Dept(id_dept: Entier,
         nom: Texte)

    Stock(#id_vaccin: Entier
          #id_dept: Entier
          date: Texte,
          nb: Entier)