IAT

Niveau : L 3 GL Enseignant : ALMOU Bassirou

Module : Architecture de SGBD

Activité 1 : PostgreSQL

PostgreSQL est un Système de Gestion de Bases de Données Relationnelles Objet (SGBDRO ou ORDBMS en
Anglais) sous licence GPL, disponible sur l’ensemble des plates-formes Unix,
 PostgreSQL est très complet :
- Il supporte le transactionnel
- Il supporte les procédures stockées
- Il permet à l’utilisateur de définir ses types et ses fonctions
- Il permet de poser des verrous
- Il permet les selects imbriqués
- Il permet la réplication
PostgreSQL utilise les commandes standards de SQL
Quelques commandes usuelles Redhat
Oracle linux unbreakable

- sudo apt-get install postgresql Installation en ligne du server postgreSQ, client, et pgadmin,
- sudo apt-get install postgresql-client utilitaire à l’image de phpMyAdmin
- sudo apt-get install pgadmin3
sudo apt-get install postgresql-contrib postgresql-contrib permet d’installer les extensions reconnues par la
yum install postgresql communauté POSTGRES
dnf install postgresql
Remarque : Sous Windows, vous pouvez installer d’autres module grâce à STACKBUILDER(C:\Program
Files\PostgreSQL\9.6\bin\stackbuilder.exe)
Vérifier le type de connexion
grep -v '^#' /etc/postgresql/9.4/main/pg_hba.conf|grep 'peer'
Pour se connecter au serveur :
psql -U postgres -W
- sudo su postgres
- sudo -u postgres psql
/etc/postgresql/9.4/main/postgresql.conf Editer ce fichier pour permettre à d’autres machines de se connecter
au serveur PostgreSQL. Pour ce faire, localisez la ligne
#listen_addresses = 'localhost' et changez-la en : listen_addresses =
'*'
Pour permettre aux machines utilisant la connexion IPV4 ou IPv6, remplacer 'localhost' par '::'
Postgresql écoute sur le port 5432 par défaut
Soit le Modele Conceptuel de Donnees de la gestion des notes

Remarque : Nous utilisons ce MCD pour répondre aux questions

1. Déduire le MLD issu du MCD ci-dessus
Comment se connecter pour la premiere fois au serveur postgres ?

1
$su postgres
#psql [nom de la base]
#

Hiérarchie des objets de PostgreSQL server

Source : extrait du livre Learning PostgreSQL
CREATE DATABASE nom
[ [ WITH ] [ OWNER [=] nom_utilisateur ]
[ TEMPLATE [=] modèle ]
[ ENCODING [=] codage ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace ]
[ CONNECTION LIMIT [=] limite_connexion ] ]

Remarque :
- Le nom du modèle squelette de la nouvelle base de données ou DEFAULT pour le modèle par défaut
(template1).
- Pour avoir l’aide sur la syntaxe de création de la base de données :
\h create database ;
Create database scolarite Création de la base de données scolarite
Créer une base de données ventes possédée par l'utilisateur ali utilisant le tablespace espace_ali comme espace par
défaut :
CREATE DATABASE ventes OWNER ali TABLESPACE espace_ali;
Créer une base de données ventes qui supporte le jeu de caractères ISO-8859-1
CREATE DATABASE ventes ENCODING 'LATIN1' TEMPLATE template0;
2. Créer la base de données note tout en respectant les paramètres suivants :
• Propriétaire postgres
• Template=template1
• Tablespace=default
• Connection limit = 4

psql scolarite Accéder à la base de données scolarite.

 Si vous n'indiquez pas le nom de la base, alors psql utilisera par
défaut le nom de votre compte utilisateur.
\h create database Fournit l’aide sur la syntaxe complète de création de base de
données
\h create user ; sur la syntaxe complète de création d’un utilisateur
CREATE USER username
[ WITH
[ SYSID uid ]
[ PASSWORD 'password' ] ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ IN GROUP groupname [, ...] ]
[ VALID UNTIL 'abstime' ]
Exemple:
#CREATE USER ibrahim WITH PASSWORD 'fantastic' VALID UNTIL ‘2026-12-31’;
2
# CREATE USER techonthenet WITH PASSWORD 'fantastic' VALID UNTIL 'infinity';

3. Créer l’utilisateur adnane tout en respectant les paramètres suivants:

• SYSID 2525
• PASSWORD 'tresor17'
• CREATEDB
• IN GROUP postgres
4. Créer l’utilisateur ben tout en respectant les paramètres suivants:
• SYSID 2545
• PASSWORD 'pouvoir15'
• NOCREATEDB
• IN GROUP postgres
ALTER USER username
[ WITH PASSWORD 'password' ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ VALID UNTIL 'abstime' ]
drop user iro ; Supprimer l’utilisateur iro
5. Modifier le compte de adnane pour lui attribuer la possibilité de créer d’utilisateurs avec CREATEUSER
Gestion de groupe d’utilisateurs

Syntaxe :
ALTER GROUP name ADD USER
username [, ... ]
ALTER GROUP name DROP USER
username [, ... ]

Exemple
- ALTER GROUP ventes ADD USER rabi, ali;
- ALTER GROUP ventes DROP USER rabi;
6. Créer le groupe super avec la possibilité de créer une base de données
7. Mettre l’utilisateur adnane dans le groupe super
Quelques commandes usuelles
drop database Détruire une base
pg_dump Extraire une base dans un script
pg_dumpall Extraire toutes les bases dans un script
postgres Activer postgres en mode simple utilisateur
postgresql-dump Permettre une mise à jour de la base de données
postmaster Le serveur multi-utilisateurs
psql -U toto Se connecter avec le compte toto
psql -c requete Indiquer un c=fichier contenant des requêtes
psql -o fichier Indiquer un fichier de sortie
psql -p 5434 Spécifier le port du serveur, par defaut 5432
psql – H Provoquer une sortie HTML
psql – W Provoque la demande de mot de passe
\dS Affiche la liste des tables système
\dt Affiche les tables de la base courante
\d Affiche la liste de toutes les tables
\z Afficher les autorisations
\!cmde Exécute une commande via shell
\df Afficher les fonctions
\q Quitter
\l Affiche la liste des bases de données
\h create table Donne de l’aide sur la syntaxe de création d’une table

-- ligne en commentaire
3
/* paragraphe */

Création de la base de données create database bourseetudes ;

« bourseetudes »
Suppression de la base de données drop database bourseetudes ;
« bourseetudes »
Accéder à la base de données psql bourseetudes
« bourseetudes »
Le programme psql dispose d'un certain nombre de commandes internes qui ne sont pas des commandes SQL. Elles
commencent avec le caractère antislash
Par exemple, vous pouvez obtenir de l'aide \h
sur la syntaxe de nombreuses commandes
SQL de PostgreSQL
Pour sortir de psql \q
Pour plus de commandes internes \?
Select current_user \g
\d+ Donne la liste des tables de la base de données
\d nomTable ; Description de la table nomTable
\x Affichage étendu
\df Liste des fonctions
Gestion des utilisateurs
\h create user ;
Définir le nombre de connexion
a) SELECT datconnlimit FROM pg_database WHERE datname='postgres';
b) ALTER DATABASE postgres CONNECTION LIMIT 1;
c) SELECT datconnlimit FROM pg_database WHERE datname= 'postgres';

Role

Les attributs des roles

Source : extrait du livre Learning PostgreSQL
CREATE ROLE financier LOGIN;
CREATE DATABASE finances ENCODING 'UTF-8' LC_COLLATE 'en_US.UTF-8' LC_
CTYPE 'en_US.UTF-8' TEMPLATE template0 OWNER financier;
Gestion des privilèges
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }
Les differents privileges: SELECT, INSERT, UPDATE, DELETE, RULE ,ALL, object [ tables, views, sequences]

Retirer les privilèges

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

4
 Schéma

Structure du schema
Source : extrait du livre Learning PostgreSQL
Nombre de parametres
SELECT count(*) FROM pg_settings;
CREATE SCHEMA etudes AUTHORIZATION ali; Créer le schema etudes pour ali
Création de table
CREATE [ TEMPORARY | TEMP ] TABLE table_name (
{ column_name type [ column_constraint [...] ] |
table_constraint }
[, ...]
)
[ INHERITS ( inherited_table [,...] ) ]

column_constraint ::=
[ CONSTRAINT column_constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
DEFAULT default_value |
CHECK (condition |
REFERENCES foreign_table [ ( foreign_column ) ]
[ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE action ]
[ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}

table constraint ::=

[ CONSTRAINT table_constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( condition ) |
FOREIGN KEY ( column_name [, ... ] )
REFERENCES foreign_table
[ ( foreign_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE action ]
[ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}

action ::= { NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT }

8. Créer les tables issues du MLD de la gestion des notes
Créer une table à partir d’une autre

CREATE TABLE table [ ( column [, ...] ) ]

AS select
5
 9. Créer la table sauvegardeNote qui contiendra les données de la table NOTER
Renommer une
Vider toute la table :
TRUNCATE [ TABLE ] name
Bloquer la table durant une transaction :
LOCK [ TABLE ] name
LOCK [ TABLE ] name IN lock_mode
Lock_mode:
ˉ ACCESS SHARE MODE : automatiquement bloquée pour le select,update ou delete. Il bloque egalement
le mode ACCESS EXCLUSIVE
ˉ ROW SHARE MODE :
ˉ ROW EXCLUSIVE MODE : bloque ALTER TABLE, DROP TABLE, VACUUM, et CREATE INDEX
ˉ SHARE MODE
ˉ EXCLUSIVE MODE
ˉ SHARE ROW EXCLUSIVE MODE
ˉ ACCESS EXCLUSIVE MODE
Supprimer une table : DROP TABLE name [, ...]
Creation d’une vue
CREATE VIEW view AS query

10. Créer une vue qui donne la liste des étudiants qui une moyenne générale supérieure ou égale a 10

case 11. Sélectionner les étudiants qui vont

when condition then ‘traitement1’ ‘REDOUBLER’ lorsque la moyenne <10,
when condition then ‘traitement2’ ‘PASSER’ lorsque la moyenne >=10,
end EXCLUS lorsque la moyenne <5
Creation de sequence
CREATE SEQUENCE seqname [ INCREMENT increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache ] [ CYCLE
nextval(’name’) Exemple:
Create sequence suite;
Select nextval(‘suite’);
currval(’name’) Select currval(‘suite’);
setval(’name’, newval) Select setval(‘suite’,100);
12. Créer une séquence qui incrémente l’identifiant de chaque étudiant. Idem pour les autres tables
Type de donnees

Backup:
a) pg_dump
Exemple:
pg_dump -h localhost -p 5432 -U utilisateur -F c -b -v -f mydb.backup mydb
b) pg_dumpall
c) pg_restore
d) pg_basebackup

Modification de la structure de la table

ALTER TABLE table [ * ]
ADD [ COLUMN ] column type
ALTER TABLE table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT default value | DROP DEFAULT }
ALTER TABLE table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD CONSTRAINT newconstraint definition
6
ALTER TABLE table
OWNER TO newowner
a. Ajouter la
b. Creation des contraintes
- Ajouter une clé primaire à la table NOTER
- Ajouter les clés étrangères dans la table NOTER
- Créer la contrainte qui oblige la saisie des notes comprises entre 0 et 20
- Créer une contrainte qui oblige la saisie d’une date de naissance >=17 et <=45
- Définir la nationalité ‘nigerienne’ comme nationalite par défaut

Création de domaine

CREATE DOMAIN nom [AS] type_donnee

[ COLLATE collation ]
[ DEFAULT expression ]
[ contrainte [ ... ] ]

où contrainte est :

[ CONSTRAINT nom_contrainte ]
{ NOT NULL | NULL | CHECK (expression) }

Les domaines permettent d'extraire des contraintes communes à plusieurs tables et de les regrouper en un seul
emplacement, ce qui en facilite la maintenance.
Exemple :
CREATE DOMAIN code_postal AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE courrier(
id_adresse SERIAL PRIMARY KEY,
rue1 TEXT NOT NULL,
rue2 TEXT,
rue3 TEXT,
ville TEXT NOT NULL,
code_postal code_postal_us NOT NULL
);
Creation de TRIGER
Syntaxe :
CREATE EVENT TRIGGER nom
ON evenement
[ WHEN variable_filtre IN (valeur_filtre [, ... ]) [ AND ... ] ]
EXECUTE PROCEDURE nom_fonction()
Remarque:
CREATE EVENT TRIGGER crée un nouveau trigger sur événement. À chaque fois que l'événement désigné
intervient et que la condition WHEN associée au trigger est satisfaite, la fonction du trigger est exécutée
- evenement : Le nom de l'événement qui déclenche un appel à la fonction donnée
- variable_filtre: Le nom d'une variable utilisée pour filtrer les événements. Ceci rend possible de restreindre
l'exécution du trigger sur un sous-ensemble des cas dans lesquels ceci est supporté
- nom_fonction: Une fonction fournie par un utilisateur, déclarée ne prendre aucun argument et renvoyant le
type de données event_trigger
Exemple:

CREATE OR REPLACE FUNCTION annule_toute_commande()

RETURNS event_trigger
LANGUAGE plpgsql

7
 AS $$
BEGIN
RAISE EXCEPTION 'la commande % est désactivée', tg_tag;
END;
$$;

CREATE EVENT TRIGGER annule_ddl ON ddl_command_start

EXECUTE PROCEDURE annule_toute_commande();

CREATE TRIGGER virement

BEFORE UPDATE
ON compte
FOR EACH ROW
EXECUTE PROCEDURE calculSolde();
Exemple
EXISTS SELECT * FROM etudiant WHERE EXISTS (SELECT * FROM
etudiant WHERE ide=1); ou
SELECT * FROM etudiant WHERE EXISTS (SELECT 1 FROM
etudiant WHERE ide=1);
UNION SELECT ide,nom,prenom FROM etudiant WHERE
UNION ALL
INTERSECT
EXCEPT
ANY
ALL
TRIM
||
Type de donnees
il y a 3 grandes catégories de types de données :
- numeric
- chaine de caracteres
- date
NUMERIC
Taille Plage
smallint Equivalent à int2 en 2octets -32768 à +32767
SQL
Int Equivalent à int4 en 4 octets -2147483648 à +2147483647
SQL
Bigint Equivalent à int8 en 8 octets -9223372036854775808 à +9223372036854775807
SQL
Numeric Pas de différence Varie Jusqu’à 131072 dans la partie entière et 16383 dans la
ou decimal partie décimale
real 4 octets
Remarque : il est conseillé d’utiliser les types NUMERIC er DECIMAL pour stocker des données monétaires
CARACTERE
Longueur
char Equivalent à char(1) 1
name Propre à PostgreSQL 64
Char(n) 1à 10485760
Varchar(n) 1 à 10485760
text Illimité
DATE
Taille
Timestamp Equivalent à timestamp 8 octets
without
time zone

8
 Timestamp with time zone
Date
Time without time zone
Time with time zone
interval
LOGIQUE
boolean boolean, true ou false
GEOMETRIQUE
POINT
LSEG ligne
PATH Liste des points
BOX rectangle
CERCLE
POLYGON
RESEAU
INET Adresse IP avec
masque du réseau
MACADDR Adresse MAC
Exemple 1 :
SET timezone TO 'Africa/Niger';
Show timezone;
SELECT now();
Exemple 2
\x
SELECT now(), now()::timestamp, now() AT TIME ZONE 'CST', now()::timestamp AT TIME ZONE 'CST';
TABLEAU
Exemple:
create Table adresse(
coordoneesGPS interger[5],
telephone integer[8][8],
region varchar[][][]);
Insertion de données dans la table adresse:
insert into adresse values('{45,70,52}','{{82546987},{20456978}}','{{Maradi},{Tahoua},{Zinder}}');
Selection de donnees :
SELECT telephone[2][1] FROM adresse ;
Création de table à partir du resulat d’une requete
a. table temporaire
SELECT * temporary etudiantTMP FROM etudiant ;
b. table ordinaire:
Exemple de création des tables

CREATE TABLE client (

idc SERIAL PRIMARY KEY,
nom TEXT NOT NULL,
prenom TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
motpasse TEXT NOT NULL,
CHECK(nom !~ '\s' AND prenom !~ '\s'),
CHECK (email ~* '^\w+@\w+[.]\w+$'),
CHECK (char_length(motpasse)>=8)
);
CREATE TABLE ventes (
idv SERIAL PRIMARY KEY,
idc INT UNIQUE NOT NULL REFERENCES
account(idc),
nbr INT DEFAULT 0,
achat float,
9
total_achat float
);
VUE
Syntaxe:
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ (
column_name [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Exemple:
CREATE OR REPLACE VIEW information(nom,prenom,email) AS SELECT * client;
VUE MATERIALISEE
CREATE MATERIALIZED VIEW table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]
Example
a) CREATE MATERIALIZED VIEW vueMAT as AS SELECT * client WITH NO DATA;
b) SELECT * FROM vueMAT;
c) REFRESH MATERIALIZED VIEW vueMAT;
Par defaut, une vue Postgres est auto-modifiable(delete ; update ; insert)
Decrire le processus d’execution de requetes
Exemple :
EXPLAIN select nom,prenom,email FROM client c, vente v WHERE c.idc=v.idc;

CAST SELECT date_part(‘’,cast(‘14/02/2018’ as date));

SELECT CAST (5.9 AS INT) AS arrondi,
CAST(5.1 AS INTEGER);
DATE_PART()
Les fonctions
CURRENT_DATE
CURRENT_TIME SELECT CURRENT_TIME AS maintenant;
CURRENT_TIMESTAMP
AGE Select age(date1,date2);
- Si date1 n’est pas fournie alors le calcul est: date courante –
date2
Exemple1 : select age(‘2017-09-25’, ‘2017-09-30’) ;
Exemple 2:
SELECT age(timestamp '2017-01-01');
Exemple 3:
SELECT age(timestamp '2017-04-25', timestamp '2017-01-01');
Exemple 4:
SELECT age(timestamp '2014-04-25', timestamp '2014-04-17');
Exemple 5:
SELECT age(current_date, timestamp '2012-09-16');

CURRENT_USER
extract( unit from date ) Unit=[ century| day| decade| dow| doy| hour| microseconds|
minute| month| quarter| second| timezone| timezone_hour|
timezone_minute| week| year]
Exemple 1:
SELECT extract(year from date '2014-04-25');
Exemple2:

10
 SELECT extract(month from timestamp '2014-04-25 08:44:21');
Exemple 3: SELECT extract(minute from time '08:44:21');
Exemple 4
SELECT extract(day from interval '5 days 3 hours')

date_part( 'unit', date ) Extrait une partie d’une date

Exemple 1 : SELECT date_part('day', date '2014-04-25');
SELECT date_part('month', date '2014-04-25');
SELECT date_part('year', date '2014-04-25');
SELECT date_part('day', timestamp '2014-04-25 08:44:21');
Transaction
Begin inserer; Begin supprimer;
Insert into nomTable VALUES(‘’,’’,’’); DELETE FROM etudiant WHERE condition;
Select * from nomTable; Select * from nomTable;
Commit inserer; Rollback supprimer;
Creation d’index
CREATE index nomIndex ON
nomTable(colonne ASC/DESC)
Creation d’une function:

CREATE [ OR REPLACE ] FUNCTION

nom ( [ [ modearg ] [ nomarg ] typearg [ { DEFAULT | = } expression_par_defaut ] [, ...] ] ) ] )
[ RETURNS type_ret
| RETURNS TABLE ( nom_colonne type_colonne [, ...] ) ]
{ LANGUAGE nom_lang
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| COST cout_execution
| ROWS nb_lignes_resultat
| SET parametre { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'fichier_obj', 'symbole_lien'
} ...
[ WITH ( attribut [, ...] ) ]
- modearg; Le mode d'un argument : IN, OUT, INOUT ou VARIADIC. En cas d'omission, la valeur par
défaut est IN. Seuls des arguments OUT peuvent suivre un argument VARIADIC. Par ailleurs, des
arguments OUT et INOUT ne peuvent pas être utilisés en même temps que la notation RETURNS TABLE.
- argtype : Le(s) type(s) de données des arguments de la fonction (éventuellement qualifié du nom du
schéma), s'il y en a. Les types des arguments peuvent être basiques, composites ou de domaines, ou faire
référence au type d'une colonne.
- expression_par_defaut : Une expression à utiliser en tant que valeur par défaut si le paramètre n'est pas
spécifié.
- type_ret: Le type de données en retour (éventuellement qualifié du nom du schéma). Le type de retour peut
être un type basique, composite ou de domaine, ou faire référence au type d'une colonne existante.

Les requêtes
Syntaxe :
SELECT [DISTINCT | ALL]
<expression>[[AS] <output_name>][, …]
[FROM <table>[, <table>… | <JOIN clause>…]
[WHERE <condition>]
[GROUP BY <expression>|<output_name>|<output_number>
[,…]]
[HAVING <condition>]
[ORDER BY <expression>|<output_name>|<output_number>
11
[ASC | DESC] [NULLS FIRST | LAST] [,…]]
[OFFSET <expression>]
[LIMIT <expression>];
CASE
Syntaxe:

CASE WHEN <condition1> THEN <expression1> [WHEN <condition2> THEN

<expression2> ...] [ELSE <expression n>] END
Exemple
SELECT
CASE
WHEN now() > date_trunc('day', now()) +interval '12 hours' THEN 'PM' ELSE 'AM'
END;

Les opérateurs multi-lignes

Les opérateurs multi-lignes sont les suivants :
• IN compare un élément à une donnée quelconque d’une liste ramenée par la sous-interrogation. Cet
opérateur est utilisé pour les équijointures ou autojointures. L’opérateur NOT IN sera employé pour les
jointures externes.
• ANY compare l’élément à chaque donnée ramenée par la sous-interrogation. L’opérateur « =ANY »
équivaut à IN . L’opérateur « <ANY » signifie « inférieur à au moins une des valeurs » donc « inférieur au
maximum ». L’opérateur « >ANY » signifie « supérieur à au moins une des valeurs » donc « supérieur au
minimum ».
• ALL compare l’élément à tous ceux ramenés par la sous-interrogation. L’opérateur « <ALL » signifie «
inférieur au minimum » et « >ALL » signifie « supérieur au maximum ».
Les variables de substitution

CTE (Common table expressions (CTE))

Syntaxe:

WITH <subquery name> AS (

<subquery code>)
[, ...]
SELECT <Select list> FROM <subquery name>;

NULLIF (a, b)
TRUNCATE TABLE nomTable;
Nettoye et analyse une base de donnees
ˉ VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]
ˉ VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
Verifier les parametres AUTOVCUUM

SELECT *FROM pg_settings WHERE name LIKE 'autovacuum%'

Etude de cas : gestion des bourses d’études

Objectif : Créer une base de données PostgreSQL sous Linux pour la gestion des bourses d’études.
a) Dictionnaire de données

Champ Type du champ Contrainte Explication

ide int Clé primaire Clé primaire qui s’auto incrémente à chaque
nouvel enregistrement
nom varchar Not null nom de l’étudiant
Taille maximale=25
Pren varchar Not null Prénoms de l’étudiant
Taille maximale=25
datenaiss date Not null Date de naissance de l’étudiant
format yyyy-mm-dd
12
 lieunaiss varchar Not null Lieu de naissance de l’étudiant
Taille maximale=25
Genre char M : masculin Genre de l’étudiant
F : féminin
Taille=1
idb intege Clé primaire Clé primaire qui s’auto incrémente à chaque
nouvel enregistrement
nomb varchar Not null Nom de la bourse (exemple : nationale, belge,
Taille maximale=150 coopération canadienne)
mnt decimal Not null Montant de la bouse
Format decimal(7,3)
nbr int Not null Le nombre des bourses octroyées
dated date Not null Date de début de la bourse
format yyyy-mm-dd
datefin date Not null Date à laquelle la bourse prend fin
format yyyy-mm-dd
diplm varchar Not null Le nom du diplôme avec lequel l’étudiant
Taille maximale=50 obtient la bourse
diplr varchar Not null Le diplôme que l’étudiant peut avoir à la fin de
Taille maximale=50 la formation
univ varchar Not null L’université d’accueil après avoir eu la bourse
Taille maximale=50
pays varchar Not null Le pays où se trouve l’université où l’étudiant
Taille maximale=25 va étudier

b) Le Modèle Conceptuel de Données(MCD)

c) Modèle Logique de Données

ETUDIANT(ide, nom, pren, datenaiss, lieunaiss)
BOURSE(idb, nomb, mnt, nbr, dated, datefin)
OBTENIR(ido, #ide, #idb, diplm, diplr, univ, pays)

Création de la table « ETUDIANT »

CREATE TABLE ETUDIANT (ide int primary key, nom varchar(50) NOT NULL, pren varchar(50) NOT NULL,
datenaiss varchar(10) NOT NULL, lieunaiss varchar(50) NOT NULL, genre char(1) CONSTRAINT ck_genre
CHECK(genre IN(‘F’,’M’))

Création de la table « BOURSE »

create table BOURSE(idb int primary key, nomb varchar(25) not null, mnt int(11) not null, nbr int(5) not null,
dateb date not null, datefin date not null
)
Création de la table « OBTENIR »
Create table OBTENIR (
Ido int primary key, ide foreign key fk_etud references etudiant(ide), idb foreign key fk_brse references
bourse(idb), Univ varchar(50) NOT NULL , pays varchar(25) NOT NULL DEFAULT ‘Niger’,diplr varchar(50),
diplm varchar(50) NOT NULL
13
 )

Table ETUDIANT
ide Nom pren datenaiss lieunaiss
1 Ali Iro 1987-02-12 Goure
2 Oussou Mani 1984-08-17 Gaya
3 Ide Kalla 1991-05-21 Bilma
4 Sani Moudi 1995-02-01 Tessaoua
5 Garba Hamza 1988-07-11 Timia
6 Hadi Hassia 1999-12-21 Ngal
7 Issa Noura 1992-02-26 Madaoua
8 Balki Moussa 1990-12-05 Bilma
9 Ibra Sani 1995-11-02 Torodi
10 Nadia Haro 1982-02-06 Birni Konni
11 Saadatou Baba 1985-05-05 Maradi
12 Dan Tanin Kaka 1979-11-23 Timia
13 Bermo Habsou 1987-04-07 Bouza
14 Amina Sala 1988-08-05 Magaria

Table BOURSE
idb nomb Mnt Nbr dated datefin
1 Co Fr 150000 52 2015-12-11 2018-12-11
2 Co Blg 25000 102 2015-01-10 2018-11-02
3 Alg 74500 79 2014-10-01 2018-09-24
4 Maroc 53200 42 2013-12-01 2018-08-16
5 Ng1 35000 2500 2014-05-15 2018-07-08
6 Ng2 75000 1500 2015-01-08 2018-05-30
7 Ng3 125000 500 2013-08-09 2018-04-21
8 Chi 132050 13 2015-11-08 2018-03-13
9 Can 257500 10 2015-12-01 2019-01-15
10 Lux 258600 5 2015-12-24 2019-11-19
11 Turk 45900 16 2016-01-16 2018-01-03
12 Ind 35200 25 2016-02-08 2019-02-25
13 Ngria 38200 200 2016-03-02 2020-04-18
14 Suiss 320000 12 2016-03-25 2021-06-10

Table OBTENIR
ido idb ide diplm diplr univ pays
1 1 1 Licence Master UCAD Senegal
2 2 2 Licence Master UNICAN Niger
3 7 3 BTS Master UAC Benin
4 5 4 Bac C Licence Ouaga1 Burkina
5 12 5 Bac A Licence UAM Niger
6 11 6 Bac A8 Licence UAM Niger
7 2 7 Licence Master UAM Niger
8 3 8 Bac D Licence Libre Tunisie
9 8 9 Bac D Licence UAM Niger
10 6 10 Bac E Licence Maradi Niger
11 4 11 Bac A Master Zinder Niger
12 4 12 Master Doctorat Tahoua Niger
13 12 13 Licence Master Agadez Niger
14 11 14 Bac A Licence Rabat Maroc

14
 15 12 15 Bac A Licence Alger Algerie

Insertion des données dans les tables

Insertion de données dans la table INSERT INTO etudiant(nom,pren,datenaiss,

« etudiant »(respect de l’ordre des colonnes, lieunaiss,genre)
expLicenceitement) VALUES (‘Ali’,’Iro’,’ 1987-02-12’,’Goure’, ’M’)
Insertion de données dans la table INSERT INTO etudiant VALUES (‘Ali’,’Iro’,’ 1987-02-
« etudiant »(respect de l’ordre des colonnes) 12’,’Goure’, ’M’)
Insertion de données dans la table « etudiant » INSERT INTO etudiant(datenaiss, genre,lieunaiss,
(Dans un ordre différent des colonnes) nom,pren)
VALUES (’ 1987-02-12 ’, ’M’, ’Goure’,‘Ali’,’Iro’)
Copier des données depuis/vers un fichier vers /depuis une table

Syntaxe :
COPY nom_table [ ( nom_colonne [, ...] ) ]
FROM { 'nom_fichier' | PROGRAM 'commande' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
-------------------------------------------------------//------------------------------------------------------------------------------
COPY { nom_table [ ( nom_colonne [, ...] ) ] | ( requête ) }
TO { 'nom_fichier' | PROGRAM 'commande' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]

- COPY transfère des données entre les tables de PostgreSQL et les fichiers du système de fichiers standard
- COPY TO copie le contenu d'une table vers un fichier
- COPY FROM copie des données depuis un fichier vers une table (ajoutant les données à celles déjà dans la
table).
- COPY TO peut aussi copier le résultat d'une requête SELECT
Remarque :
COPY ne peut être utilisé qu'avec des tables réelles, pas avec des vues.
Néanmoins, vous pouvez écrire COPY (SELECT * FROM nom_vue) TO ....

La commande COPY pour insérer les données dans COPY etudiant FROM ‘/home/iat/data.txt’
la table « etudiant » à partir du fichier data.txt
Copier l’ide, nom, prenom des étudiant dans le Copy (select ide, nom, pren FROM ETUDIANT) TO
fichier copier.txt ‘/home/iat/copier.txt
COPY TO copie le contenu d'une table vers un fichier tandis que COPY FROM copie des données depuis un
fichier vers une table (ajoutant les données à celles déjà dans la table). COPY TO peut aussi copier le résultat d'une
requête SELECT.
Si une liste de colonnes est précisée, COPY ne copie que les données des colonnes spécifiées vers ou depuis le
fichier. COPY FROM insère les valeurs par défaut des colonnes qui ne sont pas précisées dans la liste.
Sélectionner tous les étudiants SELECT * FROM etudiant
Sélectionner nom, prénom, genre des étudiants SELECT nom,pren,genre FROM etudiant
Rendre les colonnes plus compréhensibles à SELECT nom NOM,pren PRENOM,genre GENRE FROM
l’utilisateur etudiant
Sélectionner nom, prénom, genre des étudiants SELECT nom NOM,pren PRENOM,genre GENRE FROM
femmes etudiant WHERE genre=‘F’

Mise à jour des données

Mettre à jour le montant de la bourse du UPDATE bourse SET mnt=45000 WHERE nomb=‘Ng1’;
premier cycle du Niger à 45000
Mettre à jour le nom de l’Université d’accueil UPDATE obtenir SET univ=‘Ouaga2’ Where univ=‘Ouaga1’ ;
Ouaga1 du burkina à Ouaga2
Suppression

15
Supprimer l’étudiant Dan Tanin Kaka né le DELETE FROM etudiant WHRE nom=‘Dan Tanin’ AND
1979-11-23 pren=‘Kaka’ AND datenaiss=‘1979-11-23’;

Création de vue
Créer une vue qui affiche la liste des étudiants de CREATE OR REPLACE VIEW etudiantMaradi AS
l’Université de Maradi, Afficher le nom, prénom, SELECT nom,pren,datenaiss,lieunaiss, diplr FROM etudiant
date de naissance, lieu de naissance, diplôme à e, obtenir o WHERE e.ide=o.ide AND univ=‘Maradi’;
préparer
Créer une vue qui donne le montant total annuel de CREATE or REPLACE VIEW totalmntbouse AS
la bourse par nom de la bourse SELECT nomb , SUM(mnt*nbr*12) total
FROM bourse b, obtenir o WHERE b.idb=o.idb GROUP BY
nomb;

Requêtes
Sélectionner les étudiants qui sont nés à Goure SELECT * FROM etudiant WHERE lieunaiss=‘Goure’
Sélectionner les étudiants dont les noms SELECT * FROM etudiant WHERE nom LIKE ‘Ga%’
commencent par ‘Ga’
Sélectionner les 30 premiers enregistrements de la SELECT * from recyclage limit 30;
table recyclage
Sélectionner les30 premiers enregistrements de la SELECT * from etudiant limit 30;
table etudiant,
Faire une comparaison de résultat avec la table
etudiants
Exécuter cette requête et comparer le résultat SELECT * from ONLY etudiant limit 30;
Sélectionner les étudiants dont les prénoms se SELECT * FROM etudiant WHERE pren like ‘%lla’ AND
terminent par ‘lla’ et lieu de naissance commence lieunaiss LIKE ‘bil%’
par ‘bil’
Sélectionner le nom, prénom, date de naissance des SELECT nom,pren,datenaiss FROM etudiant WHERE
étudiants dont la date de naissance est comprise datenaiss BETWEEN ‘1984-08-01’ AND ‘1990-01-01’
entre ‘1984-08-01’ et ‘’1990-01-01’
Sélectionner le nombre des étudiants dont la date SELECT COUNT(*) FROM etudiant WHERE datenaiss
de naissance est comprise entre ‘1984-08-01’ et BETWEEN ‘1984-08-01’ AND ‘1990-01-01’
‘’1990-01-01’
Sélectionner les bourses dont le mont dépasse SELECT nomb FROM bourse WHRE mnt >150000
150000
Sélectionner le montant total de la bourse de SELECT SUM(mnt*nbr) MONTANT FROM bourse
coopération chinoise entre 01 janvier 2013 et 31 WHERE dated BETWEEN ‘2013-01-01’ AND ‘2015-12-31’
décembre 2015 AND nomb=‘chin’;
Sélectionner les différents pays d’études et les trier SELECT DISTINCT pays FROM obtenir ORDER BY pays
par ordre décroissant DESC
Sélectionner les étudiants (nom,pren,lieunaiss) qui SELECT nom,pren,lieunaiss FROM etudiant e, bourse b,
ont eu la bourse de coopération française obtenir o
WHERE e.ide=o.ide AND o.idb=b.idb AND nomb=‘Co
Fr’;
Utiliser les requêtes imbriquées pour le même SELECT nom,pren,lieunaiss FROM etudiant e
résultat WHERE e.ide = (SELECT o.ide FROM obtenir o, bourse b
WHERE nomb=‘Co Fr’ AND o.idb=b.idb)
Sélectionner les étudiants (nom,pren,lieunaiss) nés SELECT nom,pren,lieunaiss FROM etudiant e, bourse b
à Bilma qui ont eu la bourse nationale du premier WHERE e.ide=o.ide AND o.idb=b.idb AND nomb=‘Ng1’
cycle AND lieunaiss=‘Bilma’
Sélectionner les étudiants dont la bourse prend fin SELECT nom,pren,lieunaiss,datef FROM etudiant e, obtenir
au plus tard le 31 décembre 2015 o, bourse b
WHERE e.ide=o.ide AND o.idb=b.idb AND datefin
<=‘2015-12-31’
Sélectionner les étudiants qui ont eu la bourse pour SELECT nom,pren,datenaiss FROM etudiant e, obtenir o
16
étudier à l’UCAD WHERE e.ide=o.ide AND univ=‘UCAD’
Sélectionner les étudiants qui ont eu la bourse pour SELECT nom,pren,datenaiss FROM etudiant e, obtenir o
étudier à l’université de Maradi et l’université de WHERE e.ide=o.ide AND univ IN (‘’Maradi,’Tahoua’)
Tahoua et qui ont eu le Bac C AND diplm=‘Bac C’;
Sélectionner les étudiants qui vont étudier hors du SELECT nom NOM,pren PRENOM,datenaiss ‘DATE DE
Niger et qui doivent revenir avec le diplôme de NAISSANCE’ FROM etudiant e, obtenir o WHERE
Master e.ide=o.ide AND pays <>’Niger’ and diplr=‘Master’;
Sélectionner le montant total par bourse entre le SELECT SUM(mnt*nbr) TOTAL, nomb FROM bourse b,
2014-01-01 et 2015-12-31, montant total par mois obtenir o WHERE o.idb=b.idb AND dated BETWEEN
‘2014-01-01’ AND ‘2015-12-31’
GROUP BY nomb;
Sélectionner les donateurs extérieurs de la bourse SELECT nomb, SUM(mnt*nbr*12) total FROM etudiant e,
annuelle supérieure à 30900000 bourse b, obtenir o WHERE WHERE e.ide=o.ide AND
o.idb=b.idb AND nomb NOT IN (Ng1, Ng2, Ng3)
GROUP BY nomb
HAVING SUM(mnt*nbr*12) >30900000;

Function de fenêtrage

 Utiliser la Fonctions de fenêtrage pour SELECT nom, pren, SUM(mnt*12) OVER (PARTITION BY o.ide)
déterminer le montant total de la bourse FROM etudiant e, obtenir o, bourse b
par étudiant du second cycle par année WHERE e.ide=o.ide AND b.idb=o.idb AND nomb=‘Ng2’ ;
 Utiliser la Fonctions de fenêtrage pour SELECT nomb, mnt, rank() OVER (PARTITION BY nomb
déterminer le montant total de la bourse ORDER BY mnt DESC)
annuelle par nom de la bourse et donner FROM obtenir o, bourse b
aussi le rang en fonction du montant total WHRE b.idb=o.idb;

Ajout d’une colonne à une table ALTER TABLE etudiant ADD photo text;
Modifier une colonne ALTER TABLE etudiant ALTER COLUMN photo varchar(255);
Supprimer la colonne ALTER TABLE etudiant DROP COLUMN photo;
Ajouter une clé étrangère ALTER TABLE obtenir ADD FOREIGN KEY(idb) REFERENCES
bourse(idb);
------------------------------------ou-------------------------------------
ALTER TABLE obtenir ADD CONSTRAINT fk_brs FOREIGN KEY(idb)
REFERENCES bourse(idb);
Supprimer la clé étrangère ALTER TABLE obtenir DROP CONSTRAINT fk_etud;
Ajouter une contrainte CHECK ALTER TABLE bourse ADD CONSTRAINT ck_nbr CHECK(nbr >0);
Supprimer la contrainte CHECK ALTER TABLE bourse DROP CONSTRAINT ck_nbr;
Supprimer la table recyclage DROP TABLE recyclage;

TD numero 1
Dictionnaire de données

PROPRIETE TYPE et TAILLE CONTRAINTE DESCRIPTION

Idc Int(11) Identifiant du client
Nom Varchar(50) Not_null Nom du client
Prenom Varchar(50) Not_null Prénom du client
Tel Int(8) Not_null Numéro de téléphone du client
Residence Varchar(50) Not_null Le quartier de résidence du client

PROPRIETE TYPE et TAILLE CONTRAINTE DESCRIPTION

Idmdl Int(11) Identifiant du modèle
NumModel Int(11) Not_null Numéro du modèle
#idc Int(11) Identifiant du client

17
PROPRIETE TYPE et TAILLE CONTRAINTE DESCRIPTION
Idm Int(11) Identifiant de la mesure
Epaule Int(2) NOT NULL Mesure de l’épaule
Poitrine Int(3) NOT NULL Mesure de la poitrine
Bassin Int(3) Mesure du bassin
Hanche Int(3) Mesure de la hanche
Ceinture Int(3) Mesure de la ceinture
Cuisse Int(3) Mesure de la cuisse
Longueurchemise Int(3) NOT NULL Longueur de la chemise
Tourcou Int(3) NOT NULL Mesure du tourcou
Manche ENUM(‘1’ ,’0’) NOT NULL Mesure de la manche
tourManche Int(2) Mesure du tourmanche
MancheTroisquart Int(2) Mesure manche troisquart
Lgtroisquart Int(3) Longueur trois quart
LgPantJupe Int() Longueur du pantalon ou du jupe

PROPRIETE TYPE et CONTRAINTE DESCRIPTION

TAILLE
Idt Int(11) Identifiant de la mesure
Type Varchar(50) NOT NULL

PROPRIETE TYPE et CONTRAINTE DESCRIPTION

TAILLE
Jour Int(11) Le jour ou la mesure a était prise
Prix Int() Le prix de la couture
dateR Date La date du rendez-vous
heureR Time L’heure du rendez-vous
#idc Int(11)
#idm Int(11)

18
 CLIENT MESURE
idc idm
nom 1,N 1,N
POSSEDER epaule
prenom tourCoup
Jour
tel longeurChemise
Prix
residence manche(0,1)
1,N dateR
heureR poitrine
1,N bassin
hanche
REGLER
CHOISR ceinture
jourReg
cuisse
mnt
1,N lgPantJupe
1,N lgtroisquart
1,1
MODELE tourManche
Idmdl MancheTroisquart
NumModele COMMANDE 1,1
prix numCmd
montant APPARTENIR
1,1
dateCmd
PUBLIER
1,N
1,N
TYPE
COUTURIER idt
idcouture type
NomCouturier
telephone PERIODE
situation ipPeriode
email ABONNER
periode
jour
1,1 1,N montant

TD numéro 2
Gestion de transport des voyageurs et messagerie

19
Attribut Type ContrainTes Explication
idp NUMBER(11) PRIMARY KEY Clé primaire de l’entité PERSONNE.
titre CHAR(3) CHECK(titre La valeur doit être ‘M’ pour Monsieur ou ‘Mme’ pour
IN(‘Mme’,’Mr’) Madame
nom VARCHAR2(50) NOT NULL Le nom du client sur 50 caractères
prenom VARCHAR2(50) NOT NULL Le prénom du client sur 50 caractères
tel NUMBER(8) NOT NULL Le numéro de téléphone sur 8 chiffres
piece La pièce d’identité
idb NUMBER (11) PRIMARY KEY Clé primaire de l’entité BAGAGE.
nbrSacV NUMBER (3)
nbrValV NUMBER (3)
nbrSacGYH NUMBER (3)
nbrColis NUMBER (3)
idbus NUMBER (11) PRIMARY KEY Clé primaire de l’entité BUS.
numbus NUMBER (5) NOT NULL Le numéro du bus
clim NUMBER (1) NOT NULL 1 si le bus est climatisé, 0 sinon
nbrsiege NUMBER (3) NOT NULL Le nombre de sièges du bus
numticket CHAR(11) NOT NULL Le numéro du billet du voyage
Hdepart NUMBER (5) NOT NULL Heure de départ. Format hh :mm
vdepart VARCHAR2(50) NOT NULL La ville de départ
datedep date NOT NULL La date de départ
vdest VARCHAR2(50) NOT NULL La Ville de destination
mnt NUMBER (6) NOT NULL Le montant du voyage
Siege NUMBER (3) NOT NULL Le numéro du siège où un voyageur doit s’assoir
Type NUMBER (1) NOT NULL 0 pour national et 1 pour international c’est-à-dire à
l’extérieur du Niger
Idcol PRIMARY KEY Clé primaire de l’entité COLIS
Valeur La valeur du colis. C’est le montant remboursé au client
en cas de perte du colis
Montant NUMBER (6) NOT NULL Le montant donné par le client au service de messagerie
Decrire BLOB La description du colis

20
TD 3
WAYA.com est un opérateur de téléphonie mobile situé dans les nouveaux locaux de l’Ecole Supérieure des
Télécommunications.
Le service courrier de l’entreprise WAYA.com reçoit ou envoie des courriers aux partenaires. Dans l’ensemble il y a
deux catégories de courriers : Enveloppe et Colis. Et une fois les courriers enregistrés, Ils sont soumis à la Direction
concernée qui les transmet au service destinataire.
Langage de programmation : PHP ; Editeur : Sublime, SGBD : MySQL, Framework : Bootstrap
Voici le Modèle Conceptuel de Données(MCD)

Renseignements sur les attribues des entités

Attribut Contraint Type Explication

cat NOT NULL INT(2) Catégorie du courrier (Enveloppe ou Colis)
jour NOT NULL DATE Jour d’entrée ou de sortie du courrier
type NOT NULL INT(1) 0 pour l’entrée ; 1 pour la sortie
objet NOT NULL TEXT Objet du courrier
numero NOT NULL VARCHAR(20) Numéro du courrier
idd NOT NULL INT(2) Identifiant de la Direction
direction NOT NULL VARCHAR(80) Le nom de la Direction
idp PRIMARY INT Identifiant du partenaire
KEY
nom NOT NULL VARCHAR(50) Nom du partenaire
telp NOT NULL INT(8) Téléphone du partenaire
idservice PRIMARY INT Identifiant du service
KEY
service NOT NULL VARCHAR(50) Nom du service

TD 4
Exercice 2 (10 points) Gestion des comptes bancaires

Soit le schéma de base de données relationnel suivant :

AGENCE (numAgence, nomAgence, villeAgence, Actif)
CLIENT (numCompte, nomClient, prenomClient, dateNaiss villeClient,telClient,fermer)
MOUVEMENT(#numAgence, # numCompte, entree, sortie,typecompte, statut)
EMPRUNT (numEmprunt,#numAgence, #numCompte, montant, datedebut, datefin,tauxInteret)
REMBOURSEMENT(montantRembouse,dateRemb,# numEmprunt)
21
 Ecrire les requêtes suivantes en SQL :

1. Clients ayant fait des emprunts de plus de cinq ans

2. Clients ayant un compte et n’ayant pas fait d’emprunt
3. Clients ayant des comptes courants et n’ayant pas effectué de mouvements il y a plus de trois mois
4. Donner le Solde des comptes le solde dépassant “5000000”
 Solde=la somme des entrées moins la somme des sorties
5. Nombre de clients pour chacune des villes suivantes : “Niamey”, “Maradi”, “Agadez”
6. Lister les clients qui ont remboursé plus de 75% de leurs emprunts
7. Lister les clients n’ayant pas remboursé leurs emprunts
8. Donner la liste des clients dont la date limite de remboursement d’emprunt est dans 30 jours
9. Si la pénalité est de 1500Fcfa par jour, quel est montant de la pénalité à la date d’aujourd’hui pour
chaque client ayant fait des emprunts mais il n’a pas remboursé à temps?
10. Créer l’utilisateur ali avec le mot de passe de votre choix
11. Retirer le privilège de modification sur les colonnes ‘entree’ et ‘sortie’ de la table mouvement à
l’utilisateur ali
12. Créer le synonyme COMPTE de la table CLIENT ;
13. Créer le rôle CONTROLEUR
14. Attribuer les privilèges SELECT, CREATE SESSION au rôle CONTROLEUR
15. Attribuer le rôle CONTROLEUR à ali
16. Créer une transaction pour le virement d’une somme de 77500000 dans le compte 3578520
17. Apres avoir effectuer ce virement, créer un point de restauration nommé POINTvir3578520
18. Apres vérification, on a constat, le chef d’agence a confirmé le virement. Alors faire une confirmation
de cette transaction dans la base de données au point POINTvir3578520
19. Créer une séquence nommée SEQrembour qui commence par 1
20. Sélectionner les contraintes de la table MOUVEMENT

TD 5

Propriété Type Contraintes Explication

numAgence NUMBER PRIMARY KEY Numéro de de l’agence
nomAgence VARCHAR2(50) UNIQUE, NOT NULL Nom de l’agence de transfert d’argent
localite VARCHAR2(50) NOT NULL La situation géographique de l’agence
region VARCHAR2(25) NOT NULL La région ou se trouve l’agence
numRecu NUMBER Le numéro du reçu
nomExp VARCHAR2(50) NOT NULL Nom de l’expéditeur
prenomExp VARCHAR2(50) NOT NULL Prénom de l’expéditeur
telExp NUMBER(8) NOT NULL Téléphone de l’expéditeur
montant NUMBER >5000 Montant à envoyer
nomDest VARCHAR2(50) NOT NULL Nom du destinataire
prenomDest VARCHAR2(50) NOT NULL Prénom du destinataire
telDest NUMBER(8) NOT NULL Téléphone du destinataire
motPasse VARCHAR2(15) Mot de passe
pieceDest VARCHAR2(12) Obligatoire à la réception de Numéro de la pièce d’identité
l’argent
destination VARCHAR2(50) NOT NULL
codeEnvoi NUMBER PRIMARY KEY Code d’envoi
matAgent VARCHAR2(10) UNIQUE, NOT NULL Matricule de l’agent
nomAgent VARCHAR2(50) Nom de l’agent
prenomAgent VARCHAR2(50) Prénom de l’agent
compte VARCHAR2(25) UNIQUE, NOT NULL Compte de l’agent
motpasse VARCHAR2(15) NOT NULL Mot de passe de l’agent
profile NUMBER(1) DEFAULT 1 Si 0 alors administrateur, 1 pour agent
dateEnvoi TIMESTAMP NOT NULL, Date d’envoi
Format: ‘YYYY-MM-DD
22
 HH:MM:SS’
dateReception TIMESTAMP NOT NULL, Date de réception
Format: ‘YYYY-MM-DD
HH:MM:SS’
Type NUMBER(1) 1 si réception, 0 si envoi

AGENT
H
AGENCE matAgent
1,N POSSEDER 1,1 nomAgent
numAgence
nomAgence prenomAgent
localite compte
region motpasse
TRANSFERT
codeEnvoi 1,N
numRecu
nomExp
prenomExp 1,1
GERER
telExp
montant
nomDest
prenomDest
telDest
motPasse
pieceDest
destination
dateEnvoi
dateReception
type

TD 6
Exercice
23
Gestion des ventes sous PostgreSQL
Dictionnaires de données

Champ Description Type(taille) Contrainte/Format

idc Identifiant du client. Les valeurs s’auto SEQUENCE PRIMARY KEY
incrémentent
nom Nom du client VARCHAR(50) NOT NULL
tel Numéro de téléphone du client NUMERIC(8) NOT NULL, UNIQUE
ncom Numéro de la c commande SEQUENCE PRIMARY KEY
jour Jour de la commande TIMESTAMP CURRENT_TIMESTAMP
par défaut
montant Montant de la commande NUMERIC(8) NOT NULL
echeance La TIMESTAMP d’échéance de versement TIMESTAMP Jour<=echeance
de la somme restant
idp Identifiant du produit SEQUENCE PRIMARY KEY
prd Nom du produit VARCHAR(50) NOT NULL, UNIQUE
prix Prix de vente du produit NUMERIC(6) NOT NULL
idcat Identifiant de la catégorie du produit SEQUENCE PRIMARY KEY
categorie Le nom de la catégorie du produit VARCHAR(50) NOT NULL, UNIQUE
idlc Identifiant de la ligne de commande SEQUENCE PRIMARY KEY
qte Quantité du produit vendu NUMERIC(5) NOT NULL
idf Identifiant du fournisseur SEQUENCE PRIMARY KEY
nomf Nom du fournisseur VARCHAR(50) NOT NULL
telf Téléphone du fournissekur NUMERIC(8) NOT NULL, UNIQUE
nif Le NIF du fournisseur NUMERIC NOT NULL, UNIQUE
idl Identifiant de la livraison SEQUENCE PRIMARY KEY
jourl Jour de la livraison du produit TIMESTAMP NOT NULL
qtel Quantité du produit livré NUMERIC NOT NULL
prixl Prix a la livraison du produit NUMERIC(7) NOT NULL
idb Identifiant de bon de commande SEQUENCE PRIMARY KEY
joub Jour d’envoi du bon de commande chez le TIMESTAMP CURRENT_TIMESTAMP
fournisseur par défaut
qteb Quantité du produit définie dans le bon de NUMERIC(5) NOT NULL
commande
idv Identifiant du versement SEQUENCE PRIMARY KEY
jourv Jour de versement du montant restant de NUMERIC(7) NOT NULL
la commande
montantv Montant versé NUMERIC(7) NOT NULL
ids Identifiant de la succursale SEQUENCE PRIMARY KEY
succursale Le nom de la succursale VARCHAR(50) NOT NULL, UNIQUE
sitGeo Situation géographique de la succursale, VARCHAR(50) NOT NULL
coordonnées GPS
region Lg région où se trouve la succursale VARCHAR(50) NOT NULL

24
1) Donner les produits dont le prix dépasse 150
2) Le nombre de produits dont le prix dépasse 150
3) Donner les produits dont le prix est compris entre 50 et 150
4) Chercher le nombre de produit par tranche de prix :
[35-45]
[46-60]
[61-85]
[86-100]
[101-200]
[201-300]
[autre]
5) Donner les produits de la catégorie 1
6) Donner le nombre de commandes par mois en 2020
7) Donner le nombre de commandes par semaine au mois de janvier 2020
8) Donner le nombre de commandes par an
9) Donner le fournisseur dont le produit est le moins vendu en 2020
10) Donner les trois produits les plus livrés en 2021
11) Donner les cinq produits les moins livrés en 2021
12) Donner le pourcentage de nombre de produits vendus au deuxième trimestre de l’année 2020
13) Donner la liste des dix premiers clients ayant des numeros NigerTelecoms
14) Donner le nombre de produits par catégorie et trier le résultat par ordre croissant du nom de la
catégorie
15) Donner les commandes dont le montant total dépasse 1500 au mois de janvier 2017
16) Donner le chiffre d’affaires réalisé au dernier trimestre de l’année 2016
17) Donner les trois(3) produits les moins vendus au dernier trimestre de l’année 2016

25
 18) Donner les dix(10) produits les plus vendus au premier trimestre de l’année 2017
19) Donner la quantité restant en stock de chaque produit
20) Donner le client qui a généré le plus de chiffre d’affaires au premier trimestre de l’année 2017
21) Donner les détails de la commande qui a généré le plus grand chiffre d’affaires
22) Lister les produits qui vont périmer dans 30 jours
23) Déterminer le montant total des produits déjà périmés
24) Déterminer le chiffre d’affaire réalisé par chaque client au premier trimestre de l’année 2017
25) Calculer la remise pour chaque client : si le montant est >= 1000 alors faire une remise de 7% ; si le
montant >= 7000 et <10000 alors faire une remise de 4% , si le montant <7000 et >=5000 alors
faire une remise de 2%.
26) Calculer le montant TTC du chiffre d’affaires réalisé par chaque client sachant que TVA=19%
27) Afficher le montant TTC minimal et le montant maximal générés par chaque produit
28) Si le montant TTC est >= 10000 le client donne 70% du montant TTC et le reste est payé dans 30
jours et si le montant est compris entre 7000 et <10000 alors le client donne 85% du montant TTC
et le reste est payé dans 30 jours. Donner le reste à payer dans 30 jours
29) Déterminer le montant total restant à payer dans les 30 jours pour tous les clients
30) Déterminer le montant total restant à payer dans les 30 jours pour chaque client
31) A partir du résultat de la requête de la question numéro 6, déduire une vue nommée BUSINESS,
que vous importez les données dans EXCEL. Cela permettra aux utilisateurs d’avoir accès sur le
réseau, aux données dans un classeur EXCEL
32) Déterminer le montant TTC généré par chaque produit
33) L’entreprise souhaite faire une réduction de 5% au prochain achat, aux clients qui ont généré un
chiffre d’affaires de plus de 2000. Pour cela, déterminer la liste de ces clients à l’aide d’une vue
nommee VUEClient. Puis, écrire une requête qui faire une réduction de 5% aux clients qui sont
dans la vue VUEClient au prochain achat.
34) Donner le chiffre d’affaires généré par chaque succursale au premier trimestre de l’année 2017
35) Donner le pourcentage du Chiffre d’affaires généré par chaque succursale par rapport au montant
total HT
36) Déterminer la moyenne du montant TTC généré pour chaque produit
37) Déterminer le nombre d’achat réalisé par chaque client durant le mois de janvier 2017
Déterminer la quantité restant en stock par produit (somme de quantité livrée – somme de quantité
commandée)
38) Donner la liste des clients qui ont réglé plus de 70% de leurs dettes
39) Donner la liste des clients qui n’ont pas réglé leurs dettes trente(30) jours après la date de la
commande
40) Donner un bonus de 1000 à tout client ayant réglé sa dette avant la date prévue, c’est-à-dire 30 jours
après la date la commande
41) Donner la liste des produits qui n’ont pas été achetés depuis la date de leur livraison
42) Donner la liste des fournisseurs dont les produits n’ont pas été achetés depuis la date de leur
livraison
43) Créer une vue nommée AFFICHE70 qui afficher la liste des clients qui ont réglé plus de 70% de leurs
dettes
44) Une semaine après, le Directeur des Etudes vous demande de lui envoyer le script qui a permis à la
création de la vue AFFICHE70
45) Exporter la liste des produits qui vont périmer dans 30 jours dans une table nommée
‘peremption30jrs’

26
 Exercice
Gestion des vacations (13 points)

VACATAIRE MATIERE FILIERE

1,N 1N
idvac ENSEIGNER
codeMat Idf
nom nbreH
matiere filiere
prenom jour
tel tauxH
1,1 1N
pieceIdent

AVOIR
AFFECTER
1,N annee 1N 1,1
volumeH CYCLE
dateDebut APPARTENIR
idc
dateFin 1N
cycle
payer

COLONNE TYPE(TAILLE) CONTRAINTE DESCRIPTION

idvac NUMBER(5) PRIMARY KEY Identifiant du vacataire
nom VARCHAR2(50) NOT NULL Nom du vacataire
prenom VARCHAR2(50) NOT NULL Prénom du vacataire
tel NUMBER(8) NOT NULL, UNIQUE Téléphone du vacataire
pieceIdent VARCHAR2(15) NOT NULL, UNIQUE Pièce d’identité du vacataire
codeMat NUMBER(6) PRIMARY KEY Code de la matière
matiere VARCHAR2(50) NOT NULL Nom de la matière
idf NUMBER(5) PRIMARY KEY Identifiant de la filière
filiere VARCHAR2(50) NOT NULL, UNIQUE Nom de la filière
idc NUMBER(1) PRIMARY KEY Identifiant du cycle
cycle VARCHAR2(25) NOT NULL Nom du cycle
nbreH NUMBER(2) NOT NULL Nombre d’heures effectuées par le
vacataire à un jour donné
jour DATE NOT NULL ; jour <= sysdate Date à laquelle le vacataire a dispensé le
nombre d’heures qu’il a effectuées
tauxH NUMBER(5) NOT NULL ; tauxH>=2000 Les frais de vacations par heure
annee NUMBER(4) NOT NULL L’année à laquelle la matière est affectée
au vacataire
volumeH NUMBER(3) NOT NULL ; volumeH >=10 Le volume horaire que le vacataire
dispensera pour la matière qu’on lui a
affectée
dateDebut DATE NOT NULL ; dateDebut >= Date de début des cours de la matière
sysdate affectée au vacataire
dateFin DATE NOT NULL ; dateFin > Date de début des cours de la matière
dateDebut affectée au vacataire
payer INT(1) La valeur zéro par défaut Pour savoir si le vacataire est payé. Si la
valeur est 1 alors il est payé

Travail demandé :
I) Déduire le Modèle Logique de Données de la partie pointillée du MCD ci-dessus. Cette partie comprend les entités
VACATAIRE, MATIERE et FILIERE
27
II) Définition de données
1. Créer les tables CYCLE et FILIERE tout en respectant les contraintes d’intégrité
2. Créer la clef étrangère dans la table MATIERE
3. Créer la contrainte qui oblige la saisie d’une date de fin de vacation supérieure à la date de début de vacation
4. Créer un index sur la colonne ‘tel’ de la table VACATAIRE
5. Créer la contrainte UNIQUE sur la colonne ‘filiere’ de la table FILIERE
6. Supposons que vous avez oublié de créer la colonne ’ pieceIdent’ de la table VACATAIRE, donner une
instruction SQL qui permet de l’ajouter
7. Renommer la colonne ‘tel’ en ‘telephone‘ dans la table VACATAIRE
III) INTERROGATION DE DONNEES
1. Sélectionner les vacataires le nombre d'heures ont effectuées par chaque vacataire qui enseigne les matières
‘Algorithme’, ‘Base de donnees’, ‘Linux’, au premier trimestre de l’année 2017
2. Sélectionner les vacataires qui ont effectuée 100% du volume horaire au entre mars et mai 2017
3. Sélectionner les vacataires qui n'ont pas effectué 80% du mois de mai 2017 au 15 juillet 2017
4. Sélectionner le montant total à remettre à chaque vacataire entre au deuxième trimestre de l'année 2017
 Sachant que montant total= (somme de nombre d'heures effectuées) x taux horaire x 0.95
5. Donner le montant total des vacataires qui ont été payés entre juin et juillet 2017
IV) MANIPULATION DE DONNEES
1. Quelle est l’instruction SQL qui permet d’insérer les données suivantes dans la table cycle ?
idc cycle
1 Premier
2. Quelle est l’instruction qui permet de modifier la valeur ‘premier’ en ‘Second’ dans la table cycle ?
3. Quelle est l’instruction qui permet de supprimer l’enregistrement dont le idc=2 de la table cycle ?
4. Quelle instruction permet de rendre la table VACATAIRE accessible uniquement en lecture
5. Quelle instruction permet de créer la table SAUVEGARDE_VACATAIRE qui reçoit les vacataires qui ont été
payés entre avril et juillet 2017

28
Exercice

Quelques renseignements :
Champ Description Type [taille] Contrainte
numClient Numéro du client SERIAL PRIMARY KEY
nomC Nom du client VARCHAR(50) NOT NULL
tel Téléphone du client INTEGER NOT NULL
email L’adresse mail du client VARCHAR(60)
fax Faxe du client INTEGER
ville Ville du client VARCHAR(25)
pays Pays du client VARCHAR(25)
numCom Numéro de la commande BIGSERIAL PRIMARY KEY
etat Etat de la commande {‘En VARCHAR(12)
attente’|’Traitee’}
qteCom Quantité commandée INTEGER NOT NULL
prixU Prix unitaire MONEY NOT NULL
marque Marque du matériel VARCHAR(30) NOT NULL
numF Numero du fournisseur SMALLSERIAL PRIMARY KEY
nomF Nom du fournisseur VARCHAR(50) NOT NULL
telF Téléphone du fournisseur INTEGER NOT NULL
poids Le poids du matériel INTEGER NOT NULL
Pour la classe ORDINATEUR
ecran Pouce de l’écran (exple : 13,3 ") NUMERIC(2,2) NOT NULL
processeur Le type du processeur VARCHAR(15)
frequenceProc Fréquence du microprocesseur INTEGER
sysOS Système d’exploitation VARCHAR(25) NOT NULL
RAM La mémoire RAM INTEGER NOT NULL
espaceDisk Espace du disque dur INTEGER NOT NULL
Pour la classe IMPRIMANTE
nbrPageMin Nombre de page à imprimer par INTEGER NOT NULL

29
 minute
capBacPapier Nombre de papiers qu’on peut INTEGER NOT NULL
introduire dans le bac papiers
JetEncre Imprimante à jet d’encre. ‘O’ CHAR(1) NOT NULL
pour Oui et ‘N’ pour Non

Travail demandé :
I. DEFINITION DE DONNEES
1. Se connecter au serveur postgreSQL avec le compte postgres
a. connect –u postgres –p
b. psql –U postgres –W
c. sqlplus postgres/postgres
d. psql –u postgres –p
2. Créer l’utilisateur ‘adnane’ avec mot de passe crypté
a. create user adnane with encrypted password ‘#adnane #’
b. create user adnane with secret password ‘#adnane #’
c. create user adnane with md5 password ‘#adnane #’
d. create user adnane with password(‘#adnane #’)
3. Instruction de création de tablespace ‘informatique’ appartenant à l’utilisateur ‘ali’
a. create tablespace informatique OWNER ali LOCATION 'C:\vente2017';
b. create tablespace informatique FOR OWNER ali LOCATED 'C:\vente2017';
c. create tablespace informatique FOR OWNER ali IN 'C:\vente2017';
4. Ajouter ali dans le groupe des agents commerciaux AGENTCOM
a. GRANT INSERT ali TO AGENTCOM;
b. INSERT INTO AGENTCOM VALUES(ali);
c. ALTER GROUP AGENTCOM ADD USER ali ;
d. UPDATE AGENTCOM SET nom=ali ;
5. Créer la base de données ‘stock’ avec les paramètres suivants :
OWNER ben
TABLESPACE informatique
a. CREATE DATABASE stock to user(ben) WITH TABLESPACE informatique;
b. CREATE DATABASE stock WITH OWNER ben TABLESPACE informatique;
c. CREATE TABLESPACE informatique FOR DATABASE stock WITH OWNER ben;
6. Créer le schéma ‘business’
a. CREATE SCHEMA IF NOT EXISTS AUTHORIZATION business;
b. ALTER TABLESPACE business add schema if not exists
c. CREATE SCHEMA business IF NOT EXISTS WITH AUTHORIZATION
7. Créer le groupe stagiaire qui expirera le 31 décembre 2017
INSERT INTO POSTGRES.GROUP stagiaire WITH VALID UNTIL ‘2017-12-31’;
CREATE GROUP stagiaire WITH VALID UNTIL ‘2017-12-31’;
UPDATE POSTGRES.GROUP SET stagiaire WITH VALID UNTIL ‘2017-12-31’;
8. Définir l’utilisateur ‘postgres’ comme propriétaire du schéma ‘business’
a. ALTER SCHEMA business OWNER TO postgres;
b. UPDATE SCHEMA business OWNER TO postgres;
c. GRANT WITH OWNER ON SCHEMA business TO postgres;
d. GRANT CREATE SCHEMA TO postgres ON business;
9. L’instruction qui permet de créer la table ORDINATEUR qui hérite de la table ‘MATERIEL’
a. create table ORDINATEUR(ecran NUMERIC(2,2),processeur VARCHAR(15) ,frequenceProc ,sysOS
VARCHAR(15),RAM INTEGER,espaceDisk INTEGER) ON INHERITS(MATERIEL);
b. create table ORDINATEUR(ecran NUMERIC(2,2),processeur VARCHAR(15) ,frequenceProc ,sysOS
VARCHAR(15),RAM INTEGER,espaceDisk INTEGER) INHERITS(MATERIEL);

30
 c. create table ORDINATEUR(ecran NUMERIC(2,2),processeur VARCHAR(15) ,frequenceProc ,sysOS
VARCHAR(15),RAM INTEGER,espaceDisk INTEGER) WITH INHERITS(MATERIEL);
10. Renommer la colonne ‘telF’ en ‘telephone’ dans la table FOURNISSEUR
a. alter table FOURNISSEUR rename telF TO telephone;
b. ALTER TABLE FOURNISSEUR MODIFY telF TO telephone;
c. UPDATE FOURNISSEUR SET telF TO telephone;
d. alter table FOURNISSEUR CHANGE telF TO telephone;

11. Définir le Niger comme pays par défaut dans la table ‘CLIENT’
a. UPDATE table CLIENT SET DEFAULT pays=’Niger’
b. ALTER CLIENT UPDATE pays SET DEFAULT ‘Niger’
c. alter table CLIENT alter pays SET DEFAULT ‘Niger’

12. Créer l’index idxTel sur le champ tel de la table CLIENT

ALTER TABLE CLIENT ADD INDEX idxTel ;
CREATE INDEX idxTel ON CLIENT(tel) ;
INSERT INTO CLIENT (tel=’ idxTel’)
II. INTERROGATION DE DONNEES
1. Ecrire une requête qui catégorise chaque client en fonction du chiffre d’affaires qu’il a généré au deuxième
trimestre de l’année 2017:
Catégorie Tranche
DIAMANT >= 4500000
OR < 4500000 et >=2800000
ARGENT <2800000 et >=1000000
2. Lister les commandes (numCom,montantHT) qui sont en attente sachant que montantHT=SUM(qteCom x
prixU - qteCom x prixU x 0.19)
3. Donner le chiffre d’affaires Toute Taxe comprise, généré par les imprimantes au dernier trimestre de
l’année 2017 sachant que montantHT=SUM(qteCom x prixUx0.81)
4. Créer une vue qui permet de calculer la quantité restant des imprimantes au dernier trimestre de l’année
2017
5. Corriger si possible l’écriture de la fonction:
a. Ajouter qui insère les fournisseurs dans la table FOURNISSEUR

create or replace function inserer(nomF,telF)

returns integer
$$
INSERT INTO FOURNISSEUR(nom,prenom) VALUES (nomF,telF)
returning id;
$$
language 'sql' volatile;
b. afficherTOUT qui liste tous les matériels de la table MATERIEL
create or replace function afficherTOUT(id integer)
returns table(marque VARCHAR,prixU MONEY,poids INTEGER,couleur VARCHAR) as
$$
begin
return query
SELECT * FROM MATERIEL;
end;
$$
language 'plpgsql' stable;

III. CONTROLE DE DONNES

1. Donner les privilèges suivants a l’utilisateur allassan sur la table MATERIEL :
Utilisateur Objet Privilèges
31
 allassan MATERIEL INSERT, UPDATE, DELETE

IV. TRANSACTION
1. Ecrire une transaction pour la réduction du montant HT des achats effectués par chaque client au dernier
trimestre de l’année 2017 à condition qu’il génère plus de 500000FCFA.
V. SAUVEGARDE
1. Faire la copie de la base de donnees VENTE dans le fichier sauvegardeDB.txt en utilisant le compte ali
a. pg_dump -U ali -W -F t VENTE > C:\backup\ sauvegardeDB.txt
b. pg_admin -U ali -W -F t VENTE > C:\backup\ sauvegardeDB.txt
c. pg_save -U ali -W -F t VENTE > C:\backup\ sauvegardeDB.txt
2. sauvegarder la base de données vente depuis le serveur distant 192.168.16.54 en la compressant en
vente.tar.gz
a. pg_dump -U postgres -h 192.168.16.54 -F c –f vente.tar.gz vente
b. psql -U postgres -h 192.168.16.54 -F c –f vente.tar.gz vente
c. pg_admin -U postgres -h 192.168.16.54 -F c –f vente.tar.gz vente
d. pg_save -U postgres -h 192.168.16.54 -F c –f vente.tar.gz vente
e. pg_backup -U postgres -h 192.168.16.54 -F c –f vente.tar.gz vente
3. Ecrire une instruction SQL qui permet de créer une table ‘commandeClone’ qui reçoit toutes les données
de la table COMMADE

Exercice
Objectif : créer une base de données de gestion des incarcérations dans les prisons civiles

Champ Description Type(Taille) Contrainte/ format

idPrisonnier Identifiant du prisonnier NUMBER PRIMARY KEY, AUTO_INCREMENT
nom Nom du prisonnier VARCHAR2(50) NOT NULL
prenom Prénom du prisonnier VARCHAR2(50) NOT NULL
datenaiss Date de naissance du prisonnier DATE NOT NULL
lieunaiss Lieu de naissance du prisonnier VARCHAR2(50) NOT NULL
nationalite Nationalité du prisonnier VARCHAR2(50) NOT NULL
Genre du prisonnier CHAR(1) NOT NULL, CHECK(gente IN(‘M’ ou
genre
‘F’))
photo Photo du prisonnier TEXT
idIncarserer Identifiant d’incarcération NUMBER PRIMARY KEY, AUTO_INCREMENT
Date d’incarcération du DATE NOT NULL
jourIncarseration
prisonnier
jourJugement Date de jugement du prisonnier DATE
jourLiberte Date de libération du prisonnier DATE
idPrison Identifiant de la prison NUMBER PRIMARY KEY, AUTO_INCREMENT
nomPrison Nom de la prison VARCHAR2(50) NOT NULL
Situation géographique de la VARCHAR2(50) NOT NULL
situationGeographique
prison
region La région où se trouve la prison VARCHAR2(9) NOT NULL
idDelit Identifiant de la prison NUMBER PRIMARY KEY, AUTO_INCREMENT
Le délit commis par le VARCHAR2(50) NOT NULL
delit
prisonnier
La peine que doit subir le VARCHAR2(100) NOT NULL
peine
prisonnier

32
Soit le Modèle Physique de Données suivant :
PRISONNIER INCARSERER
idPrisonnier PRISON
idIncarserer idPrison
nom jourIncarseration
prenom nomPrison
jourJugement situationGeographique
datenaiss jourLiberte
lieunaiss region
#idPrisonnier
nationalite #idPrison DELIT
genre #idDelit idDelit
photo delit
peine
Travail demandé : (sur Ordinateur)
A. Langage de Définition et Manipulation de Données (sous Oracle ) (7 points)
1. Créer la table INCARSERER et toutes ses contraintes

2. Donner l’instruction qui permet de modifier le type de données de la colonne peine de la table DELIT, en
TEXT

3. Donner l’instruction qui permet de charger les données du fichier incarseration.csv dans la table
INCARSERER
4. Donner l’instruction qui permet d’exporter le contenu de la table INCARSERER, dans un fichier
backupINCARS.csv
5. Modifier la date de libération des prisonniers au 31 décembre 2025, pour tout prisonnier dont l’âge
dépasse 75 ans
6. Supprimer les prisonniers dont la date d’incarcération est inférieure ou égale au 3 Aout 1970
7. Créer une table nommée BACKUPINCARSERATION qui récit toutes les données de la table INCARSERER
B. Langage d’Interrogation de Données (choisir 7 questions parmi les 11) (7.5 points)
8. Donner le nombre de prisonniers par an, de 2018 à 2022
9. Donner le nombre de prisonniers par mois dans la prison de Koutoukale, au premier trimestre de l’année
2023
10. Donner le nombre de prisonniers qui ne sont pas jugés dans la prison de Diffa, au dernier trimestre 2021
11. Donner la liste des prisonniers terroristes (dont le délit est le terrorisme) qui sont détenus dans la prison
civile de Filingue
12. Donner le nombre de prisonniers par tranche d’âge dans la région de Maradi : [18-25], [26-36],[37-50],[51-
65]
13. Donner le nombre de prisonnières dans chaque prison civile et trier le résultat par ordre décroissant du
nombre
14. Donner le pourcentage de prisonniers par genre dans la maison d’arrêt de Niamey
15. Donner le pourcentage de prisonniers libérés par année, de 2019 à 2022
16. Donner la région où il y a plus de prisonniers
17. Donner le nombre de prisonniers par peine, au dernier trimestre de l’année 2022
18. Donner la liste de prisonnier, incarcérés plus de 3 fois, dans la prison de Madaoua, région de Tahoua, entre
2022 et 2023

C. Réplication de données : assurer la disponibilité de données (2.5 points)

19. Ecrire un script qui permet de migrer les données de la table INCARSERER des serveurs Oracle vers le
serveur distant PostgreSQL
20. Ecrire un script Python qui qui répliquer les données du serveur MySQL vers le serveur distant Oracle
172.16.16.17

Reseau: 172.16.16.16/28
Oracle Oracle
33
 PostgreSQL
.17
.19
.18

.20
MySQL

D. Langage de Contrôle de Données (3 points)

21. Sous PostgreSQL, créer l’utilisateur madougou et lui attribuer les privilèges sur les tables
USER PRIVILEGES TABLES
madougou SELECT, UPDATE, INSERT, DELETE INCARSERER, PRISONNIER
22. Sous PostgreSQL, retirer le privilège DELETE à l’utilisateur madougou
23. Sous Oracle, créer les utilisateurs suivants :
Utilisateur Options
PASSWORD DEFAULT QUOTA ACCOUNT PASSWORD
TABLESPACE
halimatou dEpArTmEnT SYSTEM 300M UNLOCK expire
24. Sous Oracle, créer les rôles suivants :
role Mot de passe Privileges systeme
comdivision iNsPeCtOr23 CREATE ANY TABLE, CREATE PROFILE,CONNECT, CREATE
ANY USER,
25. Sous Oracle, attribuer le rôle comdivision à l’utilisateur halimatou
26. Sous Oracle, créer le profiles suivant :
Profile FAILED_LO PASSWORD_L PASSWORD_RE PASSWORD_RE PASSWORD_
GIN_ATTEM IFE_TIME USE_TIME USE_MAX | LOCK_TIME
PTS
controPrison 3 7 8 UNLIMITED 4
27. Attribuer le profile controPrison à l’utilisateur halimatou

Sous PostgreSQL

create table prisonnier (

idPrisonnier serial primary key ,
nom varchar(50) not null,
prenom varchar(50) not null,
datenaiss date not null,
lieunaiss varchar(50) not null,
nationalite varchar(25) not null,
genre char(1) default 'M',
photo varchar(150) not null
);

alter table prisonnier add constraint ckgenre check(genre in('M','F'));

alter table prisonnier alter nationalite default 'nigerienne';
create table prison (
idPrison serial primary key ,
nomPrison varchar(25) not null,
34
 situationGeographique text,
region varchar(50) not null
);

alter table prison add constraint uqnomprison unique(nomPrison);

create table delit(
idDelit serial primary key ,
delit varchar(50) not null,
peine varchar(100) not null
);

alter table delit add constraint uqdelit unique(delit);

create table incarserer(

idIncarserer serial primary key ,
jourIncarseration date default current_date,
jourJugement date,
jourLiberte date,
idPrisonnier int not null,
idPrison int not null,
idDelit int not null
);

alter table incarserer add constraint

fkidPrisonnier foreign key(idPrisonnier) references prisonnier(idPrisonnier);

alter table incarserer add constraint fkidPrison foreign key(idPrison)

references prison(idPrison);

alter table incarserer add constraint fkiddelit foreign key(idDelit)

references delit(idDelit);

Sous MySQL

create table prisonnier (

idPrisonnier int primary key auto_increment,
nom varchar(50) not null,
prenom varchar(50) not null,
datenaiss date not null,
lieunaiss varchar(50) not null,
nationalite varchar(25) not null,
genre char(1) default 'M',
photo varchar(150) not null
);

alter table prisonnier add constraint ckgenre check(genre in('M','F'));

alter table prisonnier alter nationalite default 'nigerienne';

create table prison (

idPrison int primary key auto_increment,
nomPrison varchar(25) not null,
35
 situationGeographique text,
region varchar(50) not null
);

alter table prison add constraint uqnomprison unique(nomPrison);

create table delit(
idDelit int primary key auto_increment,
delit varchar(50) not null,
peine varchar(100) not null
);

alter table delit add constraint uqdelit unique(delit);

create table incarserer(

idIncarserer int primary key auto_increment,
jourIncarseration date default current_date(),
jourJugement date,
jourLiberte date,
idPrisonnier int not null,
idPrison int not null,
idDelit int not null
);

alter table incarserer add constraint

fkidPrisonnier foreign key(idPrisonnier) references prisonnier(idPrisonnier);

alter table incarserer add constraint fkidPrison foreign key(idPrison)

references prison(idPrison);

alter table incarserer add constraint fkiddelit foreign key(idDelit)

references delit(idDelit);

sous Oracle

create table prisonnier (

idPrisonnier number primary key,
nom varchar2(50) not null,
prenom varchar2(50) not null,
datenaiss date not null,
lieunaiss varchar2(50) not null,
nationalite varchar2(25) not null,
genre char(1) default 'M',
photo varchar2(150) not null
);

alter table prisonnier add constraint ckgenre check(genre in('M','F'));

alter table prisonnier alter nationalite default 'nigerienne';

create table prison (

idPrison number primary key,
nomPrison varchar2(25) not null,
36
 situationGeographique text,
region varchar2(50) not null
);

alter table prison add constraint uqnomprison unique(nomPrison);

create table delit(
idDelit number primary key,
delit varchar2(50) not null,
peine varchar2(100) not null
);

alter table delit add constraint uqdelit unique(delit);

create table incarserer(

idIncarserer number primary key,
jourIncarseration date default current_date,
jourJugement date,
jourLiberte date,
idPrisonnier number not null,
idPrison number not null,
idDelit number not null
);

alter table incarserer add constraint

fkidPrisonnier foreign key(idPrisonnier) references prisonnier(idPrisonnier);

alter table incarserer add constraint fkidPrison foreign key(idPrison)

references prison(idPrison);

alter table incarserer add constraint fkiddelit foreign key(idDelit);

references delit(idDelit);

2)
-- Oracle

alter table delit modify peine text;

-- Postgres
alter table delit alter peine type text;

---Mysql
alter table delit change peine text;

---3)
--**** oracle
-- a. creer le fichier qui controle le chargement de donnees C:\db\charger.txt
load data infile 'C:\db\incarseration.csv' into table incarserer fields terminated by
';'(idIncarserer,jourIncarseration,jourJugement,jourLiberte,idPrisonnier,idPrison,idDelit
)

--sur la console du système et saisir la commande:

sqllrd system/epn control=C:\db\charger.txt
37
--**** PostgreSQL
--entrer dans la console du SGBD PostgreSQL et saisir la commande:
copy incarserer from '/db/incarseration.csv' fields terminated with ';';
--*** MySQL
--- entrer dans la console du SGBD MySQL et saisir la commande:
load data infile 'C:\db\incarseration.csv' into table incarserer fields terminated by ';'

--4)
-- PostgreSQL
copy incarserer to '/db/backupINCARS.csv' fields terminated with ';';
---MySQL/Oracle
--entrer dans la console du SGBD Mysql et saisir la commande:
select * from incarserer --output-file='/db/backupINCARS.csv';

--5
--Oracle
update incarserer set jourLiberte='2025-12-31' where
idPrisonnier in(select idPrisonnier from prisonnier where (jourLiberte>'2025-12-31' OR
jourLiberte='')
and (sysdate - to_date(datenaiss,'YYYY-MM-DD'))/365>75)
--- PostgreSQL
update incarserer set jourLiberte='2025-12-31' where
idPrisonnier in(select idPrisonnier from prisonnier where (jourLiberte>'2025-12-31' OR
jourLiberte='')
and (current_date - datenaiss)/365>75)
--- MySQL
update incarserer set jourLiberte='2025-12-31' where
idPrisonnier in(select idPrisonnier from prisonnier where (jourLiberte>'2025-12-31' OR
jourLiberte='')
and timestampdiff(year,datenaiss,current_date())>75);

--6.
---PostgreSQL
delete from incarserer where jourIncarseration<=date '1970-08-03';
--- MySQL
delete from incarserer where jourIncarseration<='1970-08-03';
--- Oracle
delete from incarserer where jourIncarseration<=to_date('1970-08-03','YYYY-MM-DD');
---7
create table BACKUPINCARSERATION as select * from incarserer;
-- 8
select count(*) nbre, extract(year from jourIncarseration) annee
from incarserer
group by extract(year from jourIncarseration);
--9
select count(*) nbre, extract(month from jourIncarseration) mois
from incarserer I,prison P
where I.idPrison=P.idPrison AND extract(year from jourIncarseration)=2023 and
nomPrison='Koutoukale'
and extract(month from jourIncarseration) between 1 and 3
group by extract(month from jourIncarseration);
--10.
38
select count(*) nbre
from incarserer I,prison P
where I.idPrison=P.idPrison AND extract(year from jourIncarseration)=2021 and
nomPrison='Diffa'
and extract(month from jourIncarseration) between 10 and 12 and jourJugement='';
--11
select nom,prenom,datenaiss,lieunaiss,photo
from incarserer I,prison P,prisonnier Pr, delit D
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and D.idDelit=I.idDelit
AND nomPrison='Filingue' and delit like '%terriorisme%';

--12.
select count(*) nbre, case
when timestampdiff(year,datenaiss,current_date()) between 18 and 25 then '[18-25]'
when timestampdiff(year,datenaiss,current_date()) between 26 and 36 then '[26-36]'
when timestampdiff(year,datenaiss,current_date()) between 37 and 50 then '[37-50]'
when timestampdiff(year,datenaiss,current_date()) between 51 and 65 then '[51-65]'
end tranche
from incarserer I,prison P,prisonnier Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and region='Maradi'
group by case
when timestampdiff(year,datenaiss,current_date()) between 18 and 25 then '[18-25]'
when timestampdiff(year,datenaiss,current_date()) between 26 and 36 then '[26-36]'
when timestampdiff(year,datenaiss,current_date()) between 37 and 50 then '[37-50]'
when timestampdiff(year,datenaiss,current_date()) between 51 and 65 then '[51-65]'
end;
---Postgres

select count(*) nbre, case

when (current_date - datenaiss)/365 between 18 and 25 then '[18-25]'
when (current_date - datenaiss)/365 between 26 and 36 then '[26-36]'
when (current_date - datenaiss)/365 between 37 and 50 then '[37-50]'
when (current_date - datenaiss)/365 between 51 and 65 then '[51-65]'
end tranche
from incarserer I,prison P,prisonnier Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and region='Maradi'
group by case
when (current_date - datenaiss)/365 between 18 and 25 then '[18-25]'
when (current_date - datenaiss)/365 between 26 and 36 then '[26-36]'
when (current_date - datenaiss)/365 between 37 and 50 then '[37-50]'
when (current_date - datenaiss)/365 between 51 and 65 then '[51-65]'
end;
--ou
select count(*) nbre, case
when (current_date - datenaiss)/365 between 18 and 25 then '[18-25]'
when (current_date - datenaiss)/365 between 26 and 36 then '[26-36]'
when (current_date - datenaiss)/365 between 37 and 50 then '[37-50]'
when (current_date - datenaiss)/365 between 51 and 65 then '[51-65]'
end tranche
from incarserer I,prison P,prisonnier Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and region='Maradi'

39
group by 2;
---Oracle
select count(*) nbre, case
when (sysdate - datenaiss)/365 between 18 and 25 then '[18-25]'
when (sysdate - datenaiss)/365 between 26 and 36 then '[26-36]'
when (sysdate - datenaiss)/365 between 37 and 50 then '[37-50]'
when (sysdate - datenaiss)/365 between 51 and 65 then '[51-65]'
end tranche
from incarserer I,prison P,prisonnier Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and region='Maradi'
group by 2;

--13
---Pour Oracle, Postgres et MySQL
select count(*) nbre, nomPrison
from prison P,incarserer I, prisonnier as Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and jourLiberte='' and
genre='F'
group by nomPrison order by count(*) nbre DESC;

-- pour MySQL
select count(*) nbre, nomPrison
from prison P,incarserer I, prisonnier as Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and jourLiberte='' and
genre='F'
group by nomPrison order by nbre desc;

-- pour PostgreSQL
select count(*) nbre, nomPrison
from prison P,incarserer I, prisonnier as Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and jourLiberte='' and
genre='F'
group by 2 order by 2 desc;

-- pour oracle et PostgreSQL

select count(*) nbre, nomPrison
from prison P,incarserer I, prisonnier as Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and jourLiberte='' and
genre='F'
group by nomPrison order by 2 desc;

--14.
create view PrcprisonnierNy as
select genre, count(*) nbre
from prison P,incarserer I, prisonnier as Pr
where I.idPrison=P.idPrison and Pr.idPrisonnier=I.idPrisonnier and jourLiberte=''
and nomPrison='Maison arret' and region='Niamey'
group by genre;

--calcul du pourcentage
select genre,round(100*nbre/(select sum(nbre) from PrcprisonnierNy),2) as pourcentage
from PrcprisonnierNy;
40
---15.
--- création de la vue qui retourne le nombre de prisonniers incarseres
create view nbrePrisonnierAnnee as
select count(*) nbre, extract(year from jourIncarseration) annee
from incarserer where extract(year from jourIncarseration) between 2019 and 2022
group by extract(year from jourIncarseration);

-- création de la vue de ceux qui sont liberes par annee

create view nbrePrLibrAnnee as
select count(*) nbre, extract(year from jourIncarseration) annee
from incarserer where extract(year from jourIncarseration) between 2019 and 2022 and
jourLiberte !=''
group by extract(year from jourIncarseration);
----
select L.annee, round(100*L.nbre/I.nbre,2) as prc
from nbrePrisonnierAnnee I, nbrePrLibrAnnee L
where L.annee=I.annee;
--16.
--- MySQL et PostgreSQL
select region,count(*) nbre
from I.idPrison=P.idPrison and jourLiberte !=''
group by region order by nbre desc limit 1;

----Oracle
select region,count(*) nbre
from I.idPrison=P.idPrison and jourLiberte !=''
group by region order by nbre desc
fetch first 1 rows only;

----17.
select peine,count(*) nbre
from incarserer I, delit D
where I.idDelit=D.idDelit and extract(year from jourIncarseration)=2022 and jourLiberte
!=''
and extract(month from jourIncarseration) between 10 and 12
group by peine order by nbre desc ;
---18.
select P.idPrisonnier,nom,prenom,datenaiss,lieunaiss, count(*) nbre
from prisonnier P, incarserer I, prison as Pr
where P.idPrisonnier=I.idPrisonnier and Pr.idPrison=I.idPrison and
extract(year from jourIncarseration) between 2022 and 2023
and nomPrison='Madaoua' and region='Tahoua'
group by P.idPrisonnier
having count(*)>3;

Pour repondre aux requetes, nous utilisons les containers Docker sur le serveur 192.168.56.110
#19. Ecrire un script qui permet de migrer les données de la table INCARSERER des
serveurs Oracle vers le serveur distant PostgreSQL

41
#19. Ecrire un script qui permet de migrer les données de la table INCARSERER des
serveurs Oracle vers le serveur distant PostgreSQL
import pymysql as My, psycopg2 as Pg, oracledb as Ora
from sshtunnel import SSHTunnelForwarder as SSHT
class Acces:
def distant(self,u:str,h:str,p:str,pr:int):
self.server=SSHT((h,22), ssh_username=u, ssh_password=p,
remote_bind_address=('localhost',pr))
self.server.start()
return self.server,self.server.local_bind_port
def locale(self,u:str,bd:str,p:str,pr:int,X):
if X in (My,Pg):
self.conn=X.connect(host='localhost', user=u, password=p,
database=bd,port=pr)
elif X == Ora:
self.conn = X.connect(user=u,password=p,dsn=(f'localhost:{pr}/{bd}'))
return self.conn,self.conn.cursor()
A=Acces()
cnfp,prp=A.distant('gns3','192.168.56.110','gns3',5432)
cnlp,crlp = A.locale('postgres','prison','ben',prp,Pg)

cnfo,pro=A.distant('gns3','192.168.56.110','gns3',1521)
cnlo,crlo = A.locale('system','free','ben',pro,Ora)

cnfo2,pro2=A.distant('gns3','192.168.56.110','gns3',1522)
cnlo2,crlo2 = A.locale('system','free','ben',pro2,Ora)
#-------------------Requetes
for x in (crlo,crlo2):
x.execute("SELECT * FROM incaserer")
for l in x.fetchall():
crlp.execute('''INSERT INTO
incarser(jourIncarseration,jourJugement,jourLiberte,idPrisonnier,idPrison,idDelit)
VALUES('%s','%s','%s',%s,%s,%s)'''%(l[1],l[2],l[3],l[4],l[5],l[6]))
cnlp.commit()

cnfo.close()
cnlp.close()
crlp.close()
cnlo2.close()
crlo2.close()

20. Ecrire un script Python qui qui répliquer les données du serveur MySQL vers le
serveur distant Oracle 172.16.16.17

import pymysql as My, oracledb as Ora

from sshtunnel import SSHTunnelForwarder as SSHT
class Acces:
def distant(self,u:str,h:str,p:str,pr:int):
self.server=SSHT((h,22), ssh_username=u, ssh_password=p,
remote_bind_address=('localhost',pr))
self.server.start()

42
 return self.server,self.server.local_bind_port
def locale(self,u:str,bd:str,p:str,pr:int,X):
if X == My:
self.conn=X.connect(host='localhost', user=u, password=p,
database=bd,port=pr)
elif X == Ora:
self.conn = X.connect(user=u,password=p,dsn=(f'localhost:{pr}/{bd}'))
return self.conn,self.conn.cursor()
A=Acces()
cnfm,prm=A.distant('gns3','192.168.56.110','gns3',3306)
cnlm,crlm = A.locale('root','prison','ben',prm,My)

cnfo,pro=A.distant('gns3','192.168.56.110','gns3',1521)
cnlo,crlo = A.locale('system','free','ben',pro,Ora)

#-------------------Requetes
crlm.execute("SELECT * FROM incaserer")
for l in crlm.fetchall():
crlo.execute('''INSERT INTO
incarser(jourIncarseration,jourJugement,jourLiberte,idPrisonnier,idPrison,idDelit)
VALUES('%s','%s','%s',%s,%s,%s)'''%(l[1],l[2],l[3],l[4],l[5],l[6]))
cnlo.commit()

cnlo.close()
cnfo.close()
cnlm.close()

Exercice

create database vente;

\c vente
create schema vente;
create table if not exits vente.client(
idclient serial primary key,
nom varchar(50) not null,
telephone varchar(20) not null unique
adresse varchar(100) not null
);

create table if not exits vente.commande(

idcommande serial primary key,
jour date default current_date,
montant numeric(7) not null,
montantttc numeric(7) not null,
valide char(3) default 'non',
etat varchar(12) default 'nouvelle',
idclient integer not null
);

create table vente.categorie(

43
 idcategorie serial primary key,
designation varchar(50) not null
);

create table if not exits vente.produit(

idproduit serial primary key,
designation varchar(50) not null,
prix numeric(7) not null,
seuil integer not null,
idcategorie integer not null,
foreign key (idcategorie) references vente.categorie(idcategorie
);

create table if not exits vente.lignecommande(

idlignecommande serial primary key,
quantite integer not null,
prixvente numeric(7) not null,
idcommande integer not null,
idproduit integer not null,
foreign key (idcommande) references vente.commande(idcommande),
foreign key (idproduit) references vente.produit(idproduit)
);

create table if not exists vente.succursale(

idsuccursale serial primary key,
nom varchar(50) not null,
adresse varchar(100) not null,
ville varchar(50) not null
);

create table if not exists vente.fournisseur(

idfournisseur serial primary key,
nom varchar(50) not null,
telephone varchar(20) not null unique,
adresse varchar(100) not null,
nif numeric(10) not null unique
);

create table if not exists vente.livraison(

idlivraison serial primary key,
jour date default current_date,
montant numeric(7) not null,
idfournisseur integer not null,
foreign key (idfournisseur) references vente.fournisseur(idfournisseur)
);

create table if not exists vente.lignelivraison(

idlignelivraison serial primary key,
quantite integer not null,
prixachat numeric(7) not null,
idlivraison integer not null,

44
 idproduit integer not null,
foreign key (idlivraison) references vente.livraison(idlivraison),
foreign key (idproduit) references vente.produit(idproduit)
);

create table if not exits vente.boncommande(

idboncommande serial primary key,
jour date default current_date,
montant numeric(7) not null,
idfournisseur integer not null,
foreign key (idfournisseur) references vente.fournisseur(idfournisseur)
);

create table if not exits vente.ligneboncommande(

idligneboncommande serial primary key,
quantite integer not null,
prixachat numeric(7) not null,
idboncommande integer not null,
idproduit integer not null,
foreign key (idboncommande) references vente.boncommande(idboncommande),
foreign key (idproduit) references vente.produit(idproduit)
);

45
Exercice
Document non autorisé, pas de connexion internet, pas de communication entre candidats  2heures 15
Colonne Description Contraintes Type (taille)
idExpediteur Identifiant de l’expéditeur PRIMARY KEY NUMBER
nomExpediteur Nom de l’expéditeur NOT NULL VARCHAR2(50)
prenomExpediteur Prénom de l’expéditeur VARCHAR2(50)
telExpediteur Téléphone de l’expéditeur NOT NULL NUMBER(20)
idTransfert Identifiant du transfert PRIMARY KEY NUMBER
dateEnvoi Date d’envoi de l’argent DEFAULT SYSDATE
montant Montant transféré >=5000 NUMBER(8)
fraisEnvoi Frais du transfert 0.03* montant si c’est sur le NUMBER(5)
territoire du Niger 0.04*
montant si c’est à
l’extérieur du Niger

villeExpediteur Ville de l’expéditeur NOT NULL VARCHAR2(50)

villeDestination Ville de destination NOT NULL VARCHAR2(50)
codeEnvoi Code d’envoi NOT NULL, UNIQUE NUMBER(13)
numeroRecu Numéro du reçu NOT NULL, UNIQUE NUMBER(20)
dateReception Date de réception de l’argent dateReception>= dateEnvoi DATE
transféré
recu 1 si l’argent est reçu, 0 sinon CHECK(recu IN(0,1)) NUMBER(1)
destinataire Le nom du destinataire NOT NULL VARCHAR2(50)
typeEnvoi Type de l’envoi d’argent NUMBER(1) 0 à l’intérieur du pays
et 1 à l’extérieur
teldestinataire Le téléphone du destinataire NOT NULL NUMBER(20)

I. Langage de définition de données (5 points) sous Oracle ou PostgreSQL)

1. Créer les tables et leurs contraintes :
II. ---Connexion au serveur de base de donnees
III. psql -U postgres -W
IV. create database transfertmoney;
V. \c transfertmoney
VI. create table expediteur(
VII. idExpediteur serial primary key,
46
VIII. nomExpediteur varchar(50) not null,
IX. prenomExpediteur varchar(50) not null,
X. telExpediteur numeric(20) not null unique
XI. );
XII. create table transferer(
XIII. idTransfert bigserial primary key,
XIV. dateEnvoi timestamp default current_timestamp,
XV. montant numeric(7) not null,
XVI. fraisEnvoi numeric(5) not null,
XVII. villeExpediteur varchar(50) not null,
XVIII. villeDestination varchar(50) not null,
XIX. codeEnvoi numeric(13) not null,
XX. numeroRecu numeric(20) not null,
XXI. dateReception date,
XXII. recu numeric(1),
XXIII. destinataire varchar(50) not null,
XXIV. typeEnvoi numeric(1) default 0,
XXV. teldestinataire numeric(20) not null,
XXVI. idagent integer not null,
XXVII. constraint fk_idagent foreign key(idagent) references
agent(idagent)
XXVIII. );
XXIX. alter table transferer add constraint ck_montant check(montant>=1000);
XXX. alter table transferer add constraint uq_numeroRecu unique(numeroRecu);
XXXI. alter table transferer add constraint uq_codeEnvoi unique(codeEnvoi);
XXXII. alter table transferer add constraint ck_recu check(recu in(0,1));
XXXIII. alter table transferer add constraint ck_typeEnvoi check(typeEnvoi
in(0,1));
XXXIV.
XXXV. --
XXXVI.
XXXVII. create table agent(
XXXVIII. idagent serial primary key,
XXXIX. nomAgent varchar(50) not null,
XL. prenomAgent varchar(50) not null,
XLI. matricule varchar(12) not null unique,
XLII. idagence integer not null
XLIII. );
XLIV. alter table agent add constraint fk_
XLV. create table agence(
XLVI. idagence serial primary key,
XLVII. nomagence varchar(50) not null unique,
XLVIII. region varchar(50) not null
XLIX. );
L. alter table agent add constraint fk_idagence foreign key(idagence)
LI. references agence(idagence);
LII. ---Syntaxte ajuoter une colonne
LIII. --ALTER TABLE nomTable ADD [coloumn] nomColonne typeDonnee [contrainte];
LIV. alter table transferer add idExpediteur integer not null;
LV. alter table transferer add constraint fk_idexpediteur foreign
key(idexpediteur)
47
 LVI. references expediteur(idexpediteur);
LVII. alter table agence add pays varchar(25) not null;
LVIII. ---Syntaxe d'ajout de contrainte
LIX. --alter table nomTable add constraint nomContrainte typeContrainte;
LX.

2. Donner l’instruction SQL qui permet d’ajouter la colonne typeEnvoi dans la table TRANSFERER
3. Donner l’instruction SQL qui permet de changer le type de données de la colonne recu en CHAR(3)
4. Donner l’instruction SQL qui permet de définir par défaut la valeur ‘non’ dans la colonne recu
5. Donner l’instruction SQL qui permet de créer l’index sur la colonne telExpediteur de la table EXPEDITEUR
6. Donner l’instruction SQL qui permet de cloner la table TRANSFERER dans la table BackupTransfert
7. Donner l’instruction qui permet de supprimer la colonne prenomExpediteur de la table EXPEDITEUR
8. Donner l’instruction SQL qui permet d’ajouter la colonne typeEnvoi dans la table TRANSFERER. Cette
colonne est de type NUMBER(1) avec 0 comme valeur par défaut.
9. Donner l’instruction qui permet de créer la séquence SeqTransfert qui s’incrémente par 1 (sous Oracle
ou Postgres)
LXI. Langage de manipulation de données (1.5 points) (sous Oracle ou PostgreSQL)
10. Donner l’instruction qui permet de modifier ces données dans la table EXPEDITEUR sur l’identifiant 55
idExpediteur nomExpediteur telExpediteur
68 Hadjo Tchambal 98250015
11. Donner l’instruction qui permet de charger le contenu du fichier transfert.csv dans la table TRANSFERER,
avec point-virgule ‘ ;’ comme séparateur de valeurs des colonnes
12. Donner l’instruction SQL qui permet de supprimer l’enregistrement 15002 de la table TRANSFERER
LXII. Langage d’Interrogation de données (choisissez 8 requêtes parmi les 15) (8 points) (sous Oracle ou
PostgreSQL )
13. Donner le chiffre d’affaires réalisé par jour au mois de décembre 2021 à l’intérieur du pays. Sachant que
le chiffre d’affaires = SUM (fraisEnvoi)
14. Donner le chiffre d’affaires réalisé par année à l’intérieur du pays, de 2010 jusqu’à 2020
15. Donner le chiffre d’affaires réalisé par semaine au mois de mars 2022
16. Donner le chiffre d’affaires réalisé par destination au mois de janvier 2022 à l’extérieur du pays.
17. Donner le nombre de transferts non reçu par mois au premier trimestre 2022
18. Donner la destination qui a génèré plus de chiffre d’affaires en 2021 à l’intérieur du pays.
19. Donner l’agence qui a plus effectue de transfert au premier trimestre 2022.
20. Si la TVA est égale à 0.17 ; donner le montant à donner à l’Etat, sur les transferts effectués sur le
territoire du Niger
21. Donner trois villes d’expédition qui ont généré moins de chiffres d’affaires au dernier trimestre 2021
22. Donner le pourcentage de chiffre d’affaires réalisé au dernier trimestre 2021 par rapport au chiffre
d’affaires total réalisé en 2021
23. Donner le montant total de l’argent envoyé par année et par type d’envoi de 2015 à 2020
24. Donner le nombre de transferts effectués par semestre, en 2020
25. Donner le nombre de transferts effectués par agence de Niamey, au premier trimestre de l’année 2021
26. Donner le nombre de transferts effectués par jour au mois de décembre 2021
27. Donner le pourcentage de nombres de transferts effectués par agence de Niamey au mois de mai 2019
28. Sous PostgreSQL, créer une procédure qui retourne le nombre de transferts effectué pour un mois mis
en paramètre
LXIII. Langage de Contrôle données (2 points) Sous Oracle
27. Créer l’utilisateur avec les options suivantes :
USER PASSWORD DEFAULT TABLESPACE QUOTA
danzaki Barewa2020 USERS 200M
28. Créer le rôle suivant :
48
 ROLE PASSWORD
chefAgence LeRePuBlIcAiN
29. Attribuer ces privilèges au rôle sur la table TRANSFERER :
ROLE OBJET PRIVILEGES
chefAgence TRANSFERER SELECT, UDPATE, INSERT
30. Créer le profile DBmanager avec les paramètres suivants :
Profile SESSIONS_PER_USER CPU_PER_SESSION CPU_PER_CAL L CONNECT_TIME IDLE_TIME
DBmanager 3 7000 6000 120 2
31. Attribuer le profile DBmanager à l’utilisateur danzaki
32. Retirer le droit UDPATE sur la table TRANSFERER à l’utilisateur danzaki V. Sous PostgreSQL (3.5 points)
33. Créer le groupe chefAgence avec le mot de passe TrAnSpOrT2023 crypté
34. Créer le role controleur et lui assigner les priviliges suivants
OBJET PRIVILEGES
TRANSFERER SELECT, UDPATE, INSERT,DELETE
33. Attribuer le role controleur au groupe chefAgence
34. Créer les utilisateurs tchikaji et dankaza
35. Mettre les utilisateurs tchikaji et dankaza dans le groupe chefAgence
36. Retirer le privilège DELETE au groupe chefAgence
37. Donner l’instruction Shell Linux, qui permet de copier le fichier dunpDb.sql du serveur de base de
données et le placer dans le dossier /home/backup/ du serveur de fichier, et ce depuis la machine
cliente

49
Exercice

:1521
:3306 Otacle
MySQL [transfert argent]
[Appels telephoniques]

Reseau: 192.168.56.0/24

La machine hebergeant les conteneurs

[serveur de fichiers]

:5432
PostgreSQL
[transport voyageurs] :27017
MongoDB
[Analyse de donnees]
- Les serveurs Oracle, PostgreSQL et MySQL hébergent respectivement les bases de données de gestion des
transferts d’argent, transport des voyageurs et appels téléphoniques.
- Le serveur MongoDB héberge les bases de données d’analyse de données issues des serveurs Oracle,
PostgreSQL et MyQL
NB : les serveurs Oracle, PostgreSQL et MySQL sont des conteneurs Docker alors le serveur MongoDB est sous
la machine physique Windows
Ci-joint les fichiers transfertargent.py, transportvoyageur.py et appeltelephonique.py
Structure de la table :
Sous PostgreSQL :
create database transport;
\c transport
create table transport(
id serial primary key,
transport json not null
);

Sous Oracle :
create table transfert(
id number primary key,
transfert json not null
);
alter sequence seqcom increment by 1 ;
alter table transfert modify id number default seqcom.nextval;

Sous MySQL
create database appel;
use appel
create table appel(
id int primary key auto_increment,
appel json not null
);

50
 Script Python de chargement des données contenues dans les fichiers
from sshtunnel import SSHTunnelForwarder as Fw
import pymysql as My, psycopg2 as Pg, oracledb as Ora
import json
from transportvoyageur import T
from transfertargent import A
from appeltelephonique import Ap
def acces_Distant(h:str,u:str,p:str,pr:int):
cnFw = Fw((h,22),ssh_username=u,ssh_password=p,remote_bind_address=('localhost',pr))
cnFw.start()
return cnFw,cnFw.local_bind_port
def acces_Local(u:str,p:str,pr:int,b:str,X):
if X in (My,Pg):
cn = X.connect(host='localhost', user=u, password=p, database=b,port=pr)
elif X == Ora:
cn = X. connect(user=u,password=p,dsn=(f"localhost:{pr}/{b}"))
return cn,cn.cursor()
#--------------------------Postgresql---------------------
cnFwPg,PrFwPg = acces_Distant('192.168.56.110','gns3','gns3',5432)
cnLPg,crLPg = acces_Local('postgres','ben',PrFwPg,'transport',Pg)
#---------------------MySQL----------------
cnFwMy,PrFwMy = acces_Distant('192.168.56.110','gns3','gns3',3306)
cnLMy,crLMy = acces_Local('root','ben',PrFwMy,'appel',My)
#-------------------Oracle
cnFwOr,PrFwOr = acces_Distant('192.168.56.110','gns3','gns3',1521)
cnLOr,crLOr = acces_Local('system','ben',PrFwOr,'free',Ora)
#---------------------Chargement transportvoyageur.py
def ajouter_transport(T):
for t in T:
print('[+]',t)
crLPg.execute("insert into transport(transport) values(%s)",(json.dumps(t),))
cnLPg.commit()
#----------Chargement transfertargent.py
def ajouter_transfert(A):
for a in A:
print('[+]',a)
crLOr.execute("insert into transfert(transfert) values(%s)",(json.dumps(a),))
cnLOr.commit()
#-------------------Chargement appeltelephonique.py
def ajouter_appel(Ap):
for a in Ap:
print('[+]',a)
crLMy.execute("insert into appel(appel) values(%s)",(json.dumps(a),))
cnLMy.commit()
#----------------Appels de fonctions--------------
ajouter_transport(T)
ajouter_transfert(A)
ajouter_appel(Ap)
#---------------Fermeture des esssion
cnLOr.close()
51
cnFwOr.close()
cnLPg.close()
cnFwPg.close()
cnLMy.close()
cnFwMy.close()

Travail demandé
1. Exécuter le script Python ci-dessus pour changer les données contenues dans les fichiers
transfertargent.py, transportvoyageur.py et appeltelephonique.py sur les serveurs correspondants (4
points)
2. Ecrire une fonction qui permet de calculer le chiffre d’affaires des appels, par mois en en 2024 et enregistre
le résultat dans la collection caMoisAppel du serveur MongoDB (3 points)
3. Ecrire une fonction qui permet de calculer le chiffre d’affaires de transports de voyages par mois en 2024 et
enregistre le résultat dans la collection caMoisTransport du serveur MongoDB (3 points)
4. Ecrire une fonction qui permet de calculer le chiffre d’affaires de transferts d’argent par mois en 2024 et
enregistre le résultat dans la collection caMoisTransfert du serveur MongoDB (3 points)
5. Sous le serveur MongoDB, écrire une fonction qui permet de totaliser le chiffre d’affaires pour chaque
mois, de transferts d’agent, transports de voyageurs et appels téléphoniques, en 2024 (4 points)
6. Ecrire un script Python qui permet d’exporter dans un fichier CSV, le résultat de la question numéro 5 (3
points)

Annexe illustration les résultats de chargements de données sur les serveurs

Figure 1 : Capture d’écran du résultat d’insertion de données de transport de voyageurs

52
Figure 2 : Capture d’écran du résultat d’insertion de données des appels téléphoniques

53
Exercice
 Document non autorisé, téléphone éteint, pas d’Internet Durée :
2h15

54
 -  si la valeur de la colonne typeVote est égale 0 alors il s’agit d’un vote d’une élection présidentielle, 1
lorsqu’il s’agit d’une législative et 2 lorsque c’est une élection communale
La valeur de procuration est égale à 1 alors il s’agit d’un vote par procuration, 0 sinon
La colonne typeCandidat de la table TYPECANDIDAT contient les valeurs ‘presidentielle’ , ‘legislative’
,’communale’

Travail demandé (SGBD : MySQL ou PostgreSQL)

PARTIE1 (Langage de Définition de Données) (4 points)

1) Donner l’instruction qui permet de créer la base de données ELECTION (0,5 point)
2) Donner l’instruction qui permet de créer la table VOTER et toutes ses contraintes (1 points)
3) Donner l’instruction qui permet de voir le script SQL qui a permis de créer la table VOTER (0,5 point)
4) Donner l’instruction qui permet de créer la table backupVOTE qui contient toutes les données de la table
VOTER (1 point)
5) Ajouter la colonne lieunaissance dans la table ELECTEUR ; avec CHAR(50) comme type de données (0,5
point)
6) Donner l’instruction qui permet de modifier le type de données de la colonne lieunaissance en
VARCHAR(50) (0,5 point)

PARTIE 2(Langage de Manipulation de Données) (2 points)

7) Donner l’instruction qui permet d’insérer cet enregistrement dans la table ELECTEUR
nomElecteur prenomElecteur dateNaissElecteur lieunaissance genreElecteur

Grema Katchalla 1982-05-14 Maine Soroa M

8) Donner l’instruction qui permet de modifier le prénom de l’électeur dont le numElecteur 25014, à
Katchallou
9) Créer une procédure qui permet d’insérer le nom du parti dans la table PARTI. Cette procédure prend en
paramètre le nom du parti
10) Définir une transaction qui permet de supprimer l’enregistrement 52 de la table INSCRIRE

PARTIE 3(Langage d’Interrogation de Données) choisir 10 requêtes sur 21 (7 points)

11) Donner le nombre total des électeurs par type d’élection (présidentielle, législative, communale)
12) Donner la liste des candidats aux élections présidentielles et trier le résultat par ordre alphabétique des
noms des parties politiques
13) Donner le nombre d’électeurs ayant voté dans les bureaux 11 et 35 par procuration pour les élections
présidentielles
14) Sélectionner le nombre des candidats par parti politique aux élections législatives
15) Sélectionner le nombre des bulletins nuls par région aux élections législatives. (Un vote est nul lorsque la
valeur de la colonne voix est égale à 0 (zéro))
16) ) Sélectionner le nombre des bulletins exprimés valables aux élections présidentielles. (Un vote est
valable lorsque la valeur de la colonne voix est égale à 1 (un)
17) Sélectionner le nombre de voix (dont la voix est égale à 1) eues par chaque parti politique aux élections
présidentielles dans le département de ‘ Madarounfa’.
18) Sélectionner le nombre de voix (dont la valeur est égale à 1) eues par chaque parti politique aux élections
législatives
19) Sélectionner le nombre de voix (dont la valeur est égale à 1) eues par chaque parti politique aux élections
législatives dans les bureaux 45 et 82
20) Créer une vue qui permet de sélectionner le nombre de voix (dont la voix est égale à 1) par parti politique
aux élections législatives dans la région de Maradi

55
 21) Sélectionner le nombre de voix exprimées valables dans le département de ‘BILMA’ aux élections
présidentielles 22) Sélectionner le nombre de voix votées par procuration dans le département de
‘Madaoua’ par type d’élection (présidentielle, législative)

23) Donner le nombre d’électeurs n’ayant pas voté dans la région de ‘Zinder’ aux élections législatives. (C’est-
à-dire ceux qui sont inscrits mais n’ont pas voté)
24) Donner le nombre d’électeurs n’ayant pas voté par département, dans la région d’Agadez par type
d’élection
(présidentielle, législative), . (C’est-à-dire ceux qui sont inscrits mais n’ont pas voté)

25) Donner la région qui a connu le nombre maximal d’abstentions lors des élections présidentielles
26) Donner le nombre d’abstentions par département dans la région de ‘Maradi’ lors des élections
présidentielles
27) Donner le département qui a connu moins d’abstention dans la région de Tahoua. (Abstention. C’est-à-
dire ceux qui sont inscrits mais n’ont pas voté)
28) Donner le parti qui a le plus de voies lors des élections législatives
29) Donner le parti qui a le plus de voies pour chaque région lors des élections présidentielles
30) Trier par ordre décroissant des nombres des voies eus par chaque candidat aux élections présidentielles
31) Donner le parti qui a gagné les élections présidentielles. (Qui a eu plus de voix)
32) Créer une procédure qui trie par ordre décroissant des nombres des voies eues par chaque candidat aux
élections présidentielles

PARTIE 4(Langage de Contrôle de Données) (4 points)

33) Créer l’utilisateur hadari avec un mot de passe de votre choix
34) Donner à hadari le privilège d’insérer et de sélectionner les données sur la table CANDIDAT
35) Créer l’utilisateur ‘controleur’ à qui vous attribuer les privilèges de modifier, supprimer les données dans
la table PARTI
36) Donner l’instruction qui permet de retirer à l’utilisateur ‘controleur le privilège de suppression des
données sur la table PARTI
37) Sous PostgreSQL, créer le groupe superviseur.
38) Créer le role rolesuperviseur et lui attribuer le droit de sélectionner les données de la table VOTER
39) Créer l’utilisateur ambouta avec le mot de passe crypté et valide jusqu’à 2025-12-31
40) Ajouter l’utilisateur ambouta dans le groupe superviseur
41) Attribuer le role rolesuperviseur au groupe superviseur
PARTIE 5 : Backup des données (3 points)

23) Donner l’instruction qui permet de charger les données du fichier candidat.csv dans la table CANDIDAT du
serveur de base de données 192.168.5.7. Le script s’exécute sur le poste client
24) Sauvegarder le contenu de la table VOTER dans un fichier. Le script s’exécute sur le poste client

56
 25) Il y a le fichier backupVOTE.sql Sur le serveur 192.168.5.8. Donner l’instruction qui permet de
copier ce fichier et le placer sur le serveur de fichier 192.168.5.6. Le script s’exécute sur le poste client
26) Depuis la machine cliente, donner l’instruction qui permet de faire le dump des tables CANDIDTA, VOTER
et
ELECTEUR, du serveur 192.168.5.8

Exercice
Soit un extrait du MCD de la gestion de pèlerinage à la Mecque de l’entreprise BALAGURO sa
PELERIN VERSEMENT
idp 1,N 1,1 Idv
EFFECTUER
nom montantVersement
prenom dateVerment
genre
telephone
dateNaissance 1,1
VOYAGE
lieuNaissance 1,N
Id CONCERNER
nationalite montantVoyage
numPassport annee
photo CATEGORIE
1,1 Idc
POSSEDER
libele
1,N
PELERIN
Champ Contraintes Type Explication
idp PRIMARY KEY SERIAL Clé primaire,
nom NOT NULL VARCHAR(50) nom du pèlerin
prenom NOT NULL VARCHAR(50) Prénom du pèlerin
genre NOT NULL CHAR(1) Genre du pèlerin ; M pour homme et F pour femme
telephone NOT NULL, NUMERIC(8) Téléphone du pèlerin
UNIQUE
nationalite NOT NULL VARCHAR(25) Nationalité du pèlerin
numpassport NOT NULL, VARCHAR(25) Numéro du passeport
UNIQUE
image NOT NULL TEXT Photo du pèlerin
VERSEMENT
Champ Contraintes Type Explication
idv PRIMARY KEY SERIAL Clé primaire,
montantVersment NOT NULL NUMERIC(7) Montant du versement
dateVersement NOT NULL DATE Date du versement, current_date par défaut
VOYAGE
Champ Contraintes Type Explication
id PRIMARY KEY SERIAL Clé primaire
annee NOT NULL NUMERIC(4) Année du voyage
montantVoyage NOT NULL NUMERIC(7) Montant du voyage
CATEGORIE
Champ Contraintes Type Explication
idc PRIMARY KEY NUMERIC Clé primaire,
Libelle NOT NULL VARCHAR(50) Libelle du voyage(exple : Hadj, Oumra )

Travail demandé :

I. Langage de Définition de Données (4 points)

1. Créer la table VERSEMENT et ses contraintes :
a. Clef primaire
57
 b. Clés étrangères
c. Unicité de valeur
d. La colonne dateVersement doit être la date système par défaut
e. La colonne montanVersement doit recevoir des montants supérieurs à 100000
2. Créer l’index sur la colonne telephone de la table PELERIN
3. Renommer la colonne image en photo
4. Changer le type de donnés de la colonne photo en VARCHAR (100)
5. Donner l’instruction qui permet de cloner la table PELERIN à VOYAGEUR
II. Langage de Manipulation de Données (utilisez le SGBD de votre choix) (4 points)
6. Donner l’instruction SQL qui permet d’insérer correctement dans la table VOYAGE, la ligne
suivante :
montantVoyage annee
1500000 2022
7. Donner l’instruction SQL qui permet de modifier l’enregistrement du pèlerin dont l’identifiant est
25 par les valeurs suivantes
telephone dateNaissance lieuNaissance
98562141 1968-05-07 elkolta
8. Donner l’instruction qui permet de supprimer l’enregistrement de la table VERSEMENT dont
l’identifiant est 78.
9. Donner l’instruction SQL qui permet de charger le fichier pelerin.csv dans la table PELERIN
10. Donner l’instruction SQL qui permet d’exporter le contenu de la table VERSEMENT dans le
fichier backupVersement.txt
III. Langage d’Interrogation de Données (8 points)
 NB : le chiffre d’affaires est égal à la somme des montants versés

11. Donner le nombre de pèlerins par an, de 2010 jusqu’à 2018

12. Donner le chiffre d’affaires réalisé par an, de 2015 jusqu’à 2018
13. Donner le chiffre d’affaires réalisé par catégorie de voyages, en 2019
14. Donner le nombre de voyageurs par genre, en 2018
15. Donner le nombre de voyageurs par tranche d’âge : [25-35], [36-45], [46-55], [56-60], [61. +]
16. Donner le nombre de voyageurs qui n’ont pas versé l’intégralité du montant pour le hadj 2024
17. Donner le nombre de voyageurs qui ont versé plus de 50% du montant pour le hadj 2024
18. Le voyageur ayant le numéro de passeport 5421-498-7120-1201 demande un relevé des versements
qu’il a effectué pour hadj 2025. Pour cela, écrire une requête (nom, prenom, numPassport,
montantVersement, dateVerment) qui sélectionne les données entre parenthèses
IV. Langage de Contrôle données (4 points)
19. Créer l’utilisateur avec les options suivantes :
USER PASSWORD VALID UNTIL QUOTA CONNECTION LIMIT
danzaki Barewa2020 2029-01-01 4
20. Créer le rôle suivant :
ROLE PASSWORD
controleur LeRePuBlIcAiN
21. Attribuer ces privilèges aux rôles sur les objets suivants :
ROLE OBJET PRIVILEGES
controleur VERSEMENT SELECT, UDPATE, INSERT
agentsaisie PELERIN SELECT, INSERT
22. Créer le groupe garkuwa
23. Attribuer le role controleur au groupe garkuwa
24. Retirer le privilège UPDATE au rôle controleur
25. Créer l’utilisateur iroro avec les options suivantes :
USER ENCRYPTED PASSWORD VALID UNTIL
iroro IrOrOGoBiR 2025-09-30
26. Donner l’instruction qui permet de mettre l’utilisateur iroro dans les groupes imani et daraja
27. Supprimer le role agentsaisie
28. Donner les instructions qui permettent de :
a. Lister les bases de données du serveur
b. Lister les tables de la base de données courante
c. Avoir l’aide sur la syntaxe de création d’un groupe
58
 d. Avoir l’aide sur la syntaxe de la commande COPY

Exercice
Objectifs : Gérer les causes des décès

Code Désignation Type(taille) Contrainte/forma

idPersonne Identifiant de la personne SERIAL PRIMARY KEY
décédée
nom Nom de la personne décédée VARCHAR(50) NOT NULL
prenom Prénom de la personne VARCHAR(50) NOT NULL
décédée
dateNaiss Date de décès de la personne DATE YYYY-MM-DD, NOT NULL
décédée
lieuNaiss Lieu de naissance de la VARCHAR(50) NOT NULL
personne décédée
nationalite Nationalité de la personne VARCHAR(25) NOT NULL
décédée
genre Genre de la personne CHAR(1) NOT NULL, DEFAULT ‘M’
décédée
situationMatrim Situation matrimoniale de la VARCHAR(11) NOT NULL
personne décédée {‘celibataire’,’marie’,’divorce’,’veuve’ }
jourDeces Jour de décès de la personne DATE YYYY-MM-DD, NOT NULL
lieuDeces Lieu de décès de la personne VARCHAR(50) Exemple : maison, bataille, hopital, …
idCauseDeces Identifiant de la cause de SERIAL PRIMARY KEY
décès
cause Cause du décès VARCHAR(50) NOT NULL
description Description de la cause du VARCHAR(200) NOT NULL
décès
idCommune Identifiant de la commune SERIAL PRIMARY KEY
commune Le nom de la commune VARCHAR(50) NOT NULL
idDepartement Identifiant du département SERIAL PRIMARY KEY
Departement Le nom du département VARCHAR(50) NOT NULL
idRegion Identifiant de la région SERIAL PRIMARY KEY
region Le nom de la région VARCHAR(10) NOT NULL

REGION
PERSONNE CAUSEDECES idRegion
idPersonne idCauseDeces region
nom cause
prenom description
dateNaiss
lieuNaiss
nationalite
genre COMMUNE DEPARTEMENT
situationMatrim idCommune idDepartement
jourDeces commune Departement
lieuDeces #idDepartement #idRegion
#idCauseDeces
# idCommune

I. Langage de définition de données (sous Oracle ou PostgreSQL) (4 points)

1. Créer la table personne et toutes ses contraintes
2. Créer une table DECES2024 qui reçoit sans doublon, les enregistrements des personnes décédées en 2024
suite aux accidents de circulation routière
3. Donner une instruction qui permet d’ajouter fonction de type VARCHAR(50) dans la table PERSONNE
4. Donner une instruction qui permet de définir par défaut la nationalité nigerienne dans la colonne
NATIONALITE
59
 5. Donner l’instruction qui permet de créer un indexe sur la colonne JOURDECES
6. Donner l’’instruction qui permet de renommer la colonne jourDeces en dateDeces
II. Langage de manipulation de données (sous Oracle ou PostgreSQL) 1 points

7. Donner l’instruction qui permet de charger les données dans la table PERSONNE, avec point-virgule
comme séparateur de données
8. Créer une procédure qui permet de modifier les données dans la table CAUSEDECES
III. Langage d’Interrogation de données 9 points
Partie 1 : Sur une base de données relationnelle : PostgreSQL, MySQL ou Oracle
9. Donner le nombre de personnes décidées en 2024 par tranche d’âge : [0-7],[8-14],[15-21],[22-35],[36-
47],[48-60],[61-80],[81-100],[101-120]
10. Donner le nombre de personnes décidées par cause de décès en 2023
11. Donner le nombre de personnes décidées par lieu de décès, au dernier trimestre de l’année 2023
12. Donner le nombre de personnes décédées par année, suite au terrorisme de 2020 à 2024
13. Donner le nombre de femmes décédées suite à l’accouchement par région et par trimestre de l’année 2024
14. Un enfant est une personne âgée de moins de 18 ans. Donner le nombre d’enfants décédés suite au
paludisme dans les communes du département de Madarounfa, en 2024
15. Donner le nombre de personnes décédées par situation matrimoniale, suite au Covid19, dans les
communes de Niamey en 2019
16. Données les dix premières causes de décès, triées par ordre décroissant de nombre de décès, en 2024
Partie 2 : Sur une base de données non relationnelle : MongoDB
Voici un extrait de la structure du document de la collection DECES de la base de données CAUSEDECES
{
'nom':'Issa','prenom':'Gagara','dateNaiss':'1991:11:14', 'lieuNaiss':'Tchikaji',
'nationalite': 'nigerienne', 'genre':'Masculin','situationMatrimoniale':'celibtaire',
'jourDeces':'2024-12-08','lieuDeces':'Hopital de Maradi', 'cause':'Paludisme',
'commune':'Safo', 'Departement':'Madarounfa','region':'Maradi'
},
NB : Vous pouvez utiliser le langage Python pour répondre aux requetes suivantes
17. Donner le nombre de décès par cause de décès et par trimestre, dans la région de Zinder, en 2022
18. Donner la liste des personnes décédées suite au meurtre dans la région de Niamey
19. Donner le nombre de fille âgées de 5 ans à 12 ans, décédées dans chaque département
20. Donner le nombre de personnes décédées par mois pour une année de votre choix
IV. Sauvegarde et restauration de données (1.5 points)

NB : les modules à utiliser sont : pymysql, psycopg2, oracledb et sshtunnel

21. Ecrire un script Python permettant de répliquer les données de la table
PERSONNE(nom,prenom,datenaiss,lieuNaiss,dateNaiss) du serveur Oracle dans la table DECES du serveur
PostgreSQL
V. Langage de contrôle de données (Sous Oracle) (3 points)
22. Créer le profile datamanager avec les paramètres suivants
SESSIONS_P CPU_PER_ CPU_PE CONNEC IDLE_ PRIVATE_ FAILED_LOGI PASSWORD
ER_USER SESSION R_CALL T_TIME TIME SGA N_ATTEMPTS _LIFE_TIM
E
3 7000 6000 120 2 950K 3 30
23. Créer le role dataanalyste et lui attribuer les privilèges sur les tables suivantes :
60
 Privilèges Tables
INSERT, SELECT, UPDATE PERSONNE, CAUSE
24. Créer l’utilisateur dankounama et lui attribuer le profile datamanager
25. Attribuer le role dataanalyste à l’utilisateur dankounama
26. Donner le droit de sélectionner les données de la table PERSONNE à l’utilisateur nahantchi et qui à son
tour peut attribuer ce même droit à un autre utilisateur
Exercice
Soit un extrait du data center de l’entreprise MAIKARFI IA

1560:1521 1561:1521
TV TA

3362:3306 3363:3306
TV TA

.1 192.168.56.0/24
.101

client
 


1562:1521 1563:1521
TV TA

5461:5432 5460:5432
TA TV

Légende :
 : ce signe désigne les bases de production
TA : Transfert d’Argent
TV : Transport de Voyageur
Le serveur hébergeant les conteneurs à l’adresse IP 192.168.56.101 alors que la machine cliente a
192.168.56.1
Dictionnaire de données : Gestion de transfert d’argent

Colonne Explication Type (taille maximal) Contrainte

idexpediteur Clé primaire SERIAL Primary key
Nom Nom de l’expéditeur VARCHAR (50) NOT NULL
telExp Numéro de téléphone de VARCHAR (22) NOT NULL, UNIQUE
l’expéditeur
idTransfert Identifiant du transfert SERIAL Primary key
dateEnvoi Date d’envoi TIMESTAMP DEFAULT CURRENT_
TIMESTAMP
Montant Montant à transférer NUMERIC(7) NOT NULL ; [2000,2000000]
Frais Frais de transfert d’argent NUMERIC (5) NOT NULL
villeExp Ville de l’expédition de l’argent VARCHAR(50) NOT NULL
villeDest Ville où se trouve le destinataire VARCHAR(50) NOT NULL
61
nomDest Nom du destinataire VARCHAR(50) NOT NULL
telDest, Numéro de téléphone du VARCHAR(22) NOT NULL
destinataire
typeEnvoi Tyoe d’envoi NUMERIC(1) DEFAULT 0
1 : à l’extérieur du pays
dateReception Date de réception de l’argent DATE

Dictionnaire de données : Gestion de transport de voyageurs

Colonne Explication Type(taille maxi) Contrainte

idVoyageur Identifiant du voyageur SERIAL PRIMARY KEY
Nom Nom du voyageur VARCHAR (50) NOT NULL
Prenom Prénom du voyageur VARCHAR (50) NOT NULL
Tel Numéro de téléphone du voyageur VARCHAR(22) NOT NULL, UNIQUE
Idtarif Identifiant de la table tarif SERIAL PRIMARY KEY
Tarif Le tarif du voyage à une TIMESTAMP DEFAULT CURRENT_ TIMESTAMP
destination
villeDepart Ville de départ VARCHAR (50) NOT NULL
villeDestination Ville de destination du voyage VARCHAR (50) NOT NULL
typeVoyage NUMERIC(1) DEFAULT 0
1 : à l’extérieur du pays
idVoyage SERIAL PRIMARY KEY
Jour Jour du voyage DATE Jour <= current_date
heureDepart Heure de départ NUMERIC(2) NOT NULL
numeroBus Numéro du bus NUMERIC(3) NOT NULL
- MLD transfert d’argent:
EXPEDITEUR(idexpediteur,nom,telExp)
TRANSFERT(idTransfert,dateEnvoi,montant,frais,villeExp, villeDest,nomDest,
telDest,typeEnvoi,dateReception, #idExpediteur)
- MLD de transport de voyageurs
VOYAGEUR(idVoyageur, nom,prenom,tel)
TARIF(idtarif,tarif,villeDepart,villeDestination,typeVoyage)
VOYAGER(idVoyage,jour,heureDepart,numeroBus,# idtarif,# idVoyageur)

 Travail demandé : chaque réponse vaut 0.5 points

1. Créer les tables de gestion de transfert d’argent et celles de gestion de transport de voyageurs sous
PostgreSQL, Oracle et MySQL tout en respectant toutes les contraintes d’intégrité de données
a. Sous PostgreSQL :
Travail preliminaire:
Connecter en tant que postgres et créer les schemas transfert et voyage
CREATE TABLE EXPEDITEUR(
idexpediteur serial primary key,
nom varchar(50) not null,
telExp numeric(22) not null unique
);
CREATE TABLE TRANSFERT(
idTransfert bigserial primary key,
dateEnvoi timestamp default current_timestamp,
montant numeric(7) not null,
frais numeric(5) not null,
villeExp varchar(50) not null,
villeDest varchar(50) not null,
nomDest varchar(50) not null,
telDest numeric(22) not null,

62
 typeEnvoi numeric(1) default 0,
dateReception date default current_date,
idExpediteur integer not null,
constraint fkidexpediteur foreign key(idexpediteur)
references EXPEDITEUR(idexpediteur)
);
alter table TRANSFERT add constraint ckMontant check(montant between 2000 and
2000000);

create TABLE VOYAGEUR(

idVoyageur serial primary key,
nom varchar(50) not null,
prenom varchar(50) not null,
tel numeric(22) not null
);
alter table VOYAGEUR add constraint uqTel unique(tel);
CREATE TABLE TARIF(
idtarif serial primary key,
tarif numeric(5) not null,
villeDepart varchar(50) not null,
villeDestination varchar(50) not null,
typeVoyage numeric(1) default 0
);
alter table tarif add constraint ckVille check(villeDepart <> villeDestination);
CREATE TABLE VOYAGER(
idVoyage serial primary key,
jour date default current_date,
heureDepart numeric(2) default 4,
numeroBus numeric(3) not null,
idtarif integer not null,
idVoyageur integer not null
);
alter table VOYAGER add constraint fkidtarif foreign key(idtarif)
references tarif(idtarif);
alter table VOYAGER add constraint fkidvoyageur foreign key(idVoyageur)
references VOYAGEUR(idVoyageur);

b. Sous Oracle
 Travail preliminaire:
- Connecter au serveur Oracle en tant que system et créer les utilisateurs transfert et voyage et leur
attribuer les droits de créer des tables, séquences.
create user c##transfert identified by transfert

default tablespaces USERS quota 25M ON USERS;

grant create any table, create session, create sequence to transfert;

create user c##voyage identified by voyage

default tablespaces USERS quota 25M ON USERS;
grant create any table, create session, create sequence to voyage;

- Connecter en tant que transfert et créer les tables suivantes :

CREATE TABLE EXPEDITEUR(
idexpediteur number primary key,
nom varchar2(50) not null,
telExp number (22) not null unique
);
63
 CREATE TABLE TRANSFERT(
idTransfert number primary key,
dateEnvoi timestamp default current_timestamp,
montant number (7) not null,
frais number (5) not null,
villeExp varchar2(50) not null,
villeDest varchar2(50) not null,
nomDest varchar2(50) not null,
telDest number (22) not null,
typeEnvoi number (1) default 0,
dateReception date default current_date,
idExpediteur number not null,
constraint fkidexpediteur foreign key(idexpediteur)
references EXPEDITEUR(idexpediteur)
);
alter table TRANSFERT add constraint ckMontant check(montant between 2000 and
2000000);

- Connecter en tant que voyage et créer les tables suivantes :

create TABLE VOYAGEUR(

idVoyageur number primary key,
nom varchar2(50) not null,
prenom varchar2(50) not null,
tel number (22) not null
);
alter table VOYAGEUR add constraint uqTel unique(tel);
CREATE TABLE TARIF(
idtarif number primary key,
tarif number (5) not null,
villeDepart varchar2(50) not null,
villeDestination varchar2(50) not null,
typeVoyage number (1) default 0
);
alter table tarif add constraint ckVille check(villeDepart <>
villeDestination);
CREATE TABLE VOYAGER(
idVoyage number primary key,
jour date default current_date,
heureDepart number (2) default 4,
numeroBus number (3) not null,
idtarif number not null,
idVoyageur number not null
);
alter table VOYAGER add constraint fkidtarif foreign key(idtarif)
references tarif(idtarif);
alter table VOYAGER add constraint fkidvoyageur foreign key(idVoyageur)
references VOYAGEUR(idVoyageur);

c. sous MySQL
CREATE TABLE EXPEDITEUR(
idexpediteur int primary key auto_increment,
nom varchar(50) not null,
telExp int (22) not null unique

64
 );
CREATE TABLE TRANSFERT(
idTransfert int primary key auto_increment,
dateEnvoi timestamp default current_timestamp(),
montant int (7) not null,
frais int (5) not null,
villeExp varchar(50) not null,
villeDest varchar(50) not null,
nomDest varchar(50) not null,
telDest int (22) not null,
typeEnvoi int (1) default 0,
dateReception date default current_date(),
idExpediteur int not null,
constraint fkidexpediteur foreign key(idexpediteur)
references EXPEDITEUR(idexpediteur)
);
alter table TRANSFERT add constraint ckMontant check(montant between 2000 and
2000000);

create TABLE VOYAGEUR(

idVoyageur int primary key auto_increment,
nom varchar(50) not null,
prenom varchar(50) not null,
tel int (22) not null
);
alter table VOYAGEUR add constraint uqTel unique(tel);
CREATE TABLE TARIF(
idtarif int primary key auto_increment,
tarif int (5) not null,
villeDepart varchar(50) not null,
villeDestination varchar(50) not null,
typeVoyage int (1) default 0
);
alter table tarif add constraint ckVille check(villeDepart <> villeDestination);
CREATE TABLE VOYAGER(
idVoyage int primary key auto_increment,
jour date default current_date,
heureDepart int (2) default 4,
numeroBus int (3) not null,
idtarif int not null,
idVoyageur int not null
);
alter table VOYAGER add constraint fkidtarif foreign key(idtarif)
references tarif(idtarif);
alter table VOYAGER add constraint fkidvoyageur foreign key(idVoyageur)
references VOYAGEUR(idVoyageur);

2. Donner l’instruction qui permet de charger le contenu du fichier :

a) transfert.csv dans la table transfert sous PostgreSQL
copy TRANSFERT(dateEnvoi,montant,frais,villeExp,

villeDest,nomDest, telDest,typeEnvoi,
dateReception,idExpediteur
)
from transfert.csv with delimiter ';';

65
 b) voyager.csv dans la table voyager sous Oracle
- creer un fichier charger.txt qui controle de chargement de données et y saisir:
load data infile voyager.csv replace into table voyager
fields terminated by ';'(idVoyage,jour,heureDepart,numeroBus,idtarif,idVoyageur)
- chargement de donnees avec sqlldr (sous la console)
sqlldr voyager/MoTpAsSe control=charger.txt

c) voyager.csv dans la table voyager sous MySQL (sous la console du serveur MySQL)
load data infile voyager.csv replace into table voyager fields terminated by ';'

3. Sous Oracle, donner l’instruction qui permet de dumper le contenu de la table voyager dans le fichier
voyageDunp.txt
exp c##voyage/voyage file=voyagedump.txt tables(voyager) grants=y

4. Sous PostgreSQL, donner l’instruction qui permet de dumper le contenu de la table transfert dans le
fichier transfert.csv
- Sur la console :
pg_dump -U postgres -W -d transfert -t transfert > transfert.csv
ou
pg_dump -U postgres -W -d transfert -t transfert > transfert.csv

5. Ecrire une fonction PLSQL Oracle, permettant de mettre à jour les données de la table voyageur

 Remarque : le chiffre d’affaires de transferts est égal à la somme des frais de transferts
le chiffre d’affaires de voyages est égal à la somme des montants de voyages

 Sous un SGBD de votre choix, écrire les requêtes suivantes : [7 – 12]

6. Ecrire une requête qui donner le chiffre d’affaires de transferts par mois en 2024
- Sous MySQL :
SELECT EXTRACT(month from dateEnvoi) mois,SUM(frais) chiffreAffaires
FROM transfert WHERE EXTRACT(year from dateEnvoi) = 2024
GROUP BY mois;

- Sous Oracle :
SELECT EXTRACT(month from dateEnvoi) mois,SUM(frais) chiffreAffaires
FROM transfert WHERE EXTRACT(year from dateEnvoi) = 2024
GROUP BY EXTRACT(month from dateEnvoi);

- Sous PostgreSQL
SELECT EXTRACT(month from dateEnvoi) mois,SUM(frais) chiffreAffaires
FROM transfert WHERE EXTRACT(year from dateEnvoi) = 2024
GROUP BY 1;

7. Ecrire une requête qui donner le chiffre d’affaires de transferts par semaine pour un mois de votre choix
- Sous MySQL :
SELECT CASE
WHEN EXTRACT(day from dateEnvoi) BETWEEN 1 AND 7 then '[1-7]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 8 AND 14 then '[8-14]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 15 AND 21 then '[15-21]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 22 AND 31 then '[22-30]'
END semaine, SUM(frais) chiffreAffaires
FROM transfert WHERE EXTRACT(year from dateEnvoi) = 2024 AND
EXTRACT(month from dateEnvoi)=4
GROUP BY semaine;

66
- Sous Oracle :
SELECT CASE
WHEN EXTRACT(day from dateEnvoi) BETWEEN 1 AND 7 then '[1-7]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 8 AND 14 then '[8-14]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 15 AND 21 then '[15-21]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 22 AND 31 then '[22-30]'
END semaine, SUM(frais) chiffreAffaires
FROM transfert WHERE EXTRACT(year from dateEnvoi) = 2024 AND
EXTRACT(month from dateEnvoi)=4
GROUP BY CASE
WHEN EXTRACT(day from dateEnvoi) BETWEEN 1 AND 7 then '[1-7]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 8 AND 14 then '[8-14]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 15 AND 21 then '[15-21]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 22 AND 31 then '[22-30]'
END;

- Sous PostgreSQL :
SELECT CASE
WHEN EXTRACT(day from dateEnvoi) BETWEEN 1 AND 7 then '[1-7]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 8 AND 14 then '[8-14]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 15 AND 21 then '[15-21]'
WHEN EXTRACT(day from dateEnvoi) BETWEEN 22 AND 31 then '[22-30]'
END semaine, SUM(frais) chiffreAffaires
FROM transfert WHERE EXTRACT(year from dateEnvoi) = 2024 AND
EXTRACT(month from dateEnvoi)=4
GROUP BY 1;

8. Ecrire une requête qui donner le chiffre d’affaires de transferts par heure pour un mois de votre choix
- Sous MySQL | Oracle | PostgreSQL:
SELECT EXTRACT(hour from dateEnvoi ) heure, SUM(frais) chiffreAffaires
FROM transfert
WHERE EXTRACT(year from dateEnvoi) = 2024 AND EXTRACT(month from dateEnvoi)=4
GROUP BY heure;

9. Ecrire une requête qui donner le chiffre d’affaires de voyages par semaine pour un mois de votre choix
- Sous MySQL
SELECT SUM(tarif) CA,CASE
WHEN extract(day from jour) BETWEEN 1 AND 7 THEN '1-7'
WHEN extract(day from jour) BETWEEN 8 AND 14 THEN '8-14'
WHEN extract(day from jour) BETWEEN 15 AND 21 THEN '15-21'
WHEN extract(day from jour) BETWEEN 22 AND 31 THEN '22-31'
END AS Semaine
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(month from jour) = 1 AND
extract(year from jour) = 2023
GROUP BY Semaine
ORDER BY Semaine;

- Sous Oracle :
SELECT SUM(tarif) CA,CASE
WHEN extract(day from jour) BETWEEN 1 AND 7 THEN '1-7'
WHEN extract(day from jour) BETWEEN 8 AND 14 THEN '8-14'
WHEN extract(day from jour) BETWEEN 15 AND 21 THEN '15-21'
WHEN extract(day from jour) BETWEEN 22 AND 31 THEN '22-31'
END AS Semaine
67
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(month from jour) = 1 AND
extract(year from jour) = 2023
GROUP BY CASE
WHEN extract(day from jour) BETWEEN 1 AND 7 THEN '1-7'
WHEN extract(day from jour) BETWEEN 8 AND 14 THEN '8-14'
WHEN extract(day from jour) BETWEEN 15 AND 21 THEN '15-21'
WHEN extract(day from jour) BETWEEN 22 AND 31 THEN '22-31'
END
ORDER BY 2 ASC;

- Sous PostgreSQL
SELECT SUM(tarif) CA,CASE
WHEN extract(day from jour) BETWEEN 1 AND 7 THEN '1-7'
WHEN extract(day from jour) BETWEEN 8 AND 14 THEN '8-14'
WHEN extract(day from jour) BETWEEN 15 AND 21 THEN '15-21'
WHEN extract(day from jour) BETWEEN 22 AND 31 THEN '22-31'
END AS Semaine
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(month from jour) = 1 AND
extract(year from jour) = 2023
GROUP BY 2
ORDER BY 2 ASC;

10. Ecrire une requête qui donne le chiffre d’affaires de transfert par trimestre, à l’extérieur du pays, pour
une année de votre choix
- MySQL
SELECT SUM(tarif) CA,CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
END AS Trimestre
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(year from jour) = 2023 and
typevoyage = 1
GROUP BY Trimestre
ORDER BY Trimestre ASC;

- Sous Oracle :
SELECT SUM(tarif) CA,CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
END AS Trimestre
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(year from jour) = 2023 and
typevoyage = 1
GROUP BY CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
68
END
ORDER BY 2 ASC;

- Sous PostgreSQL
SELECT SUM(tarif) CA,CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
END AS Trimestre
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(year from jour) = 2023 and
typevoyage = 1
GROUP BY 2
ORDER BY 2 ASC;

11. Ecrire une requête qui donne le pourcentage de chiffre d’affaires de transfert par trimestre, à l’extérieur
du pays, pour une année de votre choix
- MySQL
Création de la vue CATRIM qui retourne le chiffre d’affaires par trimestre en 2023
- CREATE VIEW CATRIM AS
- SELECT SUM(tarif) CA,CASE
- WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
- WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
- WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
- WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
- END AS Trimestre
- FROM tarif T, voyager V
- WHERE T.idtarif = V.idtarif AND extract(year from jour) = 2023 and
- typevoyage = 1
- GROUP BY Trimestre
- ORDER BY Trimestre ASC;

Calcul du pourcentage
SELECT ROUND(100*CA/(SELECT SUM(CA) FROM CATRIM),2) as Pourcentage, Trimestre
FROM CATRIM ORDER BY Trimestre;

- Sous Oracle :
Création de la vue CATRIM qui retourne le chiffre d’affaires par semaine
CREATE VIEW CATRIM AS
SELECT SUM(tarif) CA,CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
END AS Trimestre
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(year from jour) = 2023 and
typevoyage = 1
GROUP BY CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
69
END
ORDER BY 2 ASC;

Calcul du pourcentage en fonction du résultat de la vue CATRIM

SELECT ROUND(100*CA/(SELECT SUM(CA) FROM CATRIM),2) as Pourcentage, TrimestreFROM
CATRIM ORDER BY Trimestre;

- Sous PostgreSQL
Création de la vue CATRIM
CREATE VIEW CATRIM AS
SELECT SUM(tarif) CA,CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
END AS Trimestre
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(year from jour) = 2023 and
typevoyage = 1
GROUP BY 2
ORDER BY 2 ASC;

Calcul du pourcentage
SELECT ROUND(100*CA/(SELECT SUM(CA) FROM CATRIM),2) as Pourcentage, Trimestre
FROM CATRIM ORDER BY Trimestre;

12.Ecrire un script Python qui sauvegarde les données de la requête 8 du serveur Oracle 1562 vers le serveur
Oracle 1560
from sshtunnel import SSHTunnelForwarder as FW
import oracledb as Ora
def Distante(h:str,u:str,m:str,pr:int):
cnFw =
FW((h,22),ssh_username=u,ssh_password=m,remote_bind_address=('localhost',pr))
cnFw.start()
return cnFw,cnFw.local_bind_port
def Locale(u:str,m:str,b:str,pr):
cnL= Ora.connect(user=u,password=m,dsn=(f'localhost:{pr}/{b}'))
return cnL,cnL.cursor()
Ora1562CnFw,Ora1562Pr = Distante('192.168.56.110','gns3','gns3',1562)
cnL1562,crL1562 = Locale('system','ben','free',Ora1562Pr)
#-----
Ora1560CnFw,Ora1560Pr = Distante('192.168.56.110','gns3','gns3',1560)
cnL1560,crL1560 = Locale('system','ben','free',Ora1560Pr)
#-------Requete
crL1562.execute('''SELECT EXTRACT(hour from dateEnvoi ) heure, SUM(frais)
chiffreAffaires
FROM transfert
WHERE EXTRACT(year from dateEnvoi) = 2024 AND EXTRACT(month from
dateEnvoi)=4
GROUP BY heure''')
crL1560.execute("CREATE TABLE IF NOT EXISTS caheure(heure number(2) NOT NULL,ca
NUMBER(8) NOT NULL)")
cnL1560.commit()
for l in crL1562.fetchall():
70
 crL1560.execute("INSERT INTO caheure VALUES(%s,%s)"%(l[0],l[1]))
cnL1560.commit()

cnL1560.commit()
cnL1562.commit()
Ora1562CnFw.close()

13.Ecrire un script Python qui sauvegarde les données de la requête 10 du serveur MySQL 3362 vers le
serveur MySQL 3363
from sshtunnel import SSHTunnelForwarder as FW
import pymysql as My
def Distante(h:str,u:str,m:str,pr:int):
cnFw =
FW((h,22),ssh_username=u,ssh_password=m,remote_bind_address=('localhost',pr))
cnFw.start()
return cnFw,cnFw.local_bind_port
def Locale(u:str,m:str,b:str,pr):
cnL= My.connect(user=u,password=m,database=b,port=pr)
return cnL,cnL.cursor()
My3362CnFw,My3362Pr = Distante('192.168.56.110','gns3','gns3',3362)
cnL3362,crL3362 = Locale('system','ben','free',My3362Pr)
#-----
My3363CnFw,My3363Pr = Distante('192.168.56.110','gns3','gns3',3363)
cnL3363,crL3363 = Locale('system','ben','free',My3363Pr)
#-------Requete
crL3362.execute('''SELECT SUM(tarif) CA,CASE
WHEN extract(month from jour) BETWEEN 1 AND 3 THEN '1-3'
WHEN extract(month from jour) BETWEEN 4 AND 6 THEN '4-6'
WHEN extract(month from jour) BETWEEN 7 AND 9 THEN '7-9'
WHEN extract(month from jour) BETWEEN 10 AND 12 THEN '10-12'
END AS Trimestre
FROM tarif T, voyager V
WHERE T.idtarif = V.idtarif AND extract(year from jour) = 2023 and
typevoyage = 1
GROUP BY Trimestre
ORDER BY Trimestre ASC''')
crL3363.execute("CREATE TABLE IF NOT EXISTS catrimestre(ca number(8) NOT
NULL,trimestre VARCHR(5) NOT NULL)")
cnL3363.commit()
for l in crL3362.fetchall():
crL3363.execute("INSERT INTO catrimestre VALUES(%s,'%s')"%(l[0],l[1]))
cnL3363.commit()

cnL3362.commit()
cnL3363.commit()
My3362CnFw.close()
My3363CnFw.close()

14. Sur le serveur PostgreSQL, créer le rôle chefagence

create role chefagence with password 'chefagence';

15. Attribuer les privilèges d’insertion, modification sur la table VOYAGER à l’utilisateur chefagence

grant insert,update on voyager to chefagence;

16. Créer le groupe agenceregion

71
 create group agenceregion with password 'agenceregion-ne';

17. Créer les utilisateurs maiaoura, maigujiya, maidankali avec des mots de passe cryptés et expireront le
2026-12-31
create user maiaoura with password 'maiaoura' valid until '2026-12-31';
create user maigujiya with password 'maigujiya' valid until '2026-12-31';
create user maidankali with password 'maidankali' valid until '2026-12-31';
18. Ajouter les utilisateurs maiaoura, maigujiya, maidankali dans le groupe agenceregion

alter group agenceregion add user maiaoura,maigujiya,maidankali;

19. Attribuer le rôle chefagence au groupe agenceregion
grant chefagence to agenceregion;
20. Créer les utilisateurs suivants sous Oracle
USER PASSWORD TABLESPACE QUOTA PASSWORD
maialawa USERS 500M EXPIRE
maizouma SYS 250M

create user c##maialawa identified by maialawa

tablespace USERS
quota 500M
on tablespace USERS
password expire;

create user c##maizouma identified by maizouma

tablespace SYS
quota 2500M
on tablespace SYS
password expire;

 Avec Python :
Supposons que nous avons un fichier contenant la liste des utilisateurs avec des paramètres (illustré par le
tableau)

import oracledb as Ora

def connecter(u:str,m:str,b:str,pr:int):
cn = Ora.connect(user=u,password=m,dsn=(f"localhost:{pr}/{b}"))
return cn,cn.cursor()
def creer(u,m,t,q,cn,cr):
cr.execute(f'''CREATE USER c##{u} identified by {m}
tablespace {t} quota {q} on {t} password expire''')
cn.commit()
#--- Appel des fonctions
cn,cr = connecter('system','ben','free',1521)
with open("utilisateur.csv") as f:
for ligne in f:
l=ligne.split(";")
creer(l[0],l[1],l[3],cn,cr)

21. Attribuer les privilèges SELECT, INSERT, UPDATE sur la table TRANSFERT à l’utilisateur maizouma
grant insert,update, SELECT on transfert to c##maizouma;
22. Créer le profile datamager avec les paramètres suivants :

FAILED_LOGIN_ATTEMPTS 4 PASSWORD_GRACE_TIME 5
PASSWORD_LIFE_TIME 120 PASSWORD_LOCK_TIME 6
PASSWORD_REUSE_TIME 12 PASSWORD_REUSE_MAX 8
CONNECT_TIME 160 CPU_PER_SESSION 9000
72
 create profile c##datamager limit
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LIFE_TIME 120
PASSWORD_REUSE_TIME 12
CONNECT_TIME 160
PASSWORD_GRACE_TIME 5
PASSWORD_LOCK_TIME 6
PASSWORD_REUSE_MAX 8
CPU_PER_SESSION 9000;

23. Attribuer le profile datamager à l’utilisateur maialawa

alter user c##datamager profile c##maialawa;

24. Sur le serveur Oracle 1561, sélectionner les utilisateurs ayant des privilèges élevés
25. Sur le compte de l’utilisateur kazalma, assigner 5 jours d’interdiction d’accès après que le nombre de
tentatives de connexions a été atteint
26. Créer le rôle consultant et lui assigner les privilèges système suivants : CREATE SESSION, CREATE
FUNCTION, CREATE VIEW, CREATE SESSION.
27. Attribuer le rôle consultant à l’utilisateur kazalma
28. Donner l’instruction qui permet d’auditer toute tentative de connexion ayant échoué sur le serveur
Oracle
29. Pour obliger les utilisateurs à changer leurs mots de passe, écrire un script Python qui parcourt une liste
d’utilisateurs saisis dans le fichier utilisateur.txt ; et fait expirer leurs mots de passe. Ensuite, donner
l’instruction qui planifie l’exécution de ce script à chaque début du mois
30. Sur le serveur Oracle, donner l’instruction qui permet de désactiver l’utilisateur maialawa
31. Depuis la machine cliente, écrire un script Python qui permet de répliquer les données de la table
TRANSFERT du serveur Oracle ayant le port public 1562 vers le serveur PostgreSQL ayant le port 5461
32. Ecrire un script Python qui permet de centraliser les données de la table VOYAGER du serveur MySQL
ayant le port public 3362 et du serveur Oracle ayant le port public 1562, dans la table VOYAGER du
serveur PostgreSQL ayant le port 5460.
33. Ecrire un script ou requête SQL qui retourne la taille de la tablespace du serveur Oracle 1562
34. Ecrire un script Shell qui permet de faire le backup des bases de données TRANSFERT des serveurs de
production. Et copier chaque fichier sur le serveur de fichier 193.125.101.18 du cloud ; via SSH.
35. Ecrire un script qui permet de vérifier l’espace disque des serveurs de production de transfert d’argent. Si
l’espace disque utilisé est supérieur à 85% alors alerter l’administrateur via [email protected]
36. La requête suivante permet de lister les utilisateurs dont les comptes sont ouverts mais ont passé six(6)
mois sans se connecter :
SELECT username, account_status FROM dba_users
WHERE account_status = 'OPEN' AND last_login < SYSDATE - 180;
Pour cela, écrire un script Python qui sélectionne ces utilisateurs sur les tous les serveurs Oracle, et
enregistre le résultat de chaque serveur dans un fichier diffèrent.
37. Ecrire un script Python qui parcourt chaque serveur Oracle et purge les logs d’audit datés d’un an de cela :
DELETE FROM dba_audit_trail WHERE timestamp < SYSDATE - 365;
38. Ecrire un script Python qui surveille les requêtes longues et suspectes sur les serveurs Oracle, voici la
requête :
SELECT sql_id, sql_text, elapsed_time, username FROM v$sql WHERE elapsed_time > 1000000;

Exercice: Gestion de ventes

Utilisation de conteneurs Docker

73
74