<!-- If the browser does not support frames put the first topic content here --> <div class="WordSection1"> <p class="MsoNormal" align="center" style='text-align:center'> <b><span style='font-size:14.0pt;text-transform:uppercase'>LE LANGAGE SQL</span></b></p> <p class="MsoNormal" align="center" style='text-align:center'> <b><span style='font-size:14.0pt;text-transform:uppercase'>Requêtes d'interrogation de données : fonctions statistiques et group by</span></b></p> <p class="MsoNormal"> </p> <p class="MsoNormal">Soit le modèle relationnel suivant :</p> <p class="table" style= 'margin-top:0cm;margin-right:-14.2pt;margin-bottom:0cm; margin-left:2.0cm;margin-bottom:.0001pt;text-indent:-2.0cm;border:none'> <span style= 'font-weight:normal'>CLIENT (n<u>um_cli</u>, nom_cli, pren_cli, rue_cli, cp_cli, ville_cli, credit_autorisé)</span></p> <p class="MsoNormal" style='margin-left:2.0cm;text-indent:-2.0cm'> COMLIVRE <u>(num_comm,</u> date_comm, qte, #no_volume, #num_emp, #num_cli)</p> <p class="MsoNormal" style='margin-left:2.0cm;text-indent:-2.0cm'> EMPLOYE (<u>num_emp</u>, nom_emp, pren_emp, fonction, date_emb, salaire, prime)</p> <p class="MsoNormal" style='margin-left:2.0cm;text-indent:-2.0cm'> La prime est facultative.</p> <p class="MsoNormal" style='margin-left:2.0cm;text-indent:-2.0cm'> VOLUME (<u>no_volume</u>, titre, prix, #code_categ)</p> <p class="MsoNormal" style='margin-left:2.0cm;text-indent:-2.0cm'> CATLIVRE (<u>code_categ</u>, lib_categ)</p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal">Soit l'extrait de la relation EMPLOYE :</p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style='border-collapse:collapse;border:none'> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:1.5pt;border-left: 1.5pt;border-bottom:1.0pt;border-right:1.0pt;border-color:windowtext; border-style:solid;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">num_emp</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:solid windowtext 1.5pt; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">nom_emp</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:solid windowtext 1.5pt; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">pren_emp</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:solid windowtext 1.5pt; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">fonction</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:solid windowtext 1.5pt; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">date_emb</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:solid windowtext 1.5pt; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">salaire</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:solid windowtext 1.5pt; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">prime</p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: solid windowtext 1.5pt;border-bottom:solid windowtext 1.0pt;border-right: solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1000</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Vilier</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Paul</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">vendeur</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">01/12/92</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">8000</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1500</p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: solid windowtext 1.5pt;border-bottom:solid windowtext 1.0pt;border-right: solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1010</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Audinet</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Elizabeth</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">secrétaire</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">01/06/93</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">7500</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: solid windowtext 1.5pt;border-bottom:solid windowtext 1.0pt;border-right: solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1020</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Bontemps</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Frédérique</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">secrétaire</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">01/08/94</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">7500</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: solid windowtext 1.5pt;border-bottom:solid windowtext 1.0pt;border-right: solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1030</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Guillaume</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Estelle</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">vendeur</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">01/10/93</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">7800</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">2000</p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: solid windowtext 1.5pt;border-bottom:solid windowtext 1.0pt;border-right: solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1040</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Chevalier</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Patrick</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">comptable</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">01/08/93</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">9000</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: solid windowtext 1.5pt;border-bottom:solid windowtext 1.0pt;border-right: solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1050</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Receveur</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Philippe</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">vendeur</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">01/06/92</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">8600</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1500</p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: solid windowtext 1.5pt;border-bottom:solid windowtext 1.5pt;border-right: solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1060</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Perrin</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Christian</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">directeur</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">01/01/92</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">15000</p> </td> <td width="51" valign="top" style= 'width:37.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> </tr> </table> <p class="MsoNormal"><b><u><span style= 'text-decoration:none'> </span></u></b></p> <p class="MsoNormal"><b><u>Exemples :</u></b></p> <p class="Question"> </p> <p class="Question">Quel est le salaire le plus élevé dans la société ?</p> <p class="MsoNormal">SELECT MAX(salaire) as moyenne</p> <p class="MsoNormal">FROM EMPLOYE;</p> <p class="MsoNormal"> </p> <p class="Question">A combien s'élèvent au total les salaires annuels des employés ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">SELECT SUM(salaire)*12</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM EMPLOYE;</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="Question">Quel est le nombre de fonctions existant dans la société ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">SELECT COUNT(DISTINCT fonction)</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM EMPLOYE;</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal">Dans les exemples précédents, les fonctions statistiques sont appliquées à la totalité des lignes d'une table. Ils peuvent bien sûr s'appliquer à un sous-ensemble des lignes d'une table en utilisant la clause WHERE.</p> <p class="MsoNormal"> </p> <p class="MsoNormal"><b><u>Exemples d'utilisation de fonctions statistiques avec la clause WHERE :</u></b></p> <p class="Question"> </p> <p class="Question">Combien y-a-t-il de volumes de catégorie Finances ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">SELECT COUNT(*)</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM VOLUME</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">WHERE code_categ IN</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> (SELECT code_categ</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> FROM CATEGORIE</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> WHERE lib_categ = 'Finances');</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><b>Lorsqu'on utilise des fonctions statistiques, on ne peut pas y introduire des interrogations individuelles.</b></p> <p class="MsoNormal">Ainsi pour obtenir le nom de l'employé qui a le salaire le plus élevé, on ne peut pas écrire la requête suivante :</p> <p class="MsoNormal" style='text-indent:35.4pt'><s>SELECT nom_emp, MAX(salaire)</s></p> <p class="MsoNormal" style='text-indent:35.4pt'><s>FROM Employe;</s></p> <p class="MsoNormal"> </p> <p class="MsoNormal">Par contre, on peut calculer un résultat dans une sous-requête à l'aide d'une fonction statistique et tester ce résultat dans la requête de niveau supérieur.</p> <p class="MsoNormal"> <span lang="EN-GB" xml:lang="EN-GB">SELECT nom_emp</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> FROM Employe</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> WHERE salaire =</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> ( SELECT MAX(salaire)</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> FROM Employe);</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="Question">Quels sont les noms des employés dont le salaire est supérieur ou égal au salaire moyen ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">SELECT nom_emp</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM Employe</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">WHERE salaire >=</span></p> <p class="MsoNormal" style='text-indent:18.0pt'><span lang="EN-GB" xml:lang="EN-GB"> ( SELECT AVG(salaire)</span></p> <p class="MsoNormal" style='text-indent:18.0pt'><span lang="EN-GB" xml:lang="EN-GB"> FROM Employe);</span></p> <p class="MsoNormal" style='text-indent:18.0pt'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <h1 style='margin-left:0cm;text-indent:0cm'><a name="node0" id= "node0"></a>1.<span style= 'font:7.0pt "Times New Roman"'> </span> Regroupement</h1> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 1.0pt 1.0pt 1.0pt; background:#DFDFDF'> <p class="MsoNormal" style= 'background:#DFDFDF;border:none;padding:0cm'><span style= 'color:black'>Le regroupement va permettre d'appliquer les fonctions statistiques non plus sur un seul ensemble de ligne mais <b>par ensemble de lignes</b> vérifiant un même critère c'est à dire par ensemble de lignes dont l'attribut (ou les attributs) de groupement a (ou ont) la même valeur.</span></p> </div> <h2 style='margin-left:0cm;text-indent:0cm'><a name="node1" id= "node1"></a><span lang="EN-GB" xml:lang="EN-GB">1.1.<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span lang="EN-GB" xml:lang="EN-GB">La clause GROUP BY</span></h2> <p class="Question">Quel est le salaire moyen des employés ?</p> <p class="MsoNormal">SELECT AVG(salaire) as Salaire_moyen</p> <p class="MsoNormal">FROM EMPLOYE;</p> <p class="MsoNormal">La moyenne est calculée sur les valeurs de salaire de toutes les lignes de la table Employe.</p> <p class="MsoNormal">Tuple résultat :</p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style='border-collapse:collapse;border:none'> <tr> <td width="109" valign="top" style= 'width:81.5pt;border:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Salaire_moyen</p> </td> </tr> <tr> <td width="109" valign="top" style= 'width:81.5pt;border:solid windowtext 1.5pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">9057,14</p> </td> </tr> </table> <p class="MsoNormal"> </p> <p class="Question">Quel est le salaire moyen des employés par fonction ?</p> <p class="MsoNormal">SELECT fonction, AVG(salaire)</p> <p class="MsoNormal">FROM EMPLOYE</p> <p class="MsoNormal"><b>GROUP BY</b> fonction;</p> <p class="MsoNormal"> </p> <p class="MsoNormal">La moyenne est calculée pour chaque ensemble de lignes, les lignes étant regroupées pour les mêmes valeurs de l'attribut fonction.</p> <p class="MsoNormal"> </p> <p class="MsoNormal">Tuples résultats :</p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style='border-collapse:collapse;border:none'> <tr> <td width="109" valign="top" style= 'width:81.5pt;border:solid black 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Fonction</p> </td> <td width="113" valign="top" style= 'width:3.0cm;border:solid black 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">AVG(salaire)</p> </td> </tr> <tr> <td width="109" valign="top" style= 'width:81.5pt;border:solid black 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">vendeur</p> </td> <td width="113" valign="top" style= 'width:3.0cm;border-top:none;border-left:none; border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">8133,33</p> </td> </tr> <tr> <td width="109" valign="top" style= 'width:81.5pt;border:solid black 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">secrétaire</p> </td> <td width="113" valign="top" style= 'width:3.0cm;border-top:none;border-left:none; border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">7500</p> </td> </tr> <tr> <td width="109" valign="top" style= 'width:81.5pt;border:solid black 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">comptable</p> </td> <td width="113" valign="top" style= 'width:3.0cm;border-top:none;border-left:none; border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">9000</p> </td> </tr> <tr> <td width="109" valign="top" style= 'width:81.5pt;border:solid black 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">directeur</p> </td> <td width="113" valign="top" style= 'width:3.0cm;border-top:none;border-left:none; border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">15000</p> </td> </tr> </table> <p class="MsoNormal"> </p> <p class="Question"> </p> <p class="Question">Quel est le nombre de livres commandés <b>par client</b> (numéro client) ?</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question"> </p> <p class="Question">Quel est le nombre de livres commandés <b>par client</b> (numéro, nom et prénom du client) ?</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="MsoNormal"><u><span style= 'text-decoration:none'> </span></u></p> <p class="MsoNormal"><u>Règle</u> :</p> <p class="MsoNormal" style= 'margin-left:1.0cm;text-indent:-14.15pt'><span lang="EN-GB" xml:lang="EN-GB"> 1</span>. ce qui figure dans le GROUP BY ne doit pas obligatoirement figurer dans le SELECT mais quand il s'agit d'un SELECT de 1<sup>er</sup> niveau, cela s'impose car sinon le résultat n'a pas de sens. Cela n'a pas de sens d'écrire :</p> <p class="MsoNormal" style='margin-left:1.0cm'>SELECT AVG(salaire)</p> <p class="MsoNormal" style='margin-left:1.0cm'>FROM EMPLOYE</p> <p class="MsoNormal" style='margin-left:1.0cm'>GROUP BY fonction;</p> <p class="MsoNormal" style='margin-left:1.0cm'>On ne sait pas à quelle fonction se rapporte chacune des moyennes affichées.</p> <h2 style='margin-left:0cm;text-indent:0cm'><a name="node2" id= "node2"></a>1.2.<span style= 'font:7.0pt "Times New Roman"'> </span> La clause HAVING</h2> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 1.0pt 1.0pt 1.0pt; background:#DFDFDF'> <p class="MsoNormal" style= 'background:#DFDFDF;border:none;padding:0cm'><span style= 'color:black'>Si un critère de restriction doit être exprimé non pas sur chaque ligne d'une table mais sur chaque ensemble de lignes produit par un regroupement, la clause à utiliser n'est pas WHERE mais <b>HAVING</b>.</span></p> </div> <p class="MsoNormal"> </p> <p class="MsoNormal">Exemple :</p> <p class="Question">Quel est le salaire moyen des employés par fonction qui compte plus d'un employé ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">SELECT fonction, AVG(salaire)</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM Employe</span></p> <p class="MsoNormal"><b><span lang="EN-GB" xml:lang="EN-GB">GROUP BY</span></b><span lang="EN-GB" xml:lang= "EN-GB">fonction</span></p> <p class="MsoNormal"><b><span lang="EN-GB" xml:lang= "EN-GB">HAVING</span></b><span lang="EN-GB" xml:lang= "EN-GB">count(*) > 1;</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="Question">Quels sont les numéros des clients qui ont acheté pour plus de 100 euros de livres ? (on précisera le montant total des livres achetés)</p> <p class="MsoNormal"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="Question"> </p> <p class="Question">……………………………………………………………………………………………………………</p> <p class="MsoNormal"> </p> <b><span style= 'font-size:14.0pt;font-family:"Arial",sans-serif'><br clear="all" style='page-break-before:always' /></span></b> <h1><a name="node3" id="node3"></a>2.<span style= 'font:7.0pt "Times New Roman"'> </span> Exercices d'application :</h1> <p class="Question" style= 'margin-left:14.15pt;text-indent:-14.15pt'>1.<span style= 'font:7.0pt "Times New Roman"'> </span> Quel est le prix minimum des livres?</p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="Question">2. Quel est le titre du livre le moins cher ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"> </p> <p class="Question">3. Quel est le prix moyen des livres traitant d'informatique ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="Question">4. Quel est le nombre de villes où résident nos clients ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="Question">5. Quel est le nombre de clients par ville ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="Question">6. Quel est le nombre de commandes passées par chaque client ?</p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="Question">7. Quels sont tous les numéros de clients ayant commandé plus de 10 volumes ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman",serif' xml:lang= "EN-GB"><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="Question" style='margin-right:-14.2pt'>8. Quel est le prix minimum des volumes par code catégorie pour les catégories comptant moins de 10 volumes ?</p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="Question">9. Réécrire la même requête avec l'affichage par libellé catégorie en supposant l’unicité des libellés.</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </div>
Table des matières
Sujets
Recherche
1. Regroupement
1.1. La clause GROUP BY
1.2. La clause HAVING
2. Exercices d'application :
1.1. La clause GROUP BY
1.2. La clause HAVING
1. Regroupement
2. Exercices d'application :
Générée avec
chmProcessor
Générée avec
chmProcessor