<!-- 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">FROM EMPLOYE;</p> <p class="MsoNormal"> </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">FROM EMPLOYE;</p> <p class="MsoNormal"> </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"> </span> WHERE salaire =</p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> ( SELECT MAX(salaire)</p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> FROM Employe);</p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> </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">WHERE salaire >=</p> <p class="MsoNormal" style='text-indent:18.0pt'> ( SELECT AVG(salaire)</p> <p class="MsoNormal" style='text-indent:18.0pt'> FROM Employe);</p> <h1 style='margin-left:0cm;text-indent:0cm'> </h1> <h1 style='margin-left:0cm;text-indent:0cm'><a name="node4" id= "node4"></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="node5" id= "node5"></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"><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"><span lang="EN-GB" xml:lang= "EN-GB"> </span></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 par client (numéro client) ?</p> <p class="Question"> </p> <p class="Question">SELECT num_cli, COUNT(*)</p> <p class="Question">FROM comlivre</p> <p class="Question">GROUP BY num_cli</p> <p class="Question"> </p> <p class="Question"> </p> <p class="Question">Quel est le nombre de livres commandés par client (numéro, nom et prénom du client) ?</p> <p class="Question"> </p> <p class="Question">SELECT nom_cli, pren_cli, COUNT(*)</p> <p class="Question">FROM comlivre c</p> <p class="Question">INNER JOIN client cl ON c.num_cli = cl.num_cli</p> <p class="Question">GROUP BY c.num_cli</p> <p class="Question"> </p> <p class="MsoNormal"><u><span style= 'text-decoration:none'> </span></u></p> <p class="MsoNormal"><u>Règles</u> :</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:1.0cm;text-indent:-14.15pt'><span lang="EN-GB" xml:lang="EN-GB"> </span>1. 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> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; margin-left:1.0cm;margin-right:527.7pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">SELECT AVG(salaire)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">FROM EMPLOYE</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">GROUP BY fonction;</span></p> </div> <p class="MsoNormal" style='margin-left:1.0cm'>Ici on ne sait pas à quelle fonction se rapporte chacune des moyennes affichées.</p> <h2 style='margin-left:0cm;text-indent:0cm'><a name="node6" id= "node6"></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">SELECT fonction, AVG(salaire)</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>HAVING</b> count(*) > 1;</p> <p class="MsoNormal"> </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">SELECT c.num_cli, SUM(prix * qte)</p> <p class="Question">FROM comlivre c</p> <p class="Question">INNER JOIN volume v</p> <p class="Question">ON c.no_volume = v.no_volume</p> <p class="Question">GROUP c.num_cli</p> <p class="Question">HAVING SUM(prix * qte) > 100 ;</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="node7" id="node7"></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">SELECT MIN(prix)</p> <p class="MsoNormal">FROM VOLUME;</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">SELECT titre</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM VOLUME</span></p> <p class="MsoNormal">WHERE prix =</p> <p class="MsoNormal" style='text-indent:35.4pt'>(SELECT MIN(prix) FROM VOLUME)</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">SELECT AVG(prix)</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='text-indent:35.4pt'><span lang="EN-GB" xml:lang="EN-GB">(SELECT code_categ FROM CATEGORIE WHERE lib_categ='Informatique')</span></p> <p class="MsoNormal" style='text-indent:35.4pt'><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">SELECT COUNT(DISTINCT ville_cli)</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM CLIENT</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">SELECT ville_cli, COUNT(*)</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM CLIENT</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">GROUP BY ville_cli</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="Question">6. Quel est le nombre de commandes passées par chaque client ?</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">SELECT num_cli, COUNT(*)</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM COMLIVRE</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">GROUP BY num_cli</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></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">SELECT num_cli</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM COM_LIVRE</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">GROUP BY num_cli</span></p> <p class="MsoNormal">HAVING COUNT(*) > 10</p> <p class="MsoNormal"> </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">SELECT code_categ, MIN(prix)</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">GROUP BY code_categ</span></p> <p class="MsoNormal">HAVING count(*) < 10</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">SELECT lib_categ, MIN(prix)</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">FROM CATEGORIE, VOLUME</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">WHERE CATEGORIE.code_categ = VOLUME.code_categ</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">GROUP BY lib_categ</span></p> <p class="MsoNormal">HAVING count(*) < 10</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