TD sur les requtes SQL - exos-corrigs SQL SGBDR TP avec

correction
SQL 0

Examen Corrig SGBDR - Langage SQL
Prrequis : Modle conceptuel de donnes (entit-association), modle relationnel, bases du langage SQL.
TD 1 - Requtes SQL


Description du systme dinformations

La direction des tudes des Mines de Nancy a dcid dinformatiser la gestion des emplois du temps. Chaque
tudiant est caractris par son numro dtudiant, son nom, son prnom et son ge. Chaque cours est identifi
de faon unique par un sigle (SI033, MD021, . . . ) et possde un intitul (bases de donnes, mathmatiques
discrtes, . . . ) ainsi quun enseignant responsable. On connat galement le nombre de sances de chaque
cours. Les enseignants sont caractriss par un identifiant alphanumrique, leur nom et leur prnom. Enfin,
chaque sance est identifie par le cours ainsi que le numro de la sance (sance 3 du cours SI033, sance 1
du cours de MD021, . . . ), le type dintervention (CM, TD, TP), la date, lheure de dbut et lheure de fin
auxquelles la sance a lieu ainsi que la salle et lenseignant qui dispense la sance. Les tudiants
sinscrivent aux cours auxquels ils souhaitent assister.

Schma relationnel retenu

Les cls primaires sont soulignes et les cls trangres sont en italique.
--------------------------------------------------------------------------------------
etudiant ( numero , nom , prenom , age )
enseignant ( id , nom , prenom )
cours ( sigle , intitule , responsable, nombreSeances )
seance ( cours , numero , type , date , salle , heureDebut , heureFin , enseignant )
inscription ( etudiant , cours )
--------------------------------------------------------------------------------------

Requtes simples

i) crire les requtes de cration des tables Etudiant et Sance .
ii) Inscrivez ltudiant (l0372,Lponge,Bob,20) au cours (LOG015,Logique,jh1908).
iii) Cherchez le nom et le prnom de tous les tudiants de moins de 20 ans.
iv) Cherchez le nom et le prnom de lenseignant responsable du cours de Statistiques.
v) Cherchez le nom et le prnom de tous les tudiants inscrits au cours de Probabilits.
vi) Dterminez le nombre denseignants intervenant dans le cours de Modlisation Stochatique.
vii) O et quand a lieu le premier cours dAlgbre linaire ?
viii) Affichez un emploi du temps du cours de Logique.
ix) Pour chaque enseignant, indiquez le nombre de cours dans lesquels il intervient (restreignez les rponses
lensemble des enseignants qui interviennent dans au moins deux cours).
Requtes imbriques

i) Ajoutez un cours magistral de Logique le 14 dcembre avec Jacques Herbrand en salle S250 de 14h 18h.

ii) Listez les tudiants inscrits aucun cours.
iii) Combien dtudiants (diffrents) ont assists au moins une sance anime par Leonhard Euler ?
Syntaxe SQL
Slection





Exemple :

SELECT SUM(p.gain)
FROM Participe p, Jockey j
WHERE p.Numero_jockey = j.Numero_jockey
AND j.nom like Jean-Claude Dusse;
Cration de tables

CREATE TABLE nom_de_la_table (
nom_de_lattribut type [ liste_de_contraintes_dattribut ]
nom_de_lattribut type [ liste_de_contraintes_dattribut ]
...
liste_de_contraintes_de_table
);


Exemple :
CREATE TABLE cours (
sigle VARCHAR(20) NOT NULL,
intitule VARCHAR(128) NOT NULL,
responsable VARCHAR(50) NOT NULL,
nombreSeances INT NOT NULL DEFAULT 0,
PRIMARY KEY (sigle),
FOREIGN KEY (responsable) REFERENCES enseignant(id)
);
Suppression de table
DROP TABLE nom_de_la_table ;
Insertion
INSERT INTO nom_de_la_table ( attribut_1, attribut_2, : : : )
VALUES( valeur_1, valeur_2, : : : ) ;



Requtes imbriques / sous-requtes

Une sous-requte est une commande SELECT dans une autre commande. Par exemple :

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

On dit que la sous-requte est imbrique dans la requte externe. Il est possible dimbriquer des requtes dans
des sous-requtes. Une sous-requte doit toujours tre entre parenthses.

Voici un exemple de commande qui montre les principaux avantages des sous-requtes et de leur syntaxe :

SELECT r1
FROM t1
WHERE s11 = (
SELECT COUNT(*) FROM t2
WHERE NOT EXISTS (
SELECT * FROM t3
WHERE r3 =
(SELECT 50,11*s1 FROM t4 WHERE r5 in (SELECT * FROM t5) AS t5)
)
);

EXISTS teste simplement si la requte interne retourne une ligne. NOT EXISTS teste si la requte interne ne
retourne aucun rsultat.

--------------------------------------------------------------------------------------------
CORRECTION
--------------------------------------------------------------------------------------------
Requtes simples
i) crire les requtes de cration des tables Etudiant et Sance .

Rponse :

CREATE TABLE Etudiant (
numero VARCHAR(20) PRIMARY KEY,
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50) NOT NULL,
age INT NOT NULL CHECK(age > 0)
);
CREATE TABLE Sance (
cours VARCHAR(20) NOT NULL,
numero VARCHAR(50) NOT NULL,
type VARCHAR(2) NOT NULL CHECK(type in "CM", "TD", "TP"),
date DATE NOT NULL,
salle VARCHAR(10) NOT NULL,


heureDebut TIME NOT NULL,
heureFin TIME NOT NULL CHECK(heureFin > heureDebut),
enseignant VARCHAR(20) NOT NULL,
FOREIGN KEY (cours) REFERENCES Cours(sigle),
FOREIGN KEY (enseignant) REFERENCES Enseignant(id),
PRIMARY KEY (cours,numero)
);


ii) Inscrivez ltudiant (l0372,Lponge,Bob,20) au cours (LOG015,Logique,jh1908).

Rponse :

INSERT INTO Inscription VALUES ("l0372","LOG015");

iii) Cherchez le nom et le prnom de tous les tudiants de moins de 20 ans.

Rponse :

SELECT nom, prenom FROM Etudiant WHERE age < 20;

iv) Cherchez le nom et le prnom de lenseignant responsable du cours de Statistiques.

Rponse :

SELECT nom, prenom
FROM Enseignant, Cours
WHERE responsable = id
AND intitule LIKE "Statistiques";




v) Cherchez le nom et le prnom de tous les tudiants inscrits au cours de Probabilits.

Rponse :

SELECT e.nom, e.prenom
FROM etudiant e, inscription i, cours c
WHERE e.numero = i.etudiant
AND i.cours = c.sigle
AND c.intitule like "Probabilites";

vi) Dterminez le nombre denseignants intervenant dans le cours de Modlisation Stochatique.

Rponse :

SELECT count(DISTINCT enseignant)
FROM Seance, Cours
WHERE sigle = cours
AND intitule LIKE "%Modelisation%";

vii) O et quand a lieu le premier cours dAlgbre linaire ?


Rponse :

SELECT date, salle, heureDebut, heureFin
FROM Seance, Cours
WHERE sigle = cours
AND numero = 1
AND intitule LIKE "Algebre lineaire";

viii) Affichez un emploi du temps du cours de Logique.



Rponse :

SELECT numero, date, salle, heureDebut, heureFin, e.nom, e.prenom
FROM Seance, Cours, Enseignant e
WHERE sigle = cours
AND enseignant = id
AND intitule LIKE "Logique"
ORDER BY date,heureDebut;

ix) Pour chaque enseignant, indiquez le nombre de cours dans lesquels il intervient (restreignez les
rponses lensemble des enseignants qui interviennent dans au moins deux cours).

Rponse :

SELECT e.nom, e.prenom, count(distinct cours)
FROM Seance, Cours, Enseignant e
WHERE sigle = cours
AND enseignant = e.id
GROUP BY e.id
HAVING count(distinct cours)>1

Requtes imbriques

i) Ajoutez un cours magistral de Logique le 14 dcembre avec Jacques Herbrand en salle S250 de 14h
18h.

Rponse :

INSERT INTO Seance VALUES (
(SELECT sigle FROM Cours
WHERE intitule LIKE Logique),
(SELECT nombreSeances+1 FROM Cours
WHERE intitule LIKE "Logique"),
CM, 2008-12-14, S250, 14:00, 18:00,
(SELECT id FROM Enseignant
WHERE nom like "Herbrand"
AND prenom = "Jacques")
);
UPDATE cours SET nombreSeances = nombreSeances+1 WHERE intitule LIKE Logique;

ii) Listez les tudiants inscrits aucun cours.

Rponse :

SELECT e.nom, e.prenom
FROM Etudiant e
WHERE NOT EXISTS
(SELECT * FROM Inscription i
WHERE i.etudiant = e.numero
);

iii) Combien dtudiants (diffrents) ont assists au moins une sance anime par Leonhard Euler ?

Rponse :

SELECT COUNT(DISTINCT e.numero)
FROM Etudiant e, Inscription i
WHERE i.etudiant = e.numero
AND EXISTS(
SELECT s.cours
FROM Enseignant e, Seance s
WHERE e.id = s.enseignant
AND s.cours = i.cours
AND e.nom LIKE "Euler"
AND e.prenom LIKE "Leonhard"
);

Les procdures stockes SQL-Server - Oracle - exercices-
corrigs
Bases de Donnes, Oracle, SQL 0

1. Dfinition et avantages des procdures stockes

Une procdure stocke est une collection prcompile d'instructions Transact-SQL stocke sous un nom et
traite comme une unit. Les procdures stockes de SQL Server permettent de grer celui-ci et d'afficher les
informations sur les bases de donnes et les utilisateurs. Les procdures stockes fournies avec SQL Server
sont appeles procdures stockes du systme.

Elles renvoient les donnes de quatre faons diffrentes :
des paramtres de sortie, qui renvoient soit des donnes (entiers ou caractres) ou une variable de
curseur, les curseurs tant des ensembles de rsultats pouvant tre extraits ligne par ligne ;
des codes de retour, qui sont toujours un entier ;
un ensemble de rsultats pour chaque instruction SELECT contenue dans la procdure stocke ou toute
autre procdure stocke appele par cette dernire ;
un curseur global qui peut tre rfrenc en dehors de la procdure stocke.
Les curseurs :

Les oprations ralises dans une base de donnes relationnelle s'excutent sur un ensemble complet de lignes.
L'ensemble de lignes renvoy par une instruction SELECT contient toutes les lignes satisfaisant aux conditions
de la clause WHERE de l'instruction. Cet ensemble complet de lignes renvoyes par l'instruction est appel jeu
de rsultats. Les applications, en particulier les applications interactives en ligne, peuvent ne pas toujours
fonctionner efficacement si le jeu de rsultats est trait comme une unit. Ces applications ont besoin d'un
mcanisme leur permettant de travailler avec une seule ligne ou un petit bloc de lignes la fois. Les curseurs
sont une extension des jeux de rsultats et contiennent ce mcanisme.

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Avantages des procdures stockes:

Les procdures stockes contribuent mettre en uvre une logique cohrente dans les applications. Les
instructions SQL et la logique ncessaires l'excution d'une tche frquente peuvent tre cres, codes et
testes une seule fois dans une procdure stocke. Il suffit ensuite chaque application devant effectuer la tche
d'excuter la procdure stocke. Le codage de la logique de gestion en une seule procdure offre aussi un point
de contrle unique permettant de vrifier que les rgles d'entreprise sont bien respectes.

Les procdures stockes peuvent galement amliorer les performances. De nombreuses
tches sont mises en uvre sous forme de squences d'instructions SQL. La logique
conditionnelle applique aux rsultats des premires instructions SQL dtermine les
instructions suivantes excuter. Si ces instructions SQL et la logique conditionnelle
sont crites dans une procdure stocke, elles deviennent partie intgrante d'un plan
d'excution unique sur le serveur. Les rsultats n'ont pas besoin d'tre renvoys au client pour
que la logique conditionnelle soit applique, car tout le travail est ralis sur le serveur.



Les procdures stockes vitent aussi aux utilisateurs d'avoir connatre les dtails des tables de la base de
donnes. Si un ensemble de procdures stockes prend en charge toutes les fonctions de gestion ncessaires
aux utilisateurs, ceux-ci n'ont pas besoin d'accder directement aux tables ; il leur suffit d'excuter les procdures
stockes qui modlisent les processus avec lesquels ils ont l'habitude de travailler.

Les procdures stockes du systme SQL Server vitant aux utilisateurs d'accder aux tables systme en sont
un exemple. SQL Server comprend un ensemble de procdures stockes systme dont les noms commencent
en gnral par sp_. Ces procdures prennent en charge toutes les tches administratives ncessaires
l'excution d'un systme SQL Server. Vous pouvez administrer un systme SQL Server l'aide des instructions
Transact-SQL associes l'administration (telles que CREATE TABLE) ou des procdures stockes du systme,
sans jamais avoir mettre jour directement les tables systme.

1. Les diffrents types de procdures stockes

1.1. Procdure stocke systme

Ensemble de procdures stockes fournies par SQL Server pour la ralisation d'oprations telles que l'extraction
d'informations du catalogue systme ou l'excution de tches d'administration.

Nombre d'activits administratives dans Microsoft SQL Server 2000 s'excutent l'aide d'un type spcial de
procdure connu sous le nom de procdure stocke systme. Les procdures stockes systme sont cres et
enregistres dans la base de donnes master et ont le prfixe sp_. Les procdures stockes du systme
peuvent s'excuter depuis n'importe quelle base de donnes, sans avoir qualifier compltement le nom de la
procdure stocke, en utilisant le nom de base de donnes master.

Il est fortement recommand de ne pas crer de procdures stockes avec le prfixe sp_. SQL Server recherche
toujours une procdure stocke en commenant par sp_ dans l'ordre suivant :
1. elle existe dans la base de donnes master ;
2. ensuite, en fonction des ventuels identificateurs fournis (nom de base de donnes ou propritaire) ;
3. enfin, avec dbo comme propritaire si aucun propritaire n'est spcifi.
Par consquent, bien qu'il puisse exister dans la base de donnes en cours une procdure stocke cre par
l'utilisateur ayant le prfixe sp_, la base de donnes master est toujours analyse la premire, mme si la
procdure stocke est qualifie avec le nom de la base de donnes.
Informations sur les procdures stockes
Pour afficher le texte utilis pour crer la procdure, excutez sp_helptext dans la base de donnes dans
laquelle la procdure se trouve en passant le nom de la procdure en paramtre.

Pour obtenir une liste des objets rfrencs par une procdure, utilisez sp_depends.
Pour renommer une procdure, utilisez sp_rename
Important Si une procdure stocke cre par un utilisateur porte le mme nom qu'une procdure stocke
systme, celle de l'utilisateur ne s'excutera jamais.

1.1. Procdures stockes temporaires

Les procdures stockes temporaires prives et globales, comme les tables temporaires, peuvent tre cres en
ajoutant les prfixes # et # # leur nom. # dsigne une procdure stocke temporaire locale, et # #, une
procdure stocke temporaire globale. Ces procdures n'existent plus aprs l'arrt de SQL Server.

Les procdures stockes temporaires locales sont disponibles au sein dune seule session dutilisateur. Tandis
que les procdures stockes temporaires globales sur lensemble des sessions dutilisateur.

Les procdures stockes temporaires sont utiles lorsque vous vous connectez des versions antrieures de SQL
Server qui ne prennent pas en charge la rutilisation des plans d'excution des instructions ou lots d'instructions
Transact-SQL.

Pour crer et excuter les procdures stockes temporaires :
Create procedure # #procedure_name
As
sql_statement
Exec sp_executesql # #procedure_name

1.2. Procdures stockes distantes


Les procdures stockes distantes sont une ancienne fonctionnalit de Microsoft SQL Server 2000. Leur
fonctionnalit dans Transact-SQL est limite l'excution d'une procdure stocke sur une installation SQL
Server distante. Les requtes distribues introduites dans la version 7.0 de SQL Server prennent en charge cette
possibilit ainsi que l'accs des tables dans des sources de donnes OLE DB htrognes directement partir
d'instructions Transact-SQL locales. Au lieu d'utiliser un appel de procdure stocke distante sur SQL Server
2000, utilisez des requtes distribues et une instruction EXECUTE pour excuter une procdure stocke sur un
serveur distant.

Une instance SQL Server 2000 peut envoyer et recevoir des appels de procdures stockes distantes d'autres
instances de SQL Server 2000 et SQL Server version 7.0. Une instance SQL Server 2000 peut galement
envoyer des appels de procdures stockes distantes vers des instances SQL Server 6.0 ou 6.5 et en recevoir.
Un serveur excutant SQL Server 2000 peut recevoir des appels de procdures stockes distantes d'une
instance SQL Server 4.21a, mais l'instance SQL Server 2000 ne peut pas faire des appels de procdures
stockes distantes l'instance SQL Server 4.21a. L'instance SQL Server 4.21a ne peut pas reconnatre la
version du flux de donnes tabulaires (TDS, Tabular Data Stream) utilise par SQL Server 2000.

1.1. Procdures stockes tendues

Les procdures stockes tendues vous permettent de crer vos propres routines externes dans un langage de
programmation comme le langage C. Les procdures stockes tendues apparaissent aux utilisateurs comme
des procdures stockes normales et s'excutent de la mme faon. Des paramtres peuvent tre passs une
procdure stocke tendue pour renvoyer des rsultats et un tat. Les procdures stockes tendues permettent
d'tendre les fonctionnalits de Microsoft SQL Server 2000.

Les procdures stockes tendues sont des bibliothques de liaison dynamique (DLL, dynamic-link library) que
SQL Server peut charger et excuter dynamiquement. Elles s'excutent directement dans l'espace d'adresse de
SQL Server et sont programmes au moyen de l'API Open Data Services de SQL Server.

Une fois que la procdure stocke tendue est crite, les membres du rle de serveur fixe sysadminpeuvent
l'inscrire dans SQL Server, puis donner l'autorisation de l'excuter d'autres utilisateurs. Les procdures
stockes tendues ne peuvent tre ajoutes qu' la base de donnes master.

Les procdures stockes tendues sont gnralement identifies par le prfixe xp_

2. Codification des procdures stocke

3.1. Cration des procdures stockes

Vous pouvez crer une procdure stocke en utilisant l'instruction Transact-SQL CREATE PROCEDURE. Lisez
les informations ci-dessous avant de crer une procdure stocke.
L'instruction CREATE PROCEDURE ne peut pas s'utiliser conjointement avec d'autres instructions SQL
dans un mme lot d'instructions.
L'autorisation de crer des procdures stockes revient par dfaut au propritaire de la base de
donnes, qui peut la transmettre d'autres utilisateurs.
Les procdures stockes sont des objets de base de donnes et leur nom doit respecter les rgles
gouvernant les identificateurs.
Vous ne pouvez crer une procdure stocke que dans la base de donnes en cours.

Pour crer une procdure stocke, vous devez prciser :
es paramtres d'entre et de sortie de la procdure ou du lot appelant ;
les instructions de programmation qui excutent les oprations dans la base de donnes, y compris
l'appel d'autres procdures ;
la valeur d'tat renvoye la procdure ou au lot appelant pour indiquer la russite ou l'chec et, dans
ce cas, la raison de l'chec.
Syntaxe
CREATE PROC [ EDURE ] procedure_name [ ; number ]
{ @parameter data_type }
AS sql_statement [ ...n ]
Arguments
procedure_name
Nom de la nouvelle procdure stocke. Les noms des procdures doivent respecter les rgles applicables aux
identificateurs et doivent tre uniques dans la base de donnes et pour son propritaire.

;number

Nombre entier facultatif utilis pour regrouper les procdures de mme nom afin qu'elles puissent tre
supprimes ensemble l'aide d'une seule instruction DROP PROCEDURE. Par exemple, les procdures
utilises avec une application appele order peuvent tre nommes orderproc;1,orderproc;2, etc. L'instruction
DROP PROCEDURE orderproc abandonne le groupe tout entier.

@parameter

Un paramtre de la procdure. Vous pouvez dclarer un ou plusieurs paramtres dans une instruction CREATE
PROCEDURE. La valeur de chaque paramtre dclar doit tre fournie par l'utilisateur lors de l'excution de la
procdure (sauf si vous dfinissez une valeur par dfaut pour le paramtre). Une procdure stocke peut
comprendre au maximum 2100 paramtres.

Spcifiez un nom de paramtre en plaant le signe @ comme premier caractre. Ce nom doit respecter les
rgles gouvernant les identificateurs. Un paramtre est local une procdure, vous pouvez donc utiliser le mme
nom dans d'autres procdures.

data_type
Type de donnes du paramtre. Tous les types de donnes y compris les types text, ntext et image, peuvent
tre utiliss comme paramtre dans une procdure stocke.

AS
Spcifie les actions que peut entreprendre la procdure.

sql_statement
Tout numro et type d'instructions Transact-SQL inclure dans la procdure. Certaines limitations s'appliquent.

n
Espace rserv qui indique que plusieurs instructions Transact-SQL peuvent tre incluses dans cette procdure.

3.2. Modification des procdures stockes

Pour modifier une procdure stocke :

ALTER PROC [ EDURE ] procedure_name [ ; number ]
{ @parameter data_type }
AS
sql_statement [ ...n ]

3.3. Suppression des procdures stockes

Pour supprimer une procdure stocke :

Drop proc[edure] procedure_name

3.4. Appel dune procdure stocke

Utilisez l'instruction EXECUTE de Transact-SQL pour excuter une procdure stocke. L'utilisation du mot cl
EXECUTE n'est pas ncessaire cette excution si la procdure est la premire instruction du lot.
Exec [ute] procedure_name @parameter 1= value1,@parameter2 = value2,@parameter3 = value3

Des valeurs de paramtres peuvent tre fournies si une procdure stocke a t crite pour les accepter.

Remarque Si vous entrez des paramtres sous la forme @Parameter = value, leur ordre n'a pas d'importance.
Vous pouvez aussi omettre les paramtres pour lesquels une valeur par dfaut a t dfinie. Si vous spcifiez un
paramtre donn sous la forme @Parameter = value, vous devez tous les spcifier de cette faon. Sinon, ils
doivent apparatre dans l'ordre indiqu par l'instruction CREATE PROCEDURE.
Lorsque le serveur excute une procdure stocke, il refuse tous les paramtres qui n'taient
pas insrs dans la liste des paramtres au moment de la cration de la procdure.
Tout paramtre qui est pass par rfrence (en fournissant explicitement son nom) ne sera pas accept si son
nom ne concorde pas.
Bien que vous puissiez omettre des paramtres ayant des valeurs par dfaut, seule la liste des paramtres peut
tre tronque. Par exemple, si une procdure stocke a cinq paramtres, vous pouvez omettre les deux derniers
paramtres, mais pas omettre le quatrime et inclure le cinquime, moins d'utiliser le format @parameter =
value.
La valeur par dfaut d'un paramtre, si elle a t dfinie dans la procdure stocke, est utilise dans les cas
suivants :
si aucune valeur n'est spcifie pour le paramtre au moment de l'excution de la procdure ;
si le mot cl DEFAULT est spcifi comme valeur du paramtre.
Pour excuter une procdure stocke qui est groupe avec d'autres procdures du mme nom, indiquez le
numro d'identification de la procdure l'intrieur du groupe. Par exemple, pour excuter la seconde procdure
stocke du groupe my_proc, spcifiez :
EXECUTE my_proc;2

Pour faire appel une procdure stocke on utilise le mot call

{call procedure_name(@parameter.)}
{call "DataBase"."Owner"."Procedure_name" }


3.5. Dfinition des paramtres, traitement des erreurs

les procdures ne seraient pas intressantes si on ne pouvait pas spcifier de paramtres. Heureusement, il est
trs facile dcrire une procdure stocke paramtrable.
Pour dclarer un paramtre, il suffit donc de le spcifier dans lentte de la procdure en lui indiquant :

son nom : @Parameter (noubliez pas le @), qui sera utilisable comme une variable dans la procdure stocke.
Un type de donne, choisi parmi les types SQL ou les types utilisateurs.
Une valeur par dfaut optionnelle.
Une direction, en mettant OUTPUT derrire le nom dun paramtre.

3.4.1 Paramtres optionnels

Vous pouvez spcifier pour chaque paramtre une valeur par dfaut.

3.4.2 Direction des paramtres

Vous pouvez galement dfinir des paramtres de sortie. Ces paramtres sont modifis dans la procdure
stocke puis retourns lappelant.
3.6. Utilisation de NOCOUNT, EXISTS

NOCOUNT :
Empche le message indiquant le nombre de lignes affectes par une instruction Transact-SQL d'tre renvoy en
tant que rsultat.
Syntaxe
SET NOCOUNT {ON | OFF}
Notes
Si SET NOCOUNT est active (ON), le chiffre indiquant le nombre de lignes affectes par une instruction
Transact-SQL n'est pas renvoy. Si la valeur de SET NOCOUNT est dfinie sur OFF, ce chiffre est renvoy.
EXISTS :

Prcise une sous-requte pour dterminer l'existence ou non de lignes.

**************

Dans les exemples et les exercices qui suivent, on travaille sur la base de
donnes GestionCommande :


1. Exemples


----Cration d'une procdure stocke simple
Create procedure PS;1
as
select * from Client
where IdClient>1650
Exec PS;1
---------------------------------


----Cration d'une procdure avec un seul paramtre
create proc myprocedure
@d datetime
as
select * from Facture where DateFacture=@d
exec myprocedure '08/09/2002'

create proc PS;2
(@Id int)
as
select IdClient,Nom,Prnom from Client where IdClient=@Id
Exec PS;2 @Id=1668
---------------------------------

----Cration d'une procdure avec deux ou plusieurs paramtres
create proc PS;3
(@n varchar(20),
@p varchar(20))
as
select * from Client where Nom=@n and Prnom=@p
Exec PS;3 'Alaoui','Ahmed'
---------------------------------

----Procdure qui a un paramtre optionnel
Create proc PS;4
(@Id int =null)
As
If @Id is null
Begin
Select * from Client
End
Else Begin
Select * from Client where IdClient=@Id
End
Go
exec PS;4 @Id=1925
exec PS;4 1668
exec PS;4
exec PS;4 @Id=default
---------------------------------

----On cre une procdure stocke porte le mme nom d'une procdure stocke systme "sp_depends"
create procedure sp_depends
@n int
as
select * from Commande where NumCommande=@n
exec sp_depends 'Client'
---------------------------------

----Procdure Stocke temporaire globale
create procedure ##ProcGlobale
as
select * from Client where IdClient=1668
exec sp_executesql ##ProcGlobale
----------------------------------

----Procdure Stocke temporaire locale
create procedure #ProcLocale
as
select distinct * from Commande
exec sp_executesql #ProcLocale
---------------------------------

----Appel d'une procdure stocke
{call "GestionCommande"."dbo"."PS;1"}
{call PS;1}
{call PS;3 ('alaoui','ahmed')}
{call myprocedure ('08/09/2002')}
---------------------------------

-------Procdure avec plusieurs commandes SQL
create proc procdureM
as
begin
select * from Client
end
begin
insert into Client values(3334,'Tourabi','Amina','Casa')
end
-----appel de la procdure
{call procdureM}
--------------------------------------

------Modification d'une Procdure stocke
alter proc procdureM
as
select * from Commande
--------------------------------------

-------Suppression d'une Procdure stocke
drop proc procdureM
--------------------------------------



--------Gestion des erreurs-----------
Create procedure pp
@aa int
as
if @aa>0
select * from client where IdClient=@aa
else
print'Attention le IdClient que vous avez entrer n''est pas correct!'
Exec pp -55
--------------------------------------

1. Exercices

Exercice 1 :

Crer une procdure stocke qui affiche les clients dont la quantit commande est suprieur 75 et les factures
sont ralises entre 2003 et 2004

Corrig :

create proc E;1
as
select * from Client where exists
(select * from Commande
where IdClient=Client.IdClient and Quntit>75 and exists
(select * from Facture
where datefacture between '01/01/2003'and '31/12/2004'and NumCommande=Commande.NumCommande ))
exec E;1

Exercice 2 :

Crer une procdure stocke qui retourne la somme des prix payer par tous les clients en utilisant un
paramtre de sortie.

Corrig :

create proc E;2
@somme money output
as

select @somme = sum(PrixTotal)
from Commande
if @somme < 1000
print 'La socit va fermer ses portes.'
else
---SELECT 'La socit a ralis ' + convert (varchar(20), @somme)+' F' as PrixTotal
select @somme as SommeRalis
go
declare @P money
exec E;2 @P output

Exercice3 :

Crer une procdure qui affiche les noms et les prnoms des clients dont le nom commence par Al en utilisant
un cursor qui permet dextraire les lignes ligne par ligne

Corrig :

create proc ProcCursor
as
DECLARE Cur CURSOR FOR
SELECT Nom, Prnom FROM Client where Nom like 'Al%'
OPEN Cur
FETCH NEXT FROM Cur
WHILE @@FETCH_STATUS = 0
BEGIN
insert into client values(2056,'toto','titi','Mars')
FETCH NEXT FROM Cur
END
CLOSE Cur
drop proc ProcCursor
exec ProcCursor


Exercice 4 :

Crer une procdure qui excute la procdure prcdente

Corrig :

create proc ProcAppelante
@P1 varchar(10)
as
exec @P1
---------
exec ProcAppelante 'ProcCursor'

Exercice 5 :

Crer la procdure stocke qui compte le nombre de commandes dun client et affiche le rsultat

Corrig :

create proc prc
(@id int,
@var int output)
as
select @var = count(NumCommande)
from Commande where IdClient=@id
group by IdClient

drop proc prc
declare @P int
Exec prc 1578,@P output
Select @p as NbreCommandes

TD SQL -exo SQL TP SGBD SQL
SQL 0

Introduction aux bases de donnes Le langage SQL


Exercice 1 Clinique de mdecine

Soit la base de donnes suivante :


------------------------------------------------------------------------------------------------------

PATIENT (NoPatient, NoAssSociale, Nom, Prenom)

MEDECIN (NoMedecin, Nom, Prenom)

DIAGNOSTIC (NoDiagnostic, description)

TRAITEMENT (NoTraitement, description)

ENTREE_DOSSIER (NoDossier, DateVisite, #NoPatient, #NoMedecin, #NoTraitement, #NoDiagnostic)
------------------------------------------------------------------------------------------------------

1) Donnez le code SQL pour crer la base de donnes

2) Vous ralisez que la taille de l'attribut "description" de la table DIAGNOSTIC n'est pas adquate. Donnez le
code SQL pour la modifier pour une chane de longueur variable de 255 caractres maximum.

3) Donnez le code SQL pour ajouter les attributs "NoTelephone" et "DateNaissance" dans la table PATIENT.

4) Donnez le code SQL pour entrer les donnes suivantes dans la base de donnes


Table PATIENT


Table MEDECIN


Table DIAGNOSTIC



Table TRAITEMENT



Table ENTREE_DOSSIER



5) Vous avez entr le mauvais traitement dans l'entre de dossier no. 3. Modifiez l'enregistrement pour
donner le traitement no. 2 au lieu du no. 1.

6) Effectuez les requtes SQL simples suivantes :

Afficher toutes les informations de tous les patients;
Afficher le nom et le prnom de tous les patients;
Afficher le nom et le prnom des patients dont le nom de famille est 'Delisle';
Afficher le nom et le prnom des patients ns aprs 1976;
Afficher les noms de famille diffrents des patients;
Afficher les patients en ordre croissant de date de naissance;
Afficher les entres de dossier o la patient trait est de no. 111111 et le mdecin traitant est de no. 67899

7) Effectuez les jointures suivantes :

Afficher toutes les entres de dossier et les informations de leurs patients respectifs;
Afficher les entres de dossier de Pierre Delisle;
Afficher la description des traitements dont a bnfici Pierre Delisle;
Afficher, du plus jeune au plus vieux, le nom et le prnom des patients traits par Ren Lajoie le 26 avril 2008.


Exercice 2 Bibliothque

Soit la base de donnes suivante :

--------------------------------------------------------------------------------------------

SPECIALITE (NoSpecialite, Description)
SECTION (NoSection, Emplacement, Description)
LIVRE (CodeISBN, Titre, #NoSpecialit, #NoSection)
FOURNISSEUR (NoFournisseur, Nom)
EXEMPLAIRE (NoExemplaire, #CodeISBN, #NoFournisseur)
ABONNE (NoAbonne, Nom, Prenom)
CARTE (NoCarte, DateDebut, DateFin, #NoAbonne)
EMPRUNT (NoExemplaire, NoCarte, DateLocation, DateRetour)
--------------------------------------------------------------------------------------------


1) Effectuez les requtes SQL suivantes :
Afficher la liste des livres classs dans les sections 1 et 4;
Ajouter un attribut adresse la table abonn;
Ajouter le fournisseur 'Livres du Qubec inc.' la base de donnes';
Afficher le nom et le prnom des abonns qui se sont abonns ou ont renouvel leur carte en 2012;
Afficher le code et le titre des livres qui ont t emprunts le 28 avril 2012, tris par ordre alphabtique de titre;
Afficher le nom et le prnom des abonns qui ont dj emprunt le livre intitul 'Nos amis les franais';
Prolonger tous les abonnements chus le 25 avril 2012 au 25 mai 2012;
Afficher le titre des livres de science-fiction emprunts durant le mois d'avril 2012.
Exercices en SQL - TP SQL
SQL 0

Soit la base de donnes constitue des relations (autoexplicatives) suivantes:

aime (personne, boisson)
frequente (personne, cafe)
sert (cafe, boisson)

aime
+----------+----------+
| personne | boisson |
+----------+----------+

| ali | coca |
| ali | 7up |
| ali | oulmes |
| said | coca |
| said | orangina |
| aziz | oulmes |
| aziz | 7up |
| amine | coca |
| amine | oulmes |
+----------+----------+

frequente
+----------+----------+
| personne | cafe |
+----------+----------+
| ali | rif |
| ali | atlas |
| amine | commerce |
| aziz | rif |
| aziz | commerce |
| aziz | atlas |
| said | atlas |
+----------+----------+


sert
+----------+----------+
| cafe | boisson |
+----------+----------+
| commerce | coca |
| commerce | 7up |
| atlas | 7up |
| rif | coca |
| commerce | oulmes |
| commerce | orangina |
| atlas | oulmes |
+----------+----------+

Ecrire en SQL les requtes suivantes
1. Quelles sont les personnes, les cafs qu'ils frequentent, et les
boissons servies par ces cafs.
2. Quelles sont les personnes qui frquentent des cafs qui servent
des boissons qu'ils aiment.
3. Quels sont les caf servant toutes les boissons.
4. Quelles sont les personnes qui ne frquentent que les cafs qui
servent des boissons qu'ils aiment.
5. Quelles sont les personnes qui ne frquentent que les cafs qui
servent des boissons qu'ils n'aiment pas.
Travaux Pratiques SQL - exercices SQL - TP SQL
SQL 0

Exercice SQL SQL SERVER ORACLE

Soit la base de donne SPDB, contenant les relations Fournisseurs (S), Produits (P) et Fournit (SP).



S
+------+-------+--------+--------+
| snum | sname | status | city |
+------+-------+--------+--------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
+------+-------+--------+--------+
5 rows in set (0.00 sec)

P

+------+-------+-------+--------+--------+
| pnum | pname | color | weigth | city |
+------+-------+-------+--------+--------+
| P1 | Nut | Red | 12.00 | London |
| P2 | Bolt | Green | 17.00 | Paris |
| P3 | Screw | Blue | 17.00 | Rome |
| P4 | Screw | Red | 14.00 | London |
| P5 | Cam | Blue | 12.00 | Paris |
| P6 | Cog | Red | 19.00 | London |
+------+-------+-------+--------+--------+
6 rows in set (0.00 sec)

SP
+------+------+------+
| snum | pnum | qty |
+------+------+------+
| S1 | P1 | 300 |
| S1 | P2 | 200 |
| S1 | P3 | 400 |
| S1 | P4 | 200 |
| S1 | P5 | 100 |
| S1 | P6 | 100 |
| S2 | P1 | 300 |
| S2 | P2 | 400 |
| S3 | P2 | 200 |

| S4 | P2 | 200 |
| S4 | P4 | 300 |
| S4 | P5 | 400 |
+------+------+------+
12 rows in set (0.00 sec)

I) Crer cette base de donnes et choisir les types de donnes appropris.

II) Ecrire en SQL les requtes suivantes:

1. Tous les dtails sur tous les fournisseurs
2. Le nom et le status des fournisseurs de 'Paris'
3. Idem par ordre decroissant de status
4. les paires numro de fournisseur et de produit situs dans la mme ville
5. le numro des fournisseurs et le nom des produits qu'ils fournissent
6. le nom des fournisseurs et le nom des produits qu'ils fournissent
7. les numros de fournisseurs et numros de produits fournis et situ dans la mme ville.
8. Les paires numros de fournisseurs ayant mme status
9. Il y a combiens d'expditions du produit de numro 'P2'?
10. Ecrire de deux faon diffrentes la requte: "Nom des fournisseurs du produit de numro 'P2'
11."Nom des fournisseurs qui fournissent au moins un produit de couleur rouge ('Red')
12. Nom des fournisseurs qui ne fournissent pas le produit 'P2'.
Travaux Dirigs 7
Introduction aux bases de donnes
Le langage SQL
Exercice 1 Clinique de mdecine:
Soit la base de donnes suivante :


PATIENT (NoPatient, NoAssSociale, Nom, Prenom)
MEDECIN (NoMedecin, Nom, Prenom)
DIAGNOSTIC (NoDiagnostic, description)
TRAITEMENT (NoTraitement, description)
ENTREE_DOSSIER (NoDossier, DateVisite, #NoPatient, #NoMedecin, #NoTraitement,
#NoDiagnostic)

1) Donnez le code SQL pour crer la base de donnes

2) Vous ralisez que la taille de l'attribut "description" de la table DIAGNOSTIC n'est
pas adquate. Donnez le code SQL pour la modifier pour une chane de longueur
variable de 255 caractres maximum.

3) Donnez le code SQL pour ajouter les attributs "NoTelephone" et "DateNaissance"
dans la table PATIENT.

4) Donnez le code SQL pour entrer les donnes suivantes dans la base de donnes

5) Vous avez entr le mauvais traitement dans l'entre de dossier no. 3. Modifiez
l'enregistrement pour donner le traitement no. 2 au lieu du no. 1.

6) Effectuez les requtes SQL simples suivantes :
Afficher toutes les informations de tous les patients;
Afficher le nom et le prnom de tous les patients;
Afficher le nom et le prnom des patients dont le nom de famille est 'Delisle';
Afficher le nom et le prnom des patients ns aprs 1976;
Afficher les noms de famille diffrents des patients;
Afficher les patients en ordre croissant de date de naissance;
Afficher les entres de dossier o la patient trait est de no. 111111 et le mdecin
traitant est de no. 67899

7) Effectuez les jointures suivantes :
Afficher toutes les entres de dossier et les informations de leurs patients
respectifs;
Afficher les entres de dossier de Pierre Delisle;
Afficher la description des traitements dont a bnfici Pierre Delisle;
Afficher, du plus jeune au plus vieux, le nom et le prnom des patients traits par
Ren Lajoie le 26 avril 2008.

Exercice 2 Bibliothque
Soit la base de donnes suivante :

SPECIALITE (NoSpecialite, Description)
SECTION (NoSection, Emplacement, Description)
LIVRE (CodeISBN, Titre, #NoSpecialit, #NoSection)
FOURNISSEUR (NoFournisseur, Nom)
EXEMPLAIRE (NoExemplaire, #CodeISBN, #NoFournisseur)
ABONNE (NoAbonne, Nom, Prenom)
CARTE (NoCarte, DateDebut, DateFin, #NoAbonne)
EMPRUNT (NoExemplaire, NoCarte, DateLocation, DateRetour)

1) Effectuez les requtes SQL suivantes :
Afficher la liste des livres classs dans les sections 1 et 4;
Ajouter un attribut adresse la table abonn;
Ajouter le fournisseur 'Livres du Qubec inc.' la base de donnes';
Afficher le nom et le prnom des abonns qui se sont abonns ou ont renouvel
leur carte en 2008;
Afficher le code et le titre des livres qui ont t emprunts le 28 avril 2008, tris par
ordre alphabtique de titre;
Afficher le nom et le prnom des abonns qui ont dj emprunt le livre intitul
'Nos amis les franais';
Prolonger tous les abonnements chus le 25 avril 2010 au 25 mai 2010;
Afficher le titre des livres de science-fiction emprunts durant le mois d'avril 2008.
Examen mcd-sql-mld- Modle relationnel et sql
SQL 0
MODLE RELATIONNEL ET REQUTES SQL


Dans la perspective de la refonte du SI, ltude des autres composantes du systme
dinformation actuel en vue de leur unification est une tape indispensable.

Aprs le domaine de la gestion collective, on sintresse lactivit Gestion prive
de STAR FINANCE. On vous prsente en annexe 1 un extrait du schma conceptuel
des donnes propre ce ple.

Travail faire
1. Traduire le modle conceptuel de donnes en modle relationnel.

2. Rdiger les requtes suivantes en langage SQL :

2.1. Nom et prnom des clients possdant un compte titre de la catgorie risque
modr.

2.2. Nombre doprations par compte.

2.3. Numro et nom des clients ne possdant pas de compte de la catgorie
risque lev .

2.4. Total des montants des oprations par compte.

3. Dans le but darchiver les oprations anciennes, ladministrateur de la base de
donnes a cr une table ARCH-OP ayant la mme structure que la table
OPRATION.

Rdiger les requtes suivantes en langage SQL :

3.1. Insrer dans la table ARCH-OP les oprations antrieures une date saisie au
clavier lors de lexcution de la requte.

3.2. Supprimer de la table ARCH-OP les enregistrements archivs dont lanne est
infrieure ou gale 2000. On peut utiliser la fonction YEAR.

------------------------------------------------------------------------------------------------
CORRECTION
------------------------------------------------------------------------------------------------

1- Traduction du MCD en modle relationnel

CLIENT (NClient, Nom, Prnom, Ad_Rue, Ad_CodeP, Ad_Ville)

COMPTE (NCompte, Date_Cration, Type_Extrait, Fiscalit, #NClient,
#NCatgorie)

CATEGORIE (NCatgorie, Lib_Catgorie)

VALEUR (Code-Valeur, Libell_Valeur, Nominal_Valeur, Date_Emission,
Date_Echance)

OPERATION (NOpration, Date_opration, Sens_opration,
Quantit_Ngocie, Cours_Ngoci, #Code_Valeur)

AFFECTER (NOpration, NCompte, Quantit_Aff)



2- Requtes simples SQL

a) Nom et prnom des clients possdant un compte titre de catgorie
risque modr

SELECT Nom, Prnom
FROM COMPTE, CLIENT, CATGORIE
WHERE COMPTE.NClient=CLIENT.NClient
AND COMPTE.NCatgorie=CATGORIE.NCatgorie
AND Lib_Catgorie = "Risque Modr" ;


b) Le nombre doprations par compte

SELECT NCompte, COUNT (NOpration) AS [Nombre doprations]
FROM AFFECTER
GROUP BY NCompte ;

c) Numro et nom des clients ne possdant pas de compte de catgorie
risque lev

SELECT NClient, Nom
FROM CLIENT
WHERE NClient NOT IN
(SELECT NClient
FROM COMPTE, CATGORIE
WHERE COMPTE.NCatgorie=CATGORIE.NCatgorie
AND Lib_Catgorie ="risque lev" ;) ;

d) Total des montants des oprations par compte .

SELECT NCompte, SUM (Quantit_Aff*Cours_Ngoci) AS [Montant Total]
FROM OPERATION, AFFECTER
WHERE OPERATION.Nopration = AFFECTER.NOpration
GROUP BY NCompte ;


3- Requtes darchivage SQL)

a) Insrer dans la table ARCH_OPE les oprations antrieures une date
saisie au clavier lors de lexcution de la requte.

INSERT INTO ARCH_OPE
SELECT *
FROM OPRATION
WHERE Date_opration < [Entrez une date dopration : ] ;


b) Supprimer de la table ARCH_OPE les enregistrements archivs dont lanne est
infrieure ou gale 2000. On peut utiliser la fonction YEAR.
DELETE
FROM ARCH_OPE
WHERE YEAR( Date_opration) <= 2000 ;
OU
WHERE Date_opration <= #01/01/2000#
SQL et phpMyadmin TP avec solutions Exercices et
corrigs SQL
SQL 0

SQL et phpMyadmin TP avec solutions Exercices et
corrigs SQL
Exercice 1:
Crer une base nomme voitures. Crer ensuite les tables de la base voitures selon
le modle logique dfini
dans les exercices du chapitre 13. Omettre volontairement certaines colonnes et
faire volontairement quelques erreurs de type de colonne. Une fois les tables
cres, ajouter les colonnes manquantes et corriger les erreurs. Vrifier la structure
de chaque table.
Solution
Nous utilisons bien sr phpMyAdmin pour crer la base puis les tables.
Cration de la table personne (en omettant volontairement le champcodepostal)
CREATE TABLE `proprietaire` (
`id_pers` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nom` VARCHAR( 30 ) NOT NULL ,
`prenom` VARCHAR( 30 ) NOT NULL ,
`adresse` VARCHAR( 50 ) NOT NULL ,
`ville` VARCHAR( 40 ) NOT NULL ,
PRIMARY KEY ( `id_pers` )
) TYPE = MYISAM ;

Nous ajoutons le champ codepostal oubli lors de la cration de la table.
ALTER TABLE `proprietaire` ADD `codepostal` MEDIUMINT( 5 ) UNSIGNED
NOT NULL ;
Nous modifions le type du champ id_pers pour avoir un ventail de valeurs plus
grand.
ALTER TABLE `proprietaire` CHANGE `id_pers` `id_pers` MEDIUMINT
UNSIGNED NOT NULL AUTO_INCREMENT

Cration de la table carte grise
CREATE TABLE `cartegrise` (
`id_pers` MEDIUMINT UNSIGNED NOT NULL ,
`immat` VARCHAR( 6 ) NOT NULL ,
`datecarte` DATE NOT NULL ,
PRIMARY KEY ( `id_pers` , `immat` )
);
Nous crons la table voiture.
CREATE TABLE `voitures` (
`immat` VARCHAR( 6 ) NOT NULL ,
`id_modele` VARCHAR( 10 ) NOT NULL ,
`couleur` ENUM( 'claire', 'moyenne', 'fonce' ) NOT NULL ,
`datevoiture` DATE NOT NULL ,
PRIMARY KEY ( `immat` )
);
Nous crons la table modele
CREATE TABLE `modele` (
`id_modele` VARCHAR( 10 ) NOT NULL ,
`modele` VARCHAR( 30 ) NOT NULL ,
`carburant` ENUM( 'essence', 'diesel', 'gpl', 'lectrique' ) NOT
NULL ,
PRIMARY KEY ( `id_modele` )
);

Exercice 2:

Exporter les tables de la base voitures dans des fichiers SQL.

Solution
Nous obtenons les fichiers suivants :

Le fichier proprietaire.sql :
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Gnr le : Mercredi 15 Dcembre 2004 18:21
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de donnes: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `proprietaire`
--
CREATE TABLE `proprietaire` (
`id_pers` mediumint(8) unsigned NOT NULL auto_increment,
`nom` varchar(30) NOT NULL default '',
`prenom` varchar(30) NOT NULL default '',
`adresse` varchar(50) NOT NULL default '',
`ville` varchar(40) NOT NULL default '',
`codepostal` mediumint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id_pers`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Le fichier cartegrise.sql :

-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Gnr le : Mercredi 15 Dcembre 2004 18:26
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de donnes: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `cartegrise`
--
CREATE TABLE `cartegrise` (
`id_pers` mediumint(8) unsigned NOT NULL default '0',
`immat` varchar(6) NOT NULL default '',
`datecarte` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id_pers`,`immat`)
) TYPE=MyISAM;

Le fichier voiture.sql

-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Gnr le : Mercredi 15 Dcembre 2004 18:27
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de donnes: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `voitures`
--
CREATE TABLE `voiture` (
`immat` varchar(6) NOT NULL default '',
`id_modele` varchar(10) NOT NULL default '',
`couleur` enum('claire','moyenne','fonce') NOT NULL default
'claire',
`datevoiture` date NOT NULL default '0000-00-00',
PRIMARY KEY (`immat`)
) TYPE=MyISAM;

Le fichier modele.sql

-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Gnr le : Mercredi 15 Dcembre 2004 18:29
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de donnes: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `modele`
--
CREATE TABLE `modele` (
`id_modele` varchar(10) NOT NULL default '',
`modele` varchar(30) NOT NULL default '',
`carburant` enum('essence','diesel','gpl','lectrique') NOT NULL default 'essence',
PRIMARY KEY (`id_modele`)
) TYPE=MyISAM;

Exercice 3:


Supprimer toutes les tables de la base voitures.
Solution

Le code SQL est le suivant :
DROP TABLE `proprietaire`
DROP TABLE `cartegrise`
DROP TABLE `voiture`
DROP TABLE `modele`

Exercice 4:

Recrer les tables de la base voitures en utilisant les fichiers SQL prcdents.

Pour recrer avec phpMyAdmin, les tables dtruites, choisir successivement la
base, puis longlet SQL , Emplacement du fichier texte , Parcourir pour
dsigner lemplacement du fichier .sql, et enfin Excuter . Les tables sont alors
recres lune aprs lautre.

Dans lexercice 2, nous avions galement la possibilit dexporter lensemble de la
base voitures en un seul fichier .sql. Nous aurions obtenu alors le fichier voitures.sql
suivant :

Solution
-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Gnr le : Mercredi 15 Dcembre 2004 18:41
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de donnes: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `cartegrise`
--
CREATE TABLE `cartegrise` (
`id_pers` mediumint(8) unsigned NOT NULL default '0',
`immat` varchar(6) NOT NULL default '',
`datecarte` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id_pers`,`immat`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `modele`
--
CREATE TABLE `modele` (
`id_modele` varchar(10) NOT NULL default '',
`modele` varchar(30) NOT NULL default '',
`carburant` enum('essence','diesel','gpl','lectrique') NOT NULL
default 'essence',
PRIMARY KEY (`id_modele`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `proprietaire`
--
CREATE TABLE `proprietaire` (
`id_pers` mediumint(8) unsigned NOT NULL auto_increment,
`nom` varchar(30) NOT NULL default '',
`prenom` varchar(30) NOT NULL default '',
`adresse` varchar(50) NOT NULL default '',
`ville` varchar(40) NOT NULL default '',
`codepostal` mediumint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id_pers`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Structure de la table `voiture`
--
CREATE TABLE `voiture` (
`immat` varchar(6) NOT NULL default '',
`id_modele` varchar(10) NOT NULL default '',
`couleur` enum('claire','moyenne','fonce') NOT NULL default
'claire',
`datevoiture` date NOT NULL default '0000-00-00',
PRIMARY KEY (`immat`)
) TYPE=MyISAM;

Exercice 5:

Insrer des donnes dans la table proprietaire de la base voitures puis en vrifier la
bonne insertion.

Solution

Exemple de code dinsertion :

INSERT INTO `proprietaire` ( `id_pers` , `nom` , `prenom` ,
`adresse` , `ville` , `codepostal` )
VALUES (
'', 'Zouk', 'Julia', '56 Boulevard Nez', 'Paris', '75011'
);



Exercice 6:

Crer un fichier texte contenant une liste de modles de voitures avec autant de
donnes par ligne que de colonnes dans la table modle de la base voitures. Insrer
ces donnes dans la base.

Solution

Exemple de fichier texte contenant des modles : le fichier
modele.txt
"17C92853AZ";"Citron C5";"diesel"
"178524ER45";"Citron Picasso";"essence"
"7499RF5679";"Renault Mgane Scnic";"diesel"
"33356677PO";"Peugeot 206";"lectrique"
"563339GH56";"Citron C3";"essence"
"83321TY455";"Renault Espace";"diesel"

Pour revoir la mthode dinsertion partir dun fichier texte avec phpMyAdmin,
voir la page 382 et suivantes.

Exercice 7:

Crer un fichier Excel ou OpenOffice contenant une liste de modles de voitures
avec autant de donnes par ligne que de colonnes dans la table modele.
Lenregistrer au format CSV et insrer les donnes dans la base.

Solution

La feuille du tableur laspect type suivant :


Linsertion des donnes se fait selon la mme procdure que celle utilise pour un
fichier texte. Aprs linsertion la table modle a le contenu suivant :





Exercice 8:

Insrer des donnes dans les autres tables de la base voitures. Effectuer des mises
jour en modifiant certaines valeurs.

Solution

Trivial avec phpMyAdmin.

Exercice 9:


Dans la base magasin, slectionner les articles dont le prix est infrieur 1 500 .

Solution

Requte SQL :

SELECT id_article, designation, prix
FROM article
WHERE prix <1500

Exercice 10:

Dans la base magasin, slectionner les articles dont le prix est compris entre 100 et
500 .
Solution

Requte SQL :

SELECT id_article, designation, prix
FROM article
WHERE prix
BETWEEN 100
AND 500

Exercice 11:

Dans la base magasin, slectionner tous les articles de marque Nikon (dont la
dsignation contient ce mot).

Solution

Requte SQL :

SELECT id_article, designation, prix
FROM article
WHERE designation LIKE '%Nikon%'

Exercice 12:


Dans la base magasin, slectionner tous les camscopes, leur prix et leur rfrence.

Solution

Requte SQL :

SELECT id_article, designation, prix
FROM article
WHERE designation LIKE '%camscope%'
On peut galement crire :
SELECT id_article, designation, prix
FROM article
WHERE categorie = 'vido'

Exercice 13:


Dans la base magasin, slectionner tous les produits de la catgorie informatique et
afficher leur code, leur dsignation et leur prix par ordre dcroissant de prix.

Solution

Requte SQL :

SELECT id_article, designation, prix
FROM article
WHERE categorie = 'informatique '
ORDER BY prix DESC



Exercice 14:

Dans la base magasin, slectionner tous les clients de moins de 40 ans et ordonner
les rsultats par ville en ordre alphabtique.

Solution

Requte SQL :

SELECT nom, prenom, age, ville
FROM CLIENT WHERE age <40
ORDER BY ville ASC

Exercice 15:


Dans la base magasin, calculer le prix moyen de tous les articles.

Solution

Requte SQL :

SELECT avg( prix )
FROM article

Exercice 16:

Dans la base magasin, calculer le nombre de-mails non NULL et distincts lun de
lautre.

Solution

Requte SQL :

SELECT count( DISTINCT mail) FROM client

Exercice 17:

Dans la base magasin, afficher les coordonnes des clients ayant la mme adresse
(mme adresse et mme ville).

Solution

Requte SQL :

SELECT nom,prenom,adresse,ville,mail FROM client WHERE adresse='75
Bd Hochimin' AND ville='Lille'

Avec PHP, si ladresse et la ville taient contenues respectivement dans les
variables $adresse et $ville on aurait le code suivant :

SELECT nom,prenom,adresse,ville,mail FROM client WHERE
adresse='$adresse' AND ville='$ville'

Exercice 18:

Dans la base magasin, slectionner tous les articles commands par chaque client.

Solution

Requte SQL :

SELECT nom,prenom,article.id_article,designation
FROM `client` ,commande,article,ligne
WHERE client.id_client=commande.id_client
AND ligne.id_comm=commande.id_comm
AND ligne.id_article=article.id_article
ORDER BY nom

Exercice 19:

Dans la base magasin, slectionner tous les clients dont le montant dune
commande dpasse 1 500 .

Solution

Requte SQL :

SELECT nom,prenom, ligne.id_comm, sum(prixunit*quantite) AS 'total'
FROM client,ligne,commande
WHERE ligne.id_comm=commande.id_comm
AND commande.id_client=client.id_client
GROUP BY ligne.id_comm
HAVING sum(prixunit*quantite)>1500

Exercice 20:

Dans la base magasin, slectionner tous les clients dont le montant total de toutes
les commandes dpasse 5 000 .

Solution

Requte SQL :

SELECT client.id_client, ligne.id_comm, sum(prixunit*quantite)
FROM client,ligne,commande
WHERE ligne.id_comm=commande.id_comm
AND commande.id_client=client.id_client
GROUP BY client.id_client
HAVING sum(prixunit*quantite)>5000

Exercice 21:

Dans la base voitures, slectionner tous les vhicules dune personne donne.

Solution

Requte SQL : Nous cherchons par exemple tous les vhicules de M. Algout.

SELECT cartegrise.immat,modele,proprietaire.id_pers
FROM voiture,modele,proprietaire,cartegrise
WHERE proprietaire.nom='Algout'
AND proprietaire.id_pers=cartegrise.id_pers
AND cartegrise.immat=voiture.immat
AND voiture.id_modele=modele.id_modele

Exercice 22:

Dans la base voitures, slectionner toutes les personnes ayant le mme modle de
voiture.

Solution

Requte SQL : Nous cherchons par exemple tous les propritaires de vhicules de
type Picasso .

SELECT
proprietaire.nom, proprietaire.prenom,modele.modele,modele.carburant
FROM voiture,modele,proprietaire,cartegrise
WHERE modele LIKE '%Picasso'
AND voiture.id_modele=modele.id_modele
AND cartegrise.immat=voiture.immat
AND proprietaire.id_pers=cartegrise.id_pers

Exercice 23:

Dans la base voitures, slectionner tous les vhicules ayant plusieurs
copropritaires.
Solution
Requte SQL :
SELECT cartegrise.immat FROM cartegrise
GROUP BY immat
HAVING count(*) >1
Examen SGBDR MYSQL Exercices SQL
SQL 0
Examen SGBDR MYSQL Exercices SQL

Soit une base de donnes qui gre les formations du personnel :






Travail Faire :

1/ Faites la cration des 3 tables laide de SQL (1.5pts)

2/ Faites le remplissage laide des requtes paramtrs des 3 tables (0.75Pts)

3/ Affichez le nombre de Formation effectus entre le 01/03/2008 et le 01/12/2008
(1 Pts)

4/Affichez les formations dont le cot est suprieur au cout moyen des formations
(1.5pts)

5/Affichez les Personnes qui ont faits des formations en Fiscalit et dont le nombre
dheures des formations est suprieur au minimum du nombre
dheures (1.5pts)

6/ Afficher les modules effectus par Mr Benamar (0.5pts)

7/ afficher la recette globale des formations effectus par du personnel habitant
Fs (1.25pts)

Examen SGBD SQL Devoir Bases de Donnes Exercices
Bases de Donnes, SQL 0
Partie I :
Quelles sont les tapes dun cycle de vie dun logiciel ?
Dfinir larchitecture 3 tiers
Dfinir et expliquer quoi servent les concepts suivants :

a. Un Trigger
b. Une contrainte dintgrit

Partie II :

Soit les tables suivantes :

Candidats compos des champs suivants :
Matricule : Numro dimmatriculation du candidat (cl primaire)
Nom : nom du candidat
DateNaissance : date de naissance du candidat
DateDiplome : date dobtention du diplme
Code_ecole : code de lcole qui a dlivre le diplme (cl etrangre)

Ecole compos des champs suivants :
Code_ecole : (cl primaire)
Lib_ecole : intitul de lcole

Ecrire en langage SQL les requtes permettant de :

Insrer dans la table candidats un nouveau candidat ayant le matricule 3200, nomm Albert , n
le 12/05/1980, et qui a obtenu son diplme le 15/08/2000 dlivr par lcole ayant le code 03.
Avoir la liste des candidats tris par ordre croissant des Ecoles.
Avoir la liste des candidats laurats de lcole XXXXX .
Calculer lage moyen des candidats.

Partie III :

La socit IMPTON est spcialise dans le recyclage des toners dimprimantes. Pour grer une partie de leur
mtier le directeur accde la base de donnes dont le schma est le suiavant :

CARTOUCHE (RefCartouche, DesCartouche, PrixCartouche)
IMPRIMANTE (RefImp, DesignationImp, #CodeType)
TYPE (CodeType, LibelleType)
COMPATIBILITE ( #RefImp, #RefCartouche)

Un attribut (ou un ensemble dattributs) soulign est une cl primaire. Les cls trangres sont
prcdes par #.
La table CARTOUCHE renseigne sur tous les modles de cartouches vendues par la socit IMPTON.
La table IMPRIMANTE renseigne sur tous les modles dimprimantes existantes.
La table TYPE contient trois lignes (Imprimantes Matricielle, Imprimante Laser, Imprimante Jet dencre).
La table COMPATIBILITE permet de connatre toute imprimante acceptant un modle de cartouche, et
rciproquement, toutes les cartouches compatibles avec un modle dimprimante.

Questions :

Prsenter le schma du modle conceptuel des donnes (MCD) du modle logique indiqu ci-dessus.
Quest ce quune cl trangre ?
Exprimer la requte pour crer la table CARTOUCHE.
Exprimer en langage SQL la requte permettant dobtenir le Nombre et prix de vente moyen des
cartouches compatibles avec les imprimantes jet dencre.
Exercices SQL TP langage SQL TD base de donnes SGBD
Requtes SQL
SQL 0
Prsentation de la base de donnes



Lgende :

Les cls primaires sont soulignes
Les cls externes sont indiqus par un symbole " + ".
Les flches indiquent les liens entre les tables o une tte de flche simple pointe vers la cl primaire et
la tte de flche double pointe vers la cl externe.
Les colonnes "quantite" et "cout" sont d'un type numrique.
Les colonnes dont le nom comporte le mot "date" sont de type date.
La colonne "taxable" est de type "tinyint", n'ayant que les valeurs 0 pour FAUX, et 1 pour VRAI.
Toutes les autres colonnes sont d'un type caractre.

Note :
Pour utiliser les dates dans un format uniforme, lancer la commande suivante au dbut de votre session dans
l'Analyseur de requtes :
SET DATEFORMAT YMD

Par la suite, les valeurs "date" sont prsentes et saisies dans le format international anne-mois-jour (ex: 2004-
10-23).

1. Produisez la liste complte des classes de produit.

2. Produisez la liste complte des chefs de projet (matricule, nom).

3. Produisez la liste contenant le numro, la description et la quantit, des produits en inventaire qui valent au
moins $50.00 l'unit.

4. Produisez la liste des sorties d'inventaire o la quantit sortie est suprieure un (1) mais infrieure dix (10).

5. Modifiez le cot du produit 6794 $17.95.

6. Prsentez la liste des sorties d'inventaire qui ont t effectues entre le 10 mai 1989 et le 25 fvrier 1990,
exclusivement.

7. La liste (code_produit, description et quantit) des produits qui ont au moins 15 units en inventaire.

8. La liste des sorties d'inventaire du projet 'P1206' o la quantit sortie est suprieure un (1).

9. Effacer de la base de donnes la produit dont le code est '78-U'.

10. Ajoutez un nouveau projet, dont le code est P1384. Il est dirig par le chef de projet dont le matricule est 106,
et il doit dbuter le 20 octobre 1994.

11. La liste des produits, code et description seulement, que l'on retrouve dans une tagre dbutant par '21' ou
'11'.

12. La liste des produits (classe, code_produit, description et tagre) que l'on retrouve dans la section d'tagre
'S' (ex: '99S99') et qui n'ont pas de quantit en inventaire.

13. La liste des sorties d'inventaire (no_sortie, code_produit, code_projet et quantit) qui ont t effectues avec
le produit 'BXM100', ou qui ont t effectues pour le projet 'P1259'.

14 Modifiez l'unit de mesure des produits appartenant la classe 'C10' et entreposes la range 'L' pour la
valeur 'UN'.

15. Fournissez la liste des numros des projets ayant dbut entre le 24 mai 1987 et le 4 mars 1990
inclusivement.

16. Le produit dont le code est 128R a t mal saisi ; sa classe doit tre C10, son tagre doit tre 03T33 et son
cot unitaire est 4 588.23$. Faites les modifications ncessaires.

17. Prsentez les sorties d'inventaire affectes au projet P1208, en autant qu'elles n'ont pas eu lieu entre le 5
avril 1991 et le 26 mars 1992, inclusivement.

18. Quel est le code des projets dont la date de fin est inconnue ?

Cours de bases de donnes - TP PLSQL SGBD relationnels
exercices avec des exemples Requtes PL/SQL
Oracle, SQL 1
1 - Interrogation avec SQL

Au dpart, vous avez accs au schma et la base de donnes Films , vue et revue en cours, qui est
partage (en lecture) par tout le monde. Voici le script de cration de ce schma (disponible sur le site). Ces
commandes doivent maintenant vous tre familires (sinon relisez les chapitres correspondant).
Exemple 1 SchemaFilms.sql : Le script de cration du schma

/*
Commandes de cration de la base Films, test avec MySQL et PostgreSQL.
Pour Oracle, il suffit de remplacer le type TEXT par le type LONG dans la table Film.
*/

/* Destruction eventuelle des tables existantes */
DROP TABLE Notation;
DROP TABLE Role;
DROP TABLE Film;
DROP TABLE Artiste;
DROP TABLE Internaute;
DROP TABLE Pays;
DROP TABLE Genre;

/* Creation des tables */

CREATE TABLE Internaute (email VARCHAR (40) NOT NULL,
nom VARCHAR (30) NOT NULL ,
prenom VARCHAR (30) NOT NULL,
region VARCHAR (30),
CONSTRAINT PKInternaute PRIMARY KEY (email));

CREATE TABLE Pays (code VARCHAR(4) NOT NULL,
nom VARCHAR (30) DEFAULT Inconnu NOT NULL,
langue VARCHAR (30) NOT NULL,
CONSTRAINT PKPays PRIMARY KEY (code));
CREATE TABLE Artiste (idArtiste INTEGER NOT NULL,
nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL,
anneeNaiss INTEGER,
CONSTRAINT PKArtiste PRIMARY KEY (idArtiste),
CONSTRAINT UniqueNomArtiste UNIQUE (nom, prenom));

CREATE TABLE Film (idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
genre VARCHAR (20) NOT NULL,
/* Remplacer TEXT par LONG pour ORACLE */
resume TEXT,
codePays VARCHAR (4),
CONSTRAINT PKFilm PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);

CREATE TABLE Notation (idFilm INTEGER NOT NULL,
email VARCHAR (40) NOT NULL,
note INTEGER NOT NULL,
CONSTRAINT PKNotation PRIMARY KEY (idFilm, email));

CREATE TABLE Role (idFilm INTEGER NOT NULL,
idActeur INTEGER NOT NULL,
nomRole VARCHAR(30),
CONSTRAINT PKRole PRIMARY KEY (idActeur,idFilm),
FOREIGN KEY (idFilm) REFERENCES Film,
FOREIGN KEY (idActeur) REFERENCES Artiste);

CREATE TABLE Genre (code VARCHAR (20) NOT NULL,
CONSTRAINT PKGenre PRIMARY KEY (code));

Vous pouvez remarquer que lordre de cration des tables respecte le rfrencement entre PRIMARY KEY et
FOREIGN KEY. Les tables qui sont rfrences par cette dernire clause doivent tre cres avant celles qui les
rfrencent. Par exemple la table Artiste est cre avant la table Film cause de la cl trangre idMES. Cest
en revanche lordre inverse qui est suivi pour les commandes DROP : on ne peut pas dtruire une table qui est
rfrence par une commande FOREIGN KEY. Notez quen principe on ne place pas les commandes DROP
dans un script de cration puisquon ne souhaite pas prendre le risque de dtruire des donnes existantes.
Comme il sagit ici dune base de test, la situation est diffrente.

La base est disponible sur le site et contient un chantillon de lms avec leur metteur en scne, leurs acteurs et
les notations de quelques internautes. vous de jouer : il faut concevoir, saisir et excuter les ordres SQL
correspondant aux requtes qui suivent.

1.1 Slections simples

1. Tous les titres de lms.
2. Nom et prnom des internautes auvergnats.
3. Titre et anne de tous les drames, tris par anne ascendante. Donnez ensuite le tri par anne descendante.
4. Nom et anne de naissance des artistes ns avant 1950.
5. Titre et anne de tous les lms parus entre 1960 et 1980
6. Tous les genres de lms (liminez les doublons).
7. Titre, genre et rsum des lms qui sont soit des drames, soit des westerns (utilisez la construction IN), et dont
le rsum contient la chane de caractres vie .
8. Les artistes dont le nom commence par H (commande LIKE).
9. Quels sont les acteurs dont on ignore lanne de naissance ? (Attention : cela signie que la valeur est
absente).
10. Prnom, nom et ge de chaque artiste (NB : lge est la diffrence entre lanne courante et lanne de
naissance). Nommez ge la colonne obtenue (commande AS).

1.2 Jointures

1. Qui jou le rle de Morpheus (nom et prnom) ?
2. Qui est le ralisateur de Alien ?
3. Prnom et nom des internautes qui ont donn une note de 4 un lm (donner aussi le titre du lm).
4. Quels acteurs ont jou quel rle dans le lm Vertigo ?
5. Films dont le ralisateur est Tim Burton, et un des acteurs est Johnny Depp.
6. Titre des lms dans lesquels a jou Bruce Willis. Donner aussi le nom du rle.
7. Quel metteur en scne a tourn dans ses propres lms ? Donner le nom, le rle et le titre des lms.
8. Quel metteur en scne a tourn en tant quacteur (mais pas dans son propre lm) ? Donner le nom, le rle et le
titre des lms o le metteur en scne a jou.
9. Dans quels lms le metteur en scne a-t-il le mme prnom que lun des interprtes ? (titre, nom du metteur en
scne, nom de linterprte). Le metteur en scne et linterprte ne doivent pas tre la mme personne.

1.3 Requtes imbriques

Les requtes suivantes peuvent sexprimer avec une imbrication des clauses SELECT, mais on peut
galement utiliser des jointures plat . Si le cur vous en dit, essayez les deux versions.

1. Donnez les nom et prnom des artistes qui on mis en scne un lm.
2. Donnez le titre et anne des lms qui ont le mme genre que Matrix.
3. Donnez le nom des internautes qui ont not le lm Alien. Donnez galement la note.

1.4 Ngation

L encore, il existe souvent plusieurs manires dexprimer la mme requte.

1. Les lms sans rle.
2. Nom et prnom des acteurs qui nont jamais mis en scne de lm.
3. Les internautes qui nont pas not de lm paru en 1999.

1.5 Fonctions de groupe

1. Quelle est le nombre de lms nots par linternaute [email protected], quelle est la moyenne des notes donnes,
la note minimale et la note maximale ?
2. Combien de fois Bruce Willis a-t-il jou le role de McClane ?
3. Anne du lm le plus ancien et du lm le plus rcent.
4. id, Nom et prnom des ralisateurs, et nombre de lms quils ont tourns.

2 - Cration dun schma relationnel

Il sagit de dnir un schma de base de donnes, dy intgrer des contraintes, des vues et dy insrer quelques
informations. Vriez le comportement des contraintes et des vues en essayant de les mettre en dfaut.

2.1 Cration des tables

Crez les tables du schma Agence de voyages, vues en cours, et rappeles ci-dessous.

Station (nomStation, capacit, lieu, rgion, tarif)
Activite (nomStation, libell, prix)
Client (id, nom, prnom, ville, rgion, solde)
Sejour (id, station, dbut1, nbPlaces)

Attention bien dnir les cls primaires et trangres. Voici les autres contraintes portant sur ces
tables.

1. Les donnes capacit, lieu, nom, ville, solde et nbPlaces doivent toujours tre connues.
2. Les montants (prix, tarif et solde) ont une valeur par dfaut 0.
3. Il ne peut pas y avoir deux stations dans le mme lieu et la mme rgion.
4. Les rgions autorises sont : Ocean Indien, Antilles, Europe, Ameriques et Extreme Orient.
5. La destruction dune station doit entraner la destruction de ses activits et de ses sjours.
6. Le prix dune activit doit tre infrieur au tarif de la station et suprieur 0.
7. Pour une date de dbut donne, le nombre total de places rserves dans une station doit tre infrieur la
capacit de la station.

Conseil : donnez des noms vos contraintes PRIMARY KEY, FOREIGN KEY et CHECK avec la clause
CONSTRAINT.









FIG. 1 La base Agence

2.2 Insertion de donnes

Insrez dans la base les donnes de la gure 1 avec des ordres INSERT. Attention, lordre des INSERT est
important (pourquoi ?).

Vous pouvez ensuite tester les contraintes avec quelques ordres SQL. Par exemple : dtruisez la station et
vriez que les activits ont disparu ; insrez une autre station en (Guadeloupe, Antilles) ; insrez une station
dans une rgion Nullepart, etc.

2.3 Vues

Maintenant il faut crer des vues et tester linterrogation et la mise jour travers ces vues.

1. Crez les vues suivantes sur le schma prcdent.

(a) Une vue ActivitesModiques (Station, Activite) donnant le nom des stations et des activits dont le prix est
infrieur 140 FF. Toute ligne insre dans cette vue doit apparatre dans la vue ensuite.

(b) Une vue ActivitesCheres, de mme schma, avec prix suprieur 140 FF, et la mme contrainte dinsertion.

(c) Une vue StationDollars (Nom, Capacite, Lieu, TarifDollar) donnant le nom dune station, sa capacit, le lieu et
le tarif en dollars (metez le taux de conversion en dur , ou bien mieux crez une table stockant le taux de
conversion).

(d) Une vue Tarifs (Station, Tarif, OptionMin, OptionMax) donnant, pour chaque station, le tarif et les prix min et
max des activits.

(e) Une vue Reservation (nomStation, PlacesReservees) donnant, par station et date de dbut de sjour, le
nombre de places rserves.

2. Consultez ensuite le contenu de ces vues. Vous pouvez insrez quelques lignes supplmentaires dans
les tables et constater quelles sont prises en compte dans les vues.

3. Dans quelles vues peut-on insrer, dtruire et mettre jour ? Essayez les oprations suivantes :

(a) Insrez une activit Kayac pour la station Venusa dans ActivitesCheres et ActivitesModiques. Le contrle
sur linsertion est-il utile dans ce cas ?

(b) Peut-on insrer dans StationEuro ? Sous quelle condition ? Faites lessai.

(c) Dtruisez une ligne de StationEuro.

3 - Programmation

Les exercices qui suivent permettent de se familiariser avec les langages de programmation interne (PL/SQL) et
externe (Java/JDBC, PHP) permettant de manipuler une base de donnes de manire procdurale. Des
exemples sont fournis sur le site, qui doivent vous permettre de crer vos propres procdures, fonctions et
programmes.

3.1 Procdures stockes

Le langage utilis est le PL/SQL version PostgreSQL. Quelques modications mineures sufsent transcrire pour
le PL/SQL dOracle.

1. Crer une fonction NomClient qui prend en entre lid dun client et qui renvoie une chane contenant le
prnom et le nom du client (voir lexemple ralisateur .plsql).

2. Crer une fonction Activits qui prend en entre le nom du station et produit une chane de caractres
contenant lnumration des activits de la station (par exemple, Ski, Yoga, Massage).

3. Crer ensuite une vue qui afche les stations, avec un attribut supplmentaire donnant la liste des activits
(par appel la fonction bien sr).

4. Crer une fonction Actualiser qui prend en entre un pourcentage et le nom dune station, et augmente le tarif
de la station et le prix de chacune de ses activits du pourcentage indiqu.

3.2 Triggers

1. Implantez par un trigger la rgle suivante : si le prix dune activit baisse, alors le tarif de la station doit
augmenter de la diffrence.

Indication : le trigger doit se dclencher sur une modication, et tester pour chaque ligne que la nouvelle valeur
est plus grande que lancienne. Si ce nest pas le cas, faire un UPDATE de la station pour ajouter la diffrence
entre lancienne et la nouvelle valeur.

2. Faites lexprience : diminuez le prix dune activit, et regardez ce qui se passe pour la station.

3. On veut disposer de linformation nbActivites dans la table Station. Pour cela :

(a) Ajoutez la colonne nbActivites avec pour valeur par dfaut 0.
(b) Crez un trigger qui maintient cette information.

4. Interdisez par un trigger linsertion dune ligne dans la table Sjour si le solde du client est infrieur au
nombre de places multipli par le tarif de la station.

3.3 Applications externes : PHP

Il ny a aucune limite (enn, presque), ce que lon peut faire avec une programmationHTML/PHP/PostgreSQL
(ou nimporte quelle autre SGBD). Pour commencer vous devez installer les trois scripts vus en cours qui
montrent les principales techniques (connexion, excution dune requte, interrogation par formulaire).

Rcuprez les chiers sur le site (ils sont dans une archive pgphp.tar disponible dans la page des exemples).

Vous devez ensuite effectuer les oprations suivantes :

1. Crer les tables FilmComplet et FilmSimple avec les deux scripts FilmSimple.sql et FilmCOmplet.sql
2. Insrer quelques lignes dans la table FilmSimple avec le script InsFilmSimple.sql
3. Modier les paramtres de connexion dans Connect.php
4. ExPGSQL1.php interroge et afche le contenu de FilmSimple.
5. ExForm3.html afche un formulaire pour rechercher des donnes en lanant le script ExPGSQL2.php
6. ExForm4.html afche un formulaire pour insrer, modier oui dtruire des donnes en lanant le script
ExPGSQL3.php

Voici quelques suggestions pour des scripts PHP sur la base Station.

1. Rcuprez les scripts PHP donns en exemple, et travaillant sur la table FilmSimple, et adaptez-les
pour saisir et modier les informations de la table Client.

2. Faites un formulaire de saisie dun sjour, en proposant la liste des stations et la liste des clients dans
un menu droulant.

4 - Concurrence daccs

On va maintenant tudier le comportement concret dun SGBD en cas daccs concurrents la mme ressource.
Pour cel on va simuler lexcution concurrente de programmes laide du petit ensemble de lectures/critures
sur la base Agence de voyage cr dans le premier exercice : modication du solde de certains clients et
slection des clients. Crez 4 chiers, nomms SEL.sql, MAJpas.sql, MAJfog.sql et MAJker.sql et entrez-y les
commandes suivantes :

1. SEL.sql
PROMPT Affichage des clients =>;
SELECT * FROM client;
2. MAJpas.sql
PROMPT Augmentation du client Pascal =>;
UPDATE client SET solde = solde + 1000
WHERE client = Pascal;

3. MAJfog.sql
PROMPT Augmentation du client Fogg =>;
UPDATE client SET solde = solde + 1000
WHERE client = Fogg;

4. MAJker.sql
PROMPT Augmentation du client Kerouac =>;
UPDATE client SET solde = solde + 1000
WHERE client = Kerouac;

Ensuite, ouvrez deux fentres et lancez SQLPLUS dans chacune : chaque session est considre par ORACLE
comme un utilisateur, et on a donc 2 utilisateurs, nomms 1 et 2, en situation de concurrence.

Dans tout ce qui suit, on note INSTRi lexcution de linstruction INSTR par lutilisateur i. Par exemple MAJker1
corespond lexcution du chier MAJker dans la premire fentre par la commande @MAJker. On note de
mme ROLi et COMi lexcution des commandes rollback; et commit ;dans la fentre i.

Questions Executez les squences dinstruction dcrites ci-dessous. Dans chacun des cas, expliquez ce qui se
passe.

1. Premire exprience : lutilisateur 1 effectue des mises--jour, tandis que lutilisateur 2 ne fait que des
slections. Que constate-t-on ?

SEL1; SEL2;MAJker1; SEL2;MAJpas; SEL2;ROL1; SEL2.

2. idem, mais avec des commit.

SEL1; SEL2;MAJker1; SEL2;COM1; SEL2;MAJker1; SEl2;COM1; SEL2.

3. Maintenant les deux utilisateurs effectuent des MAJ simultanes.

SEL1; SEL2;MAJker1;MAJpas2; SEl1; SEL2;MAJfog1;MAJfog2; SEL1;COM1;COM2.
Un blocage est apparu. Pourquoi ?
4. Idem, avec un ordre des oprations qui diffre dun utilisateur lautre.

SEL1; SEL2;MAJker1;MAJpas2; SEl1; SEL2;MAJpas1;MAJker2ROL1ROL2.
Que constate-t-on ?

5.En fait, ORACLE pratique une verrouilage deux phases assez libral, qui ne garantit pas la
srialisabilit : aucun verrrou nest plac sur une ligne lors dune lecture. Lutilisateur peut verrouiller
explicitement en ajoutant la clause FOR UPDATE. Exemple :

SELECT * FROM client FOR UPDATE;

Chaque ligne slectionne est alors verrouille. Refaites les expriences prcdentes avec un verrouillage
explicite des lignes que vous voulez modier.

6. Exprimentez les excutions prcdentes en spciant le mode suivant :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Exercices SQL niveau avanc TP SQL
SQL 0
Exercices : SQL Avanc

Objectif de l'exercice:

Utiliser les notions de valeur nulle, tris, expressions statistiques en SQL

Les champs des tables de donnes sont les suivants:

ENO: Numro d'employ (cl)
ENOM: Nom d'employ
PROF: Profession
DATEEMB: Date d'embauche
SAL: Salaire
COMM: Commission (un employ peut ne pas avoir de commission)
DNO: numro de dpartement
DNOM: Nom de dpartement
DIR: Directeur du departement
VILLE: Localisation du dpartement

Considrez les Tables de Donnes suivantes:

Employs:


Departements:


Exprimez les requtes suivantes :

en Algbre relationnelle
en SQL

Requte 1:

Donnez la liste des employs ayant une commission

Requte 2:
Donnez les noms, emplois et salaires des employs par emploi croissant, et pour chaque emploi, par
salaire dcroissant

Requte 3:

Donnez le salaire moyen des employs

Requte 4:

Donnez le salaire moyen du dpartement Production

Requte 5:

Donnes les numros de dpartement et leur salaire maximum

Requte 6:

Donnez les noms des employs ayant le salaire maximum dans chaque dpartement

Requte 7:

Donnez les diffrentes professions et leur salaire moyen

Requte 8:

Donnez le salaire moyen par profession le plus bas

Requte 9:

Donnez le ou les emplois ayant le salaire moyen le plus bas, ainsi que ce salaire moyen

B. Exercice B

Cration de tables
Donnez les requtes SQL qui permettent de crer les tables suivantes. Indiquer les contraintes sur les
champs, ainsi que les cls primaires et trangres.

Table Magasin, qui contient les champs suivants:

nom du magasin
nom du grant
prnom du grant
date de cration
nombre de clients
chiffre d'affaire.

Table Personne, qui contient les champs suivants:

prnom
nom
fonction (grant, employ, client)
telephone
date de naissance.