TP1: BASE DE DONNÉES AVANCÉES

 Langage de définition de données(LDD)

 Langage de manipulation de données (LMD)

 Fonctions D’agrégation basiques

Introduction

Ce TP contient 2 parties, la première concerne la création de schéma de la base de données gestion de la

Musique, dont les tables sont décrites dans le schéma logique de la figure 1, dans cette partie vous aller
manipuler les principales instructions de langage de définition de données (LDD). Tandis que la deuxième
partie concernera l’utilisation de langage de manipulation de données (LMD) la consultation de la base de
données en utilisant des sélections simples, de jointure et quelque fonction d’agrégation.

Important : Un compte rendu est à rendre à la fin de la séance

Partie 1 : Création de l’utilisateur et de la base de donnée

1- Se connecter en tant que super-utilisateur :

- Lancer CMD command line à partir de Windows vous aurez l’invite de commande suivant : C:\
Windows\system32>
- Taper ensuite la commande : sqlplus / as sysdba
- Vous aurez le prompt suivant : SQL>
2- Créer votre Utilisateur par l’instruction SQL suivante :

CREATE USER TP_votre_nom IDENTIFIED BY estm_ora

DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK ;

( ici l’utilisateur est : TP_votre_nom et son mot de passe est estm_ora )

Attention le mot de passe doit respecter la casse).
3- Autoriser la connexion et les ressource à l’utilisateur que vous venez de créer :

La commande est : Grant connect, resource to TP_votre_nom ;

4- Se connecter maintenant en tant que TP_votre_nom

5- Créer les tables de schémas logique ci-dessous (figure 1) , sachant que :
P= cette attribut représente la clés primaire ( Primary key )
F= cette attribut représente la clé étrangère Foring Key migrante de la table en relation.
FP= cette attribut est à la fois clés primaire et clé étrangère migrante de la table en relation.

1
 Figure 1 Le Schéma logique de la base de donnée Musique
NB : vous avez deux astuces (comme nous avons fait dans la séance de TD):

- Soit vous créer directement les tables et les clés en même temps
- Soit vous laisser les clés après la création des table puis rajouter les en utilisant la création des
contraintes sur les attributs concernés.

Important : Chargement de données dans les tables :

- Vous vous servez de fichier donnée_minim.txt ( en Annexe) qui contient les données pour
chargement de la base.
- Vous pouvez visualiser ce fichier il contient des INSERT INTO NOM_TABLE VALUES
- Vous êtes invité à vérifier le résultat de l’insertion dans chaque tables par la commande selecet *
from mon_de_la_table

2. Partie II

2.1 Requêtes simples (sans jointures)

- Écrire les requêtes qui permettent d’afficher :

6- Le n-uplet correspondant à l’album d’identifiant (ASIN) ‘10’.

7- Les titres des albums réalisés par l’artiste numéro (id) ’2’;
8- Les albums dont le prix est inférieur à 9 euros.
9- Les albums qui sont sortis après le 18 mai 1999.
10- Les Id des artistes dont l’album a atteint un rank supérieur ou égal à 25000.
11- Les titres et Id artiste des albums dont le titre se termine par la lettre ’e’ et le prix (Price) est supérieur
à 9 euros.
2
2.2 Jointures simples

- Écrire les requêtes qui permettent d’afficher :

12- Les titres des albums et le nom des artistes ayant des albums dont le titre commence par la lettre ’T’
et dont le prix (Price) est situé entre 10 et 20 euros.
13- Donner les noms des artistes ayant sortis un album classé entre la position (rank) 4000 et 20000, trié
par date de sortie.
14- Les titres (song) des chansons de l’album ’ Careless World’;
15- Le nom des artists ayant un label contenant le mot ’sony’
16- Les trois albums ayant les pris les plus bas (utiliser rownum qui numérote les lignes du résultat).
17- Le titre de l’album, le titre de sa première chanson (num), son style, pour les albums de rang (rank)
inférieur à 30000.
2.3 Agrégats simple :

Dans cette partie vous utiliserez les fonctions d’agrégation: (Count, avg, min,…)
- Écrire les requêtes qui permettent d’afficher :
18- Le nombre d’albums stockés dans la base ;
19- Le prix moyen d’un album;
20- Les titres des albums ayant le prix le plus bas ;
Annexe :
Le contenu de fichier
INSERT INTO ARTIST VALUES (0,'Harich');
INSERT INTO ARTIST VALUES (1,'Fank');
INSERT INTO ARTIST VALUES (2,'Tomas Hich');
INSERT INTO ARTIST VALUES (3,'Truch Oye');
INSERT INTO ARTIST VALUES (4,'Bob Marley');
INSERT INTO ARTIST VALUES (5,'Lil Wayne ');
INSERT INTO ARTIST VALUES (6,'Nana');
INSERT INTO ARTIST VALUES (7,'Khaled');
INSERT INTO ARTIST VALUES (8,'Armine');
INSERT INTO ARTIST VALUES (9,'Lobe');
INSERT INTO ARTIST VALUES (10,'Farih');
INSERT INTO ARTIST VALUES (11,'Tigh');

INSERT INTO LABEL VALUES (0,'Capitol');

INSERT INTO LABEL VALUES (1,'sony');
INSERT INTO LABEL VALUES (2,'saycgvx');
INSERT INTO LABEL VALUES (3,'Capitol2');
INSERT INTO LABEL VALUES (4,'vxi');
INSERT INTO LABEL VALUES (5,'Capitol3');
INSERT INTO LABEL VALUES (6,'Musicson');
INSERT INTO LABEL VALUES (7,'sony');
INSERT INTO LABEL VALUES (8,'sony55');

3
INSERT INTO LABEL VALUES (9,'SAYXOF');
INSERT INTO LABEL VALUES (10,'Early');
INSERT INTO LABEL VALUES (11,'Yong');

alter session set nls_date_format= 'YYYY-MM-DD'

commit
INSERT INTO ALBUM VALUES (0,'Tha Carter III',2,24,'1986-03-22',41652,0);
INSERT INTO ALBUM VALUES (1,'boilxoir',0,11,'1987-03-11',11134,10);
INSERT INTO ALBUM VALUES (2,'No Introduction',11,19,'1996-12-15',17795,9);
INSERT INTO ALBUM VALUES (3,'So Far Gone',10,29,'1995-07-2',23786,8);
INSERT INTO ALBUM VALUES (4,'Comme Hier',9,19,'1986-01-1',21651,7);
INSERT INTO ALBUM VALUES (5,'Rebirth',4,8,'2000-06-20',33677,7);
INSERT INTO ALBUM VALUES (6,'Thank Me Later',7,13,'2001-11-22',38893,6);
INSERT INTO ALBUM VALUES (7,'Good Bye',4,6,'1988-09-21',42340,6);
INSERT INTO ALBUM VALUES (8,'saturday',4,7,'2002-05-27',38506,5);
INSERT INTO ALBUM VALUES (9,'Pink Friday',2,13,'2005-04-7',4231,5);
INSERT INTO ALBUM VALUES (10,'Tha Carter IV',5,13,'1981-02-21',18445,5);
INSERT INTO ALBUM VALUES (11,'Careless World',4,29,'1988-04-12',17001,6);

commit;

INSERT INTO TRACK VALUES (1, 1, 1, 'zf');

INSERT INTO TRACK VALUES (1, 4, 2, 'idkyhqu');
INSERT INTO TRACK VALUES (1, 4, 3, 'tzee');
INSERT INTO TRACK VALUES (11, 1, 4, 'kgewl');
INSERT INTO TRACK VALUES (1, 1, 5, 'leyhcts');
INSERT INTO TRACK VALUES (11, 1, 6, 'himisn');
INSERT INTO TRACK VALUES (11, 1, 7, 'vfqghxwc');
INSERT INTO TRACK VALUES (11, 1, 8, 'hqqlgy');
INSERT INTO TRACK VALUES (2, 2, 1, 'wde');
INSERT INTO TRACK VALUES (2, 6, 2, 'bemcype');
INSERT INTO TRACK VALUES (2, 7, 3, 'mjewcljp');
INSERT INTO TRACK VALUES (2, 6, 1, 'wvyw');
INSERT INTO TRACK VALUES (3, 3, 2, 'eua');
INSERT INTO TRACK VALUES (3, 1, 1, 'tttvl');
INSERT INTO TRACK VALUES (3, 1, 2, 'ijhjgy');
INSERT INTO TRACK VALUES (4, 1, 1, 'tttvlw');
INSERT INTO TRACK VALUES (4, 1, 2, 'ijhjgy');
INSERT INTO TRACK VALUES (4, 1, 3, 'vcmywgam');
INSERT INTO TRACK VALUES (5, 1, 4, 'okcetshaa');
INSERT INTO TRACK VALUES (5, 2, 1, 'qvnzwaix');
INSERT INTO TRACK VALUES (5, 2, 2, 'qmipykza');
INSERT INTO TRACK VALUES (5, 2, 3, 'ybyxntysn');
INSERT INTO TRACK VALUES (5, 2, 4, 'hnvevneegjdm');
INSERT INTO TRACK VALUES (5, 2, 5, 'oi');
INSERT INTO TRACK VALUES (6, 2, 6, 'bqbzuwrv');
INSERT INTO TRACK VALUES (6, 2, 7, 'zluyvvo');
INSERT INTO TRACK VALUES (7, 2, 8, 'kyb');
INSERT INTO TRACK VALUES (7, 2, 9, 'hapzi');
INSERT INTO TRACK VALUES (7, 2, 10, 'dxguetsm');
INSERT INTO TRACK VALUES (7, 2, 11, 'lcjrr');
INSERT INTO TRACK VALUES (7, 3, 1, 'glorlxyl');

4
INSERT INTO TRACK VALUES (7, 3, 2, 'iuijyo');

commit;

INSERT INTO STYLES VALUES ('JAZ');

INSERT INTO STYLES VALUES ('Blues');
INSERT INTO STYLES VALUES ('HIPO');
INSERT INTO STYLES VALUES ('regada');
INSERT INTO STYLES VALUES ('tawtaw');
INSERT INTO STYLES VALUES ('tara');
INSERT INTO STYLES VALUES ('RAP');
INSERT INTO STYLES VALUES ('Classical');
INSERT INTO STYLES VALUES ('HIPHOP');
INSERT INTO STYLES VALUES ('ROCK');
INSERT INTO STYLES VALUES ('Samba');
INSERT INTO STYLES VALUES ('samsam');
INSERT INTO STYLES VALUES ('Linga');
INSERT INTO STYLES VALUES ('samba');
INSERT INTO STYLES VALUES ('Bitho');
INSERT INTO STYLES VALUES ('Tamja');
commit
INSERT INTO STYLE VALUES(1, 'ROCK');
INSERT INTO STYLE VALUES(2, 'HIPHOP');

INSERT INTO STYLE VALUES(3, 'Classical');

INSERT INTO STYLE VALUES(4, 'Blues');
INSERT INTO STYLE VALUES(5, 'Blues');
INSERT INTO STYLE VALUES(6, 'HIPHOP');
INSERT INTO STYLE VALUES(7, 'Classical');
INSERT INTO STYLE VALUES(8, 'Blues');
INSERT INTO STYLE VALUES(9, 'Blues');
INSERT INTO STYLE VALUES(10, 'Classical');
INSERT INTO STYLE VALUES(0, 'ROCK');
INSERT INTO STYLE VALUES(11, 'RAP');

5
 TP2 : BASE DE DONNÉES AVANCÉES

Requêtes SQL avancées

- Langage de manipulation de données (LMD)

- Fonctions d’agrégation (count, sum, max, min, avg)
- Requêtes imbriquées.
- La clause having

Introduction
Durant ce TP, vous allez écrire des requêtes SQL d’interrogation en utilisant des
constructions plus compliquées telles que les fonctions d’agrégation, group by,
having, et les requêtes imbriquées.
Préparation de l’environnement
Se connecter à votre schéma (ORA_votre_nom) le créer de nouveau s’il n’existe plus :
1- Créer la base de données dont la structure est ci-dessous (avant de créer les table visualiser le type de
données de chaque attribut à partir des données contenues dans la fin de cette Enoncée
2- Alimenter la base de données, à partir des commandes insert into. contenus dans la fin de cette
Enoncée
Structure de Schéma importé
- les Gras sont des clés
Le schéma de la base que vous venez d’importer est :
primaires
EQUIPE(code_equipe,nom,directeur)
- les * sont des clés
PAYS(code_payes,nom)
étrangères
COUREUR(num_dossart,code_equipe*,nom,code_pays*)
- les Gras et* sont à la fois
ETAPE(num_etape,date_etape,kms,ville_depart,ville_ar
clés primaires et estrangères
rivee)
TEMPS(num_dossart*,num_etape*,temps_realise)

Remarque : la table TEMPS ne stocke que les temps des joueurs qui ont participé à l’étape. Si un coureur
déclare forfait pour une étape, son temps n’apparait pas.

I. Fonctions d’agrégation (count, sum, max, min, avg)

3. Donnez le meilleur et le pire temps de l’étape 1.
4. Donnez le nombre de coureurs de l’équipe 'TMT'.
5. Donnez nombre d’étapes et le temps total effectués par 'CHAVANEL Sylvain'.
6. Donnez la moyenne de temps mis pour chaque étape.
II. Group by, having

6
 7. Donnez le nombre d’étapes effectuées pour chaque coureur. Compléter la requête en ordonnant
les résultats par ordre croissant du nom des coureurs. Modifier la requête de sorte de ne considérer
que les temps supérieurs à 2h. Compléter la requête en ne gardant que les coureurs qui ont
effectués au moins une étape. Quelle est la différence entre la clause WHERE et la clause
HAVING ?
8. Donnez le code et le nom des pays ayant plus d'un coureur, ainsi que le nombre de coureurs par
pays, classé par ordre alphabétique croissant des noms de pays.
9. Donnez le nom des coureurs dont le temps total (somme du temps mis pour chaque étape) est
inférieur à 9h00, classé par temps total croissant.
III. Requêtes imbriquées
10. Donnez le nom des joueurs qui n'ont pas couru l'étape 2.
11. Donnez le nom et le temps du dernier coureur arrivé pour chaque étape
12. Donnez les coureurs qui n'ont pas gagné (autrement dit tous les coureurs sauf le premier) pour
chaque étape.
13. Donnez le 2e meilleur temps pour l'étape 1.
14. Donnez le top 3 des coureurs pour chaque étape.
15. Donner les Noms des coureurs dont la première lettre est identique à celle d'un autre joueur

insert into equipe values('TMT','T-MOBILE TEAM','Mario Kummer');

insert into equipe values('BLB','Brioche la Boulangere','Jean-Rene Bernaudeau');
insert into equipe values('USP','US Postal Service', 'Berry Floor' );
insert into pays values('FRA','France');
insert into pays values('EU','Etats Unis');
insert into pays values('CAN','Canada');
insert into pays values('ESP', 'Espagne');
insert into pays values('ALL','Allemagne');
insert into pays values('ITA', 'Italie');
insert into coureur values(1,'HIEKMANN Torsten','TMT','ALL');
insert into coureur values(2,'GUERINI Giuseppe','TMT','ITA');
insert into coureur values(3,'REICHL Dirk', 'TMT','ALL');
insert into coureur values(4,'CHAVANEL Sylvain','BLB','FRA');
insert into coureur values(5,'ROUS Didier','BLB','FRA');
insert into coureur values(6,'YUS QUEREJETA Unai','BLB','ESP');

7
insert into coureur values(7,'ARMSTRONG Lance','USP','EU');
insert into coureur values(8,'McCARTHY Patrick','USP','EU');
insert into coureur values(9,'BARRY Michael','USP','CAN');
insert into etape values(1,to_date('06/07/2008','DD/MM/YYYY'),197,'Brest','Plumelec');
insert into etape values(2,to_date('07/07/2008','DD/MM/YYYY'),164,'Plumelec','Saint-Brieuc');
insert into etape values(3,to_date('08/07/2008','DD/MM/YYYY'),208,'Saint-Malo','Nantes');
insert into temps values(1,1,15123);
insert into temps values(3,1,14881);
insert into temps values(4,1,15123);
insert into temps values(5,1,15321);
insert into temps values(6,1,15330);
insert into temps values(7,1,15142);
insert into temps values(8,1,15493);
insert into temps values(9,1,15603);
insert into temps values(1,2,13873);
insert into temps values(2,2,13563);
insert into temps values(3,2,13703);
insert into temps values(4,2,13810);
insert into temps values(5,2,13688);
insert into temps values(6,2,13742);
insert into temps values(8,2,13793);
insert into temps values(9,2,13644);
insert into temps values(1,3,18313);
insert into temps values(2,3,18603);
insert into temps values(3,3,18203);
insert into temps values(4,3,18010);
insert into temps values(5,3,18488);
insert into temps values(7,3,18392);
insert into temps values(9,3,18444);
commit;

8
 TP3 : BASE DE DONNÉES AVANCÉES

SQL avancé :

- Vues
- Séquencés
- synonymes
- transaction ( verou, savepoint, commit,
rollback)

Introduction
Durant ce TP, vous allez appréhender le rôle des objets oracle à savoir les vues, les
séquences et les synonymes, vous allez aussi toucher de prés la gestion des
transactions et de verrou et le rôle de la commande savepoint .
Important : Dans tout le TP vous utiliser le schéma HR , si vous avez oublier le
mot de passe, procéder comme suit pour l’initialiser :
Commande cmd
C:\Users\hp>sqlplus / as sysdba
SQL> alter user hr identified by hr123 account unlock;

- Après cette commande vous avez initialisé le schéma hr avec le mot de

passe hr123
- Vous pouvez faire la même chose à tout utilisateur dont vous
avez oublié le mot de passe !

I- Vues
1. Le service de la paie a besoin d'accéder régulièrement à des informations concernant le salaire
des employés. Le DBA de la société vous a ordonné de créer une vue nommé vwSalary et basée
sur la table ‘EMPLOYEES’. La vue devrait inclure numéro de l'employé( Employee_id) , son
nom et son prénoms et son salaire . Nommez les colonnes de la vue comme suit: EmpID,
EmpLastName, EmpFirstName, et salaireemp. Ecrire le code SQL nécessaire pour créer cette
vue. Ecrire une instruction SELECT pour afficher les lignes de la vue pour les employés avec des
salaires égaux ou supérieurs à $ 3000.
2. Remplacez la vue nommée vwSalary créé en 1 avec une nouvelle vue (même nom), qui comprend
également la colonne ‘Department_id’. Nommez cette colonne ‘departement’ dans la nouvelle

9
 vue. Ecrire une instruction SELECT pour afficher les lignes de la vue contenant les employés de
département 80 ayant salaire >= à 1000 $.
3. Créer une vue nommé VU_IT_PG qui contient les enregistrements de la table EMPLOYEES dont
le job_id est IT_PROG
a. Afficher le salaire des IT_PROG
b. Essayer de lancer la requête suivante
Update VU_IT_PG set salary=6000.50 ; commit ;
c. Quel est le salaire actuel des IT_PROG ?
3. Vue avec l’option : WITH CHECK OPTION
 Problème
3.1 On crée tout d’abord la vue ci-dessous l’ordre SQL correspondant
Create view Nom_Emp as select * from EMPLOYEES where firts_name
like ‘John’

3.2 Puis on essaye d’insérer un enregistrement dans la table EMPLOYEES via la vue, ci-
dessous l’ordre SQL correspondant

insert into Nom_Emp values (6666,'MOHAMED','BARAK','MB@gmail',

'777','17/06/15', 'AD_PRES’,'24000',NULL,NULL,'90');

Est-ce que la ligne est insérer ?

Résultat : La vue est sensé traiter juste les employés ayant le prénom ‘Jonh’’, alors qu’elle a pu
insérer un employé de prénom ‘'MOHAMED', et pourtant l’insertion a réussi
Attention on se retrouve dan la table employées avec des données non souhaitées

3.3 Que ce que va se passer en cas d’utilisation des ordres Update et delete sur la vue et la table
d’origine. Essayer l’order update et delete via la vue et verifier
- que remarquer vous ?
- faites aprés rollback pour annuler.

3.4 Supprimer la ligne insérer et aussi vue Nom_Emp

 Solution
La solution pour ce genre de problème est La clause WITH CHECK OPTION. Elle permet
d'éviter ce type d’insertion. En effet, la clause « with check option » doit être déclaré dans l’ordre
SQL de création de vu comme suit :

3.5 créer la vu avec clause WITH CHECK OPTION

10
 - Create view Nom_Emp as select * from EMPLOYEES where first_name like ‘
John’ with check option

3.6 insérer un enregistrement dans la table emplyees via la vue crée (Nom_Emp) dans 4.5

insert into Nom_Emp values(6666, 'MOHAMED', 'MOHAMED', 'BARAK','777', '17/06/15',

'AD_PRES’,'24000',NULL,NULL,'90');

3.7 Qu’est ce que se passe après l’exécution de l’insertion ?

3.8 : Insérer un enregistrement valable?

4. Vue avec l’option : WITH READ ONLY

a. Redéfinir la vue de créée en 3 ‘VU_IT_PG’ en lecture seule avec le meme contenu
(les enregistrements de la table EMPLOYEES dont le job_id est IT_PROG)
b. Essayer de lancer la requête suivante
Update VU_IT_PG’ set salary=5000.50

Qu’est-ce que ça passe? Et pourquoi ?

c. Créer une vue en lecture seule nommée lecseul_job_emp contenant le nom, premon des
employés qui ont occupé 2 fonctions ou plus ( 2 Jobs), les tables de base sont
JOB_HISTORY , et EMPLOYEES. Faites des select sur cette vue.
II- Synonymes
1- Se connecter en tant que sysdba, et créer des synonymes public aux tables de shéma HR
comme suit : PEMP pour la table EMPLOYEES, PJO pour la table JOBS , et PDEP pour la
table DEPARTEMENTS.
2- Les synonymes publique ne vont pas marcher si les autres utilisateurs n’ont pas le droit sur les
table pour cela faites :

-sqlplus / as sysdba
- grant select on [Link] to DIP ;
- grant select on [Link] to DIP ;
- grant select on [Link] to DIP ;
Faites des select et des d’autre ordre SQL en utilisant ces synonymes public, essayer de se connecter
par plusieurs utilisateurs (l’utilisateur DIP, ou autre utilisateur)

11
 1- Dans le schémas HR Créer les synonyme privés suivants : EM pour la table EMPLOYEES,
JO pour la table JOBS , et DEP pour la table DEPARTEMENTS

Faite des select et des d’autre ordre SQL en utilisant ces synonymes. Vérifier que le synonyme privé
n’est pas valide dans les autres schémas

Que remarquer vous ?

III- Séquences
Dans cette partie

- vous vous connecter en tant que ORA_ESTM votre schéma créer dans la 1 er séance de TP,
s’il n’existe plus créer le de nouveau
- Créer les deux tables suivantes :
DEPARTEMENT ( id_dep,nom_dept,id_manager, date_affc_manager)
Avec (id_dep : clé primaire)
PROJET(Num_projet,nom_projet, localisation, id_dep_proj*)
Avec ( Num_projet clé primaire et id_dep_proj clé étrangère référence
DEPARTEMENT (id_dep ) )

1. Le Manager des ressources désire numéroter le nouveau département de l’entreprise de façon

séquentielle en commençant par le numéro 40. La numérotation des départemenst sera
incrémentée de 1. Écrire le code nécessaire pour créer cette sequence, la séquence portera le
nom ‘DepartmentSequence’
2.
a- Ecrire l’order SQL pour insérer deux nouvelles lignes dans la table DEPARTEMENT en
utilisant la sequence ‘DepartmentSequence’ créee dans question 1. Les autres
informations à insérer sont indiquées ci-dessous:
 nom_dept = 'Medical Surgical Ward 2’
 nom_dept = ‘Gerontology’
 id_Manager = NULL pour les deux enregistrement
 date_affc_manager = NULL pour les deux enregistrement

b- Ecrire une instruction SELECT pour afficher toutes les lignes de la table de département
trié par id_dep pour les numéros de département supérieures ou égales à 10.
3.
a- Écrire une commande pour insérer un nouveau projet dans la table du PROJET. Ce
projet sera contrôlé par le département de ‘Gerontology’. Vous pouvez utiliser la
séquence ‘DepartmentSequence’ créé et utiliseé dans la question 1 et 2 (puisque c’est

12
 le dernier département inséré dans la table DEPARTEMENT) pour insérer la nouvelle
ligne dans la table PROJET. Les autres informations à insérer sont représentées ci-
dessous.
b- Écrire une instruction SELECT pour afficher la ligne de projet 55. Comparer aux
numéros de départements énumérés à la question 12. S’assurer que la valeur de
id_dep_proj stockée soit correct par rapport au department correspondant (ie
‘Gerontology’) .
 Num_projet = 55
 nom_projet = 'New Inventory Sys'
 localisation = 'Alton'
 id_dep_proj = (généré par le sequence)

IV- Transaction
Une transaction (ensemble d’ordres SQL) est atomique c’st-`a-dire qu’elle ne peut se terminer que
par un sucées (elle est alors validée) ou par un échec (tous ses effets sont alors détruits).

En conséquence, en contexte multi-utilisateurs, les modifications effectuées par une transaction

réalisée par un utilisateur ne sont connues des autres utilisateurs que lorsque la transaction a été
confirmée par un COMMIT.

Oracle gère automatiquement les accès concurrents. Si une transaction est en train de modifier les
lignes d’une table, les autres transactions peuvent modifier les données telles qu’elles étaient avant
ces dernières modifications (pas de temps d’attente pour la lecture).

Pour rester “simple” nous dirons que toute transaction pose des verrous sur les objets qu’elle
manipule et que deux grands types de verrous existent :

– en lecture (verrou passant plusieurs lectures simultanées peuvent avoir lieu)

– en écriture (verrou bloquant la première écriture bloque les autres jusqu’`a ce que le verrou soit
relâché)
Commandes qui provoquent un blocage implicite sur les tables et les lignes impliquées sont :

DELETE, INSERT, UPDATE, ALTER TABLE, ...

1- Faites des sélections sur les mêmes lignes des mêmes tables avec deux session en se
connectant au schéma rh (deux fenêtre cmd S1 et S2 comme illustrer dans le tableau ci-
dessous)

13
Session S1 Session S2

a- Par exemple, sur S1 et S2 réalisez la même requête qui est :

“Donnez le nom et la date d’embauche des Employés” sur la table EMPLOYEES.

2- Le rôle de commit et rollback

a- Apartir de la session S1
- faites un select sur le salair et le nom l’employé ayant employee_id=105
- augmenter le salair de l’employé employee_id=105 de 50% (update)
- faite un select sur le même employé pour vérifier est ce que le salaire est bien changé
b- Puis de S2 réaliser la même requête : faite un select sur le même employé pour vérifier
est ce que le salaire est bien changé

Que constatez-vous sur S2 ? Et pourquoi ?

c- Revenez à S1 et faites COMMIT

d- Sur S2 : faite un select sur le même employé pour vérifier est ce que le salaire est bien
changé
Que constater vous ?

14
 3- Réessayons avec des commandes provoquant des blocages (verrou)
a- A partir de la session S1 modifie la table EMPLOYEES: “Modifiez le nom (first_name)
de l’employé ‘Lex’ en ‘Lexis’.
b- Puis de S2 réaliser la requête suivante : augmenter le salaire de l’employé
employee_id=102 de 50%
c- Que constater vous sur S2 ? Expliquer ?
d- Revenez à S1 et faites COMMIT ou ROLLBACK.
Que constater vous sur S2 ? Expliquer ?

4 – savepoint et rollback to savepoint :

a- Se connecter en tant que HR et faite les commandes suivantes en un seul coup

1- Update EMPLOYEES set first_name=’TANTA’ where employee_id=107 ;

2- insert into EMPLOYEES values(11111,'KINGSTON','POLO','KP@gamil','777', '17/06/15',
'AD_PRES’,'24000', NULL,NULL,'90');
3- savepoint sav_num1 ;
4- delete from EMPLOYEES where employee_id=105;
5- Update EMPLOYEES set first_name=’TOPAK’ where emplyee_id=108 ;
6- savepoint sav_num2 ;
7- insert into DEPARTMENTS valeus (270, ‘DSI’, null,1700) ;

b- Faite des select pour vérifier toute ces mise à jour

c- Taper et exécuter ensuite la commande : rollback to sav_num1
d- Refaire des select et décrire qu’est ce que s’est passé après rollback to sav_num1

CORRECTION TP1: BASE DE DONNÉES AVANCÉES

 Langage de définition de données(LDD)

15
 Langage de manipulation de données (LMD)

 Fonctions D’agrégation basiques

Partie 1 : Création de l’utilisateur et de la base de donnée

1- Se connecter en tant que super-utilisateur :

2- Créer votre l’tilisateur :

3- Autoriser la connexion et les ressource à l’utilisateur crée :

4- Se connecter maintenant en tant que anass :

5- Créer les tables de schémas logique :

16
Partie 2 :

Se connecter en tant que anass dans sql developer

2.1 Requêtes simples (sans jointures)

6- Le n-uplet correspondant à l’album d’identifiant (ASIN) ‘10’ :

7- Les titres des albums réalisés par l’artiste numéro (id) ’2’ :

17
8- Les albums dont le prix est inférieur à 9 euros :

9- Les albums qui sont sortis après le 18 mai 1999 :

10- Les Id des artistes dont l’album a atteint un rank supérieur ou égal à 25000 :

18
11- Les titres et Id artiste des albums dont le titre se termine par la lettre ’e’ et le prix (Price) est supérieur à 9
euros :

II.2 Jointures simples

12- Les titres des albums et le nom des artistes ayant des albums dont le titre commence par la lettre ’T’ et
dont le prix (Price) est situé entre 10 et 20 euros :

13- Donner les noms des artistes ayant sortis un album classé entre la position (rank) 4000 et 20000, trié par
date de sortie :

14- Les titres (song) des chansons de l’album ’ Careless World’ :

19
15- Le nom des artists ayant un label contenant le mot ’sony’ :

16- Les trois albums ayant les pris les plus bas :

17- Le titre de l’album, le titre de sa première chanson (num), son style,

pour les albums de rang (rank) inférieur à 30000 :

2.3 Agrégats simple :

18- Le nombre d’albums stockés dans la base :

19- Le prix moyen d’un album :

20
 21- Les titres des albums ayant le prix le plus bas :

TP2 : BASE DE DONNÉES AVANCÉES

Requêtes SQL avancées

- Langage de manipulation de données (LMD)

- Fonctions d’agrégation (count, sum, max, min, avg)
- Requêtes imbriquées.
- La clause having

1- Créer la base de données :

 Table EQUIPE:
create table equipe (code_equipe varchar2(3) primary key, nom varchar2(21), directeur varchar2(20));
Table PAYS:
create table PAYS(code_payes varchar2(3) primary key ,nom varchar2(10));
 Table ETAPE:
create table ETAPE(num_etape number(1) primary key ,date_etape date,kms number(3),ville_depart
varchar2(10),ville_arrivee varchar2(12));
 Table COUREUR:
create table COUREUR(num_dossart number(1) primary key ,nom varchar2(19),code_equipe
varchar(3),code_pays varchar2(3));
 Table TEMPS:
create table TEMPS(num_dossart number(1),num_etape number(1) ,temps_realise number(6), primary
key(num_dossart, num_etape));

alter table temps add constraint fk3 foreign key (num_dossart) references coureur(num_dossart);

21
alter table temps add constraint fk4 foreign key (num_etape) references etape (num_etape);

2- alimenter la base de données en lançant des insert into jointes à ce TP ( voir vers la fin de l’énoncée)

I. Fonctions d’agrégation (count, sum, max, min, avg)

3. Donnez le meilleur et le pire temps de l’étape 1 :

4. Donnez le nombre de coureurs de l’équipe 'TMT' :

5. Donnez nombre d’étapes et le temps total effectués par 'CHAVANEL Sylvain' :

6. Donnez la moyenne de temps mis pour chaque étape :

22
7. Donnez la moyenne de distance mise pour chaque étape :

II. Group by, having

8. Donnez le nombre d’étapes effectuées pour chaque coureur. Compléter la requête en ordonnant les
résultats par ordre croissant du nom des coureurs. Modifier la requête de sorte de ne considérer que les
temps supérieurs à 2h. Compléter la requête en ne gardant que les coureurs qui ont effectués au moins une
étape. Quelle est la différence entre la clause WHERE et la clause HAVING ?

La différence entre where et having est que having est la condition appliquée sur les
groupements (group by) par contre le where est utilisée comme condition initiale.

23
9. Donnez le code et le nom des pays ayant plus d'un coureur, ainsi que le nombre de coureurs par pays,
classé par ordre alphabétique croissant des noms de pays :

10. Donnez le nom des coureurs dont le temps total (somme du temps mis pour chaque étape) est inférieur
à 9h00, classé par temps total croissant :

24
III. Requêtes imbriquées
11. Donnez le nom des joueurs qui n'ont pas couru l'étape 2 :

12. Donnez le nom et le temps du dernier coureur arrivé pour chaque étape :

13. Donnez les coureurs qui n'ont pas gagné (autrement dit tous les coureurs sauf le premier) pour chaque
étape :

25
14. Donnez le 2e meilleur temps pour l'étape 1 :

16. Donnez le top 3 des coureurs pour chaque étape :

1er solution
Select * from (select num_dossart, num_etape from temps where num_etape=1
order by temps_realise) where rownum<=3
Union all(Select * from (select num_dossart, num_etape from temps where
num_etape=2 order by temps_realise) where rownum<=3)
Union all(Select * from (select num_dossart, num_etape from temps where
num_etape=3 order by temps_realise) where rownum<=3);

26
16. Donner les Noms des coureurs dont la première lettre est identique à celle d'un autre joueur :

27