Les fonctions en SQL

DOSSIER G11 – Interroger une base de données
Problématique
•
•
•
Chaque traitement (la facturation, la paie, la gestion des stocks dans une entreprise, la gestion des bulletins scolaires
dans un lycée, etc.) mobilise beaucoup d’informations.
Si l’on veut informatiser un traitement, il faut, au préalable, structurer toutes ces informations sur le disque
d’un ordinateur de manière extrêmement rigoureuse.
Nous allons étudier les principes du modèle relationnel qui vont permettre d’atteindre cet objectif.
Activité 1 : Une vue d’ensemble de la base de données « Facturation »
Voici comment se présente, sous Access (logiciel de gestion de base de données relationnels), le schéma relationnel
de la base de données étudiée au préalable.
Modèle Relationnel des Données
Table 1 : PRODUITS (Reference du produit, Designation du produit, Prix unitaire du produit)
Clé primaire :
Reference du produit
Clé étrangère :
Table 2 : LIGNE_FACTURES (Reference du produit, N°_facture, Quantite)
Clé primaire :
Reference du produit, N°_facture
Clé étrangère :
Reference du produit en référence à Reference du produit de PRODUITS
N°_facture en référence à N°_facture de FACTURES
Table 3 : FACTURES (N°_facture, Date de la facture, Payée, Code de la categorie, N° client dans la categorie)
Clé primaire :
N°_facture
Clé étrangère :
Code de la categorie + N° client dans la categorie en référence
à Code de la categorie + N° client dans la categorie de CLIENTS
Table 4 : CLIENTS (Code de la categorie, N°_client dans la categorie, Nom de l’entreprise, Adresse, Adresse Suite,
Code postal, Montant des achats, Suivi par le représentant n°, Téléphone, E-mail)
Clé primaire :
Code de la categorie + N° client dans la categorie
Clé étrangère :
Code postal en référence à Code postal de VILLES
Table 5 : VILLES (Code postal, Nom de la ville)
Clé primaire :
Code postal
Clé étrangère :
1.
Que représente chaque table ?
Première STG – Dossier G11 – Interroger une base de données
Page 1 sur 11
2.
Que représentent les clés primaires ?
3.
Donnez la signification de la clé étrangère.
4.
Comment interprétez-vous le chiffre 1 et le symbole mathématique de l’infini ∞,
qui signifie « plusieurs ».
5.
Pourquoi la fenêtre qui contient le schéma de la base de données s’appelle « Relations ».
6.
Chacun des quatre traitements ci-après évoque une opération sur la table CLIENTS.
Pourriez-vous lui donner un nom ?
Affichage de la liste des clients :
Enregistrement d’un client :
Changer l’adresse d’un client :
Retirer toutes les information d’un client :
Première STG – Dossier G11 – Interroger une base de données
Page 2 sur 11
Activité 2 :
Quelques opérations sur la base de données « Facturation »
Voici les extraits de la table CLIENTS et de la table VILLES de la SAFPB (société anonyme de fabrication de produits
de boulangerie).
Tableau 1 : CLIENTS
Code de N° client
la
dans la
catégorie catégorie
Nom de
l’entreprise
Suivi par le Montant des
Code
représentant
achats
postal
n°
Adresse
suite
Adresse
BOU
001
Anis & Miel
7 chemin de
Cassis
13010
R2
3 584,00 €
BOU
002
Les moulins
de Pvce
74 bd R. Rolland
13010
R4
2 458,00 €
EPI
001
Bataille
17 rue des Trois
Rois
13006
R2
18 245,00 €
HYP
001
Auchan
485 bd des
Bartavelles
ZI Les
Paluds
13400
R1
105 147,00 €
HYP
002
Casino
47 route de
Cuques
La
Valentine
13011
R1
250 475,00 €
HYP
003
Leclerc
5 route de
Cabriès
Plan de
campagne
13480
R4
85 560,00 €
PAT
001
Les Moulins
de Pvce
13400
R3
5 780,00 €
SUP
001
13456
R1
1 854,00 €
SUP
002
13010
R4
15 784,00 €
45 av. M. Pagnol
Intermarché 30 rue Pierre
Casino
145 bd P. Claudel
Tableau 2 : VILLES
Code postal
Nom de la ville
13010
Marseille
13011
Marseille
13400
Aubagne
13456
Mallemort
13480
Cabriès
Voici, sous la forme d’une table, la liste des clients avec leur numéro (Code de la catégorie et N° client dans la catégorie),
le nom de l’entreprise et le montant de leurs achats (obtenu par le biais d’une requête).
Code
N° client dans
de la catégorie la catégorie
BOU
001
BOU
002
EPI
001
HYP
001
HYP
002
HYP
003
PAT
001
SUP
001
SUP
002
7.
Nom
de l’entreprise
Anis et Miel
Les Moulins de Pvce
Bataille
Auchan
Casino
Leclerc
Les Moulins de Pvce
Intermarché
Casino
Montant
des achats
3 584,00 €
2 458,00 €
18 245,00 €
105 147,00 €
250 475,00 €
85 560,00 €
5 780,00 €
1 854,00 €
15 784,00 €
Par rapport à la table CLIENTS, cette opération concerne-t-elle les lignes ou les colonnes ?
Première STG – Dossier G11 – Interroger une base de données
Page 3 sur 11
8.
Par rapport à la requête obtenue précédemment, quelle modification a été apportée ci-dessous ?
Code
de la catégorie
BOU
HYP
EPI
HYP
SUP
SUP
HYP
BOU
PAT
N° client dans la
catégorie
001
001
001
002
002
001
003
002
001
Nom
de l’entreprise
Anis et Miel
Auchan
Bataille
Casino
Casino
Intermarché
Leclerc
Les Moulins de Pvce
Les Moulins de Pvce
Montant
des achats
3 584,00 €
105 147,00 €
18 245,00 €
250 475,00 €
15 784,00 €
1 854,00 €
85 560,00 €
2 458,00 €
5 780,00 €
Voici, sous la forme d’une table, une deuxième requête
Code
N° client dans la
de la catégorie
catégorie
BOU
001
SUP
002
PAT
001
9.
Nom
de l’entreprise
Anis et Miel
Casino
Les Moulins de Pvce
Montant
des achats
3 584,00 €
15 784,00 €
5 780,00 €
Nom de la ville
Marseille Centre
Marseille Centre
Marseille Centre
Pourquoi tous les clients ne sont-ils pas présents ?
10. Quelle est donc cette nouvelle opération ? En quoi consiste-t-elle ?
11. Quelles tables ont été consultées pour obtenir cette requête ?
12. Comment faites-vous le lien (la relation) entre elles ?
Première STG – Dossier G11 – Interroger une base de données
Page 4 sur 11
Activité 3 :
Parlons un peu SQL
Sur la norme du langage SQL, on vous communique les informations suivantes :
Select traduit la projection et indique, à sa suite, les champs qui doivent être affichés ;
From indique à quelle table appartiennent les champs ;
Les champs sont séparés par une virgule ;
Pour éviter toute erreur lors de l’exécution de la requête par le SGBDR, lorsque le nom d’un champ comprend
des espaces, on doit l’encadrer par des crochets.
Requête SQL :
SELECT CLIENTS.[Code de la catégorie], CLIENTS.[N° client dans la catégorie],
CLIENTS.[Nom de l’entreprise], CLIENTS.[Montant des achats]
FROM CLIENTS;
13. Que fait cette requête ?
Première STG – Dossier G11 – Interroger une base de données
Page 5 sur 11
Requête SQL :
SELECT DISTINCT CLIENTS.[Code de la catégorie]
FROM CLIENTS;
14. Que fait cette requête, sachant que le mot « Distinct » élimine les répétitions d’une même valeur
(les doublons) ?
Sur la norme du langage SQL, il est possible de trier les données, dans l’ordre croissant (alphabétique) ou non.
Requête SQL :
SELECT CLIENTS.[Code de la catégorie], CLIENTS.[N° client dans la catégorie],
CLIENTS.[Nom de l’entreprise], CLIENTS.[Montant des achats]
FROM CLIENTS
ORDER BY CLIENTS.[Nom de l’entreprise] ASC;
15. Quelle est la clause qui permet de trier les données ?
Sur la norme du langage SQL, il est possible d’extraire certaines données en fonction d’un critère de sélection bien
précis.
Requête SQL :
SELECT CLIENTS.[Code de la catégorie], CLIENTS.[N° client dans la catégorie],
CLIENTS.[Nom de l’entreprise], CLIENTS.[Montant des achats]
FROM CLIENTS
WHERE CLIENTS.[Code de la catégorie] = « BOU »
ORDER BY CLIENTS.[Nom de l’entreprise] ASC;
16. Quelle est la clause qui permet d’extraire certaines données ?
Première STG – Dossier G11 – Interroger une base de données
Page 6 sur 11
Il vous est demandé d’établir la liste des clients
(par ordre alphabétique) qui ont acheté pour moins de 20 000 €
(leur identifiant, leur nom et le montant des achats correspondant).
Application :
Table 1 : PRODUITS (Reference du produit, Designation du produit, Prix unitaire du produit)
Clé primaire :
Reference du produit
Clé étrangère :
Table 2 : LIGNE_FACTURES (Reference du produit, N°_facture, Quantite)
Clé primaire :
Reference du produit, N°_facture
Clé étrangère :
Reference du produit en référence à Reference du produit de PRODUITS
N°_facture en référence à N°_facture de FACTURES
Table 3 : FACTURES (N°_facture, Date de la facture, Payée, Code de la categorie, N° client dans la categorie)
Clé primaire :
N°_facture
Clé étrangère :
Code de la categorie + N° client dans la categorie en référence
à Code de la categorie + N° client dans la categorie de CLIENTS
Table 4 : CLIENTS (Code de la categorie, N°_client dans la categorie, Nom de l’entreprise, Adresse, Adresse Suite,
Code postal, Montant des achats, Suivi par le représentant n°, Téléphone, E-mail)
Clé primaire :
Code de la categorie + N° client dans la categorie
Clé étrangère :
Code postal en référence à Code postal de VILLES
Table 5 : VILLES (Code postal, Nom de la ville)
Clé primaire :
Code postal
Clé étrangère :
17. Quelle est la table concernée par cette requête ?
18. Quels sont les champs nécessaires pour cette requête ?
19. Quel champ peut être concerné pour trier les clients dans l’ordre alphabétique ?
20. Sur quel champ le critère de restriction va-t-il être posé ?
21. Écrivez cette requête sous la forme SQL.
22. Quel est le critère qui détermine la fin d’une requête ?
Première STG – Dossier G11 – Interroger une base de données
Page 7 sur 11
Activité 4 :
Le lien entre les différentes tables
Il vous est demandé de présenter la liste des clients avec leur identité, le montant des achats et leur taux de remise, dans
l’ordre alphabétique du nom de l’entreprise du client.
Table 1 : PRODUITS (Reference du produit, Designation du produit, Prix unitaire du produit)
Clé primaire :
Reference du produit
Clé étrangère :
Table 2 : LIGNE_FACTURES (Reference du produit, N°_facture, Quantite)
Clé primaire :
Reference du produit, N°_facture
Clé étrangère :
Reference du produit en référence à Reference du produit de PRODUITS
N°_facture en référence à N°_facture de FACTURES
Table 3 : FACTURES (N°_facture, Date de la facture, Payée, Code de la categorie, N° client dans la categorie)
Clé primaire :
N°_facture
Clé étrangère :
Code de la categorie + N° client dans la categorie en référence
à Code de la categorie + N° client dans la categorie de CLIENTS
Table 4 : CLIENTS (Code de la categorie, N°_client dans la categorie, Nom de l’entreprise, Adresse, Adresse
Suite, Code postal, Montant des achats, Suivi par le représentant n°, Téléphone, E-mail)
Clé primaire :
Code de la categorie + N° client dans la categorie
Clé étrangère :
Code postal en référence à Code postal de VILLES
Table 5 : VILLES (Code postal, Nom de la ville)
Clé primaire :
Code postal
Clé étrangère :
23. Quelles sont les tables concernées par cette requête ?
Sur la norme du langage SQL, il est possible d’extraire certaines données présentes sur plusieurs tables.
Requête SQL :
SELECT CLIENTS.[Code de la catégorie], CLIENTS.[N° client dans la catégorie],
CLIENTS.[Nom de l’entreprise], CLIENTS.[Montant des achats]
CATEGORIE.[taux de remise]
FROM CLIENTS, CATEGORIE
WHERE CLIENTS.[Code de la catégorie] = CATEGORIE.[Code de la catégorie]
ORDER BY CLIENTS.[Nom de l’entreprise] ASC;
24. Quel est le critère qui permet de relier (jointure) les deux tables nécessaires à cette requête ?
Première STG – Dossier G11 – Interroger une base de données
Page 8 sur 11
Il vous est demandé d’établir la liste des clients,
par ordre alphabétique (afficher le nom des clients et leur adresse complète).
Application :
Table 1 : PRODUITS (Reference du produit, Designation du produit, Prix unitaire du produit)
Clé primaire :
Reference du produit
Clé étrangère :
Table 2 : LIGNE_FACTURES (Reference du produit, N°_facture, Quantite)
Clé primaire :
Reference du produit, N°_facture
Clé étrangère :
Reference du produit en référence à Reference du produit de PRODUITS
N°_facture en référence à N°_facture de FACTURES
Table 3 : FACTURES (N°_facture, Date de la facture, Payée, Code de la categorie, N° client dans la categorie)
Clé primaire :
N°_facture
Clé étrangère :
Code de la categorie + N° client dans la categorie en référence
à Code de la categorie + N° client dans la categorie de CLIENTS
Table 4 : CLIENTS (Code de la categorie, N°_client dans la categorie, Nom de l’entreprise, Adresse, Adresse
Suite, Code postal, Montant des achats, Suivi par le représentant n°, Téléphone, E-mail)
Clé primaire :
Code de la categorie + N° client dans la categorie
Clé étrangère :
Code postal en référence à Code postal de VILLES
Table 5 : VILLES (Code postal, Nom de la ville)
Clé primaire :
Code postal
Clé étrangère :
25. Quelles sont les tables concernées par cette requête ?
26. Quels sont les champs nécessaires pour cette requête ?
27. Quel champ peut être concerné pour trier les clients dans l’ordre alphabétique ?
28. Quel est le champ qui permet de relier (jointure) les deux tables ?
29. Écrivez cette requête sous la forme SQL.
Première STG – Dossier G11 – Interroger une base de données
Page 9 sur 11
Activité 5 : La réalisation de calculs et l’utilisation des fonctions
Les calculs en SQL
SELECT PRODUITS.[Prix unitaire produit]*0,95 AS Net_commercial
FROM PRODUITS ;
Remarque : le mot-clé « AS » permet de renommer le champ qui contient le résultat.
SELECT PRODUITS.[Prix unitaire produit - PRODUIT.Net_commercial
FROM PRODUITS ;
30. Quels types de calcul peut-on réaliser avec le SQL ?
Les fonctions en SQL
SELECT SUM(FACTURES.Quantite) AS Quantite_totale
FROM FACTURES
WHERE FACTURES.Num_facture=«2» ;
31. Expliquer le résultat obtenu.
32. En déduire la fonction SUM.
SELECT COUNT(FACTURES.Quantite) AS Nombre_de_lignes
FROM FACTURES
WHERE FACTURES.Num_facture=«2» ;
33. Expliquer le résultat obtenu.
34. En déduire la fonction COUNT.
35. Quelle est la syntaxe permettant d’utiliser une fonction.
Première STG – Dossier G11 – Interroger une base de données
Page 10 sur 11
L’interrogation du système d’information_
Schématiser
THÈME 2. Le système d'information et les bases de données
© Hachette Livre - INFORMATION ET GESTION - 1RE STG - Spécialité Gestion -
Première STG – Dossier G11 – Interroger une base de données
Page 11 sur 11