Affichage des articles dont le libellé est sql_tp. Afficher tous les articles
Affichage des articles dont le libellé est sql_tp. Afficher tous les articles
mardi 30 septembre 2014
mercredi 2 avril 2014
TP Système de Gestion de Base de Données (II) Avec Solution
TP Système de Gestion de Base de Données (II) Avec Solution
Exercices :Voici le schéma relationnel de la base AcciRoute pour représentater les rapports
d’accidents de la route. Le S.R de chaque relation est enrichi avec un type de l’attribut, afin de
vous permettre de formuler adéquatement les requétes SQL
Personne (NAS : char(9), nom : varchar(35), VilleP : Varchar(50))
Voiture (Imma : Char(6), modele : varchar(20), annee : char(4), nas : char(9))
Accident (DateAc : Date, NAS : char(9), dommage : numeric(7 :2), villeAc : varchar(50),
imma : char(6) )
Note :
1. Les types des attributs représentent les domaines syntaxiques.
2. Une personne est propriétaire d’une ou plusieurs voitures.
3. Une personne conduit q’une voiture dont elle est propriétaire.
4. Il peut y avoir des homonymes dans la base différentiés par leur NAS respectif.
Questions :
1. Créer la base de données AcciRoute.
2. Créer la procédure CreateAcciRoute qui permet de construire les tables de données
AcciRoute en les supprimant s’ils existent avant leur création.
3. Créer la procédure InsertAccident qui permet d’insérer les données dans Accident en
vérifiant l’intégrité référentielle.
4. Créer la procédure GetnumProp qui permet de calculer le nombre de propriétaires
impliqués dans un accident entre deux années données.
5. Créer la procédure GetProp qui donne le nom et le nas des propriétaires qui ont fait
deux accidents dans un intervalle de 4 mois.
6. Créer la procédure GetDomCity qui calcule le total des dommages d’une ville donnée
et affiche « catégorie1 » pour dommage<=5000 et « catégorie2 » pour dommage entre
5000 et 10000 et « catégorie3 » pour dommage >10000.
7. Créer la procédure GetnumAcci qui permet d’afficher pour chaque ville le nombre
total d’accidents enregistrés.
8. Créer la procédure GetNamProp qui permet d’afficher le nom des propriétaires qui
résident dans une ville où il y a eu plus de x accidents tel que x un paramètre de la
procédure.
9. Créer la procédure GetnumAcciDat qui calcule le nombre d’accidents qui sont
survenus à une date donnée.
10. Créer la procédure GetnumAcciHour qui calcule le nombre d’accidents survenus entre
deux heures données.
11. Créer la procédure UpdateDom qui permet de diminuer de 5% le dommage à chaque
véhicule dont les dommages dépassant les 5000.00.
Solution
<script src="https://gist.github.com/anonymous/9931388.js"></script>
TP-Gestion de Stock SQL
TP-Gestion de Stock SQL
- Clients (Ref_cli,DescriptionCl,Contact,villleCl,solvabiulite,telCl)
- Fournisseurs (Ref_fou,descriptionF,VilleF, TelF)
- Produits (Ref_pro, DescriptionP, Ref_fou, Ref_cat, PrixU, Quantite)
- Categorie (Ref_cat, DescriptionCa)
- Commande (Ref_com, Ref_cli, DateCom, Date_liv)
- DetailCommande (Ref_com,Ref_pro, Qtite)
Questions et reponses :
- Liste des commandes du 1er Trimestre de l’année 1997.
- Select * From Commande Where Datecom Between '01/01/1997' AND '30/04/1997'
- Liste des commandes dont la différence entre la date de commande et la date de livraison est supérieure à 10 jours.
- Select * From Commande Where DateDiff(day, DateCom,Date_liv)>=10
- Liste des commandes en affichant les produits commandés avec leurs prix et quantités respectifs ainsi que la date de commande et le client.
- Select DescriptionP, PrixU, Quantite, DateCom, DescriptionCL
From Produits P INNER JOIN DetailCommande DC ON P.Ref_pro=DC.Ref_pro
INNER JOIN Commande C ON DC.Ref_Com=C.Ref_Com
INNER JOIN Client CL ON C.Ref_Cli=CL.Ref_cli
- Liste des catégories dont la désignation contient la lettre « N ».
- Select * From Categorie Where DescriptionCa LIKE '%n%'
- Lister les fournisseurs qui ne figurent pas dans la table Produit.
- Select * From Fournisseurs Where Ref_fou Not IN (Select Ref_fou From Produits)
- Liste des produits affichant les quantités maximale et minimale commandées par Produit.
- Select Produits.Ref_pro, DescriptionP, Max(Qtite) As "Quantite Max", Min(Qtite) As "Quantite Min"
From Produits INNER JOIN DetailCommande ON Produits.Ref_pro=DetailCommande.Ref_pro
Groupe by Produits.Ref_pro, DescriptionP
- Liste des produits affichant une nouvelle colonne «Montant Total par Produit».
- Select Produits.Ref_pro, DescriptionP, SUM(Qtite*PrixU) As "Montant Total par Produit"
From Produits INNER JOIN DetailCommande ON Produits.Ref_pro=DetailCommande.Ref_pro
Groupe by Produits.Ref_pro, DescriptionP
Questions et reponses :
- Liste des commandes du 1er Trimestre de l’année 1997.
- Select * From Commande Where Datecom Between '01/01/1997' AND '30/04/1997' - Liste des commandes dont la différence entre la date de commande et la date de livraison est supérieure à 10 jours.
- Select * From Commande Where DateDiff(day, DateCom,Date_liv)>=10 - Liste des commandes en affichant les produits commandés avec leurs prix et quantités respectifs ainsi que la date de commande et le client.
- Select DescriptionP, PrixU, Quantite, DateCom, DescriptionCL
From Produits P INNER JOIN DetailCommande DC ON P.Ref_pro=DC.Ref_pro
INNER JOIN Commande C ON DC.Ref_Com=C.Ref_Com
INNER JOIN Client CL ON C.Ref_Cli=CL.Ref_cli - Liste des catégories dont la désignation contient la lettre « N ».
- Select * From Categorie Where DescriptionCa LIKE '%n%' - Lister les fournisseurs qui ne figurent pas dans la table Produit.
- Select * From Fournisseurs Where Ref_fou Not IN (Select Ref_fou From Produits) - Liste des produits affichant les quantités maximale et minimale commandées par Produit.
- Select Produits.Ref_pro, DescriptionP, Max(Qtite) As "Quantite Max", Min(Qtite) As "Quantite Min"
From Produits INNER JOIN DetailCommande ON Produits.Ref_pro=DetailCommande.Ref_pro
Groupe by Produits.Ref_pro, DescriptionP - Liste des produits affichant une nouvelle colonne «Montant Total par Produit».
- Select Produits.Ref_pro, DescriptionP, SUM(Qtite*PrixU) As "Montant Total par Produit"
From Produits INNER JOIN DetailCommande ON Produits.Ref_pro=DetailCommande.Ref_pro
Groupe by Produits.Ref_pro, DescriptionP
mardi 1 avril 2014
DEVOIR DE SQL avec Solution
DEVOIR DE SQL
La société
INFOWARE
a été créée le 1er septembre 2000. Son activité principale est la
conception de logiciels adaptés aux besoins de ses clients. Cette société
connaît un fort développement. Toutefois pour rester concurrentielle, INFOWARE
a mis en place un important plan de formation de son personnel. Afin de mieux
suivre cette politique de formation et de prévoir son financement, INFOWARE a
développé une base de données permettant d’extraire les besoins en formation
par salarié mais aussi par service et par catégorie. Vous
disposez ci-dessous du schéma relationnel de cette base de données et du
contenu des différentes tables.
Voir le devoir ci dessus :
samedi 29 mars 2014
SGBD 2 TP Fonctions Avec Correction
SGBD 2 TP Fonctions Avec Correction
Produit (IDP, LibP, IDM#, PU_V, Qté)
Marque (IDM, Désignation, NBProd)
Fournir (IDF#, IDP#, Date, Qté, PU_A)
Fournisseur (IDF, RS, Ville, Tél)
1) Réaliser une fonction qui retourne le prix moyen des produits d’une marque donnée
2) Réaliser une fonction qui renvoie la quantité moyenne fournie d’un produit pendant une période
donnée
3) Réaliser une fonction qui retourne le libellé le plus long des produits (composé de plus de
caractère)
4) Réaliser une fonction qui renvoie le libellé et le pu de tous les produits classés par PU croissant,
sans utiliser le tri
5) Sachant que l’IDP représente, pour chaque produit, son classement selon un PU croissant,
Réaliser une fonction qui permet de modifier le PU d’un produit donné en retournant le nouveau
classement des produits.
6) Réaliser une fonction qui permet d’afficher le libellé et le PU, des produits d’une famille donnée,
augmentés ou diminués d’un pourcentage passé en paramètre
7) Réaliser une fonction qui retourne le nombre des produits dont le libellé est écrit en majuscules.
8) Listez pour chaque produit, le libellé, la famille et un champ calculé qui aura pour alias ‘CodP’
et qui sera obtenu comme suit : NBcar_Fam (Avec NBCar représente le nombre de caractères du
libellé et Fam représente la désignation correspondante à sa famille)
9) Réaliser une fonction qui affiche pour tous les produits, le libellé, l’écart entre le PU_A moyen et
le PU_V.
Correction :
create database TP1_Fonction
use TP1_Fonction
create table produit(
idp int primary key,
libp varchar(50),
idm int foreign key references marque(idm),
pu_v float,
qté varchar(50)
)
create table marque(
idm int primary key,
designation varchar(200),
nbprod int
)
insert into fournir values(NULL,1,'2010/1/1',1,100)
create table fournir(
idf int foreign key references fournisseur(idf),
idp int foreign key references produit(idp),
date_f date,
qte int,
pu_a float
primary key(idf,idp,date_f)
)
create table fournisseur(
idf int primary key,
rs varchar(50),
ville varchar(50),
tel varchar(50)
)
insert into marque values(123,'adidas',1000)
insert into marque values(124,'nike',2000)
insert into marque values(125,'puma',4000)
insert into produit values(1,'tiger',125,500,'40')
insert into produit values(2,'smith',123,400,'50')
insert into produit values(3,'sheekers',124,600,'40')
insert into produit values(4,'cock',125,300,'10')
insert into fournisseur values(10,'ona','casablanca',0524435010)
insert into fournisseur values(20,'pigi','marrakech',0524312345)
insert into fournisseur values(30,'akim','rabat',0524678976)
insert into fournir values(10,1,'2011/01/01',10,450)
insert into fournir values(20,2,'2011/05/09',20,350)
insert into fournir values(30,3,'2011/04/01',15,550)
--1
create function F1(@marque varchar(255)) returns table
As
return (select AVG(p.pu_v * p.qté) as [La moyenne des produit de marque ] from produit p, marque m where p.idm = m.idm and m.designation = @marque)
select * from dbo.F1('puma')
--2
create function F2(@idp int , @d1 date, @d2 date) returns table
As
return (select avg(qte) as 'Moyenne de quantité' from fournir where date_f between @d1 and @d2 and idp = @idp)
select * from dbo.F2(1,'2001/2/10','2012/1/1')
--3
alter function F3() returns @t table (Lib varchar(50))
As
Begin
Declare t cursor for (select libp from produit)
declare @l varchar(50), @max varchar(50), @n int
open t
fetch next from t into @l
select @max=@l, @n = len(@l)
while(@@FETCH_STATUS = 0)
Begin
if len(@l) > @n
begin
set @max = @l
set @n = len (@l)
end
fetch next from t into @l
end
insert into @t values(@max)
close t
deallocate t
return
End
select * from dbo.F3()
create function F4() returns @t table (libp varchar(255),pu float)
As
Begin
Declare C1 cursor for (select libp, pu_v from produit)
Declare @l varchar(55), @p float, @intr float,@l1 varchar(55), @p1 float, @tempP float, @tempLib varchar(50)
open C1
fetch next from C1 into @l,@p
while(@@Fetch_status = 0)
Begin
Declare C2 cursor for (select libp, pu_v from produit)
Open C2
fetch last from C2 into @l1,@p1
declare @pp float
set @pp = p1
while(@@Fetch_status = 0)
Begin
fetch last from C2 into @l1,@p1
if(@p1 > @pp)
Begin
--set @tempP = :x
print 'Erreur'
end
end
fetch next from C1 into @l,@p
end
return
end
----------------------------------------
--M1
create function F4() returns @t table (idp int, lib varchar(255), prix float)
As
Begin
declare @i1 int, @l1 varchar(255), @p1 float
declare c1 cursor for (select idp, libp,pu_v from produit)
open c1
fetch next from c1 into @i1,@l1,@p1
while @@FETCH_STATUS = 0
Begin
insert into @t select idp,libp,pu_v from produit where pu_v = (select MIN(pu_v) from produit where idp not in(select idp from @t))
fetch next from c1 into @i1,@l1,@p1
end
close c1
deallocate c1
return
end
select * from dbo.F4()
--M1
create function F4_2() returns @t table (idp int, lib varchar(255), prix float)
As
Begin
declare @i int
set @i = 0
while @i < (select count(*) from produit)
Begin
insert into @t select idp,libp,pu_v from produit where pu_v = (select MIN(pu_v) from produit where idp not in(select idp from @t))
set @i = @i + 1
end
return
end
select * from dbo.F4_2()
/*
Réaliser une fonction qui permet d’afficher le libellé et le PU, des produits d’une famille donnée,
augmentés ou diminués d’un pourcentage passé en paramètre
*/
alter function F5(@marque varchar(max), @p float) returns @t table (libp varchar(max), prix float)
As
Begin
insert into @t select libp, pu_v from produit p, marque m where p.idm = m.idm and m.designation = @marque
create proc p1
As
Begin
update produit set pu_v = 0
-- Le problème de Update :D
end
exec p1
return
end
select * from dbo.F5('puma', 10)
--------------------------------------------------------------------------
create function F7() returns int
As
Begin
declare t cursor for (select libp from produit)
declare @l varchar(255), @n int,@c int, @i int
set @n = 0
open t
fetch next from t into @l
while(@@fetch_status = 0)
Begin
-------------------
select @c=1, @i=1
while(@i < LEN(@l))
Begin
if(ascii(substring(@l,@i,1)) < 65 or ascii(substring(@l,@i,1)) > 90)
set @c = 0
set @i = @i + 1
end
if @c = 1
set @n = @n + 1
-------------------
fetch next from t into @l
end
close t
deallocate t
return @n
end
select dbo.F7()
create function F8() returns @t table(Libp varchar(255), fmlp varchar(255), CodP varchar(255))
As
Begin
declare c1 cursor for (select p.libp,m.designation from produit p , marque m where p.idm = m.idm)
declare @lb varchar(255), @mr varchar(255)
open c1
fetch next from c1 into @lb, @mr
while(@@fetch_status = 0)
begin
insert into @t values (@lb, @mr,len(@lb))
fetch next from c1 into @lb, @mr
end
return
end
select * from dbo.F8()
create function dbo.F9() returns @t table (libp varchar(255), ecart varchar(255))
As
Begin
insert into @t select p.libp, p.pu_v - f.pu_a as 'l’écart' from produit p , fournir f where p.idp = f.idp
return
end
select * from dbo.F9()
SGBD 2 Série N° 3 Avec Correction
ISTA
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 3
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
EMPLOYE (Matricule, nom, prenom, echelle)
SERVICE (Numero, Nom, Adresse)
PROJET (code, Matricule, Numero, DateDebut, NbreJour, Comission) code incrémenté
automatiquement.
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe
déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau
service sera inséré, s’il existe, le service trouvé sera modifié).
4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero
de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi
retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à
la fois.
5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de
réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à
un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet
d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
Exercice 2: Soit le modèle relationnel suivant :
TECHNICIEN (code, nom, agence, prix_heure)
ORDINATEUR (réf, marque, processeur, mémoire, disque)
REPARER (numéro, codeTec, réfOrdi, date, nbreHeure) numéro incrémenté
automatiquement
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un technicien après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un ordinateur après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
4) Créer une procédure qui permet d’enregistrer une réparation en vérifiant si la réf de
l’ordinateur et le code du technicien existent déjà (valeur retournée 0) ou non (valeur
retournée -1), on désire aussi retourner le numéro affecté à la réparation.
5) Créer une procédure qui supprime une réparation après avoir vérifié qu’elle n’est pas en
cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un ordinateur après avoir vérifié qu’il n’est pas affecté
à une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un technicien après avoir vérifié qu’il n’est affecté à
une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
8) Créer une procédure qui permet de modifier le prix de maintenance d’un technicien (dont
le code est passé en paramètre) par heure selon la valeur de ce dernier :
a. <100 il sera diminué de 10%
b. Entre 100 et 150 il restera inchangé.
c. >150 il sera augmenté de 2%
On désire récupérer la nouvelle valeur du prix dans un paramètre de sortie.
create database TP_11_EX2
USE TP_11_EX2
-- 1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
create table EMPLOYE (
Matricule int primary key,
nom varchar(50),
prenom varchar(50),
echelle int
)
create table SERVICEE (
Numero int primary key,
Nom varchar(50),
Adresse varchar(100)
)
create table PROJET (
code int identity (1,1),
Matricule int foreign key references employe(matricule) on delete cascade on update cascade,
Numero int foreign key references servicee(numero) on delete cascade on update cascade,
DateDebut datetime ,
NbreJour int,
Comission float
)
-- 2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
CREATE PROCEDURE pro_employee (
@matricule INT OUTPUT,
@Nom VARCHAR(30),
@prenom varchar(30),
@echelle VARCHAR(30)
)
AS
begin
if not exists (select * from employe where @matricule=matricule)
begin
INSERT INTO Employe (matricule,nom,prenom,echelle)
VALUES (@matricule,@nom,@prenom,@echelle)
RETURN 0
end
return -1
end
-- 3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau service sera inséré, s’il existe, le service trouvé sera modifié).
CREATE PROCEDURE insert_service (
@numero INT ,
@Nom VARCHAR(30),
@adresse varchar(30)
)
AS
declare @etat int
declare @con int
set @etat=0
select @con=count (*)from servicee where numero= @numero
if @con=0
begin
INSERT INTO servicee values (@numero,@nom,@adresse)
end
else
begin
update servicee set nom=@nom,adresse=@adresse where @numero=numero
end
-- 4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à la fois.
CREATE PROCEDURE proc_projet (
@code INT OUTPUT,
@matricule INT,
@numero VARCHAR(30),
@DateDebut datetime ,
@NbreJour int,
@Comission float
)
AS
if exists (select * from employe where matricule= @matricule )
and exists (select * from servvicee where numero= @numero )
and not exists (select * from projet where @datedebut between datedebut and dateadd(day,nbrejour,datedebut) and matricule=@matricule)
begin
INSERT INTO projet (matricule,numero,DateDebut,NbreJour,Comission)
VALUES (@matricule,@numero,@DateDebut,@NbreJour,@Comission)
SELECT @code=SCOPE_IDENTITY()
RETURN 0
end
return -1
-- 5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_projet(
@code INT
)
AS
if not exists (select * from projet where @code=code and getdate() not between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from projet where code=@code
RETURN 0
end
return -1
-- 6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE suprimmer_employe(
@matricule INT
)
AS
if not exists (select matricule from projet where getdate() between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from employe where matricule=@matricule
RETURN 0
end
return -1
end catch
-- 7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_service(
@numero INT
)
AS
begin
begin try
if exists (select * from projet where @numero=numero)
delete from employe where @matricule=matricule
RETURN 0
end try
begin catch
return -1
end catch
end
------------------------------------------------------------------------------------------------------
create database EX2_ser3
use EX2_ser3
create table TECHNICIEN (
code int primary key ,
nom varchar(50),
agence varchar(50),
prix_heure money )
create table ORDINATEUR (
réf int primary key,
marque varchar(50),
processeur varchar(50),
mémoire varchar(50),
disque varchar(50))
create table REPARER (
numéro int primary key identity,
codeTec int foreign key references TECHNICIEN(code)on delete cascade on update cascade ,
réfOrdi int foreign key references ORDINATEUR (réf) on delete cascade on update cascade,
datee datetime ,
nbreHeure int )
--2
create procedure insert_tec (@code int , @nom varchar , @Age varchar , @prix_h varchar)
as
if not exists (select * from TECHNICIEN where code = @code)
begin
insert into TECHNICIEN values (@code,@nom,@Age,@prix_h)
return 0
end
return -1
--3
create procedure insert_Ord (@ref int , @Marque varchar , @processeur varchar , @mémoire varchar ,@disque varchar)
as
if not exists (select * from ORDINATEUR where réf = @ref)
begin
insert into ORDINATEUR values (@ref,@Marque,@processeur,@mémoire ,@disque)
return 0
end
return -1
--4
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 3
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
EMPLOYE (Matricule, nom, prenom, echelle)
SERVICE (Numero, Nom, Adresse)
PROJET (code, Matricule, Numero, DateDebut, NbreJour, Comission) code incrémenté
automatiquement.
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe
déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau
service sera inséré, s’il existe, le service trouvé sera modifié).
4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero
de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi
retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à
la fois.
5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de
réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à
un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet
d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
Exercice 2: Soit le modèle relationnel suivant :
TECHNICIEN (code, nom, agence, prix_heure)
ORDINATEUR (réf, marque, processeur, mémoire, disque)
REPARER (numéro, codeTec, réfOrdi, date, nbreHeure) numéro incrémenté
automatiquement
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un technicien après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un ordinateur après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
4) Créer une procédure qui permet d’enregistrer une réparation en vérifiant si la réf de
l’ordinateur et le code du technicien existent déjà (valeur retournée 0) ou non (valeur
retournée -1), on désire aussi retourner le numéro affecté à la réparation.
5) Créer une procédure qui supprime une réparation après avoir vérifié qu’elle n’est pas en
cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un ordinateur après avoir vérifié qu’il n’est pas affecté
à une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un technicien après avoir vérifié qu’il n’est affecté à
une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
8) Créer une procédure qui permet de modifier le prix de maintenance d’un technicien (dont
le code est passé en paramètre) par heure selon la valeur de ce dernier :
a. <100 il sera diminué de 10%
b. Entre 100 et 150 il restera inchangé.
c. >150 il sera augmenté de 2%
On désire récupérer la nouvelle valeur du prix dans un paramètre de sortie.
create database TP_11_EX2
USE TP_11_EX2
-- 1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
create table EMPLOYE (
Matricule int primary key,
nom varchar(50),
prenom varchar(50),
echelle int
)
create table SERVICEE (
Numero int primary key,
Nom varchar(50),
Adresse varchar(100)
)
create table PROJET (
code int identity (1,1),
Matricule int foreign key references employe(matricule) on delete cascade on update cascade,
Numero int foreign key references servicee(numero) on delete cascade on update cascade,
DateDebut datetime ,
NbreJour int,
Comission float
)
-- 2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
CREATE PROCEDURE pro_employee (
@matricule INT OUTPUT,
@Nom VARCHAR(30),
@prenom varchar(30),
@echelle VARCHAR(30)
)
AS
begin
if not exists (select * from employe where @matricule=matricule)
begin
INSERT INTO Employe (matricule,nom,prenom,echelle)
VALUES (@matricule,@nom,@prenom,@echelle)
RETURN 0
end
return -1
end
-- 3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau service sera inséré, s’il existe, le service trouvé sera modifié).
CREATE PROCEDURE insert_service (
@numero INT ,
@Nom VARCHAR(30),
@adresse varchar(30)
)
AS
declare @etat int
declare @con int
set @etat=0
select @con=count (*)from servicee where numero= @numero
if @con=0
begin
INSERT INTO servicee values (@numero,@nom,@adresse)
end
else
begin
update servicee set nom=@nom,adresse=@adresse where @numero=numero
end
-- 4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à la fois.
CREATE PROCEDURE proc_projet (
@code INT OUTPUT,
@matricule INT,
@numero VARCHAR(30),
@DateDebut datetime ,
@NbreJour int,
@Comission float
)
AS
if exists (select * from employe where matricule= @matricule )
and exists (select * from servvicee where numero= @numero )
and not exists (select * from projet where @datedebut between datedebut and dateadd(day,nbrejour,datedebut) and matricule=@matricule)
begin
INSERT INTO projet (matricule,numero,DateDebut,NbreJour,Comission)
VALUES (@matricule,@numero,@DateDebut,@NbreJour,@Comission)
SELECT @code=SCOPE_IDENTITY()
RETURN 0
end
return -1
-- 5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_projet(
@code INT
)
AS
if not exists (select * from projet where @code=code and getdate() not between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from projet where code=@code
RETURN 0
end
return -1
-- 6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE suprimmer_employe(
@matricule INT
)
AS
if not exists (select matricule from projet where getdate() between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from employe where matricule=@matricule
RETURN 0
end
return -1
end catch
-- 7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_service(
@numero INT
)
AS
begin
begin try
if exists (select * from projet where @numero=numero)
delete from employe where @matricule=matricule
RETURN 0
end try
begin catch
return -1
end catch
end
------------------------------------------------------------------------------------------------------
create database EX2_ser3
use EX2_ser3
create table TECHNICIEN (
code int primary key ,
nom varchar(50),
agence varchar(50),
prix_heure money )
create table ORDINATEUR (
réf int primary key,
marque varchar(50),
processeur varchar(50),
mémoire varchar(50),
disque varchar(50))
create table REPARER (
numéro int primary key identity,
codeTec int foreign key references TECHNICIEN(code)on delete cascade on update cascade ,
réfOrdi int foreign key references ORDINATEUR (réf) on delete cascade on update cascade,
datee datetime ,
nbreHeure int )
--2
create procedure insert_tec (@code int , @nom varchar , @Age varchar , @prix_h varchar)
as
if not exists (select * from TECHNICIEN where code = @code)
begin
insert into TECHNICIEN values (@code,@nom,@Age,@prix_h)
return 0
end
return -1
--3
create procedure insert_Ord (@ref int , @Marque varchar , @processeur varchar , @mémoire varchar ,@disque varchar)
as
if not exists (select * from ORDINATEUR where réf = @ref)
begin
insert into ORDINATEUR values (@ref,@Marque,@processeur,@mémoire ,@disque)
return 0
end
return -1
--4
SGBD 2 Série N° 2 Avec Correction
ISTA
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 2
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresse, cp, ville)
PRODUIT (référence, désignation, prix)
TECHNICIEN (codetec, nomtec, prenomtec, tauxhoraire)
INTERVENTION (numero, date, raison, codeclt, référence, codetec)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui prend une ville en paramètre et retourne le nombre de clients qui
habitent cette ville.
3) Créer une fonction qui retourne le nombre d’interventions effectuées par le technicien
dont le nom est passé en paramètre.
4) Créer une fonction qui retourne la somme des prix des produits qui ont subi une
intervention entre deux dates passées en paramètre et qui sont à la propriété du client
auquel le nom est passé en paramètre.
5) Créer une fonction qui retourne la liste des interventions (numéro, date, nomclt,
désignation) du technicien auquel le nom est passé en paramètre.
6) Créer une fonction qui retourne la liste des clients qui n’ont pas demandé d’interventions
entre deux dates passées en paramètre.
Exercice 2: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresseclt, CPclt, villeclt)
APPARTEMENT (ref, superficie, pxvente, secteur, coderep, codeclt)
REPRESENTANT (coderep, nomrep, prenomrep)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui retourne la liste des clients (nom, prénom) qui habitent la ville
passée en paramètre.
3) Créer une fonction qui retourne le nombre d’appartements vendus par le représentant
passé en paramètre (coderep).
4) Créer un curseur Cur_Apprt qui permet de parcourir tous les appartements (ref, pxvente,
secteur) et qui permet de modifier le pxvente seulement.
5) Créer une fonction qui retourne le nouveau prix de vente selon l’ancienne valeur qui est
passée en paramètre :
a. Le prix est augmenté de 10% s’il est inferieur à 250000
b. Le prix est augmenté de 7% s’il est entre 250000 et 380000
c. Le prix est augmenté de 5% s’il est supérieur à 380000
6) Modifier les prix (pxvente) des appartements en utilisant la fonction définie dans la question 5)
et le curseur déclaré à la question 4).
Exercice 3: Soit le modèle relationnel suivant :
STAGIAIRE (code, nom, groupe, dateNaissance)
MODULE (code, nom, Coef)
EVALUER (codeStg, codeMod, Date, note)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu d’enregistrements.
2) Créer une fonction qui retourne la liste des modules dans lesquels le stagiaire (nom) passé
en paramètre a passé des évaluations.
3) Créer une fonction qui retourne les dates et les notes obtenues pour chaque date dans le
module (code) passé en paramètre par le stagiaire (code) passé en paramètre.
4) Créer une fonction qui retourne la moyenne obtenue par le stagiaire (code) passé en
paramètre dans le module (code) passé en paramètre (utiliser la fonction définie dans 3).
5) Créer une fonction qui retourne la moyenne générale obtenue par le stagiaire (code) passé
en paramètre (Indications : utiliser un curseur qui parcourt les modules et la fonction
définie dans 4).
6) Créer une fonction qui retourne la mention obtenue selon la moyenne passée en
paramètre :
a. Ajourné : si la moyenne est inférieure à 10
b. Passable : si la moyenne appartient à [10, 12[
c. A. Bien : si la moyenne appartient à [12, 14[
d. Bien : si la moyenne appartient à [14, 16[
e. T. Bien : si la moyenne appartient à [16, 20]
7) Créer un curseur qui permet de parcourir les stagiaires (Code, Nom, Groupe) et d’afficher
pour chaque stagiaire la moyenne générale obtenue et la décision correspondante.
create database ex1_Serie2
use ex1_serie2
create table CLIENT (
codeclt int primary key ,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int,
ville varchar(50)
)
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
create table PRODUIT (
référence int primary key ,
désignation varchar(50),
prix money )
insert into PRODUIT values (1,'Produit1',20000)
insert into PRODUIT values (2,'Produit2',30000)
insert into PRODUIT values (3,'Produit3',40000)
create table TECHNICIEN (
codetec int primary key ,
nomtec varchar(50),
prenomtec varchar(50),
tauxhoraire float
)
insert into TECHNICIEN values (1,'SALHI','Samir',10)
insert into TECHNICIEN values (2,'alaoui','khalid',8)
insert into TECHNICIEN values (3,'nassiri','Fouad',9)
create table INTERVENTION (
numero int primary key ,
dateI datetime ,
raison varchar(50),
codeclt int foreign key references CLIENT(codeclt),
référence int foreign key references PRODUIT(référence) ,
codetec int foreign key references TECHNICIEN(codetec))
insert into INTERVENTION values (1,'20/08/2011','raison1',1,1,1)
insert into INTERVENTION values (2,'13/09/2011','raison2',2,2,2)
insert into INTERVENTION values (3,'27/08/2011','raison3',3,3,3)
--2
create function NbrClientParVille( @Ville varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(codeclt) from CLIENT where Ville = @Ville
return @NB
end
select ville ,dbo.NbrClientParVille(ville) as [nbr client]from CLIENT group by ville
--3
create function Nbrinervention(@Nomtec varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(INTERVENTION.codetec) from TECHNICIEN inner join INTERVENTION on TECHNICIEN.codetec = TECHNICIEN.codetec where TECHNICIEN.nomtec = @Nomtec
return @NB
end
select codetec , nomtec ,dbo.Nbrinervention('SALHI') from TECHNICIEN
--4
create function Sommeprix (@Nomclt varchar(50),@Date1 datetime , @Date2 datetime)
returns float
as
begin
declare @Somme float
select @Somme = Sum (prix) from PRODUIT P inner join INTERVENTION I on I.référence = P.référence inner join CLIENT C on C.codeclt = I.codeclt where C.nomclt = @Nomclt and I.dateI between @Date1 and @Date2
return @Somme
end
--5
create function Lite_inter(@Nomtec varchar(50))
returns table
as
return (select INTERVENTION.numero, INTERVENTION.dateI, CLIENT.nomclt, PRODUIT.désignation from TECHNICIEN t inner join INTERVENTION I on I.codeTEC= t.codeTEC inner join PRODUIT P on P.référence = I.référence where t.nomtec = @Nomtec)
--6
create function ListeClt (@date1 datetime,@date2 datetime)
returns table
as
return (select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between @date1 and @date2)
select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between '12/09/2011' and '14/09/2011'
------------------------------------------------------------------------------------------------
create database Ex2
use Ex2
create table CLIENT (
codeclt int primary key,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int ,
ville varchar(50))
create table APPARTEMENT (
ref int primary key ,
superficie int ,
pxvente money,
secteur varchar(50),
coderep int foreign key references REPRESENTANT(coderep),
codeclt int foreign key references CLIENT(codeclt),
)
create table REPRESENTANT (
coderep int primary key,
nomrep varchar(50),
prenomrep varchar(50))
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
--3
declare cur_app cursor
global
scroll
dynamic
for
select
--------------------------------------------------------------------------------------------
create database ex3
use ex3
create table STAGIAIRE (
code int primary key,
nom varchar(50),
groupe varchar(50),
dateNaissance datetime)
create table MODULE (
code int primary key,
nom varchar(50),
Coef int )
create table EVALUER (
codeStg int foreign key references STAGIAIRE (code),
codeMod int foreign key references MODULE(code),
DateE datetime,
note float
primary key (codeStg,codeMod,DateE)
)
insert into STAGIAIRE values (1,'widad','GA','20/05/1990')
insert into STAGIAIRE values (2,'ali','GB','30/06/1990')
insert into STAGIAIRE values (3,'Khalid','GA','09/12/1991')
insert into MODULE values (1,'MOD1',2)
insert into MODULE values (2,'MOD2',1)
insert into MODULE values (3,'MOD3',7)
insert into EVALUER values (1,1,'13/06/2011',15)
insert into EVALUER values (2,1,'16/06/2011',14.06)
insert into EVALUER values (3,2,'16/06/2011',12)
insert into EVALUER values (1,3,'15/11/2011',17)
--2
create function liste_mod (@nom varchar(50))
returns table
as
return (select M.* from MODULE M inner join EVALUER E ON M.code = E.codeMod inner join STAGIAIRE S on S.code = E.codeStg where S.nom = @nom)
--3
create function liste_moy(@codeS int , @codeM int)
returns table
as
return(select DateE,note from EVALUER where codestg =@codeS and codeMod = @codeM)
--4
create function Moyenne(@codeM varchar(50), @codeS varchar(50))
returns float
as
begin
declare @M float
select @M = AVG(note) from liste_moy(@codeS,@codeM)
return @M
end
---5
create function moygen (@codeS int)
returns float as
begin
declare cur_module cursor for select code,dbo.Moyenne(@codeS,codeM) ,coef from MODULE
declare @moy float
---6
declare @code int , @nom varchar(50),@groupe varchar(50),@
declare cur cursor for select code,nom,groupe from STAGIAIRE
open cur
fetch next from cur into @code,@nom,@groupe
while @@fetch_status = 0
begin
print 'Code :'+ convert(varchar,@code)+'NOM :'+@nom +'groupe :'+@groupe
fetch next from cur into @code,@nom,@groupe
end
close cur
deallocate cur
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 2
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresse, cp, ville)
PRODUIT (référence, désignation, prix)
TECHNICIEN (codetec, nomtec, prenomtec, tauxhoraire)
INTERVENTION (numero, date, raison, codeclt, référence, codetec)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui prend une ville en paramètre et retourne le nombre de clients qui
habitent cette ville.
3) Créer une fonction qui retourne le nombre d’interventions effectuées par le technicien
dont le nom est passé en paramètre.
4) Créer une fonction qui retourne la somme des prix des produits qui ont subi une
intervention entre deux dates passées en paramètre et qui sont à la propriété du client
auquel le nom est passé en paramètre.
5) Créer une fonction qui retourne la liste des interventions (numéro, date, nomclt,
désignation) du technicien auquel le nom est passé en paramètre.
6) Créer une fonction qui retourne la liste des clients qui n’ont pas demandé d’interventions
entre deux dates passées en paramètre.
Exercice 2: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresseclt, CPclt, villeclt)
APPARTEMENT (ref, superficie, pxvente, secteur, coderep, codeclt)
REPRESENTANT (coderep, nomrep, prenomrep)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui retourne la liste des clients (nom, prénom) qui habitent la ville
passée en paramètre.
3) Créer une fonction qui retourne le nombre d’appartements vendus par le représentant
passé en paramètre (coderep).
4) Créer un curseur Cur_Apprt qui permet de parcourir tous les appartements (ref, pxvente,
secteur) et qui permet de modifier le pxvente seulement.
5) Créer une fonction qui retourne le nouveau prix de vente selon l’ancienne valeur qui est
passée en paramètre :
a. Le prix est augmenté de 10% s’il est inferieur à 250000
b. Le prix est augmenté de 7% s’il est entre 250000 et 380000
c. Le prix est augmenté de 5% s’il est supérieur à 380000
6) Modifier les prix (pxvente) des appartements en utilisant la fonction définie dans la question 5)
et le curseur déclaré à la question 4).
Exercice 3: Soit le modèle relationnel suivant :
STAGIAIRE (code, nom, groupe, dateNaissance)
MODULE (code, nom, Coef)
EVALUER (codeStg, codeMod, Date, note)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu d’enregistrements.
2) Créer une fonction qui retourne la liste des modules dans lesquels le stagiaire (nom) passé
en paramètre a passé des évaluations.
3) Créer une fonction qui retourne les dates et les notes obtenues pour chaque date dans le
module (code) passé en paramètre par le stagiaire (code) passé en paramètre.
4) Créer une fonction qui retourne la moyenne obtenue par le stagiaire (code) passé en
paramètre dans le module (code) passé en paramètre (utiliser la fonction définie dans 3).
5) Créer une fonction qui retourne la moyenne générale obtenue par le stagiaire (code) passé
en paramètre (Indications : utiliser un curseur qui parcourt les modules et la fonction
définie dans 4).
6) Créer une fonction qui retourne la mention obtenue selon la moyenne passée en
paramètre :
a. Ajourné : si la moyenne est inférieure à 10
b. Passable : si la moyenne appartient à [10, 12[
c. A. Bien : si la moyenne appartient à [12, 14[
d. Bien : si la moyenne appartient à [14, 16[
e. T. Bien : si la moyenne appartient à [16, 20]
7) Créer un curseur qui permet de parcourir les stagiaires (Code, Nom, Groupe) et d’afficher
pour chaque stagiaire la moyenne générale obtenue et la décision correspondante.
create database ex1_Serie2
use ex1_serie2
create table CLIENT (
codeclt int primary key ,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int,
ville varchar(50)
)
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
create table PRODUIT (
référence int primary key ,
désignation varchar(50),
prix money )
insert into PRODUIT values (1,'Produit1',20000)
insert into PRODUIT values (2,'Produit2',30000)
insert into PRODUIT values (3,'Produit3',40000)
create table TECHNICIEN (
codetec int primary key ,
nomtec varchar(50),
prenomtec varchar(50),
tauxhoraire float
)
insert into TECHNICIEN values (1,'SALHI','Samir',10)
insert into TECHNICIEN values (2,'alaoui','khalid',8)
insert into TECHNICIEN values (3,'nassiri','Fouad',9)
create table INTERVENTION (
numero int primary key ,
dateI datetime ,
raison varchar(50),
codeclt int foreign key references CLIENT(codeclt),
référence int foreign key references PRODUIT(référence) ,
codetec int foreign key references TECHNICIEN(codetec))
insert into INTERVENTION values (1,'20/08/2011','raison1',1,1,1)
insert into INTERVENTION values (2,'13/09/2011','raison2',2,2,2)
insert into INTERVENTION values (3,'27/08/2011','raison3',3,3,3)
--2
create function NbrClientParVille( @Ville varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(codeclt) from CLIENT where Ville = @Ville
return @NB
end
select ville ,dbo.NbrClientParVille(ville) as [nbr client]from CLIENT group by ville
--3
create function Nbrinervention(@Nomtec varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(INTERVENTION.codetec) from TECHNICIEN inner join INTERVENTION on TECHNICIEN.codetec = TECHNICIEN.codetec where TECHNICIEN.nomtec = @Nomtec
return @NB
end
select codetec , nomtec ,dbo.Nbrinervention('SALHI') from TECHNICIEN
--4
create function Sommeprix (@Nomclt varchar(50),@Date1 datetime , @Date2 datetime)
returns float
as
begin
declare @Somme float
select @Somme = Sum (prix) from PRODUIT P inner join INTERVENTION I on I.référence = P.référence inner join CLIENT C on C.codeclt = I.codeclt where C.nomclt = @Nomclt and I.dateI between @Date1 and @Date2
return @Somme
end
--5
create function Lite_inter(@Nomtec varchar(50))
returns table
as
return (select INTERVENTION.numero, INTERVENTION.dateI, CLIENT.nomclt, PRODUIT.désignation from TECHNICIEN t inner join INTERVENTION I on I.codeTEC= t.codeTEC inner join PRODUIT P on P.référence = I.référence where t.nomtec = @Nomtec)
--6
create function ListeClt (@date1 datetime,@date2 datetime)
returns table
as
return (select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between @date1 and @date2)
select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between '12/09/2011' and '14/09/2011'
------------------------------------------------------------------------------------------------
create database Ex2
use Ex2
create table CLIENT (
codeclt int primary key,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int ,
ville varchar(50))
create table APPARTEMENT (
ref int primary key ,
superficie int ,
pxvente money,
secteur varchar(50),
coderep int foreign key references REPRESENTANT(coderep),
codeclt int foreign key references CLIENT(codeclt),
)
create table REPRESENTANT (
coderep int primary key,
nomrep varchar(50),
prenomrep varchar(50))
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
--3
declare cur_app cursor
global
scroll
dynamic
for
select
--------------------------------------------------------------------------------------------
create database ex3
use ex3
create table STAGIAIRE (
code int primary key,
nom varchar(50),
groupe varchar(50),
dateNaissance datetime)
create table MODULE (
code int primary key,
nom varchar(50),
Coef int )
create table EVALUER (
codeStg int foreign key references STAGIAIRE (code),
codeMod int foreign key references MODULE(code),
DateE datetime,
note float
primary key (codeStg,codeMod,DateE)
)
insert into STAGIAIRE values (1,'widad','GA','20/05/1990')
insert into STAGIAIRE values (2,'ali','GB','30/06/1990')
insert into STAGIAIRE values (3,'Khalid','GA','09/12/1991')
insert into MODULE values (1,'MOD1',2)
insert into MODULE values (2,'MOD2',1)
insert into MODULE values (3,'MOD3',7)
insert into EVALUER values (1,1,'13/06/2011',15)
insert into EVALUER values (2,1,'16/06/2011',14.06)
insert into EVALUER values (3,2,'16/06/2011',12)
insert into EVALUER values (1,3,'15/11/2011',17)
--2
create function liste_mod (@nom varchar(50))
returns table
as
return (select M.* from MODULE M inner join EVALUER E ON M.code = E.codeMod inner join STAGIAIRE S on S.code = E.codeStg where S.nom = @nom)
--3
create function liste_moy(@codeS int , @codeM int)
returns table
as
return(select DateE,note from EVALUER where codestg =@codeS and codeMod = @codeM)
--4
create function Moyenne(@codeM varchar(50), @codeS varchar(50))
returns float
as
begin
declare @M float
select @M = AVG(note) from liste_moy(@codeS,@codeM)
return @M
end
---5
create function moygen (@codeS int)
returns float as
begin
declare cur_module cursor for select code,dbo.Moyenne(@codeS,codeM) ,coef from MODULE
declare @moy float
---6
declare @code int , @nom varchar(50),@groupe varchar(50),@
declare cur cursor for select code,nom,groupe from STAGIAIRE
open cur
fetch next from cur into @code,@nom,@groupe
while @@fetch_status = 0
begin
print 'Code :'+ convert(varchar,@code)+'NOM :'+@nom +'groupe :'+@groupe
fetch next from cur into @code,@nom,@groupe
end
close cur
deallocate cur
SQL Exercices Procédures stockées
Exercices Procédures stockées
1--Ecrire un programme qui calcule le montant d’une commande et affiche un
message 'Commande Normale' ou 'Commande Spéciale' selon que le montant est
inférieur ou supérieur à 100000 DH
Declare @Montant decimal
Set @Montant=(Select Sum(PUArt*QteCommandee) from Commande C, Article
A, LigneCommande LC where C.NumCom=LC.NumCom and
LC.NumArt=A.NumArt and C.NumCom=10)
If @Montant is null
Begin
Print 'Cette Commande n''existe pas ou elle n''a pas d''ingrédients'
Return
End
if @Montant <=10000
Print 'Commande Normale'
Else
Print 'Commande Spéciale
2--Ecrire un programme qui supprime l'article numéro 8 de la commande numéro 5
et met à jour le stock. Si après la suppression de cet article, la commande numéro 5
n'a plus d'articles associés, la supprimer.
Declare @Qte decimal
Set @Qte=(select QteCommandee from LigneCommande where NumCom=5 and
NumArt=8)
Delete from LigneCommande where NumCom=5 and NumArt=8
Update article set QteEnStock=QteEnStock+@Qte where NumArt=8
if not exists (select numcom from LigneCommande where NumCom=5)
Delete from commande where NumCom=5
3. Ecrire un programme qui affiche la liste des commandes et indique pour chaque
commande dans une colonne Type s'il s'agit d'une commande normale
(montant <=100000 DH) ou d'une commande spéciale (montant >= 100000 DH)
Select C.NumCom, DatCom, Sum(PUArt*QteCommandee), 'Type'=
Case
When Sum(PUArt*QteCommandee) <=10000 then 'Commande Normale'
Else 'Commande Spéciale'
End
From Commande C, Article A, LigneCommande LC
Where C.NumCom=LC.NumCom and LC.NumArt=A.NumArt
Group by C.NumCom, DatCom
4. A supposer que toutes les commandes ont des montants différents, écrire un
programme qui stocke dans une nouvelle table temporaire les 5 meilleures
commandes (ayant le montant le plus élevé) classées par montant décroissant (la
table à créer aura la structure suivante : NumCom, DatCom, MontantCom)
Create Table T1 (NumCom int, DatCom DateTime, MontantCom decimal)
Insert into T1 Select Top 5 C.NumCom, DatCom, Sum(PUArt*QteCommandee) as
Mt From Commande C, Article A, LigneCommande LC
Where C.NumCom=LC.NumCom and LC.NumArt=A.NumArt
Group by C.NumCom, DatCom
Order by Mt Desc
5---Ecrire un programme qui :
Recherche le numéro de commande le plus élevé dans la table commande
et l'incrémente de 1
Enregistre une commande avec ce numéro
Pour chaque article dont la quantité en stock est inférieure ou égale au seuil
minimum enregistre une ligne de commande avec le numéro calculé et une
quantité commandée égale au triple du seuil minimum
if exists(select NumArt from article where QteEnStock<=SeuilMinimum)
Begin
Declare @a int
set @a=(select max(NumCom) from commande) + 1
insert into commande values(@a, getdate())
insert into lignecommande Select @a, NumArt, SeuilMinimum * 3
From article Where QteEnStock <=SeuilMinimum
End
1--Ecrire un programme qui calcule le montant d’une commande et affiche un
message 'Commande Normale' ou 'Commande Spéciale' selon que le montant est
inférieur ou supérieur à 100000 DH
Declare @Montant decimal
Set @Montant=(Select Sum(PUArt*QteCommandee) from Commande C, Article
A, LigneCommande LC where C.NumCom=LC.NumCom and
LC.NumArt=A.NumArt and C.NumCom=10)
If @Montant is null
Begin
Print 'Cette Commande n''existe pas ou elle n''a pas d''ingrédients'
Return
End
if @Montant <=10000
Print 'Commande Normale'
Else
Print 'Commande Spéciale
2--Ecrire un programme qui supprime l'article numéro 8 de la commande numéro 5
et met à jour le stock. Si après la suppression de cet article, la commande numéro 5
n'a plus d'articles associés, la supprimer.
Declare @Qte decimal
Set @Qte=(select QteCommandee from LigneCommande where NumCom=5 and
NumArt=8)
Delete from LigneCommande where NumCom=5 and NumArt=8
Update article set QteEnStock=QteEnStock+@Qte where NumArt=8
if not exists (select numcom from LigneCommande where NumCom=5)
Delete from commande where NumCom=5
3. Ecrire un programme qui affiche la liste des commandes et indique pour chaque
commande dans une colonne Type s'il s'agit d'une commande normale
(montant <=100000 DH) ou d'une commande spéciale (montant >= 100000 DH)
Select C.NumCom, DatCom, Sum(PUArt*QteCommandee), 'Type'=
Case
When Sum(PUArt*QteCommandee) <=10000 then 'Commande Normale'
Else 'Commande Spéciale'
End
From Commande C, Article A, LigneCommande LC
Where C.NumCom=LC.NumCom and LC.NumArt=A.NumArt
Group by C.NumCom, DatCom
4. A supposer que toutes les commandes ont des montants différents, écrire un
programme qui stocke dans une nouvelle table temporaire les 5 meilleures
commandes (ayant le montant le plus élevé) classées par montant décroissant (la
table à créer aura la structure suivante : NumCom, DatCom, MontantCom)
Create Table T1 (NumCom int, DatCom DateTime, MontantCom decimal)
Insert into T1 Select Top 5 C.NumCom, DatCom, Sum(PUArt*QteCommandee) as
Mt From Commande C, Article A, LigneCommande LC
Where C.NumCom=LC.NumCom and LC.NumArt=A.NumArt
Group by C.NumCom, DatCom
Order by Mt Desc
5---Ecrire un programme qui :
Recherche le numéro de commande le plus élevé dans la table commande
et l'incrémente de 1
Enregistre une commande avec ce numéro
Pour chaque article dont la quantité en stock est inférieure ou égale au seuil
minimum enregistre une ligne de commande avec le numéro calculé et une
quantité commandée égale au triple du seuil minimum
if exists(select NumArt from article where QteEnStock<=SeuilMinimum)
Begin
Declare @a int
set @a=(select max(NumCom) from commande) + 1
insert into commande values(@a, getdate())
insert into lignecommande Select @a, NumArt, SeuilMinimum * 3
From article Where QteEnStock <=SeuilMinimum
End
mardi 25 mars 2014
SGBD Tp procédure stokes avec correction
introduction :
Soit la base de données suivante :
Stagiaire (N°stagiaire, Nom, Prénom, datenaiss, , dateinscri, Adresse, tel, #Nfilière)
Filière(Nfilière, Intituléfil, Capacité, nbreannées)
Notation(N°notation ,#N°stagaire, #N°module, note)
Module (N°module, intitulémod, masse horaire)
Exercice avec correction complete :