Ju2010-EFF TP V01: Correction SQL

create database Hopital
use Hopital
Go
Create table Hopital(idhopital int identity primary key, nom varchar(30),adresse varchar(100),ville varchar(50))
Create table Specialite(idspecialite int identity primary key, libelle varchar(30))















Create table Patient(idpatient int identity primary key, nom
varchar(30),prenom varchar(30), DdN smalldatetime, Sexe
varchar(10),adresse varchar(100))
Create table Servicee(idservice int identity primary key, idhopital int foreign key references Hopital(idhopital),Nblits int)
Create table Medecin(idmedecin int identity primary key,Nom
nvarchar(30), prenom varchar(30), DdN smalldatetime, sexe varchar(10),
idspecialite int foreign key references
Specialite(idspecialite),idservice int foreign key references
Servicee(idservice) )
Create table Sejourne(idsejour int identity primary key, idservice
int foreign key references Servicee(idservice),idpatient int foreign key
references Patient(idpatient), DateEntree smalldatetime,DateSortie
smalldatetime)
Create table Soigne(idsoin int identity primary key, idmedecin int
foreign key references Medecin(idmedecin),idpatient int foreign key
references Patient(idpatient), NomMaladi varchar(150), Commentaire
varchar(200),Date_soigne smalldatetime)
Go







--A
Create proc AffNbreSej @idPatient int as
begin
select count(*) as Nbre from sejourne where idpatient=@idpatient
end

exec AffNbreSej 2
--B
Create Proc SejourDat @dat1 smalldatetime,@dat2 smalldatetime as
begin
select Soigne.IdSoin,Medecin.nom,patient.nom
from soigne,medecin,sejourne,patient,Servicee
Where sejourne.dateEntree>=@dat1 AND sejourne.dateSortie<=@dat2
AND sejourne.idpatient=patient.idpatient ANd Sejourne.idservice=servicee.idService
AND Servicee.idService=Medecin.IdService
end

exec SejourDat '1/1/2001','1/5/2001'
--C

create proc MedSejourne as
begin
select patient.idpatient, Nom, prenom
From patient,sejourne,soigne
Where patient.idpatient=sejourne.idpatient and patient.idpatient=soigne.idpatient
group by idsejour,patient.idpatient, Nom, prenom
having count(distinct idmedecin)>=2
end
exec MedSejourne





-- D
alter trigger formatTel on medecin for insert,update
as
begin
update medecin set tel= substring(tel,1,2) + ' ' +substring(tel,3,2)+
'.' +substring(tel,5,2)+ '.' +substring(tel,7,2)+ '.'
+substring(tel,9,2) where idmedecin in (select idmedecin from inserted)
end

-- test
insert into medecin( nom, prenom, ddn, sexe, idspecialite, idservice, tel)
values('Ahmed00','Ahmed00','1/1/2000','M',1,1,'0522332222')

update medecin set tel='0522336622' where idmedecin=1
select * from medecin
select * from sejourne
si vous avez des commentaires n' hésiter pas