

PROMPT --- Requete 1 --- ;

select nc,prix from circuit where nc not in 
	(select nc from ciretape) order by prix desc;


PROMPT --- Requete 2 --- ;

select distinct nc from ciretape
where vilet not in 
	(select nomv from ville where pays<>'France')
	order by nc;


PROMPT --- Requete 3 --- ;

select distinct nc from circuit where nc not in 
(select nc from ciretape where vilet not in 
(select nomv from ville where pays='France')) order by nc asc;


PROMPT --- Requete 4 --- ;

select distinct nc,nomm from ciretape,monument 
where vilet=nomv
and vilet not in 
	(select nomv from ville where pays<>'France')
	order by nc;


PROMPT --- Requete 5 --- ;

select nc,count(*) as "nb villes" 
from ciretape group by nc order by nc; 


PROMPT --- Requete 6 --- ;

select c1.nc, count(vilet) as nbvilet 
from circuit c1, ciretape c2
where c1.nc = c2.nc (+) 
group by c1.nc
order by nbvilet;


PROMPT --- Requete 7 --- ;

select distinct nomc from reser r, ciretape c
where r.nc=c.nc
and vilet='Londres'
and to_char(datedep,'YY/MM') in ('06/03','06/04')
order by nomc;


PROMPT --- Requete 8 --- ;

select nomc,sum(nbres) as nbplaces
from reser group by nomc
having sum(nbres)>20
order by nbplaces;


PROMPT --- Requete 9 --- ;

select nc, sum(prix) as "somme prix" from ciretape, monument
where vilet=nomv group by nc
having sum(prix) > 10
order by nc asc;


PROMPT --- Requete 10 --- ;

select nc, sum(prix) as "somme prix" from ciretape, monument
where vilet=nomv group by nc
having sum(prix) = (
select max(sum(prix)) from ciretape, monument
where vilet=nomv group by nc
);