exploiter données pgi sql - economie-gestion.ac...

GREID Créteil Mars 2008

Véronique Bennetot-Dereux 1 / 18

Exploiter les données d'un PGI avec SQL

Propriétés

Description

Titre de la production

Exploiter les données d'un PGI avec SQL

Auteur

Véronique Bennetot-Dereux – GREID Académie de Créteil [email protected]

Niveau

Terminale Sciences et Technologies de Gestion (STG)

Thème

Gestion des Systèmes d'Information (GSI)

Type de ressources

Mode opératoire

Partie de programme visée

Thème B –Services fournis par le système d'information et technologie associées. Partie 1 : La gestion des données (1.1 Définition, interrogation et mise à jour des données).

Problématique générale

Extraire des informations pertinentes par l’interrogation d'une base de données. (requêtes SQL d’interrogation des données). Ecrire des requêtes SQL au moyen du requêteur SQL fourni dans le PGI Cegid.

Domaine de compétences du B2i

Domaine 3 : Créer, produire, traiter, exploiter des données

Logiciel(s) RIP et outils TIC

CEGID S1 (RIP)

Durée

Séance de travaux pratiques de 2 heures

Mots-clés

Terminale STG GSI, CEGID, Base de données, langage SQL, SELECT, FROM, WHERE, MIN, MAX, SUM, COUNT, AVG, GROUP BY, AS, HAVING

Date de première publication

30/06/2008

Adresse de téléchargement

http://economie-gestion.ac-creteil.fr/

NB : Pour obtenir la ressource dans un autre format (DOC ou ODT) que le format PDF, contactez directement l'auteur.



Présentation L'objet de ce mode opératoire est d'utiliser le requêteur SQL fourni dans le PGI Cegid. Il permet notamment de créer ses propres requêtes SQL et d'afficher leurs résultats (que l'on peut exporter sous Excel). Par contre, il ne permet pas les requêtes de mise à jour des données (les ordres SQL de type INSERT, UPDATE, DELETE sont interdits via ce requêteur).

L'exercice peut se dérouler dès la 1ère installation du PGI sans autre installation. La société exemple fournie par défaut (la société Neworld) est utilisée dans ce mode opératoire. Le dictionnaire des tables de cette société est disponible mais pas modifiable (pas de possibilité d'évolution de la structure de la base de données car les ordres SQL de type CREATE, ALTER ou DROP sont interdits via ce requêteur).

En pré requis, il est demandé à ce que les élèves aient déjà une connaissance des grandes fonctionnalités de ce PGI ainsi que des notions d'analyse notamment sur le schéma relationnel.

Un mémento SQL sur les commandes SQL d'interrogation des données est fourni en annexe.

Objectifs

Apprendre le langage SQL de manipulation de données (exclusivement des requêtes SQL de consultation). Les diverses activités proposées seront de :

- Trouver, par observation dans les tables, les informations devant être fournies par une requête SQL,

- Ecrire et tester des requêtes SQL afin d'en contrôler la conformité,

- Modifier une requête SQL dont le résultat n'est pas conforme à la demande formulée.

- Observer les requêtes SQL générées automatiquement par l'outil.

Déroulement de la séance La séance se divise en 6 parties :

1. Une 1ère partie dans laquelle les élèves étudient la structure de la base Neworld. Pour cela, ils sont amenés à afficher et observer la structure de certaines tables et soumis à un questionnement visant à vérifier leur acquis de l'intégrité référentielle entre 2 attributs de 2 tables différentes.

2. Une 2ème partie dans laquelle ils se familiarisent avec le requêteur SQL en testant une 1ère

requête et en vérifiant son résultat.

3. Une 3ème partie dans laquelle ils testent des requêtes SQL fournies dans l'énoncé et pour lesquelles ils doivent définir l'intitulé (à quel besoin répond la requête ?).

4. Une 4ème partie dans laquelle ils écrivent et testent des requêtes SQL (vérifier que le résultat

est conforme à la demande).

5. Une 5ème partie dans laquelle les élèves corrigent des requêtes incorrectes.

6. Une 6ème partie dans laquelle les élèves interrogent la base de données CEGID au moyen d'un assistant graphique et observent les requêtes SQL effectuées en interne par le PGI.



Enoncé Lancer le logiciel

Ouvrir la société "Jeu d'essai" fournie par défaut dans Cegid S1. Il s'agit de l'entreprise Neworld dont l'activité est la vente de matériels de plongée et de nautisme.

Se connecter en utilisant le Login : CEGID Mot de passe : VB

1ère partie : Observation de tables de la base "Neworld".

Ouvrir le module "Boite à outils".

Par défaut, vous êtes sur l'onglet Paramètres. Cliquez sur l'onglet Utilitaires puis sur le menu

Dictionnaire des tables

Sélectionner la table PAYS afin d'afficher sa structure.

Cliquez ici pour lister les tables disponibles



Combien d'attributs composent la table Pays ?

Quel attribut permet d'identifier de manière unique un enregistrement de la table ?

Faites de même avec la table CodePost:

Les 2 tables ont-elles des attributs en commun ?

Si oui, qu'est ce que cela implique ?

A l'aide de la vue Dictionnaire des tables, sélectionnez la 1ère table de la base Neworld puis validez. Combien de tables contient la base de données Neworld ?

2ème partie : Test d'une première requête.

Ouvrez le moniteur SQL de Cegid.

Testez la requête suivante : SELECT * FROM CODEPOST

Cliquez ici pour afficher la structure de la table

Cliquez ici pour ouvrir le moniteur SQL de Cegid



Que donne-t-elle comme résultat ? 3ème partie : Compréhension de requêtes SQL.

Pour chacune des requêtes SQL ci-dessous :

- Donner l'intitulé de la question à laquelle elle répond.

- Testez-la sur le requêteur Cegid et contrôler que le résultat obtenu est bien conforme à votre intitulé.

Requête N°1 :

Requête N°2 :



Requête N°3 :

Requête N°4 :



Requête N°5 :

Requête N°6 :



4ème partie : Ecriture de requêtes SQL.

A l'aide du requêteur, écrivez les requêtes SQL permettant d'obtenir les résultats suivants :

Requête N°1 : Nombre total de pays.

Requête N°2 : Liste des pays (nom abrégé et code bancaire) classés par ordre décroissant.



Requête N°3 : Libellé du pays dont le code pays est CSK.

Requête N°4 : Libellés des pays dont les 2 premières lettres sont CH ou DA

Requête N°5 : Nom, prénom et ville des commerciaux



5ème partie : Modification de requêtes SQL erronées.

Pour chacune des requêtes SQL ci-dessous, on vous communique :

- Son intitulé, la requête SQL correspondante et le résultat obtenu.

- Expliquez pourquoi le résultat obtenu n'est pas conforme au besoin exprimé dans l'intitulé et modifier la requête SQL fournie en conséquence.

Requête N°1 :

Intitulé : Tarif des articles dont le libellé commence par "Masque".

Requête : select gf_tarif, a_libelle from tarif T, article A

where A.a_libelle like "Masque%"

Résultat :

Requête N°2 :

Intitulé : Nombre d'articles par articles composés.

Requête : Select count(*) as nb_articles, ca_artcomp

from compoart

Résultat :



Requête N°3 :

Intitulé : Libellé de l'article ayant le prix d'achat le plus élevé.

Requête : Select a_libelle, max(a_prixachat) from article

Résultat :

6ème partie : Observation des requêtes SQL internes du PGI.

Passage en mode Debug Il existe dans Cegid la possibilité de visualiser les requêtes SQL générées automatiquement par l'outil. Pour cela, il faut paramétrer le logiciel au moyen des manipulations suivantes : Lancez la fenêtre "Paramètres Développeur" en appuyant sur les touches Alt + F11. Dans l'onglet Debug, cochez la case "Se mettre en vision SAV" puis validez.



Ce paramétrage aura pour effet de rajouter dans certains modules un onglet SQL (en lecture seule) permettant de visualiser les requêtes SQL effectuées par l'outil sur la base CEGID pour afficher les données demandées par l'utilisateur. Observation d'une requête en mode SAV Dans le module "Comptabilité", cliquez sur l'item "Interrogation d'un tiers" de l'icône "Consultation

des comptes".

Demandez l'affichage de toutes les écritures concernant le tiers "Aqua Velva" et ce pour tous les exercices.



Vous devez obtenir les lignes suivantes:

Cliquez maintenant sur l'onglet SQL et observez la requête SQL générée par l'outil afin de sélectionner et afficher ce résultat.

Quels mots-clés SQL reconnaissez-vous ?

Combien d'attributs sont renvoyés par cette requête ?

A combien de tables accède cette requête SQL ?

Quels sont les critères de sélection des enregistrements renvoyés par la requête ?

A quoi sert la clause ORDER BY ?



ANNEXE : mémento SQL

Ordre SELECT sans fonction SQL ni regroupement

● SELECT [DISTINCT] colonne1 [AS nomAlias1] [, colonne2 [AS nomAlias2] …] FROM nomTableOuNomVue1 [nomAlias1] [, nomTableOuNomVue2 [nomAlias2] … ] [WHERE conditionDeSélection] [ORDER BY colonne1 [DESC] [, colonne2 [DESC] …] ] - La liste de colonnes située après le mot SELECT peut être remplacée par le symbole "*". Condition de sélection Une condition de sélection (désignée dans ce mémento par "conditionDeSélection") peut être composée d'une ou de plusieurs conditions élémentaires combinées à l’aide des opérateurs logiques NOT, AND et OR, en utilisant éventuellement des parenthèses. Condition élémentaire colonne = valeurOuColonne colonne <> valeurOuColonne

colonne < valeurOuColonne colonne > valeurOuColonne

colonne <= valeurOuColonne colonne >= valeurOuColonne

colonne IS [NOT] NULL colonne LIKE filtre

colonne BETWEEN valeur1 AND valeur2 colonne IN (valeur1, valeur2, …)

colonne IN (ordreSelect) colonne = (ordreSelect)

- "filtre" désigne une chaîne de caractères comportant les symboles "%"et/ou "_". - Les filtres peuvent être utilisés avec une colonne de type chaîne ou date. - Certains SGDBs utilisent "*" et "?" au lieu de "%" et "_" pour l'écriture des filtres.

Ordre SELECT utilisant des fonctions SQL et/ou des regroupements

● SELECT [DISTINCT] colonneOuFonctionSql1 [AS nomAlias1] [, colonneOuFonctionSql2 [AS nomAlias2]… ] FROM nomTableOuNomVue1 [nomAlias1] [, nomTableOuNomVue2 [nomAlias2] … ] [WHERE conditionDeSélection] [GROUP BY colonne1 [, colonne2 …] [HAVING conditionDeSélectionGroupes] ] [ORDER BY colonneOuFonctionSql1 [DESC] [, colonneOuFonctionSql2 [DESC] …] ] - "colonneOuFonctionSql" désigne une colonne ou l’une des fonctions SQL décrites ci-dessous. Fonctions SQL COUNT (*) COUNT (colonne)

SUM (colonne) AVG (colonne)

MAX (colonne) MIN (colonne) Condition de sélection des groupes Une condition de sélection des groupes (désignée dans ce mémento par "conditionDeSélectionGroupes") peut être composée d'une ou de plusieurs conditions élémentaires de sélection de groupes combinées à l’aide des opérateurs logiques NOT, AND et OR, en utilisant éventuellement des parenthèses. Une condition élémentaire de sélection des groupes peut prendre l'une des formes ci-dessous : fonctionSql = valeur fonctionSql <> valeur

fonctionSql < valeur fonctionSql > valeur

fonctionSql <= valeur fonctionSql >= valeur



Correction

1ère partie : Observation de tables de la base "Neworld".

Combien d'attributs composent la table Pays ? 3

Quel attribut permet d'identifier de manière unique un enregistrement de la table ? L'attribut PY_PAYS (Code Pays) qui est d'ailleurs obligatoire.

Les 2 tables ont-elles des attributs en commun ? Oui l'attribut O_PAYS (Code pays du code postal)

Si oui, qu'est ce que cela implique ? Cela permet de définir pour chaque code postal à quel pays il appartient. Cela implique également que plusieurs codes postaux peuvent être affectés à un même pays.

A l'aide de la vue Dictionnaire des tables, sélectionnez la 1ère table de la base Neworld puis validez. Combien de tables contient la base de données Neworld ? 133 tables

2ème partie : Test d'une première requête.

Que donne-t-elle comme résultat ? La liste des codes postaux contenus dans la table CodePost.

3ème partie : Compréhension de requêtes SQL.

Requête N°1 :

Libelle, date_debut et date_fin des exercices dont la date de début est comprise entre le 1/1/03 et le 1/1/04

Requête N°2 : Code postal le plus grand

Requête N°3 :

Libellé du pays dont le code pays commence par la lettre C

Requête N°4 : Listes des articles composés avec pour chacun d'eux les articles les composant.

Requête N°5 :

Libellé et nombre d'utilisateurs des groupes d'utilisateurs

Requête N°6 :

Libellé et nombre d'utilisateurs des groupes d'utilisateurs qui comptent plus de 2 utilisateurs.



4ème partie : Ecriture de requêtes SQL.

Requête N°1 : Nombre total de pays.

Select count(*) as TOTAL from pays

Requête N°2 : Liste des pays (nom abrégé et code bancaire) classés par ordre décroissant.

select py_abrege, py_codebancaire from pays order by 1 desc

Requête N°3 : Libellé du pays dont le code pays est CSK

select py_libelle from pays where py_pays like "CSK"

Requête N°4 : Libellés des pays dont les 2 premières lettres sont CH ou DA

select py_libelle from pays where py_libelle like "CH%" or py_libelle like"DA%"

Requête N°5 : Nom, prénom et ville des commerciaux

select cm_prenom, cm_nom, o_ville from commerc C, codepost CP

where C.cm_codepostal = CP.o_codepostal

5ème partie : Modification de requêtes SQL erronées.

Requête N°1 : il manque la jointure

Requête N°2 : il manque le Group By



Requête N°3 : il manque la sous requête



6ème partie : Observation des requêtes SQL internes du PGI.

Quels mots-clés SQL reconnaissez-vous ? Tous les mots en rouge SELECT, FROM, WHERE ORDER BY

en profiter pour parler de la norme SQL2 avec les jointures naturelles, gauches et droites (NATURAL JOIN, LEFT JOIN, RIGHT JOIN)

Combien d'attributs sont renvoyés par cette requête ? 11

A combien de tables accèdent cette requête SQL ? 2 Tables (Ecriture et Tiers)

Quels sont les critères de sélection des enregistrements renvoyés par la requête ? On sélectionne les enregistrements ayant l'attribut Auxiliaire valorisé et contenant 'CADC01'.

A quoi sert la clause ORDER BY ? A trier les enregistrements résultats (ici on trie en ordre croissant sur les attributs Auxiliaire et N° Pièce).

exploiter données pgi sql - economie-gestion.ac...

Documents