co ren bdd

Compte rendu de tp 01
Talailef ahlem
Création des tables
Zoubiri ranya
create table hotell (
n_hotel int primary key identity(1,1) ,
nom_hotel nvarchar(100) ,
prix_participation int ,
prix_suppl int ,
nombre_etoiles int ,
)
create table activite (
n_activite int primary key identity(1,1) ,
nom_activite nvarchar(200) ,
prix_activite int ,
heure_act datetime ,
check (prix_activite<150),
)
create table congressiste (
addresse_cong nvarchar(200) ,
date_inscription date ,
n_congressiste int primary key identity(1,1) ,
n_hotel int ,
n_org int foreign key references organisme_payeur (n_organisme) ,
nom_congressiste nvarchar(200) ,
prenom_congressiste nvarchar(200) ,
tel_congressiste int ,
)
create table organisme_payeur (
n_organisme int primary key identity(1,1) ,
addresse_org nvarchar(200) ,
nom_org nvarchar(100) ,
)
create table participation (
n_activite int primary key foreign key references activite (n_activite) ,
n_cong int foreign key references congressiste (n_congressiste) ,
nbr_personne int ,
)
Requêtes de projection
select nom_hotel ,nombre_etoiles , address_hotel from hotell ;
select DISTINCT address_hotel from hotell ;
Requêtes de sélection
select * from congressiste where addresse_cong = 'Blida' ;
select nom_congressiste ,prenom_congressiste from congressiste where n_org is
NULL;
select * from congressiste where n_hotel = 2 or n_hotel = 3 or n_hotel = 4 ;
select * from activite where date_act = '2013-11-13' or date_act = '2013-11-22' ;
select * from hotell ORDER BY nom_hotel ASC ;
select * from activite where prix_activite between 200 and 520 ;
select nom_congressiste ,prenom_congressiste from congressiste where
nom_congressiste LIKE'B%' ;
select * from hotell where nom_hotel LIKE '%a%' ;
select nom_congressiste ,prenom_congressiste from congressiste where
nom_congressiste LIKE'%t' ;
select * from hotell where prix_participation between 35 and 55 and prix_suppl
between 20 and 30;
select prix_participation + prix_suppl as total from hotell ;
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel
select n_congressiste , nom_congressiste,nom_hotel ,nom_org from congressiste
,hotell , organisme_payeur where congressiste.n_hotel =hotell.n_hotel and
congressiste.n_org = organisme_payeur.n_organisme;
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel order by nom_hotel asc ;
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel order by nom_congressiste asc ;
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel and address_hotel='Blida' order by
nom_congressiste asc ;
Requêtes par fonction
select count (*) from congressiste ;
select avg (prix_participation ) from hotell where address_hotel ='Alger' ;
select avg (prix_suppl ) from hotell where address_hotel ='Alger' ;
select n_activite , count (n_cong) as num_cong from participation
group by (n_activite);
select n_cong , avg(nbr_personne) as moyenne from participation group by n_cong
having count (n_activite) >2 ;
Requêtes imbrique
select nom_congressiste , prenom_congressiste from congressiste where
n_congressiste in (select n_cong from participation where n_activite = 3);
select nom_congressiste , prenom_congressiste from congressiste where
n_congressiste in (select n_cong from participation , activite where
participation.n_activite=activite.n_activite and nom_activite != 'toumoi');
select nom_congressiste , prenom_congressiste from congressiste where n_hotel in
(select n_hotel from hotell where (prix_participation + prix_suppl) >70 );
select nom_congressiste , prenom_congressiste from congressiste ,participation
where congressiste.n_congressiste = participation.n_cong and n_activite in(select
n_activite from activite where prix_activite = (select MIN (prix_activite) from activite
));
Requêtes jointure
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel
select n_congressiste , nom_congressiste,nom_hotel ,nom_org from congressiste
,hotell , organisme_payeur where congressiste.n_hotel =hotell.n_hotel and
congressiste.n_org = organisme_payeur.n_organisme;
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel order by nom_hotel asc ;
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel order by nom_congressiste asc ;
select n_congressiste , nom_congressiste,nom_hotel from congressiste ,hotell
where congressiste.n_hotel =hotell.n_hotel and address_hotel='Blida' order by
nom_congressiste asc ;