/* DISPLAY STUDENT PERFORMANCE (average grade) BY PROGRAM */

select program, round(avg(grade),3) as GPA  
from student join grade using(student_id)
group by program
order by 1 desc;


/* DRILL DOWN BY SEX */

select 'Drill down by Sex', 
        program, sex, round(avg(grade),3) as GPA  
from student join grade using(student_id)
group by program, sex
order by 2 desc, 3;


/* DISPLAY THE ABOVE IN CROSS TAB (HORIZONTAL) */

select program as "Cross Tab", 
       max(case sex when 'F' then GPA end) as "F",
       max(case sex when 'M' then GPA end) as "M"
from
    (select program, sex, round(avg(grade),3) as GPA  
     from student join grade using(student_id)
     group by program, sex) i
group by program
order by 1 desc; 


/* DRILL DOWN BY SEX AND SCHOOL_YEAR */

select 'Drill down by Sex and School_year', 
        program, sex, school_year, round(avg(grade),3) as GPA  
from student join grade using(student_id)
where program = 'undergraduate'
  and sex     = 'F'
group by program, sex, school_year
order by 4;

select 'Drill down by Sex and School_year', 
        program, sex, school_year, round(avg(grade),3) as GPA  
from student join grade using(student_id)
where program = 'undergraduate'
  and sex     = 'M'
group by program, sex, school_year
order by 4;

select 'Drill down by Sex and School_year', 
        program, sex, school_year, round(avg(grade),3) as GPA  
from student join grade using(student_id)
where program = 'graduate'
  and sex     = 'M'
group by program, sex, school_year
order by 4;

select 'Drill down by Sex and School_year', 
        program, sex, school_year, round(avg(grade),3) as GPA  
from student join grade using(student_id)
where program = 'graduate'
  and sex     = 'F'
group by program, sex, school_year
order by 4;