base de donnée Ju2010EFF-V01

[bouazza driouch]

create database Hopital0

use Hopital0

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

insert into Hopital values ()

 

--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

--

Formateur Ingénieur : OFPPT - DRGC/CFMOTI
Cours-Exercice-Tp-Examen et Correction, Sur:
http://www.tsntic.eb2a.com/ ou http://www.driouchb.0fees.net/