Chapitre II

I/introduction :
TRANSACT SQL est un langage qui permet de programmer des algorithmes de
traitement des données au sein des SGBDR.
II/Conception de bases :
1/les commentaires :
Elle existe 2 façons pour faire les commentaires en TRANSACT SQL
a-Commentaire sur une ligne :
On doit précéder le commentaire par deux traits - -
Exemple :
- - mon commentaire
b-Commentaire sur plusieurs lignes :
On doit encadrer le texte par /* et */
Exemple :
/*
Mon premier
Commentaire
*/
2/Les identificateurs :
Le langage TRANSACT SQL fournit une série de règles de dénomination standard pour
les identificateurs d’objet :
a. Un identificateur standard peut contenir de 1 à 128 caractères
b. Un identificateur commence par une lettre ou Under score(_)
c. Les caractères spéciaux et le blanc ne sont pas admis
3/Les variables :
Permet de stocker des valeurs, pour pouvoir utilisé une variable, on doit utiliser le mot
‘’declare’’ pour la déclaré, le nom d’une variable locale est précédé par le caractère @ tandis
que les variables globales doivent être précédé par @@
Syntaxe1 :
Declare @var1 type
Exemple1 :
Declare @nb1 Int
→ Dans cet exemple on a déclaré une variable nommé nb1 de type entier (Int)
Example2:
Declare @nom varchar (50)
Declare @note float
* Vous pouvez également déclarer plusieurs variables à la fois :
Syntaxe2 :
Declare @var1 type1, @var2 type2 , @var3 type3
Exemple 1 :
Declare @nom varchar (16) , @note float
-Contrairement à de nombreux langages de programmation comme c ++ , java , pascal, si vous
déclarez plusieurs variables de même type , le nom de chaque variable doit être suivit par son
propre type.
-Pour remplir une variable on doit utiliser les instructions « Set » ou « Select »
Exemple 2 :
Declare @nb int
Set @nb=25 - - affectation
Print @nb - -affiche le contenu de nb
Exemple2 :
Declare @a int, @b int,@sm int
Set @a=10
Set @b=20
Set @sm=@a+@b
Print @sm
ou
Print ' la somme est : ' + cast(@sm as varchar(20)) –>convertir @sm en type texte pour
l’afficher
Exemple3 :
Declare @nom varchar(30)
Select @nom= nom_et from etudiants where note= (select max (note) from etudiants)
Print (' le produit le plus élevé est : ' + @nom)
Exemple4
Declare @note float
Declare @nom varchar(30)
Select @note=note, @nom=nom_et from etudiants
Where num_et= 114
Select @nom_et as nom , @note as prenom - -Affichage
Ou
Print @nom_et + @note
Exemple5
declare @nom varchar(50),@prix decimal
select @nom=dsg, @prix=prix_vente from produits
where prix_vente=(select min(prix_vente) from produits)
print ('le produit le plus élevé est: << ' + @nom
+ ' >> dont son prix est: ' +cast(@prix as varchar(5)))
Remarque: dans le cas d’une requête renvoyons plusieurs valeurs, seule la dernière valeur sera
récupérer dans la variable
4- variables systèmes :
Variable Description
@@connections Nombre de connexions actives
@@datefirst Cette variable permet de stocker le 1er jour de la semaine.
On peut modifier cette valeur en utilisant l’instruction
suivante
Set @@datefirst 4
@@error Il nous permet de stocker le code erreur de la dernière
opération exécutée (0 si n’ya pas d’erreur)

4/ les opérateurs :
a\ les opérateurs arithmétiques
+ : addition
- : soustraction
* : multiplication
/ : division
% : modulo (reste de la division)
Exemple :
Declare @nb1 int, @nb2 int, @res int
Set @nb1=9
Set @nb2= 4
Set @res= @nb1*@nb2
Print @res /* affiche 36 */
Set @res= @nb1+@nb2
Print @res /* affiche 13 */
Set @res= @nb1%@nb2
Print @res /* affiche 1*/
Set @res=@nb1-@nb2
Print @res /* affiche 5 */
b\ les opérateurs logiques :
 • And : true si les deux expressions sont true →binaire
• Or : renvoi true si en moins une expression est true→binaire
• Not : inverse la valeur →unaire
• All : vrai si tous les éléments sont vrai
• Any : vrai si n’importe quel élément est vrai
• Between : vrai si l’opérande est situé dans une certaine plage
• Exists : renvoie vrai si une sous requête contient des lignes
• In : renvoi vrai si l’opérande à un élément d’une liste
• Like : renvoi vrai si l’opérande est égal un modèle
• Some : renvoi vrai si certaine éléments de l’expression est vrai
5\ Structures conditionnelles :
Syntaxe :
*Forme simple :
If condition
Begin
Instructions
End
Exemple1 :
Declare @nb int
Set @nb=5
If @nb > 0
Print ‘le nombre est positif ‘
Exemple 2 : vérifier si une classe est plein
Declare @num int
Set @num=1
If(select count(*) from etudiant where num_cls=@num)=30
Begin
Print ‘classe plein’
End

*Forme complexe :
If condition
Begin
Instruction 1
End
Else
 Begin
Instruction 2
End
Exemple 1 :
Declare @nb int
Set @nb=10
If @nb > =0
Print ‘nombre positif’
Else
Print ‘nombre negatif’
Exemple 2 : (la moyenne d’un étudiant)
Declare @code int
Set @code = 100 --code etudiant
If (select note from Etudiants where num-et =@code) >10
Print ‘l’étudiant est au-dessus de la moyenne donc il est admis ’
Else
Print ‘l’étudiant est en dessous de la moyenne donc non admis’
Exemple 3 : ( prof coordinateur à une classe)
Declare @prof int
Set @prof=7 --numprof
If (select count(*) from classe where numprof=@prof) >=1
Print ‘le prof est un coordonateur ’
Ou
If exists (select * from classe where numprof=@prof)
Print ‘le prof est un coordonateur ’
Ou
If @prof in (select num_prof from classe)
Print ‘le prof est un coordonateur ’
Exercice1 :
Ecrire un script TSQL qui permet de faire les taches suivantes :
1. Déclarer 3 variables :
Num, nom et note
2. Affecter à ces 3 variables respectivement le numéro, le nom, et la note de l’étudiant
majorant de la classe1 :
3. Afficher le message :
Le majorant est : …………
 Numéro est : ……..
Nom est : ………..
Note est : ………………
Correction :
Declare @num int
Declare @nom varchar(20)
Declare @note float
Select @nom=nom_et, @num=num_et, @note=note from étudiants
where note=(select max(note) from étudiant where num_cls=1)
And num_cls=1
Print ’le majeur est’
Print ‘numero :’ +cast (@num as varchar(11))
Print’nom :’ +@nom // (les 2 ont le même type)
Print ‘note’+cast (@ note as varchar(10))
Exercice2:
Ecrire un script T.SQL qui permet d’afficher le nombre de filles et le nombre de garçons
de la classe2
Exemple d’affichage :
Nombre fille : 10
Nombre garçon : 25
Correction :
Declare @nbf int, @nbg int
Select @nbg=count(*) from etudiant where sex_et=’m’ and num-cls=2
Select @nbrf=count(*) from etudiant where sex_et=’f’ and num-cls=2
Print ‘le nombre de filles :’+cast (@ nbf as varchar(11))
Print ‘le nombre de garçon ;’+cast (@nbg as varchar(11))
Exercice3 :
Ecrire un script T.SQL qui nous permet d’afficher le genre du coordonnateur de la classe2
c’est-à-dire si le sex=f il affiche :’la classe 2 a un coordinatrice ’ sinon ’la classe 2 a
un coordonnateur ’
Declare @genre varchar(1)
Select @genre=sexe_prof from professeur p, classe c
where p.num_prof=c.num prof
and num_cls=2
If @genre=’f’
Print ’la classe 2 a une coordinatrice’
Else
 Print ‘la classe 2 a un coordinateur’
Exercice4 :
Ecrire un script qui permet d’afficher la mention de l’étudiant 114 c’est-à-dire :
• Faible si la note est <10
• Passable si la note est ] 10, 12]
• Assez bien si la note entre ] 10, 14]
• Bien si la note entre] 14, 16]
• Très bien si la note entre] 16, 18]
• Et Excellent si la note entre] 18, 20]
Declare @ note float
Select @note=note from etudiant where num-et=114
If @note <10
print ‘faible’
@note>10 and @note<=12
print ‘Passable’
@note>12 and @note<=14
Print ‘assez bien’
If @note>14 and @note <=16
Print ‘bien’
If @note>16 and @note <=18
Print ‘très bien’
If @note>18 and @note <=20
Print ‘Excellent’

7\ la boucle while
Syntaxe générale :
While condition
Begin
Instructions
End
Exemple :
Declare @i int
Set @i=10
While @i>0
Begin
Print ‘bonjour tout le monde’
 Set @i=@i-1
End
8\ procédures stockées :
- Une procédure stockée est un bloc de codes que l’on met sur le serveur est qui va
s’exécuter en suite directement sur le serveur. Les procédures stockées de SQL serveur
sont comme les procédures les autres langages de programmations :
Syntaxe de création de procédures :
Create procédure nom-proc @param1 type,@2
As
Begin
Instructions
End
Exemple :
- Creation d’une procedure qui affiche n fois ‘bonjour tout le monde’
Create procedure affiche bnj @n int
As
Begin
Declare @i int
Set @i=0
While @i <@n
Begin
Print ‘bonjour tout le monde’
Set @i=@i+1
End
End
Exemple d’utilisations : (l’élément de la procédure)
Execute affiche 25

➢ Les projets :
1- Site école
2- Site e-Commerce (micro choix.ma)*site chat php+ajax+mysql
3- Site réseau, social
4- Gestion de ressource humaine
5- Gestion de location de voiture
6- Gestion commercial
7- Annuaire des logiciels libres
8- Site hôtel
 9- Site annonces (achat/vente : voiture)
10- site Annonces (achat/vente : immobilier)
11- site annonces (achat/vente annuaire des fichiers PDF)
12- site annonces professionnel (adresse-téléphone)
8\ Exercice :
Ecrire une procédure qui permet de calculer le TTC d’un prix Ht passé en argument la
TVA=20%
Solution 1 :
Creat procedure calculé TTC 1 @pht as float
As
Begin
Declare @pTTc float
Set @pttc=@pht+@ht*0.2
Print @pttc
End
Solution 2 :
- en concéderont que TVA aussi un paramètre
Creat procedure calcul TTC2 @pht float, @tva float
As
Begin
Print @pht+@pht*@tva
End
Exercice :
Crée les procédures suivantes :
1- procedure affiche étudiant dont le numéro est passé en argument
Solution :
Creat procedure affiche etudiant @num as int
As begin
Declare @nom varchar(30)
Select @nom=nom-et from etudiant where num-et =@num
Print @num
Ou
As
Begin
Select nom-et from etudiant where num-et=@num
End
2- écrire une procédure NBR profs en permet d’afficher le nombre des professeurs
Solution 1 :
Creat procedure nbrprofs
As
Begin
Declare @nb=count(*) from etudiant
Print @nb
End
3- écrire une procédure qui permet d’afficher le nom de coordinateur de la classe dont le
numéro est passé en argument classe
Creat procedure affiche coord@numd as int
As
begin
select nom-prof from professeur p, classe c
where p-num-prof=c.num-prof
and
c.num-cls=@numd
End
4- écrire une procédure nbreEtdcls qui permet de calculer le nbr des étudiants dans
chaque classe

Méthode 1 :
Create num-cls, count(*) from etudiant group by num-cls
End
Creat procedure nomcls-nbre
As
Select nom-cls, count (num-et) from classe c, etudiant e Where c.num-cls=e.num-cls group
by nom-cls
Creat procedure nbrEt-classe @n :classe int
As
Begin
Declare @nbet int
Select @nbet=count(*) from etudiant where num-cls=@num classe
Print @nbrt
End
5- écrire une procédure modifier qui permet de modifier le coordonnateur d’une classe le
numéro du nouveau coordonnateur et la classe seront des arguments de la procédure
Solution :
 Create procedure modifiercoord @num-cls
As int,@num-prof as int
As
Update classe set num-prof=@num-prof where num-cls=@num-cls
6- modifier la procédure pour qu’elle vérifier si le nouveau professeur existe dans la table
professeur, dans le cas contraire vous devez afficher ‘’ le nouveau professeur n’existe
pas’’
Creat procedure mod-coord @ cls int, @num-p int
As begin
If exists (select num-prof from professeur where num-prof =@num-prof) update classe
Set num-prof=@numprof where num-cls=@cls
Else
Print ‘le professeur n’existe pas’
End
Exemple 7 :
Créer une procédure affetdcoord qui accepte en argument un numéro d’étudiant, le nom de
classe, et le nom du coordonnateur.
Creat procedure affetdclscoord @ numetd int
As
Begin
Select nom-et, nom-cls, nom-prof from etudiant e, classe c, professeur p where e.num-
cls=c.num-cls and c.num-prof=p.num-prof and num-et=@num-etd
End
Ou
As
Begin
Declare @ ne varchar(30)
Declare @ nc varchar(30)
Declare @ np varchar(30)
Select @ne=nom-et, @nc=nom-cls,@np=nom-prof from etudiant e, classe c, professeur p
where
Print ‘le nom d’étudiant : ‘+@ne
Print ‘le nom de la classe : ‘+@nc
Print ‘le nom de professeur : ‘+@np
End
Exemple 8 :
Créer une procédure aff nbretdcoord qui permet en argument un numéro de classe et qui
afficher le nombre d’étudiant et le nom de coordinateur de la classe
Creat procdure affnbretdcoord
As
Begin
Declare @nbr int
Declare @nom varchar (30)
Select @nbr=count (*) @num- from etudiant where num-cls =@num-cls
Select @nom=nom-prof from professeur p classe c where p.num-prof=c.num-prof and num-
cls=@num
Print ’le nombre des étudiants est :’+ cast(@nbr as coordinateur est :’ +@nom
End
Exemple 9 :
Ecrire affiche majorant, le nombre de sa classe, et le coordinateur. La procédure prend en
argument un numéro de classe.
Create procedure affiche majorant @num int
As
Begin
Declare @nom-maj varchar (30)
Declare @nom-cls varchar (30)
Declare @nom-coord varchar (30)
Select @nom-maj=nom-et from etudiant where num-cls=@num and not (select max (note)
from etudiant where num-cls=@num)
Select @num-cls from classe where num-cls=@num
Select @nom-coord =nom-prof from professeur p, classe c where p.nnums-prof=c.num-prof
and c.num-cls=@num
Print ‘le nom du majorant est:’[email protected]
Print ‘le nom de la classe est:’[email protected]
Print ‘le nom de coordinateur est:’[email protected]
End
Exemple 10 :
Ecrire une procédure affiche 10 notes premières qui permettent d’afficher les 10 premiers
étudiants dont le numéro de classe c’est passé en argument
Create procedure affiche 10 @num int
As
Select top 10nom-et from etudiant where num-cls=@num order by note desc
Exemple 11: create procédure affiche 10 notes deniers qui permettent d’afficher les 10étudiants
dont le numéro de classe c’est passé en argument
Create procdure afiche 10 @num int
As
Select top10 nom-et from etudiant where num-cls=@num order by note ask
Exemple 12:
Ecrire une procédure qui permet d’afficher la moyenne de la classe dont le nom du
coordinateur est passé en argument
Create procedure affiche moyenne cls@nom varchar (30)

As
Begin
If exists (select p.num-prof from professeur p, classe c where c.num-prof =p.num-prof and
Nom-prof=@prof=@nom)
Select AVG (note) as moyenne from etudiant e, classe c, professeur p where e.num-cls=c.num-
cls and c.num-prof=p.num-prof and nom-prof=@nom
Else
Print @nom-prof+’n’est pas coordinateur’
End
Chapitre N°3

Les déclencheurs (triggers) :

I-introduction :
Permet de renforcer l’intégrité de la base de données particulièrement l’intégrité référentielle.
Les triggers définis sur la base de données en deux rôles principaux :
- Contrôler que les données manipulées vérifier l’intégrité référentielle. par exemple créer
un nouveau étudiant nécessite de l’affecter à une classe existant.
- Mettre en œuvre des traitements correspondant à une règle de gestion de l’entreprise.
Lorsqu’un produit est passé en commande on mettre à jour la qualité en stock dans la
table produit.
II- les caractéristiques des triggers :
- Les caractéristiques des triggers sont :
a- Les triggers sont stockés avec les données de la base.
b- Les triggers sont un type particulier de procédure :
- Sont attachés à des table réagissent en fonctions de création (create, modification
(update) et suppression (delete))
- Ne peuvent pas être appelés explicitement.
c- Les triggers sont déclenchés automatiquement par le noyau à chaque intervention Sur la
table concernée.
d- Un trigger ne s’applique qu’un seul table, plusieurs tables ne peuvent pas utiliser le
même triggers.
e- La suppression d’une table entraine la destruction de ces triggers .le peuvent
f- Une table peuvent avoir au maximum 3 triggers, chaqu’unétant déclenché en fonction
de l’évènement détecté (insertion, modification, suppression)
III- deux tables vérétilles sont crées lorsque vous appliquées des opérations sur une table :
- INSERTED
- DELETED
Elles sont destinées à contenir les lignes de la table sur les quelles en étaient effectuées les
opérations.
- Les tables INSERTED et DELETED peuvent être utilisées par le trigger pour
déterminer comment le traitement doit se dérouler les mécanismes d’utilisation par SQL
serveur de ces deux tables différents en fonctions de l’opération effectuées (ajout,
modification, et suppression)
1\ cas de suppression :( DELETE)
- La \les lignes supprimées sont placés dès la table DELETE et supprimé de la table réel
2\ cas de création d’une nouvelle ligne : (insert)
La /les lignes nouvelles lignes sont placées dès la table INSERTD et de la table réel.
3\ cas de modification : (update)
La/les avant modifications sont placées dès la table DELETED et la /les lignes après
modification sont placées dès la table INSERTED et de la table réel.
- Création de trigger :
Syntaxes:
Create tigger nom-trigger on nom-table
For operation as
Insertions
Opération peut prendre les valeurs suivantes INSERT, DELETE, UPDATE
Exemple 1 :
- Un étudiant ne peut être crée que si la classe qui lui correspondant existe déjà dans table
classe
Create trigger TI-etudiant on etudiant
For insert as
Begin
If exist (select nom-cls from classe, inserted where classe.num-cls=insertd.num-cls)
Print ’étudiant ajouté’
Else
Begin
Rollback transaction
Print ‘ classe inexistant’
End
End
TP utilisation des triggers
Soit la base test-trigger, ayant le MCD suivant :

(0,
Produit Contenir facture
Ref-prod Qt-fact Num-fact
Designation Date-fact
PU Mode-reg
Qt-stock N) (1,N)

*MLD

Produit Contenir facture

Ref-prod Num-fact
Ref-prod
Designation Date-fact
Num-fact
PU Mode-reg
Mode-reg
Qt-stock

Create database test-trigger

Create table produit
(
Ref-prod int primary key
Designation varchar (20)
PU float,
Qt-stock float
)
Create table facture
(
Num-fact int primary key,
Date-fact datetime,
Mode-reg varchar (20)
)
Create table contenir
(
Ref-prod int,
Num-fact int,
Qt-fact float,
Constraint pkey contenir primary key (ref-prod, num-fact)
)
Alter table contenir
Add constraint pkaey-contenir1 foreign key (ref-prod) references produit (ref-prod)
Alter table contenir
Add constraint pkey-contenir2 foreign key (num-fact) references (num-fact)
Insert into produit values (20,’produit1’, 50, 100)
Insert into produit2 values (21,’produit2’, 150, 200)
Insert into produit2 values (21,’produit2’, 150, 200)
Insert into produit3 values (22,’produit2’, 270, 97)
Insert into facture values (115,’1/1/2011’,’espece’)
Insert into facture values (116,’10/1/2011’,’cheque’)
Insert into contenir values (20, 115, 22)
Insert into contenir values (22, 115, 300)
• Trigger lors de l’ajout d’une ligne à la table contenir.
Create trigger ti-contenir on contenir for insert as
Begin
Declare @refv int
Declare @ats float
Declare @atv float
Select @refv=ref-prod,@atc=qt-fact from inserted
Select @ats=qt-stock from produit where ref-prod=@refv if @atv<=@ats
Begin
Update produit set qt-stock=qt-stock_@atv where ref-prod=@ref
Print ‘operation terminé avec succées’
End
Else
Begin
Rollback transaction
Print’ qt insuffisente’
End
End
• Trigger lors de la suppression des données:
Create trigger td-contenir on contenir for delete as
Begin
Declare @ref ad int
Declare @qt-fact as float
Select @ref=ref-prod, @qt-fact=q-tfact from deleted
Update produit set qt-stock=qt-stock +@qt-fact where ref-prod =@ref
Print ‘suppression terminée’
End
• Trigger lors de la modification (on suppose qu’on va par modifier le champs ref-prod)
Create trigger td-contenir on contenir for as
Begin
Declare @ref as int
Declare @qt-fact as float
Declare @qt-fact as float
Declare @qt-fact2 as float
Declare @qt-stock as float
Select @ref=ref-prod,@qt-fact1=qt-fact from deleted
Select @qt-stock2=qt-stock from produit where ref-prod=@ref
If @qt-stock+qt-fac1>=@qt-fact2
Begin
Update produit set qt-stock=qt-stock+qt-fact1-@qt-fact2 where ref-prod=@ref
Print ‘operation terminée’
End
Else
Begin
 Rollback transaction
Print ’erreur: quantity insuffisent’
End
End
❖ Devoir libre
1- MLD:

editer editeur
Livre
Num-livre Num-editeur
Num-livre
Num-editeur Nom-editeur
Titre
Année-editeur Raison-social
theme

ecrire

Num-livre stocker
Num-
ecrivain Num-livre depot
Num-editeur
Num-depot
Num-depot
Qt-stock
Nom-depot
ecrivain

Num-ecrivain
Nom-ecrivain
prenom

2- Create database biblio

Create table livre
(
Num-livre int primary key,
Titre varchar (30),
Theme varchar (30)
)
Create table editeur
(
Num’editeur int primary key,
Nom-edit varchar (30),
Raison-social varchar (30)
)
Create table ecrivain
(
Num-ecrivain int primary key,
Nom-ecrivain varchar (20),
Prenom-ecrivain varchar (20)
)
Create table depot
(
Num-depot int primary key,
Nom-depot varchar (30)
)
Create table ecrire
(
Num-livre int foreign key references livre (num-livre),
Num-ecrivain int foreign key references ecrivain (num-ecrivain),
Constraint pkeyecrire primary key(num- livre,num-ecrire)
)
Create table editer
(
Num-livre int foreign key references livre (num-livre),
Num-editeur int foreign key editeur (num-editeur)
Année-edition int,
Constraint pkeyediter primary key (num-livre, num-editeur),
)
Create table stocker
(
Num-livre int foreign key referneces livre (num-livre),
Num-editeur int foreign key references editeur (num-livre),
Num-depot int foreign key editeur (num-depot),
Qt-stock float,
Constraint pkey stocké primary key (num-livre, num-editeur, num-depot)
)
3- Select *from editeur
4- Select titre, nomEditeur from editeur e, editer, livre l where
e.numEditeur=editer.numEditeur and l.num-livre=editer.num-livre
5- Select *from livre where titre like (‘p%’)
6- Select *from where= ‘programme’
 7- Select num-livre, titre, them from livre l, ecrire e, ecrivain e2 where l.num-
livre=e1.num-livre and e.num-ecrivain =e2.num-ecrivain and nom-ecrivain=’malk’ and
prenom-ecrivain=’driss’
8- Select e.num-editeur, nom-editeur, raison-social from editeur e, nomEditeur1, livre l,
ecrire e2, ecrivain e3 where e.num-editeur=e1.numEditeur and e1.numediteur=e1.num-
editeur and e1.num-livre2.num-livre ande2.numEcrivain=e3.numEcrivain and
nomEcrivain’malk’ and prenomEcrivain=’driss’
9- Select e.numEditeur, nomEditeur, raison-social from editeur e, editer e1, livre l where
e.numEditeur and e1.num-livre=l.num-livre and titre=livre=’phpfacile’
10- Select *from l where not existe (select *from stock s where=l.num-livre=s.num-livre
and s.num-depot=22)
Solution 1 :
Select *from livre where num-livre not in (select num-livre from stocker where num-depot=22)
Solution 2 :
11- Select *from livre where num-livre not in (select num-livre from stocker)
12- Select *from ecrivain where num-ecrivain not in (select num-ecrivain from ecrire)
13- Select *from livre where (select count (e.num-livre) from ecrire e where l.num-
livre=e.num-livre)=2
14- Select *from livre where titre like’%php%’
Solution 1 :
Select e1.numEditeur, nom’editeur, raison-social from editeur e1, editer e2, livre l
where e1.numEditeur=e2.numEditeur e2.num-livre=l.num-livre and titre=’java2’
AnnéeEdition= (select max (annéeEdition) from editer e, livre l where e.num-
livre=l.numLivre where e.numLivre and titre=’java2’)
Solution 2 :
Même réponse mais avec annéeEdition >=all (select annéeEdition from ……………..)
Solution 3 :
Select top1 e1.numEditeur, nomEditeur, raisonSocial from editeur e1, editeur e2, livre l
e1.numEditeur=e2.numEditeur e2.numLivre=l.numLivre and titre= ‘java2’ order by
annéeEdition desc
15- Select count(*) from livre
16- Select nomDepot, sum (qtStock s, livre l where d.numDepot d, s.numDepot and
s.numLivre and l.titre=’java2’, group by nomDepot
17- Select them, count (num-livre) from livre groupp by them
18- Select count (distinct them) from livre
Partie B
23- create procedure NVlivredepot @num int
 As
Begin
Select sum cqtstock from stocké where numDepot=@num
End
24- create procedure affiche-livre-auteur @nom varchar(56)
As
Begin
Select l.num-livre, titre, them from livre l, ecrire e1, ecrivain e2 where l.num-livre=e1.num-
livre and e1.num-ecrivain=e2.num-ecrivain and nom-ecrivain=@nom
End
25- create procedure NBRauteurEditeur @titre varchar (50)
As
Begin
Declare @auteur int
Declare @editeur int
Select @auteur=count (e.num-livre) from livre l, ecrire e where e.num-livre=l.num-livre
and titre=@titre
Select @editeur=count (e.num-editeur) from livre l, editer where l.num-livre=e.num-livre
and titre=@titre
Print ‘nombre auteur est’+ cast (@auteur as varchar (30))
End
26- selectc @depot=count (distinct num-depot) from stocker where num-livre=@num-livre
Print ‘ nombre de projet : ‘ +cast (@depot) as varchar (20))
End
27- create procedure ajout-qt @titre varchar (20), @nom-depot varchar(20), @qt int,
@num-editeur int
As
Begin
Declare @num livre int, @num-depot int
Select @num-Livre=numLivre from livre where titre=@titre
Select @numDepot =numDepot from depot where nomdepot=@nomDepot update stocker
Set qtStock =qtStock+@qt where num-livre=@numLivre and numEditeur=@numEditeur and
numDepot=@numDepot
End
28- Create procedure depotpleine @nomDepot varchar (20)
As
Begin
Declare @numdepot int
Select @numdepot=numdepot from depot where @numdepot
If (select sum (qtStock) from stocker where numdepot=@numdepot>=1555
Print ‘il n’a pas de place pour ajouter des livres’
Else
Print ‘il y’a encore de place pour ajouter des livres’
End
29- create procedure ajoutLivre @numLivre int, @titre varchar(50),@them varchar (56),
@nomDepot varchar(55),@nomEdit varchar (55)
As
Begin
Declare @numDepot int
Declare @numEdit int
Select @numDepot=numdepot from depot where numdepot=@numdepot
Select @numEdit= numEdit from editeur where numEdit =@ numEdit
If (select count (*) from livre where num-livre=@numLivre>0
If (select count(*) from stocker where numLivre=@numLivre and numDepot=@numDepot and
num-edit=@num-edit)>0
Update stocker set qtStock=qtStock+1 where numLivre=@numLivre and numdepot
=@numDepot and numEdit=@numEdit
Else
Insert into stocker values (@numLivre,@numEdit,@numDepot,1)
Else
Begin
Insert into livre values (@numLivre,@titre,@them)
Insert into stocker values (@numLivre, @numEdit, @numDepot,1)
Insert into editer (numEdit,numLivre) values(@numEdit,@numLivre)
End
End
30- create procedure supp-livre @nomLivre varchar (50)
As
begin
Declare @numlv int
Select @numLivre from livre where titre=@nomLivre
Delete from editer where numLivre=@numlv
Delete from ecrire where numLivre=@numlv
Delete from stocké where numLivre=@numlv
Delete from livre where numLivre=@numlv
End
31- create procedure suppAuteur @nom varchar (30)
As
Begin
Declare @numEv int
Select @numEv=numEcrivain from nomecrivain=@nom if (select count(*) from ecrire where
numEcrivain=@num)=0
Delete from ecrivain where numEcrivain=@num
Else
Print ‘vous ne pouvez pas supprimer l’auteur’
End
Exemple :
Liste des membres
ID Nom Age Sexe Tel Adresse Modifier Supprimer

Révision :
• Langage de définition de données
- Creat
- Alter
- Drop
• Langage de manipulation de données
- Select
- Jointures
- Group by – having
- Les requêtes imbriquées (in-all)
Exemple : soit le module suivant :

etudiant classe

Num-et Num-cls
(1,1) (1,n)
Nom-et Insérer Nom-cls
Tel-et
note
Num-et Nom-et Tel-et Note Num-cls
15 Amine 0615234215 3
16 Laila 0614121452 2
17 Samir 0666451214 3

1\ Afficher le N) de classe et la moyenne des notes de chaque classe

Num-cls Nm-cls
1 Classe1
2 Classe2
3 Classe3

Create table ecole

Use ecole
Create table classe
(
Num-cls int,
Nom-cls varchar (20),
Constraint pk-classe primary key (num-cls)
)
Create table etudiant
(
Num-et int,
Nom-et varchar (30),
Tel-et varchar (10),
Note float,
Num-cls int,
Constraint pk-etudiant primary key (num-et),
Constraint fk-etudiant foreign key (num-cls) references classe (num-cls)
)
Insert into classe values (1,’classe1’)
Insert into classe values (2,’classe2’)
Insert into classe values(3,’classe3’)
Insert into etudiant values (15,11)
Num-cls note
3 11.5
2 15

SD :
Select num-cls as ‘’numéro de la classe’’, AVG (note) as ‘’moyen de la classe’’ from etudiant
group by num-cls
2- Afficher le nom-cls et la moyenne
➔ Select nom-cls as ‘’nom de la classe’’, AVG (note) as ‘’moyenne de la classe’’ from c,
etudiant e where c.num-cls=e.num-cls group by nom-cls
3- Afficher le numéro de la classe et la moyenne des notes des classe ayant une
moyenne>=14
4- Select num-cls as ‘’numéro de la classe’’,AVG (note) as ‘’moyenne de la classe’’ from
etudiant group by num-cls having avg (note) >=14
Contrôle 3 :
1- Create table produit
(
Rep-prod int,
Desg varchar (30),
PU float,
Qt6stock float,
Constraint pk-produit primary key (ref-prod)
)
Creat table facture
(
Num-fact int,
Date-fact datetime,
Mode-reg varchar (30),
Constraint pk-facture primary key (num-fact)
)
Create table contenir
(
Ref-prod int,
Num-fact int,
Qt-fact float,

Constraint pk-contenir primary key (ref-prodn num-fact),

Constraint fk1-contenir foreign key (ref-prod) referneces produit (ref-prod)
Constraint fk2-contenir foreign key (num-fact) references facture (num-fact)
Create table fournisseur
(
Id-four int,
Nom-four varchar (20),
Adresse varchar(20),
Tel varchar (10),
Constraint pk-fournisseur primary key (id-four)
)
Create table fournir
(
Id-prod int,
Ref-four int,
Qt-four float,
Constraint pk-fournir primary key (id-four, ref-four)
Constraint fk1-fournir foreign key (id-four) references fournir (id-four)
Constraint fk2-four foreign key (ref-prod) references produit (ref-prod)
)
C- trigger ajout
Create trigger ti-contenir on contenir for insert as
Begin
Declare @id-prod int
Declare @qt-facture float
Declare @qt-stock float
Select @num-prod =ref-prod, @qt-facture=qt-fact from inserted
Select @qt-stock = qt-stock from produit where ref-prod=@num-prod
If @qt-stock>@qt-facture
Begin
Print ‘qt-stock<@qt-facture
Begin
Rollback transaction
End
Else

Begin
Update produit set qt-stock=qtstock_qt-facture where ref-prod=@num-prod
Print ‘operation effectuée’
End
End
• Trigger suppression :
Create trigger td-contenir on contenir for table delete as begin
Declare @num-prod int
Declare @qt-fact float
Select @num-prod int
Declare @at-fact float
Select @num-prod=ref-prod qt-fact=qt-fact from deleted update produit
Set qt-stock=qt-stock+@qt-fact where ref-prod=@num-prod
Print ‘suppression effectuée’
End
• Trigger de modification :
Create trigger tm-trigger on contenir for update as
Begin
Declare @ref1 as int
Declare @ref2 as int
Declare qt-fact1 as float
Declare qt-fact2 as float
Declare qt-stock1 as float
Declare qt-stock as float
Select @ref1=ref-prod, qt-fact=qt-fact from deleted
Select @ref1=ref-prod, qt-fact2=qt-fact from inserted
Select @qt-stock1=qt-stock from produit where ref-prod=@ref1
Select @qt-stock2=qt-stock from produit where ref-prod=@ref2
If @ref1=@ref2
Begin
If @qt-stock1>= qt-fact2-fact1
Begin
Update produit set qt-stock=qt-stock-@qt-f2+@qt-f1 where ref-prod=@ref1
Print ’opération terminer’
End
Else
Begin
Rollback transction
Print ‘quantité insiffisente’
End
End
Else
If @qt-stick>=@q-fact2
Begin
Update produit set qt-stock=qt-stock+qt-fact where ref-prod=@ref2
Update produit set qt_stock=qt-stock+qt-fact1 where ref-prod=@ref1
Print ‘operation terminée’
End
Else
Print’quantité insuffisente ‘
Rollback transaction
End
End