Exercice
SGBDR 2
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 :
Questions :
Solution ;
-- 1)
use master
go
create database
M21N3Ex1--AcciRoute
go
-- 2)
use M21N3Ex1
go
alter Proc AcciRoute
as
begin
if exists(select * from
sys.tables where name like 'Personne')
drop table Personne
create table Personne (NAS
char(9)primary key, nom varchar(35), VilleP Varchar(50))
if exists(select * from
sys.tables where name like 'Voiture')
drop table Voiture
create table Voiture (Imma
Char(6)primary key, modele varchar(20), annee smalldatetime, nas char(9))
if exists(select * from
sys.tables where name like 'Accident')
drop table Accident
create table Accident
(DateAc smallDatetime, NAS char(9)foreign key references Personne(Nas),
dommage numeric(7,2),
villeAc varchar(50),imma char(6)foreign key references Voiture(imma))
end
Go
Exec AcciRoute
go
-- 3)
create proc inserte(@dateAc
datetime,@NAS char,@dommage numeric,@villeAc varchar,@imma char)
as
begin
BEGIN try
if not
exists(select * from Personne where @NAS=NAS)
RAISERROR('NAS introuvable ',15,1)
else if not
exists(select * from Voiture where @Imma=Imma)
RAISERROR('IMMA introuvale ',15,1)
else --if not
exists(select * from Accident)
insert
into Accident values(@dateAc,@NAS,@dommage,@villeAc,@imma)
end try
begin catch
select
ERROR_MESSAGE() AS ErrorMessage
end catch
end
Exec inserte '12/06/1993
05:30','xxx',30,'rabat','yyy'
Exec inserte
'xxx','fandi','casa'
Exec inserte
'yyy','uno','12/05/1990','qqq'
Exec inserte
'12/06/1993','xxx',30,'rabat','yyy'
go
--- 4)
alter proc GetnumProp(@A1
int,@A2 int)
as
begin
select COUNT(distinct
NAS) from Accident where YEAR(DateAc) between @A1 and @A2
end
Go
exec GetnumProp 1990,1998
go
--- 5)
create proc GetProp
as
begin
select P.nom,P.NAS from
Personne P,Accident A1,Accident A2
where A1.NAS=A2.NAS and
A1.NAS=P.NAS and A1.NAS>=A2.NAS and ABS(DATEDIFF(month ,A1.DateAc,A2.DateAc))>4
end
exec GetProp
go
--- 6)
alter proc GetDamCity @ville
varchar(12) as
begin
select sum(dommage) as
'total' ,case when sum(dommage)<=5000 then 'catégorie1'
when (sum(dommage)between
5000 AND 10000) then 'catégorie2'
else 'catégorie3'
end
from Accident where
@ville=villeAc
end
exec GetDamCity
go
---- autre méthode
alter proc GetDamCiti @ville
varchar(12) as
begin
declare @total numeric(6)
select @total=sum(dommage)
from Accident where @ville=villeAc
if @total<5000
begin
print 'catégorie1'
end
if @total between 5000 AND
10000
begin
print 'catégorie2'
end
if @total>10000
begin
print 'catégorie3'
end
print @total
end
exec GetDamCiti
go
---Q6
create proc GetnumAcci as
begin
select villeAc, COUNT(*)as
'nombre total' from Accident group by villeAc
end
exec GetnumAcci
go
-----Q7
alter proc GetNamProp @X int
as
begin
select P.nom,COUNT(*)as
'nombre accident' from Personne P,Accident A
where P.VilleP=A.villeAc
group by P.nom,A.villeAc
having count(*)>=@X
end
exec GetNamProp 1
go
-----Q8
create proc GetnumAcciDat
@DateAc datetime as
begin
select COUNT(*)as'nbr
d"accident' from Accident where DateAc=@DateAc
end
exec GetnumAcciDat
'01/01/2010'
go
----Q9
create proc GetnumAcciHour
@h1 datetime,@h2 datetime as
begin
select count(*)as 'nbr
d"accident' from Accident A where DATEPART(hour,A.DateAc) between
DATEPART(hour,@h1)and DATEPART(hour,@h2)
end
exec GetnumAcciHour
'01:00:00','02:10:00'
go
------Q10
alter proc UpdateDam as
begin
update Accident set
dommage=dommage-(dommage*0.05) where dommage>=5000
end
exec UpdateDam
|
Exercice Corrigées Transact Sql (SGBD 2)