SQL Query — Avg SGPA of Boys from Karnataka, Odd Semesters (2005–2009)

Using the star schema from Q4a (FACT_PERFORMANCE, DIM_STUDENT, DIM_SESSION):

SELECT
    ds.academic_year,
    AVG(fp.sgpa) AS avg_sgpa
FROM
    FACT_PERFORMANCE fp
    JOIN DIM_STUDENT  st ON fp.student_key  = st.student_key
    JOIN DIM_SESSION  ds ON fp.session_key  = ds.session_key
WHERE
    st.gender = 'M'
    AND st.state = 'Karnataka'
    AND ds.semester_type = 'Odd'
    AND ds.academic_year BETWEEN 2005 AND 2009
GROUP BY
    ds.academic_year
ORDER BY
    ds.academic_year;

How it maps to the requirement

• st.gender = 'M' → restricts to boys.
• st.state = 'Karnataka' → restricts to the given state.
• ds.semester_type = 'Odd' → restricts to Autumn semesters only.
• ds.academic_year BETWEEN 2005 AND 2009 → restricts the year range.
• GROUP BY ds.academic_year → produces one average SGPA row per year, as required ("for each Autumn semester during 2005–2009").
• The joins connect the fact table's measures (sgpa) to the descriptive attributes (gender, state, academic_year) stored in the dimension tables — this is exactly why a star schema keeps such analytical queries simple (only two joins, no deep nesting).