SQL : LID, fonctions

1 Rappel important

Toute requête renvoie une table comportant un ou plusieurs attribut et zéro ou plusieurs enregistrements.

Dans le cas où la requête renvoie un seul attribut et un seul enregistrement, le contenu une valeur qui peut être utilisée telle que dans une expression d'une autre requête.

La requête suivante ...

select * from facture
where facNoFou=(

select fourNo from Fourniseur where fourNom="toto"

) ;

... est divisible en plusieurs sous-parties.

a) select * from facture where facNoFou=(QUELQUE CHOSE);

et

b) select fourNo from Fourniseur where fourNom="toto" ;

La partie b) donne une table résultat ...

select fourNo from Fourniseur where fourNom="toto" ;

<fourNo>	<fourNom>	suite des champs
F001	premier fournisseur	...
F003	3ème Fournisseur	...
F023	toto	...
F045	je suis le fournisseur 45	...
F123	1,2,Troy	...

soit la table résultat :

F023

(c'est une valeur)

... qui est substituée (remplace et utilisée) dans la partie a) ...

select * from facture where facNoFou=("F023");

<facNo>	<facDate>	<facMontant>	<facMontantRegle>	<facNoFou>
00000001	19/01/05	15000 €	15000 €	F023
00000002	19/01/05	12500 €	12500 €	F001
00000003	20/01/05	1000 €	0 €	F023
00000004	21/01/05	22250 €	22250 €	F123
00000005	01/02/05	14000 €	10000 €	F001

... pour donner le résultat ci-dessous :

<facNo>	<facDate>	<facMontant>	<facMontantRegle>	<facNoFou>
00000001	19/01/05	15000 €	15000 €	F023
00000003	20/01/05	1000 €	0 €	F023

Dans le cas où une requête renvoie une seule colonne, le contenu peut être utilisé comme une liste pour l'opérateur IN comme suit :

select * from fournisseur where fourNo IN (

select fourNo from facture where facMontant>facMontantRegle

) ;

La partie en jaune donne le résultat suivant :

<facNo>	<facDate>	<facMontant>	<facMontantRegle>	<facNoFou>
00000001	19/01/05	15000 €	15000 €	F023
00000002	19/01/05	12500 €	12500 €	F001
00000003	20/01/05	1000 €	0 €	F023
00000004	21/01/05	22250 €	22250 €	F123
00000005	01/02/05	14000 €	10000 €	F001

soit la table liste :

F023,F001

Qui est réutilisé dans la partie bleue comme suit :

select * from fournisseur where fourNo IN (

F023,F001

) ;

Le résultat final est :

<fourNo>	<fourNom>	suite des champs
F001	premier fournisseur	...
F023	toto	...

Par ailleurs, il existe des clauses ensemblistes qui permettent d'assembler, soustraire ou faire l'intersection de deux requêtes :

UNION : union de deux requêtes.
INTERSECT : intersection, ne sont affichées que les lignes présentes dans les deux requêtes.
MINUS : différence, le résultat sont les lignes de la première requête dont on a supprimé celles qui sont aussi dans la seonde.

Note : les résultats de chaque requête doivent avoir la même forme (mêmes type et nombre d'attributs).

Note 2 : les clauses Intersect et minus ne sont pas utilisables dans ms-Access (yek, yek, yek!), il faut faire autrement (cf exo sur les opérateurs ensemblistes).

Retour index

2 Les expressions entre attributs

Lors de requêtes, il peut être nécessaire d'effectuer des calculs sur les attributs.

Exemple :
Calcul de prix TTC.
Soit un catalogue, donnez requête SQL qui affiche la liste des tarifs HT, le montant de la TVA et le prix TTC des articles.

a) taux fixe : TVA=19,6%
Article(codeA, descA, prixHT)

b) Le taux de TVA dépend du type de taux de l'article.
Article(codeA, descA, prixHT, typeTVA)
TauxTaxe(codeT, tauxTaxe)

Il est possible d'utiliser tous les opérateurs mathématiques courants (+, -, *, /) entre des attribut et des valeurs ou d'autres attributs.

Retour index

3 Les fonctions d'agrégat d'enregistrements en SQL

Dans certains cas, nous désirons faire apparaître des totaux ou autres calculs sur des valeurs.
Pour cela, on utilise des fonctions (dites d'agrégat car elles regroupent - agrègent- plusieurs lignes).

Ces fonctions sont à utiliser conjointement à la clause GROUP BY

Certaines fonctions ne sont utilisables que sur des attributs numériques (sinon on obtient des résultats inattendus ou une erreur).

3.1 Le comptage : count(*)

Il sert à compter le nombre d'enregistrements.

Exemple :
Rédigez la requête permettant de compter le nombre d'articles ;
Le MR est composé de la relation :

Article(codeA, descA, prixHT, …)

3.2 Les fonctions courantes : avg(a), sum(a)

Elles servent à calculer, respectivement, la moyenne et la somme d'un attribut d'une table

Exemple :
Calculer la somme des factures du fournisseur 10 ainsi que le montant moyen des factures ;
Le MR est composé de la relation :

Facture(facNo, facNoFou, facMontantHT, facTVA, …)

3.3 Les fonctions spécialisées : max(a), min(a)

Renvoient le(s) enregistrement(s) ayant pour valeur de l'attribut a la valeur maximum/minimum de l'ensemble des enregistrements.

Exemple :
Le MR est composé de la relation :

Facture(facNo, facDate, facMontantHT, facTVA, facNoFou, …)

Donnez les numéros des dernières factures reçues.
Donnez les numéros et montants de la première et de la dernière facture du fournisseur 10

3.4 La clause having

Cette clause est très utile lorsqu'on veut effectuer une sélection des lignes dans la table résultat de la requête.

Exemple :
Rédigez la requête qui affiche l'article "AZ345" sans utiliser la clause Where.

Donnez la liste des catégories de TVA qui comportent plus de 10 articles.
On effectue la requête de comptage et on ajoute une clause Having pour éliminer les lignes qui ne correspondent pas à la demande.

Donnez la liste des clients qui ont passé au moins une commande de plus se 5000€

Client(cliNo, …)

Clé primaire : cliNo

Commande(cdNo, … , cliNo)

Clé primaire : cdeNo
Clé étrangère : cliNo référence cliNo dans Client

LigneCdeArticle(cdeNo, artCode, cdePVenteHT, cdeQté)

Clé primaire : cdeNo, artCode
Clé étrangère : cdeNo référence cdeNo dans Commande
Clé étrangère : artCode référence artCode dans Article

Retour index

Annexe 1 Solutions des exercices ou exemples

Chapitre 2

Calcul du prix TTC : prixTTC=prixHT + TVA = prixHT + prixHT*TauxTVA/100 = prixHT( 1+tauxTVA/100)
a) taux fixe : TVA=19,6%

Select codeA, descA, prixHT, prixHT*0,196 as TVA, prixHT*(1+0,196) as prixTTC from Article ;

b) Le taux de TVA dépend du type de taux de l'article.

Select codeA, descA, prixHT, prixHT*tauxTaxe/100 as TVA, prixHT*(1+tauxTaxe/100) as prixTTC
from Article, TauxTaxe
Where typeTVA=codeT ;

Chapitre 3.1

Rédigez la requête permettant de compter le nombre d'articles ;

Select count(*) from Article ;

Chapitre 3.2

Calculer la somme des factures du fournisseur 10 ainsi que le montant moyen des factures ;

Select sum(facMontantHT*facTVA), avgfacMontantHT*facTVA) from Facture ;

Chapitre 3.3

Donnez les numéros des dernières factures reçues.

Select facNo, max(facDate) from Facture ;

Donnez les numéros et montants de la première et de la dernière facture du fournisseur 10

Select facNo facMontantHT
From Facture
Where facNo in (

Select max(facNo) from Facture where facNoFou=10 // la plus grande
union
Select min(facNo) from Facture where facNoFou=10 // la plus petite

) ;

Chapitre 3.4

Rédigez la requête qui affiche l'article "AZ345" sans utiliser la clause Where.

select * from Article
having artCode="AZ345" ;

C'est l'équivalent de :

select * from Article
where artCode="AZ345" ;

Donnez la liste des catégories de TVA qui comportent plus de 10 articles.
On effectue la requête de comptage et on ajoute une clause Having pour éliminer les lignes qui ne correspondent pas à la demande.

select Categorie.catnum, catDesc, catTaux from Categorie
where Categorie.catNum=Article.catNum
group by Categorie.catNum, catDesc
having count(*) > 10 ;

Donnez la liste des clients qui ont passé au moins une commande de plus se 5000€

a) Calcul du montant des commandes

select cdeNum, sum(cdePVenteHT*cdeQté) as cdeMontant
from LigneCdeArticle
group by cdeNum
;

Restriction des commandes à celles dont le montant est supérieur à 5000€

select cdeNum, sum(cdePVenteHT*cdeQté) as cdeMontant
from LigneCdeArticle
group by cdeNum
having cdeMontant > 5000
;

ou mieux, sans afficher le montant de la commande :

select cdeNum
from LigneCdeArticle
group by cdeNum
having sum(cdePVenteHT*cdeQté) > 5000
;

Note : les éléments ou lignes qui sont issues de la requête ci-dessus sont surlignées dans la suite de l'explication.

b) Recherche des clients
1ère méthode
Attention : on obtient les n° et noms du client, mais aussi le n° de cde.
De plus, il peut y avoir des clients qui auraient passé plusieurs commandes >5000€ et on les verrai affichés plusieurs fois.

select Client.cliNo, cliNom, cdeNum
from LigneCdeArticle, Commande, Client
where Client.cliNo=Commande.cliNo
and Commande.cdeNum=LigneCdeArticle.cdeNum
group by Client.cliNo, cliNom, cdeNum
having sum(cdePVenteHT*cdeQté) > 5000
;

2ème méthode
Cette méthode est plus propre car nous n'affichons plus le n° de commande (et un client sélectionné n'est affiché qu'une seule fois).
La requête initiale produit une table de n° de commandes.
On va rechercher les clients qui ont passé ces commandes :
a) la requête initiale est ajoutée dans la clause from

select distinct Client.cliNo, cliNom
from Client, Commande, (

select cdeNum as cdeNum_recherchee
from LigneCdeArticle
group by cdeNum
having sum(cdePVenteHT*cdeQté) > 5000

)
where Client.cliNo=Commande.cliNo
and Commande.cdeNum=cdeNum_rechechee
;

b) la requête initiale est utilisée dans la clause where, avec l'opérateur in, un peu moins compliqué ...

select distinct Client.cliNo, cliNom
from Client, Commande
where Client.cliNo=Commande.cliNo
and Commande.cdeNum in (

select cdeNum
from LigneCdeArticle
group by cdeNum
having sum(cdePVenteHT*cdeQté) > 5000

)
;

Retour index