cours bases de données

Elaboré par : Mme YEKKEN Sabrine
ANNEE UNIVERSITAIRE : 2015 - 2016
Introduction Générale
3
Objectifs du cours
1ère Partie : Le système de gestion des fichiers
1.
Définitions et Caractéristiques
2.
Limites des fichiers
2ème Partie : Le système de gestion de bases de données
1.
Définitions
2.
Objectifs d’un SGBD
3.
Les modèles de Données
4.
Conception des BD
Objectifs de la partie :
Définition et présentation du SGF ainsi
que ses limites
5
Le SGF.
i.
Définitions (fichier, enregistrement)
Un fichier: une suite de données structurée (souvent sous
la forme d'une liste d'enregistrements suivant un même
format), portant un nom et codé sur un support physique.
ii. Un enregistrement: élément d'un fichier, qui représente
l'unité logique.
6
Le SGF.
Présentation du SGF
(1/2)
Information
logique
Information
physique
 Nom répertoire
 Nom fichier
 Indications
fichier
 Disque
 Cylindre
 Piste
 Secteur
Le fonctionnement d’un système de gestion de fichiers
7
Le SGF.
i.
Présentation du SGF
(2/2)
Le Système de Gestion de Fichiers (SGF) joue un rôle central
dans un système d’exploitation car il doit gérer la plupart des
informations des usagers et du système lui-même.
ii. Le SGF est aussi un outil de manipulation des fichiers et de la
structure d’arborescence des fichiers sur disque.
iii. Le fichier est l’unité logique de base d’un SGF.
8
Le SGF. Exemples du SGF
(1/2)
1. FAT : 1981 - MS-DOS / PC-DOS
i.
Avantages : Simple, rapide pour des petites partitions
(<200Mo), peu gourmand en place disque.
ii. Inconvénients : Limité à des partitions de 2Go. Baisse des
performances sur des volumes de grande taille. Attributs
limités. Aucune sécurité d'accès.
9
Le SGF.
Exemples du SGF
(2/2)
1. NTFS : 1993 - Windows NT 3.x
i.
Avantages : Efficace pour des volumes de 400 Mo ou + sans
perte de performance. Système de recouvrement de fichiers ne
rendant pas nécessaire le lancement d'utilitaires de réparation de
disque. Gestion d'attributs étendus, sécurité des données,
contrôle d'accès.
ii. Inconvénients : Taille utilisée par NTFS (~ 4%) sur disque
ne le rendant pas adapté aux volumes < 400Mo.
10
Le SGF.
1.
Limites du SGF
Redondance des données
i.
la même donnée est présente plusieurs fois.
ii. Difficulté lors de la mise à jour des données.
2.
Insécurité des données
i.
Accès pour un seul user.
ii. Pas de confidentialité de l’information.
3.
Incohérence des données
i.
MAJ d’une partie des données redondantes
ii. Non respect des contraintes d’intégrités
Objectifs de la partie :
Définition et objectifs d’un SGBD et
présentation des modèles de données
et la conception d’une BD.
12
Une base de donnée -
Définition
BD ou BDD est l'abrév. française, équivalente à DB. « Database »
en anglais.
Définition:
Ensemble structuré d'informations conçu et réalisé afin de
faciliter leur consultation et modification rapide et sûre,
effectuées simultanément par plusieurs utilisateurs.
13
Le SGBD. Définition
i.
Un Système de Gestion de Base de Données (abr. SGBD) est
un logiciel système destiné à stocker et à partager des
informations dans une BD, en garantissant la qualité, la
pérennité et la confidentialité des informations.
ii. Un SGBD (en anglais DBMS pour « Data base Management
System ») permet d'inscrire, de retrouver, de modifier, de
trier, de transformer ou d'imprimer les informations de la
base de données.
14
Le SGBD. Objectifs
i.
Eliminer les redondances de données
ii. Offrir un accès multiple aux données
iii. Faciliter la recherche des données via le langage de
manipulation de données (LMD)
iv. Fournir une description des données stockées dans la base
v.
Assurer la sécurité d’accès aux données
15
Exemples d’SGBD
 Microsoft Access
 MySql
 Oracle Database
 Microsoft Sql Server...
23
La conception des BD
(1/5)
un schéma conceptuel est une représentation graphique qui sert à
décrire le fonctionnement d'une BD.
Il représente ainsi les objets principaux contenus dans cette dernière,
leurs caractéristiques et les relations qui s'établissent entre ces
différents objets.
Cette représentation est normée suivant une modélisation bien
définie.
19
La conception des BD
(2/5)
24
La conception des BD
(3/5)
Plusieurs types de schémas conceptuels existent, correspondants aux
différents types de base de données que l'on peut rencontrer :

le modèle hiérarchique :L'information est organisée de manière
arborescente+le point d'accès à l'information est bien unique,

Exemple: le canard appartient à la famille des anatidés qui elle-même
appartient à l'ordre des ansériformes qui lui-même appartient à la classe
des oiseaux qui elle-même appartient au sous-embranchement des
vertébrés qui lui-même appartient au règne animal.
25
La conception des BD

(4/5)
le modèle réseaux : principe du regroupement des différents
éléments de la base de données par leur sens, plusieurs points
d’accès, le schéma conceptuel est représenté par un graphe ce
qui augmente les possibilités de liens entre les objets.

le modèle relationnel (modèle entité / association),
correspond à un diagramme où sont définies les entités de la
base ainsi que les associations qui les lient.
25
La conception des BD

(5/5)
Le modèle objet : classes + objets
Une donnée est une instance d’une classe.
Chaque champ est un objet et la notion d’héritage peut être
utilisée lors de la définition des tables.

le modèle déductif :
les données sont des tables et le langage d’interrogation est basé
sur le calcul des prédicats et la logique du premier ordre.
Chapitre 1:
Le Modèle Entité/Association
21
Objectifs du cours
1.
Définir le modèle Entité/Association
2.
Présenter les concepts de bases du modèle E/A
26
Présentation du modèle E/A
Le modèle Entité/Association (appelé aussi Modèle entité-relation)
est un type de schéma conceptuel très utilisé pour les bases de
données, notamment les bases de données relationnelles.
une BDR est un stock d'informations décomposées et organisées
dans des matrices appelées relations ou tables conformément au
modèle de données relationnel. Le contenu de la base de données
peut ainsi être synthétisé par des opérations d'algèbre relationnelle:
l'intersection, la jointure, l’union, le produit cartésien…
27
Les concepts du modèle E/A. Présentation
Il s'agit d'un outil permettant de décrire le fonctionnement de la base de
données en notifiant :

Une Entité

Une Propriété

Un Identifiant

Une Association / Propriétés d’association

Les Cardinalités

Une Sous-Entité

Une Agrégation
(1/11)
28
Les concepts du modèle E/A. Une Entité

(2/11)
Une entité est un objet du monde réel ayant une existence
propre et présentant un intérêt pour l'application. Une entité est
utilisée par l'application, l'entreprise ou l'organisme.
MATCHS

JOUEURS
On représente un type d'entité pour figurer un ensemble
d’entités : une entité = 1 occurrence de l'ensemble.
29
Les concepts du modèle E/A. Propriétés
(3/11)
 Une propriété est une Information ayant des valeurs
qualifiant une occurrence d'une entité.
 Une propriété ne doit dépendre que de la seule entité à laquelle
elle appartient.
Nom de l’ENTITE
Identifiant
Propriété 1
Propriété 2
Propriété 3
30
Les concepts du modèle E/A. Propriétés
(4/11)
Les attributs ou propriétés :
Sont : élémentaire ou composé / mono-valué ou multi-valué.

Élémentaire = atomique (exemple nom, prénom)

Composé = peut être divisé en sous parties d’attributs élémentaires
ayant des significations indépendantes (exemple: adresse (n°, rue, code
postal, ville…))

Mono-valué = attribut ne pouvant avoir qu’une seul valeur (exemple :
âge, grandeur, matricule…)

Multi-valué = attribut qui peut avoir plus qu’une valeur ( exemple:
diplômes)
Les concepts du modèle E/A.
Identifiant
 Une entité a un unique identifiant
 Un identifiant = une ou plusieurs propriétés
 Caractérise de manière unique une entité de l'ensemble/ un
enregistrement parmi d’autres.
Exemple:
JOUEURS
N°Joueur
NomJoueur
PrénomJoueur
Joueur
6,Cantona,Eric
Joueur
16,Cantona,Joël
(5/11)
32
Les concepts du modèle E/A.

Association
(6/11)
Ensemble de liens entre occurrences d'entités.
Joueur 1
Equipe
1
Joueur 2
Joueur 3
Joueur 4
Joueur 5

L'Equipe 1 Fait Jouer 5 Joueurs. Le Joueur 1 Joue dans l’Equipe 1.
Equipes
N°Equipe
NomEquipe
Ville
5-n
JOUEURS
Fait-Jouer
1-1
N°Joueur
NomJoueur
PrénomJoueur
33
Les concepts du modèle E/A.
Propriété d’une A
(7/11)
 Une association peut avoir une ou plusieurs propriété(s) si celleci dépend de toutes les occurrences d'entités qu'elle relie.
Matchs
0,n
N°Match
NbSpectateurs
NbPtsLocaux
NbPtsVisiteurs
jouer
NbPointsMarqués
0,n
JOUEURS
N°Joueur
NomJoueur
PrénomJoueur
34
Les concepts du modèle E/A.
Cardinalités
 La cardinalité minimale est 0 ou 1.
 La cardinalité maximale est 1 ou n.
 Il existe 4 types de cardinalités : 0,1 / 0,n / 1,1 / 1,n
Exemple :
Equipes
5-n
JOUEURS
Fait-Jouer
1-1
N°Equipe
NomEquipe
Ville
 Une Equipe Fait Jouer de 5 à n Joueurs.
 Un Joueur joue dans 1 et au plus 1 Equipe.
N°Joueur
NomJoueur
PrénomJoueur
(8/11)
35
Les concepts du modèle E/A.
Sous-entité
(9/11)
Une sous-entité exprime la notion d’héritage. Les sous-entités
héritent des attributs de la classe mère avec quelques spécifications
qui sont ces attributs.
Exemple:
36
Les concepts du modèle E/A.
Réflexive
(10/11)
Une association réflexive est une association binaire qui fait intervenir
au moins deux fois la même entité.
37
Les concepts du modèle E/A.
Entité faible
(11/11)
Une entité faible : entité sans identifiant propre et qui dépend d’une
autre entité dite forte.
NumSalle
38
Quelques conventions à Retenir
•
Les entités sont représentées dans des rectangles et s'écrivent en
lettres majuscules avec un nom au pluriel.
•
Le nom de l’association est un verbe à l’infinitif.
•
L'identifiant d'une entité (clé primaire) est le premier attribut cité et
est souligné. Les autres attributs sont placés à la suite.
•
Les relations sont placées dans des ellipses ou des losanges avec
leurs attributs respectifs.
•
Les cardinalités sont placées à côté de l'entité qu'elles caractérisent.
•
Les clés étrangères n'apparaissent pas dans l'entité où elle n'est pas
la clé primaire.
39
Démarche de conception
Voici une méthode possible pour réaliser un schéma E/A :
1.
Etablir la liste des entités
2.
Déterminer les attributs de chaque entité en choisissant un
identifiant
3.
Etablir les relations entre les différentes entités : les Associations
4.
Déterminer les attributs de chaque relation et définir les
cardinalités
5.
Vérifier la cohérence et la pertinence du schéma obtenu
Chapitre 2:
Le Modèle Relationnel
41
Objectifs du cours
1ère
2
ème
Partie : Définir Le modèle relationnel
Partie : Définir Les règles de passage
1.
Entités
2.
Associations
3ème Partie : Définir les règles d’optimisation
4
ème
Partie : La normalisation
1.
Principe
2.
Les formes normales
Objectifs de la partie :
Définition et notation du modèle
relationnel.
43
Le modèle relationnel.
Présentation
Modèle relationnel = Niveau logique
44
Le modèle relationnel.
i.
Définition
Un modèle relationnel est composé de relations. (tables)
ii. Ces relations dont décrites par des attributs (noms colonnes)
iii. Toute relation (ou table) a une et une seule clé primaire
(attribut souligné), qui peut être composée d’un ou plusieurs
attributs.
iv. Une relation peut faire référence à une autre en utilisant une
clé étrangère, qui correspond à la clé primaire de la relation
référencée.
45
Le modèle relationnel.
i.
Notations
Souligner la totalité de clé primaire d’une relation
ii. Précéder ou suivre les clés étrangères par une #
 Chaque ligne (tuple ou enregistrement) d’une table
représente une occurrence de l’entité ou de l’association
correspondante.
ème
2
Partie :
Les Règles de passage du modèle E/A au modèle
relationnel
Objectifs de la partie :
Définir les règles de passage du modèle
E/A au modèle relationnel, les règles
d’optimisation et quels cas particuliers.
47
Règles de passage. Entités
(1/2)
Une entité (modèle E/A) = Une Relation (modèle R)
i.
Nom de l’entité = Nom de la relation
ii. Attributs de l’entité = Attributs de la relation
iii. Les identifiants de l’entité = la clé primaire de la relation
48
Règles de passage. Entités
(2/2)
49
Règles de passage. Association
(1/2)
Une Association (modèle E/A) = Une Relation (modèle R)
i.
Nom de l’association = Nom de la relation
ii. Les identifiants des entités participantes = la clé primaire
de la relation
iii. Attributs de l’association + Les identifiants des entités
participantes = attributs de la relation
50
Règles de passage. Associations
(2/2)
51
Règles d’optimisation.
1. Repérer les cardinalités (0-1) et (1-1)
2. Déplacer tous les attributs de l’association vers l’entité du côté
des cardinalités (0-1) ou (1-1) en éliminant les attributs en
double.
3. Si le nombre d’entité restant est strictement inférieur à 2 alors
supprimer la relation (c à d l’ancienne association).
52
Règles de passage. Règle 1 ( X-N / 1-1)
53
Règles de passage. Règle 2 (X-N / X-N)
54
Règles de passage. Cas de l’association réflexive
1er Cas:
0-1 / 0-n
(1/2)
55
Règles de passage. Cas de l’association réflexive
2ème Cas:
0-n / 0-n
(2/2)
56
Règles de passage. Cas de l’association faible
57
Règles de passage. Cas d’une sous entité
1ère solution :
NomEquipe)
(1/2)
58
Règles de passage. Cas d’une sous entité
(2/2)
Remarque:
La mise à jour simultanée
de ces attributs peut être
réalisée à travers un
mécanisme automatique
implémentant l'héritage,
par exemple un
TRIGGER.
2ème solution
3ème Partie :
La normalisation des bases de données
Objectifs de la partie :
Définition de la normalisation, son
intérêt ainsi que les 3 formes
normales
60
Normalisation. Principe et Objectifs
i.
Normaliser une base = imposer des règles de construction à
sa structure afin de respecter la cohérence des données et
éviter toute redondance d'informations.
ii. L’objectif de la normalisation = construire un schéma
de
bases de données cohérent. Pour qu’un modèle relationnel
soit normalisé, il faut respecter certaines contraintes
appelées Formes Normales. Ces FN s’appuient sur des
Dépendances Fonctionnelles (DF).
61
Exemple. Enoncé
Pour vous montrer l’intérêt de la normalisation d’une BDR, commencez par
détecter les problèmes que peuvent poser l’utilisation d’une BD basée sur ce
modèle relationnel non normalisé.
62
Exemple. Réponse
(1/2)
63
Exemple. Réponse
(2/2)
64
Exemple. Conclusion
La normalisation élimine les redondances, ce qui permet :
 Une diminution de la taille de la base de donnée sur le
disque
 Une diminution des risques d’incohérence
 D’Éviter une mise à jour multiple des mêmes données
65
Normalisation. Dépendance Fonctionnelle : Définition
Définition :
B est en dépendance fonctionnelle de a si à une
valeur quelconque de la propriété a on ne peut
faire correspondre qu’une seule valeur au plus
de la propriété b.
Si on connait la valeur de a, on peur en déduire
une seule valeur de b (le contraire n’est pas
vrai)
66
Normalisation. Dépendance Fonctionnelle : Exemple
Exemple de DF:
Num_client  Nom_client
i.
Il existe une DF entre le numéro client et son nom.
ii.
Si on connait une valeur de la propriété « num_client »
(ex: 127), on ne peut lui correspondre qu’une seule valeur
de la propriété « nom_client » (ex: Bensalah ali )
67
Normalisation. Les formes normales (1
ère
FN)
(1/3)
1ère FN: Il s’agit de vérifier si tous les attributs sont
élémentaires + mono valués
Exemple1. Attributs composés :
1er Cas : Si la personne a une seule adresse
68
Normalisation. Les formes normales (1
ère
FN)
2ème Cas: Si la personne peut avoir plusieurs Adresses
(2/3)
69
Normalisation. Les formes normales (1
Exemple2. Attributs multivalués :
ère
FN)
(3/3)
70
Normalisation. Les formes normales (2
ème
FN)
(1/2)
2ème FN: Une relation est en 2FN si elle est en 1FN et de plus, si
les attributs n'appartenant pas à la clé primaire ne dépendent
pas fonctionnellement d'une partie de la clé.
Exemple : Gestion de compte bancaire
OPERATION (N°Compte, CodeOpe, DateOpe, Nom, Prenom, LibelOpe, Somme)
On note que :
- Nom et Prénom dépendent fonctionnellement de N°Compte
- Libellé d'opération dépend fonctionnellement de Code opération
71
Normalisation. Les formes normales (2
ème
FN)
(2/2)
Correction - Exemple : Gestion de compte bancaire
On va obtenir les relations suivantes :
COMPTE(N°Compte, Nom, Prénom)
LIBELLE(CodeOpe, LibelOpe)
OPERATION(#N°Compte, DateOpe, #CodeOpe, somme)
72
Normalisation. Les formes normales (3
ème
FN)
(1/4)
3ème FN: Une relation est en 3FN si elle est en 2FN
et de plus, tout attribut non clé ne dépend pas fonctionnellement
d'un autre attribut non clé.
Exemple 1 : l'organisme de formation
Soit la relation:
ADHERENT(CodeAdh,NomAdh,AdressAdh,TypAdh,CotisTyp,NbSaladh)
 On impose que la cotisation de l'adhérent dépend fonctionnellement du type
de l'adhérent. (TypAdh  CotisTyp)
73
Normalisation. Les formes normales (3
ème
FN)
(2/4)
Correction – Exemple 1 : l'organisme de formation
TYPE(TypAdh, CotisAdh)
ADHERENT(CodeAdh,NomAdh,AdressAdh,#TypAdh, NbSaladh)
74
Normalisation. Les formes normales (3
ème
FN)
(3/4)
Exemple 2 : l'organisation des salariés
Soit la relation:
SALARIES(num_salarié, nom,date_naissance,num_service,nom_service)
Et la dépendance suivante:
num_service  nom_service
75
Normalisation. Les formes normales (3
ème
FN)
(4/4)
Correction - Exemple 2 : l'organisation des salariés
SALARIES (Num_salarié, nom, date_naissance,#num_service)
SERVICES (Num_service, nom_service)
76
Exercice.
Enoncé
Soit la relation suivante:
SOIGNER (code_medecin, code_malade, nom_medecin,
nom_malade, date , adresse )
On note que:
code_medecin nom_medecin
code_malade nom_malade
Chaque médecin a un seul cabinet
Question : Mettre la relation en 3FN.
77
Exercice. Solution
Medecin (code_medecin , nom_medecin )
Malade (code_malade, nom_malade )
Soigner (code_malade#, code_medecin#, date , num,
nom_rue, ville, CP )
Chapitre 3:
L’ a l g è b r e R e l a t i o n n e l l e
79
Objectifs du cours.
1.
Qu’est ce qu’une Algèbre Relationnelle?
2.
Les types d’opérations
3.
i.
Les opérateurs de base
ii.
Les opérateurs ensemblistes
iii.
Opérations d’agrégation
Exercices d’application
80
Introduction
i.
le modèle relationnel est un modèle d'organisation des
données sous forme de Tables (Tableaux de valeurs) ou
chaque
Table
représente
une
Relation,
au
sens
mathématique d'Ensemble.
ii. Les colonnes des tables s'appellent des attributs et les
lignes des n-uplets ou enregistrements.
81
Qu’est ce qu’une algèbre relationnelle?
i.
Une algèbre est
un ensemble d’opérateurs de
base,
formellement définis, qui peuvent être combinés à souhait
pour construire des expressions algébriques.
ii. Une algèbre relationnelle est un ensemble d’opérateurs
agissant sur des relations et produisant des relations.
i.
Les Opérandes sont des relations du modèle relationnel,
ii.
le résultat de toute opération est une nouvelle relation
82
Les types d’opérations
i.
Opérations de base

Projection, Sélection, Jointure
ii. Opérations ensemblistes

Union, Intersection

Différence, Produit
iii. Opérations d’agrégation

Somme, Moyenne, Minimum, comptage…
83
Opérations de base.
i.
Projection
(1/2)
Définition
Notée (

) cet opération permet d’extraire des colonnes d’une
relation. Cet opérateur ne porte que sur 1 relation.
Il permet de ne retenir que certains attributs spécifiés d'une relation.
L’opérateur de projection doit éliminer les duplicata
ii. Syntaxe
R = PROJECTION (R1, liste des colonnes)
Équivalent à
 colonnes (R1)
84
Opérations de base.
Projection
(2/2)
iii. Exemple
Soit le schéma relationnel suivant :
R1
Quelles sont les espèces enregistrées dans la tables CHAMPIGNONS?
Résultat :
85
Opérations de base.
i.
Sélection
(1/2)
Définition
Notée ( ) cet opération permet de Sélectionner un sous-ensemble
de lignes d’une relation. (tout en conservant la totalité des colonnes)
ii. Syntaxe
R = SELECTION (R1, condition)
Équivalent à
 condition (R1)
Rq: une condition est exprimée à l'aide des opérateurs arithmétiques ( =, >,
<, >=, <=, <>) ou logiques de base (ET, OU, NON).
86
Opérations de base.
Sélection
(2/2)
iii. Exemple
Soit le schéma relationnel suivant :
R3
Quelle est la liste des champignons dont la catégorie est ‘Sec’ ?
Résultat :
87
Opérations de base.
i.
Jointure
(1/3)
Définition
Notée ( ) ) cet opération permet d’extraire des lignes de deux relations
différentes. Généralement, ce sont les Primary Key et Foreign Key qui
sont utilisées.
ii. Syntaxe(s)
R = JOINTURE (R1, R2, conditions)
Rq: - condition d'égalité entre attributs  équijointure
- condition d’inégalité (<,>, <=,>=, <>…)  non équijointure
88
Opérations de base.
Jointure
(2/3)
iii. Exemple
Soit le schéma relationnel suivant :
???
89
Opérations de base.
Jointure
iii. Exemple – solution
R = JOINTURE (PRODUIT, DETAIL_COMMANDE,
Produit.CodePrd=Détail_Commande.CodePrd)
(3/3)
90
Opérations ensemblistes.
i.
Union
(1/3)
Définition
∪ ) cet opération permet de ramener toutes les lignes
Notée ( 
distinctes existante dans les deux relations..
ii. Syntaxe
R = R1 UNION R2
Ou
R = UNION ( R1 , R2)
91
Opérations ensemblistes.
Union
iii. Exemple
Soit le schéma relationnel suivant :
Quelle est la liste des enseignantes qui sont élus au CA OU
représentants syndicaux?
(2/3)
92
Opérations ensemblistes.
Union
(3/3)
iii. Exemple – solution
R = UNION (E1 ,E2)
Rq: La relation résultat possède les attributs des relations d'origine et les nuplets de chacune, avec élimination des doublons éventuels.
93
Opérations ensemblistes.
i.
Intersection
Définition
(1/3)

Notée ( ∩ ) cet opération permet de ramener toutes les lignes
communes dans les deux relations..
ii. Syntaxe
R = INTERSECTION ( R1, R2)
94
Opérations ensemblistes.
Intersection
iii. Exemple
Soit le schéma relationnel suivant :
Quelle est la liste des enseignantes qui sont élus au CA ET
représentants syndicaux?
(2/3)
95
Opérations ensemblistes.
Intersection
(3/3)
iii. Exemple – solution
R = INTERSECTION (E1 ,E2)
Remarque : La relation résultat possède les attributs des relations d'origine
et les n-uplets communs à chacune.
96
Opérations ensemblistes.
i.
Différence
(1/3)
Définition
Notée ( - ) cet opération permet de ramener toutes les lignes de la
première relation sauf les lignes existante dans la seconde relation.
ii. Syntaxe
R = DIFFERENCE ( R1, R2)
97
Opérations ensemblistes.
Différence
iii. Exemple
Soit le schéma relationnel suivant :
Quelle est la liste des enseignantes du CA qui ne sont pas des
représentants syndicaux?
(2/3)
98
Opérations ensemblistes.
Différence
(3/3)
iii. Exemple – solution
R = DIFFERENCE (E1 ,E2)
Rq: La relation résultat possède les attributs des relations d'origine et les nuplets de la première relation qui n'appartiennent pas à la deuxième.
- DIFFERENCE (R1, R2) ne donne pas le même résultat que DIFFERENCE
(R2, R1)
99
Opérations ensemblistes.
i.
Produit
(1/3)
Définition
Notée ( X ) cet opération permet de ramener un produit cartésien
formé par la concaténation de chaque n-uplet de la première relation
avec l'ensemble des n-uplets de la deuxième relation.
ii. Syntaxe
R = PRODUIT ( R1, R2)
100
Opérations ensemblistes.
iii. Exemple
Soit le schéma relationnel suivant :
Examen??
Produit
(2/3)
101
Opérations ensemblistes.
Produit
iii. Exemple – solution
Examen = PRODUIT (Etudiants, Epreuves)
(3/3)
102
Exercice1.
Enoncé
Soit le schéma relationnel suivant :
CLIENTS (CodeClient, NomClient, AdrClient, TélClient)
COMMANDES (N°Commande, Date, CodeClient#)
Question : Ecrire les relations qui permettent d’obtenir le
code et le nom des clients ayant commandé le 10/06/97
103
Exercice1.
Solution
Soit le schéma relationnel suivant :
R1 = SELECTION (COMMANDE, Date=10/06/97)
R2 = JOINTURE (R1,CLIENT,R1.CodeClient=CLIENT.CodeClient)
R3 = PROJECTION (R2, CodeClient, NomClient)
104
Opérations d’agrégation.

Elles
sont
utilisées
dans
les
Définition
opérateurs
(1/2)
CALCULER
et
REGROUPER_ET_CALCULER.

Elles portent sur un ou plusieurs groupes de n-uplets et évidemment
sur un attribut de type numérique.
i.
Somme (attribut) : total des valeurs d'un attribut
ii.
Moyenne (attribut) : moyenne des valeurs d'un attribut
iii.
Minimum (attribut) : plus petite valeur d'un attribut
iv.
Maximum (attribut) : plus grande valeur d'un attribut
105
Opérations d’agrégation.
v.
Définition
(2/2)
La fonction de comptage : Comptage()
Définition:
La fonction de comptage donne le nombre de n-uplets ou
enregistrements d'une relation.
R=CALCULER(R0,
fonction_agreg1,
fonction_agreg2,
...)
ou
N=CALCULER(R0, fonction_agreg)
R=REGROUPER_ET_CALCULER(R0, att1, att2, ..., fonction_agreg1,
fonction_agreg2, ...)
106
Exercice2.
Enoncé
Soit le schéma relationnel suivant :
ETUDIANT (N°Etudiant, Nom, Prénom)
MATIERE (CodeMat, LibelléMat, CoeffMat)
EVALUER (N°Etudiant#, CodeMat#, Date, Note)
Q1. Quel est le nombre total d'étudiants ?
Q2. Quelle est la moyenne générale de la promotion ?
107
Exercice2.
Corrigé
1.
N= CALCULER ( ETUDIANT, Comptage( ) )
2.
MGETU = REGROUPER_ET_CALCULER ( MOYETUMAT, N°Etudiant,
Nom, Prénom, MgEtu : Somme (MoyEtuMat*CoeffMat) / Somme
(CoeffMat))
MG = CALCULER (MGETU, Moyenne(MgEtu))
Chapitre 4:
SQL : L a n g a g e d e d é f i n i t i o n
des données
109
Objectifs du cours
(1/2)
1ère Partie : Introduction du langage SQL
2ème Partie : Présentation du langage LDD
1.
Définir « LDD »
2.
Les types de données
3ème Partie : Création d’une table/des contraintes d’intégrités
1.
Créer une table
2.
Ajouter une contrainte clé primaire
3.
Ajouter une contrainte clé étrangère
4.
Autres contraintes ( not null, check, unique)
110
Objectifs du cours
5.
Activer / désactiver une contrainte
4ème Partie : Manipulation de la structure de la table
1.
Ajouter, modifier, supprimer une colonne/contrainte
2.
Supprimer une table
3.
Renommer une table
(2/2)
111
1ère Partie:
Introduction du langage SQL
Objectifs de la partie :
Introduire et définir le langage SQL.
112
Introduction générale.
 Pour interroger une BD, il faut utiliser un langage .
 SQL est le langage le plus complet pour interagir une BD.
113
Définition de l’SQL.
SQL (Structured Query Language ), en français langage de requête
structurée, est composé d’un ensemble de sous langages:

un langage de définition de données (LDD),

un langage de manipulation de données (LMD),

un langage d’interrogation de données (LID),

un langage de contrôle de données (LCD),
pour les bases de données relationnelles.
114
2ème Partie :
Présentation du langage LDD
Objectifs de la partie :
Définition et présentation du langage
de définition de données ainsi que les
différents types de données.
115
LDD.
Le langage de définition de données, c’est un langage qui
permet de définir et manipuler les structures de
données et non pas les données.
Structure de données : tout objet de la BD destiné à
contenir des données : Exemple : TABLE.
116
Les types de données.
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
BINARY_FLOAT
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
32-bit nombre avec virgule flottante. C etype nécessite 5 octets
BINARY_DOUBLE
64-bit nombre avec virgule flottante. C etype nécessite 9 octets
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
INTERVAL YEAR(year_precision)
TO MONTH
Stocke une période de temps en année et mois, où year_precision
est compris entre 0 et 9. La valeur par défaut est 2
NUMBER[(precision [, scale]])
117
3ème et 4ème Partie :
Création d’une table/contraintes d’intégrités –
Manipulation de la structure d’une table
Objectifs de la partie :
Comment créer une table et définir les contraintes d’intégrités.
Comment
ajouter,
modifier
supprimer des colonnes ou
contraintes…
ou
des
118
Créer une table.
Syntaxe
Syntaxe :
CREATE TABLE <nom_de_la_table>
( <nom_colonne1> <type_colonne1>,
<nom_colonne2> <type_colonne2>,
<nom_colonne’> <type_colonne’>
);
Remarque : Pour créer une table il faut avoir :
• Le privilège CREATE TABLE
• Un espace de stockage
…
119
Créer une table.
Exemple
 CREATE TABLE Etudiants
( Netudiant number,
Nom varchar2(10),
Prenom varchar2(10)
);
 DESCRIBE (ou DESC) Etudiants ; --pour voir la description de la table
120
Ajouter une contrainte clé primaire
- Syntaxe
(1/2)
1er cas : clé primaire simple
Syntaxe 1: lors de la création de la table:
CREATE TABLE <nom_de_la_table>
( <nom_col1 > <type_col1> CONSTRAINT <nom_contrainte> PRIMARY KEY,
<nom_col2 > <type_col2>, …
<nom_coln > <type_coln>
);
 Contrainte niveau colonne.
Syntaxe 2: après la création de la table :
ALTER TABLE <nomtable> ADD Constraint <nom_contrainte> Primary key
(nom_Col)
121
Ajouter une contrainte clé primaire
- Syntaxe
(2/2)
2ème cas : clé primaire composée
Syntaxe 1: lors de la création de la table:
CREATE TABLE <nom_de_la_table>
( <nom_col1 > <type_col1>,
<nom_col2> <type_col2>,
…
<nom_coln> <type_coln>,
Constraint <nom_contrainte> Primary key (nomCol1, nomCol2...) );
 Contrainte niveau Table
Syntaxe 2: après la création de la table :
ALTER TABLE <nom_de_la_table> ADD constraint <nom_contr> Primary key
(nomCol1,nomcol2…)
122
Ajouter une contrainte clé primaire
- Exemple
Syntaxe :
ALTER TABLE <nom_de_la_table> ADD Constraint
<nom_contrainte> PRIMARY KEY (nom_colonne(s))
ALTER TABLE Etudiants ADD Constraint pk_nom_prenom PRIMARY KEY
(nom,prenom) ;
123
Supprimer
une contrainte clé primaire.
Syntaxe :
ALTER TABLE <nom_de_la_table> DROP CONSTRAINT
<nom_contrainte> ;
ALTER TABLE Etudiants DROP CONSTRAINT pk_nom_prenom ;
124
Ajouter
une contrainte clé étrangère.
Syntaxe :
ALTER TABLE <nom_de_la_table> ADD constraint <nom_contrainte>
FOREIGN KEY (nom_cols) references <table_référencée>
(nom_cols) ;
Create table Etud as select * from Etudiants;
Alter table Etud ADD constraint pk_nom_prenom PRIMARY KEY (nom,prenom) ;
ALTER TABLE Etud ADD constraint fk_etud_etudiants FOREIGN KEY
(nom,prenom) references Etudiants (nom,prenom) ;
125
Ajouter
une contrainte <NOT NULL>.
Cette contrainte est définie :
- Soit lors de la création de la table, syntaxe :
<nom_colonne> <type_colonne> constraint <nom_de_contrainte> NOT NULL
- Soit après la création de la table, syntaxe :
Alter table <nom_tab> modify <nom_col> constraint <nom_de_contr> NOT NULL
La contrainte NOT NULL ne peut être définie qu’au niveau de la
colonne, pas au niveau de la table
126
Ajouter
une contrainte <UNIQUE> -
Définition
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.
Remarque : Une contrainte unique autorise la valeur NULL à
moins que vous définissiez des contraintes NOT NULL
127
Ajouter
une contrainte <UNIQUE>
- Syntaxe
Cette contrainte est définie :
- Soit lors de la création de la table, syntaxe :
<nom_colonne> <type_colonne> constraint <nom_de_contrainte> UNIQUE
- Soit après la création de la table, syntaxe :
Alter table <nom_table> add constraint <nom_de_contrainte> UNIQUE
(<nom_colonne>)
128
Ajouter
une contrainte <CHECK>.
La contrainte Check définit une condition que chaque ligne doit vérifier
Cette contrainte est définie :
- Soit lors de la création de la table, syntaxe :
<nom_colonne> <type_col> CONSTRAINT <nom_de_contr> CHECK (condition)
- Soit après la création de la table, syntaxe :
Alter table <nom_table> ADD CONSTRAINT <nom_de_contr> CHECK (condition)
129
Gérer des colonnes et/ou des contraintes
- Syntaxes
Syntaxe générale :
ALTER TABLE <nom_table>
+ ADD
<Nouvelle définition de la colonne>
+ MODIFY
+ DROP
+ ADD CONSTRAINT
<Nom_colonne>
<Nouvelle définition de la contrainte>
+ DROP CONSTRAINT
+ ENABLE CONSTRAINT
+ DISABLE CONSTRAINT
<Nom_contrainte>
130
Ajouter une/plusieurs colonne(s).
Exemple
Ajouter 2 colonnes à la table « Etudiants »
ALTER TABLE Etudiants ADD (telephone number ,email varchar2(50) );
131
Modifier une colonne. Exemple
Syntaxe :
ALTER TABLE <nom_de_la_table> MODIFY <nouvelle définition de la colonne>
ALTER TABLE Etudiants MODIFY ( email varchar2(100) );
132
Supprimer une colonne.
Exemple
Syntaxe :
ALTER TABLE <nom_de_la_table> DROP <colonnes>
ALTER TABLE Etudiants DROP ( email, telephone);
133
Activer/Désactiver une contrainte.
Syntaxe :
ALTER TABLE <nom_de_la_table>
ENABLE | DISABLE constraint <nom_de_la_contrainte>;
134
Supprimer
une table.
DROP / TRUNCATE
Syntaxe :
DROP TABLE
<nom_de_la_table> ;
Remarque:
On a aussi la commande « TRUNCATE » qui permet de vider la table
TRUNCATE TABLE <nom_de_la_table> ;
135
Renommer une table.
Syntaxe :
RENAME <ancien _nom_table> TO <nouveau_nom_table> ;
Chapitre 5:
SQL : L a n g a g e d e M a n i p u l a t i o n
des données
137
Objectifs du cours
1.
Définir le langage « LMD »
2.
Insertion des données
3.
Mise à jour des données
4.
Suppression des données
138
Définir le langage LMD
LMD : Langage de Manipulation des Données (DML, Data
Manipulation Language)
i.
Il Permet la manipulation des tables, soit l’insertion, la
mise à jour et la suppression des données.
ii. Il est composé de 3 Requêtes :
UPDATE, INSERT, DELETE.
139
Insertion des données
– Syntaxe IMPLICITE
(1/3)
1er cas: Insertion dans toutes les colonnes
INSERT INTO <nom_de_la_table> VALUES
( <valeur_colonne1>,
<valeur_colonne2>, …
<valeur_colonne’>
);
Remarque: le nombre de valeurs de colonnes doit être égal au nombre de
colonne de la table et conformément à leurs types
140
Insertion des données
– Syntaxe EXPLICITE
2ème cas: Insertion dans quelques colonnes
INSERT INTO <nom_de_la_table>
( <colonne1>,
<colonne2>,
<colonne3> )
VALUES
( <valeur_colonne1>,
<valeur_colonne2>,
<valeur_colonne3> );
(2/3)
140
Insertion des données
– Remarques
(3/3)
i.
Les valeurs à ajouter doivent vérifier les contraintes
définies au moment de la définition des données. Tout
enregistrement ne vérifiant pas les contraintes sera rejeté.
ii.
Les champs ayant été créés avec la contrainte Not Null
devront, obligatoirement, avoir des valeurs.
iii. Insertion à partir d'une autre table :
INSERT INTO nom_table [(les_champs_de_la_table)] Requête;
141
Insertion des données
– Exercice - Enoncé
(1/2)
Soit le schéma relationnel suivant :
CLIENTS (CodeClient, NomClient, AdrClient, TelClient)
COMMANDES (NumCommande, Date, CodeClient#)
Questions:
1. Créer les 2 tables avec les contraintes d’intégrité (clés
primaires et étrangères)
142
Insertion des données
– Exercice - Enoncé
(2/2)
2. Insérer les lignes suivantes:
CLIENTS
CodeClient
NomClient
AdrClient
TelClient
C01
Bensalem Ali
3 rue tunis 2080 tunis
71123800
C02
Toumi salma
-
71129870
COMMANDES
NumCommande
Date
CodeClient
110
03/04/2011
C01
111
09/07/2011
C02
143
Mise à jour des données
(1/2)
Syntaxe:
UPDATE <nom_table> SET <colonne_a_modifié> = <nouvelle_valeur>
WHERE ( <condition_de_mise_à_jour> );
Exemple:
Le client Bensalem Ali vient de changer d’adresse et habite avec le client
Toumi salma à « 15 rue Basra Bizerte 7000 », veuillez mettre à jour les
deux adresses.
143
Mise à jour des données
- Remarques
(2/2)
i.
Il n'est pas possible de mettre à jour plus qu'une table à la
fois.
ii.
La modification des données n’est pas autorisée que si les
contraintes sont toujours valides.
iii. Les valeurs peuvent être des constantes, des expressions,
des résultats de sous-requêtes ou NULL (pour supprimer
la valeur initiale du champ).
iv. Si la clause WHERE n’apparaît pas dans la commande, il
s’agit de mettre à jour tous les enregistrements de la
table avec la même valeur.
144
Suppression des données
Syntaxe:
DELETE FROM <nom_table> WHERE ( <conditions> );
Chapitre 6:
SQL : L a n g a g e d’ i n t e r r o g a t i o n
des données
146
Objectifs du cours.
1.
Définir le LID
2.
Savoir formuler des Requêtes simples « SELECT »
3.
Distinguer les fonctions mono lignes/multi lignes
4.
Définir les Jointures, les Opérateurs ensemblistes et les
sous-interrogations
147
1ère Partie :
Les Requêtes simples « la clause SELECT »
Objectifs de la partie :
Définir «LID», savoir sélectionner des
colonnes avec ou sans doublons, avec ou sans
des conditions connaître les Expressions
arithmétiques, la Valeur « NULL », les Alias
de colonne, les opérateurs logique et ceux de
comparaison.
148
LID.
(1/2)
Le langage LID permet, comme son nom l’indique, d’interroger
une BD. Il sert à rechercher, extraire, trier, mettre en forme des
données et calculer d’autres données à partir des données
existantes.
La structure de base d’une interrogation est formée des 3 clauses
suivantes :
SELECT *|<liste champ(s)> FROM <nom_table>
WHERE <condition(s)> ;
149
LID.

(2/2)
La clause SELECT : indique les colonnes à récupérer. (ou
encore des champs projetés).

La clause FROM indique le nom de la ou des table(s)
impliquée(s) dans l’interrogation.

La clause WHERE correspond aux conditions de sélection
des champs.

Chaque nom de champ ou de table est séparé par une
virgule.
150
Sélection des colonnes.
SELECT
*
FROM employees;
Résultat:
Toutes les colonnes
Affiche tous les champs
de la table
151
Sélection des colonnes.
SELECT first_name, last_name
FROM employees;
Résultat:
Une/plusieurs colonnes
Affiche les champs :
« last_name » et
« first_name »
152
Expressions arithmétiques.
i.
Définition
Une expression contient des données de type NUMBER,
DATE et des opérateurs arithmétiques.
Opérateur
Description
+
Addition
-
Soustraction
*
Multiplication
/
Division
153
Expressions arithmétiques.
Exemple 1
SELECT min_salary + max_salary , min_salary - max_salary,
min_salary * max_salary, min_salary / max_salary
FROM jobs;
Résultat:
154
Expressions arithmétiques.
Exemple 2
Ecrire la requête qui permet d’afficher le nom, prénom de
tous les employés ainsi que leur salaire annuel avec une
augmentation de 50 dinars par mois.
155
Valeur «NULL».
Définition
 NULL est une valeur qui n’est pas disponible, non affectée ou inconnue.
 NULL est différent de zéro, espace ou chaîne vide
Exemple 1:
SELECT employee_id, commission_pct
FROM employees ;
Résultat:
156
Valeur «NULL».
Dans les expressions arithmétique.
Exemple 2 :
SELECT Employee_id, (1+commission_pct)*salary
FROM employees;
Résultat:
157
Alias de colonne.

Renomme un en-tête de colonne

Suit le nom de colonne

Peut utiliser le mot clé « as »

Doit obligatoirement être inclus entre guillemets s’il
contient des espaces, des caractères spéciaux ou s’il y a
des majuscules et des minuscules
(1/2)
158
Alias de colonne.
Exemple
(2/2)
SELECT first_name as nom, last_name prenom
FROM employees;
Résultat:
SELECT first_name as "nom de l’employee", last_name "prenom de l’employee"
FROM employees;
Résultat:
159
Opérateur de concaténation.

Concatène des colonnes et/ou des chaines de caractères

Est représenté par le symbole ||
Exemple:
SELECT 'le nom est ' ||first_name|| ', le prénom est '||last_name as "nom et
prenom de l’employe"
FROM employees;
Résultat:
160
Eliminer les doublons

le mot-clé DISTINCT élimine les doublons.
Exemple:
SELECT DISTINCT(commission_pct)
FROM employees;
Résultat:
161
Restriction avec la clause « WHERE »
Syntaxe :
SELECT<nom_des colonnes> FROM <nom_de_la_table >
WHERE <conditions>;
Exemple: liste des employés du département 20
SELECT last_name , first_name FROM employees
WHERE department_id=20;
Résultat:
162
Opérateurs de comparaison.
OPERATEUR
=
<
<=
>
>=
<> Ou !=
BETWEEN val1 AND val2
In (liste de valeurs)
LIKE
DESCRIPTION
Egal à
Inférieur à
Inférieur à ou égal
Supérieur à
Supérieur à ou égale à
Différent
val1 <= val <= val2
Valeur de la liste
Comme
( _ :un caractère, %=plusieurs caractères)
IS NULL
Correspond à une valeur NULL
(1/2)
163
Opérateurs logiques.
OPERATEUR
AND
OR
NOT
(2/2)
DESCRIPTION
Retourne TRUE si les deux conditions sont VRAIES
Retourne TRUE si au moins une des conditions est
VRAIE
Inverse la valeur de la condition
TRUE si la condition est FAUSSE
FALSE si la condition est VRAIE
164
Trier avec « ORDER by ».
Définition
Triez les lignes selon un ordre bien précis avec la clause
ORDER BY:

ASC : ordre croissant (par défaut)

DESC : ordre décroissant
Remarque: La clause ORDER BY vient en dernier dans
l’instruction SELECT
165
Trier avec « ORDER by ».
Exemple
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;
Résultat:
166
Exercices d’applications.
(1/2)
( nous allons utilisé la table « employees » sous le schèma HR )

Afficher la liste de tous les employés.

Afficher le nom, le prénom, la date d’embauche de tous les employés et
renommer les colonnes comme suit: « nom de l’employe », « prenom de
l’employe » et « date dembauche »

Afficher le nom et le prénom des employés triés par date d’embauche.

Afficher les employés qui ont été embauché au cours de l’année 1990.

Afficher la liste des employés dont le salaire est > 2800.

Afficher la liste des employés des départements 10, 30 et 50.
167
Exercices d’applications.

(2/2)
Afficher la liste des employés dont le salaire entre 4000 et 6000 du
département 20

Afficher la liste des employés dont la commission_pct est NULL.

Afficher la liste des employés dont le nom commence par la lettre ‘A’

Afficher la liste des employés dont le prénom comporte la lettre ‘s’

Afficher la liste des employés dont la deuxième lettre du nom est ‘i’

Afficher la liste des employés embauchés pendant les années entre 1986
et 1990

Afficher la liste des employés dont le job est différent de 'CLERK‘ ou
'MANAGER'
2ème Partie :
Les Fonctions Mono lignes
Objectifs de la partie:
Définir et savoir utiliser les :
i.
ii.
iii.
iv.
v.
vi.
Fonctions de caractères
Fonctions de manipulation des caractères
Fonctions de manipulation des dates
Fonctions numériques
Fonctions de conversions
Autres fonctions
169
Fonctions de caractères.
Conversion Minuscule/Majuscule(1/2)
Il existe trois fonctions principales:
 LOWER : convertir les caractères « M » en « m »
 UPPER : convertir les caractères « m » en « M »
 INITCAP : convertir l’initiale de chaque mot en « M » et les
caractères suivants en « m »

Remarque: Une fonction mono ligne est une fonction qui s’applique
enregistrement par enregistrement.
170
Fonctions de caractères.
Conversion Minuscule/Majuscule(2/2)
SELECT LOWER(last_name) as "prénom" FROM employees;
SELECT UPPER(last_name) as "prénom" FROM employees;
SELECT INITCAP(last_name) as "prénom" FROM employees;
171
Fonctions de manipulation des caractères.

CONCAT(chaine1,chaine2) : concatène 2 chaines = ||

SUBSTR(chaine,
pos, taille)
Définitions (1/2)
: extrait une sous chaîne d’une autre
chaîne

LENGTH(ch) : taille d’une chaîne en caractères

INSTR(ch,sch) : position d’une chaîne de caractères dans une
autre chaîne

TRIM(ch) : élimine les espaces à gauche et à droite

LTRIM(ch) : élimine les espaces à gauche

RTRIM(ch) : élimine les espaces à droite
172
Fonctions de manipulation des caractères.

Définitions (2/2)
LPAD (chaine, nbr, carac) : complète une chaîne de caractères sur la
gauche avec une autre chaîne pour avoir n caractères.

RPAD
(chaine, nbr, carac):
complète une chaîne de caractères sur la
droite avec une autre chaîne pour avoir n caractères.

ASCII (chaine) : retourne le code ascii du premier caractère de
la chaîne.

CHR (nbr): retourne le caractère (inverse de ascii).
173
Fonctions de manipulation des caractères.
SELECT 'station' ch1, 'agil' ch2,
Concat ('station 1','agil') "la station",
Substr ('station 1',9,1) "numéro station",
Length ('agil') "longueur ch1",
Instr ('agil','i') "position de i",
Length (Trim(' agil ')) "trim",
Length (Rtrim(' agil ')) "Rtrim",
Length (Ltrim(' agil ')) "Ltrim",
Rpad ('agil',8,'*') "RPAD", Lpad ('agil',8,'-') "LPAD",
ASCII ('test') " code ascii " , CHR(75)
FROM Dual;
Exemples
174
Fonctions numériques.
Présentation

ROUND (x, n) : Arrondir la valeur de x à la précision spécifiée n

TRUNC (x, n) : Tronquer la valeur de x à la précision spécifiée n

FLOOR (x) : si n<x<n+1 alors FLOOR(x)=n

CEIL (x) : si n<x<n+1 alors CEIL(x)=n+1

MOD (x, y) : reste de la division de x sur y

REMAINDER (m, n) : reste d’une division calculé comme suit :
m-(n*q) avec q égale à la partie entière du quotient
175
Fonctions numériques.
Round
SELECT round(123.646, 0) "0 chiffres" , round(123.646, 1) "1 chiffres",
round(123.646, 2) "2 chiffres", round(123.646, 3) "3 chiffres"
FROM Dual;
Résultat:
(1/2)
176
Fonctions numériques.
Round
SELECT round(123.646, -1) "1 chiffres" ,round(123.646, -2) "2 chiffres",
round(123.646, -3) "3 chiffres"
FROM Dual;
Résultat:
(2/2)
177
Fonctions numériques.
SELECT
TRUNC
TRUNC (123.646, 0) "n=0", TRUNC (123.646, 1) "n=1",
TRUNC (123.646, 2) "n=2", TRUNC (123.646, 3) "n=3", TRUNC (123.636, -1)
"n=-1", TRUNC (123.646, -2) "n=-2", TRUNC (123.646, -3) "n=-3"
FROM Dual;
Résultat:
178
Fonctions numériques.
SELECT
FLOOR/ CEIL
floor(-2.56), floor(-2.02), floor(-2.8),
ceil(-2.56), ceil(-2.02), ceil(-2.8)
FROM dual;
Résultat:
SELECT
floor(2.56), floor(2.02), floor(2.8),
ceil(2.56), ceil(2.02), ceil(2.8)
FROM dual;
Résultat:
179
Fonctions numériques.
SELECT
MOD/ REMAINDER
MOD (12, 4), MOD (16, 6)
FROM Dual;
Résultat:
SELECT
remainder (15, 6), remainder (15, 5),
remainder (15, 4), remainder (-15,4)
FROM Dual;
15-(2*6) = 3 / 15-(3*5) = 0 / 15-(4*4) = -1 / -15-(-4*4) = 1
Résultat:
180
Fonctions de manipulation des dates.
(1/3)

MONTHS_BETWEEN (date1,date2): nombre de mois entre deux dates

ADD_MONTHS (date, nb_mois): Ajoute des mois calendaires à une date

NEXT_DAY (date, jour) : le jour suivant

LAST_DAY (date) : le dernier jour du mois

ROUND (date, précision) : arrondi une date

TRUNC (date, précision) : tronque une date

EXTRACT (day/month/year/hour/minute/seconde from date): extraction du
jour, mois, année, heure, minute et seconde
181
Fonctions de manipulation des dates.
SELECT
(2/3)
sysdate "date du jour",
sysdate+2 "Date jr plus 2jrs",
to_date ('10/09/2012','dd/mm/yyyy')-10 "date du jour moins 10" ,
Trunc (sysdate - to_date('01/05/2012','dd/mm/yyyy'),0)
"tronquer une date"
FROM dual ;
Résultat:
182
Fonctions de manipulation des dates.
(3/3)
SELECT
Trunc (months_between (to_date ('2012/01/01', 'yyyy/mm/dd'), to_date
('2012/03/15', 'yyyy/mm/dd') ),2) "nbr de mois",
next_day(to_date ('2012/03/01', 'yyyy/mm/dd'), 'Monday') "lundi suivant",
last_day(to_date ('2012/03/01', 'yyyy/mm/dd')) "dernier jr du mois mars12" ,
add_months(to_date ('2012/03/01', 'yyyy/mm/dd'),3) "ajouter 3 mois"
FROM Dual;
Résultat:
183
Fonctions de conversions.

TO_DATE : Convertir une Chaine en format Date

TO_NUMBER : Convertir une Chaine en format Numérique

TO_CHAR : Convertir une Date /un nombre en une Chaine
184
Fonctions de conversions.
OPTION
Options avec To_char
DESCRIPTION
yyyy
l’année (quatre chiffres)
month
nom complet du mois en minuscule
mon
abréviation du nom du mois en minuscule (trois caractères)
day
nom complet du jour en minuscule
DD
jour du mois (01-31)
D
jour de la semaine (de 1 à 7, dimanche étant le 1)
dy
abréviation du nom du jour en minuscule (3 caractères)
ddd
jour de l'année (001-366)
q
trimestre
w
numéro de semaine du mois (de 1 à 5) (la première semaine commence
le premier jour du mois.)
ww
numéro de semaine dans l'année (de 1 à 53) (la première semaine
commence le premier jour de l'année.
185
Autres Fonctions.
(1/5)
 NVL (nom_col, valeur) : remplace une valeur nulle
 NVL2 (expr,val1,val2) : si expr n’est pas nulle alors elle est remplacée
par val1 sinon par val2.
 NULLIF (val1,val2) : si val1= val2 la valeur NULL est retournée sinon
val1
 Case : évalue une liste de conditions et retourne un résultat parmi les cas
possibles
186
Autres Fonctions.

(2/5)
Ecrire les requêtes SELECT qui permettent de:

Remplacer « commission_pct » par 0 au niveau de la table
« employees » si elle est null

Remplacer « commission_pct » par 0 au niveau de la table
« employees » si elle est null sinon par 1

Afficher
la
liste
des
employés
en
ajoutant
une
« nom_departement » qui affiche :

Si department_id=10 , nom_departement=‘depart_10’

Si department_id=20 , nom_departement=‘depart_20’

Si department_id=30 , nom_departement=‘depart_30’

…
colonne
187
Autres Fonctions.
(3/5)
 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. Ne prend pas en considération les doublons
 RANK: retourne le rang de chaque ligne au sein de la partition d'un
ensemble de résultats. Compte les doublons mais laisse des trous
 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.
Compte les doublons mais ne laisse pas des trous
188
Autres Fonctions.
(4/5)
SELECT
Row_number() over
(partition by <col x> order by <col_y>
DESC/ASC), Col1,…colN
FROM <nom_table> ;
A retenir:
- La clause <order by> est obligatoire.
- La clause <partition by> est facultative, est utilisée pour faire un ordre par
ensemble de lignes selon <colx>.
- Rank et dense_rank ont la même syntaxe.
189
Autres Fonctions.
(5/5)
Ecrire les requêtes SELECT qui permettent de :

Afficher la liste des employees numérotés par « department_id ».

Afficher la liste des employees numérotés par département et selon le
salaire décroissant :

Utiliser « row_number »

Utiliser « rank »

Utiliser « dense_rank »
3ème Partie :
Les Fonctions Multi lignes ( ou fonctions de groupe )
Objectif du cours :
Définir la clause « GROUP BY »,
la clause « HAVING »
191
Fonctions Multi lignes.
Définitions
(1/3)
Une fonction mono ligne est une fonction qui s’applique enregistrement par
enregistrement.
Une fonction multi ligne ou fonction de groupe s’applique sur un groupe
d’enregistrements et donne un résultat par groupe.

Clause GROUP BY : Définit le critère de groupement pour la fonction

Clause HAVING : Permet de mettre des conditions sur les groupes
d’enregistrements
192
Fonctions Multi lignes.
Définitions
 COUNT : Nombre de lignes,
 SUM : Somme des valeurs,
 AVG : Moyenne des valeurs,
 MIN : Minimum,
 MAX : Maximum,
 STDDEV: Ecart type,
 VARIANCE: Variance
(2/3)
194
Fonctions Multi lignes.
SELECT
Exemple1
COUNT (salary) as count,
Trunc (SUM (salary),2) as sum,
MIN (salary) as min,
MAX (salary) as max,
Trunc (AVG (salary),2) as avg,
Trunc (VARIANCE (salary),2) as variance,
Trunc (STDDEV(salary),2) as ecart
FROM employees;
Résultat:
195
Fonctions Multi lignes.
Syntaxe de la clause GROUP BY
SELECT <colonnes>, <fonction de groupe>
FROM table
WHERE <conditions>
GROUP BY <col>|<expr>
HAVING <conditions>
ORDER BY <col>|<expr>
196
Fonctions Multi lignes.
Exemple2
SELECT department_id,
COUNT(employee_id) "nbr employes"
FROM employees
12
Départements
GROUP BY department_id;
SELECT department_id, count(employee_id) "nbr employes"
FROM employees
GROUP BY department_id
HAVING department_id<40;
ème
4
Partie :
Les Jointures
Objectifs de cette partie :
i.
Equijointure et jointure interne, Jointure naturelle
ii.
Jointure externe
iii.
Non équijointure
iv.
Produit cartésien
198
Les Jointures.

Définition.
Une jointure permet d’extraire des données de plusieurs
tables à la fois.

La condition de jointure peut être exprimée dans la clause
« WHERE » ou « ON ».

Précédez le nom de la colonne par le nom de la table lorsque
celui-ci figure dans plusieurs tables
199
Types de Jointures.

Equijointure ou jointure interne

Jointure naturelle

Jointure externe

Non équijointure

Produit cartésien
200
Equijointure.

Définition
Une jointure interne ou équijointure est une jointure avec
une condition de jointure contenant un opérateur d‘égalité.

C’est la plus répandue, elle combine les lignes qui ont des
valeurs équivalentes pour les colonnes de la jointure.

Généralement dans ce type de jointure, ce sont les Primary
Key et Foreign Key qui sont utilisées.
201
Equijointure.
Syntaxe
SELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON (table1.column_name = table2.column_name) ;
(1/2)
202
Equijointure.
SELECT
Syntaxe
T1.Colonne1, …T1.ColonneN,
T2.Colonne1, …T2.ColonneN
FROM table1 T1 INNER JOIN table2 T2
ON T1.C1=T2.C1
WHERE <Condition(s)>
Équivalent à :
SELECT
T1.Colonne1, …T1.ColonneN,
T2.Colonne1, …T2.ColonneN
FROM T1 ,T2
WHERE T1.C1=T2.C1 AND <Condition(s)>
(2/2)

T1 : Alias de la table : table1

T2 : Alias de la table : table2

C1 : colonne permettant la
jointure entre les 2 tables
( primary key + foreign key)
203
Equijointure.
Exemple
Exemple1:
SELECT nomE,
e.numdept, nomdept
FROM emp e INNER JOIN dept d
ON e.numdept=d.numdept;
Exemple2:
SELECT nomE, e.numdept, nomdept
FROM emp e INNER JOIN dept d
ON e.numdept=d.numdept
WHERE e.numdept=11;
204
Jointure naturelle.
Définition
 La jointure naturelle est une équijointure.
 La clause NATURAL JOIN est basée sur toutes les colonnes
des deux tables portant le même nom.
 Elle sélectionne les lignes des deux tables dont les valeurs sont
identiques dans toutes les colonnes qui correspondent.
 Si les colonnes portant le même nom présentent des types de
données différents, une erreur est renvoyée.
205
Jointure naturelle.
Syntaxe
SELECT colonne1, colonne2…
FROM Table1
NATURAL JOIN Table2
WHERE Condition(s) ;
Conditions autres que la condition
de jointure
206
Jointure naturelle.
Exemple
Exemple 1:
SELECT nomE, numdept,nomdept
FROM emp NATURAL JOIN dept;
Exemple 2:
SELECT nomE, numdept,nomdept
FROM emp NATURAL JOIN dept
WHERE numdept=11;
207
Non équijointure.

Définition
Il s'agit là d'utiliser n'importe quelle condition de jointure
entre deux tables, exceptée la stricte égalité. Ce peuvent être
les conditions suivantes :
208
Jointure externe.
Définition

JOINTURES EXTERNES = OUTER JOINS.

Une jointure externe élargie le résultat d’une jointure interne
(INNER JOINS) et permet d’extraire des enregistrements qui
ne répondent pas aux critères de jointure.
 Une jointure externe renvoie toutes les lignes qui satisfont la
condition de jointure et retourne également une partie de ces
lignes de la table pour laquelle aucune des lignes de l’autre ne
satisfait pas la condition de jointure.
209
Jointure externe.
Types de jointures externes
SELECT t1.column, t2.column..
FROM table1 t1
LEFT | RIGHT | FULL OUTER JOIN table2 t2 ON
(t1.column_name = t2.column_name) ;
 Le sens de la jointure externe LEFT ou RIGHT de la clause
OUTER JOIN désigne la table dominante.
 FULL = INNER + LEFT + RIGHT
210
Jointure externe.
Gauche
Exemple:
SELECT nomE, e.numdept, nomdept
FROM dept d LEFT JOIN emp e
ON e.numdept=d.numdept
211
Jointure externe.
Droite
Exemple:
SELECT nomE, e.numdept,nomdept
FROM dept d RIGHT JOIN emp e
ON e.numdept=d.numdept
212
Jointure externe.
complète
Exemple:
SELECT nomE, e.numdept,nomdept
FROM dept d FULL JOIN emp e
ON e.numdept=d.numdept
213
Produit Cartésien.
Définition
On obtient un produit cartésien lorsque :
 Une condition de jointure est omise
 Une condition de jointure est incorrecte
 A chaque ligne de la table 1 sont jointes toutes les lignes de
la table 2.
 Le nombre de lignes renvoyés est égal n1 * n2 où
 n1 est le nombre de lignes de la table 1
 n2 est le nombre de lignes de la table 2
214
Produit Cartésien.
Syntaxe
SELECT « nom_colonne1 », « nom_colonne2 »…
FROM table1 t1
CROSS JOIN table2 t2
Exemple:
Select * from employees CROSS JOIN departments
 On va avoir 107*27 lignes = 2889 lignes
215
Produit Cartésien.
Exemple
SELECT * From emp e
INNER JOIN dept d
ON e.numdept=e.numdept
 OU
SELECT * From emp, dept
 On va avoir 9*4 lignes
216
Jointure.
Récapitulatif
SELECT
Jointure
interne
FROM
<table gauche>
[INNER]JOIN <table droite>
ON <condition de jointure>
SELECT
Jointure
externe
FROM
<table gauche>
LEFT | RIGHT | FULL [OUTER]JOIN
ON <condition de jointure>
<table droite>
SELECT
Jointure
Jointure
naturelle
croisée
FROM
<table gauche>
NATURAL JOIN <table droite>
(USING <noms de colonnes>]
SELECT
FROM
<table gauche>
CROSS
JOIN <table
droite>
5ème Partie :
ensemblistes
i.
UNION
ii.
UNION ALL
iii.
INTERSECT
iv.
MINUS
Les Opérateurs
218
Les opérateurs ensemblistes.
OPERATEUR
INTERSECT
DESCRIPTION
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 ramenées par la première requête
sauf les lignes ramenées par la seconde requête
219
Union(ALL).
Exemple
 Créer une table d’archivage qui contient les informations des
employés qui ont été embauchés après l’année 2009.
 Ecrire une requête qui va afficher tous les employés:
SELECT * from emp
Union (ALL)
SELECT * from emp2
220
Intersect/Minus.
Exemple
Ecrire les requêtes permettant de :
 Afficher les employés qui ont été archivés.
 Afficher les employés qui n’ont pas été archivés.
SELECT * from emp
Intersect/minus
SELECT * from emp2 ;
221
Exercice d’application.
 Ecrire une requête qui permet d’afficher ce résultat à partir de
la table ‘dept’ :
6ème Partie :
Les Sous interrogations
i.
Sous interrogations mono lignes
ii.
Sous interrogations multi lignes
223
Sous interrogations.

Définition
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)

Une sous-interrogation est utilisée dans les clauses suivantes :
 WHERE
 HAVING
 FROM
224
Sous interrogations mono lignes.
Définition
Une sous-interrogation peut ramener un seul résultat on
l’appelle sous interrogation mono ligne.
Les opérateurs de comparaison mono ligne sont :
=, !=, <, >, <=, >=
225
Sous interrogations mono lignes.
Exemple1
Exemple de Mise à jour avec une sous interrogation :
l’employé numéro 112 est affecté au même poste et même
département que l’employé numéro 111
UPDATE employee_id SET (job_id, department_id)
= ( SELECT job_id, department_id
FROM employees
WHERE employee_id=111 )
WHERE employee_id=112 ;
226
Sous interrogations mono lignes.
Exemple2
Exemple de Suppression avec une sous interrogation :
Supprimer tous les employés du département ‘SALES’
DELETE FROM employees
WHERE Department_id =
( SELECT department_id
FROM departments
WHERE department_name=‘SALES’ );
228
Sous interrogations multi lignes.

Définition
(1/3)
Une sous-interrogation peut ramener plusieurs lignes à
condition que l'opérateur de comparaison admette à sa
droite un ensemble de valeurs.

Les opérateurs permettant de comparer une valeur à un
ensemble de valeurs sont :

l'opérateur IN et

les opérateurs obtenus en ajoutant ANY ou ALL à la
suite des opérateurs de comparaison classique :
=, !=, <, >, <=, >=.
229
Sous interrogations multi lignes.

Définition
(2/3)
ANY : la comparaison sera vraie si elle est vraie pour au
moins un élément de l'ensemble (elle est donc fausse si
l'ensemble est vide).

ALL : la comparaison sera vraie si elle est vraie pour tous
les éléments de l'ensemble (elle est vraie si l'ensemble est
vide).

L'opérateur IN est équivalent à = ANY,

L'opérateur NOT IN est équivalent à != ALL.
230
Sous interrogations multi lignes.
Opérateur de comparaison
WHERE salaire IN (1200,1600,2000,2900)
WHERE salaire NOT IN (1200,1600,2000,2900)
WHERE salaire >ANY (1200,1600,2000,2900)
Définition
(3/3)
Résultat
SALAIRE doit être égale à une des valeurs
dans la liste
SALAIRE ne doit pas être égale à une des
valeurs dans la liste
SALAIRE doit être supérieur à au moins une
des valeurs.
Donc plus que le minimum (+ de 1200).
WHERE salaire <ANY (1200,1600,2000,2900)
SALAIRE doit être inférieur à au moins une
des valeurs.
Donc moins que le maximum (- de 2900).
WHERE salaire >ALL (1200,1600,2000,2900)
SALAIRE doit être supérieur au maximum
de toutes les valeurs.
Donc plus que le maximum (+ de 2900).
WHERE salaire <ALL (1200,1600,2000,2900)
SALAIRE doit être inférieur au minimum de
toutes les valeurs.
Donc moins que le minimum (- de 1200).