cours sgbd 2014

R É A L I S É PA R: I N E S S L I M E N E

SGBD

2013-2014

PLAN

• LDD :• Tables et contraintes• Vues• Synonymes• Index• Séquence

• LMD :• Insertion • Suppression• Modification

• LID

Système de Gestion de Bases de Données

2

L D D

CHAPITRE 1:

LANGAGE DE DEFINITION DES DONNEES

INTRODUCTION

• Un objet est une composante stockée dans la base de données. Un objet d’une BD peut être une table, une contrainte, une vue, une séquence, un index, une procédure, etc.

• Pour visualiser la liste des objets créés par un utilisateur:

SELECT * FROM user_catalog;


4


5

TABLES & CONTRAINTES

TABLE

• CREATE TABLE [schema.]table (colonne type [default

expr], ……)

Exemple : Create table Etudiants(Netudiant number,

nom varchar2(10), prenom varchar2(10));

• Pour décrire la table on utilise la commande : Describe etudiants ;


6

TYPES DE DONNEES


7

• Types de données • Description

•CHAR [(size [BYTE | CHAR])]

•Taille fixe comprise entre 1 et 2000

•VARCHAR2 (size) •Taille Variable comprise entre 1 et 4000

•NUMBER[(precision [, scale]])

•Nombre ayant une précision p et une échelle s. La précision est comprise entre 1 et 38. L’échelle varie de -84 à 127

•LONG •Données caractères ayant une taille <= 2GO

•DATE •Date comprise entre 1/1/4712 AJC et 31/12/999 APJC

•TIMESTAMP •Année, mois, jour , heure, minute et seconde, fraction de seconde

ALTER TABLE

ALTER TABLE [schema.]table ADD (colonne type [default expr], ……), MODIFY (colonne type [default expr], ……), DROP (colonne,….)Exemples

alter table etudiants modify (nom varchar2(15))alter table etudiants add (age number)alter table etudiants drop column age


8

TRUNCATE/DROP TABLE

1.TRUNCATE TABLE [schema.]table

ExempleTRUNCATE TABLE etudiants

2.DROP TABLE [schema.]table CASCADE CONSTRAINTS

ExempleDROP TABLE etudiants


9

LES CONTRAINTES

1.NOT NULL

2.DEFAULT

3.CHECK

4.UNIQUE

5.PRIMARY KEY

6.FOREIGN KEY


10

LES CONTRAINTES

CREATE table [schema.]table (colonne type [Default expr] [contrainte_colonne] ,….[Contrainte_table] ) Contrainte au niveau colonne : contrainte d’intégrité incluse dans la définition de la colonneContrainte au niveau table : contrainte d’intégrité incluse dans la définition de la table


11

CONTRAINTE NOT NULL

La contrainte NOT NULL ne peut être définie

qu’au niveau de la colonne, pas au niveau

de la table

Exemples :

CREATE TABLE Fournisseurs1 (fournisseur_id

number(10) PRIMARY KEY , nom varchar2(50) not

null, contact varchar2(50));Système de Gestion de Bases de

Données12

CONTRAINTE DEFAULT

• La contrainte DEFAULT permet de préciser une valeur qui sera automatiquement insérée en l'absence de précision d'une valeur explicite dans un ordre d'insertion.

• Exemple:ALTER TABLE Fournisseurs1 ADD (pays varchar2(20) DEFAULT ‘Tunisie’);


13

CONTRAINTE CHECK

• La contrainte Check définit une condition

que chaque ligne doit vérifier

ALTER table etudiants ADD Constraint ck_age

CHECK( age>18)


14

CONTRAINTE UNIQUE

• Une contrainte d’intégrité de type clé

unique exige que chaque valeur dans

une colonne ou dans un ensemble de

colonnes constituant une clé soit unique.

ALTER table etudiants ADD constraint uq_etud

UNIQUE(prenom)


15

PRIMARY KEY

• Une seule clé primaire peut être créée par

table.

• La clé primaire peut être constituée d’une ou

plusieurs colonnes.

• Aucune des colonnes faisant partie de la clé ne

peut être NULL

ALTER table etudiants ADD CONSTRAINT

pk_etud PRIMARY KEY(Netudiant)Système de Gestion de Bases de

Données16

FOREIGN KEY

• CREATE TABLE nom_table(col1 type null/not null,col2 type null/not null,...CONSTRAINT fk_table_colonne FOREIGN KEY (col1, col2, ... coln) REFERENCES table_parente (col1, col2, ... coln) ON DELETE {CASCADE|SET NULL|SET DEFAULT} ON UPDATE {CASCADE|SET NULL|SET DEFAULT})


17

FOREIGN KEY : DECLARATION CONTRAINTE NIVEAU TABLE

• CREATE TABLE Fournisseurs ( fournisseur_id numeric(10) not null, nom varchar2(50) not null, contact varchar2(50),CONSTRAINT pk_fournisseur PRIMARY KEY (fournisseur_id ));

• CREATE TABLE Produits (produit_id numeric(10) not null , fournisseur_id numeric(10) not null, CONSTRAINT fk_fournisseur FOREIGN KEY (fournisseur_id ) REFERENCES Fournisseurs (fournisseur_id ));


18

FOREIGN KEY : DECLARATION CONTRAINTE NIVEAU COLONNE

• CREATE TABLE Fournisseurs ( fournisseur_id numeric(10) not null PRIMARY KEY , nom varchar2(50) not null, contact varchar2(50));

• CREATE TABLE Produits (produit_id numeric(10) not null PRIMARY KEY , fournisseur_id numeric(10) not null REFERENCES Fournisseurs (fournisseur_id), nom varchar2(50) not null);


19

ACTIVER/DÉSACTIVER CONTRAINTE

• ActivationALTER TABLE nom_table ENABLE CONSTRAINT nom_constraintALTER table etudiants ENABLE constraint uq_etud;

• DésactivationALTER TABLE nom_table DISABLE CONSTRAINT nom_cOnstraintALTER table etudiants DISABLE constraint uq_etud; Système de Gestion de Bases de

Données20

DROP CONSTRAINT

• ALTER TABLE nom_table DROP CONSTRAINT fk_table_colonne

ALTER table etudiants DROP constraint uq_etud


21


22

VUES

INTRODUCTION

• Une vue est une vision partielle ou particulière des données d'une ou plusieurs tables de la base.

• Les utilisateurs pourront consulter la base, ou modifier la base (avec certaines restrictions) à travers la vue.

• Seule la définition de la vue est enregistrée dans la base, et pas les données de la vue.

On peut parler de table virtuelle.


23

UTILITÉ D’UNE VUE

• Permettre une certaine indépendance des données.• On sépare l'aspect externe (ce que voit un

utilisateur particulier de la base) de l'aspect conceptuel (comment a été conçu l'ensemble de la base).

• Indépendance entre les programmes et les données. Si la structure des données est modifiée, les programmes ne seront pas à modifier

• Pour des raisons de sécurité : restreindre l’accès aux données.

• Conserver des données de synthèses afin d’éviter les requêtes très complexes.


24

CRÉATION

• CREATE VIEW nom_vue (col1, col2...) AS SELECT ...

• Exemple :Vue EMP ne comportant que le matricule, le nom et le département des employés :CREATE VIEW EMP (EMPNO,ENAME,DEPTNO) AS SELECT employee_id,first_name,department_id FROM employees;• Il est possible de créer une vue de vue :CREATE VIEW EMP3 (EMPNO,ENAME) AS SELECT EMPNO,ENAME FROM EMP;


25

CRÉATION

• par défaut, les colonnes de la vue ont pour nom les noms des colonnes résultats du SELECT.

• Si certaines colonnes résultats du SELECT sont des expressions sans nom, il faut alors obligatoirement spécifier les noms de colonnes de la vue.

• Le SELECT peut contenir toutes les clauses d'un SELECT, sauf la clause ORDER BY.

• Exemple :Vue constituant une restriction de la table EMPLOYEES aux employés du département 10 :CREATE VIEW EMP10 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 10;


26

OPERATIONS SUR LES VUES

1.MODIFICATION D’UNE VUE

CREATE OR REPLACE nom_vue

2.SUPPRESSION D’UNE VUE

DROP VIEW nom_vue


27

MISE À JOUR AVEC UNE VUE

• Les conditions suivantes doivent être remplies :• pour effectuer un DELETE, le select qui définit la

vue ne doit pas comporter de jointure, de group by, de distinct, de fonction de groupe ;

• pour un UPDATE, en plus des conditions précédentes, les colonnes modifiées doivent être des colonnes réelles de la table sous-jacente ;

• pour un INSERT, en plus des conditions précédentes, toute colonne « not null » de la table sous-jacente doit être présente dans la vue.


28

MISE À JOUR AVEC UNE VUE

• Exemple :• Augmenter les salaires des employées du

département 10 de 10% à travers la vue EMP10.UPDATE EMP10 SET SALARY = SALARY * 1.1;• Toutes les lignes de la table EMPLOYEES avec

DEPARTMENT_ID = 10 seront modifiées. insert into emp10 (employee_id, last_name, email, hire_date, job_id, department_id) values(1 ,'test','eeer@re','12/06/1998','AD_VP',20)


29

WITH CHECK OPTION

• Si l'on veut éviter qu’une vue puisse créer des données qu'elle ne pourra pas visualiser. il faut ajouter « WITH CHECK OPTION »

• Il est alors interdit de créer au moyen de la vue des lignes qu'elle ne pourrait relire.

Exemple :• CREATE VIEW EMP11 AS SELECT * FROM

EMPLOYEES WHERE DEPARTMENT_ID = 10 WITH CHECK OPTION

Ainsi on ne peut pas ajouter un employé du département 20 avec la vue EMP10.


30


31

SYNONYMES

SYNONYMES

• C'est un Alias sur un Objet de la base ou Schéma, une sorte de raccourcis.

• L'Objet peut être une Table, une Vue, une Séquence, une Procédure, une Fonction, un Package.

Pourquoi créer des synonymes?- Masquer le vrai nom des objets et leur localisations.- Simplifier les noms des objets.- Éviter le pré-fixage dans les requêtes avec le nom de son propriétaire.


32

SYNONYMES

• Référencie une table appartenant à un autre

utilisateur

• CREATE [PUBLIC] SYNONYM synonym FOR object

CREATE PUBLIC

SYNONYM agence FOR sales.industrie;

• DROP SYNONYM synonym


33


34

INDEX

DÉFINITION

SELECT * FROM EMPLOYEES WHERE first_name = 'David‘;• Un moyen de retrouver la ou les lignes pour

lesquelles first_name est égal à ‘David' est de balayer toute la table.

temps de réponse prohibitifs pour des tables dépassant quelques milliers de lignes. • Solution : création d'index, qui permettra de

satisfaire aux requêtes les plus fréquentes avec des temps de réponses acceptables.


35

CRÉATION

• Automatiquement

Un index unique est crée automatiquement lors de

la définition d’une contrainte Clé Primaire ou unique

• Manuellement

Un index peut être crée manuellement par la

commande :CREATE INDEX nom-index ON table (col1, col2,...)


36

CHOIX DES INDEX

• Indexer en priorité : • les colonnes servant de critère de jointure • les colonnes servant souvent de critère de recherche• La colonne contient un grand nombre de valeurs NULL• La table est de grande taille et la plupart des requêtes

doivent extraire moins de 2 à 4% des lignes

• Ne pas indexer : • Les colonnes contenant peu de valeurs distinctes

(RESULTAT = 1 ou 0 )• les colonnes fréquemment modifiées • La table est de petite taille• La plupart des requêtes sont prévues pour extraire un trop

grand pourcentage de lignesSystème de Gestion de Bases de

Données37

VÉRIFICATION DES INDEX

• la table USER_INDEXES donne des informations sur les index qu‘un utilisateur a créés.

• La table ALL_INDEXES donne les index qu‘on peut utiliser (même s'il ne les a pas créées).

Exemple :• Donner la liste des indexes dans la baseSELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES;


38

SUPPRESSION D’UN INDEX

• DROP INDEX nom_index [ON table]

• Remarque : • Un index est automatiquement supprimé dès

qu'on supprime la table à laquelle il appartient.


39


40

SÉQUENCES

DÉFINITION

1.Génère automatiquement des numéros uniques

2.Est Partageable entre plusieurs utilisateurs et

éventuellement entre plusieurs tables

3.Permet de créer une valeur de clé primaire


41

CRÉATION

CREATE SEQUENCE [INCREMENT BY n]définit l’intervalle entre les numéros[START WITH n] premier numéro de la séquence [{MAXVALUE n | NOMAXVALUE}] valeur maximale [{MINVALUE n | NOMINVALUE}] valeur minimale[{CYCLE | NOCYCLE}] la séquence peut continuer à générer ou non des valeurs[{CACHE n | NOCACHE}] nombre de valeurs pré-allouées et conservées en mémoire.


42

CRÉATION

• Par défaut, la séquence commence à 1 et augmente de 1 à chaque fois.

• Exemple :Créer une séquence qui commence par dix avec un pas d’incrémentation dix

CREATE SEQUENCE SEQDEPT INCREMENT BY 10 START WITH 10;


43

UTILISATION

• Deux pseudo-colonnes permettent d'utiliser les séquences :• CURRVAL retourne la valeur courante de la séquence ;• NEXTVAL incrémente la séquence et retourne la nouvelle

valeur.

Exemple :• INSERT INTO departments (department_id,

demartment_name, location_id) VALUES (SEQDEPT.NEXTVAL, 'FINANCES', 'NICE') ;

• SELECT SEQDEPT.CURRVAL FROM DUAL;Remarque :currval n'est pas défini tant qu'on n'a pas appelé au moins une fois nextval dans la session de travail.


44

MODIFICATION

ALTER SEQUENCE sequence [INCREMENT BY n][{MAXVALUE n | NOMAXVALUE}][{MINVALUE n | NOMINVALUE}][{CYCLE | NOCYCLE}][{CACHE n | NOCACHE}]• Modifier le pas d’incrémentation de la séquence

seqdept à 5ALTER SEQUENCE SEQDEPT INCREMENT BY 5;


45

SUPPRESSION

• DROP SEQUENCE nom_séquence

• Exemple :Supprimer la séquence seqdeptDROP SEQUENCE SEQDEPT ;


46

L M D

CHAPITRE 2:

LANGAGE DE MANIPULATION DES DONNEES

OBJECTIFS

• Décrire chaque instruction du langage de manipulation des données (LMD).

• Insérer des lignes dans une table.• Mettre à jour des lignes dans une table.• Supprimer des lignes d’une table.


48

INTRODUCTION

• LMD : Ensemble des commandes concernant la manipulation des données dans une base de données.

• Le LMD permet • l’ajout, • la suppression et • la modification de lignes,


49

INSERT

• La commande INSERT permet d’insérer une ligne dans une table en spécifiant les valeurs à insérer.

INSERT INTO nom_table (nom_col_1, nom_col_2, ...) VALUES (val_1, val_2, ...) ;• Il est possible d’insérer dans une table des lignes

provenant d’une autre table. La syntaxe est la suivante :

INSERT INTO nom_table(nom_col1, nom_col2, ...)< requête SELECT dont le résultat a le même schéma que la relation nom_table> ;


50

INSERT

• Exemple :Create table work(cin number, prenom varchar2(20), salaire number, email varchar2(10));

Insérer dans la table work les employés dont le job est programmeur (job_id = ‘IT_PROG’ )insert into work select employee_id,last_name,salary,email

from employees where job_id = 'IT_PROG' ;


51

UPDATE

• La commande UPDATE permet de modifier les valeurs d’une ou plusieurs colonnes, dans une ou plusieurs lignes existantes d’une table. La syntaxe est la suivante :

UPDATE tableSET column = value [, column = value, ...][WHERE condition];• Les valeurs des colonnes sont modifiées dans

toutes les lignes qui satisfont la condition WHERE. • La clause WHERE est facultative. En l’absence

d’une clause WHERE, toutes les lignes sont mises à jour.


52

UPDATE AVEC UNE SOUS-INTERROGATION

• Mettre à jour le poste et le salaire de l’employé 114 afin qu’ils correspondent à ceux de l’employé 205.

UPDATE employeesSET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114;


53

DELETE

• La commande DELETE permet de supprimer des lignes d’une table.

DELETE [FROM] nom_tableWHERE conditions ;• Toutes les lignes pour lesquelles la condition est

évalué à vrai sont supprimées.


54

SUPPRIMER DES LIGNES EN FONCTION D’UNE AUTRE TABLE

• Supprimer les employés qui appartiennent au département Marketing

DELETE FROM employeesWHERE department_id in (SELECT department_id FROM departments WHERE department_name LIKE 'Marketing');


55

CHAPITRE 3 :

LANGAGE D’INTERROGATION DES DONNEES LID

REQUÊTES SIMPLES


57

REQUÊTES SIMPLES

• SELECT [DISTINCT ] {*, colonne [as] [alias], …}

FROM table ;

SELECT : indique les colonnes à récupérer

DISTINCT : supprime les doublons

FROM : indique les tables recherchées


58

EXPRESSIONS ARITHMÉTIQUES

• Expression contenant des données de type NUMBER, DATE et des opérateurs arithmétiques


59

Opérateur Description

+ Addition

- Soustraction

* Multiplication

/ Division

ALIAS DE COLONNE

• Renomme un en-tête de colonne• Suit le nom de colonne• Doit obligatoirement être inclus entre guillemets

s’il contient des espaces, des caractères spéciaux ou si la casse est respectée.

SELECT first_name “prénom”, last_name as nom ,salary as salaire, commission_pct as commission, salary*(1+commission_pct) as “salaire majoré” From employees;


60

OPERATEUR DE CONCATÉNATION

• Concatène des colonnes ou des chaines de caractères

• Est représenté par le symbole ||• La colonne résultante est une expression

caractèreSELECT employee_id, last_name || ‘ ‘ || first_name as "nom et prénom " FROM employees;


61

SÉLECTION DES LIGNES


62

OPERATEUR DESCRIPTION

= Egal à

< Inférieur à

<= Inférieur à ou égal

> Supérieur à

>= Supérieur à ou égale à

<> Ou != Différent

BETWEEN val1 AND val2

val1 <= val <= val2

In (liste de valeurs) Valeur de la liste

Like Ressemblance partielle de chaînes de caractères

IS NULL Correspond à une valeur NULL

• Filtrer la sélection au moyen de la clause where

OPERATEURS DE COMPARAISON

• Afficher les noms el les salaires des employés dont le salaire est entre 2000 et 4000

SELECT last_name, salaryFROM employees WHERE salary BETWEEN 2000 AND 4000;• Afficher les employés dont le manager est 124 ou

205 ou 114SELECT employee_id, last_name, salary, manager_id FROM employeesWHERE manager_id in (124, 205, 114);


63

UTILISATION DE L’OPERATEUR LIKE

• LIKE permet de rechercher des chaînes de caractères à l’aide de caractères génériques

• Les conditions de recherche peuvent contenir des caractères ou des nombres littéraux

• % représente Zéro ou plusieurs caractères• _ représente un caractère


64

UTILISATION DE L’OPERATEUR LIKE

• Afficher les employés dont le nom commence par M et le troisième caractère est r.

SELECT * FROM employees WHERE last_name LIKE 'M_r%';

• Afficher les employés dont le job_id commence par A et le troisième caractère est _.

SELECT * FROM employees WHERE job_id LIKE 'A_\_%' ESCAPE '\';


65

UTILISATION DE L’OPERATEUR IS NULL

• Afficher les employés qui n’ont pas de manager.

SELECT employee_id ,last_name, manager_id FROM employees WHERE manager_id IS NULL;


66

OPERATEURS LOGIQUES

• Afficher les employés dont le salaire est 3500 et le manager est CLERK

SELECT * FROM employees WHERE salary >= 3500 AND job_id='SH_CLERK';

• Afficher les employés qui ne sont ni ‘Stock Clerk’ ni ‘Finance Manager’ ni ‘president’

SELECT * FROM employees WHERE job_id NOT IN ('ST_CLERK','FI_MGR','AD_PRES');


67

RÈGLES DE PRIORITÉ


68

ORDRE DE PRIORITE OPERATEUR

1 Conditions de comparaison

2 IS NULL, LIKE, IN

3 BETWEEN

4 NOT

5 AND

6 OR

RÈGLES DE PRIORITÉ

SELECT last_name,job_id,salary FROM employees WHERE job_id = 'SA_REP'OR job_id = 'AD_PRES'AND salary > 9500;

SELECT last_name,job_id,salary FROM employees WHERE (job_id = 'SA_REP'OR job_id = 'AD_PRES')AND salary > 9500;


69

TRI : CLAUSE ORDER BY

Tri des lignes avec la clause ORDER BY

SELECT last_name, job_id, department_id, hire_date FROM EMPLOYEES WHERE job_id <> 'FI_MGR'ORDER BY hire_date ASC;

SELECT last_name, job_id, department_id, hire_date FROM EMPLOYEES WHERE job_id <> 'FI_MGR'ORDER BY hire_date DESC;


70

TRI : CLAUSE ORDER BY

• TRI SUR L’ALIAS DE COLONNE SELECT employee_id, last_name, salary * 12 AnnuelFROM EMPLOYEES WHERE department_id= 10ORDER BY Annuel DESC;• TRI SUR PLUSIEURS COLONNES

SELECT employee_id, department_id, salary FROM EMPLOYEES ORDER BY department_id, salary DESC;


71

FONCTIONS MONO-LIGNES


72

FONCTIONS MONO-LIGNE

• Agissent sur une seule ligne et ramène un seul

résultat

• Types de fonctions mono-ligne:

• Fonctions caractères

• Fonctions numériques

• Fonctions dates

• Autres Fonctions


73

FONCTIONS CARACTÈRES

1.Fonctions de conversion majuscules/minuscules LOWER(arg) : convertit les caractères majuscules en

minuscules UPPER(arg) : convertit les caractères minuscules en

majuscules INITCAP(arg) : convertit l’initiale de chaque mot en

majuscule et les caractères suivants en minuscules

SELECT last_name, lower(last_name) , upper(last_name), initcap(last_name)FROM EMPLOYEES;


74


1.Fonctions de manipulation de caractères•CONCAT(arg1,arg2) : concatène la première chaîne avec la seconde = ||•SUBSTR(arg,m[,n]) : extrait une sous chaîne d’une autre chaîne

SUBSTR (‘ORACLE’, 2, 4) ‘RACL’• LENGTH(arg) : taille d’une chaîne en caractères• INSTR(arg,cc) : retourne la position de cc dans

l’argument en commençant par la position m et dont l’occurrence est n. Par défaut m et n sont mis à 1.

INSTR('download','load') 5


75


• LTRIM (arg,cc) : renvoie l’argument dont elle enlève chaque caractère de cc si ce caractère se trouve à son extrémité gauche.

LTRIM(‘OPERATION’,‘OP’) ‘ERATION’• RTRIM(arg,cc) : renvoie l’argument duquel elle

enlève chaque caractère de cc si ce caractère se trouve à son extrémité droite.

• TRIM(cc from arg) : le dimensionnement se fait des deux cotés

TRIM ('N' from 'NATION') ‘ATIO’


76


• LPAD(arg,m,cc) : insert l’argument à gauche plusieurs fois par les caractères de cc jusqu’à ce que la taille de la chaîne retournée soit de m. Par défaut, cc est égale à l’espace.

LPAD(‘SQL’,8,‘or’) ‘ororoSQL’• RPAD(arg,m,cc) : insert l’argument à droite

plusieurs fois par les caractères de cc jusqu’à ce que la taille de la chaîne retournée soit de m.

RPAD(‘SQL’,8) ‘SQL ’


77


REPLACE : remplace toutes les occurrences de la chaîne recherchée par une autre

TRANSLATE : remplace chaque caractère recherché par son correspondant dans la chaîne principale

SELECT 'ABCDE ADEU', replace ('ABCDE ADEU', 'AB','XYZ'), translate('ABCDE ADEU', 'AB','UVW'), translate('ABCDE ABDEU', 'AB','U') FROM dual;


78


ASCII(arg) : retourne le code ascii du premier caractère de la chaîne

CHR(n) : retourne le caractère (inverse de ascii)

SELECT ascii('A'),ascii('ABC'), chr(75)||chr(68)||chr(78)From dual;


79

FONCTIONS NUMÉRIQUES

• ROUND(arg,n) : arrondit l’argument à la précision n.

• ROUND(46.8) 47• ROUND(46.862,1) 46.9• ROUND(46.862,2) 46.86• ROUND(46.862,-1) 50• ROUND(51.342,-2) 100• Il est à noter que ROUND(5.5) 6


80


• TRUNC(arg,n) : tronque l’argument à la précision n.

• TRUNC(46.862,1) 46.8• TRUNC(46.862,2) 46.86• TRUNC(46.862,-1) 40• TRUNC(51.342,-2) 0


81


• CEIL(arg) : retourne le plus petit entier supérieur à l’argument.

• CEIL(99.9) 100• CEIL(-11.1) -11• FLOOR(arg) : retourne le plus grand entier

inférieur à l’argument.• FLOOR(99.9) 99• FLOOR(-11.1) -12• MOD(arg, n) : reste de la division de col par n.

• mod(5,2) 1


82

FORMAT NUMÉRIQUE

Elément résultat

9 Représente un nombre

0 Force l’affichage d’un zéro

$ Place un signe dollar flottant

L Utilise le symbole flottant de la devise locale

. Affiche un point comme séparateur décimal

, Affiche un virgule comme séparateur des milliers


83

FONCTIONS DATES

• On peut faire des calculs arithmétiques sur les DATE :• DATE1 + n: ajoute ou soustrait n jours de DATE1 (selon le

signe de n) et retourne la date résultat.

Remarque : l’ajout d’un nombre d’heures à une date se fait en divisant le nombre d’heures par 24

• DATE1 – DATE2 : retourne la différence entre les deux dates en nombre réel de jours. car l’opération de soustraction tient compte des heures des deux dates.

SELECT sysdate, sysdate+2, to_date('20/09/1998') - 10, Trunc(sysdate - to_date('10/10/2008'),0) FROM dual ;


84

FONCTIONS DATES

• MONTHS_BETWEEN (date1,date2) : retourne en nombre réel la différence en mois entre les deux dates.

• ADD_MONTHS(date1,n) : retourne la date qui vient n mois après date1.

• NEXT_DAY(date1,char/n) : Elle retourne le prochain jour char dans la semaine de date1.

char∈{‘Lundi’,’Mardi’,…,’Dimanche’}, n∈{1,2,…,7}.• LAST_DAY(date1) : retourne la date du dernier jour du

mois de date1.• Extract : extraction du jour, mois, année, heure, minute et

seconde


85

FONCTIONS DATES

select trunc(MONTHS_BETWEEN('09/01/95','01/11/94')) nbr_mois,ADD_MONTHS ('31/01/96',1), NEXT_DAY('09/01/95','JEUDI'), LAST_DAY('01/02/95')from dual ;


86

FONCTIONS DATES

SELECT systimestamp, extract(day from systimestamp) as Jour, extract(month from systimestamp) as Mois, extract(year from systimestamp) as Année, extract(hour from systimestamp)+1 as heure, extract(minute from systimestamp) as Minute, trunc(extract(second from systimestamp)) as SecondFROM dual;


87

FONCTIONS DATES

• ROUND(date1[,fmt]): retourne date1 arrondie selon le format fmt

TRUNC(date1[,fmt]): tronque une dateselect ROUND(SYSDATE,'DD'), ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YY'),TRUNC(SYSDATE ,'DD'), TRUNC(SYSDATE ,'MM'),TRUNC(SYSDATE ,'YY') from dual ;


88

FORMAT DATE

élément résultat

YYYY Année complète en chiffre

YEAR Année en toute lettres

MM Mois sur deux chiffres

MONTH Mois en toute lettres

MON Abréviation a 3 lettres du mois

DY Abréviation a 3 lettres du jour de la semaine

DAY Jours de la semaine en toute lettre

DD Jour du mois sous forme numérique


89

FONCTIONS DE CONVERSION

• TO_CHAR(Date1,‘fmt’) : convertit une date en chaîne de caractères et l’affiche dans le format ‘fmt’ indiqué.

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM DUAL;SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;

TO_CHAR(NOMBRE,‘frmt’) : convertit un nombre en chaîne de caractère dans le format spécifié. Select TO_CHAR (900.00,'$999.999') from dual ;


90

FONCTIONS DE CONVERSION

• TO_NUMBER(char,’fmt’) : convertit une chaîne de caractère en nombre.

select TO_NUMBER('$1500','$9999') from dual ;

• TO_DATE(char,’fmt’) : convertit char en date selon le format de date mentionnée.

select TO_DATE('23/10/2004','DD/MM/YYYY') from dual ;


91

AUTRES FONCTIONS

• NVL(arg,val) : retourne val si arg est NULL.• NVL2(arg,val1,val2):

retourne val2 si arg est NULL sinon retourne val1.

SELECT employee_id, last_name, salary, commission_pct, NVL(commission_pct,0) comm, NVL(to_char(commission_pct), 'Pas de commission') comm1, NVL2(commission_pct,commission_pct/2,0) comm2 FROM employees;


92

AUTRES FONCTIONS

• NULLIF (val1,val2) : si val1= val2 la valeur NULL est retournée sinon val1

• Decode (expr, val1, val11, val2, val21, …. Valn, valn1, default) :

retourne valn1 si expr = valn sinon default • Case : évalue une liste de conditions et retourne

un résultat parmi les cas possibles


93

AUTRES FONCTIONS (NULLIF)

SELECT NULLIF(last_name, last_name) last_name, NULLIF(100, 200), trunc(salary / NULLIF(commission_pct, 0),2) "sal/comm"FROM employees;


94

AUTRES FONCTIONS (CASE)

SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY"

FROM employees;


95

AUTRES FONCTIONS (DECODE)

SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG' , 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP' , 1.20*salary, salary) REVISED_SALARYFROM employees;


96

FONCTIONS DE CLASSEMENT

• Les fonctions analytiques sont très puissantes et très utiles, elles permettent d’alléger les requêtes SQL d’une façon élégante et performante.

• Fonctions :• Row_number : retourne le numéro séquentiel

d'une ligne d'une partition d'un ensemble de résultats, en commençant à 1 pour la première ligne de chaque partition

• Rank : retourne le rang de chaque ligne au sein de la partition d'un ensemble de résultats


97

FONCTIONS DE CLASSEMENT

• Dense_rank : retourne le rang des lignes à l'intérieur de la partition d'un ensemble de résultats, sans aucun vide dans le classement

• First_value : retourne la première valeur d’une partition

• Last_value : retourne la dernière valeur d’une partition


98

FONCTIONS DE CLASSEMENT (ROW_NUMBER)

• permet de donner un ordre numérique pour les lignes

Exemple : Afficher le classement des employés selon leurs salaire.SELECT department_id, last_name, first_name, salary, row_number() over(order by salary DESC) as Cls FROM employees;


99

FONCTIONS DE CLASSEMENT (ROW_NUMBER)

Afficher le classement des employés de chaque département selon leurs salaire.select department_id, last_name, first_name, salary, row_number() over(partition by department_id order by salary DESC) as Cls from employees;


100

FONCTIONS DE CLASSEMENT(RANK)

• select department_id, last_name, salary, rank() over (partition by department_id order by salary desc) as rank from employees;


101

FONCTIONS DE CLASSEMENT (DENSE_RANK)

• select department_id, last_name, salary, dense_ranK() over(partition by department_id order by SALARY DESC) as dense_rank from employees;


102

FONCTIONS DE CLASSEMENT (FIRST_VALUE)

• Retourne la première valeur dans un jeu de valeurs ordonné

Exemple : afficher le nom de l’employé le mieux payé SELECT distinct first_value(last_name) over(order by salary DESC) as first_value from employees;

select last_name from employees where salary = (select max(salary) from employees);


103

FONCTIONS DE CLASSEMENT (FIRST_VALUE)

• afficher le nom des employés les mieux payés par département

• select distinct department_id, first_value(last_name) over(partition by department_id order by salary DESC) as first_value from employees;


104

FONCTIONS DE CLASSEMENT (LAST_VALUE)

• Retourne la dernière valeur dans un jeu de valeurs ordonné

• Exemple : afficher la date d'embauche du dernier employé dans chaque service pour le salaire donné.

SELECT department_id, salary, hire_date, last_value(hire_date) over(partition by department_id order by salary DESC) as last_value from employees;


105

FONCTIONS DE GROUPE


106

FONCTIONS DE GROUPES

Les fonctions de groupe agissent sur des groupes de lignes et donnent un résultat par groupe AVG([distinct|all]expr) : valeur moyenne en

ignorant les valeurs NULL COUNT ([*|distinct|all]expr) : nombre de

lignes où expr est différente de NULL. Le caractère * comptabilise toutes les lignes sélectionnées.

MAX ([distinct|all]expr) : valeur maximale en en ignorant les valeurs NULL


107


MIN([distinct|all]expr) : valeur minimale en en ignorant les valeurs NULL

STDDEV([distinct|all]expr) : ecart-type en en ignorant les valeurs NULL

SUM([distinct|all]expr) : somme en en ignorant les valeurs NULL

VARIANCE([distinct|all]expr) : variance en ignorant les valeurs NULL


108


SELECT colonne, fonction de groupe FROM table[WHERE condition][GROUP BY colonne|expression][ORDER BY colonne|expression]Afficher pour chaque département le nombre d’employés et le salaire total. SELECT department_id, count(*) as "nb salariés", sum(salary) as "Total salaire" FROM employeesGROUP BY department_id ;


109

FONCTIONS DE GROUPES : HAVING

• Sert à restreindre les groupes retournés. • La clause HAVING est suivie d’une condition simple

ou composée qui concerne les colonnes qui suivent la clause GROUP BY, ou/et des fonctions de groupe.

• Exemple :Afficher les moyennes de salaires de chaque département s’il inclut plus de 3 employés ?select department_id, trunc(avg(salary),2) from employees group by department_id having count(employee_id) > 3 ;


110

JOINTURES


111

DÉFINITION D’UNE JOINTURE

Une jointure sert à extraire des données de plusieurs

tables

La condition de jointure peut être exprimée dans la

clause :

• WHERE T1.C1= T2.C1

• ON T1.C1= T2.C1

Précédez le nom de la colonne par le nom de la table

lorsque celui-ci figure dans plusieurs tables


112

EQUIJOINTURE = JOINTURE INTERNE

SELECT T1.COLONNE1, …T1.COLONNEn, T2.COLONNE1, …T2.COLONNEmFROM T1 INNER JOIN T2ON T1.C1=T2.C1WHERE CONDITION(S)

Ou bienSELECT T1.COLONNE1, …T1.COLONNEn, T2.COLONNE1, …T2.COLONNEmFROM T1 ,T2WHERE T1.C1=T2.C1 AND CONDITION(S)


113

EQUIJOINTURE = JOINTURE INTERNE

Afficher les noms des employés et les noms de leurs départements

SELECT last_name, e.department_id, department_nameFROM employees e inner join departments don e. department_id = d. department_idorder by department_id, last_name;


114

JOINTURE EXTERNE = FULL OUTER JOIN

• Une jointure externe élargie le résultat d’une jointure

simple (INNER JOINS) et permet en plus d’extraire des

enregistrements qui ne répondent pas aux critères de

jointure.

• Le sens de la jointure externe LEFT ou RIGHT de la

clause OUTER JOIN désigne la table dominante.

SELECT T1.COLONNE1, …T1.COLONNEn, T2.COLONNE1, …T2.COLONNEm

FROM T1 FULL OUTER JOIN T2ON T1.C1=T2.C1;


115

JOINTURE EXTERNE = FULL OUTER JOIN

• La jointure externe nous a permis d’avoir aussi : • Les départements qui ne contiennent aucun employés. • Le nombre d’employés qui n’appartiennent a aucun

département.


116

JOINTURE EXTERNE DROITE

SELECT T1.COLONNE1, …T1.COLONNEn, T2.COLONNE1, …T2.COLONNEmFROM T1 RIGHT OUTER JOIN T2ON T1.C1=T2.C1 ou bienSELECT T1.COLONNE1, …T1.COLONNEn, T2.COLONNE1, …T2.COLONNEmFROM T1, T2WHERE T1.C1(+)=T2.C1


117

• Afficher le nombre de salariés pour les départements Shipping et Treasury;

Nous avons maintenant le département Treasury qui apparaît.

JOINTURE EXTERNE DROITE

Système de Gestion de Bases de Données 118

JOINTURE EXTERNE GAUCHE

SELECT T1.COLONNE1, …T1.COLONNEn, T2.COLONNE1, …T2.COLONNEmFROM T1 LEFT OUTER JOIN T2ON T1.C1=T2.C1 ou bienSELECT T1.COLONNE1, …T1.COLONNEn, T2.COLONNE1, …T2.COLONNEmFROM T1, T2WHERE T1.C1=T2.C1(+)


119

AUTO-JOINTURE

• Liaison d’une table à elle-même

• Exemple :

Afficher pour chaque employé le numéro, le nom, le job

et le nom de son manager

SELECT a.employee_id, a.last_name, a.job_id, b.last_name as MGR

FROM employees a inner join employees b

On a. manager_id = b.employee_id

ORDER BY employee_id;


120

NON-EQUIJOINTURE

CREATE TABLE GRADE (GRADE NUMBER, min_salary NUMBER, max_salary NUMBER ) ; insert into grade values(1, 700,1200); insert into grade values(2,1201,1400); insert into grade values(3,1401,2000); insert into grade values(4,2001,3000); insert into grade values(5,3001,9999);

• Afficher les grades des employés de la table EMPLOYEES. Système de Gestion de Bases de

Données121

NON-EQUIJOINTURE

SELECT last_name, grade, salary, min_salary,max_salary

FROM employees a inner join grade b

ON salary BETWEEN min_salary and max_salary ;


122

SOUS-INTERROGATIONS


123

SOUS-INTERROGATIONS

SELECT select_listeFROM tableWHERE expr operateur (SELECT select_liste

FROM table)

• La sous-interrogation (requête interne) est exécutée une seule fois avant la requête principale

• Le résultat de la sous-interrogation est utilisé par la requête principale (requête externe)

• Les opérateurs de comparaison mono-ligne (>,>= ,<, <=, …)

• Les opérateurs de comparaison multi-ligne (IN, ALL, ANY)


124

SOUS-INTERROGATIONS MONO-LIGNES

• Afficher les employés qui ont le même job que Lorentz.

SELECT last_name, job_id, salaryFROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Lorentz' )and last_name != 'Lorentz'• Afficher le nom et le job de l’employé qui a le

salaire le plus faibleSELECT last_name, job_id, salary FROM employees WHERE salary =(SELECT MIN(salary) FROM employees)


125

SOUS-INTERROGATIONS MONO-LIGNES

• Afficher le département dont le salaire minimum est supérieur au salaire minimum du département 20.

SELECT department_id, min(salary) FROM employees GROUP BY department_idHAVING min(salary) >(SELECT MIN(salary)

FROM employees WHERE

department_id = 20)Système de Gestion de Bases de

Données126

SOUS-INTERROGATIONS MULTI-LIGNES

• Afficher les employés les moins payés pour chaque département ?

SELECT last_name, salary, department_idFROM employeesWHERE salary IN (SELECT min(salary) FROM employees GROUP BY department_id)

• liste des employés qui gagnent plus qu'un employé du département 10

SELECT last_name, salary, department_idFROM employees WHERE salary > ANY (SELECT salary

FROM employees where department_id= 10)


127

SOUS-INTERROGATIONS MULTI-LIGNES

• Afficher les employés qui sont mieux payés que tous les employés du département 30 ?

SELECT last_name, salary, department_idFROM employees WHERE salary > ALL (SELECT salary

FROM employees WHERE department_id = 30)


128

SOUS-INTERROGATIONS CORRÉLÉES

• une sous-requête dont l’évaluation se fait pour chaque ligne de la requête principale.

• Exemple :Afficher les employés dont le salaire est supérieur à la moyenne de salaires dans leurs départements?SELECT last_name , salary FROM employees E WHERE salary> (SELECT AVG(salary) FROM employees WHERE E.department_id = department_id) Système de Gestion de Bases de

Données129

OPÉRATEURS ENSEMBLISTES


130

OPERATEURS ENSEMBLISTES


131

OPERATEUR DESCRIPTION

INTERSECT Ramène toutes les lignes communes aux deux requêtes

UNION Toutes les lignes distinctes ramenées par les deux requêtes

UNION ALL Toutes les lignes ramenées par les deux requêtes y compris les doublons

MINUS Toutes les lignes qui existent dans le résultat de la première requête mais qui n’existent pas dans le résultat de la deuxième.

OPERATEURS ENSEMBLISTES

• Règles a appliquer : • Le nombre de colonnes et leurs types de

données doivent être les mêmes pour chaque requête.

• La clause ORDER BY peut apparaître une seule fois à la fin de la requête composée. Elle inclut les noms de colonnes (de la première requête), leurs alias ou leurs positions dans la clause SELECT.


132

UNION

• Le nombre de colonnes et le type des colonnes doivent être identiques dans les 2 ordres Select

• Exemple :Afficher pour chaque employé les postes qu’il a occupés depuis sa mise en service dans l’entreprise ?SELECT employee_id, job_id FROM employeesUNION SELECT employee_id, job_id FROM job_history ;• Si on veut afficher toutes les occurrences, alors

l’opérateur UNION ALL peut être utilisé.


133

INTERSECTION

• Afficher les employés ayant occupé le poste actuel plus d’une fois ?

SELECT employee_id, job_id FROM employeesINTERSECTSELECT employee_id, job_id FROM job_history ;


134

MINUS

• Afficher les employés qui n’ont jamais changé de job ?

SELECT employee_id, job_id FROM employeesMINUSSELECT employee_id, job_id FROM job_history ;


135

cours sgbd 2014

Documents