Gap QBE

Gilles Thomassin

Copyright (C) 2016 Gilles Thomassin. Vous avez la permission de copier, distribuer ou modifier ce document selon les termes de la licence GNU de documentation libre, dans sa version 1.3 ou dans toute version ultérieure publiée par la Free Software Foundation . Cette licence est consultable ici.

Dédicace

Ce manuel ainsi que le logiciel associé est dédié à tous les étudiants qui découvrent le développement d'application. Il est dédié aussi à la communauté des développeurs Linux qui partage sans compter et grâce à laquelle on peut disposer d'un environnement de développement performant.

Table des matières

Introduction
Présentation
Le projet
gapQBE en chiffres
GapQBE pour quoi faire?
gapQBE par l'exemple

Liste des illustrations

1. Connexion
2. Select * from achat
3. Select cruno, crunom from cru
4. Select vmilles from vin
5. Icône Sans Doublon
6. Select distinct vmilles from vin
7. Tri par le menu contextuel des champs
8. Select distinct vmilles from vin
9. Liste des achats dans l'ordre décroissant des quantités.
10. Icône modifier ordre de tri
11. Priorité des critères de tri
12. Résultat du tri
13. Ajout d'un champ libre
14. Ajout d'un champ libre à une table
15. Ajout d'une fonction de groupe
16. Valeur maximale d'un champ
17. AVG(aquant)
18. Count sans doublon
19. where clinom like '_a%'
20. like '%a%'
21. Restriction sur champ numérique
22. champ calculé et restriction
23. fonction de groupe portant sur une restriction
24. count(distinct...) et restriction
25. les alias
26. between comme restriction
27.
28. Natural join
29. Jointure Inner
30.
31. 2 jointures naturelle et une restriction
32.
33.
34. Jointure à trois tables avec restriction sur la semaine
35. Select count(*) from achat Natural Join client where clinom='Lambert'
36. select avg(aquant) from achat natural join vin natural join cru where ...
37.
38.
39.

Ce document présente le logiciel d'interrogation de donnée <program>gapQBE</program>, il présente aussi les différents concepts nécessaires à la réalisation de requêtes SQL.

Introduction


Présentation

Le projet

Ce projet date de l'année 2010.

J'ai coutûme de développer les logiciels par vague, en laissant reposer le code entre deux interventions un peu comme le ferait un boulanger avec ses brioches.

La comparaison s'arrête là car contrairement à la pâte, le code ne se bonifie pas quand on le laisse reposer.

Par contre le développeur lui prend assez de recul pour voir son projet avec un regard presque extérieur, et ç'est très important quand on travaille seul.

gapQBE en chiffres

4390 lignes de code "wc -l *.cpp *.h" (merci wc)

7 ans entre la première ligne et la première release.

1 développeur :(

13 classes "ls *.h | wc -l"

26 fichiers sources

Traduit en français ;)

Approximativement: 150 heures de codage/test/debogage

Documentation: 20 heures

GapQBE pour quoi faire?

Ce logiciel sert à construire des requêtes SQL d'interrogation des données.

Il travaille sur une base de données dont il affiche les tables.

Pour chaque requête à réaliser on ajoute la ou les tables nécessaires, on choisit quels champs doivent apparaître dans le résultat, on filtre sur des valeurs, on relie les tables nécessaires entre elles, on trie, on groupe. Enfin on enregistre sa requête dans sa bibliothèque (for future use).

gapQBE par l'exemple

On va essayer ici de voir une session complète d'utilisation de GapQBE sur la célèbre base des vins utilisée par des générations d'étudiant.

En voici le code sql:

create table region( rno integer not null primary key,rnom char(30));

create table cru( cruno char(2) not null primary key,crunom char(30),rno integer not null,
foreign key (rno) references region(rno));

create table client( clino integer not null primary key,clinom char(30));

create table vin( vno integer not null primary key,cruno char(2) not null,vmilles integer not null,foreign key (cruno) references cru(cruno));

create table achat( clino integer not null,vno integer not null,asem integer not null,aquant integer not null,
primary key (clino,vno,asem),
foreign key(clino) references client(clino),
foreign key (vno) references vin(vno));


insert into region values (1,'Alsace');

insert into region values (2,'Beaujolais');

insert into region values (3,'Bordeaux');

insert into region values (4,'Bourgogne');

insert into region values (5,'Pays de Loire');

insert into client values (1001,'Fraudeau');

insert into client values (1002,'Lambert');

insert into client values (1003,'Dupont');

insert into client values (1004,'Simonneau');

insert into client values (1005,'Lautrec');

insert into cru values ('VO','Volnay', 4);

insert into cru values ('TO','Tokay', 1);

insert into cru values ('MU','Muscadet', 5);

insert into cru values ('PF','Pouilly-Fuissé', 4);

insert into cru values ('CH','Chenas', 2);

insert into cru values ('RO','Romanée', 4);

insert into cru values ('HB','Haut-Brion', 3);

insert into cru values ('MO','Morgon', 2);

insert into cru values ('PO','Pommard', 4);

insert into cru values ('SA','Sauterne', 3);

insert into vin values (10,'VO', 1985);

insert into vin values (11,'VO', 1990);

insert into vin values (20,'TO', 1991);

insert into vin values (30,'MU', 1992);

insert into vin values (40,'PF', 1991);

insert into vin values (41,'PF', 1992);

insert into vin values (50,'CH', 1992);

insert into vin values (60,'PO', 1982);

insert into vin values (61,'PO', 1985);

insert into vin values (70,'HB', 1987);

insert into vin values (80,'RO', 1989);

insert into vin values (85,'RO', 1990);

insert into vin values (90,'MO', 1992);

insert into vin values (5,'SA', 1988);

  1. La connexion

    On doit ici fournir le moteur de base de donnée à utiliser, le nom de la base sur laquelle on travaille, un utilisateur habilité à utiliser la base ainsi que son mot de passe, vous pouvez aussi indiquer le nom du serveur ou son adresse ip si vous travaillez sur un serveur distant et qu'il vous autorise l'utilisation par le réseau.

    Figure 1. Connexion

    La boîte de dialogue de connexion.

  2. Voir les achats

    On glisse la table "achat" dans la zone de travail et on clique avec le bouton droit de la souris sur un champ que l'on souhaite voir apparaître dans le résultat on sélectionne alors l'oeil. On recommence la procédure pour chaque champ à afficher.

    Figure 2. Select * from achat

    Voir les infos de tous les achats

  3. Voir les codes et les noms de tous les crus

    On glisse la table "CRU" dans la zone de travail et on clique avec le bouton droit de la souris sur un champ que l'on souhaite voir apparaître dans le résultat on sélectionne alors l'oeil. On recommence la procédure pour chaque champ à afficher (cruno et crunom).

    Figure 3. Select cruno, crunom from cru

    Numéro et nom de tous les crus.

  4. Voir les millesimes des vins

    On glisse la table "vin" dans la zone de travail et on clique avec le bouton droit de la souris sur le champ "vmilles" que l'on souhaite voir apparaître dans le résultat on sélectionne alors l'oeil.

    Figure 4. Select vmilles from vin

    Afficher la liste des millésimes des vins.

  5. Voir les millesimes des vins sans les doublons

    On clique sur l'icône la plus basse de la barre d'icône verticale

    Figure 5. Icône Sans Doublon

    Icône Distinct.


    représentant 2 ronds et 1 carré et cela ajoute le distinct permettant de retirer les doublons.

    Figure 6. Select distinct vmilles from vin

    Afficher la liste des millésimes des vins sans les doublons.

  6. Trier le résultat

    A l'aide d'un clic droit sur le champ pour lequel le tri s'applique on choisit trier ascendant dans le menu contextuel.

    Figure 7. Tri par le menu contextuel des champs

    Tri ascendant ou descendant.


    Figure 8. Select distinct vmilles from vin

    Afficher la liste des millésimes des vins sans les doublons.

  7. Voir les informations sur les achats effectués trier par quantité décroissante

    Cette fois dans le menu contextuel, on choisira trier mais en ordre "desc".

    Figure 9. Liste des achats dans l'ordre décroissant des quantités.

    Tri décroissant

  8. Tri sur plusieurs champs

    On peu trier sur un premier champ et lorsque les valeurs sont identiques trier sur un deuxième champ.

    Il est donc nécessaire de définir un ordre de tri.

    Figure 10. Icône modifier ordre de tri

    Icône Sort order.

    Lors d'un clic sur cette icône, la boîte de dialogue suivante apparait permettant de définir la priorité des critères de tri.

    Figure 11. Priorité des critères de tri

    Achats triés par semaine puis par quantité.

    Figure 12. Résultat du tri

    Tri par semaine croissante et quantité décroissante.

  9. Champs libre hors table

    On peut aussi demander à un sgbd d'afficher quelque chose qui n'est pas issu d'une table. J'appelle ça un champ "libre".

    On peut par exemple lui demander d'afficher la date du jour now() ou combien font 3*5.

    A l'aide du menu contextuel de la zone de travail on sélectionne ajouter champ libre et il n'y a plus qu'à le définir.

    Figure 13. Ajout d'un champ libre

    Afficher 3*5.

  10. Champs libre dans une table

    On peut aussi ajouter un champ libre constant ou calculé à une table. J'appelle ça aussi un champ "libre".

    On peut par exemple afficher 1.2*puHT ou "Bonjour" ou PuHT* tauxTVA as prixTTC.

    A l'aide du menu contextuel de la table on sélectionne ajouter champ libre et il n'y a plus qu'à le définir.

    Figure 14. Ajout d'un champ libre à une table

    Afficher "Connaissez-vous".

  11. Fonction de groupe

    Les fonctions de group count(),sum(),max(),min(),avg() portant sur des groupes de ligne du résultat sont à ajouter dans la zone de saisie située en bas à droite.

    On peut par exemple afficher le nombre de client contenu dans la table Client.

    Figure 15. Ajout d'une fonction de groupe

    Select count(*) from client

  12. Fonction de groupe

    Les fonctions de group count(),sum(),max(),min(),avg() portant sur des groupes de ligne du résultat sont à ajouter dans la zone de saisie située en bas à droite ici la quantité maximale achetée.

    Figure 16. Valeur maximale d'un champ

    Select max(aquant) as qteMax from achat

  13. Moyenne des achats

    Encore une fonction de groupe: la moyenne AVG qui signifie average.

    Figure 17. AVG(aquant)

    Select avg(aquant) from achat

  14. Nombre de vins différents achetés

    Le distinct commence par supprimer les doublons et le count opère ensuite.

    Figure 18. Count sans doublon

    Select count(distinct vno) from achat

  15. Restreindre en fonction de la valeur d'un champ

    Par le menu contextuel du champ on ajoute une condition: ici like '_a%' c'est à dire tous les clients qui ont la lettre a en deuxième position.

    Le caractère joker '_' remplace un caractère.

    Le caractère joker '%' remplace une suite de caractère.

    Figure 19. where clinom like '_a%'

    Select distinct client.clinom where clinom like '_a%'

  16. Les clients qui ont la lettre a dans leur nom de famille

    On utilise ici deux fois le joker % une fois devant le a et une fois après.

    Figure 20. like '%a%'

    Select clinom from client where clinom like '%a%'

  17. restriction sur un champ non affiché

    Bien sûr la restriction peut être réalisée sur un champ non affiché ici vno=11 nous donnera les numéro des clients ayant acheté au moins une fois le vin numéro 11.

    Figure 21. Restriction sur champ numérique

    Select achat.clino from achat where vno=11

  18. le prix du vin numéro 11

    Si le vin 11 est vendu 6 euros quels sont les montants des achats de ce vin?

    Figure 22. champ calculé et restriction

    Select clino,asem,aquant aquant*6 from achat where vno=11

  19. Achats du client numéro 1004

    Nombre d'achat effectué par le client numéro 1004.

    Figure 23. fonction de groupe portant sur une restriction

    Select count(*) from achat where clino=1004

  20. count de valeurs différentes d'un champ

    Nombre de vins différents achetés par le client numéro 1004

    Figure 24. count(distinct...) et restriction

    Select count(distinct vno) from achat where clino=1004

  21. définition d'alias de champ

    Un alias permet de changer le nom d'une colonne affichée.

    Figure 25. les alias

    Select count(distinct(clino)) as nombreDAcheteur from achat

  22. between

    Obtenir le nombre de clients différents ayant réalisé au moins un achat entre les semaines 11 et 41.

    Figure 26. between comme restriction

    Select count(distinct clino) from achat where asem between 11 and 41

  23. quantité maximale achetée pendant ou après la semaine 30

    La condition peut s'écrire à l'aide des opérateurs < <= > >=.

    Figure 27. 

    Select max(aquant) as maximum from achat where asem>=30

  24. Enfin une jointure

    Certaines requêtes nécessitent de prendre les champs dans plusieurs tables qui doivent être alors correctement reliées. Il est possible d'utiliser le Natural join quand la clef étrangère a le même nom que la clef primaire ce qui est le cas ici en effet la table cru dispose en clef étrangère du champ rno clef primaire de la table région.

    Figure 28. Natural join

    Select crunom,rnom from cru Natural Join Region

  25. Une vrai jointure maîtrisée

    Cette fois nous allons réaliser une jointure en spécifiant la condition qui permet de relier les deux tables

    Figure 29. Jointure Inner

    Select crunom,rnom from cru Inner Join region on region.rno=cru.rno

  26. Jointure impliquant trois tables 1 natural et une inner

    Une jointure "INNER" suivie d'une jointure "NATURAl" (pour le plaisir).

    Figure 30. 

    Select vno,vmilles,crunom,rnom from region inner join ...

  27. 2 jointures naturelles et une restriction

    Nom des clients ayant acheté au moins un vin du cru nommé 'Pommard'.

    Select clinom from cru natural join vin natural join cru where crunom='Pommard';

    Figure 31. 2 jointures naturelle et une restriction

    Select

  28. noms des clients, numéro des vins, quantité achetée pour la semaine 30

    Figure 32. 

    Select

  29. Quatre tables et des natural join

    Nom des clients ayant acheté du Pommard

    Figure 33. 

    Select...

  30. Noms des clients,no des vins qtes achetées, numero des crus, millesimes

    Figure 34. Jointure à trois tables avec restriction sur la semaine

    Select...

  31. Nombre des achats effectués par Lambert

    Figure 35. Select count(*) from achat Natural Join client where clinom='Lambert'

    Select...

  32. Quantité moyenne achetée du cru 'Tokay'

    Figure 36. select avg(aquant) from achat natural join vin natural join cru where ...

    Select

  33. Numéro du cru du vin 20

    Figure 37. 

    Select

  34. Sauver la requete

    Figure 38. 

    Select

  35. S'en servir

    Figure 39. 

    Select

Enfin c'est fini, j'espère que vous apprécierez gapQBE et que vous apprendrez le SQL un peu plus vite et mieux.

P.S. Le développement libre, c'est un peu l'humanitaire de l'informatique. Le code publié permet à d'autres développeurs d'avancer dans leurs propres travaux. Un des plus prolifique virus informatique est la passion du développement, et je m'efforcerai toujours de le transmettre à mes futurs étudiants ;) .