create schema mif04;
set search_path to mif04;

create table ue(
	titre text not null,
	resume text not null,
	code varchar(8) primary key,
	niveau text not null
);


insert into ue values('BD pour le web', 'sparql, xml et le reste', 'MIF04', 'M1');
insert into ue values('Prog web', 'java et le reste', 'MIF03', 'M1');
insert into ue values ('SSI', 'Sécurité', 'TIW4', 'M2');

create table etudiant(
	nom text not null,
	adresse text not null,
	num varchar(8) primary key
);

insert into etudiant values('Romuald', 'Lyon', 'p1');
insert into etudiant values('Emmanuel', 'Villeurbanne', 'p2');
insert into etudiant values('Lionel', 'Villeurbanne', 'p3');

create table inscription(
	codeUE varchar(8) references ue(code),
	numEtu varchar(8) references etudiant(num),
	semestre text
);


alter table inscription add primary key (codeue, numetu, semestre);
insert into inscription values('MIF03', 'p1', '2019A');
insert into inscription values('MIF03', 'p2', '2019A');
insert into inscription values('MIF04', 'p2', '2019A');

select xmlelement(
  name etudiant,
  xmlattributes(num as "num"), 
  xmlelement(name nom, nom),
  xmlelement(name adresse, adresse)
)
from etudiant;

select xmlelement(
  name ue,
  xmlattributes(code as "codeUe"), 
  xmlelement(name titre, titre),
  xmlelement(name resume, resume),
  xmlagg(xmlelement(name inscrit, xmlattributes(numEtu as "num"))) FILTER (where numEtu is not null) 
)
from ue left outer join inscription on ue.code = inscription.codeue
group by code;



