Consider a database for the basic and secondary education system (B + S) with the following attributes:

• Curricular_Year = {1, 2, 3, ... 12}
• Teaching_level = {basic, secondary}
• Nature = {public education, private education}
• Modality = {regular basic education, basic vocational education,

secondary education, secondary vocational education, other basic}

• NUTS = {Metropolitan Area Lisbon, Metropolitan Area Porto, Centro,

Autonomous Region of Azores, Autonomous Region of Madeira}

• Transitions = {transit, retained, abandoned, rejoined}

And with the following tables:

• students (studentID -> name, address, contact, supervisor_education)
• history (idFact -> studentID, ano_letivo, ano_curricular, nuts, idModality, nature, transition)
• modalities (idModality -> levelID)
• levels (level)

a) In the academic year 2014-2015 and in the year 2015-2016, which students (idEstudante) left school in the 12th year, at NUTS of RA Aores and RA Madeira? Use the join operator in the query.

b) Which students from the academic year 2015-2016 and in the academic year N, who in the previous academic year (2014-2015) were in a year lower than N-1. Example: (est12345, 2014-2015, 7year, 2015-2016, 10year). Use sub queries with IN clause in your query.

c) Which students have more retentions than the average retention of all students?