\i /ens/jurski/Public/BD/TP1.sql

---------- 1 ----------
select * from produit;
select * from usine;
select * from magasin;
select * from provenance;

---------- 2 ----------
select * from usine where ville='Marseille';

---------- 3 ----------
select ref_mag from provenance where ref_usine=109 and ref_prod=1;

---------- 4 ----------
select ref_prod,nom_prod from produit where couleur='rouge';

---------- 5 ----------
select ref_prod, nom_prod from produit where nom_prod like 'casse%';

---------- 6 ----------
select ref_mag from provenance where quantite>0;
select distinct ref_mag from provenance;

---------- 7 ----------
select ref_mag from magasin except select ref_mag from provenance;

---------- 8 ----------
select nom_prod,couleur from produit where ref_prod in (select ref_prod from provenance where ref_usine=189);
select nom_prod,couleur from (produit natural join provenance) where ref_usine=189;
select distinct on (ref_prod) nom_prod,couleur from (produit natural join provenance) where ref_usine=189;

---------- 9 ----------
select ref_mag from provenance where ref_usine=302 and ref_prod in (select ref_prod from produit where couleur='rouge');

---------- 10 ----------
select poids*quantite as poids_livraison from (produit natural join provenance) where ref_prod=12 and ref_usine=189 and ref_mag=30;
select poids*quantite as poids_livraison from ((select * from produit where ref_prod=12) as a natural join provenance) where ref_prod=12 and ref_usine=189 and ref_mag=30;

---------- 11 ----------
select ref_prod,poids*quantite as poids_livraison,ref_mag,ref_usine from (produit natural join provenance);

---------- 12 ----------
select nom_usine,nom_mag,ville from (usine natural join magasin);

---------- 13 ----------
select distinct ref_mag,ref_mag2 from (provenance natural join (select ref_prod,ref_usine,ref_mag as ref_mag2 from provenance) as a) where ref_mag<>ref_mag2;

---------- 14 ----------
select nom_mag1, nom_mag as nom_mag2 from (magasin natural join (select nom_mag as nom_mag1,ref_mag2 as ref_mag from (magasin natural join (select distinct ref_mag,ref_mag2 from (provenance natural join (select ref_prod,ref_usine,ref_mag as ref_mag2 from provenance) as a) where ref_mag<>ref_mag2) as b) as c) as d);
select nom_mag1,nom_mag as nom_mag2 from ((magasin natural join provenance) natural join (select ref_prod,ref_usine,nom_mag as nom_mag1 from (magasin natural join provenance) as a) as b) where nom_mag1<>nom_mag;

---------- 15 ----------
select nom_mag from (magasin natural join (select * from provenance where ref_prod=12) as a);
select nom_mag from magasin where ref_mag in (select ref_mag from provenance where ref_prod=12);

---------- 16 ----------
select nom_mag from magasin where ref_mag not in (select ref_mag from provenance where ref_prod=12);

---------- 17 ----------
select nom_mag from magasin where ref_mag in (select ref_mag from provenance where ref_prod in (select ref_prod from produit where couleur='rouge'));

---------- 18 ----------
select nom_mag from magasin where ref_mag in (select distinct ref_mag from provenance where ref_usine in (select ref_usine from usine where nom_usine<>'enfer'));