Université de Valenciennes - LIG Membres

|Université Joseph Fourier |Année Universitaire 2001-2002 |
| |Bases de Données |
TD Requêtage SQL sur Access Concepteur: Didier DONSEZ. Objectifs: Programmer des requêtes sur une Base de Données. Note aux enseignants : La correction est sur ariane dans
~ddonsez/prof/bddeug
Une société de vente de matériel informatique souhaite mettre en place un
système d'information de gestion des commandes et de la facturation. Pour
cela, un premier travail a été la conception d'une base de données
"FactCmd" constituée des tables suivantes : CLIENT (NoClient, Nom, Adresse, Ville, Reduction) FACTURE (NoFact, NoClient, Date, Reduction) COMMANDE (NoFact, NoLigne, RefArt, Qte) ARTICLE (RefArt, Libellé, PU, TVA) PAIEMENT (NoFact, NoPaiement, Paiement)
[pic] Il reste à programmer les requêtes répondant aux questions que se posent
les utilisateurs du système. Ces questions sont : 1- Quels sont les clients ? 2- Quels sont les noms des clients 3- Quels sont les noms des clients parisiens ? 4- Quels sont les noms des clients dont le nom commence par un "D" OU
qui habitent PARIS ? 5- Quels sont les noms des clients dont le nom commence par un "D" ET
qui habitent PARIS ? 6- Listez les numéros de facture pour chaque client. 7- Donnez le nom des clients et les libellés des articles qu'ils ont
commandés. 8- Quels sont les prix TTC des articles ? 9- Donnez le chiffre d'affaire (CA) HT et TTC depuis le 1/2/98. 10- Donnez les quantités commandées par client et par libellé
d'article. 11- Donnez le montant TTC de chaque facture. 12- Donnez la somme des paiements pour chaque facture. 13- Donnez le reste à payer pour chaque facture. Utilisez le résultat
des requêtes 11 et 12. 14- Quels sont les couples de clients qui habitent la même ville ? Dans ce TP, vous programmerez différentes requêtes pour répondre à ces
questions. Pour cela, vous disposez d'un système de gestion de bases de
données (SGBD), Microsoft Access. Pour démarrer votre TD, vous devez procéder aux étapes suivantes : 1- Lancez une session sur Winchester depuis votre terminal X. 2- Recopiez le fichier C:\users\ddonsez\bddeug\factcmd.mbd dans votre
répertoire personnel D:\users\vous\bddeug au moyen du File Manager 3- Lancez l'application Access en double cliquant sur le fichier
factcmd.mbd recopié dans votre répertoire personnel. 4- Sous l'application MS Access : Pour visualiser le schéma de la base, cliquez sur le bouton [pic] de la
barre d'outils. L'onglet "Tables" contient la liste des tables de la base. Pour consulter
le schéma d'une table, double-cliquez sur le bouton "Modifier". Pour
lister les lignes du table, double-cliquez sur le bouton "Ouvrir". [pic] L'onglet "Requêtes" contient la liste des requêtes d'interrogation de la
base. . Pour ajouter une nouvelle requête, double-cliquez sur le bouton
"Nouveau". Pour modifier une requête, double-cliquez sur le bouton
"Modifier". Pour visualiser le résultat d'une requêtes, double-cliquez
sur le bouton "Ouvrir". [pic] La création d'une nouvelle requête commence par l'ajout des tables
nécessaires à la requête, puis par l'ajout des jointures en glissant les
champs de jointure d'une table à l'autre. Ensuite, il faut spécifier les
restrictions sur les champs et leur affichage dans le résultat. [pic] Vous pouvez vérifier la syntaxe SQL de la requête et la modifier en
passant en mode SQL (Menu "Affichage:Mode SQL"). Cependant, une fois
modifiée, une requête n'est plus toujours éditable graphiquement (Menu
"Affichage:Création"). Les expressions et les comparaisons peuvent être éditées au moyen du
générateur d'expression (Menu "Bouton Droit Souris:Générer"). Dans
l'exemple, placez vous sur la case du champ dans lequel vous voulez
mettre une expression (ex : PU*(1+TVA/100)). [pic] Corrigé du TD de Bases de Données Concepteur: Didier DONSEZ. RQ1: SELECT Client.*
FROM Client; RQ2: SELECT Client.Nom
FROM Client;
[pic] [pic] RQ3: SELECT Client.Nom, Client.Ville
FROM Client
WHERE (((Client.Ville)="PARIS"));
RQ4: SELECT Client.Nom, Client.Ville
FROM Client
WHERE (((Client.Nom) Like "D*")) OR (((Client.Ville)="PARIS"));
[pic] [pic] RQ5: SELECT Client.Nom, Client.Ville
FROM Client
WHERE (((Client.Nom) Like "D*") AND ((Client.Ville)="PARIS"));
RQ6: SELECT Facture.NoFact, Client.Nom
FROM Client INNER JOIN Facture ON Client.NoClient = Facture.NoClient; SELECT Facture.NoFact, Client.Nom
FROM Client, Facture
WHERE Client.NoClient = Facture.NoClient;
[pic] [pic]
RQ7: SELECT Client.Nom, Article.Libellé
FROM (Client INNER JOIN Facture ON Client.NoClient = Facture.NoClient)
INNER JOIN (Article INNER JOIN Commande ON Article.RefArt =
Commande.RefArt) ON Facture.NoFact = Commande.NoFact
ORDER BY Client.Nom; SELECT Client.Nom, Article.Libellé
FROM Client,Facture,Article,Commande
WHERE Client.NoClient = Facture.NoClient
AND Facture.NoFact = Commande.NoFact
AND Article.RefArt = Commande.RefArt
ORDER BY Client.Nom;
[pic] RQ8: SELECT Article.Libellé, [Article]![PU]*(1+[Article]![TVA]/100) AS PUTTC
FROM Article;
[pic] RQ9: SELECT Sum(Commande.Qté*Article.PU) AS CAHT
FROM Facture, Article, Commande
WHERE Article.RefArt = Commande.RefArt AND Facture.NoFact = Commande.NoFact
AND ((Facture.Date>1/2/98));
RQ10: SELECT Client.Nom, Article.Libellé, Sum(Commande.Qté) AS SommeDeQté
FROM (Client INNER JOIN Facture ON Client.NoClient = Facture.NoClient)
INNER JOIN (Article INNER JOIN Commande ON Article.RefArt =
Commande.RefArt) ON Facture.NoFact = Commande.NoFact
GROUP BY Client.Nom, Article.Libellé; SELECT Client.Nom, Article.Libellé, Sum(Commande.Qté) AS SommeDeQté
FROM Client, Facture, Article,Commande
WHERE Client.NoClient = Facture.NoClient
AND Facture.NoFact = Commande.NoFact
AND Article.RefArt = Commande.RefArt
GROUP BY Client.Nom, Article.Libellé;
[pic] RQ11: SELECT Commande.NoFact,
Sum([Article]![PU]*(1+[Article]![TVA]/100)*[Commande]![Qté]) AS
MontantTotal
FROM Article INNER JOIN Commande ON Article.RefArt = Commande.RefArt
GROUP BY Commande.NoFact; SELECT Commande.NoFact,
Sum([Article]![PU]*(1+[Article]![TVA]/100)*[Commande]![Qté]) AS
MontantTotal
FROM Article,Commande
WHERE Article.RefArt = Commande.RefArt
GROUP BY Commande.NoFact;
[pic]
RQ12 : SELECT Paiement.NoFact, Sum(Paiement.Paiement) AS SommeDePaiement
FROM Paiement
GROUP BY Paiement.NoFact;
[pic] RQ13: SELECT Paiement.NoFact, Sum(Paiement.Paiement) AS SommeDePaiement
FROM Paiement
GROUP BY Paiement.NoFact; RQ13:
SELECT RQ11.NoFact, [RQ11]![MontantTotal]-[RQ12]![SommeDePaiement] AS
Reste_a_Payer
FROM RQ11 INNER JOIN RQ12 ON RQ11.NoFact = RQ12.NoFact; SELECT RQ11.NoFact, [RQ11]![MontantTotal]-[RQ12]![SommeDePaiement] AS
Reste_a_Payer
FROM RQ11, RQ12
WHERE RQ11.NoFact = RQ12.NoFact;
[pic] RQ14: SELECT Client.Nom, Client_1.Nom, Client.Ville
FROM Client AS Client_1 INNER JOIN Client ON Client_1.Ville = Client.Ville
WHERE (((Client.Nom)>[Client_1].[Nom]))
ORDER BY Client.Nom, Client_1.Nom; SELECT Client.Nom, Client_1.Nom, Client.Ville
FROM Client AS Client_1,Client
WHERE (((Client.Nom)>[Client_1].[Nom])) AND Client_1.Ville = Client.Ville
ORDER BY Client.Nom, Client_1.Nom;
[pic]