forked from fxjollois/fxjollois.github.io
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinterrogation-sql-exemple.html
415 lines (366 loc) · 15.5 KB
/
interrogation-sql-exemple.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="generator" content="pandoc" />
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Exemple de requêtes - SQL</title>
<script src="libs/jquery-1.11.3/jquery.min.js"></script>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link href="libs/bootstrap-3.3.5/css/journal.min.css" rel="stylesheet" />
<script src="libs/bootstrap-3.3.5/js/bootstrap.min.js"></script>
<script src="libs/bootstrap-3.3.5/shim/html5shiv.min.js"></script>
<script src="libs/bootstrap-3.3.5/shim/respond.min.js"></script>
<style>
.contenu {
margin-bottom: 50px;
}
.contact-liens {
text-align: center;
}
.contact-icones {
height: 30px;
}
/* Espacement pour barre du haut et pied de page */
#header, .section.level1 {
margin-top: 60px;
margin-bottom: 60px;
}
/* Espacement pour table des matières */
#TOC {
margin-top: 100px;
}
.footer {
position: fixed;
width: 100%;
text-align: center;
bottom: 0;
left: 0;
background-color: #E6E6E6;
}
</style>
<style type="text/css">code{white-space: pre;}</style>
<link rel="stylesheet"
href="libs/highlight/default.css"
type="text/css" />
<script src="libs/highlight/highlight.js"></script>
<style type="text/css">
pre:not([class]) {
background-color: white;
}
</style>
<script type="text/javascript">
if (window.hljs && document.readyState && document.readyState === "complete") {
window.setTimeout(function() {
hljs.initHighlighting();
}, 0);
}
</script>
<link rel="stylesheet" href="libs/bootstrap-journal.min.css" type="text/css" />
</head>
<body>
<style type = "text/css">
.main-container {
max-width: 940px;
margin-left: auto;
margin-right: auto;
}
code {
color: inherit;
background-color: rgba(0, 0, 0, 0.04);
}
img {
max-width:100%;
height: auto;
}
h1 {
font-size: 34px;
}
h1.title {
font-size: 38px;
}
h2 {
font-size: 30px;
}
h3 {
font-size: 24px;
}
h4 {
font-size: 18px;
}
h5 {
font-size: 16px;
}
h6 {
font-size: 12px;
}
.tabbed-pane {
padding-top: 12px;
}
button.code-folding-btn:focus {
outline: none;
}
</style>
<div class="container-fluid main-container">
<!-- tabsets -->
<script src="libs/navigation-1.0/tabsets.js"></script>
<script>
$(document).ready(function () {
window.buildTabsets("TOC");
});
</script>
<!-- code folding -->
<div class="navbar navbar-default navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#navbar-main">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="index.html">FX Jollois</a>
</div>
<div id="navbar-main" class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button">Données <span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="accesdonnees.html">A télécharger</a></li>
<li><a href="donnees-integrees-r.html">Sous R</a></li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button">Enseignement <span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="enseignement.html">Informations</a></li>
<li><a href="supports.html">Supports</a></li>
<li role="separator" class="divider"></li>
<li class="dropdown-header">DUT 1ère année</li>
<li><a href="interrogation-donnees.html">Interrogation de données</a></li>
<li><a href="reporting.html">Reporting</a></li>
<li class="dropdown-header">DUT 2ème année</li>
<li><a href="sid.html">SID</a></li>
<li><a href="bd-prog-avancees.html">BD et Programmation avancées</a></li>
<li class="dropdown-header">DU Analyste Big Data</li>
<li><a href="initiation-r-et-rmongodb.html">Intiation à R</a></li>
<li class="dropdown-header">DU Dataviz</li>
<li><a href="visualisation-donnees.html">Visualisation de données</a></li>
<li role="separator" class="divider"></li>
<li class="dropdown-header">Master MIAGE</li>
<li><a href="webreporting.html">Web-Reporting</a></li>
<li class="dropdown-header">Master</li>
<li><a href="logiciels-stats.html">Logiciels statistiques</a></li>
<li><a href="slides/cnam-classif-modeles.html">Classification et Modèles de mélange</a></li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button">Recherche <span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="recherche.html">Sujets</a></li>
<li><a href="publications.html">Publications</a></li>
</ul>
</li>
</ul>
<ul class="nav navbar-nav navbar-right">
<li><a href="contact.html">Contact</a></li>
</ul>
</div><!--/.nav-collapse -->
</div><!--/.container-fluid -->
</div>
<div class="contenu">
<div class="fluid-row" id="header">
<h1 class="title">Exemple de requêtes - SQL</h1>
</div>
<p>Cette page vous permet de voir en application l’interrogation de données à l’aide du langage <em>SQL</em></p>
<div id="quelques-exemples-pour-peut-etre-mieux-comprendre" class="section level2">
<h2>Quelques exemples pour (peut-être ?) mieux comprendre</h2>
<p>On va travailler avec les tables suivantes :</p>
<pre><code>CLIENT(NumClt, Nom, Prenom, Adresse)
TVA(NumTx,Valeur)
PRODUIT(NumPrd, Designation, PrixHT, QteStock, #TxTVA)
FACTURE(NumFac, #NumClt, Total)
ACHAT(#NumFac, #NumPrd, Qte)</code></pre>
<p>Ici, chaque table a une clé primaire simple, qui est le premier attribut (par exemple, <code>NumClt</code>pour la table <code>CLIENT</code>). Les <code>#</code> permettent d’indiquer les clés externes (ou références à une autre table).</p>
<p>Dans la suite, vous verrez des questions qu’on se pose et comment on peut les résoudre à l’aide de SQL</p>
<div id="nom-et-prenom-des-clients." class="section level3">
<h3>Nom et prénom des clients.</h3>
<p>Ici, nous ne devons faire qu’une <strong>projection</strong> (sélection des colonnes à afficher).</p>
<pre><code>SELECT Nom, Prenom
FROM CLIENT;</code></pre>
</div>
<div id="produits-de-moins-de-10-euros-ht-strictement." class="section level3">
<h3>Produits de moins de 10 euros HT strictement.</h3>
<p>La, c’est l’inversion, c’est une <strong>restriction</strong> (sélection des lignes à afficher). Le <code>*</code> permet d’afficher tous les attributs de la table <code>PRODUIT</code>.</p>
<pre class="sql"><code>SELECT *
FROM PRODUIT
WHERE PrixHT > 10;</code></pre>
</div>
<div id="numero-de-produit-designation-et-prix-ttc-de-chaque-produit." class="section level3">
<h3>Numéro de produit, désignation et prix TTC de chaque produit.</h3>
<p>Les informations du produit (numéro, désignation et prix hors taxe) sont présentes dans la table <code>PRODUIT</code>. Mais pour calculer le prix TTC, nous devons aussi utiliser la table <code>TVA</code> pour avoir la valeur du taux à appliquer à chaque produit. Donc nous devons faire une <strong>jointure</strong>. Comme vous allez le voir, il y a plusieurs façons de réaliser celle-ci.</p>
<pre class="sql"><code>-- Première possibilité : produit cartésien + restriction
-- On renomme le résultat du calcul
SELECT NumPrd, Designation,
PrixHT * (1 + Valeur / 100) AS PrixTTC
FROM PRODUIT, TVA
WHERE PRODUIT.NumTx = TVA.NumTx;
-- Deuxième possibilité : introduction des alias
SELECT NumPrd, Designation,
PrixHT * (1 + Valeur / 100) AS PrixTTC
FROM PRODUIT P, TVA T
WHERE P.NumTx = T.NumTx;
-- Troisième possibilité : avec l'opérateur INNER JOIN
SELECT NumPrd, Designation,
PrixHT * (1 + Valeur / 100) AS PrixTTC
FROM PRODUIT P
INNER JOIN TVA T ON P.NumTx = T.NumTx;
-- Quatrième possibilité : avec l'opérateur NATURAL JOIN (possible ici car
-- l'attribut NumTx est présent dans les deux tables (avec le même nom)
SELECT NumPrd, Designation,
PrixHT * (1 + Valeur / 100) AS PrixTTC
FROM PRODUIT NATURAL JOIN TVA;</code></pre>
</div>
<div id="clients-numero-nom-et-prenom" class="section level3">
<h3>Clients (numéro, nom et prénom)</h3>
<div id="ayant-au-moins-1-facture-a-leur-nom" class="section level4">
<h4>Ayant au moins 1 facture à leur nom</h4>
<p>Ici, nous devons juste nous assurer que le client est dans la liste des clients ayant fait un achat, donc présent dans la table <code>FACTURE</code>.</p>
<pre class="sql"><code>-- Première possibilité : avec une jointure
SELECT C.NumClt, Nom, Prenom
FROM CLIENT C, FACTURE F
WHERE C.NumClt = F.NumClt;
-- ou
SELECT C.NumClt, Nom, Prenom
FROM CLIENT C NATURAL JOIN FACTURE;
-- Deuxième possibilité : avec l'opérateur IN
SELECT NumClt, Nom, Prenom
FROM CLIENT
WHERE NumClt IN (SELECT NumClt FROM FACTURE);
-- Troisème possibilité : avec l'opérateur EXISTS
SELECT NumClt, Nom, Prenom
FROM CLIENT C
WHERE EXISTS
(SELECT NumClt
FROM FACTURE F
WHERE F.NumClt = C.NumClt);</code></pre>
</div>
<div id="ayant-au-moins-5-factures-a-leur-nom" class="section level4">
<h4>Ayant au moins 5 factures à leur nom</h4>
<p>La, nous devons compter le nombre de factures (et donc d’achats) pour chaque client afin de déterminer lesquels ont 5 (ou plus) factures à leur nom. Nous allons donc combiner un <strong>calcul d’agrégat</strong> avec une restriction sur ce calcul.</p>
<pre class="sql"><code>-- Première possibilité : jointure et calcul d'agrégat en une fois
SELECT C.NumClt, Nom, Prenom
FROM CLIENT C, FACTURE F
WHERE C.NumClt = F.NumClt
GROUP BY C.NumClt, Nom, Prenom
HAVING COUNT(*) >= 5;
-- Deuxième possibilité : calcul d'agrégat dans une sous-requête
SELECT NumClt, Nom, Prenom
FROM CLIENT
WHERE NumClt IN
(SELECT NumClt
FROM FACTURE
GROUP BY NumClt
HAVING COUNT(*) > 5);</code></pre>
</div>
<div id="nayant-aucune-facture-a-leur-nom-cas-possible-un-client-a-commence-puis-annule-sa-commande" class="section level4">
<h4>N’ayant aucune facture à leur nom (cas possible : un client a commencé puis annulé sa commande)</h4>
<p>Quand nous faisons une jointure, les clients ayant une facture sont obligatoirement dans le résultat. Et nous ne pouvons pas faire de <code>HAVING COUNT(*) = 0</code> car cela n’a aucun sens (s’il y a la valeur, c’est qu’elle est au moins sur une ligne). Il faut donc utiliser le <code>NOT IN</code> ou le <code>NOT EXISTS</code>. On peut aussi utiliser l’opérateur <code>LEFT JOIN</code> (par exemple) et tester le numéro de facture est <code>NULL</code>.</p>
<pre class="sql"><code>-- Première possibilité : avec l'opérateur NOT IN
SELECT NumClt, Nom, Prenom
FROM CLIENT
WHERE NumClt NOT IN
(SELECT NumClt FROM FACTURE);
-- Deuxième possibilité : avec l'opérateur NOT EXISTS
SELECT NumClt, Nom, Prenom
FROM CLIENT C
WHERE NOT EXISTS
(SELECT NumClt
FROM FACTURE F
WHERE F.NumClt = C.NumClt);
-- Troisième possibilité : avec un LEFT JOIN et un test = NULL (à tester si ca
-- fonctionne réellement, mais il n'y a pas de raison que ce soit le contraire)
SELECT C.NumClt, Nom, Prenom
FROM CLIENT C
LEFT JOIN FACTURE F ON C.NumClt = F.NumClt
WHERE NumFac IS NULL;</code></pre>
</div>
</div>
<div id="produits-achetes-avec-la-quantite-totale-sur-tous-les-achats-par-le-client-28." class="section level3">
<h3>Produits achetés (avec la quantité totale sur tous les achats) par le client 28.</h3>
<p>Dans ce cas, nous avons besoin de quatre tables :</p>
<ul>
<li><code>CLIENT</code> et <code>PRODUIT</code> naturellement,</li>
<li><code>FACTURE</code> et <code>ACHATS</code> car il nous faut relier les deux tables précédentes.</li>
</ul>
<p>Voici une proposition de requête. Comme nous pourrez vous en apercevoir, il y a plusieurs autres possibilités (sur les jointures, sur les calculs d’agrégats, …)</p>
<pre class="sql"><code>SELECT P.NumPrd, Designation,
SUM(Qte) AS QteTotal
FROM CLIENT C, PRODUIT P, FACTURE F, ACHATS A
WHERE C.NumClt = F.NumClt
AND F.NumFac = A.NumFac
AND A.NumPrd = P.NumPrd
AND C.NumClt = 28
GROUP BY P.NumPrd, Designation;</code></pre>
</div>
<div id="recuperation-des-informations-concernant-la-facture-121" class="section level3">
<h3>Récupération des informations concernant la facture 121</h3>
<p>Pour éditer une facture, nous avons trois étapes :</p>
<ol style="list-style-type: decimal">
<li>Récupérer les informations du client (numéro, nom, prénom et adresse)</li>
<li>Récupérer la liste des produits qui sont sur la facture (numéro de produit, désignation, prix HT, prix TTC, quantité, taux TVA)</li>
<li>Calculer le montant total de la facture (celui-ci n’étant pas encore calculé)</li>
</ol>
<div id="informations-du-client" class="section level4">
<h4>Informations du client</h4>
<p>Nous choisissons ici de le faire avec l’opérateur <code>IN</code>.</p>
<pre class="sql"><code>SELECT NumClt, Nom, Prenom, Adresse
FROM CLIENT
WHERE NumClt IN
(SELECT NumClt FROM FACTURE WHERE NumFac = 121);</code></pre>
</div>
<div id="liste-des-produits-achetes" class="section level4">
<h4>Liste des produits achetés</h4>
<p>Idem pour sélectionner les produits de la facture 121, mais il faut tout de même faire une jointure pour avoir le taux de TVA à appliquer.</p>
<pre class="sql"><code>SELECT NumPrd, Designation,
PrixHT * (1 + Valeur / 100) AS PrixTTC
FROM PRODUIT NATURAL JOIN TVA
WHERE NumPrd IN
(SELECT NumPrd FROM ACHATS WHERE NumFac = 121);</code></pre>
</div>
<div id="montant-total-de-la-facture." class="section level4">
<h4>Montant total de la facture.</h4>
<p>Il faut reprendre la requête ci-dessus pour la modifier légèrement pour faire le calcul du total (avec <code>SUM</code>).</p>
<pre class="sql"><code>SELECT SUM(PrixHT * (1 + Valeur / 100)) AS Total
FROM PRODUIT NATURAL JOIN TVA
WHERE NumPrd IN
(SELECT NumPrd FROM ACHATS WHERE NumFac = 121);</code></pre>
</div>
</div>
</div>
</div>
<div class="footer">
Site créé avec <a href="http://www.r-project.org" target="_blank"><code>R</code></a> et la
librairie <a href="http://rmarkdown.rstudio.com/" target="_blank"><code>rmarkdown</code></a>.
</div>
<script>
$("#TOC").css("margin-top", "100px");
</script>
</div>
<script>
// add bootstrap table styles to pandoc tables
$(document).ready(function () {
$('tr.header').parent('thead').parent('table').addClass('table table-condensed');
});
</script>
<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
(function () {
var script = document.createElement("script");
script.type = "text/javascript";
script.src = "https://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
document.getElementsByTagName("head")[0].appendChild(script);
})();
</script>
</body>
</html>