Suite

Requête d'écriture pour sélectionner les valeurs MIN et MAX par groupes ?


Dans ArcMap 10, je travaille dans une géodatabase personnelle où les points (voir capture d'écran ci-dessous) représentent le numéro de chaque maison dans la rue. Chaque point a un ObjectID. Cet ObjectID est une valeur unique pour chaque "lien" de la rue à laquelle tous les points sont liés.

J'ai un total de 35 000 points (numéros de maison), qui sont tous liés à 14 000 liens différents. Ce que je veux, c'est sélectionner à la fois les valeurs MAX et MIN de chaque côté de la rue. Chaque côté de la rue a également un identifiant unique, qui apparaît dans ma table attributaire en tant que CVEFT. Sur chaque lien, le CVEFT (pour les deux côtés) est différent.

Voici à quoi ressemble ma table :

Comme vous pouvez le voir dans la capture d'écran ci-dessus, il existe plusieurs points (NUMEXT) liés au même lien (OBJECTID). Vous pouvez également voir que tous les points liés à un même lien peuvent avoir 2 codes de rue différents (CVEFT).

Je souhaite créer une requête SQL qui me permet de sélectionner les valeurs MIN et MAX pour chaque lien, pour chaque côté de la rue.

Est-ce que n'importe qui a une idée de comment faire ceci ?


Il semble que vous vouliez faire un résumé des statistiques avec un minimum et un maximum de NUMEXT et un champ de cas de CVEFT. Cela vous donnera un tableau avec chaque instance de CVEFT et le NUMEXT minimum et maximum.

Joignez cette table à la base de données PAs_Geodatabase à l'aide de CVEFT, puis sélectionnez où NUMEXT = SumTable.NUMEXT_MAX et NUMEXT = SumTable.NUMEXT_MIN pour obtenir les numéros de maison maximum et minimum pour ce côté de la rue.


Requête d'écriture pour sélectionner les valeurs MIN et MAX par groupes ? - Systèmes d'information géographique

MySQL server est un système de gestion de base de données relationnelle open source qui est un support majeur pour les applications Web. Les bases de données et les tables associées sont le composant principal de nombreux sites Web et applications, car les données sont stockées et échangées sur le Web. Même tous les sites de réseaux sociaux, principalement Facebook, Twitter et Google, dépendent des données MySQL qui sont conçues et optimisées à cette fin. Pour toutes ces raisons, le serveur MySQL devient le choix par défaut pour les applications Web.

Le serveur MySQL est utilisé pour les opérations de données telles que l'interrogation, le tri, le filtrage, le regroupement, la modification et la jonction des tables. Avant d'apprendre les requêtes couramment utilisées, examinons certains des avantages de MySQL.

  • Base de données rapide et haute performance.
  • Facile à utiliser, à entretenir et à administrer.
  • Facilement disponible et maintenir l'intégrité de la base de données.
  • Fournit l'évolutivité, la convivialité et la fiabilité.
  • Matériel à faible coût.
  • MySQL peut lire des requêtes simples et complexes et écrire des opérations.
  • InnoDB est le moteur de stockage par défaut et largement utilisé.
  • Fournit un support d'indexation solide.
  • Fournit une prise en charge SSL pour les connexions sécurisées.
  • Fournit un cryptage et une précision puissants des données.
  • Fournit une compatibilité multiplateforme.
  • Fournit une répétition de code minimisée.

Cela affiche des informations sur toutes les bases de données existantes sur le serveur.

Noter : Les bases de données ‘information_schema’, ‘mysql’ et ‘performance_schema’ sont des bases de données système utilisées en interne par le serveur MySQL. UNE ‘test’ La base de données est destinée à des fins de test fournies lors de l'installation.

Cela exécute l'instruction SELECT sans aucune table.
SELECT peut être utilisé pour exécuter une expression ou évaluer une fonction intégrée.
SELECT peut également être utilisé pour plus d'une ou plusieurs colonnes.

Noter : Tu as ‘id’ colonne comme AUTO_INCREMENT avec une contrainte de clé primaire qui garantit que chaque identifiant est une valeur incrémentée, évitant la duplication. Le moteur de stockage sélectionné est ‘InnoDB’ permettant la contrainte de clé étrangère et les transactions associées.

Noter : La clause WHERE dans les requêtes MySQL est utilisée pour filtrer les lignes pour une condition spécifique.

Noter : Les fonctions AGGREGATE vous permettent d'exécuter des calculs sur des données et de fournir des informations en utilisant
une requête SELECT.

Noter :Les fonctions MIN et MAX fonctionnent correctement sur les valeurs numériques et alphabétiques.

Noter : Les exemples de requêtes ci-dessus peuvent également être imbriqués les uns avec les autres en fonction des besoins.

Cela donne 10 enregistrements à partir du 5e enregistrement.

Noter : Ceci n'est pas utilisé dans le moteur de stockage MYISAM du serveur MySQL.
Les moteurs de stockage InnoDB prennent en charge les contraintes de clé étrangère.

Noter : Les signes de pourcentage (%) dans la requête représentent zéro ou plusieurs caractères.

Rejoindre régulièrement :
C'est la jointure qui obtient tous les enregistrements des deux tables qui correspondent exactement à la condition donnée.
Exemple :

Joint gauche :
C'est la jointure qui récupère tous les enregistrements qui correspondent à la condition donnée, et récupère également tous les enregistrements de
le tableau de gauche.
Exemple :

Joindre à droite :
C'est la jointure qui récupère tous les enregistrements qui correspondent à la condition donnée, et récupère également tous les enregistrements de
le bon tableau.
Exemple :

De même, n'importe quelle colonne peut être supprimée d'une table de base de données.

Conclusion :
L'exécution de requêtes MySQL est la tâche la plus couramment effectuée pour obtenir des données dans le processus de gestion de la base de données. Il existe de nombreux outils de gestion de base de données comme phpMyAdmin, qui permet d'effectuer des requêtes et de gérer confortablement les transactions avec des résultats de données visuels. Vous avez fait défiler les requêtes MySQL les plus courantes, qui sont utilisées dans les pratiques de codage quotidiennes. Le développeur peut personnaliser ou améliorer les requêtes en fonction d'une exigence particulière.

Attention lecteur ! N'arrêtez pas d'apprendre maintenant. Obtenez tous les concepts importants de la théorie CS pour les entretiens SDE avec le Cours de théorie CS à un prix adapté aux étudiants et devenez prêt pour l'industrie.


Одержание

Joomla fournit une couche d'abstraction de base de données sophistiquée pour simplifier l'utilisation pour les développeurs tiers. Les nouvelles versions de l'API de la plate-forme Joomla offrent des fonctionnalités supplémentaires qui étendent davantage la couche de base de données et incluent des fonctionnalités telles que des connecteurs vers une plus grande variété de serveurs de base de données et le chaînage de requêtes pour améliorer la lisibilité du code de connexion et simplifier le codage SQL.

Joomla peut utiliser différents types de systèmes de base de données SQL et s'exécuter dans une variété d'environnements avec différents préfixes de table. En plus de ces fonctions, la classe crée automatiquement la connexion à la base de données. En plus d'instancier l'objet, vous n'avez besoin que de deux lignes de code pour obtenir un résultat de la base de données dans une variété de formats. L'utilisation de la couche de base de données Joomla assure un maximum de compatibilité et de flexibilité pour votre extension.


Utilisation des questions sur les fonctions de groupe

Réponse : C. NVL est une fonction générale utilisée pour fournir une valeur alternative aux valeurs NULL. Les fonctions MAX, MIN et AVG peuvent être utilisées comme fonctions GROUP BY.

2. Laquelle des fonctions suivantes peut être utilisée sans la clause GROUP BY dans la requête SELECT ?

Réponse : A, B, C, D. Toutes les fonctions de groupe répertoriées peuvent être utilisées dans une requête à condition qu'aucune autre colonne ne soit sélectionnée dans la requête SELECT.

3. Laquelle des requêtes SELECT suivantes renvoie le numéro de service avec le salaire maximum payé à un employé ? (Considérez la structure du tableau telle qu'elle est donnée)

Réponse : B. La fonction MAX peut être utilisée pour retourner le salaire maximum dans un département où chaque groupe est formé par un département.

4. Parmi les affirmations suivantes, lesquelles sont vraies à propos de la fonction COUNT ?

  1. La fonction COUNT compte le nombre de lignes
  2. La fonction COUNT(*) compte le nombre de lignes avec des doublons et des valeurs NULL
  3. La fonction COUNT(DISTINCT) compte le nombre de lignes distinctes
  4. COUNT(*) équivaut à COUNT(ALL)

Réponse : B. Le COUNT (*) compte le nombre de lignes, y compris les doublons et les valeurs NULL. Utilisez les mots clés DISTINCT et ALL pour restreindre les valeurs en double et NULL.

5. Quels sont les types de données appropriés acceptés par les fonctions GROUP BY ?

Réponse : B. Les types de données pour les fonctions avec un argument peuvent être CHAR, VARCHAR2, NUMBER ou DATE.

6. Une table T_COUNT a 12 valeurs numériques comme 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Prédisez la sortie de la requête ci-dessous.

  1. 12
  2. 6
  3. 9
  4. Lève une exception car la fonction COUNT ne fonctionne pas avec les valeurs NULL

Réponse : A. Le COUNT (*) compte le nombre de lignes, y compris les doublons et les valeurs NULL. Utilisez les mots clés DISTINCT et ALL pour restreindre les valeurs en double et NULL.

7. Une table T_COUNT a 12 valeurs numériques comme 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Prédisez la sortie de la requête ci-dessous.

  1. 12
  2. 6
  3. 9
  4. Lève une exception car la fonction COUNT ne fonctionne pas avec les valeurs NULL

Réponse : C. COUNT (colonne) ignore les valeurs NULL mais compte les doublons.

8. Une table T_COUNT a 12 valeurs numériques comme 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Prédisez la sortie de la requête ci-dessous.

  1. 12
  2. 6
  3. 9
  4. Lève une exception car la fonction COUNT ne fonctionne pas avec les valeurs NULL

Réponse : C. COUNT(ALL column) ignore les valeurs NULL mais compte les doublons.

9. Une table T_COUNT a 12 valeurs numériques comme 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Prédisez la sortie de la requête ci-dessous.

  1. 12
  2. 6
  3. 9
  4. Lève une exception car la fonction COUNT ne fonctionne pas avec les valeurs NULL

Réponse : B. COUNT (colonne DISTINCT) compte les valeurs distinctes non nulles.

10. Que se passe-t-il lorsque la requête ci-dessous est exécutée dans SQL* Plus ?

  1. S'exécute avec succès et ne renvoie aucune sortie
  2. S'exécute avec succès et renvoie la sortie comme '1'
  3. Lève l'exception "ORA-00909 : nombre d'arguments invalide"
  4. Lève l'exception "ORA-00904: "COUNT": identifiant invalide" car la fonction COUNT ne fonctionne pas avec la table DUAL

Réponse : C. La fonction COUNT requiert au moins un argument qui peut être soit la colonne avec [ALL | DISTINCT] ou '*'.

11. Voici quelques instructions sur la fonction VARIANCE dans SQL.

je. La fonction accepte plusieurs entrées numériques et renvoie la variance de toutes les valeurs

ii. La fonction accepte une colonne numérique et renvoie la variance de toutes les valeurs de colonne, y compris les valeurs NULL

iii. La fonction accepte une colonne numérique et renvoie la variance de toutes les valeurs de colonne à l'exception des valeurs NULL

Choisissez la bonne combinaison parmi les options ci-dessous.

Réponse : C. La fonction VARIANCE accepte un seul argument numérique comme nom de colonne et renvoie la variance de toutes les valeurs de colonne en tenant compte des valeurs NULL.

12. Laquelle des extensions suivantes n'est PAS une extension GROUP BY en SQL ?

Réponse : A. Les opérations GROUPING SETS peuvent être utilisées pour effectuer plusieurs agrégations GROUP BY avec une seule requête.

13. Sélectionnez les déclarations correctes concernant la requête ci-dessous. Considérez la structure du tableau comme indiqué.

  1. SUM est un groupe par fonction car il traite un groupe d'employés travaillant dans un département
  2. SUM est une fonction d'agrégat car elle produit un résultat par groupe de données
  3. SUM est une fonction à une seule ligne car elle renvoie une valeur unique pour un groupe, c'est-à-dire un département
  4. SUM est une fonction de groupe par extension car elle utilise la clause GROUP BY pour regrouper logiquement les départements

Réponse : A. SUM est une fonction de groupe qui calcule la somme des salaires d'un groupe d'employés travaillant dans un service.

14. Quelle clause est utilisée pour filtrer la sortie de la requête en fonction des résultats agrégés à l'aide d'un groupe par fonction ?

Réponse : D. La clause HAVING est utilisée pour restreindre les résultats de groupe. Vous utilisez la clause HAVING pour spécifier les groupes à afficher, limitant ainsi davantage les groupes sur la base d'informations agrégées. La clause HAVING peut précéder la clause GROUP BY, mais il est recommandé de placer la clause GROUP BY en premier car elle est plus logique. Les groupes sont formés et les fonctions de groupe sont calculées avant que la clause HAVING ne soit appliquée aux groupes de la liste SELECT.

15. Examinez la structure de table donnée et prédisez le résultat de la requête suivante.

  1. La requête renvoie le nombre d'employés qui n'ont pas de commission
  2. La requête renvoie une erreur car le signe égal ne peut pas être utilisé lors de la recherche de la valeur NULL
  3. La requête renvoie le nombre d'employés dans un département dont la commission est la valeur NULL
  4. La requête renvoie une erreur car la clause GROUP BY est manquante dans la requête

Réponse : B. L'exclusion des valeurs NULL à l'aide de la condition WHERE est un moyen de demander à la requête d'ignorer les valeurs NULL. Mais ici, l'utilisation de l'opérateur IS NULL est erronée. La condition doit être « WHERE comm IS NULL ».

16. Laquelle des affirmations suivantes est vraie au sujet des fonctions de groupe ?

  1. La fonction MIN ne peut être utilisée qu'avec des données numériques.
  2. La fonction MAX ne peut être utilisée qu'avec des valeurs de date.
  3. La fonction AVG ne peut être utilisée qu'avec des données numériques.
  4. La fonction SOMME ne peut pas faire partie d'une fonction imbriquée.

Réponse : C. La fonction AVG ne peut être utilisée qu'avec des valeurs numériques. D'autres fonctions qui ont une telle restriction sont SUM, STDDEV et VARIANCE.

17. Lequel des énoncés suivants est une instruction SELECT valide ?

Réponse : A. Les alias de colonne ne peuvent pas être utilisés dans la clause GROUP BY ou HAVING.

18. Laquelle des affirmations suivantes est correcte ?

  1. La clause WHERE peut contenir une fonction de groupe uniquement si la fonction n'est pas également répertoriée dans la clause SELECT.
  2. Les fonctions de groupe ne peuvent pas être utilisées dans les clauses SELECT, FROM ou WHERE.
  3. La clause HAVING est toujours traitée avant la clause WHERE.
  4. La clause GROUP BY est toujours traitée avant la clause HAVING.

Réponse : D. Bien qu'Oracle ne génère pas d'erreur si la clause HAVING précède la clause GROUP BY, mais qu'elle n'est traitée qu'une fois la clause GROUP BY traitée et le groupe prêt à être filtré.

19. Lequel des énoncés suivants n'est pas une instruction SQL valide ?

20. Laquelle des affirmations suivantes est correcte ?

  1. La fonction COUNT peut être utilisée pour déterminer combien de lignes contiennent une valeur NULL.
  2. Seules des valeurs distinctes sont incluses dans les fonctions de groupe, sauf si le mot clé ALL est inclus dans la clause SELECT.
  3. La clause WHERE limite les lignes à traiter.
  4. La clause HAVING détermine quels groupes sont affichés dans les résultats de la requête.

Réponse : C, D. La clause WHERE restreint les lignes avant qu'elles ne soient regroupées et traitées tandis que la clause HAVING restreint les groupes.

21. Lequel des énoncés suivants est une instruction SQL valide ?

Réponse : B. La clause GROUP BY doit contenir toutes les colonnes sauf celle qui est utilisée dans la fonction de groupe.

22. Laquelle des déclarations SELECT suivantes répertorie uniquement le livre avec le plus grand profit ?

23. Lequel des énoncés suivants est/sont correct(s) ?

1. Une fonction de groupe peut être imbriquée dans une fonction de groupe.

2. Une fonction de groupe peut être imbriquée dans une fonction à une seule ligne.

3. Une fonction à une ligne peut être imbriquée dans une fonction de groupe.

Réponse : A, B, C. Les fonctions de groupe ne peuvent être imbriquées qu'à une profondeur de deux. Les fonctions de groupe peuvent être imbriquées dans des fonctions à une seule ligne (AVG intégré dans une fonction TO_CHAR). De plus, les fonctions à une seule ligne peuvent être imbriquées dans des fonctions de groupe.

24. Laquelle des fonctions suivantes est utilisée pour calculer la valeur totale stockée dans une colonne spécifiée ?

Réponse : D. La fonction SUM est utilisée pour obtenir l'addition de valeurs numériques.

25. Lequel des énoncés SELECT suivants indique le prix de vente au détail le plus élevé de tous les livres de la catégorie Famille ?

Réponse : A. Étant donné que la catégorie FAMILY doit être restreinte avant le regroupement, les lignes de la table doivent être filtrées à l'aide de la clause WHERE et non de la clause HAVING.

26. Laquelle des fonctions suivantes peut être utilisée pour inclure des valeurs NULL dans les calculs ?

Réponse : B.NVL est une fonction générale qui fournit des valeurs alternatives aux valeurs NULL. Cela peut vraiment faire une différence dans les calculs arithmétiques à l'aide des fonctions de groupe AVG, STDDEV et VARIANCE.

27. Lequel des énoncés suivants n'est pas une déclaration valide ?

  1. Vous devez entrer le mot clé ALL dans une fonction de groupe pour inclure toutes les valeurs en double.
  2. La fonction AVG peut être utilisée pour trouver la différence moyenne calculée entre deux dates.
  3. Les fonctions MIN et MAX peuvent être utilisées sur les colonnes VARCHAR2.
  4. Tout ce qui précède

Réponse : A. Le mot-clé ALL compte les doublons mais ignore les valeurs NULL. Les doublons sont également inclus avec « * » et la spécification du nom de la colonne.

28. Laquelle des instructions SQL suivantes détermine le nombre total de clients référés par d'autres clients ?

Réponse : B. Considérant tous les clients comme un seul groupe, COUNT(référé) ne comptera que ceux qui sont référés par quelqu'un. COUNT(référé) ignorera les valeurs NULL de la colonne.

29. Déterminez l'ordre d'exécution correct des clauses suivantes dans une instruction SELECT.

Réponse : A. L'ordre de traitement commence à partir de la clause FROM pour obtenir les noms de table, puis en restreignant les lignes à l'aide de la clause WHERE, en les regroupant à l'aide de la clause GROUP BY, en restreignant les groupes à l'aide de la clause HAVING. La clause ORDER BY est la dernière à être traitée pour trier l'ensemble de données final.

30. Laquelle des clauses ci-dessous est utilisée pour regrouper un ensemble de lignes en fonction d'une colonne ou d'un ensemble de colonnes ?

Réponse : C. La clause GROUP BY forme les groupes de données en fonction de la liste de colonnes spécifiée.

31. Laquelle des fonctions de groupe suivantes peut être utilisée pour les problèmes de variance de population et d'écart type de population ?

32. Sélectionnez les positions dans une requête SELECT où une fonction de groupe peut apparaître.

Réponse : A, C, D. Les fonctions de groupe peuvent apparaître dans les clauses SELECT, ORDER BY et HAVING. Oracle lève une exception si des fonctions de groupe sont utilisées dans les clauses WHERE ou GROUP BY.

33. Examinez la structure de la table EMPLOYEES telle qu'elle est donnée. Quelle requête renverra le salaire minimum dans chaque département ?

Réponse : B. La fonction MIN renvoie le salaire minimum dans un groupe formé par département.

34. Examinez la structure de la table EMPLOYEES et interprétez le résultat de la requête ci-dessous

  1. Il renvoie une erreur car une seule fonction d'agrégat peut être utilisée dans une requête.
  2. Il renvoie une erreur car la clause GROUP BY est manquante.
  3. Il s'exécute avec succès et renvoie les mêmes valeurs pour les deux.
  4. Il s'exécute avec succès où COUNT(*) y compris les valeurs NULL et COUNT (toutes les communications) à l'exclusion des valeurs NULL.

35. Parmi les propositions suivantes, lesquelles sont vraies au sujet des fonctions de groupe ?

  1. Vous pouvez utiliser des fonctions de groupe dans n'importe quelle clause d'une instruction SELECT.
  2. Vous ne pouvez utiliser les fonctions de groupe que dans la liste des colonnes de la clause select et dans la clause WHERE d'une instruction SELECT.
  3. Vous pouvez mélanger des colonnes à une seule ligne avec des fonctions de groupe dans la liste des colonnes d'une instruction SELECT en regroupant sur les colonnes à une seule ligne.
  4. Vous pouvez transmettre des noms de colonnes, des expressions, des constantes ou des fonctions en tant que paramètre à une fonction de groupe.

Réponse : C. Les fonctions de groupe ne peuvent être imbriquées qu'à une profondeur de deux. Les fonctions de groupe peuvent être imbriquées dans des fonctions à une seule ligne (AVG intégré dans une fonction TO_CHAR). De plus, les fonctions à une seule ligne peuvent être imbriquées dans des fonctions de groupe.

36. Examinez la structure du tableau EMPLOYÉS telle qu'elle est donnée. Vous souhaitez créer une vue "emp_dept_sales" en exécutant les instructions SQL suivantes.

Quelle affirmation est vraie concernant l'exécution de la déclaration ci-dessus ?

  1. La vue sera créée et vous pouvez effectuer des opérations DLM sur la vue
  2. La vue ne sera pas créée car les instructions de jointure ne sont pas autorisées pour créer une vue
  3. La vue ne sera pas créée car la clause GROUP BY n'est pas autorisée pour créer une vue
  4. La vue sera créée mais aucune opération DML ne sera autorisée sur la vue

Réponse : D. Règles d'exécution d'opérations DML sur une vue. Vous ne pouvez pas ajouter de données via une vue si la vue inclut des fonctions de groupe ou une clause GROUP BY ou un mot clé DISTINCT. Le mot-clé pseudo-colonne ROWNUM Colonnes définies par des expressions colonnes NOT NULL dans les tables de base qui ne sont pas sélectionnées par la vue.

37. Parmi les affirmations suivantes, lesquelles sont vraies concernant les opinions ?

  1. Une sous-requête qui définit une vue ne peut pas inclure la clause GROUP BY
  2. Une vue est créée avec la sous-requête ayant le mot-clé DISTINCT peut être mise à jour
  3. Une opération Data Manipulation Language (DML) peut être effectuée sur une vue qui est créée avec la sous-requête ayant toutes les colonnes NOT NULL d'une table
  4. Une vue créée avec la sous-requête ayant le mot-clé pseudo-colonne ROWNUM ne peut pas être mise à jour

Réponse : C, D. Règles d'exécution d'opérations DML sur une vue. Vous ne pouvez pas ajouter de données via une vue si la vue inclut des fonctions de groupe ou une clause GROUP BY ou un mot clé DISTINCT. Le mot-clé pseudo-colonne ROWNUM Colonnes définies par des expressions colonnes NOT NULL dans les tables de base qui ne sont pas sélectionnées par la vue.

38. Examinez la structure du tableau telle qu'elle est donnée.

Quelle clause dans la requête SQL ci-dessous génère une erreur ?

Réponse : D. La clause GROUP BY doit contenir toutes les colonnes apparaissant dans l'instruction SELECT. Cela génère une erreur car JOB n'est pas une colonne sélectionnée. Il aurait dû utiliser DEPARTMENT_ID à la place de JOB.

39. Examinez la structure du tableau telle qu'elle est donnée.

Laquelle des requêtes SELECT ci-dessous affichera le salaire maximum et minimum gagné par chaque catégorie d'emploi ?

Réponse : B. Plusieurs fonctions de groupe peuvent apparaître dans l'instruction SELECT.

40. Considérez la structure du tableau telle qu'elle est donnée.

Examinez l'erreur dans la requête ci-dessous.

  1. Il s'exécute avec succès et génère le résultat requis.
  2. Cela produit une erreur car COUNT(*) doit également être spécifié dans la clause SELECT.
  3. Il s'exécute avec succès mais ne produit aucun résultat car COUNT(prod_id) doit être utilisé au lieu de COUNT(*).
  4. Cela produit une erreur car COUNT(*) doit être uniquement dans la clause HAVING et non dans la clause WHERE.

Réponse : D. Les fonctions de groupe ne peuvent pas être utilisées dans la clause WHERE. Le peut apparaître dans les clauses SELECT, HAVING et ORDER BY.

41. Examinez la structure du tableau telle qu'elle est donnée.

Prédire le résultat de la requête ci-dessous

  1. Il s'exécute avec succès et répertorie le nombre d'employés dans chaque catégorie d'emploi, mais ignore la clause HAVING car « salaire » n'est pas dans la clause GROUP BY.
  2. Il renvoie une erreur car la clause HAVING n'est pas valide.
  3. Il renvoie une erreur car "salary" n'est pas inclus dans la clause GROUP BY.
  4. Il s'exécute avec succès et répertorie le nombre d'employés dans chaque catégorie ayant une somme de salaire supérieure à 5000.

Réponse : D. La clause HAVING restreint les résultats du groupe. La fonction COUNT est utilisée pour compter tandis que SUM est utilisée pour ajouter les valeurs numériques.

42. Qu'est-ce qui est vrai de l'utilisation de fonctions de groupe sur des colonnes qui contiennent des valeurs NULL ?

  1. Les fonctions de groupe sur les colonnes ignorent les valeurs NULL.
  2. Les fonctions de groupe sur les colonnes renvoyant des dates incluent des valeurs NULL.
  3. Les fonctions de groupe sur les colonnes renvoyant des nombres incluent des valeurs NULL.
  4. Les fonctions de groupe sur les colonnes ne peuvent pas être utilisées avec précision sur les colonnes qui contiennent des valeurs NULL.

Réponse : A. À l'exception de la fonction COUNT, toutes les fonctions de groupe ignorent les valeurs NULL.

43. Parmi les affirmations suivantes, lesquelles sont vraies concernant l'utilisation des colonnes GROUP BY dans une sous-requête ?

  1. Les sous-requêtes peuvent contenir des clauses GROUP BY et ORDER BY.
  2. Les sous-requêtes ne peuvent pas contenir de clauses GROUP BY et ORDER BY.
  3. Les sous-requêtes peuvent contenir ORDER BY mais pas la clause GROUP BY.
  4. Les sous-requêtes ne peuvent pas contenir ORDER BY mais peuvent avoir une clause GROUP BY.

Réponse : A. Comme la requête primaire, une sous-requête peut contenir une clause GROUP BY ainsi qu'une clause ORDER BY.

Examinez la structure du tableau telle qu'elle est donnée et répondez aux questions 44 à 49 qui suivent.

44. Prédire le résultat de la requête ci-dessous

  1. Il s'exécute avec succès.
  2. Il renvoie une erreur car la clause HAVING n'est pas valide.
  3. Il renvoie une erreur car l'expression GROUP BY n'est pas valide.
  4. Cela génère une erreur car les fonctions d'agrégat ne peuvent pas être imbriquées dans l'instruction SELECT.

Réponse : B. La clause HAVING ne permet pas l'imbrication de fonctions d'agrégat.

45. Prédire la sortie de la requête ci-dessous

  1. Cela donne une erreur car une fonction d'agrégat ne peut pas apparaître juste après la clause SELECT.
  2. Il génère une erreur car la clause GROUP BY n'est pas valide.
  3. Il s'exécute sans erreur mais ne produit aucune sortie.
  4. Il s'exécute avec succès et donne un salaire moyen dans chaque département.

Réponse : D. Les fonctions de groupe peuvent être utilisées dans n'importe quel ordre (avant ou après le regroupement par colonnes) dans une requête SELECT.

46. ​​Prédire la sortie de la requête ci-dessous

  1. Il s'exécute avec succès et affiche "job" en minuscules.
  2. Il s'exécute avec succès mais affiche "job" dans le cas d'origine.
  3. Il génère une erreur car les fonctions de ligne unique et d'agrégat ne peuvent pas être utilisées ensemble.
  4. Il renvoie une erreur car la conversion de casse dans la liste SELECT ne correspond pas à la clause GROUP BY de conversion de casse.

Réponse : D. La fonction LOWER, étant une fonction à une seule ligne, doit être spécifiée dans la clause GROUP BY pour baser le regroupement des données EMPLOYEES.

47. Laquelle des requêtes ci-dessous s'exécute avec succès ?

Réponse : B, C. La première requête exploite l'ensemble des données EMPLOYÉS tandis que la seconde traite les données par groupes de service.

48. Identifiez l'erreur dans l'instruction SELECT ci-dessous.

  1. Il s'exécute avec succès et affiche le salaire moyen des départements supérieur à 10.
  2. Il renvoie une erreur car la colonne non agrégée ne peut pas être utilisée dans la clause HAVING.
  3. Il s'exécute avec succès mais affiche un résultat erroné pour les départements.
  4. Il renvoie une erreur car la clause HAVING doit être placée avant la clause GROUP BY.

Réponse : A. Les expressions GROUP BY peuvent être utilisées dans la clause HAVING pour filtrer les groupes de l'ensemble de données final.

49. Prédire la sortie de la requête ci-dessous

  1. Il renvoie une erreur car plusieurs conditions ne peuvent pas être données dans la clause HAVING.
  2. Il renvoie une erreur car une colonne non agrégée ne peut pas être utilisée dans la clause HAVING.
  3. Il s'exécute avec succès et affiche le salaire moyen du département supérieur à 10 et supérieur à 2000.
  4. Il s'exécute avec succès mais aucun résultat ne s'affiche.

Réponse : C. La clause HAVING peut imposer plusieurs conditions jointes à l'aide de l'opérateur AND ou OR pour filtrer les groupes.

50. Laquelle des fonctions de groupe suivantes peut être utilisée avec les valeurs DATE ?

Réponse : B, D. La fonction de groupe AVG et SUM ne peut être utilisée qu'avec des données numériques.

51. Lesquelles des affirmations suivantes sont vraies ?

  1. AVG et SUM ne peuvent être utilisés qu'avec des types de données numériques.
  2. STDDEV et VARIANCE ne peuvent être utilisés qu'avec des types de données numériques.
  3. MAX peut être utilisé avec le type de données LONG.
  4. MAX et MIN ne peuvent pas être utilisés avec les types de données LOB ou LONG.

Réponse : A, B, D. Les fonctions de groupe AVG,SUM, VARIANCE et STDDEV ne peuvent être utilisées qu'avec des données numériques. Aucune des fonctions de groupe ne peut être utilisée avec le type de données LONG.

52. Examinez la structure du tableau telle qu'elle est donnée.

Identifiez l'erreur dans la requête ci-dessous.

  1. Plusieurs fonctions d'agrégat ne peuvent pas être utilisées dans une seule requête SELECT
  2. La clause GROUP BY n'est pas valide
  3. La fonction COUNT ne peut pas être utilisée avec les valeurs DATE
  4. Aucune erreur et il s'exécute avec succès

53. Laquelle des fonctions de groupe suivantes peut être utilisée avec les types de données LOB ?

Réponse : D. Aucune fonction d'agrégat ne peut être utilisée avec les types de données LOB.

54. Examinez la structure du tableau telle qu'elle est donnée.

Prédire la sortie des deux requêtes ci-dessous

  1. Les deux requêtes produisent le même résultat
  2. Query - 1 et Query - 2 produisent des résultats différents car Query-1 considère les valeurs NULL de COMM et Query-2 remplace les valeurs NULL de COMM par zéro
  3. Query - 1 produit une erreur car COMM a des valeurs NULL
  4. La requête - 2 génère une erreur car NVL ne peut pas être imbriqué avec la fonction d'agrégation.

Réponse : B. La fonction AVG ignore les valeurs NULL lors du calcul de la moyenne des données numériques. AVG (colonne) calculera la moyenne uniquement pour les valeurs non nulles. Cependant, si NVL est utilisé pour remplacer les valeurs NULL par un zéro, toutes les valeurs seront prises en compte.

55. Choisissez les instructions correctes concernant la clause GROUP BY.

  1. L'alias de colonne peut être utilisé dans la clause GROUP BY.
  2. La colonne GROUP BY doit être dans la clause SELECT.
  3. La clause GROUP BY doit apparaître avec la clause HAVING d'une requête SELECT.
  4. La clause GROUP BY doit apparaître après la clause WHERE dans une requête SELECT.

Réponse : D. Conformément à la séquence de traitement, la clause GROUP BY doit apparaître après la clause WHERE dans une requête SELECT.

56. Examinez la structure du tableau telle qu'elle est donnée.

Prédire le résultat de la requête ci-dessous

  1. Il renvoie une erreur car la liste de colonnes GROUP BY ne correspond pas à la liste de colonnes SELECT.
  2. Il s'exécute avec succès et produit le salaire moyen d'une catégorie d'emploi dans chaque département.
  3. Il s'exécute avec succès et produit un salaire moyen pour un département dans chaque catégorie d'emploi.
  4. Il renvoie une erreur car les clauses GROUP BY et ORDER BY ont une liste de colonnes différente.

Réponse : B. Bien que la clause GROUP BY trie implicitement les groupes, les clauses GROUP BY et ORDER BY peuvent être utilisées ensemble dans une requête.

57. Quelle clause devez-vous utiliser pour exclure les résultats de groupe dans une requête utilisant des fonctions de groupe ?

Réponse : B. La clause HAVING est utilisée pour restreindre les groupes.

Examinez la structure du tableau telle qu'elle est donnée et répondez aux questions 58 et 59 qui suivent.

58. Prédire le résultat de la requête ci-dessous

  1. Il s'exécute avec succès.
  2. Il renvoie une erreur car la clause HAVING précède la clause GROUP BY.
  3. Elle renvoie une erreur car la clause HAVING utilise la fonction d'agrégation.
  4. Il s'exécute mais aucun résultat n'est affiché car la clause HAVING précède la clause GROUP BY.

Réponse : A. La clause HAVING peut précéder la clause GROUP BY mais elle n'est traitée qu'après le calcul des résultats du groupe.

59. Prédire le résultat de la requête ci-dessous

  1. Elle renvoie une erreur car l'opérateur BETWEEN ne peut pas être utilisé dans la clause HAVING.
  2. Elle renvoie une erreur car les clauses WHERE et HAVING ne peuvent pas être utilisées dans la même instruction SELECT.
  3. Il renvoie une erreur car les clauses WHERE et HAVING ne peuvent pas être utilisées pour appliquer des conditions sur la même colonne.
  4. Il s'exécute avec succès.

Réponse : D. La clause WHERE limite le nombre de lignes participant au traitement de la clause de groupe.

60. Quelles affirmations sont vraies concernant les clauses WHERE et HAVING dans une instruction SELECT ?

  1. La clause HAVING peut être utilisée avec les fonctions de groupe dans les sous-requêtes.
  2. La clause WHERE peut être utilisée pour exclure des lignes après les avoir divisées en groupes.
  3. La clause WHERE peut être utilisée pour exclure des lignes avant de les diviser en groupes.
  4. Les clauses WHERE et HAVING peuvent être utilisées dans la même instruction uniquement si elles sont appliquées à des colonnes différentes de la table.

Réponse : A, C. Les clauses WHERE et HAVING peuvent être utilisées ensemble dans une requête. WHERE exclut les lignes avant le traitement du groupe tandis que HAVING restreint les groupes.

Examinez la structure du tableau telle qu'elle est donnée et répondez aux questions 61 et 62 qui suivent.

61. Prédisez le résultat de la requête ci-dessous.

  1. Il renvoie une erreur car les fonctions d'agrégat utilisées dans la clause HAVING doivent être dans la liste SELECT.
  2. Il renvoie une erreur car la clause HAVING apparaît avant la clause GROUP BY.
  3. Il affiche les départements dont le salaire moyen est supérieur au salaire minimum du département.
  4. Il affiche les départements dont le salaire moyen est supérieur au salaire minimum de l'organisation.

Réponse : C. Les fonctions de groupe peuvent être utilisées par la clause HAVING pour filtrer les groupes.

62. Interprétez la sortie de la requête ci-dessous.

  1. Il calcule la somme des moyennes de longueur du nom de l'employé dans chaque département.
  2. Il calcule la longueur moyenne du nom de l'employé dans chaque département.
  3. Il renvoie une erreur car la fonction de ligne unique ne peut pas être utilisée avec les fonctions de groupe.
  4. Il renvoie une erreur car la colonne de groupe DEPARTMENT_ID n'est pas utilisée dans la liste SELECT.

Réponse : A. Les fonctions de groupe peuvent être utilisées avec une seule ligne ou des fonctions générales dans la requête SELECT.

63. Jusqu'à combien de niveaux les fonctions de groupe peuvent-elles être imbriquées ?

Réponse : B. Les fonctions de groupe peuvent être imbriquées jusqu'à 2 niveaux maximum. Cependant, les fonctions à une seule ligne peuvent être imbriquées jusqu'à n'importe quel nombre de niveaux.

64. Quelle est la limite du nombre de groupes au sein des groupes créés par la clause GROUP BY ?

Réponse : D. Il n'y a pas de limite au nombre de groupes et de sous-groupes qui peuvent être formés.

65. Choisissez les affirmations correctes concernant la clause HAVING.

  1. La clause HAVING est une clause facultative dans l'instruction SELECT.
  2. La clause HAVING est une clause obligatoire si l'instruction SELECT utilise une clause GROUP BY.
  3. La clause HAVING ne peut apparaître dans une instruction SELECT que si elle utilise une clause GROUP BY.
  4. La clause HAVING est une clause obligatoire si l'instruction SELECT utilise une clause GROUP BY.

Réponse : A, C. La clause HAVING ne peut apparaître dans une requête que si la clause GROUP BY est présente, mais l'inverse n'est pas vrai.

66. Quelle est la sortie de la requête ci-dessous.

  1. 1
  2. 0
  3. NUL
  4. Lève une erreur car les fonctions de groupe ne peuvent pas être appliquées sur la table DUAL.

Réponse : A. La table DUAL contient une seule colonne DUMMY de type CHAR(1) dont la valeur est 'X'.

Sur la base du scénario ci-dessous, répondez à la question 67 à 74.

Une organisation compte 14 salariés qui travaillent avec un salaire fixe de 1000. L'entreprise recrute 5 nouveaux salariés dont le salaire n'est pas encore fixé par le service paie. Cependant, lors du traitement de fin de mois, le service de paie RH génère plusieurs rapports pour rapprocher les données financières de l'organisation. Examinez la structure du tableau comme indiqué.

67. Quelle est la sortie de la requête ci-dessous ?

Réponse : B. La fonction SOMME ajoute les salaires des employés.

68. Quelle est la sortie de la requête ci-dessous ?

Réponse : A. La fonction AVG (salaire) calcule la moyenne des salaires en ignorant les valeurs NULL. Dans ce cas, AVG(salaire)=(14*1000)/14=1000.

69. Quelle est la sortie de la requête ci-dessous ?

Réponse : C. L'AVG(NVL(salary ,0)) donne une valeur alternative aux NULL et leur permet de participer au calcul de la moyenne. Dans ce cas, (14*1000)/19 = 736,84.

70. Quelle est la sortie de la requête ci-dessous ?

Réponse : B. La VARIANCE (salaire) calcule la variance des valeurs de la colonne salaire en ignorant les valeurs NULL.

71. Quelle est la sortie de la requête ci-dessous ?

Réponse : D. La VARIANCE (NL(salary ,0)) calcule la variance des valeurs de la colonne des salaires, y compris les valeurs NULL.

72. Quelle est la sortie de la requête ci-dessous ?

Réponse : C. Le STDDEV (salaire) calcule l'écart type des valeurs de la colonne des salaires en ignorant les valeurs NULL.

73. Quelle est la sortie de la requête ci-dessous ?

Réponse : B. Le STDDEV (nvl(salary ,0)) calcule l'écart type des valeurs de la colonne des salaires, y compris les valeurs NULL.

74. Quelle est la sortie de la requête ci-dessous ?

sélectionnez count(*),count(salary ) parmi les employés

Réponse : C. COUNT(*) inclut les valeurs NULL tandis que COUNT(salary ) ignore les valeurs NULL.

75. Examinez la structure du tableau telle qu'elle est donnée.

Laquelle des requêtes ci-dessous donnera au service qui compte plus de 5 employés ?

76. Parmi les propositions suivantes, lesquelles sont vraies à propos de l'extension CUBE de GROUP BY ?

  1. Permet d'effectuer plusieurs clauses GROUP BY avec une seule requête.
  2. Effectue des agrégations pour toutes les combinaisons possibles de colonnes incluses.
  3. Effectue des niveaux croissants de sous-totaux cumulatifs, en fonction de la liste de colonnes fournie.
  4. Aucune de ces réponses

Réponse : B. CUBE, ROLLUP sont les extensions GROUP BY utilisées pour le traitement OLAP. CUBE agrège les résultats chaque fois qu'une nouvelle permutation de colonne est formée.

Utilisez l'instruction SELECT suivante pour répondre aux questions 77 à 82 ci-dessous :

77. Quelle ligne de l'instruction SELECT est utilisée pour restreindre le nombre d'enregistrements traités par la requête ?

Réponse : B. La clause WHERE est utilisée pour restreindre les lignes avant la formation des groupes.

78. Quelle ligne de l'instruction SELECT est utilisée pour restreindre les groupes affichés dans les résultats de la requête ?

Réponse : D. HAVING est utilisé pour restreindre les résultats du groupe une fois le traitement du groupe terminé.

79. Quelle ligne de l'instruction SELECT est utilisée pour regrouper les données stockées dans la base de données ?

Réponse : C. La clause GROUP BY utilise les colonnes group by pour regrouper les données dans la table.

80. Quelle clause doit être incluse pour que la requête s'exécute correctement ?

Réponse : C. Étant donné que la clause SELECT contient la colonne CUSTOMER#, il est obligatoire d'avoir la clause GROUP BY avec la colonne CUSTOMER#.

81. Quel est le but d'utiliser COUNT(*) dans la requête SELECT ?

  1. Le nombre d'enregistrements dans les tables spécifiées
  2. Le nombre de commandes passées par chaque client
  3. Le nombre de valeurs NULL dans les tables spécifiées
  4. Le nombre de clients qui ont passé une commande

Réponse : B. Il compte le nombre de lignes traitées sous un groupe. Dans ce cas, le groupe est formé par le client et COUNT(*) compte les commandes passées par chaque client.

82. Laquelle des fonctions suivantes peut être utilisée pour déterminer la première date d'expédition pour toutes les commandes récemment traitées par JustLee Books ?

Réponse : C. La fonction MIN est utilisée pour récupérer la plus petite valeur de la colonne. Lorsqu'il est utilisé avec des colonnes de date, il récupère la date minimale de la colonne.

83. Lequel des énoncés suivants n'est pas une instruction SELECT valide ?

Réponse : D. La clause GROUP BY doit spécifier une colonne ou un ensemble de colonnes contenues dans la clause SELECT. Ici, PUBID n'est pas contenu dans la clause SELECT, donc la requête n'est pas valide.

84. Lesquelles des affirmations ci-dessous sont vraies concernant l'imbrication des fonctions de groupe ?

  1. La fonction la plus interne est résolue en premier.
  2. Oracle permet l'imbrication de la fonction de groupe jusqu'à 3 niveaux.
  3. Les fonctions à ligne unique peuvent être imbriquées avec les fonctions de groupe.
  4. Oracle permet l'imbrication de la fonction de groupe jusqu'à 2 niveaux.

Réponse : A, C, D. Dans une expression contenant des fonctions imbriquées, la fonction la plus interne est exécutée en premier dont le résultat est introduit dans la fonction suivante se déplaçant vers l'extérieur. Les fonctions à une rangée peuvent être bien utilisées avec les fonctions de groupe qui peuvent être imbriquées au maximum jusqu'à 2 niveaux.

85. Quelles sont les fonctions de groupe statistique dans Oracle ?

Réponse : B, C. VARIANCE et STATS sont les fonctions de groupe statistique disponibles dans Oracle SQL.

86. Si la liste SELECT contient une colonne et des fonctions de groupe, laquelle des clauses suivantes doit être obligatoirement incluse ?

Réponse : C. La clause GROUP BY doit nécessairement contenir la colonne ou l'ensemble de colonnes contenu dans la clause SELECT.

87. Examinez la structure du tableau telle qu'elle est donnée.

Quelle est la meilleure explication pour laquelle cette instruction SQL ne s'exécutera PAS ?

  1. Les salaires ne peuvent pas être calculés en moyenne car tous les nombres ne se diviseront pas également.
  2. Vous ne pouvez pas utiliser un alias de colonne dans la clause GROUP BY.
  3. La clause GROUP BY doit avoir quelque chose à GROUP.
  4. L'ID du département n'est pas répertorié dans la table des départements.

Réponse : B. Ni la clause GROUP BY ni la clause HAVING ne fonctionnent avec les alias de colonne.

88.Parmi les types de données suivants, lesquels sont compatibles avec les fonctions AVG, SUM, VARIANCE et STDDEV ?

  1. Uniquement les types de données numériques
  2. Entiers uniquement
  3. Tout type de données
  4. Tout sauf numérique

Réponse : A. Les fonctions AVG, SUM, VARIANCE et STDDEV fonctionnent obligatoirement avec des données de type numérique uniquement.

Examinez la structure du tableau ci-dessous et répondez aux questions 89 et 90 qui suivent.

89. Laquelle des requêtes ci-dessous affichera le nombre de catégories d'emplois distinctes travaillant dans chaque département ?

Réponse : D. Utilisez le modificateur DISTINCT pour filtrer les doublons.

90. Évaluez cette instruction SQL :

Pourquoi cette déclaration provoquera-t-elle une erreur ?

  1. La clause HAVING est manquante.
  2. La clause WHERE contient une erreur de syntaxe.
  3. La colonne SALARY n'est PAS incluse dans la clause GROUP BY.
  4. La colonne HIRE_DATE n'est PAS incluse dans la clause GROUP BY.

Réponse : D. Toutes les colonnes apparaissant dans les clauses SELECT et ORDER BY doivent être incluses dans la clause GROUP BY.

91. Laquelle des affirmations suivantes est vraie à propos de la clause GROUP BY ?

  1. Pour exclure des lignes avant de les diviser en groupes à l'aide de la clause GROUP BY, vous devez utiliser une clause WHERE.
  2. Vous devez utiliser la clause HAVING avec la clause GROUP BY.
  3. L'alias de colonne peut être utilisé dans une clause GROUP BY.
  4. Par défaut, les lignes ne sont pas triées lorsqu'une clause GROUP BY est utilisée.

Réponse : A. À l'aide d'une clause WHERE, vous pouvez exclure des lignes avant de les diviser en groupes.

92. Examinez la structure du tableau telle qu'elle est donnée.

Interprétez le résultat de la requête ci-dessous.

  1. La première date d'embauche dans l'organisation.
  2. La dernière date d'embauche dans l'organisation.
  3. La première date d'embauche dans un département.
  4. La dernière date d'embauche dans un département.

Réponse : C. La requête renvoie le premier employé embauché dans chaque service.

93. Quelle affirmation sur les fonctions de groupe est vraie ?

  1. Les fonctions de groupe sauf COUNT(*), ignorent les valeurs nulles.
  2. Une requête qui inclut une fonction de groupe dans la liste SELECT doit inclure une clause GROUP BY.
  3. Les fonctions de groupe peuvent être utilisées dans une clause WHERE.
  4. Les fonctions de groupe ne peuvent être utilisées que dans une liste SELECT.

Réponse : A. Toutes les fonctions de groupe, à l'exception de COUNT(*), ignorent les valeurs NULL. C'est parce qu'ils traitent les valeurs directement contenues dans une colonne spécifique.

94. Laquelle des clauses suivantes représente des utilisations valides des fonctions de groupe ?

  1. GROUPER PAR MAX (salaire)
  2. ORDRE PAR AVG (salaire)
  3. AVOIR MAX(salaire) > 10000
  4. SELECT AVG(NVL(salaire, 0))

Réponse : B, C, D. Les fonctions de groupe ne peuvent apparaître que dans les clauses SELECT, HAVING et ORDER BY.

95. Parmi les affirmations suivantes, lesquelles sont vraies à propos de la clause GROUP BY ?

  1. La dernière colonne répertoriée dans la clause GROUP BY est le regroupement le plus important.
  2. La première colonne répertoriée dans la clause GROUP BY est le regroupement le plus important.
  3. Une clause GROUP BY ne peut pas être utilisée sans clause ORDER BY.
  4. La clause GROUP BY n'assure pas le tri des sorties.

Réponse : B. Le regroupement des données est basé sur la séquence de colonnes apparaissant dans la clause GROUP BY.

96. Quelle est la différence entre la clause WHERE et la clause HAVING ?

  1. La clause WHERE restreint les lignes avant le regroupement tandis que la clause HAVING restreint les groupes.
  2. La clause WHERE ne peut pas contenir de fonction de groupe mais la clause HAVING peut en avoir.
  3. La clause WHERE peut joindre plusieurs conditions à l'aide des opérateurs AND ou OR, mais la clause HAVING ne le peut pas.
  4. La clause WHERE peut apparaître dans la requête SELECT sans la clause GROUP BY mais la clause HAVING ne le peut pas.

Réponse : A, B, D. La clause WHERE restreint les lignes avant le regroupement mais HAVING restreint les groupes.

97. Examinez la structure du tableau telle qu'elle est donnée.

Prédisez le résultat de la requête ci-dessous.

  1. Il s'exécute avec succès.
  2. Il renvoie une erreur car la clause ORDER BY n'est pas valide.
  3. Il renvoie une erreur car la clause GROUP BY n'est pas valide.
  4. Il renvoie une erreur car les clauses GROUP BY et ORDER BY ne peuvent pas être utilisées ensemble.

Réponse : A. La clause ORDER BY peut utiliser les fonctions de groupe pour le tri.


Exemples de GROUP BY MySQL

Prenons un exemple d'utilisation de la clause GROUP BY.

A) Exemple simple de GROUP BY MySQL

Jetons un coup d'œil à la table des commandes dans l'exemple de base de données.

Supposons que vous souhaitiez regrouper les valeurs de l'état de la commande en sous-groupes, vous utilisez la clause GROUP BY avec la colonne d'état comme requête suivante :

Comme vous pouvez le voir, la clause GROUP BY renvoie des occurrences uniques de valeurs d'état. Il fonctionne comme l'opérateur DISTINCT comme indiqué dans la requête suivante :

B) Utilisation de MySQL GROUP BY avec des fonctions d'agrégat

Les fonctions d'agrégat permettent d'effectuer le calcul d'un ensemble de lignes et de renvoyer une valeur unique. La clause GROUP BY est souvent utilisée avec une fonction d'agrégat pour effectuer un calcul et renvoyer une valeur unique pour chaque sous-groupe.

Par exemple, si vous souhaitez connaître le nombre de commandes dans chaque statut, vous pouvez utiliser la fonction COUNT avec la clause GROUP BY comme suit :

Voir le tableau des commandes et des détails de commande suivant.

Pour obtenir le montant total de toutes les commandes par statut, vous joignez le tableau des commandes au tableau des détails des commandes et utilisez la fonction SOMME pour calculer le montant total. Voir la requête suivante :

De même, la requête suivante renvoie les numéros de commande et le montant total de chaque commande.

C) MySQL GROUP BY avec exemple d'expression

En plus des colonnes, vous pouvez regrouper les lignes par expressions. La requête suivante obtient le total des ventes pour chaque année.

Dans cet exemple, nous avons utilisé la fonction YEAR pour extraire les données de l'année de la date de commande ( orderDate ). Nous n'avons inclus que les commandes avec le statut d'expédition dans le total des ventes. Notez que l'expression qui apparaît dans la clause SELECT doit être la même que celle de la clause GROUP BY.

D) Utilisation de MySQL GROUP BY avec l'exemple de clause HAVING

Pour filtrer les groupes renvoyés par la clause GROUP BY, vous utilisez une clause HAVING. La requête suivante utilise la clause HAVING pour sélectionner le total des ventes des années postérieures à 2003.


Exercices SQL, Pratique, Solution - SOUS-REQUÊTES

14. Écrivez une requête pour trouver les sommes des montants du tableau des commandes, regroupées par date, en éliminant toutes les dates où la somme n'était pas au moins de 1000.00 au-dessus du montant maximum de la commande pour cette date. Aller à l'éditeur

Exemple de tableau : Commandes

Exemple de tableau : Client

15. Ecrivez une requête pour extraire toutes les données de la table des clients si et seulement si un ou plusieurs des clients de la table des clients sont situés à Londres. Aller à l'éditeur

Exemple de tableau : Client

16. Rédigez une requête pour trouver les vendeurs qui ont plusieurs clients. Aller à l'éditeur

Exemple de tableau : Client

Exemple de tableau : Vendeur

17. Rédigez une requête pour trouver tous les vendeurs qui ont travaillé pour un seul client. Aller à l'éditeur

Exemple de tableau : Client

Exemple de tableau : Vendeur

18. Écrivez une requête qui extrait les lignes de tous les vendeurs qui ont des clients avec plusieurs commandes. Aller à l'éditeur

Exemple de tableau : Vendeur

Exemple de tableau : Commandes

Exemple de tableau : Client

19. Écrivez une requête pour trouver des vendeurs avec toutes les informations qui vivent dans la ville où vit l'un des clients. Aller à l'éditeur

Exemple de tableau : Vendeur

Exemple de tableau : client

20. Ecrivez une requête pour trouver tous les vendeurs pour lesquels il y a des clients qui les suivent. Aller à l'éditeur

Exemple de tableau : Vendeur

Exemple de tableau : client

21. Ecrivez une requête pour afficher les vendeurs dont le nom est alphabétiquement inférieur au nom des clients. Aller à l'éditeur

Exemple de tableau : Vendeur

Exemple de tableau : Client

22. Écrivez une requête pour afficher les clients qui ont une gradation plus élevée que n'importe quel client appartenant à l'ordre alphabétique inférieur à la ville de New York. Aller à l'éditeur

Exemple de tableau : Vendeur

Exemple de tableau : Client

23. Rédigez une requête pour afficher toutes les commandes dont les montants étaient supérieurs à au moins une des commandes au 10 septembre 2012. Accéder à l'éditeur

Exemple de tableau : Commandes

24. Écrivez une requête pour trouver toutes les commandes d'un montant inférieur à n'importe quel montant pour un client à Londres. (En utilisant N'IMPORTE QUEL mot-clé) Accédez à l'éditeur

Exemple de tableau : Commandes

Exemple de tableau : Client

25. Écrivez une requête pour afficher toutes les commandes dont le montant est inférieur au montant maximum pour un client à Londres. Aller à l'éditeur

Exemple de tableau : Commandes

Exemple de tableau : Client

26. Écrivez une requête pour afficher uniquement les clients dont la note est, en fait, supérieure à celle de tous les clients de New York. Aller à l'éditeur

Exemple de tableau : Client

27. Écrivez une requête dans SQL pour trouver le nom, la ville et le montant total des commandes qu'un vendeur collecte. Le vendeur doit appartenir aux villes où appartient l'un des clients. Aller à l'éditeur

Exemple de tableau : Vendeur

Exemple de tableau : Client

28. Écrivez une requête pour obtenir toutes les informations sur les clients dont le grade n'est pas le grade du client qui appartient à la ville de Londres. Aller à l'éditeur

Exemple de tableau : Client

29. Ecrivez une requête pour trouver tous les clients dont la note ne correspond pas à la note, appartient à la ville de Paris. Aller à l'éditeur

Exemple de tableau : Client

30. Écrivez une requête pour trouver tous ces clients qui détiennent une note différente de celle de n'importe quel client de la ville de Dallas. Aller à l'éditeur

Exemple de tableau : Client

31. Écrivez une requête SQL pour trouver le prix moyen des produits de chaque fabricant ainsi que leur nom.

Exemple de tableau : company_mast

Exemple de tableau : item_mast

32. Ecrivez une requête SQL pour afficher le prix moyen des produits supérieur ou égal à 350 ainsi que leurs noms.

Exemple de tableau : company_mast

Exemple de tableau : item_mast

33. Écrivez une requête SQL pour afficher le nom de chaque entreprise, le prix de leur produit le plus cher ainsi que leur nom.

Exemple de tableau : company_mast

Exemple de tableau : item_mast

34. Ecrivez une requête en SQL pour trouver tous les détails des employés dont le nom de famille est Gabriel ou Dosio.

Exemple de tableau : emp_details

35. Ecrivez une requête en SQL pour afficher tous les détails des employés qui travaillent dans le département 89 ou 63.

Exemple de tableau : emp_department

Exemple de tableau : emp_details

36. Ecrivez une requête en SQL pour afficher le prénom et le nom des employés travaillant pour le département dont le montant alloué est supérieur à Rs.50000.

Exemple de tableau : emp_department

Exemple de tableau : emp_details

37. Ecrivez une requête en SQL pour trouver les départements dont le montant de la sanction est supérieur au montant moyen de la sanction de tous les départements.

Exemple de tableau : emp_department

38. Écrivez une requête en SQL pour trouver les noms des départements avec plus de deux employés qui travaillent.

Exemple de tableau : emp_department

Exemple de tableau : emp_details

39. Écrivez une requête en SQL pour trouver le prénom et le nom des employés travaillant pour les départements dont le montant de la sanction est le deuxième plus bas.

Exemple de tableau : emp_department

Exemple de tableau : emp_details


Requête d'écriture pour sélectionner les valeurs MIN et MAX par groupes ? - Systèmes d'information géographique

Vous pouvez utiliser le mysqld options et variables système qui sont décrites dans cette section pour affecter le fonctionnement du journal binaire ainsi que pour contrôler quelles instructions sont écrites dans le journal binaire. Pour plus d'informations sur le journal binaire, reportez-vous à la Section 5.4.4, « Le journal binaire ». Pour plus d'informations sur l'utilisation des options du serveur MySQL et des variables système, consultez la Section 5.1.6, « Options de commande du serveur » et la Section 5.1.7, « Variables système du serveur ».

Options de démarrage utilisées avec la journalisation binaire

La liste suivante décrit les options de démarrage pour l'activation et la configuration du journal binaire. Les variables système utilisées avec la journalisation binaire sont abordées plus loin dans cette section.

Format de ligne de commande --binlog-row-event-max-size=#
Taper Entier
Valeur par défaut 8192
Valeur minimum 256
Valeur maximale (plateformes 64 bits) 18446744073709551615
Valeur maximale (plateformes 32 bits) 4294967295

Spécifiez la taille maximale d'un événement de journal binaire basé sur des lignes, en octets. Les lignes sont regroupées en événements plus petits que cette taille si possible. La valeur doit être un multiple de 256. Voir Section 17.1.2, « Formats de réplication ».

Active la journalisation binaire. Lorsque la journalisation binaire est activée, le serveur consigne toutes les instructions qui modifient les données dans le journal binaire, qui est utilisé pour la sauvegarde et la réplication. Le journal binaire est une séquence de fichiers avec un nom de base et une extension numérique. Pour plus d'informations sur le format et la gestion du journal binaire, reportez-vous à la Section 5.4.4, « Le journal binaire ».

La valeur de l'option, si elle est donnée, est le nom de base de la séquence de journal. Le serveur crée des fichiers journaux binaires en séquence en ajoutant un suffixe numérique au nom de base. Il est recommandé de spécifier un nom de base (voir Section B.3.7, « Problèmes connus dans MySQL », pour la raison). Sinon, MySQL utilise nom_hôte -bin comme nom de base.

Si vous fournissez une valeur pour l'option --log-bin, la valeur est utilisée comme nom de base pour la séquence de journal. Le serveur crée des fichiers journaux binaires en séquence en ajoutant un suffixe numérique au nom de base. Dans MySQL 5.6, le nom de base par défaut est le nom du fichier d'ID de processus, avec le suffixe -bin . Ce nom peut être défini avec l'option --pid-file, et il prend par défaut le nom de la machine hôte. Il est recommandé de spécifier un nom de base à l'aide de l'option --log-bin, afin de pouvoir continuer à utiliser les mêmes noms de fichiers journaux binaires quelles que soient les modifications apportées au nom par défaut.

L'emplacement par défaut des fichiers journaux binaires est le répertoire de données. Vous pouvez utiliser l'option --log-bin pour spécifier un autre emplacement, en ajoutant un nom de chemin absolu de début au nom de base pour spécifier un répertoire différent. Lorsque le serveur lit une entrée du fichier d'index du journal binaire, qui suit les fichiers journaux binaires qui ont été utilisés, il vérifie si l'entrée contient un chemin relatif. Si c'est le cas, la partie relative du chemin est remplacée par le chemin absolu défini à l'aide de l'option --log-bin. Un chemin absolu enregistré dans le fichier d'index du journal binaire reste inchangé dans un tel cas, le fichier d'index doit être édité manuellement pour permettre l'utilisation d'un ou de plusieurs nouveaux chemins. (Dans les anciennes versions de MySQL, une intervention manuelle était nécessaire à chaque déplacement du journal binaire ou des fichiers journaux de relais.) (Bogue #11745230, Bug #12133)

La définition de cette option entraîne la définition de la variable système log_bin sur ON (ou 1 ), et non sur le nom de base. Le nom de base du fichier journal binaire et tout chemin spécifié sont disponibles en tant que variable système log_bin_basename.

Si vous souhaitez désactiver la journalisation binaire pour un démarrage de serveur mais conserver le paramètre --log-bin intact, vous pouvez spécifier l'option --skip-log-bin ou --disable-log-bin au démarrage. Spécifiez l'option après l'option --log-bin, afin qu'elle soit prioritaire. Lorsque la journalisation binaire est désactivée, la variable système log_bin est définie sur OFF.

Nom du fichier d'index du journal binaire, qui contient les noms des fichiers journaux binaires. Par défaut, il a le même emplacement et le même nom de base que la valeur spécifiée pour les fichiers journaux binaires à l'aide de l'option --log-bin, plus l'extension .index . Si vous ne spécifiez pas --log-bin , le nom du fichier d'index de journal binaire par défaut est binlog.index . Si vous omettez le nom du fichier et n'en spécifiez pas avec --log-bin , le nom du fichier d'index du journal binaire par défaut est nom_hôte -bin.index , en utilisant le nom de la machine hôte.

Pour plus d'informations sur le format et la gestion du journal binaire, reportez-vous à la Section 5.4.4, « Le journal binaire ».

Options de sélection de relevé. Les options de la liste suivante affectent les instructions écrites dans le journal binaire et donc envoyées par un serveur source de réplication à ses répliques. Il existe également des options pour les serveurs répliques qui contrôlent les instructions reçues de la source qui doivent être exécutées ou ignorées. Pour plus de détails, reportez-vous à la Section 17.1.4.3, « Options et variables du serveur de réplication ».

Cette option affecte la journalisation binaire de la même manière que --replicate-do-db affecte la réplication.

Les effets de cette option dépendent de l'utilisation du format de journalisation basé sur les instructions ou sur les lignes, de la même manière que les effets de --replicate-do-db dépendent de la réplication basée sur les instructions ou sur les lignes. utilisation. Vous devez garder à l'esprit que le format utilisé pour enregistrer une instruction donnée peut ne pas être nécessairement le même que celui indiqué par la valeur de binlog_format . Par exemple, les instructions DDL telles que CREATE TABLE et ALTER TABLE sont toujours enregistrées en tant qu'instructions, quel que soit le format de journalisation en vigueur, de sorte que les règles basées sur les instructions suivantes pour --binlog-do-db s'appliquent toujours pour déterminer si oui ou non le l'instruction est enregistrée.

Journalisation basée sur des instructions. Seules ces instructions sont écrites dans le journal binaire où la base de données par défaut (c'est-à-dire celle sélectionnée par USE ) est nom_base . Pour spécifier plus d'une base de données, utilisez cette option plusieurs fois, une fois pour chaque base de données. ne pas provoquer des déclarations de bases de données croisées telles que UPDATE some_db.some_table SET foo='bar' à enregistrer lorsqu'une base de données différente (ou aucune base de données) est sélectionnée.

Pour spécifier plusieurs bases de données, vous doit utiliser plusieurs instances de cette option. Étant donné que les noms de base de données peuvent contenir des virgules, la liste est traitée comme le nom d'une seule base de données si vous fournissez une liste séparée par des virgules.

Un exemple de ce qui ne fonctionne pas comme prévu lors de l'utilisation de la journalisation basée sur des instructions : si le serveur est démarré avec --binlog-do-db=sales et que vous émettez les instructions suivantes, l'instruction UPDATE est ne pas connecté :

La principale raison de ce comportement de « vérifier simplement la base de données par défaut » est qu'il est difficile à partir de l'instruction seule de savoir si elle doit être répliquée (par exemple, si vous utilisez des instructions DELETE à plusieurs tables ou des instructions UPDATE à plusieurs tables qui agissent sur plusieurs bases de données). Il est également plus rapide de vérifier uniquement la base de données par défaut plutôt que toutes les bases de données si cela n'est pas nécessaire.

Un autre cas qui peut ne pas être évident se produit lorsqu'une base de données donnée est répliquée même si elle n'a pas été spécifiée lors de la définition de l'option. Si le serveur est démarré avec --binlog-do-db=sales , l'instruction UPDATE suivante est enregistrée même si les prix n'ont pas été inclus lors de la définition de --binlog-do-db :

Étant donné que sales est la base de données par défaut lorsque l'instruction UPDATE est émise, l'UPDATE est consignée.

Journalisation basée sur les lignes. La journalisation est limitée à la base de données nom_base . Seules les modifications apportées aux tables appartenant à nom_base sont enregistrés, la base de données par défaut n'a aucun effet sur cela. Supposons que le serveur est démarré avec --binlog-do-db=sales et que la journalisation basée sur les lignes est en vigueur, puis les instructions suivantes sont exécutées :

Les modifications apportées à la table de février dans la base de données des ventes sont consignées conformément à l'instruction UPDATE. Cela se produit, que l'instruction USE ait été émise ou non. Cependant, lorsque vous utilisez le format de journalisation basé sur les lignes et --binlog-do-db=sales , les modifications apportées par la MISE À JOUR suivante ne sont pas enregistrées :

Même si l'instruction prix USE était changée en ventes USE , les effets de l'instruction UPDATE ne seraient toujours pas écrits dans le journal binaire.

Une autre différence importante dans la gestion de --binlog-do-db pour la journalisation basée sur les instructions par rapport à la journalisation basée sur les lignes se produit en ce qui concerne les instructions faisant référence à plusieurs bases de données. Supposons que le serveur soit démarré avec --binlog-do-db=db1 et que les instructions suivantes soient exécutées :

Si vous utilisez la journalisation basée sur des instructions, les mises à jour des deux tables sont écrites dans le journal binaire. Cependant, lors de l'utilisation du format basé sur les lignes, seules les modifications apportées à table1 sont consignées. table2 se trouve dans une base de données différente, elle n'est donc pas modifiée par UPDATE . Supposons maintenant qu'au lieu de l'instruction USE db1, une instruction USE db4 ait été utilisée :

Dans ce cas, l'instruction UPDATE n'est pas écrite dans le journal binaire lors de l'utilisation de la journalisation basée sur les instructions. Cependant, lors de l'utilisation de la journalisation basée sur les lignes, la modification de table1 est enregistrée, mais pas celle de table2 - en d'autres termes, seules les modifications apportées aux tables de la base de données nommée par --binlog-do-db sont enregistrées et le choix de la valeur par défaut base de données n'a aucun effet sur ce comportement.

Cette option affecte la journalisation binaire d'une manière similaire à la façon dont --replicate-ignore-db affecte la réplication.

Les effets de cette option dépendent de l'utilisation du format de journalisation basé sur les instructions ou sur les lignes, de la même manière que les effets de --replicate-ignore-db dépendent de la réplication basée sur les instructions ou sur les lignes. utilisation. Vous devez garder à l'esprit que le format utilisé pour enregistrer une instruction donnée peut ne pas être nécessairement le même que celui indiqué par la valeur de binlog_format . Par exemple, les instructions DDL telles que CREATE TABLE et ALTER TABLE sont toujours enregistrées en tant qu'instructions, quel que soit le format de journalisation en vigueur, de sorte que les règles basées sur les instructions suivantes pour --binlog-ignore-db s'appliquent toujours pour déterminer si oui ou non le l'instruction est enregistrée.

Journalisation basée sur des instructions. Indique au serveur de ne consigner aucune instruction où se trouve la base de données par défaut (c'est-à-dire celle sélectionnée par USE ) nom_base .

Avant MySQL 5.6.12, cette option empêchait les instructions contenant des noms de table complets d'être consignées si aucune base de données par défaut n'était spécifiée (c'est-à-dire lorsque SELECT DATABASE() renvoyait NULL ). Dans MySQL 5.6.12 et versions ultérieures, lorsqu'il n'y a pas de base de données par défaut, aucune option --binlog-ignore-db n'est appliquée et ces déclarations sont toujours enregistrées. (Bogue #11829838, Bogue #60188)

Format basé sur des lignes. Indique au serveur de ne pas enregistrer les mises à jour des tables de la base de données nom_base . La base de données actuelle n'a aucun effet.

Lors de l'utilisation de la journalisation basée sur des instructions, l'exemple suivant ne fonctionne pas comme prévu. Supposons que le serveur démarre avec --binlog-ignore-db=sales et que vous émettez les instructions suivantes :

L'instruction UPDATE est enregistré dans un tel cas car --binlog-ignore-db s'applique uniquement à la base de données par défaut (déterminée par l'instruction USE). Étant donné que la base de données des ventes a été spécifiée explicitement dans le relevé, le relevé n'a pas été filtré. Cependant, lors de l'utilisation de la journalisation basée sur les lignes, les effets de l'instruction UPDATE sont ne pas écrit dans le journal binaire, ce qui signifie qu'aucune modification de la table sales.january n'est enregistrée dans cette instance, --binlog-ignore-db=sales provoque tous les modifications apportées aux tables dans la copie source de la base de données des ventes doivent être ignorées à des fins de journalisation binaire.

Pour spécifier plusieurs bases de données à ignorer, utilisez cette option plusieurs fois, une fois pour chaque base de données. Étant donné que les noms de base de données peuvent contenir des virgules, la liste est traitée comme le nom d'une seule base de données si vous fournissez une liste séparée par des virgules.

Vous ne devez pas utiliser cette option si vous utilisez des mises à jour de bases de données croisées et que vous ne souhaitez pas que ces mises à jour soient enregistrées.

Options de somme de contrôle. MySQL prend en charge la lecture et l'écriture des sommes de contrôle des journaux binaires. Ceux-ci sont activés à l'aide des deux options répertoriées ici :

L'activation de cette option oblige la source à écrire des sommes de contrôle pour les événements écrits dans le journal binaire. Définissez sur NONE pour désactiver, ou le nom de l'algorithme à utiliser pour générer des sommes de contrôle actuellement, seules les sommes de contrôle CRC32 sont prises en charge.

Pour contrôler la lecture des sommes de contrôle par la réplique (à partir du journal du relais), utilisez l'option --slave-sql-verify-checksum.

Options de test et de débogage. Les options de journal binaire suivantes sont utilisées dans les tests de réplication et le débogage. Ils ne sont pas destinés à être utilisés dans des opérations normales.

Cette option est utilisée en interne par la suite de tests MySQL pour les tests de réplication et le débogage.

Cette option est utilisée en interne par la suite de tests MySQL pour les tests de réplication et le débogage.

Variables système utilisées avec la journalisation binaire

La liste suivante décrit les variables système permettant de contrôler la journalisation binaire. Ils peuvent être définis au démarrage du serveur et certains d'entre eux peuvent être modifiés lors de l'exécution à l'aide de SET . Les options de serveur utilisées pour contrôler la journalisation binaire sont répertoriées plus haut dans cette section.

Format de ligne de commande --binlog-cache-size=#
Variable système binlog_cache_size
Portée Global
Dynamique Oui
Taper Entier
Valeur par défaut 32768
Valeur minimum 4096
Valeur maximale (plateformes 64 bits) 18446744073709547520
Valeur maximale (plateformes 32 bits) 4294967295
Taille de bloc 4096

La taille du cache pour contenir les modifications apportées au journal binaire au cours d'une transaction. Un cache de journal binaire est alloué à chaque client si le serveur prend en charge tous les moteurs de stockage transactionnels et si le serveur a le journal binaire activé (option --log-bin). Si vous utilisez souvent des transactions volumineuses, vous pouvez augmenter cette taille de cache pour obtenir de meilleures performances. Les variables d'état Binlog_cache_use et Binlog_cache_disk_use peuvent être utiles pour régler la taille de cette variable. Voir Section 5.4.4, « Le journal binaire ».

binlog_cache_size définit la taille du cache de transactions, seule la taille du cache d'instructions est régie par la variable système binlog_stmt_cache_size.

Lorsqu'elle est activée, cette variable oblige le serveur source de réplication à écrire une somme de contrôle pour chaque événement dans le journal binaire. binlog_checksum prend en charge les valeurs NONE (désactivé) et CRC32 . La valeur par défaut est CRC32 .

Lorsque binlog_checksum est désactivé (valeur NONE ), le serveur vérifie qu'il n'écrit que des événements complets dans le journal binaire en écrivant et en vérifiant la longueur de l'événement (plutôt qu'une somme de contrôle) pour chaque événement.

La modification de la valeur de cette variable entraîne la rotation du journal binaire. Les sommes de contrôle sont toujours écrites dans un fichier journal binaire entier, et jamais dans une partie seulement.

La définition de cette variable sur la source sur une valeur non reconnue par le réplica amène le réplica à définir sa propre valeur binlog_checksum sur NONE et à arrêter la réplication avec une erreur. (Bogue #13553750, Bogue #61096) Si la compatibilité descendante avec des répliques plus anciennes est un problème, vous pouvez définir la valeur explicitement sur NONE .

Format de ligne de commande --binlog-direct-non-transactional-updates[=]
Variable système binlog_direct_non_transactional_updates
Portée Global, Session
Dynamique Oui
Taper booléen
Valeur par défaut DÉSACTIVÉ

En raison de problèmes de simultanéité, un réplica peut devenir incohérent lorsqu'une transaction contient des mises à jour de tables transactionnelles et non transactionnelles. MySQL essaie de préserver la causalité entre ces instructions en écrivant des instructions non transactionnelles dans le cache des transactions, qui est vidé lors de la validation. Cependant, des problèmes surviennent lorsque les modifications apportées aux tables non transactionnelles au nom d'une transaction deviennent immédiatement visibles pour les autres connexions, car ces modifications peuvent ne pas être écrites immédiatement dans le journal binaire.

La variable binlog_direct_non_transactional_updates offre une solution de contournement possible à ce problème. Par défaut, cette variable est désactivée. L'activation de binlog_direct_non_transactional_updates entraîne l'écriture des mises à jour des tables non transactionnelles directement dans le journal binaire, plutôt que dans le cache des transactions.

binlog_direct_non_transactional_updates fonctionne uniquement pour les instructions qui sont répliquées à l'aide du format de journalisation binaire basé sur les instructions c'est-à-dire qu'il ne fonctionne que lorsque la valeur de binlog_format est STATEMENT , ou lorsque binlog_format est MIXED et qu'une instruction donnée est répliquée à l'aide du format basé sur les instructions. Cette variable n'a aucun effet lorsque le format du journal binaire est ROW , ou lorsque binlog_format est défini sur MIXED et qu'une instruction donnée est répliquée à l'aide du format basé sur les lignes.

Avant d'activer cette variable, vous devez vous assurer qu'il n'y a pas de dépendances entre les tables transactionnelles et non transactionnelles. Un exemple d'une telle dépendance serait l'instruction INSERT INTO myisam_table SELECT * FROM innodb_table . Sinon, de telles déclarations sont susceptibles de faire diverger le réplica de la source.

Dans MySQL 5.6, cette variable n'a aucun effet lorsque le format du journal binaire est ROW ou MIXED . (Bogue #51291)

Contrôle ce qui se passe lorsque le serveur rencontre une erreur telle que l'impossibilité d'écrire, de vider ou de synchroniser le journal binaire, ce qui peut entraîner une incohérence du journal binaire de la source et une perte de synchronisation des réplicas.

Dans MySQL 5.6, cette variable est par défaut IGNORE_ERROR . Si le serveur rencontre une telle erreur, il poursuit la transaction en cours, enregistre l'erreur puis arrête la journalisation et continue d'effectuer les mises à jour. Pour reprendre la journalisation binaire, log_bin doit être réactivé, ce qui nécessite un redémarrage du serveur. Ce paramètre offre une compatibilité descendante avec les anciennes versions de MySQL.

La définition de cette variable sur ABORT_SERVER fait que le serveur arrête la journalisation et s'arrête chaque fois qu'il rencontre une telle erreur avec le journal binaire. Au redémarrage, la restauration se déroule comme dans le cas d'un arrêt inattendu du serveur (consultez la Section 17.3.2, « Gestion d'un arrêt inattendu d'un serveur de réplication »). Il s'agit du paramètre recommandé, en particulier dans les environnements de réplication complexes.

Dans les versions précédentes, cette variable était nommée binlogging_impossible_mode .

Cette variable définit le format de journalisation binaire et peut être l'un de STATEMENT , ROW ou MIXED . Voir Section 17.1.2, « Formats de réplication ».

binlog_format peut être défini au démarrage ou à l'exécution, sauf que dans certaines conditions, la modification de cette variable à l'exécution n'est pas possible ou entraîne l'échec de la réplication, comme décrit plus loin.

Dans MySQL 5.6, le format par défaut est STATEMENT . Exception : Dans MySQL NDB Cluster 7.3 et versions ultérieures, la valeur par défaut est MIXED La réplication basée sur les instructions n'est pas prise en charge pour NDB Cluster.

La définition de la valeur de session de cette variable système est une opération restreinte. L'utilisateur de session doit disposer de privilèges suffisants pour définir des variables de session restreintes. Voir Section 5.1.8.1, « Privilèges de variable système ».

Les règles régissant le moment où les modifications apportées à cette variable prennent effet et la durée de l'effet sont les mêmes que pour les autres variables système du serveur MySQL. Pour plus d'informations, reportez-vous à la Section 13.7.4.1, « Syntaxe SET pour l'affectation de variables ».

Lorsque MIXED est spécifié, la réplication basée sur les instructions est utilisée, sauf dans les cas où seule la réplication basée sur les lignes est garantie pour conduire à des résultats corrects. Par exemple, cela se produit lorsque les instructions contiennent des fonctions chargeables ou la fonction UUID().

Pour plus de détails sur la façon dont les programmes stockés (procédures et fonctions stockées, déclencheurs et événements) sont gérés lorsque chaque format de journalisation binaire est défini, reportez-vous à la Section 20.7, « Journalisation binaire des programmes stockés ».

Il existe des exceptions lorsque vous ne pouvez pas changer le format de réplication lors de l'exécution :

Depuis une fonction stockée ou un déclencheur.

Si la session est actuellement en mode de réplication basée sur les lignes et a des tables temporaires ouvertes.

À partir d'une transaction.

Essayer de changer le format dans ces cas entraîne une erreur.

La modification du format de journalisation sur un serveur source de réplication n'oblige pas une réplique à modifier son format de journalisation pour qu'il corresponde. Le changement de format de réplication alors que la réplication est en cours peut entraîner des problèmes si la journalisation binaire est activée sur un réplica et si la modification entraîne le réplica à l'aide de la journalisation au format STATEMENT alors que la source utilise la journalisation au format ROW ou MIXED. Un réplica ne peut pas convertir les entrées de journal binaire reçues au format de journalisation ROW au format STATEMENT pour une utilisation dans son propre journal binaire, cette situation peut donc entraîner l'échec de la réplication. Pour plus d'informations, reportez-vous à la Section 5.4.4.2, « Définir le format du journal binaire ».

Le format de journal binaire affecte le comportement des options de serveur suivantes :


18.8.3. Quoi enregistrer

Le Nom de l'application peut être n'importe quelle chaîne inférieure à NOMDATALEN caractères (64 caractères dans une version standard). Il est généralement défini par une application lors de la connexion au serveur. Le nom sera affiché dans le pg_stat_activity voir et inclus dans les entrées de journal CSV. Il peut également être inclus dans les entrées de journal régulières via le paramètre log_line_prefix. Seuls les caractères ASCII imprimables peuvent être utilisés dans le Nom de l'application valeur. Les autres caractères seront remplacés par des points d'interrogation (?).

debug_print_parse (booléen)
debug_print_rewrite (booléen)
debug_print_plan (booléen)

Ces paramètres permettent d'émettre différentes sorties de débogage. Lorsqu'ils sont définis, ils impriment l'arborescence d'analyse résultante, la sortie du réscripteur de requête ou le plan d'exécution pour chaque requête exécutée. Ces messages sont émis à JOURNAL niveau message, donc par défaut, ils apparaîtront dans le journal du serveur mais ne seront pas envoyés au client. Vous pouvez changer cela en ajustant client_min_messages et/ou log_min_messages. Ces paramètres sont désactivés par défaut.

Lorsqu'il est défini, debug_pretty_print met en retrait les messages produits par debug_print_parse, debug_print_rewrite, ou alors debug_print_plan. Il en résulte une sortie plus lisible mais beaucoup plus longue que le format "compact" utilisé lorsqu'il est désactivé. Il est activé par défaut.

Provoque l'enregistrement des points de contrôle et des points de redémarrage dans le journal du serveur. Certaines statistiques sont incluses dans les messages du journal, notamment le nombre de tampons écrits et le temps passé à les écrire. Ce paramètre ne peut être réglé que dans le postgresql.conf fichier ou sur la ligne de commande du serveur. La valeur par défaut est désactivée.

Provoque l'enregistrement de chaque tentative de connexion au serveur, ainsi que la réussite de l'authentification du client. Seuls les superutilisateurs peuvent modifier ce paramètre au démarrage de la session, et il ne peut pas du tout être modifié au cours d'une session. La valeur par défaut est désactivé.

Noter: Certains programmes clients, comme psql , tentent de se connecter deux fois tout en déterminant si un mot de passe est requis, donc les messages en double "connexion reçue" n'indiquent pas nécessairement un problème.

Provoque l'enregistrement des terminaisons de session. La sortie du journal fournit des informations similaires à log_connexions, plus la durée de la session. Seuls les superutilisateurs peuvent modifier ce paramètre au démarrage de la session, et il ne peut pas du tout être modifié au cours d'une session. La valeur par défaut est désactivé.

Entraîne l'enregistrement de la durée de chaque instruction terminée. La valeur par défaut est désactivé. Seuls les superutilisateurs peuvent modifier ce paramètre.

Pour les clients utilisant un protocole de requête étendu, les durées des étapes Parse, Bind et Execute sont enregistrées indépendamment.

Noter: La différence entre la définition de cette option et la définition de log_min_duration_statement à zéro est que le dépassement log_min_duration_statement force le texte de la requête à être enregistré, mais pas cette option. Ainsi, si log_duration est sur et log_min_duration_statement a une valeur positive, toutes les durées sont enregistrées mais le texte de la requête n'est inclus que pour les instructions dépassant le seuil. Ce comportement peut être utile pour collecter des statistiques dans les installations à forte charge.

Contrôle la quantité de détails écrits dans le journal du serveur pour chaque message enregistré. Les valeurs valides sont LACONIQUE, DÉFAUT, et VERBEUX, chacun ajoutant plus de champs aux messages affichés. LACONIQUE exclut l'enregistrement de DÉTAIL, INDICE, METTRE EN DOUTE, et LE CONTEXTE informations d'erreur. VERBEUX la sortie comprend le ETAT SQL code d'erreur (voir également l'annexe A) et le nom du fichier de code source, le nom de la fonction et le numéro de ligne qui a généré l'erreur. Seuls les superutilisateurs peuvent modifier ce paramètre.

Par défaut, les messages du journal de connexion affichent uniquement l'adresse IP de l'hôte qui se connecte. L'activation de ce paramètre entraîne également la journalisation du nom d'hôte. Notez que selon votre configuration de résolution de nom d'hôte, cela peut imposer une pénalité de performances non négligeable. Ce paramètre ne peut être réglé que dans le postgresql.conf fichier ou sur la ligne de commande du serveur.

Il s'agit d'une chaîne de style printf qui est sortie au début de chaque ligne de journal. % les caractères commencent des "séquences d'échappement" qui sont remplacées par des informations d'état comme indiqué ci-dessous. Les échappements non reconnus sont ignorés. Les autres caractères sont copiés directement dans la ligne de journal. Certains échappements ne sont reconnus que par les processus de session et seront traités comme vides par les processus d'arrière-plan tels que le processus du serveur principal. Les informations d'état peuvent être alignées à gauche ou à droite en spécifiant un littéral numérique après le % et avant l'option. Une valeur négative entraînera le remplissage des informations d'état sur la droite avec des espaces pour lui donner une largeur minimale, tandis qu'une valeur positive sera remplie sur la gauche. Le remplissage peut être utile pour faciliter la lisibilité humaine dans les fichiers journaux. Ce paramètre ne peut être réglé que dans le postgresql.conf fichier ou sur la ligne de commande du serveur. La valeur par défaut est une chaîne vide.

Échapper Effet Séance seulement
%une Nom de l'application Oui
%u Nom d'utilisateur Oui
%ré Nom de la base de données Oui
%r Nom d'hôte distant ou adresse IP et port distant Oui
%h Nom d'hôte distant ou adresse IP Oui
%p ID de processus non
%t Horodatage sans millisecondes non
%m Horodatage avec millisecondes non
%je Balise de commande : type de la commande actuelle de la session Oui
%e Code d'erreur SQLSTATE non
%c ID de session : voir ci-dessous non
%l Numéro de la ligne de journal pour chaque session ou processus, commençant à 1 non
%s Horodatage du début du processus non
%v ID de transaction virtuelle (backendID/localXID) non
%X ID de transaction (0 si aucun n'est attribué) non
%q Ne produit aucune sortie, mais indique aux processus hors session de s'arrêter à ce stade de la chaîne ignorée par les processus de session non
%% Littéral % non

Pointe: Si vous définissez une valeur non vide pour log_line_prefix, vous devez généralement faire en sorte que son dernier caractère soit un espace, pour fournir une séparation visuelle du reste de la ligne de journal. Un caractère de ponctuation peut également être utilisé.

Pointe: Syslog produit ses propres informations d'horodatage et d'ID de processus, vous ne voudrez donc probablement pas inclure ces échappements si vous vous connectez à syslog .

Contrôle si un message de journal est généré lorsqu'une session attend plus longtemps que deadlock_timeout pour acquérir un verrou. Ceci est utile pour déterminer si les attentes de verrouillage entraînent des performances médiocres. La valeur par défaut est désactivé. Seuls les superutilisateurs peuvent modifier ce paramètre.

Contrôle quelles instructions SQL sont consignées. Les valeurs valides sont rien (désactivé), ddl, mode, et tous (toutes les déclarations). ddl enregistre toutes les instructions de définition de données, telles que CRÉER, MODIFIER, et TOMBER déclarations. mode enregistre tout ddl instructions, ainsi que des instructions de modification de données telles que INSÉRER, METTRE À JOUR, EFFACER, TRONQUER, et COPIER DE. PRÉPARER, EXÉCUTER, et EXPLIQUER ANALYSER les instructions sont également consignées si leur commande contenue est d'un type approprié. Pour les clients utilisant le protocole de requête étendu, la journalisation se produit lorsqu'un message Execute est reçu et les valeurs des paramètres Bind sont incluses (avec les guillemets simples incorporés doublés).

La valeur par défaut est rien. Seuls les superutilisateurs peuvent modifier ce paramètre.

Noter: Les instructions contenant des erreurs de syntaxe simples ne sont pas consignées, même par le instruction_log = tous car le message de journal n'est émis qu'une fois l'analyse de base effectuée pour déterminer le type d'instruction. Dans le cas d'un protocole de requête étendu, ce paramètre n'enregistre pas non plus les instructions qui échouent avant la phase d'exécution (c'est-à-dire pendant l'analyse ou la planification). Régler log_min_error_statement à ERREUR (ou inférieur) pour enregistrer de telles déclarations.

Provoque l'enregistrement de chaque commande de réplication dans le journal du serveur. Voir Section 50.3 pour plus d'informations sur la commande de réplication. La valeur par défaut est désactivé. Seuls les superutilisateurs peuvent modifier ce paramètre.

Contrôle la journalisation des noms et des tailles de fichiers temporaires. Des fichiers temporaires peuvent être créés pour les tris, les hachages et les résultats de requêtes temporaires. Une entrée de journal est créée pour chaque fichier temporaire lorsqu'il est supprimé. Une valeur de zéro enregistre toutes les informations des fichiers temporaires, tandis que les valeurs positives n'enregistrent que les fichiers dont la taille est supérieure ou égale au nombre de kilo-octets spécifié. Le paramètre par défaut est -1, ce qui désactive cette journalisation. Seuls les superutilisateurs peuvent modifier ce paramètre.

Définit le fuseau horaire utilisé pour les horodatages écrits dans le journal du serveur. Contrairement à TimeZone, cette valeur est à l'échelle du cluster, de sorte que toutes les sessions rapporteront les horodatages de manière cohérente. La valeur par défaut intégrée est GMT, mais cela est généralement remplacé dans postgresql.conf initdb y installera un paramètre correspondant à son environnement système. Voir la section 8.5.3 pour plus d'informations. Ce paramètre ne peut être réglé que dans le postgresql.conf fichier ou sur la ligne de commande du serveur.


À quoi sert l'instruction SQL CASE ?

Une instruction CASE dans SQL Server évalue une expression et renvoie une valeur en fonction des conditions définies. Par conséquent, dans l'exemple précédent, les instructions CASE fonctionnent comme indiqué ci-dessous.

À un niveau élevé, la syntaxe d'une instruction SQL CASE est illustrée ci-dessous. Ici, nous avons spécifié plusieurs conditions. SQL Server évalue les conditions de manière séquentielle. Une fois qu'une condition est évaluée avec succès, elle arrête l'évaluation des conditions restantes. Si aucune des conditions n'est satisfaite, nous pouvons utiliser une instruction ELSE facultative pour renvoyer la valeur par défaut. Par exemple, si nous avons une valeur différente de 0 et 1 dans la colonne de disponibilité, vous obtenez la sortie du bloc de code ELSE. Il nécessite au moins un ensemble de blocs WHEN et THEN. L'instruction CASE doit se terminer par le bloc END.

Explorons l'instruction SQL CASE à l'aide de divers exemples.

Remarque : dans cet article, nous utilisons l'exemple de base de données Microsoft, AdventureWorks. Vous pouvez télécharger sa sauvegarde à partir de Microsoft Docs.

L'instruction SELECT avec une expression CASE simple


Dans ce type d'instruction CASE, nous utilisons des expressions de contrôle d'égalité. La requête suivante implémente une expression CASE simple.

  • Si la valeur dans [SalariedFlag] est 1, alors il affiche le Employé actif
  • Pour toutes les autres valeurs, il affiche la sortie sous la forme Employé inactif

Nous pouvons spécifier plusieurs conditions pour l'instruction CASE.

Standardisation des données à l'aide d'instructions SQL CASE

Habituellement, nous utilisons des abréviations pour stocker des valeurs dans des tables SQL. Les abréviations standard sont le sexe, les codes de pays, le statut de mariage, les noms de produits populaires, etc.

Supposons que nous spécifiions les abréviations pour stocker les sexes des employés. Maintenant, notre application devrait afficher les résultats sans aucune abréviation.

Les instructions SQL CASE aident à normaliser la sortie pour des critères définis. Dans la requête ci-dessous, nous utilisons les conditions suivantes :

  • Si la valeur de genre est M, affichez-le comme Homme
  • Si la valeur de genre est F, affichez-le comme Femme
  • Pour toute autre valeur, afficher InvalideValeur

Instructions CASE recherchées

Dans l'instruction CASE recherchée, nous spécifions une expression CASE au lieu des valeurs directes. Une fois que la valeur de l'expression est évaluée et satisfait une condition dans la clause WHEN, sa valeur correspondante est renvoyée.

Regardez la requête SQL ci-dessous. Ici, nous avons défini des expressions dans la clause WHEN pour le [ListPrice]. Il identifie que le coût du produit est de 250 $ et est marqué comme un article électronique.

Pour l'exemple de genre mentionné précédemment, nous pouvons réécrire l'instruction SQL CASE pour les abréviations de genre à l'aide des instructions de cas recherchées.

Utilisation des instructions CASE avec la clause ORDER BY

Les requêtes SQL utilisent la clause ORDER BY pour le tri des données dans l'ordre croissant ou décroissant. Vous pouvez utiliser les instructions CASE avec la clause ORDER BY. Supposons que dans la table des produits, nous récupérons le [ProductName] et le [ListPrice]. Nous voulons trier les résultats des manières suivantes :

  • Si le prix de la liste de produits est inférieur à 2 000, vous voulez le résultat dans l'ordre de tri par défaut, c'est-à-dire croissant
  • Si le prix catalogue du produit est supérieur à 2 000, le tri de la clause ORDER BY donne un ordre décroissant

Dans cette requête, nous utilisons deux instructions SQL CASE pour implémenter la logique.

Dans la sortie de requête ci-dessous, vous pouvez vérifier les tris de données apparaissant dans l'ordre décroissant et croissant.

Dans un autre exemple, supposons que nous voulions trier les données de la table des employés en fonction de la condition suivante :

  • Pour les employés actifs (indicateur actuel =1), les données doivent trier la colonne de date d'embauche
  • Pour les employés inactifs, il doit trier les données selon les valeurs de la colonne date de naissance

Dans la sortie de la requête, nous pouvons vérifier l'ordre de tri des données défini par la clause ORDER BY et les instructions CASE.

Instruction CASE en SQL et fonctions d'agrégat

Les fonctions d'agrégation de SQL Server effectuent des calculs et renvoient une valeur unique. Des exemples de fonctions d'agrégation sont MIN, MAX, COUNT, ABG et CHECKSUM.

Supposons que nous voulions récupérer le nombre d'employés embauchés pour chaque année de 2007 à 2010. Il doit afficher les résultats dans le format suivant :

À cette fin, nous utilisons la fonction d'agrégation COUNT dans SQL Server.

  • Tout d'abord, la fonction SQL DATEPART filtre les données en fonction de l'année. Par exemple, DATEPART(YY, Hiredate)=2007, filtre les données pour l'année 2007.
  • Nous utilisons ensuite l'instruction CASE pour renvoyer 1 si l'année est 2007.
  • La fonction d'agrégation count compte les enregistrements et affiche les résultats.
  • De même, la requête fonctionne pour les années restantes.

De même, disons que nous voulons utiliser la fonction d'agrégation GROUP BY pour regrouper les lignes ayant la même catégorie de produits. Nous pouvons spécifier l'instruction CASE dans SQL pour trier les données du jeu de résultats groupé.

Dans la requête ci-dessus, nous utilisons deux instructions SQL CASE.

  • La première instruction CASE classe les données en fonction de l'expression définie dans le prix catalogue. À l'aide de cette déclaration CASE, nous divisons les produits dans les catégories suivantes :
    • Articles en rupture de stock
    • Biens de consommation
    • Articles électroniques
    • Articles de luxe

    Empêcher l'erreur de division par zéro à l'aide des instructions SQL CASE

    Une erreur de division par zéro se produit si la valeur du dénominateur est zéro. Si vous faites ces fractions dans SQL Server, cela vous donnera l'erreur de division par zéro comme indiqué ci-dessous.

    C'est une excellente pratique d'écrire vos requêtes de manière à éviter ces erreurs courantes. Pour éviter cela, nous utilisons la logique de fraction dans une instruction CASE.

    Nous avons protégé notre requête de la division par zéro erreur. Maintenant, avec la logique modifiée, si nous obtenons un zéro au dénominateur, vous obtenez NULL dans la sortie, comme indiqué ci-dessous.


    D. MATCH D'INDICE avec MAX

    Maintenant, Norrington veut savoir combien de pirates se trouvent sur le navire le plus peuplé, et de quel navire s'agit-il ? Il utilise la formule INDEX avec MAX pour obtenir le plus grand nombre de pirates, mais il doit également savoir quel navire les transporte. Il utilise donc la formule INDEX/MATCH avec MAX pour découvrir quel navire a le plus de pirates à bord.

    1. =MAX(INDEX(Ships, ,7)) est égal à 300, le plus grand nombre de pirates sur l'un des navires

    2. =INDEX($A$2:$A$16, MATCH(MAX(Ships), $G$2:G$16, 0)) est égal au Royal James, le navire avec le plus de pirates à bord

    3. =INDEX($F$2:$F$16, MATCH(MAX(Ships), $G$2:G$16, 0)) est égal à Stede Bonnet, capitaine du Royal James avec un équipage de 300 pirates

    Utilisez INDEX-MATCH et MAX pour récupérer des informations spécifiques de votre base de données.

    JD Sartain est un journaliste technologique de Boston. Elle écrit la chronique Max Productivity pour PCWorld, une chronique mensuelle pour le CIO et des articles de fond réguliers pour Network World.