Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Le langage SQL et l'outil iSQL 5-2 But du chapitre Ce chapitre poursuit l'étude

Le langage SQL et l'outil iSQL 5-2 But du chapitre Ce chapitre poursuit l'étude des fonctions. Il explique comment obtenir des informations synthétisées, telles que des moyennes, sur des groupes de lignes. Il présente les différentes manières de regrouper les lignes d'une table en sous-ensembles et de préciser des critères de recherche sur ces groupes de lignes. Le langage SQL et l'outil iSQL 5-3 Fonctions de groupe Contrairement aux fonctions monoligne, les fonctions de groupe agissent sur des groupes de lignes et donnent un résultat par groupe. Un groupe peut être une table entière ou un sous-ensemble d'une table. Le langage SQL et l'outil iSQL 5-4 Fonctions de groupe (suite) Chaque fonction accepte un argument. Le tableau suivant présente les différentes options de syntaxe possibles : Fonction Description AVG([DISTINCT|ALL]n) Valeur moyenne de n, ignore les valeurs NULL COUNT({*|[DISTINCT|ALL]expr}) Nombre de lignes où la valeur de expr n'est pas NULL (* permet de compter toutes les lignes sélectionnées, y compris les doublons et les lignes contenant des valeurs NULL) MAX([DISTINCT|ALL]expr) Valeur maximale de expr, ignore les valeurs NULL MIN([DISTINCT|ALL]expr) Valeur minimale de expr, ignore les valeurs NULL STDDEV([DISTINCT|ALL]x) Ecart type de n, ignore les valeurs NULL SUM([DISTINCT|ALL]n) Somme des valeurs de n, ignore les valeurs NULL VARIANCE([DISTINCT|ALL]x) Variance de n, ignore les valeurs NULL Le langage SQL et l'outil iSQL 5-5 Règles d'utilisation des fonctions de groupe • Avec DISTINCT, la fonction ne prend en compte que les valeurs distinctes ; avec ALL, elle tient compte de toutes les valeurs y compris des doublons. La valeur par défaut est ALL, par conséquent il n'est pas nécessaire de la préciser. • Les données des fonctions utilisant un argument expr peuvent être de type CHAR, VARCHAR2, NUMBER ou DATE . • Toutes les fonctions de groupe ignorent les valeurs NULL. Pour affecter une valeur à une valeur NULL, utilisez la fonction NVL, NVL2 ou COALESCE. • Le serveur Oracle trie de manière implicite le résultat dans l'ordre croissant en utilisant une clause GROUP BY. Pour modifier cet ordre de tri par défaut, il est possible d'utiliser le mot-clé DESC dans une clause ORDER BY. Le langage SQL et l'outil iSQL 5-6 Fonctions de groupe Vous pouvez utiliser les fonctions AVG, SUM, MIN et MAX avec des colonnes de données numériques. L'exemple de la diapositive affiche la moyenne, le maximum, le minimum et la somme des salaires mensuels de tous les représentants. Le langage SQL et l'outil iSQL 5-7 Fonctions de groupe (suite) Vous pouvez utiliser les fonctions MAX et MIN pour tous les types de données. La diapositive ci- dessus affiche la date d'embauche du dernier employé recruté et celle de l'employé présentant le plus d'ancienneté. L'exemple suivant affiche le nom des deux employés apparaissant respectivement en premier et en dernier dans le classement alphabétique de tous les employés. SELECT MIN(last_name), MAX(last_name) FROM employees; Remarque : Les fonctions AVG, SUM, VARIANCE et STDDEV n'acceptent que des données de type numérique. Le langage SQL et l'outil iSQL 5-8 Fonction COUNT La fonction COUNT se présente sous trois formes : • COUNT(*) • COUNT(expr) • COUNT(DISTINCT expr) La fonction COUNT(*) renvoie le nombre de lignes d'une table qui répondent aux critères de l'instruction SELECT, y compris les doublons et les lignes contenant des valeurs NULL dans l'une des colonnes. Si une clause WHERE est incluse dans l'instruction SELECT, la fonction COUNT(*) renvoie le nombre de lignes répondant à la condition de la clause WHERE. En revanche, la fonction COUNT(expr) renvoie le nombre de valeurs non NULL contenues dans la colonne identifiée par expr. La fonction COUNT(DISTINCT expr) renvoie le nombre de valeurs non NULL uniques contenues dans la colonne identifiée par expr. L'exemple de la diapositive affiche le nombre d'employés du service 50. Le langage SQL et l'outil iSQL 5-9 Fonction COUNT (suite) La diapositive ci-dessus affiche le nombre d'employés du service 80 habilités à toucher une commission. Exemple Affichez le nombre de valeurs contenues dans la colonne DEPARTMENT_ID de la table EMPLOYEES. SELECT COUNT(department_id) FROM employees; Le langage SQL et l'outil iSQL 5-10 Mot-clé DISTINCT Le mot-clé DISTINCT vous permet d'exclure tous les doublons contenus dans une colonne. L'exemple de la diapositive affiche le nombre de services distincts de la table EMPLOYEES. Le langage SQL et l'outil iSQL 5-11 Fonctions de groupe et valeurs NULL Toutes les fonctions de groupe ignorent les valeurs NULL des colonnes. Dans l'exemple de la diapositive, la moyenne est calculée uniquement sur les lignes pour lesquelles la colonne COMMISSION_PCT est correctement renseignée. Le calcul de la moyenne s'effectue par division du total des commissions versées à tous les employés par le nombre d'employés touchant une commission (4). Le langage SQL et l'outil iSQL 5-12 Fonctions de groupe et valeurs NULL (suite) La fonction NVL contraint les fonctions de groupe à intégrer des valeurs NULL. Dans la diapositive ci-dessus, la moyenne est calculée sur toutes les lignes de la table, y compris celles dont la colonne COMMISSION_PCT contient une valeur NULL. Le calcul de la moyenne s'effectue par division du total des commissions de tous les employés par le nombre total d'employés de l'entreprise (20). Le langage SQL et l'outil iSQL 5-13 Groupes de données Jusqu'ici, la table s'est vue considérée par les fonctions de groupe comme un seul groupe d'informations. Cependant, il est parfois nécessaire de diviser les informations d'une table en groupes plus restreints. Pour cela, utilisez la clause GROUP BY. Le langage SQL et l'outil iSQL 5-14 Clause GROUP BY La clause GROUP BY vous permet d'organiser les lignes d'une table en groupes restreints. Vous pouvez ensuite utiliser les fonctions de groupe pour renvoyer des informations synthétisées sur chaque groupe. Explication de la syntaxe : group_by_expression désigne les colonnes dont les valeurs déterminent les différents groupes Règles • Si vous intégrez une fonction de groupe dans une clause SELECT, vous ne pouvez pas sélectionner dans le même temps des résultats individuels, à moins que la colonne individuelle ne soit mentionnée dans la clause GROUP BY. Si vous omettez de préciser la liste des colonnes dans la clause GROUP BY, un message d'erreur s'affiche. • Avec la clause WHERE, vous pouvez exclure des lignes avant de procéder à la division en groupes. • Vous devez inclure les colonnes dans la clause GROUP BY. • Vous ne pouvez pas utiliser d'alias de colonne dans la clause GROUP BY. • Par défaut, les lignes sont triées dans l'ordre croissant des colonnes incluses dans la liste GROUP BY. Vous pouvez changer cet ordre en utilisant la clause ORDER BY. Le langage SQL et l'outil iSQL 5-15 Clause GROUP BY (suite) Lorsque vous utilisez la clause GROUP BY, assurez-vous que toutes les colonnes de la liste SELECT qui ne figurent pas dans des fonctions de groupe sont intégrées à cette clause. L'exemple de la diapositive affiche le numéro de service et le salaire moyen dans chaque service. L'instruction SELECT, qui contient une clause GROUP BY, est évaluée de la manière suivante : • La clause SELECT indique les colonnes à extraire : – la colonne des numéros de service de la table EMPLOYEES, – la moyenne de tous les salaires du groupe que vous avez indiqué dans la clause GROUP BY. • La clause FROM indique les tables auxquelles doit accéder la base de données : en l'occurrence, la table EMPLOYEES. • La clause WHERE indique les lignes à extraire. En l'absence de clause WHERE, toutes les lignes seront extraites. • La clause GROUP BY indique la manière dont les lignes doivent être groupées. Dans le cas présent, elles sont groupées par numéro de service pour permettre à la fonction AVG, appliquée à la colonne des salaires, de calculer le salaire moyen de chaque service. Le langage SQL et l'outil iSQL 5-16 Clause GROUP BY (suite) La colonne GROUP BY ne doit pas nécessairement figurer dans la clause SELECT. Par exemple, l'instruction SELECT de la diapositive affiche le salaire moyen dans chaque service, mais pas les numéros de service correspondants. Toutefois, sans les numéros de service, les résultats ne sont pas très parlants. Vous pouvez utiliser la fonction de groupe dans la clause ORDER BY. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary); … Le langage SQL et l'outil iSQL 5-17 Sous-groupes Vous devez parfois visualiser les résultats de sous-groupes. Sur la diapositive ci-dessus, l'état affiche le total des salaires pour chacun des postes à l'intérieur de chaque service. La table EMPLOYEES est classée par numéro de service et, au sein de ce groupe, par poste. Par exemple, les quatre magasiniers (ST_CLERK) du service 50 ont été regroupés ; vous obtenez un seul résultat (salaire total) pour ce sous-groupe. Le langage SQL et l'outil iSQL 5-18 Sous-groupes (suite) Vous pouvez obtenir des informations synthétisées sur des groupes et des sous-groupes en précisant plusieurs colonnes GROUP BY. Vous pouvez en outre déterminer l'ordre de tri par défaut des résultats en fonction de l'ordre des colonnes figurant dans la clause GROUP BY. L'instruction SELECT de la diapositive, qui contient une clause GROUP BY, est évaluée de la manière suivante : • uploads/Philosophie/ oracle-sql-lesson05.pdf