Mise en place du système d’information

INTRODUCTION A LA CREATION DES BASES DE DONNEES

1. INTRODUCTION

La mise en place du système d’information est un atout décisif dans le monde actuel [1]. On considère que le fonctionnement normal de toute entreprise1 conduit à une suite de décisions qui ont chacune plus ou moins de conséquences selon la position hiérarchique du décideur.

Le rôle du système d’information va être de recueillir, de mémoriser, de véhiculer et de fournir les informations pertinentes liées au bon fonctionnement d’une organisation

role du systeme information

2. DEFINITION D’UN SYSTEME D’INFORMATION

D’un point de vue général un système d’information doit permettre

  •  La production d’information
  •  La mise en œuvre des moyens destinés à la réalisation cette production

D’un point de vue strictement informatique d’un système d’information doit permettre de

  •  Collecter des informations : Ces informations doivent être formelles (connues et existantes)
  •  Traiter et transmettre : Font référence à des opérations techniques
  •  Mémoriser : Doit être réalisé de façon ordonnée et non anarchique, archivage judicieux, structuration de façon à pouvoir retrouver rapidement l’information

3. CONCEPTION D’UN SYSTEME D’INFORMATION

Pour concevoir un système d’information, il est nécessaire de s’appuyer sur les concepts de modèle (modèles de traitement et modèles de données)

Un modèle de données est un outil intellectuel qui permet une représentation du monde réel perçu au travers des informations. Il est suffisement puissant pour capter la sémantique des données et représente le lien qu’il existe entre elles.

  •  Un modèle de traitement permet de modéliser l’aspect dynamique de l’organisation
  •  Un modèle possède bien souvent une représentation graphique
  •  Une méthode d’analyse (méthode de conception ou d’aide à la conception) représente un ensemble ordonné de règles opératoires qui permet de résoudre un problème en accord avec les concepts du modèle considéré

Les informations en sortie peuvent être différentes selon les utilisateurs : le chef des ventes veut des statistiques de vente mensuelles pour les transmettre au service achat alors qu’un employé veut les commandes au jour le jour. Pour pouvoir réaliser ces deux opérations, les données doivent suivre un formalisme rigoureux.

Plusieurs méthodes d’aide à la conception d’un système d’information existent : On peu se tourner vers la méthode merise qui malgré son âge reste toujours d’actualité pour la conception des outils simples [2] et [3]. Je ne vous cache pas que la conception orientée (très en vogue actuellement) objet présente des difficultés importantes tant au niveau des concepts maniés que des outils à maîtriser (voir plus avant dans ce document) [4]. En ce qui concerne la suite de ce chapitre, qui détaille les aspects de conception, la méthode reste identique quelque soit les outils utilisés par la suite.

Dans tout projet (que l’on cherche à réussir) on doit mettre en place un cycle de développement qui passe par les étapes suivantes:

3.1. LA PHASE D’ANALYSE

Cette phase définie les attentes par rapport au projet. Elle coûte environ 20 à 30% des efforts liés au projet. C’est une phase clé : Si elle est négligée, on risque de gros ennuis par la suite.

3.1.1. PROPOSITION INITIALE

C’est un document bref qui justifie de l’intérêt de cette étude. On doit identifier les personnes impliquées et leur rôle, les structures intéressées dans l’entreprise et définir les besoins de façon sommaire. Un état de l’existant peut trouver sa place dans cette phase (description de l’existant, avantages et contraintes)

3.1.2. ETUDE DE FAISABILITE

Elle doit examiner l’intérêt du nouvel outil en terme d’avantages et de contraintes (ressources humaines, matérielles, …). Cette étude doit juger des capacités techniques :

  • En disposons nous en interne ?
  • Devons nous faire appel à des ressources externes ?
  • Quel serait le coût ?

On devra étudier la faisabilité technique, économique, l’analyse des motivations, le calendrier, la faisabilité opérationnelle (évaluation de l’utilisation potentielle du système).

Cette étude de faisabilité est donc une étude de risques associés au futur projet : risque technique, économique, humain, de délais et de risque lié aux évolutions de l’entreprise.

3.1.3. ANALYSE DES BESOINS

C’est une étape primordiale et indispensable. Elle doit permettre d’exprimer ce que doit réaliser le futur outil. Elle s’exprime donc en terme de résultats attendus. Quels sont les besoins des utilisateurs ? En fonction des organisations on pourra opter pour deux types d’approches

 Descendante : Du responsable aux subalternes mais les soucis du chef peuvent être différents de ceux des subalternes

 Ascendante : Du personnel vers le responsable. On risque une profusion de détails et du coup une vision par trop parcellaire et donc une synthèse inadéquate.

Cet aspect de définition des besoins est clairement la partie qui souvent pèche dans les PME2.

Arriver à motiver les utilisateurs et les responsables autour d’un tel projet n’a rien de simple.

Il faut motiver les participants arriver à les intéresser aux résultats, à les impliquer. C’est le rôle (pas toujours simple) du porteur de projet.

Pour cela il convient de réaliser des entretiens de groupe avec chacune des structures de l’entreprise, des entretiens individuels, des réunions de mis en commun des idées (créativité).

Il peut être intéressant de demander aux futurs utilisateurs de concevoir sur papier les écrans sur lesquels ils vont devoir travailler (Quelles informations ont ils besoin?), classer ces différentes informations de la plus essentielle à la moins nécessaire.

Par exemple les questions à poser pourraient être :

Au responsable commercial : Quelle forme doivent prendre les états mensuels ? Avez vous besoin d’exporter les données sous un tableur pour en réaliser des graphiques (par exemple) ?

Au des commandes au jour le jour : désirez-vous une alerte lorsque vous ouvrirez l’outil ? Quelle pourrait être la forme sous laquelle vous voulez voir apparaître les données ? Par commande ? Par client ? Par date ? Est il nécessaire d’avoir une liaison vers les coordonnées du client concerné ? Comment voulez vous voir cette information une fenêtre bondissante suite à un clic sur un bouton ?

Il est également important de prévoir les évolutions de l’outil et son adaptabilité : changement de la définition des écrans (de 800 x 600 vers 1200 x 800) changement du type de programme de tableur (d’Excel de Microsoft vers Calc d’Openoffice par exemple).

Enfin il convient de s’intéresser à la sécurité.

La sécurité en matière d’accès : Qui doit avoir accès aux données ? L’accès en consultation est elle suffisante ou l’accès en écriture est nécessaire ?

La sécurité en matière de sauvegarde : Quelle méthode de sauvegarde, incrémentiel, totale, incrémentiel tous les jours et totale une fois par semaine, sécurité de ces sauvegardes…

Cette étape de définition des besoins doit aboutir à un cahier des charges exhaustif et précis.

3.1.4. DESCRIPTION FONCTIONNELLE

C’est l’analyse fonctionnelle. Les flux entrant et sortant, la séparation des opérations manuelles et automatiques, les fonctions réalisées par l’application, l’organigramme générale des traitements, les processus de contrôle et d’audit

3.2. CONCEPTION ET REALISATION

3.2.1. DESCRIPTION ORGANIQUE

Cette étape à pour but de préciser les choix techniques. La phase d’analyse joue un rôle très important dans la qualité du produit fini. Le logiciel (ou l’application) est décomposé en éléments unitaires eux même décomposés en opérations unitaires pour faciliter la maintenance et les lectures ultérieurs

3.2.2. DESCRIPTION DES BASES DE DONNEES

C’est la description physique qui est réalisée à partir de la description conceptuelle du modèle.

3.2.3. ECRITURES DES PROGRAMMES

C’est du ressort des informaticiens. Ils doivent tenir compte des spécificités décrites dans les éléments précédents

3.2.4. PREPARATION DES PROCEDURES OPERATIONNELLES

C’est la réalisation du mode d’emploi : manuel utilisateur, manuel de formation et manuel de maintenance. Ces manuels sont primordiaux quant à la pérennité de l’outil malheureusement c’est une phase qui est souvent négligée.

3.3. MISE EN PLACE

Cette phase est délicate. Elle « coûte » environ 20% du temps.

3.3.1. BASCULEMENT VERS LE NOUVEAU SYSTEME

Cela comprend les phases de test, la récupération des anciennes données, la formation des utilisateurs et l’implantation du nouvel outil.

3.3.2. MAINTENANCE

Matériel et logiciel : La prudence est de rigueur. Il ne faut pas croire que l’on passe aisément d’une version à une autre sans qu’il y ait des « effets de bord ». L’installation d’un nouveau pilote d’impression peut tout compromettre (ça s’est déjà vu…). Ne rien changer tant que l’on aura pas pris le temps de réaliser des tests validés.

3.3.3. AUDIT A POSTERIORI

L’audit est toujours nécessaire. Il permet de détecter les soucis et de prévoir leur correction Si le système est correctement conçu, on ne devrait pas avoir de problèmes insurmontables.

Les modifications devrait être que mineures. Par contre si on doit réaliser des modifications de fond c’est qu’une des étapes a été négligée. Un manque d’exhaustivité ou de précision dans le cahier des charges peut être ?

4. LES BASE DE DONNEES

4.1. QU’EST-CE QU’UNE BASE DE DONNEES? qui est ce que une base de donnees

Une base de données (son abréviation est BD, en anglais DB, database) est une entité dans laquelle il est possible de stocker des données de façon structurée et avec le moins de redondance possible. Ces données doivent pouvoir être utilisées par des programmes, par des utilisateurs différents. Ainsi, la notion de base de données est généralement couplée à celle de réseau, afin de pouvoir mettre en commun ces informations, d’où le nom de base. On parle généralement de système d’information pour désigner toute la structure regroupant les moyens mis en place pour pouvoir partager des données.

4.2. UTILITE D’UNE BASE DE DONNEES?

Une base de données permet de mettre des données à la disposition d’utilisateurs pour une consultation, une saisie ou bien une mise à jour, tout en s’assurant des droits accordés à ces derniers. Cela est d’autant plus utile que les données informatiques sont de plus en plus nombreuses.

Une base de données peut être locale, c’est-à-dire utilisable sur une machine par un utilisateur, ou bien répartie, c’est-à-dire que les informations sont stockées sur des machines distantes et accessibles par réseau.

L’avantage majeur de l’utilisation de bases de données est la possibilité de pouvoir être accédées par plusieurs utilisateurs simultanément.

4.3. LA GESTION DES BASES DE DONNEES

Afin de pouvoir contrôler les données ainsi que les utilisateurs, le besoin d’un système de gestion s’est vite fait ressentir. La gestion de la base de données se fait grâce à un système appelé SGBD (système de gestion de bases de données) ou en anglais DBMS (Database management system). Le SGBD est un ensemble de services (applications logicielles) permettant de gérer les bases de données, c’est-à-dire:

  • Permettre l’accès aux données de façon simple
  •  Autoriser un accès aux informations à de multiples utilisateurs
  •  Manipuler les données présentes dans la base de données (insertion, suppression, modification)

5. LES PRINCIPAUX SGBD

Les principaux systèmes de gestion de bases de données sont les suivants:

les principaux systemes de gestion de bases de donnees

5.1. LES NIVEAUX ANSI/SPARC

On définit des niveaux d’abstraction pour un système de gestion de bases de données:

1. Niveau interne (ou physique): Il définit la façon selon laquelle sont stockées les données et les méthodes pour y accéder

2. Niveau conceptuel: appelé aussi MCD (modèle conceptuel des données) ou MLD (modèle logique des données). Il définit l’arrangement des informations au sein de la base de données

3. Niveau externe: Il définit les vues des utilisateurs

5.2. LES CARACTERISTIQUES D’UN SGBD

L’architecture à trois niveaux définie par le standard ANSI/SPARC permet d’avoir une indépendance entre les données et les traitements. D’une manière générale un SGBD doit avoir les caractéristiques suivantes:

Indépendance physique : Le niveau physique peut être modifié indépendamment du niveau conceptuel. Cela signifie que tous les aspects matériels de la base de données n’apparaissent pas pour l’utilisateur, il s’agit simplement d’une structure transparente de représentation des informations

Indépendance logique : le niveau conceptuel doit pouvoir être modifié sans remettre en cause le niveau physique, c’est-à-dire que l’administrateur de la base doit pouvoir la faire évoluer sans que cela gêne les utilisateurs

Manipulabilité : des personnes ne connaissant pas la base de données doivent être capables de décrire leurs requêtes sans faire référence à des éléments techniques de la base de données

Rapidité des accès : le système doit pouvoir fournir les réponses aux requêtes le plus rapidement possible, cela implique des algorithmes de recherche rapides

Administration centralisée : le SGBD doit permettre à l’administrateur de pouvoir manipuler les données, insérer des éléments, vérifier son intégrité de façon centralisée

Limitation de la redondance : le SGBD doit pouvoir éviter dans la mesure du possible des informations redondantes, afin d’éviter d’une part un gaspillage d’espace mémoire mais aussi des erreurs

Vérification de l’intégrité : les données doivent être cohérentes entre elles, de plus lorsque des éléments font référence à d’autres, ces derniers doivent être présents

Partageabilité des données : le SGBD doit permettre l’accès simultané à la base de données par plusieurs utilisateurs

Sécurité des données : Le SGBD doit présenter des mécanismes permettant de gérer les droits d’accès aux données selon les utilisateurs

Les bases de données sont apparues à la fin des années 60, à une époque où la nécessité d’un système de gestion de l’information souple se faisait ressentir. Il existe cinq modèles de SGBD, différenciés selon la représentation des données qu’elle contient :

5.2.1. LE MODELE HIERARCHIQUEle modele hierarchique

Les données sont classées hiérarchiquement, selon une arborescence descendante. Ce modèle utilise des pointeurs entre les différents enregistrements. Il s’agit du premier modèle de SGBD

5.2.2. LE MODELE RESEAUle modele reseau

Comme le modèle hiérarchique ce modèle utilise des pointeurs vers des enregistrements. Toutefois la structure n’est plus forcément arborescente dans le sens descendant

5.2.3. LE MODELE RELATIONNELle modele relationnel

Les données sont enregistrées dans des tableaux à deux dimensions (lignes et colonnes). La manipulation de ces données se fait selon la théorie mathématique des relations

5.2.4. LE MODELE DEDUCTIF

Les données sont représentées sous forme de table, mais leur manipulation se fait par calcul de prédicats

5.2.5. LE MODELE OBJET (SGBDO, SYSTEMEDEGESTIONDE BASES DE DONNEES OBJET)

Les données sont stockées sous forme d’objets, c’est-à-dire de structures appelées classes présentant des données membres. Les champs sont des instances de ces classes. Cette méthode permet de regrouper dans une même entité les structures et les traitements. Ainsi l’approche3 conceptuelle orientée objets repose sur quatre principes de base :

l’abstraction des données,

le partage des comportements,

la prise en compte de l’évolution,

la validité.

L’abstraction des données consiste à permettre une approche à haut niveau d’abstraction offrant à l’utilisateur une vision externe des données proche de sa définition conceptuelle.

Cette approche est possible grâce à la modularisation, qui consiste à structurer un problème en sous problèmes récursivement, et à la décision de cacher les détails d’implémentation à l’utilisateur, lui laissant pour seule tâche de comprendre le concept du produit et son mode d’emploi, sans se préoccuper de sa structure.

Le principe de partage des comportements consiste à définir les entités par leur comportement, c’est à dire leur interface externe, et à faire partager ces comportements par les entités de nature identique ou semblable. Ce partage peut se faire par classification, c’est à dire en regroupant des entités semblables, donc possédant le même comportement, en classes. De plus, une taxonomie des classes peut être mise en oeuvre, permettant l’utilisation de mécanismes de spécialisation et d’inclusion.

La prise en compte de l’évolution est facilitée par les deux aspects précédents. L’évolution peut avoir deux formes : l’évolution des besoins, nécessitant des modifications et des ajouts, et le développement d’un produit par la méthode incrémentale. L’approche objet permet de traiter ces deux aspects de manière similaire, et applique à l’ensemble du cycle de développement le modèle incrémental. L’approche objet est par essence évolutionnaire.

Enfin, le souci de validité consiste à fournir au développeur des moyens de vérifier a priori la validité de la structure du système qu’il conçoit. Cette préoccupation n’a pas toujours été respectée par les systèmes orientés objets, mais elle est tout particulièrement importante dans des domaines où l’impossibilité pour un objet de répondre à un message potentiel est inacceptable et doit être détectée au plus tôt, en tous cas avant l’exécution en exploitation (applications temps réel ).

A la fin des années 90 les bases relationnelles sont les bases de données les plus répandues (environ trois quarts des bases de données). De plus en plus de système s’oriente vers le modèle orienté objet.

6. LES BASE DE DONNEES RELATIONELLES

6.1. INTRODUCTION

Le fondement du modèle relationnel trouve son origine dans la théorie des ensembles aux fichiers informatiques. Les objectifs sont :

  • 1. D’utiliser des structures simples
  • 2. Proposer des langages par différents types d’utilisateurs
  • 3. De proposer une indépendance entre données et traitement
  • 4. De permettre des vues utilisateurs différentes des relations implantées

La relation est la raison d’être du modèle relationnel. On nomme relation une table mettant en relation des données ou plusieurs tables en relations entre elles. On caractérise une relation par :

  • L’occurrence : C’est une ligne de la table
  •  La cardinalité : C’est le nombre de lignes de cette relation
  •  Le degré : C’est le nombre de colonnes d’une relation
  •  L’attribut : Cela défini les champs mémorisés dans la relation
  •  La clé : Attribut ou ensemble d’attributs dont la valeur permet de distinguer de façon certaine les occurrences entre elles

Je suis sincèrement désolé mais les bases des données sont comme l’amour…. Pour aller plus lion il faut aller plus prêt. C’est pour cette raison que nous devrons aborder des notions peu simples mais parfaitement compréhensibles (puisque je les ai comprises) pour peu que l’on s’en donne la peine. Si vraiment vous êtes allergique à tout cela je vous conseille de lire, au moins, le présent chapitre.

6.2. LA CONCEPTION

Maintenant que vous connaissez tout sur les relations nous allons aborder quelques points particuliers de conception.

6.2.1. COMMENT FAIRE ? UNE OU PLUSIEURS TABLES ?

C’est simple. La question a poser est : Cette donnée risque-t-elle de se retrouver plusieurs fois dans ma table ?

Par exemple on conçoit un outil de gestion d’adresse d’entreprise. Il nous faut inclure plusieurs champs :

raison_social, perm_adress, second_adress, code_postal, ville, pays, index_téléphonqie_pays, téléphone

On remarque que le champ pays et que le champ index_téléphonique_pays risque de stocker souvent la même information (sans compter que l’on aura à taper à chaque fois).

La table ressemblera à :

conception une ou plusieurs tables

On peut donc « sortir » cette donnée de la table pour en faire une table secondaire qui stockerai les pays avec leurs indicatifs téléphoniques.

Par une liaison on fera en sorte de ne pas perdre cette information que l’on pourra retrouver aisément dans une requête.

sortir donnee de la table pour en faire une table secondairesortir donnee de la table pour en faire une table secondaire 2

En terme de stockage, le fait de stocker France « coûte » environ 6 octets et l’indicatif téléphonique « coûte » 2 octets.

Dans le premier cas où l’on aurait 1000 adresses, le coût total serait de 8000 octets.

Dans la seconde solution on stockera « France » et « 33 » une seule fois soit 8 octets. La liaison « coûtera » 1 octet par adresse soit 1000 octets. 1038 contre 8000. Il n’y a pas photo….

6.2.2. L’AUTO LIAISON

Il existe des cas où on a besoin de rattacher des éléments à d’autres éléments de la même table. Par exemple on veut faire la liste des composants d’un groupe d’ordinateur. Les éléments sont La carte réseau, l’écran, le clavier la souris, la carte son, …. Les informations saisies pour chacun de ces éléments sont identiques : définition, identification, fabricant, date achat, prix, référence, numéro de série.

On fera en sorte de relier chacun de ses composants à l’un des éléments de cette table. La table ressemblera à :

auto liaison base de donnees

On rattache chaque élément à un élément de la table. En ordonnant cette table on aura :

auto liaison base de donnees 2

Rudiments SQL pour Oracle

Résumé

Ce support de cours regroupe les instructions SQL basiques qui permettent de mettre en place une base de données sur un serveur Oracle, de la remplir et de l’interroger. Ce document, volontairement succinct, peut servir d’aide mémoire des commandes SQL essentielles, évitant ainsi la (re)-lecture du manuel utilisateur ou de tout autre ouvrage volumineux sur Oracle.

Introduction

Oracle est un Système de Gestion de Bases de Données (SGBD) Relationnelles (SGBDR). Il s’agit d’un logiciel édité par Oracle Corporation et dont la première version remonte à 1977.

SQL (pour Structured Query Langage) est un langage de communication standard avec les SGBDR.

Il a été conçu par IBM en 1976, puis normalisé en 1986, 1992, 1999 et 2003.

Comme premier exemple d’utilisation de SQL avec Oracle, prenons la création sur un serveur Oracle d’un nouvel utilisateurcgruauavec le mot de passe provisoirecgpwd. Pour effectuer cette création, l’administrateur doit saisir les commandes SQL suivantes :

Conseils généraux sur la programmation SQL :

– SQL ne fait pas la différence entre les minuscules et les majuscules , donc nous adoptons la convention suivante (dans un soucis de lisibilité) : les mots-clés SQL en majuscules et les autres noms en minuscules ;

– le langage autorise également autant d’espaces et de sauts de ligne que l’on veut entre les mots de la programmation ; nous en profitons pour couper les lignes trop longues (celles qui dépassent la page ou l’écran) et pour indenter rigoureusement le texte afin d’aligner verticalement les éléments d’une même catégorie ;

– il ne faut pas hésiter non plus hésiter à commenter abondamment le code à l’aide des doubles tirets–;

– enfin, il ne faut pas oublier le point-virgule à la fin de chaque instruction, car c’est lui qui déclenche son exécution.

Remarques sur l’exemple précédent :

– les espaces de travailtbsusers,tmpusersettoolsexistent déjà sur le serveur Oracle et il est conseillé de définir, pour chaque utilisateur, des quotas sur ces espaces communs ;

– le fait que le mot de passecgpwdexpire à la première connection, oblige l’utilisateurcgruauà choisir son propre mot de passe à l’insu de l’administrateur (sécurité et confidentialité) ;

– enfin, l’instructionGRANT CONNECTest indispensable pour que l’utilisateurcgruaupuisse se connecter au serveur Oracle.

1 Création de tables

Une partie du langage SQL est dédiée à la mise en place et à la maintenance du schéma relationnel des bases de données.

1.1 Syntaxe

Pour créer une table Oracle en SQL, il existe l’instructionCREATE TABLEdans laquelle sont précisés pour chaque colonne de la table : son intitulé, son type de donnée et une ou plusieurs contrainte(s) éventuelles(s). Pour créer les trois tables du schéma relationnel de la figure 1 :

trois tables et une cle etrangere

les trois instructions SQL correspondantes sont les suivantes (les noms des colonnes sont volontairement simplifiés) :

trois instructions sql

Remarques :

– le type de donnée d’une clé étrangère doit être identique à celui de la clé primaire référencée ;

– une clé étrangère doit être créée après la table qu’elle référence ;

– pour les colonnes qui participent à une clé primaire, il est inutile de préciserNOT NULL, car c’est implicitement le cas avecPRIMARY KEY;

– une valeur par défaut (déclarée avecDEFAULT) doit être une constante ou une fonction sans argument ;

– lorsque la valeur pas défaut n’est pas précisée, pour Oracle il s’agit de la valeurNULL;

– la fonctionSYSDATEretourne la date et l’heure courantes du serveur Oracle ;

– les noms que l’on donne aux tables, aux colonnes, etc., n’acceptent que des lettres (non accentuées) et des chiffres (sauf pour le premier caractère), éventuellement le tiret bas mais surtout pas d’espace ;

– les mots-clés SQL sont réservés, on ne peut donc pas appeler une colonnedate.

Une table qui existe déjà peut être modifiée par l’instructionALTER TABLEaccompagnée d’une clause ADDouMODIFY:

instruction alter table

Une table qui n’est référencée par aucune clé étrangère peut être supprimée à l’aide de l’instruction DROP TABLE. Si la suppression est autorisée, toutes les données de la table sont perdues. Dans notre exemple, l’instruction suivante :

drop table clients

est refusée car la tablecommandesréférence la tableclients.

Pour supprimer ou modifier une table, il faut en connaître le nom. L’instruction suivante permet à un utilisateur d’Oracle de retrouver toutes ses tables :

select all from user tables

Il peut également être utile de connaître la liste des colonnes d’une table en particulier. Pour cela, l’utilisateur dispose de l’instruction suivante :

describe clients

1.2 Types de données

Les principaux types de données ainsi que leurs limites sont décrits dans le tableau 1 :

types de donnees

Attention, car en informatique et plus particulièrement avec SQL, la virgule d’un nombre est un point.

Par ailleurs, les chiffres dont il est question avec le type de donnéesNUMBER(p,s)sont des chiffres en base 10 (et non en base 2). De plus, le choix des entierspetsdoit être soigneusement réfléchi.

En effet, avec le typeNUMBER(8,2), la valeur 123456.789 est stockée sous la forme123456.79(arrondi à deux décimales après la virgule), tandis qu’avec le typeNUMBER(8,-2)elle est stockée sous la forme 123400(arrondi à deux décimales avant la virgule) auquel cas, seuls quatre chiffres sur les huit possibles sont utilisés pour cette valeur. Attention, car avec les typesNUMBER(5),NUMBER(6,1)etNUMBER(4,-1)la même valeur 123456.789 déclenche un dépassement de capacité (car la partie qui reste après le décalage desschiffres comporte trop de chiffres significatifs).

Tous ces types de données acceptent la valeurNULL(qui signifie absence de valeur et non pas 0). Toute opération numérique (addition, multiplication, comparaison) avec unNULLrésulte systématiquement en unNULL(y comprisNULL = NULL). Heureusement, dans les requêtes SQL de la section 2, les valeursNULL d’une colonnexpeuvent être  temporairement remplacées par une constantevaleurgrâce à la fonction NVL(x, valeur).

1.3 Contraintes

Le fait d’être clé primaire, clé étrangère, non vide ou unique sont des contraintes possibles sur les colonnes d’une table. Comme nous l’avons vu au cours de la section précédente, les contraintes qui portent sur une seule colonne peuvent être déclarée en même temps que la colonne elle-même (que ce soit à la création ou lors d’une modification de la table).

1.3.1 Clé primaire composite

Lorsqu’une contrainte porte sur plusieurs colonnes, l’utilisateur est obligé de la déclarer à part et de lui donner un nom. Par exemple, la table de jonctionlignes de commandes, qui avait été volontairement omise sur la figure 1 (page 4), possède une clé primaire composée de deux colonnes (figure 2) :

table de jonction avec cle primaire composite

La syntaxe pour déclarer cette contrainte fait apparaître le mot-cléCONSTRAINTsuivi du nom de la contrainte puis de sa description :

syntaxe pour declarer contrainte

Le fait de préfixer le nom de la contrainte parpkoufkn’est pas une obligation, mais permet de différencier plus facilement les clés primaires des clés étrangères lorsque l’utilisateur liste les contraintes de ses tables à l’aide de l’instruction suivante :

select all from user constraints

1.3.2 De la nécessité de donner un nom aux contraintes

Lorsque l’utilisateur ne précise pas le nom d’une contrainte, le serveur Oracle en choisit un automatiquement, mais il n’est pas très explicite. Or, connaître le nom d’une contrainte est indispensable lorsque l’utilisateur souhaite la modifier ou la supprimer :

nom de contraint

C’est pourquoi il est recommandé de toujours donner un nom aux contraintes. D’ailleurs c’est obligatoire de préciser ce nom lorsqu’il s’agit d’ajouter une contrainte oubliée ou supprimée par erreur :

contrainte unicite

1.3.3 Clé étrangère composite

Les clés primaires ne sont pas les seules à pouvoir être composées de plusieurs colonnes. Les clés étrangères le peuvent également (figure 3) :

cle etrangere composite

Comme pour les clés primaires composite, la déclaration d’une clé étrangère composite se fait en fin d’instruction CREATE TABLE:

create table refus

Il faut bien comprendre sur cet exemple que c’est le couple (numéro de commande, numéro d’article) dans la tablereparationsqui doit correspondre à un couple (numéro de commande, numéro d’article) déjà présent dans la tablelignescommandeset non pas (numéro de commande) et (numéro d’article) séparément.

1.3.4 Références croisées et réflexives

Le fait de pouvoir ajouter une contrainte après la création de la table correspondante est indispensable lorsque les références entre deux tables sont croisées ou réflexives comme sur la figure 4 :

references croisees reference reflesive

Il faut alors procéder en plusieurs étapes :

etapes references croisees et reflexives

1.3.5 Intégrité de domaine

Aux contraintes PRIMARY KEY, FOREIGN KEY, UNIQUE et NOT NULL s’ajoutent les contraintes CHECK qui permettent de s’assurer que les données saisies dans une colonne logent dans une plage de valeurs définie à l’avance :

integrite de domaine

Tout ce que l’on peut mettre dans une clause WHERE (section 2.1.2) on peut le mettre dans les parenthèses d’une contrainte CHECK, excepté une sous-requête. Cela inclut les opérateurs AND, OR, NOT, BETWEEN, LIKE ainsi que tous les opérateurs de comparaison :

operateurs de comparaison utilisables

Attention tout de même, car l’ajout ou la modification d’une contrainte peut être refusée par le serveur Oracle si une donnée déjà présente dans la table ne vérifie pas la définition de la contrainte.

Il est possible d’écrire une contrainte qui implique plusieurs colonnes d’une même table. Considérons la table emprunts de la figure 5 page 20 :

alter table add constraint

En revanche, il n’est pas possible d’écrire avec une contrainteCHECK, une règle d’intégrité qui implique plusieurs colonnes issues de plusieurs tables.

contrainte check

Pour implémenter ce type de règle, il faut faire appel à des déclencheurs (notion qui n’est malheureusement pas abordée ici).

2 Sélection et modification des données

À ce stade, la base de données créée précédemment ne contient aucune donnée. Mais avant d’aborder les requêtes d’insertion, nous supposons qu’elle est remplie afin de pouvoir interroger ses données .

2.1 SELECT

Pour afficher les données sous la forme d’un tableau dont les colonnes portent un intitulé, SQL propose l’instruction SELECT … FROM … WHERE …dont voici des exemples d’utilisation :

selection et modification des donnees

2.1.1 Colonnes calculées

Dans la clause SELECT, on peut utiliser les opérateurs arithmétiques (+ – * /), l’opérateur de concaténation des chaînes de caractères (||) ou des fonctions SQL (comme MOD pour le modulo,FLOOR pour la troncature entière ou SUBSTR pour l’extraction de sous-chaînes de caractères) :

substr pour extraction de sous chaines de caracteres

Dès que l’on a un doute sur l’ordre de priorité des différents opérateurs, il ne faut pas hésiter à employer des parenthèses afin de lever toute ambiguïté.

À partir du moment où une colonne affichée est calculée, il est conseillé de lui donner un nom (un alias) avec le mot-cléAS, afin d’en expliciter le contenu, car rien n’est plus ambigu que de ne pas connaître la nature du contenu d’une colonne du résultat. Un alias, comme”prixTTC”est donné entre guillemets, contrairement aux chaînes de caractères qui sont données entre quotes’. ’.

Dans cette même clause SELECT, on peut aussi utiliser les fonctions d’agrégation SQL (tableau 2) :

principales fonctions agregation sql

Par bonheur, ces fonctions ignorent les valeursNULLdans leur calcul et n’éliminent pas les doublons.

Exemples d’utilisation :

fonctions agregation sql

2.1.2 Clause WHERE

La clauseWHEREd’une requête peut être très riche en conditions de sélection. D’abord, tous les opérateurs de comparaison (page 11) peuvent être utilisés :

la clause where

Ensuite, comme les syntaxes= NULLou<> NULL renvoient toujours  faux, on ne peut tester la vacuité d’une colonne qu’avec la syntaxe suivante :

clause where null

Dans cet exemple, le mot-clé logique NOT est utilisé. Nous avons également à disposition les mots-clés AND et OR logiques :

and logique

Par contre, pour un segment de valeurs bornes incluses, il n’est pas nécessaire d’utiliser la syntaxe … >= … AND … <= …, car nous pouvons utiliser l’opérateur BETWEEN:

between

Ensuite, une condition de sélection peut faire intervenir une liste de valeurs, auquel cas c’est l’opérateur IN qui est approprié :

where not in

Enfin, il est possible de balayer une colonne de type chaîne de caractères à la recherche d’un motif, grâce à l’opérateur de filtre LIKE et du caractère%qui remplace toute série de caractère(s) (y compris vide) :

where not like

Il existe également le caractère qui remplace un caractère (pour remplacer deux caractères, il suffit de l’utiliser deux fois dans le filtre).

2.1.3 Opérations sur le résultat

Le résultat d’une requête SELECT peut comporter plusieurs fois la même ligne. Pour éliminer les lignes doublons, il existe le mot-clé DISTINCT:

requete avec distinct

Ensuite, pour trier les lignes du résultat (ce qui n’est pas le cas, par défaut), nous disposons de la clause ORDER BY:

order by

Enfin, pour ne garder que les 100 premières lignes du résultat, Oracle a prévu de numéroter les lignes du résultat dans une colonne cachée nommée ROWNUM:

where rownum

2.1.4 Opérations ensemblistes

On peut articuler les résultats de plusieurs requêtes homogènes à l’aide des opérations ensemblistes UNION, INTERSECT et MINUS:

operations ensemblistes

Les règles à respecter avec ces opérations ensemblistes sont les suivantes :

– les colonnes affichées par les deux requêtes doivent être compatibles, en nombre, en ordre et en type de données ;

– les éventuels alias ne sont définis que dans la première clause SELECT;

– une éventuelle clause ORDER BY n’est possible qu’à la fin de la dernière requête, car elle agit sur le résultat final.

Dernières remarques :

– par défaut, l’opérateur UNION élimine les doublons ; pour les conserver, il faut utiliser l’opérateur UNION ALL;

– contrairement à UNION et INTERSECT, l’opérateur MINUS n’est pas commutatif, donc l’ordre dans lequel les requêtes sont écrites, de part et d’autre, a de l’importance.

2.2 INSERT

Pour créer une ou plusieurs ligne(s) dans une seule table, SQL offre l’instruction INSERT INTO. Lorsque l’on connaît directement les valeurs à insérer, il faut utiliser une instruction INSERT INTO … VALUES par ligne :

insert into

Plusieurs précautions doivent être prises lors d’une instruction INSERT INTO:

– les valeurs qui sont données viaVALUESdoivent être dans le même ordre que les colonnes qui sont précisées dans le INTO;

– et avec un type de données compatible à celui qui a été déclaré dans la table ;

– toutes les colonnes qui ne sont pas précisées, reçoivent alors la valeur défaut qui leur a été attribuée (bien souvent, il s’agit de la valeur NULL) ;

– si la moindre valeur insérée ne vérifie pas les contraintes de la table (clé primaire, clé étrangère, uni-cité, NOT NULL ou CHECK), alors l’instruction INSERT INTO est refusée en entier par le serveur Oracle.

Dans notre exemple, la colonnenaissancereçoit la valeur NULL, ce qui n’est pas gênant. Par contre, la colonne clé primairenumeroreçoit également la valeur NULL, ce qui est interdit. L’instruction INSERT INTO précédente est donc refusée.

Pour que la colonnenumeroreçoive un entier incrémenté automatiquement, il suffit de créer, avant la première insertion, une séquence pour cette table et utiliser cette séquence (avec NEXTVAL) dans l’instructionINSERT INTO:

insertion plusieurs lignes

On peut également insérer dans une table, le résultat d’une requête SELECT, auquel cas, plusieurs lignes peuvent être insérées à la fois :

inserer resultat de requete select

Les colonnes qui figurent dans la clause SELECT doivent être compatibles, en type de données, en nombre et en ordre, avec celles qui sont précisées dans la clause INTO. De plus, si une valeur dans une ligne ne vérifie pas les contraintes qui la concerne, alors l’insertion de toutes les lignes est refusée (il n’y a pas d’insertion partielle).

2.3 DELETE

Pour supprimer une ou plusieurs ligne(s) dans une seule table, la langage SQL a prévu l’instruction DELETE FROM … WHERE …:

delete

À nouveau, il ne peut y avoir de réussite partielle de l’instruction DELETE : si une des lignes supprimées est référencée par une clé étrangère, alors l’instruction DELETE est refusée en entier par le serveur Oracle.

Cela permet d’éviter qu’une clé étrangère ne deviennent orpheline.

L’attention du lecteur est également attirée sur le fait que si la clause WHERE est oubliée, alors toute la table est vidée :

delete from

Heureusement, cette instruction sera vraisemblablement refusée car il doit exister une ligne de commande qui référence une commande.

2.4 UPDATE

Pour modifier la valeur d’une ou plusieurs colonne(s), d’une ou plusieurs ligne(s), mais dans une table, c’est l’instruction UPDATE … SET … WHERE …qu’il faut utiliser :

update

De nouveau, lorsque la clause WHERE est omise,toutes les lignes sont modifiees et lorsqu’une des nouvelles valeurs ne vérifie pas les contraintes, l’instruction UPDATE est refusée en entier par le serveur.

L’informaticien avisé aura noté que la requête suivante est plus performante que la première, car une division coûte toujours beaucoup plus cher que la multiplication par l’inverse :

requete equivalente mais plus rapide

Enfin, on peut mettre à jour plusieurs colonnes à la fois, mais il faut se méfier des corrélations entre les formules. Considérons par exemple la tableemployesde la figure 4 (page 10) et augmentons de 10 % les employés du service n4 :

mettre a jour plusieurs colonnes

3 Jointures

Les requêtes écrites jusqu’à présent ne font intervenir qu’une seule table à la fois. Or, dans la plupart des cas, une requête a besoin de données qui se trouvent réparties dans plusieurs tables.

3.1 Sélection multi-table

Pour afficher à la fois la date de commande et le nom du client qui a passé la commande, les deux tables commandes et clients doivent figurer dans la clause FROM et il faut rappeler la liaison entre ces deux tables , dans la clause WHERE:

jointure simple entre deux tables

Remarques :

– la condition qui figure dans la clause WHERE est une condition de jointure, à ne pas confondre avec les conditions de sélection rencontrée jusqu’à présent ;

– cette fois-ci, la condition de jointure est un rappel de la liaison clé étrangère – clé primaire, mais ce ne sera pas toujours le cas ;

– étant donné que commandes.client = clients.numero, on aurait pu afficher commandes.client au lieu declients.numero;

– l’ordre des tables dans la clause FROM n’a pas d’importance.

Pour lever toute ambiguïté sur le nom des colonnes (notamment la colonne numero), il faut préciser le nom de la table en préfixe. Heureusement, pour raccourcir les noms complets des colonnes, on peut définir des alias dans la clause FROM:

requete avec des alias de table

Considérons le schéma relationnel de la figure 5 :

schema relationnel sur lequel ne figure pas la condition de jointure

Nous pouvons imaginer un exemple de requête dans laquelle la condition de jointure n’est pas une liaison directe clé étrangère – clé primaire :

court circuit de la table des exemplaires

En outre, il est possible dans une requête, de faire la jointure entre trois tables (ou plus), auquel cas il faut fournir au minimum deux conditions de jointure. À ces conditions de jointure, peuvent s’ajouter une ou plusieurs condition(s) de sélection (que l’on mettra de préférence à la suite) :

afficher les commandes completes du client

Par ailleurs, une même jointure peut faire appel à plusieurs conditions pour être réalisée. C’est le cas lorsque la clé étrangère est composite :

jointure sur deux colonnes

Cette dernière requête devient plus lisible grâce à l’opérateur JOIN dans la clause FROM:

operateur join dans la clause from

Enfin, une table peut apparaître plusieurs fois dans la clause FROM, lorsque la requête nécessite de cette table qu’elle joue plusieurs rôles à la fois. C’est le cas dans la section suivante.

3.2 Auto-jointure

Un problème qu’il faut avoir rencontré au moins une fois pour être capable de le résoudre, est illustré par l’exercice suivant : afficher les numéros des clients qui ont commandé en même temps (sur deux colonnes). La solution consiste à faire intervenir dans cette requête, deux fois la table des commandes (ce qui n’est possible que grâce aux alias de table) avec une condition de jointure qui assure qu’à chaque ligne, les deux clients sont distincts :

select distinct from where and

Nous voyons sur cet exemple qu’une condition de jointure n’utilise pas forcément l’opérateur =. Dans ce type de requête, l’emploi de l’opérateur < ou > est préférable à l’emploi de l’opérateur <>, car ce dernier ferait apparaître chaque couple deux fois : une fois dans un sens et une fois dans l’autre.

Pour afficher, non pas les numéros de client, mais les noms des clients, il faut joindre à chaque table commandes sa table clients, soit quatre tables au total :

select distinct from where and and and

Par ailleurs, pour compter les couples de clients, le mot-clé DISTINCT (qui est indispensable) doit apparaître à l’intérieur de la fonction COUNT:

mot cle DISTINCT

3.3 Jointure externe

La tableannuaireclientde la figure 6 contient des informations complémentaires relatives aux clients :

jointure externe

Fig. 6 – Table d’informations complémentaires pour illustrer la jointure externe

Mais comme il existe des clients pour lesquels on ne connaît aucune information complémentaire, la requête suivante n’affiche pas tous les clients :

requete jointure externe

Il faut donc que la condition de jointure ne soit pas obligatoire afin de ne pas supprimer les clients dont on n’a pas d’information complémentaire. On parle alors de jointure externe. Il suffit pour cela d’ajouter le signe+entre parenthèse dans la condition de jointure du côté de la table facultative :

requete equivalente

Dans le résultat de cette requête, les colonnes telephone,emailetadressedes clients pour lesquels

ces informations sont inconnues affichent des valeurs NULL.

Le signe+ne fonctionne pas avec l’opérateur JOIN, il faut utiliser l’opérateur LEFT OUTER JOIN:

operateur join plus

Par ailleurs, si une jointure externe repose sur plusieurs conditions, alors il se peut que plusieurs signes+soient utiles :

jointure externe repose sur plusieurs conditions

Maintenant, si la table facultative de la jointure externe est elle-même jointe à une autre table, alors cette nouvelle jointure doit aussi être externe :

table facultative de la jointure externe

Enfin, il est parfois indispensable d’utiliser la fonction NVL pour effectuer des calculs avec des colonnes issues d’une jointure externe. Considérons par exemple, le schéma relationnel de la figure 7 :

schema relationnel qui possede en cycle

Pour mettre à plat le cycle de ce schéma relationnel, il est nécessaire de dédoubler une des tables du cycle :

cycle de ce schema relationnel

3.4 Vue

On peut donner un nom à une requête SELECT, puis l’utiliser comme une table dans une autre requête :

create view

Remarque : le couple d’instructions CREATE OR REPLACE VIEW est plus pratique que la seule instruction CREATE VIEW, car pendant ca phase de definition la programmation d’une vue est amenee a etre corrigée plusieurs fois. Sans REPLACE VIEW, il faudrait supprimer puis re-créer la vue à chaque correction.

Les nouveaux intitulés de colonnes donnés lors de la définition de la vue pourront être ré-utilisés comme n’importe quel nom de colonne, on veillera donc à respecter les mêmes règles dans leur dénomination (pas d’espace et pas d’accent, notamment).

Attention, une vue ne contient pas de données, seules les tables sous-jacentes en contiennent. Mais les intérêts des vues sont nombreux, en voici les premiers :

– le travail répétitif de jointure et de sélection de la clause WHERE peut être fait une bonne fois pour toute dans la définition de la vue (simplification de programmation des requêtes qui utilisent les vues) ;

– si le schéma relationnel change, alors la programmation des vues risque de changer mais pas forcément les requêtes qui utilisent les vues (gain de travail de maintenance) ;

– si les utilisateurs n’ont accès qu’aux vues qui les concernent, alors ils ne voient que les données dont ils ont besoin (simplicité et sécurité) ;

– il est possible de donner aux vues et à leurs colonnes, des intitulés plus parlant que ceux des tables et de les traduire dans différentes langues ou langages métiers (clarté et adaptation aux utilisateurs :

Business Objects n’a rien inventé ;-).

Un autre avantage des vues : elles permettent d’effectuer des requêtes INSERT,DELETE et UPDATE sur plusieurs tables à la fois, à condition d’utiliser des déclencheurs (notion qui n’est malheureusement pas abordée ici). En outre, elles offrent une alternative systématiquement plus efficace à certaines sous-requêtes (section suivante).

4 Sous-requêtes

Il est possible d’utiliser dans une requête principale, une autre requête SELECT à condition que cette dernière soit délimitée par des parenthèses.

4.1 Sous-requête qui renvoient une seule valeur

Lorsque la sous-requête renvoie une unique valeur (encore fait-il en être sûr), la requête principale peut utiliser cette valeur avec les opérateurs de comparaison classiques :

sous requete

Ce type de sous-requête s’emploie également avec les opérateurs arithmétiques classiques :

sous requete avec operateurs arithmetiques classiques

Lorsque la sous-requête ne renvoie qu’une valeur, il n’existe bien souvent pas d’équivalent plus performant. Ce n’est pas toujours le cas dans ce qui suit.

4.2 Sous-requête qui renvoient une colonne

Lorsque la sous-requête renvoie une colonne de valeurs (c’est le cas le plus fréquent), la requête principale peut utiliser un opérateur de comparaison classique, mais accompagné soit de ALL soit de ANY:

sous requete qui renvoient une colonne

Malheureusement dans la majorité des cas, l’emploi du mot-clé ALL dans la requête principale, quoique plus clair, est moins efficace que l’emploi des fonctions d’agrégation MIN ou MAX dans la sous-requête. Il en va de même pour le mot-clé ANY:

mot cle all

Par ailleurs, il reste l’opérateur IN, avec lequel les sous-requêtes qui renvoient une colonne de valeurs semblent tout à fait indiquées :

operateur in

Toutefois, on peut substituer à cette sous-requête une jointure qu’Oracle pourra optimiser :

sous requete une jointure oracle

Remarquons finalement que l’opérateur IN est équivalent à l’opérateur= ANY, tandis que l’opérateur NOT IN est équivalent à l’opérateur<> ALL, qui sont tous aussi peu performants.

4.3 Sous-requête qui renvoient plusieurs colonnes

Une sous-requête peut renvoyer plusieurs colonnes de valeurs, auquel cas elle peut être utilisée (éventuellement affublée d’un alias de table) dans la clause FROM de la requête principale :

sous-requete qui renvoient plusieurs colonnes

Nous voyons bien sur cet exemple que formellement,une sous-requete est une vue qui n’est pas enregistrée sous un nom particulier et qui n’est pas, de ce fait, ré-utilisable dans une autre requête.

Notons par ailleurs qu’une sous-requête qui renvoie plusieurs colonnes est également utilisable avec un opérateur de comparaison entre couples, entre triplets ou entre tous les autres tuples (en anticipant légèrement sur les GROUP BY de la section 5) :

operateur de comparaison entre couples

4.4 Corrélation

Une sous-requête peut avoir besoin d’information provenant de la requête principale. On dit alors que la sous-requête est corrélée et le passage d’information se fait par utilisation d’alias de table :

correlation

Attention, car avec la corrélation dont elle fait l’objet, la sous-requête SELECT AVG (b.prix) FROM

articles b WHERE b.couleur = a.couleursera exécutée autant de fois qu’il y a d’articles (et non pas autant de fois qu’il y a de couleurs). Il s’agit-là d’une perte de temps considérable.

Heureusement, il existe toujours une solution sans sous-requête, qui consiste à utiliser dans la requête principale une jointure supplémentaire avec une vue préliminaire contenant le corps de la sous-requête :

jointure supplementaire avec une vue preliminaire contenant le corps de la sous requete

Dans ce cas, le calcul des prix moyens par couleur est fait une bonne fois pour toutes, la requête est donc plus rapide (elle était quadratique en nombre d’articles, elle devient linéaire).

Inversement, à la question   est-ce que toutes les jointures peuvent être remplacées par des sous-requêtes inutiles, la réponse est oui, grâce au mot clé EXISTS qui ne renvoie faux que lorsque la sous requête possède un résultat vide :

mot cle exists

4.5 Autres utilisations

L’emploi d’une sous-requête n’est pas limité aux requêtes SELECT et aux requêtes INSERT … SELECT.

Une requête UPDATE peut y faire appel :

une requete update

La clause WHERE d’une requête DELETE peut également nécessiter une sous requête :

clause where une requete delete

Enfin, l’opérateur de division ensembliste n’existe pas en SQL. La solution, pour mener malgré tout cette opération (rarement utile, il faut le reconnaître), consiste à employer une double inexistence :

double inexistence

Nous voyons sur cet exemple qu’il est possible d’imbriquer les sous-requêtes, à partir du moment où les sous-sous-requêtes sont correctement délimitées par leurs parenthèses.

5 Groupements

Comme nous avons pu le constater dans la section 4.3, il est parfois nécessaire de calculer la même fonction d’agrégation sur plusieurs groupes de lignes.

5.1 Clause GROUP BY

Pour afficher le montant total de chaque commande, il est nécessaire de grouper les lignes de commandes qui portent le même numéro de commande, puis de faire la somme des quantités prix unitaire :

clause group by

Pour reconnaître une requête SELECT qui nécessite une clause GROUP BY, il suffit de se demander si le résultat doit afficher plusieurs valeurs d’une même fonction d’agrégation :

requête SELECT qui nécessite une clause GROUP BY

Attention, il existe deux contraintes impératives sur les colonnes de la clause SELECT:

– toutes les colonnes de la clause GROUP BY doivent figurer dans la clause SELECT;

– toutes les colonnes de la clause SELECT sans fonction d’agrégation, doivent figurer dans la clause GROUP BY.

À titre de contre-exemple, la requête suivante sera refusée :

contraintes imperatives sur les colonnes de la clause select

La solution générique pour afficher des informations complémentaires aux groupes issus d’un GROUP BY, consiste à enregistrer la requête GROUP BY dans une vue préalable :

enregistrer la requete group by dans une vue prealable

enregistrer la requete group by dans une vue prealable 2

D’ailleurs, il est parfois indispensable de procéder par plusieurs vues préliminaires avant de pouvoir calculer C’est le cas lorsque l’on veut calculer les profits des formations de la figure 7 (page 24), il faut d’abord calculer séparément les coûts et les revenus :

plusieurs vues preliminaires

5.2 Sur plusieurs colonnes

La clause GROUP BY permet d’effectuer des groupements sur plusieurs colonnes (puis des calculs d’agrégation sur ces sous-groupes) :

calculs agregation sur ces sous groupes

L’ordre des colonnes dans la clause GROUP BY n’a pas d’importance et l’ordre des mêmes colonnes dans la clause SELECT n’a pas besoin d’être le même. Par contre, pour contrôler le classement dans l’affichage des sous-groupes, il est conseillé d’utiliser la clause ORDER BY pour laquelle l’ordre des colonnes a de l’importance :

utiliser la clause order by order by pour laquelle ordre des colonnes

Attention, la clause ORDER BY s’applique après groupement et ne peut, en conséquence, s’appuyer que sur les colonnes de la clause SELECT.

5.3 Clause HAVING

Il est parfois utile d’exclure certains groupes issus d’une requête GROUP BY à l’aide d’une ou plusieurs condition(s) de sélection. Ces conditions ne doivent pas être écrites dans la clause WHERE qui est réservée à l’exclusion des lignes avant groupement, mais dans une dernière clause, la clause HAVING:

clause having

Malheureusement, l’alias “nombre de commandes” qui est défini dans la clause SELECT, ne peut pas être utilisé dans la clause HAVING. En conséquence de quoi, il faut répéter exactement la formule COUNT(numero).

D’un point de vue syntaxique, tous les opérateurs qui sont autorisés dans une clause WHERE peuvent être utilisés dans la clause HAVING, y compris une sous-requête. En revanche, d’un point de vue sémantique, les conditions de sélection avant groupement (et les conditions de jointure) doivent rester dans la clause WHERE:

clause having avec where

Il existe d’ailleurs deux contraintes sur les colonnes utilisables dans ces deux clauses :

– la clause WHERE peut porter sur les colonnes de la clause SELECT sans fonction d’agrégation ainsi que sur les colonnes non affichées ;

– tandis que la clause HAVING ne peut porter que sur les colonnes de la clause SELECT (agrégées ou non).

Conclusion

La syntaxe complète d’une requête de sélection est la suivante :

syntaxe complete une requete de selection

Méthodologie de base pour élaborer ce type de requête à partir d’un problème bien défini :

1. décomposer la requête de sélection en plusieurs requêtes articulées avec UNION,INTERSECT ou MINUS;

2. décomposer chaque sélection complexe en requêtes et sous-requête (ou en vues intermédiaires) ;

3. pour chaque requête obtenue, remplir dans cet ordre (qui n’est pas l’ordre imposé par la syntaxe) :

(a) la clause FROM avec les tables impliquées ;

(b) la clause WHERE avec les conditions de jointure entre ces tables ;

(c) la clause WHERE avec les conditions de sélection avant groupement ;

(d) la clause GROUP BY avec les colonnes de groupement ;

(e) la clause HAVING avec les conditions de sélection après groupement ;

(f) la clause SELECT avec les colonnes à afficher, notamment celles des clauses GROUP BY et HAVING, sans oublier un éventuel DISTINCT;

(g) la clause ORDER BY avec les colonnes de la clause SELECT à trier et le sens de leur classement.

Exercices corrigés sqlplus

Telecharger serie Exercices corrigés sqlplus pour Mettre en œuvre pratiquement vos connaissances en langage SQL PLUS d’Oracle.