Instruction Select

ECOLE SUPERIEURE ET D'APPLICATION DES TRANSMISSIONS

Division management des systèmes d'information

Cours Analyse

LANGAGE SQL (Instruction SELECT)

Edition 1999

SQL

Les Bases du Langage

Objectif Apporter la connaissance des bases de ce langage de manipulation de données de 4ème génération utilisé pour intervenir sur les bases de données relationnelles.

SOMMAIRE

I.

S.Q.L. : LE LANGAGE ERROR: REFERENCE SOURCE NOT FOUND

II.

CONCEPTS DE BASE ERROR: REFERENCE SOURCE NOT FOUND

DE

RECHERCHE

STRUCTURÉ DÉFINITIONS

ET

III. LA BASE DE ERROR: REFERENCE SOURCE NOT FOUND

STAGE

DONNÉES

A. MODÈLE CONCEPTUEL DES DONNÉES

7

B. SCHÉMA ERROR: REFERENCE SOURCE NOT FOUND

RELATIONNEL

C. STRUCTURE ERROR: REFERENCE SOURCE NOT FOUND

DES

TABLES

IV. S.Q.L. : ERROR: REFERENCE SOURCE NOT FOUND

INTRODUCTION

A. LE SÉPARATEUR ERROR: REFERENCE SOURCE NOT FOUND

DE

B. COMMENTER ERROR: REFERENCE SOURCE NOT FOUND

LES

C. LE RÉSULTAT AFFICHÉ ERROR: REFERENCE SOURCE NOT FOUND

DÉPASSE

REQUÊTES

REQUÊTES

LES

LIMITES

OFFERTES

!

D. NORMES ERROR: REFERENCE SOURCE NOT FOUND E. QUELQUES FONCTIONS INGRES ERROR: REFERENCE SOURCE NOT FOUND

UTILES

F. COMPRENDRE LA PROGRESSION ERROR: REFERENCE SOURCE NOT FOUND

V.

DU

POUR

SUPPORT

S.Q.L. : LE ERROR: REFERENCE SOURCE NOT FOUND A. RÉCUPÉRATION

DMSI/ANALYSE du 11/10/2009

ÉLÉMENTAIRE

Instruction Select.doc

LES

EXERCICES

DE

COURS

LANGAGE DE

DONNÉES

Page 2 / 44

SQL

Les Bases du Langage

ERROR: REFERENCE SOURCE NOT FOUND 1.

SÉLECTION ERROR: REFERENCE SOURCE NOT FOUND

2.

SÉLECTION AVEC ÉLIMINATION ERROR: REFERENCE SOURCE NOT FOUND

3.

SÉLECTION AVEC ERROR: REFERENCE SOURCE NOT FOUND

(SELECT)

SIMPLE

DE

(DISTINCT)

REDONDANCES

(WHERE)

CONDITIONS

♦ Opérateurs de Error: Reference source not found ♦ Valeurs nulles Error: Reference source not found ♦ Intervalles Error: Reference source not found ♦ Correspondance des Error: Reference source not found ♦ Listes Error: Reference source not found ♦ Opérateurs logiques ou Conditions Error: Reference source not found

comparaison (Is

(Between...And...) caractères

OPÉRATEURS ARITHMÉTIQUES ERROR: REFERENCE SOURCE NOT FOUND

2.

RENOMMER LES ERROR: REFERENCE SOURCE NOT FOUND

3.

CHAÎNES DE CARACTÈRES ERROR: REFERENCE SOURCE NOT FOUND

4.

TRI / ERROR: REFERENCE SOURCE NOT FOUND

(Like) (In)

de

connexions

B. CALCULS ET PRÉSENTATION ERROR: REFERENCE SOURCE NOT FOUND 1.

Null)

DE

(+, ENTÊTES

(And

DANS

ORDONNANCEMENT

Or)

RÉSULTATS

-, DE

/

*, COLONNES

LES

(ORDER

/) (AS) RÉSULTATS

BY)

C. JOINTURES ERROR: REFERENCE SOURCE NOT FOUND 1.

INTRODUCTION ERROR: REFERENCE SOURCE NOT FOUND

2.

JOINTURE ERROR: REFERENCE SOURCE NOT FOUND

3.

ALIAS ERROR: REFERENCE SOURCE NOT FOUND

4.

AUTO-JOINTURE ERROR: REFERENCE SOURCE NOT FOUND

D. SOUS-REQUÊTES ERROR: REFERENCE SOURCE NOT FOUND

INTERNE

SOUS-INTERROGATIONS

OU

1.

SOUS-REQUÊTE AVEC ERROR: REFERENCE SOURCE NOT FOUND

2.

SOUS-REQUÊTE AVEC ERROR: REFERENCE SOURCE NOT FOUND

3.

SOUS-REQUÊTE AVEC ERROR: REFERENCE SOURCE NOT FOUND

LE

MOT-CLÉ

ANY

4.

SOUS-REQUÊTE AVEC ERROR: REFERENCE SOURCE NOT FOUND

LE

MOT-CLÉ

ALL

DMSI/ANALYSE du 11/10/2009

LES

Instruction Select.doc

OPÉRATEURS

LE

DE

COMPARAISON

MOT-CLÉ

IN

Page 3 / 44

SQL

Les Bases du Langage 5.

PRÉDICAT ERROR: REFERENCE SOURCE NOT FOUND

EXISTS

E. ORGANISER LES DONNÉES ERROR: REFERENCE SOURCE NOT FOUND 1.

2. 3.

ET

SYNTHÉTISER

LES

FONCTIONS ERROR: REFERENCE SOURCE NOT FOUND

D'AGRÉGATION

♦ Les fonctions SUM Error: Reference source not found ♦ La Error: Reference source not found ♦ Le Error: Reference source not found REGROUPEMENT ERROR: REFERENCE SOURCE NOT FOUND CONDITION DE REGROUPEMENT ERROR: REFERENCE SOURCE NOT FOUND

RÉSULTATS

/

AVG

/

MAX

fonction

MIN COUNT

mot-clé

(HAVING)

/

DISTINCT (GROUP

OU

DIVISION

BY) EN

ALGÈBRE

RELATIONNELLE

♦ Rappel sur le principe de la division en algèbre relationnelle ♦ Application de la division en langage SQL

VI. DES MOTS-CLÉS S.Q.L. ERROR: REFERENCE SOURCE NOT FOUND

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

42 43 AU

FRANÇAIS

Page 4 / 44

SQL

Les Bases du Langage

I.S.Q.L. : Le Langage de Recherche Structuré Issu du langage SEQUEL (Structure English Query Language) mis au point par la société IBM, il est naturellement basé sur l’anglais comme beaucoup d’autres langages. Il a eu une reconnaissance internationale et a été adopté par les grands constructeurs de SGBDR comme langage de référence pour l’exploitation et l’administration des bases de données relationnelles. Aussi recense-t-on plusieurs normes : - ISO 83 pour SQL, - ISO 92 pour SQL 2, - et une nouvelle norme est à l’étude pour SQL 3. Ce qui fait que l’on dénombre non pas un SQL standard mais une grande quantité de dialectes qui répondent à une norme. Car, bien sûr, chaque constructeur a souhaité différencier son langage par rapport aux autres tout en restant dans le cadre de la norme.

-

SQL possède un triple mode de fonctionnement : mode interactif (objet du cours) ; mode intégré (Embedded SQL) c’est-à-dire que les requêtes sont intégrées dans des programmes en L3G ou L4G (langages de nième génération) comme les langages événementiels L4G type Visual C++,Object View, … ou L3G type Pascal, Cobol, … ; mode autonome (Modular SQL) c’est-à-dire que les requêtes écrites seront appelées comme des fonctions externes par un programme écrit en L3G.

SQL est un langage complet qui ne fait absolument pas référence aux chemins d’accès aux données comme les langages de type navigationnel. Le programmeur n’est plus obligé de connaître et de mentionner l’accès aux données qu’il souhaite atteindre. C’est un langage complet qui permet de réaliser des opérations aussi diverses que la mise à jour de la base de données, la création, la recherche la récupération et la manipulation de données et l’administration de la base de données. On trouve ainsi trois types d’utilisateurs de ce langage : l’administrateur de la base de données qui est responsable de la cohérence de la base de données, de l’optimisation des accès et du stockage ; - l’administrateur d’application qui s’occupera de la définition des tables, d’attribuer les droits d’accès aux tables aux utilisateurs mais aussi aux programmeurs ; - le développeur d’applications qui, lui, écrira les requêtes et transactions à intégrer dans les programmes en L4G. Souvent, l’administrateur de base de données et l’administrateur d’applications ne représentent qu’une même personne. -

On peut découper le langage SQL en trois sous-catégories : - le Langage de Manipulation de Données (LMD), langage de base pour le développeur, - le Langage de Définition de Données (LDD), langage d’administration de la base de données, - le Langage de Contrôle de Données (LCD) qui permet l’administration des droits d’accès aux données.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 5 / 44

SQL

Les Bases du Langage

II.Concepts de base et Définitions Système de Gestion de Base de Données Logiciel qui facilite la définition des structures d'une base de données ainsi que le stockage et la recherche des données à partir de ces structures. Base de Données C'est un ensemble de données ayant des relations entre elles. Les données sont structurées de façon à permettre une expansion de la base de données. Modèle relationnel Dans une base de données relationnelle, toutes les données sont sous forme de tables. Une table se compose de lignes et de colonnes et à chaque intersection se trouve une donnée. Colonne Une colonne (ou attribut) est analogue à un champ dans un enregistrement. Chaque colonne d'une ligne donnée contient une valeur unique et chaque colonne est d'un type déterminé. Toute colonne porte un nom. Ligne Une ligne (ou tuple) est analogue à un enregistrement dans un fichier. Les lignes d'une table sont composées des mêmes colonnes. Chaque ligne contient des données concernant un cas de la table. Les lignes ne sont pas dans un ordre particulier. Index -

-

On distingue deux types d’index : l’index primaire qui peut porter sur une colonne ou une association de colonnes d’une même table. On peut le comparer à la clé primaire d’une table. Cet index peut être défini comme étant unique (les doublons ne seront pas autorisés) ou comme autorisant les doublons. Une table ne peut posséder qu’un seul index primaire. les index secondaires qui, eux, ne seront pas définis comme pouvant être uniques. Ils sont, en général, définis sur les clés étrangères, clés migrantes des autres tables qui entrent en relation avec la table concernée.

Les index sont très utiles en ce qui concerne l’accès aux données. En effet, sur la base des index, le SGBDR entretient des tables d’index qui permettent au système des accès optimisés aux données.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 6 / 44

SQL

Les Bases du Langage

III.La base de données STAGE A.Modèle Conceptuel des Données

B.Schéma relationnel EMP DEPT BATIMENT

(Matricule, Nom, Emploi, Mat_chef, Embauche, Salaire, Com, #Division) (Division, Nom_div, Budget, #Bâtiment) (Bâtiment, Rue, Ville, Code_postal)

C.Structure des tables Table des bâtiments : batiment

Table des divisions : dept

Table des employés : emp

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 7 / 44

SQL

Les Bases du Langage

IV.S.Q.L. : Introduction A.Le séparateur de requêtes S.Q.L. permet de taper plusieurs requêtes avant d’en lancer l’exécution. Le ";" est le caractère de séparation entre les instructions S.Q.L. Lorsque vous n'avez qu'une seule instruction, ce séparateur est optionnel. B.Commenter les requêtes Vous pouvez également insérer des commentaires entre les instructions. Pour cela, vous devez taper votre texte comme suit : /* ........ */. Les symboles /* et */ peuvent également servir à transformer une requête en simple texte. Attention à la redéfinition de votre clavier ! !

C.Le résultat affiché dépasse les limites offertes ! ∧

J K ∧ L ∧ H ∧

déplacement vers le bas déplacement vers le haut déplacement latéral d'une page vers la droite déplacement latéral d'une page vers la gauche

PgUp PgDown

page précédente page suivante

D.Normes Certains symboles utilisés dans l’écriture des instructions S.Q.L. sont à connaître pour une meilleure interprétation de celles-ci. [ …] Mot(s) à caractère optionnel. {…|…} Une seule proposition au choix. objet [, …] Il est possible d’énumérer une liste d’objets de même nature que celui cité.

Les mots-clés dans les différentes instructions S.Q.L. sont en majuscule, gras et surlignés. Ils sont OBLIGATOIRES ! !

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 8 / 44

SQL

Les Bases du Langage

E.Quelques fonctions Ingres utiles pour les exercices

Ingres offre diverses fonctions utilisables pour le langage S.Q.L. Vous pouvez en voir une liste non exhaustive en fin d’aide de l’outil ISQL. Trois d’entre-elles nous seront utiles lors des exercices pour les comparaisons de chaîne de caractères. : ♦ Conversion de chaîne de caractères -

Lowercase (nom_colonne) convertit le contenu de la colonne tout en minuscule ; Uppercase (nom_colonne) convertit le contenu de la colonne tout en majuscule.

♦ Conversion de type de donnée - Varchar (nom_colonne) convertit une colonne de type char ou date en type varchar. Pour les colonnes de type char, on peut combiner ces fonctions de la manière suivante : {Lowercase | Uppercase} (Varchar (nom_colonne_type_char)) Ces fonctions peuvent être utilisées soit au niveau du SELECT, soit au niveau de la clause WHERE comme nous le verrons. Vous les retrouverez parmi d’autres avec leur syntaxe et de plus amples explications et exemples en annexe de cet ouvrage.

Aérez et commentez l’écriture de vos requêtes S.Q.L.

F.Comprendre la progression du support de cours

La grande partie qui suit, va vous présenter l’essentiel des commandes S.Q.L. à maîtriser pour pouvoir exploiter une base de données. A chaque étape, à chaque nouvelle syntaxe, vous apprendrez de nouveaux mots-clés, de nouveaux ordres S.Q.L. Mais, surtout, sachez d’ores et déjà que la très grande majorité des ordres ou mots-clés que vous découvrirez peuvent s’associer dans les requêtes S.Q.L. Chaque nouvelle étape, chaque nouvelle syntaxe n’est donc pas indépendante des autres.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 9 / 44

SQL

Les Bases du Langage

V.S.Q.L. : Le Langage A.Récupération élémentaire de données 1.Sélection simple (Select) Syntaxe SELECT [ALL]

{ * | <liste_colonnes> }

FROM

<nom_table>

Cette syntaxe permet de sélectionner la totalité des données contenues dans les champs énumérés. Si des doublons existent, ils seront également affichés. " * " représente la totalité des colonnes de la table. Exemples /* Liste de toutes les données des bâtiments */ SELECT * FROM batiment ; /* Liste des matricules et des noms des employés */ SELECT [ALL] matricule, nom FROM emp ; NOTA L'ordre d'énumération des colonnes impose l'ordre d'affichage des données après exécution de la requête. Si vous utilisez " * ", les données seront affichées dans leur ordre de conservation dans la table. Questions 1. Liste des données de toutes les divisions. 2. Libellé et budget des divisions. 3. Liste des villes où sont implantés les bâtiments.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 10 / 44

SQL

Les Bases du Langage

2.Sélection avec élimination de redondances (Distinct) Syntaxe SELECT DISTINCT

<liste_colonnes>

FROM

<nom_table>

L’emploi du mot-clé DISTINCT est basé sur le principe de la projection en algèbre relationnelle. Pour mémoire, la projection consiste à sélectionner la totalité des attributs (ou colonnes) d’une table et à en éliminer automatiquement les doublons. NOTA Si plusieurs colonnes sont à sélectionner, le mot-clé DISTINCT s’appliquera sur l’association des colonnes citées. Exemples /* Liste des différents bâtiments où sont localisées les divisions */ SELECT DISTINCT batiment FROM dept ; /* Liste des différents matricules des chefs */ SELECT DISTINCT mat_chef FROM emp ; On remarque, dans cet exemple, qu’il apparaît une occurrence ne comportant pas de valeur réelle. Erreur ou non ? Nous verrons par la suite… Questions 4. Liste des différentes villes où sont implantés les bâtiments. 5. Liste des différents salaires des employés. 6. Liste des différents salaires et des noms des employés. Comparez les résultats des questions 5 et 6.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 11 / 44

SQL

Les Bases du Langage

3.Sélection avec conditions (Where)

Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE



Avec = <nom_colonne> Opérateurs de recherche pour la clause WHERE

Opérateurs de comparaison

=, >, <, ...

Valeurs nulles

IS NULL et IS NOT NULL

Intervalles

BETWEEN, NOT BETWEEN

Correspondance de caractères

LIKE, NOT LIKE

Listes

IN et NOT IN

Opérateurs logiques

AND, OR

Remarque : le NOT Le NOT traduit une négation d’expression booléenne ou de mot-clé tel que Like, Between, In , Null.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 12 / 44

SQL

Les Bases du Langage

♦ Opérateurs de comparaison

Opérateur Signification = > <

égal à plus grand que ou avant (dates) plus petit que ou après (dates)

Opérateur

Signification

>= ou !< <= ou !> != ou <>

Plus grand ou égal Pas plus petit que Plus petit ou égal Pas plus grand que Pas égal (différent)

NOTA Les littéraux alphanumériques, alphabétiques et les données de type date sont bornés par des simples quotes ('). Règles de comparaison F

Valeur numérique - Les nombres négatifs sont plus petits que les nombres positifs. - Un nombre négatif de grande valeur est plus petit qu'un nombre négatif de faible valeur. - Un nombre positif de grande valeur est plus grand qu'un nombre positif de faible valeur.

F

Valeur alphanumérique / alphabétique Les valeurs sont comparées caractère par caractère jusqu'à la rencontre du premier caractère différent. F Les lettres minuscules sont supérieures aux lettres majuscules qui sont supérieures aux nombres.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 13 / 44

SQL

Les Bases du Langage

Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE

<nom_colonne>

Exemples EGALITE /* Liste des employés qui perçoivent un salaire de 6900 F */ SELECT nom FROM emp WHERE salaire = 6900 ; DIFFERENCE /* Nom et salaire des employés n’appartenant pas à la division 30 */ SELECT nom, salaire FROM emp WHERE division != 30 ; SUPERIORITE /* Liste des employés embauchés après le 01/01/1992 */ SELECT matricule, nom FROM emp WHERE embauche > '01/01/1992' ; INFERIORITE /* Nom, salaire et com des employés dont le nom se classe normalement avant celui de Mr Maréchal */ SELECT nom, salaire, com FROM emp WHERE lowercase(nom) < ‘marechal’ ; Questions 7. Liste des employés non vendeurs avec leur date d’embauche et leur salaire. 8. Liste des divisions implantées dans le bâtiment (A, a). 9. Liste des employés dont la commission est supérieure à 0,00 F.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 14 / 44

SQL

Les Bases du Langage

♦ Valeurs nulles (Is Null) Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE

<nom_colonne> IS [NOT] NULL

Signification IS NULL

: traduit que les données recherchées sont nulles (NULL).

IS NOT NULL

: traduit que les données recherchées ne sont pas nulles (NULL).

F Une valeur NULL implique une grandeur inconnue. Elle n’est pas un zéro ou un blanc ; il n’y a pas de valeur explicitement assignée. F

Une valeur NULL n’est jamais égale à une autre valeur NULL.

Exemples /* Liste des employés qui n’ont pas de chef */ SELECT nom FROM emp WHERE mat_chef IS NULL ; Pour l’exemple suivant, reportez-vous au résultat du 2nd exemple du paragraphe concernant l’élimination de redondances. /* Liste des différents matricules des chefs */ SELECT DISTINCT matricule FROM emp WHERE mat_chef IS NOT NULL ; On remarque maintenant que l’occurrence qui ne contenait pas de valeur réelle n’a pas été sélectionnée. Cela montre bien que la valeur NULL est une valeur à part entière. Questions 10. Liste des employés qui perçoivent une commission [0 ; + ∞]. 11. Liste des employés dont la commission est inexistante.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 15 / 44

SQL

Les Bases du Langage

♦ Intervalles (Between...And...) Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE

<nom_colonne> [NOT] BETWEEN AND

Signification BETWEEN

: mot-clé utilisé pour désigner un intervalle fermé. Les valeurs extrêmes de l'intervalle sont prises en compte pour les recherches.

NOT BETWEEN

: mots-clés qui excluent l'intervalle y compris les valeurs extrêmes spécifiées.

Naturellement, l'intervalle annoncé doit être croissant. Exemples /* Liste des employés dont le salaire est compris entre 8000 et 9000 F */ SELECT matricule, nom FROM emp WHERE salaire BETWEEN 8000 AND 9000 ; /* Liste des employés alphabétiquement classés entre MM. ALLAIN et PAITEL */ SELECT nom FROM emp WHERE uppercase (nom) BETWEEN ‘ALLAIN’ AND ‘PAITEL’ ; Questions 12. Liste des employés qui perçoivent entre 100 et 300 F de commission. 13. Liste des divisions alphabétiquement comprises entre la division informatique et celle des ventes. 14. Divisions pourvues d’un budget exclu de l’intervalle [150000 - 200000] F.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 16 / 44

SQL

Les Bases du Langage

♦ Correspondance des caractères (Like) Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE

<nom_colonne> [NOT] LIKE

Signification LIKE : mot-clé utilisé pour désigner une comparaison sur une portion de caractères. NOT LIKE : mot-clé qui exclue la portion de caractères saisie de la comparaison. Caractères spécifiques % remplace une chaîne de caractères quelconque (de 0 à n caractères). _ remplace un seul caractère Ces caractères spécifiques peuvent être combinés et utilisés plusieurs fois simultanément. Quelques cas particuliers • Recherche de donnée(s) comportant une apostrophe : select from where LIKE ‘%’’%’ ; •

Recherche de donnée(s) dont la 1ère lettre est comprise entre deux valeurs : select from where LIKE '\[val1-val2\]%' escape '\' ;

•

Recherche de donnée(s) comportant le caractère % : select from where LIKE '%\%%' escape '\' ;

•

Recherche de donnée(s) commençant par un \ et se terminant par un % : select from where LIKE '\\%\%' escape '\' ;

•

Recherche de donnée(s) commençant par un chiffre inclus dans une liste (continue ou discontinue) et suivi d’une lettre comprise entre deux valeurs en majuscule puis de n autres caractères quelconques. select from where LIKE ‘\[val1 val2 … valn\]\[ val1-val2\]%’ escape ‘\’ ;

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 17 / 44

SQL

Les Bases du Langage

Exemples /* Liste des informations concernant M. Maréchal */ SELECT * FROM emp WHERE lowercase (nom) LIKE ‘marechal%’ ; /* Liste des informations relatives aux bâtiments dont le code postal de la ville ne contient pas les caractères 5 et 0 respectivement en deuxième et troisième positions. */ SELECT * FROM batiment WHERE code_postal NOT LIKE ‘_50%’ ; Questions 15. Matricule, nom, emploi pour les employés dont le nom n’a pas comme deuxième lettre un (U, u) et ne se termine pas par (T, t). 16. Liste des divisions dont le libellé contient (TI, ti). 17. Liste des employés dont le nom se termine par (ON, on).

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 18 / 44

SQL

Les Bases du Langage

♦ Listes (In) Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE

<nom_colonne> [NOT] IN ( <liste_valeurs> )

Signification IN

: mot-clé utilisé pour désigner un ensemble de valeurs pour une recherche.

NOT IN

: mot-clé qui exclue de la recherche les valeurs contenues dans l’ensemble.

F

Les valeurs énoncées peuvent être de type soit caractère, soit numérique.

Exemple /* Liste des employés des divisions 10 ou 20 avec leur emploi respectif */ SELECT nom, emploi, division FROM emp WHERE division IN (10, 20) /* Liste des divisions qui ne sont pas implantées dans les bâtiments A et B */ SELECT nom_div FROM dept WHERE batiment NOT IN (‘A’, ‘B’) Comment vérifier l’exactitude de ce dernier résultat ? Questions 18. Nom, emploi, et salaire des employés embauchés le 01/05/91 ou le 03/12/91. 19. Liste des employés dont le matricule du chef n’est pas 7839, 7782 ou 7698. 20. Nom, emploi et date d’embauche des employés qui ne sont ni secrétaire, ni pupitreur.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 19 / 44

SQL

Les Bases du Langage

♦ Opérateurs logiques ou Conditions de connexions (And / Or) Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE [NOT]



{AND | OR} [NOT] Rappel sur les tables de vérité de ces opérateurs A AND B B

Vrai Faux

A

A OR B

Vrai Faux Vrai Faux Faux Faux

B

Vrai Faux

A Vrai Vrai Vrai

Faux Vrai Faux

NOTA F Ces opérateurs peuvent être combinés entre-eux et employés à plusieurs reprises dans une même requête. F Lorsque plus d’un opérateur logique est employé, leur ordre d’évaluation est : NOT / AND / OR F Egalement, ces opérateurs permettent de combiner tous les opérateurs de recherche pour la clause WHERE vus jusqu’ici. F L’emploi de parenthèses () peut changer le sens d’une requête. Exemples /* Liste des employés de la division 30 ayant un salaire inférieur à 7500 F */ SELECT nom, emploi, salaire FROM emp WHERE division = 30 AND salaire < 7500 /* Liste des employés n’appartenant pas à la division 10 ou dont le salaire n’excède pas 7000 F */ SELECT nom, emploi, salaire FROM emp WHERE NOT division = 10 OR salaire <= 7000

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 20 / 44

SQL

Les Bases du Langage

Questions 21. Nom, emploi et salaire des employés embauchés le 01/05/91 ou le 03/12/91. Comparez le résultat obtenu avec celui de la question 18. 22. Liste des employés qui ne perçoivent pas de commission. Comparez le résultat obtenu avec ceux des questions 9 et 10. 23. Liste des employés dont le nom commence par (DU, du) et dont le salaire est compris entre 7000 F et 9000 F. 24. Liste des employés dont le nom se termine par (ON, on). Vous n'utiliserez aucune des fonctions de conversion vues jusqu'ici. Comparez le résultat obtenu avec ceux de la question 17. 25. Liste des analystes, des vendeurs et des pupitreurs dont le nom comporte le lettre (E, e). Vous n'utiliserez aucune des fonctions de conversion vues jusqu'ici. 26. Code des différentes divisions qui embauchent des employés dont le nom comporte un (U, u) en deuxième lettre et dont le salaire est exclu de l’intervalle [7200-8900] F. 27. Matricule, nom, emploi et salaire du Président et des Directeurs des employés (vous utiliserez l'opérateur IS NULL pour rechercher le Président). 28. Nom, salaire et métier des employés dans la catégorie des analystes et nom, salaire et métier des employés dans la catégorie des vendeurs qui ont un salaire supérieur à 8000 F (sans utiliser l’opérateur ensembliste IN). 29. Nom, salaire et métier des employés dans les catégories des analystes et des vendeurs qui ont un salaire supérieur à 8000 F (sans utiliser l’opérateur ensembliste IN). Comparez les résultats des questions 28 et 29. Expliquez.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 21 / 44

SQL

Les Bases du Langage

B.Calculs et présentation de résultats Les syntaxes présentées ne sont que des syntaxes simplifiées. La clause WHERE et les autres opérateurs précédents sont toujours applicables. 1.Opérateurs arithmétiques (+, -, *, /) Syntaxe SELECT

[,…]

FROM

<nom_table>

WHERE



F

Les opérateurs proposés sont applicables sur tous les types de données numériques.

F

L'opérateur arithmétique " + " (addition) permet de concaténer le contenu de colonnes de type chaîne de caractères.

F

Ils sont également utilisables dans la clause WHERE.

F

Il existe d’autres opérateurs mathématiques sous forme de fonction tels que la fonction exponentielle (non utilisable avec des attributs de type money) , le sinus, le cosinus, etc. Vous les trouverez en fin d’aide au paragraphe des fonctions numériques.

F

Les calculs simples du type {+ | - | * | /} ignorent la valeur NULL.

F

Les calculs entre attributs numériques ( {+ | - | * | /} < attribut >)comportant des valeurs NULL ne donneront pas de résultat, la valeur NULL devenant absorbante pour le résultat. Il existe alors une fonction qui nous permettra de contourner ce problème en attribuant une valeur par défaut à l'attribut si sa valeur ponctuelle est NULL : c'est la fonction IFNULL (). Syntaxe de la fonction IFNULL è IFNULL (colonne, valeur par défaut) NOTA Si la colonne est de type chaîne de caractères, la valeur par défaut devra être entre apostrophes. ATTENTION !!

ATTENTION !!

ATTENTION !!

ATTENTION !!

Dans le cas d'une colonne de type numérique, le séparateur entre la colonne et la valeur par défaut devra impérativement être suivi d'un espace, faute de quoi l'analyseur syntaxique interprétera le contenu des parenthèses comme un décimal.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 22 / 44

SQL

Les Bases du Langage

Exemples /* Nom, emploi et salaire augmenté de 10 % pour les employés dont le salaire majoré est supérieur à 10000 F */ SELECT nom, emploi, (salaire * 1,1) FROM emp WHERE salaire * 1,1 > 10000 /* Liste des commissions des employés en fixant à 555 F celles qui sont nulles */ SELECT nom, emploi, salaire, IFNULL (com, 555) FROM emp

Questions 30. Revenu global et emploi pour les secrétaires et les vendeurs. Vous calculerez le revenu global une fois sans utiliser la fonction IFNULL, une autre fois en utilisant cette fonction et en attribuant une commission égale à 0 par défaut. 31. Augmentation de 20 % du budget pour les divisions dont le libellé comporte la lettre (C, c).

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 23 / 44

SQL

Les Bases du Langage

2.Renommer les entêtes de colonnes (As) Syntaxe SELECT

[AS] ["] Titre_colonne ["] [,…]

FROM

<nom_table>

AS

Ce mot-clé réservé non obligatoire permet d’attribuer un titre à une colonne immédiatement après sa sélection. Il ne doit y avoir d’espace significatif dans le titre de la colonne.

Ecrivez alors le titre entre guillemets (" ") ou remplacez les espaces par des ‘_’ (souligné). Exemple /* Nom, emploi et salaire augmenté de 10 % pour les employés dont le salaire est supérieur à 10000 F */ SELECT nom, emploi, (salaire * 1,1) AS " Salaire majoré " FROM emp WHERE salaire > 10000 Questions 32. Matricule, nom, salaire augmenté de 5 % dénommé SALAIRE 1999 pour les employés qui gagnent moins de 7200 F. 33. Calculer le rapport salaire/commission pour chaque employé qui perçoit une commission ( ]0 ; + ∞[ ). Vous donnerez un titre à la colonne calculée.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 24 / 44

SQL

Les Bases du Langage

3.Chaînes de caractères dans les résultats Syntaxe SELECT

‘Texte’, [,…]

FROM

<nom_table>

WHERE



A la différence du mot-clé AS qui permet d’attribuer un nouveau titre à une colonne, la chaîne de caractères sera le contenu d’une nouvelle colonne. Elle apparaîtra donc pour chaque occurrence ramenée. Le texte doit obligatoirement être écrit entre simples quotes. Exemple /* Présentez les différents métiers des employés */ SELECT DISTINCT ‘Un des métiers est : ‘, emploi FROM emp Combien trouvez-vous d’occurrences ? Trouvez l’autre syntaxe dont l’exécution permet d’obtenir une occurrence de moins. Questions 34. Annoncez le Président des employés. 35. Pour les analystes et vendeurs, annoncez leur nom, leur emploi et leur salaire.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 25 / 44

SQL

Les Bases du Langage

4.Tri / Ordonnancement (Order By) Syntaxe SELECT

<liste_colonnes>

FROM

<nom_table>

WHERE [NOT]



{AND | OR} [NOT] ORDER BY

{} {[ASC] | DESC} [,…]

F

Le mot-clé ASC (tri croissant) est optionnel car c’est le tri appliqué par défaut. Par contre, pour un tri décroissant, le mot-clé DESC doit être mentionné.

F

En cas de tri sur le résultat d’un calcul arithmétique, on doit utiliser le titre attribué à l’opération dans la clause de tri ou le numéro d'ordre de la colonne dans la clause SELECT.

F

L'usage des fonctions de conversion vues jusqu'ici est interdit.

F

Les attributs utilisés dans la clause de tri doivent faire partie des colonnes sélectionnées.

Exemples /* Liste des divisions dans l’ordre alphabétique */ SELECT * FROM dept ORDER BY nom_div Donnez une autre écriture de cette requête. /* Nom, emploi et salaire + 10% des employés */ SELECT nom, emploi, (salaire* 1,1) as salaire_majoré FROM emp ORDER BY nom, salaire_majoré DESC Questions 36. Liste des chefs de publicité et directeurs dans l’ordre décroissant de l’emploi. 37. Triez les employés dans l’ordre croissant de leur emploi et dans l’ordre décroissant de leur salaire en utilisant la position des colonnes. 38. Liste des employés de la division 30 dans l’ordre décroissant de leur "revenu global" ( 0 ≤ com ≤ +∞ ) (la colonne calculée apparaîtra sous cette appellation).

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 26 / 44

SQL

Les Bases du Langage

C.Jointures

Tous les opérateurs étudiés précédemment sont applicables. L’opérateur principal pour établir des jointures entre les tables est l’égalité (=). 1.Introduction La jointure permet de faire des rapprochements entre tables par comparaison d'attributs appartenant au même domaine (cf. tables emp et dept : lien avec la colonne division) afin de pouvoir exploiter les données de l’une et de l’autre simultanément. Il est donc important de bien connaître les liens entre les différentes tables de la base. Ces liens s'établissent entre clés primaires et clés étrangères. Ainsi, même s'ils n'ont pas la même dénomination, les attributs auront le même format (type et taille) et les données contenues auront la même signification. Par exemple, la colonne division est une clé étrangère dans la table emp alors qu’elle est clé primaire dans la table dept. Toutes les tables énumérées dans la clause FROM doivent être utilisées. Par principe, il faut un minimum de 2 tables pour réaliser une jointure. Ainsi, pour 2 tables citées dans la clause FROM, on aura 1 jointure ; pour 3 tables citées, on aura 2 jointures, etc. A partir de 3 tables, on utilisera les conditions de connexion (opérateurs logiques) pour enchaîner les critères de jointures. Le nom de la table propriétaire (ou son alias) doit être précisé devant les colonnes ambiguës où qu’elles soient utilisées (Select, Where, Group by, Having, Order by). Car, en général, les colonnes servant pour les jointures ont la même dénomination (ex : la colonne batiment dans les tables dept et batiment). Conseil Lorsque vous avez des jointures à mettre en œuvre, écrivez-les en premier dans la clause WHERE de votre requête SQL. Ainsi, vous serez sûrs de ne pas en omettre. Remarque Nous n’aborderons ci-après que les types de jointures internes. Pour les autres cas, si vous les rencontrez dans des programmes, reportez-vous à la documentation du programme ou à celle du langage SQL utilisé par le SGBDR.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 27 / 44

SQL

Les Bases du Langage

2.Jointure interne Ce type de jointure est également dénommé jointure naturelle. Syntaxe SELECT

<[tablen].nom_colonne> [, …]

FROM

table1, table2 [, …]

WHERE



[{AND | OR}

{ | }]

Avec ó [table1].nom_colonne [table2].nom_colonne Exemple /* Donner le nom, l’emploi et le nom de leur division pour tous les employés */ SELECT nom, emploi, nom_div FROM emp, dept WHERE emp.division = dept.division Questions 39. Donner les divisions et leur budget où l’on trouve des postes d’analyste et de vendeur. L'usage de l'opérateur ensembliste IN vous est strictement défendu pour cette question. 40. Liste des chefs de publicité (matricule, nom, salaire) travaillant à Paris. 41. Liste des employés (nom, emploi, salaire, com) de la division des ventes par ordre croissant de salaire et décroissant de commission.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 28 / 44

SQL

Les Bases du Langage

3.Alias Syntaxe SELECT

<{[tablen | aliasn]}.nom_colonne> [, …]

FROM

table1 alias_table1, table2 alias_table2 [, …]

WHERE



[{AND | OR}

{ | }]

Avec ó [{tablen | alias}].nom_colonne [{tablen | alias}].nom_colonne L’alias permet d’attribuer une dénomination raccourcie à une table. L’intérêt est un gain de temps pour l’écriture des requêtes. Un autre intérêt sera mis en évidence dans le paragraphe suivant. Exemple /* Donner le nom, l’emploi, le numéro et le nom de leur division pour tous les salariés */ SELECT nom, emploi, e.division, nom_div FROM emp e, dept d WHERE e.division = d.division Questions 42. Liste des différents codes postaux et villes des employés ne gagnant pas entre 5000 F et 8000 F. 43. Quels sont les différents noms de division (dans l’ordre croissant) où l’on trouve des postes de Directeur ? 44. Nom, salaire (renommé) et salaire majoré de 4% (renommé) des employés de la division informatique. On fera apparaître l’augmentation détaillée comme suit : salaire, majoration, salaire majoré.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 29 / 44

SQL

Les Bases du Langage

4.Auto-jointure

L’auto-jointure est la liaison d’une table sur elle-même ; il faut donc citer la même table au moins deux fois. Ceci revient à traiter deux tables strictement identiques, les mêmes noms de colonnes apparaissant dans chaque table. Dès lors, on comprend que les jointures peuvent s’opérer indifféremment sur n’importe quelle colonne. Aussi, l’alias qui sera donné à chaque table aura sa part prépondérante à ce niveau-là. Il sera le seul élément permettant de différencier les colonnes des tables entre elles. Syntaxe SELECT

<[tablen | aliasn].nom_colonne> [, …]

FROM

table1 alias1, table1 alias2 [, …]

WHERE



[{AND | OR}

{ | }]

Avec ó alias1.nom_colonne alias2.nom_colonne Exemple /* Afficher le nom et le salaire des employés et, dans la même requête, le nom et le salaire de leur chef respectif */ SELECT e.nom, e.salaire, ec.nom, ec.salaire FROM emp e, emp ec WHERE e.mat_chef = ec.matricule ; Questions 45. Afficher le nom et le salaire des employés avec le nom et le salaire de leur chef respectif et, pour ces chefs, le nom et le salaire de leur chef respectif. Que peut-on observer dans le résultat ? 46. Afficher le nom, le salaire, l’emploi des personnels gagnant plus que leur propre chef. 47. Afficher les divisions dont le budget est inférieur ou égal aux budgets des autres divisions. Vous éliminerez ensuite les comparaisons entre mêmes divisions.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 30 / 44

SQL

Les Bases du Langage

D.Sous-requêtes ou sous-interrogations

Syntaxe SELECT

<[tablen | aliasn].nom_colonne> [, …]

FROM

table1 alias_table1, table2 alias_table2 [, …]

WHERE

[<nom_colonne>]

Avec ó { | | <mot-clé> | <prédicat>} Possibilités de conditions de comparaison La sous-requête retourne une seule occurrence :



= > < <>, != >= <=

(égal à) (supérieur strict à) (inférieur strict à) (différent de) (supérieur ou égal à) (inférieur ou égal à)

La sous-requête retourne un ensemble d’occurrences : <mot-clé> + <mot-clé>

[NOT] IN = (égal à) > (supérieur strict à) < (inférieur strict à) <>, != (différent de) >= (supérieur ou égal à) <= (inférieur ou égal à)

ANY ALL

Test d’existence par rapport au résultat de la sous-requête : <prédicat>

[NOT] EXISTS

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 31 / 44

SQL

Les Bases du Langage

Utilisation -

Les sous-requêtes sont utilisées : à la place d’une jointure (c’est une autre forme d’écriture de jointure entre tables) ; pour appliquer une fonction d’agrégation dans une clause WHERE (sujet abordé dans le chapitre suivant –Chap. F §1-) ; pour vérifier l’existence de lignes (prédicat EXISTS).

Remarques Une sous-requête peut avoir plusieurs niveaux d’imbrications, c’est-à-dire qu’une sousrequête peut contenir une ou plusieurs sous-requêtes. A chaque fois, les résultats de la sousrequête sont substitués dans la requête supérieure. Seules les colonnes sélectionnées dans la clause SELECT de plus haut niveau seront affichées. Dans la sous-requête, la clause SELECT ne peut comprendre qu’un seul nom de colonne. à partir du moment où la clause WHERE de la requête supérieure comprend un seul nom de colonne. Les colonnes ainsi jointes devront être compatibles (même domaine). Le prédicat EXISTS, lui, est un cas particulier puisqu’il n’établit pas de comparaison entre le résultat de la sous-requête avec une colonne dans la clause WHERE supérieure mais il teste l’existence ou non de lignes en commun entre les résultats de la requête et de la sousrequête.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 32 / 44

SQL

Les Bases du Langage

1.Sous-requête avec les opérateurs de comparaison Syntaxe SELECT

<[tablen | aliasn].nom_colonne> [, …]

FROM

table1 alias_table1, table2 alias_table2 [, …]

WHERE

[<nom_colonne>]

F

La comparaison s’opère par rapport à une valeur.

Exemple /* Liste des personnels qui ne sont pas employés dans la division Vente */ SELECT nom, emploi FROM emp WHERE division != (SELECT division FROM dept WHERE nom_div = ‘Vente’) ; Questions 48. Liste des directeurs et secrétaires des divisions dont le budget est compris entre 400000 F et 700000 F. 49. Liste des personnels travaillant à Rennes et gagnant plus de 8000 F. 50. Liste des employés dont M. Garnier est le chef (par sous requête). On affichera le matricule, le nom et l’emploi pour les employés et pour le chef dans la même requête.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 33 / 44

SQL

Les Bases du Langage

2.Sous-requête avec le mot-clé IN Syntaxe SELECT

<[tablen | aliasn].nom_colonne> [, …]

FROM

table1 alias_table1, table2 alias_table2 [, …]

WHERE

[<nom_colonne>] [NOT] IN

F

L’élément recherché doit exister dans l’ensemble pris en compte, résultat de la sousrequête.

Exemple /* Liste des personnels employés dans les divisions Informatique et Vente */ SELECT nom, emploi FROM emp WHERE division IN ( SELECT division FROM dept WHERE nom_div IN (‘Informatique’, ‘Vente’) ; Questions 51. Liste des employés des divisions ayant un budget inférieur à 380000 F. 52. Libellé et budget des divisions non localisées dans le département 75. 53. Nom et revenu global (salaire+com) des employés travaillant dans les divisions de Rennes. Attribuez 0 (zéro) en cas de commission nulle.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 34 / 44

SQL

Les Bases du Langage

3.Sous-requête avec le mot-clé ANY Syntaxe SELECT

<[tablen | aliasn].nom_colonne> [, …]

FROM

table1 alias_table1, table2 alias_table2 [, …]

WHERE

[<nom_colonne>] ANY

F

La condition doit être vérifiée pour au moins un élément de l’ensemble pris en compte, résultat de la sous-requête.

Exemple /* Liste des employés dont le salaire est supérieur à celui d’un directeur */ SELECT nom, emploi, salaire FROM emp WHERE salaire > ANY (SELECT salaire FROM emp WHERE emploi = ‘DIRECTEUR’) ; Questions 54. Liste des divisions dont le budget est supérieur à celui d’une division employant du personnel dont le nom débute par ‘GA’. 55. Nom, emploi, salaire des personnels qui touchent plus qu’au moins un des salariés de la Direction.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 35 / 44

SQL

Les Bases du Langage

4.Sous-requête avec le mot-clé ALL Syntaxe SELECT

<[tablen | aliasn].nom_colonne> [, …]

FROM

table1 alias_table1, table2 alias_table2 [, …]

WHERE

[<nom_colonne>] ALL

F

La condition doit être vérifiée pour chaque élément de l’ensemble pris en compte, résultat de la sous-requête. Donc pour tous les éléments de l’ensemble.

Exemple /* Liste des employés dont le salaire est supérieur à ceux de tous les directeurs */ SELECT nom, emploi, salaire FROM emp WHERE salaire > ALL (SELECT salaire FROM emp WHERE emploi = ‘DIRECTEUR’) ; Questions 56. Liste des bâtiments (avec les divisions) où sont installées les divisions dont le budget est supérieur aux budgets des divisions employant du personnel dont le nom débute par un B. 57. Nom, emploi, salaire des personnels qui touchent plus que tous les salariés du département Informatique.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 36 / 44

SQL

Les Bases du Langage

5.Prédicat EXISTS Syntaxe SELECT

<[{tablen | aliasn}].nom_colonne> [, …]

FROM

table1 alias_table1, table2 alias_table2 [, …]

WHERE

[NOT] EXISTS

F

EXISTS est utilisé pour trouver les éléments de l’intersection de deux ensembles.

F

NOT EXISTS est utilisé pour trouver les éléments appartenant à la différence de deux ensembles.

F

[NOT] EXISTS n’est pas précédé d’un nom de colonne.

F

L’instruction SELECT de la sous-requête devra nécessairement contenir une jointure avec une (ou la) table citée dans la requête supérieure. On parle alors de requêtes correlées.

F

La liste de colonnes qui sera énoncée dans la clause SELECT de la sous-requête sera généralement " * " (tous les champs) car la fonction [NOT] EXISTS renvoie VRAI ou FAUX (pas de donnée retournée).

Exemple /* Quelles divisions n’emploient pas de salariés ? */ SELECT FROM WHERE

* dept NOT EXISTS (SELECT FROM WHERE

* emp emp.division = dept.division) ;

Questions 58. Quelles divisions (libellé et budget) emploient des salariés à plus de 9000 F ? 59. Dans quels bâtiments ne sont pas installées les divisions qui emploient des salariés ?

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 37 / 44

SQL

Les Bases du Langage

E.Organiser les données et synthétiser les résultats 1.Fonctions d'agrégation Syntaxe SELECT

[liste_colonnes], fonction-agrégation ({colonne | expression}) [, …]

FROM

table [, …]

WHERE



Signification Mot-clé de la fonction SUM AVG MIN MAX COUNT (*) COUNT ( [DISTINCT] nom_colonne )

Valeur calculée Total Valeur moyenne Valeur minima Valeur maxima Nombre de lignes Nombre de valeurs uniques

Principes sur les fonctions d’agrégation F

Elles ignorent les valeurs NULL, sauf count(*).

F

SUM et AVG utilisent uniquement des valeurs numériques.

F

Elles renvoient seulement une ligne (si une clause GROUP BY n’est pas utilisée).

F

Elles ne peuvent être utilisées que dans une clause SELECT <liste_colonnes>.

F

Elles ne peuvent pas être utilisées dans une clause WHERE.

F

Elles peuvent être appliquées à toutes les lignes d’une table ou à un sous-ensemble.

F

On peut utiliser plus d’une fonction d’agrégation dans une sélection.

F

Les opérateurs arithmétiques peuvent être utilisés avec les fonctions SUM, AVG, MAX, MIN.

F

Les colonnes résultats de fonction d’agrégation peuvent être renommées pour être plus significatives.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 38 / 44

SQL

Les Bases du Langage

♦ Les fonctions SUM / AVG / MAX / MIN F F F F

SUM calcule la somme des valeurs de la colonne spécifiée. AVG calcule la valeur moyenne des valeurs de la colonne spécifiée. MAX trouve la plus grande valeur. MIN trouve la plus petite valeur.

Exemple /* Donner les somme, moyenne, valeurs maxima et minima des salaires des employés de la division 30*/ SELECT SUM (salaire), AVG (salaire), MAX (salaire), MIN (salaire) FROM emp WHERE division = 30 ♦ La fonction COUNT F

COUNT compte le nombre de lignes qui vérifient une éventuelle condition.

Exemple /* Donner le nombre d’employés travaillant dans la division 10 */ SELECT COUNT (*) FROM emp WHERE division = 10 ♦ Le mot-clé DISTINCT F F F

Optionnel avec les fonctions SUM, AVG, COUNT. Interdit avec les fonctions MIN, MAX, COUNT (*). Utilisé seulement avec les noms de colonnes, pas avec les expressions arithmétiques.

Exemples /* Donner le nombre d’emplois différents */ SELECT COUNT (DISTINCT emploi) FROM emp

/* Compter le nombre d’emplois */ SELECT COUNT(emploi) FROM emp

Comparer ces deux résultats.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 39 / 44

SQL

Les Bases du Langage

Questions Pour toutes les questions qui suivent, les colonnes de calculs seront renommées. 60. Donner la moyenne des différents salaires des employés. 61. Donner la moyenne des salaires des employés. 62. Donner la moyenne des revenus globaux des employés (en conservant les commissions pouvant être nulles). 63. Donner la moyenne des revenus globaux des employés (en éliminant les commissions pouvant être nulles). Comparer ces deux résultats. 64. Compter le nombre d’employés qui comportent la lettre (T, t) dans leur nom. 65. Liste des employés ayant un salaire supérieur au salaire moyen de la société. 66. Compter le nombre de divisions différentes qui emploient soit des analystes, soit des vendeurs dont le salaire varie entre 4000 F et 8000 F et qui ne touchent pas de commission ( [0 ; + ∞ [ ). 67. Nombre de divisions implantées à Rennes. 68. Donner les divisions ayant le plus fort et le plus faible budget. 69. Masse salariale des employés travaillant à Paris. 70. Masse budgétaire des divisions employant des vendeurs et des analystes. 71. Donner la somme des différents budgets, la masse salariale et le rapport de ces deux calculs pour toutes les divisions du département d’Ile-de-France (75).

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 40 / 44

SQL

Les Bases du Langage

2.Regroupement (Group By) SELECT

<liste_sélection>

FROM

table [, …]

WHERE



GROUP BY

[, …]

F

Les fonctions d’agrégation sont interdites dans la clause GROUP BY.

F

Le regroupement de lignes ne peut s'effectuer que sur les noms des colonnes contenues physiquement dans la base de données.

F

L'usage des titres (attribués par requête), des calculs (arithmétique ou agrégat) ainsi que des diverses fonctions de conversion vues jusqu'ici est interdit dans cette clause.

F

La clause GROUP BY regroupe les lignes sur la base de leur contenu.

F

Les valeurs NULL dans la colonne du GROUP BY sont considérées comme un groupe.

F

La clause GROUP BY doit contenir les colonnes de tables énoncées dans la clause SELECT. En revanche, on peut effectuer des regroupements sur des colonnes non citées au niveau du SELECT.

Exemples /* Nombre d’employés par code division */ SELECT count(*), division FROM emp GROUP BY division ; Questions 72. Nombre de salariés et nombre d’emplois distincts par ville. 73. Donner la moyenne salariale par division (nom de division) et par ville. Vous donnerez un titre représentatif à la moyenne salariale. 74. Donner par division le nombre de salariés embauchés entre début 1993 et fin 1996. 75. Liste des salariés qui ont le plus grand salaire de leur division.

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 41 / 44

SQL

Les Bases du Langage

3.Condition de regroupement (Having) ou division en algèbre relationnelle SELECT

<liste_sélection>

FROM

table [, …]

WHERE



GROUP BY

[, …]

HAVING



Avec

F F F F

= {} . {}

La clause HAVING est similaire à la clause WHERE mais elle pose les conditions sur la clause GROUP BY. La condition est appliquée aux groupes après leur formation. Les fonctions d’agrégation sont autorisées dans la clause HAVING. Si la clause HAVING compare une colonne à une valeur ou une sous-requête, cette colonne devra être contenue dans la clause SELECT de même niveau. ♦ Rappel sur le principe de la division en algèbre relationnelle

Soit la relation suivante : HISTORIQUE

(CODELEVE, DATERELEVE, MOYGENE)

Cette relation (ou table) mémorise la moyenne trimestrielle de chaque élève. Il n’y a que trois dates de relevé possibles : une par trimestre (31/12/1998, 31/03/1999, 30/06/1999). Ainsi, selon la période de l’année, il y a 0, 1, 2 ou 3 dates de relevé différentes dans la table. Soit la question suivante : quels sont les élèves pour lesquels je dispose de tous les relevés de moyenne générale ? Plaçons-nous en fin de deuxième trimestre et considérons que la mémorisation des moyennes a été effectuée pour une partie des élèves. Donc, pour certains élèves, il y a une seule date de relevé (31/12/1998) tandis que, pour d’autres, il y a deux dates de relevé (31/12/1998 et 31/03/1999). Ainsi, répondre à une telle question nous permet de définir indirectement ceux pour lesquels le travail de mémorisation de la moyenne n’a pas été effectué. DIVISEUR : tous les relevés R1 = PROJECT HISTORIQUE (DATERELEVE) DIVIDENDE R2 = PROJECT HISTORIQUE (CODEELEVE, DATERELEVE) RESULTAT R3 = DIVISION R2 % R1 (DATERELEVE)

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 42 / 44

SQL

Les Bases du Langage

♦ Application de la division en langage SQL Nous allons dénombrer deux quantités puis les comparer dans une requête.  1ère quantité : le diviseur. Comptons le nombre de dates de relevé différentes dans la table. SELECT COUNT DISTINCT (DATERELEVE) FROM HISTORIQUE  2nde quantité : le dividende. Comptons le nombre de dates de relevé différentes par élève. SELECT CODEELEVE, COUNT (DATERELEVE) FROM HISTORIQUE GROUP BY CODE ELEVE Comparons maintenant ces deux quantités dans une seule requête. On ne conserve que les élèves qui ont autant de date de relevé qu’il en existe en tout, soit 2. SELECT CODEELEVE FROM HISTORIQUE GROUP BY CODE ELEVE HAVING COUNT (DATERELEVE) = (

SELECT COUNT DISTINCT (DATERELEVE) FROM HISTORIQUE) ;

Exemple /* Donner la moyenne salariale pour les divisions employant moins de 8 personnes */ SELECT nom_div, avg(salaire) FROM dept d, emp e WHERE e.division = d.division GROUP BY nom_div HAVING count(matricule) < 8 ; Questions 76. Afficher les emplois communs à toutes les divisions. 77. Quelles sont les divisions dont le salaire moyen de ses employés est supérieur au salaire moyen des employés de la société ?

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 43 / 44

SQL

Les Bases du Langage

VI.Des mots-clés S.Q.L. au Français

MOT-CLE S.Q.L.

SIGNIFICATION

SELECT

Trouver, lister, sélectionner

DISTINCT

Pas de doublons, "les différents …"

WHERE

Qui, où, dont

BETWEEN

Entre avec inclusion des bornes

LIKE

Identique à, comme, qui ressemble à

IN

Egal à l'un des suivants, "soit …, soit …"

ORDER BY

Arranger, organiser, trier

ANY

Par rapport à au moins un élément de l’ensemble

ALL

Par rapport à tous les éléments de l’ensemble

EXISTS

Trouver au moins une ligne

GROUP BY

Organiser en, (re)grouper

HAVING

Après condition de groupement

DMSI/ANALYSE du 11/10/2009

Instruction Select.doc

Page 44 / 44

---

Related Documents

Instruction Select

June 2020 12

Sql - Select Within Select

October 2019 20

Instruction

October 2019 63

Instruction

November 2019 48

Instruction

November 2019 49

Instruction

October 2019 58

Copyright © 2024 PDFCOKE.