FICHE PÉDAGOGIQUE

Place dans le programme 2. Analyser le marché2.4) Le recueil et l’analyse des informations commerciales

- bases de données commerciales

Objectifs pédagogiques Comprendre l’organisation d’une base relationnelle Exploiter une base relationnelle à partir de requêtes Editer des résultats sous la forme de documents imprimés

Pré – requis Classe de 1ère STG : Information et Gestion la base de données : le langage de requête SQL (restriction, projection, jointure, tri).

Matériels Micro – ordinateurs (logiciel Access) Vidéo projecteur

Organisation de la classe ½ classeTravail individuel.

Durée 2 heures.

Déroulement -Faire lire la totalité du travail aux élèves-Faire réaliser aux élèves les différentes requêtes-Passer dans les rangs afin de guider les élèves-Proposer une correction à la fin de chaque requête à partir du vidéo projecteur.

Documents de travail élèves Mode opératoire sur Access : le SQL Polycopié (9 requêtes à formuler).

Supports professeur Base de données Bijouterie (à installer sur le commun de la classe) Correction des 9 requêtes.

Plan Pour chaque requête :- résultat à obtenir- tableau d’analyse de la requête à compléter- construction de la requête en langage SQL- impression de la requête.-

Evaluation Evaluation sommative :- lors d’un devoir surveillé (informatique commerciale).

b TD : SQL b

Vous allez interroger la base de données de la société BIJOUX spécialisée dans la vente de bijoux or, argent,….dont voici le schéma relationnel   :

Les tables utilisées sont les suivantes :

Le modèle relationnel vous est donné ci-dessous :

CLIENT ( numcli, nomcli,nuecli,villecli,cpcli, numtél)FACTURE (numfac,date fac,numcli#)PRODUIT (refprod,desigprod,prixprod,matière)LIGNE_FACTURE (numfac#,refprod#,quantité)

Vous devez formuler les requêtes suivantes en langage SQL (effectuez le travail préparatoire sur papier puis testez votre travail sur le logiciel Access) :

Requête 1

è RESULTAT A OBTENIRLa liste des clients (nom, ville) par ordre alphabétique

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 1 EN LA NOMMANT R1 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 2

è RESULTAT A OBTENIRLa liste des clients de Clermont (nom, numéro de facture)

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CRITERE CRITERE RESULTAT A

CONCERNEE DE RESTRICTION

TRI

DEJOINTURE

AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 2 EN LA NOMMANT R2 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 3

è RESULTAT A OBTENIRLa liste des articles en or (référence, désignation, prix)

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 3 EN LA NOMMANT R3 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 4

è RESULTAT A OBTENIRLa liste des clients (nom) leurs achats (désignation) et leur numéro de facture et date de facturation

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 4 EN LA NOMMANT R4 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 5

è RESULTAT A OBTENIRLa liste des produits (référence désignation, prix) supérieurs à 50 euros

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 5 EN LA NOMMANT R5 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 6

è RESULTAT A OBTENIRTous les clients (nom, ville) dont la ville commence par la lettre c

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 6 EN LA NOMMANT R6 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 7

è RESULTAT A OBTENIRLa moyenne des prix des articles en vente

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 8 EN LA NOMMANT R8 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 8

è RESULTAT A OBTENIRLe nombre des articles référencés

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 9 EN LA NOMMANT R9 AVEC VOTRE NOM Vérifiez qu’elle tienne sur une page sinon imprimez en paysage

Requête 9

è RESULTAT A OBTENIRÀ vous de choisir mais il faut au moins une jointure :

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

èIMPRIMER LA REQUÊTE 9 EN LA NOMMANT R9 AVEC VOTRE NOM vérifiez qu’elle tient sur une page sinon imprimez en paysage

PROPOSITION DE CORRIGÉ

b TD : SQL b

Vous allez interroger la base de données de la société BIJOUX spécialisée dans la vente de bijoux or, argent,….dont voici le modèle relationnel.

CLIENT ( numcli, nomcli,nuecli,villecli,cpcli, numtél)FACTURE (numfac,date fac,numcli#)PRODUIT (refprod,desigprod,prixprod,matière)LIGNE_FACTURE (numfac#,refprod#,quantité)

Requête 1

è RESULTAT A OBTENIRLa liste des clients (nom, ville) par ordre alphabétique

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

Projection CLIENT Tri croissantnomcli

nomclivillecli

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT nomcli, villecliFROM clientORDER BY nomcli ASC;

Requête 2

è RESULTAT A OBTENIRLa liste des clients de Clermont (nom, numéro de facture)

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

PROJECTIONRESTRICTION

JOINTURE

CLIENTFACTURE

Villecli=clermont numcli nomclinumfac

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT nomcli, numfacFROM client, factureWHERE FACTURE.numcli=numcli.CLIENTAND villecli="clermont";

Requête 3

è RESULTAT A OBTENIRLa liste des articles en or (référence, désignation, prix,matière)

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

PROJECTIONRESTRICTION

PRODUIT matière=or refproddesignprodprixprodmatière

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT refprod, designprod, prixprod, matièreFROM produitWHERE matière="or";

Requête 4

è RESULTAT A OBTENIRLa liste des clients (nom) leurs achats (désignation) et leur numéro de facture et date de facturationèCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

PROJECTION CLIENTPRODUITFACTURE

LIGNE_FACTURE

numclirefprodnumfac

nomclidesigprodnumfacdatefac

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT nomcli.client, desiprod.produit, numfac.facture, datefac.factureFROM produit, ligne_facture, client, factureWHERE lignefacture.numfac=numfac.factureAnd facture.numcli=numcli.clientAnd lignefacture.refprod= refprod.produit ;

Requête 5

è RESULTAT A OBTENIRLa liste des produits (référence désignation, prix) supérieurs à 50 euros

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

PROJECTIONRESTRICTION

Produit Prixprod>50 refproddesigprodprixprod

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT desigprod, prixprod, refprodFROM produitWHERE prixprod>50;

Requête 6

è RESULTAT A OBTENIRTous les clients (nom, ville) dont la ville commence par la lettre c

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

PROJECTIONRESTRICTION

Client villeclient commence par"c"

nomclivillecli

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT nomcli, villecliFROM clientWHERE villecli like "c";

Requête 7

è RESULTAT A OBTENIRLa moyenne des prix des articles en vente

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

PROJECTION Produit moyenne prix moyenne des prix

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT AVG (prixprod) as "moyenne des prix"FROM produit;

Requête 8

è RESULTAT A OBTENIRLe nombre des articles référencés

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

PROJECTION PRODUIT Compte Nombre d'articles référencés

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL

SELECT COUNT (refprod as "nombre d'articles référencés")FROM ligne_facture;

Requête 9

è RESULTAT A OBTENIRÀ vous de choisir mais il faut au moins une jointure :

èCOMPLETER LE TABLEAU D’ANALYSE DE LA REQUETE

OPERATEUR RELATION CONCERNEE

CRITEREDE

RESTRICTIONTRI

CRITEREDE

JOINTURE

RESULTAT A AFFICHER

èCONSTRUISEZ LA REQUETE EN LANGAGE SQL