-- Q6: Who are the Junior members of the Senate? Who are the Senior members?
select xmlelement("results", xmlattributes(sen3.JuniorOrSenior as "JuniorOrSenior"), xmlagg(xmlelement("SENATOR", xmlforest(sen3.firstName, sen3.middleInitial, sen3.lastName, sen3.suffix)))) as Results
from (
SELECT sen1.*, case when sen1.dateAssumedOffice < sen2.dateAssumedOffice then 'Senior' else 'Junior' end AS JuniorOrSenior
FROM Senator sen1
INNER JOIN Senator sen2
ON sen1.state=sen2.state
WHERE NOT sen1.id=sen2.id AND NOT sen1.dateAssumedOffice IS NULL AND NOT sen2.dateAssumedOffice IS NULL
ORDER BY sen1.state
) sen3
group by sen3.JuniorOrSenior;