Tfe

Old stuff/ecole_etude_fac_de_pau/licence_2/bd/requetes.sql

select 	code_int,nom
from	interprete
where	date_naiss<'01/01/1970';

select   titre
from     album
where    maison_disque = 'universel'
	AND genre = 'rock';


select *
from chanson
where 
	auteur='Henri Des' and
	compositeur='Henri Des';


select * 
from   chanson
where  auteur=compositeur;


select titre,date_sortie
from album,interprete
where 
	album.code_int=interprete.code_int and
	nom='Cali';


select titre_chan
from compose co, chanson c
where co.position=1
	AND co.num_c=c.num_c; 

select distinct titre
from album a,chanson c,compose co
where
	c.num_c=co.num_c and
	a.code_album=co.code_album and
	date_sortie between '31/12/03' and '01/01/05'
;

select distinct titre_chan
from            album a, chanson c,compose co, interprete i
where           a.code_int=i.code_int
            AND nom = 'Cali'
	    AND co.num_c=c.num_c
	    AND co.code_album=a.code_album;





select nom
from interprete
MINUS
select compositeur
from chanson;






select a.*
from album a ,interprete i
where a.code_int=i.code_int
   AND a.titre=i.nom;



select chan.*
from interprete i,album a, chanson chan, compose co
where
	i.code_int=a.code_int and
	a.code_album=co.code_album and
	co.num_c=chan.num_c and
	genre='pop' and
	duree > 120 and
	i.code_int in (
		select code_int 
		from interprete
		where extract(year from date_naiss) < extract(year from current_date)-40
	);