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 ;