---  Query for Semester Summary 

set serveroutput on

--- 1. -----------Registered Students ----------------

---      Total Students

select count(distinct reg_no) from src
where semester_code=3 and semester_year=2006
/

--- 2. --------Academic Standings Procedure------------

create or replace procedure summary as
 cursor c1 is select * from it where sc=3 and sy=2006;
 HD number:=0;
 H number:=0;
 D number:=0;
 G number:=0;
 S number:=0;
 C number:=0;
 P number:=0;
 N number:=0;
 total number;
 rem varchar2(200);
 begin
 for x in c1 loop
 if round(x.sgpa,2) >= 3.90 then
     HD:=HD+1;
 elsif       x.sgpa>=3.75 then
     H:=H+1;
 elsif       x.sgpa>=3.50 then
     D:=D+1;
 elsif       x.sgpa>=3.00 then
     G:=G+1;
 elsif       (x.sgpa>1.99 and x.cgpa<1.99) then
    C:=C+1;
    rem:='Caution';
 elsif      (x.sgpa>1.99 and x.cgpa >=1.99) then
    S:=S+1;
    rem:='Satisfactory';    
 elsif       x.sgpa<=1.99 then
    P:=P+1;
     rem:='Probation';
 else       n:=n+1;
    rem :='Normal';
 end if;
 end loop;
 total:=hd+h+d+g+c+s+p+n;
 dbms_output.put_line('-');
 dbms_output.put_line('-');
 dbms_output.put_line('-');
 dbms_output.put_line('Registered Students '||total);
 dbms_output.put_line('-');
 dbms_output.put_line('-');
 dbms_output.put_line('Highest Distinction '||hd);
 dbms_output.put_line('Hight Distinction '||h);
 dbms_output.put_line('Distinction '||d);
 dbms_output.put_line('Good '||g);
 dbms_output.put_line('Satisfactory '||s);
 dbms_output.put_line('Caution '||c);
 dbms_output.put_line('Probation '||p);
 dbms_output.put_line('-');
 dbms_output.put_line('-');
 end;
/

exec summary
/

/*---3..To find total no of students on probation 1,2,3

select count(regno),remarks from(
SELECT REGNO, upper(rpad(name,40)) as name,faculty,SGPA,cgpa, MAX (PROBATION) as remarks
  FROM   (select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation1' AS PROBATION from it
        where sc=3 and sy=2006
        and sgpa<=1.995
        UNION ALL
        select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation2' AS PROBATION from it
       where sc=3 and sy=2006
        and sgpa<=1.995
        and regno in(
        select regno from it
        where sc=1 and sy=2006
        and sgpa<=1.995)
        UNION ALL
        select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation3' AS PROBATION from it
      where sc=3 and sy=2006
        and sgpa<=1.995
        and regno in(
        select regno from it
        where sc=1 and sy=2006
        and sgpa<=1.995
       and regno in(
        select regno from it
        where sc=3 and sy=2005
        and sgpa<=1.995))) gg, grn
where grn.rn=gg.regno
  GROUP  BY REGNO,name,faculty, SGPA, cgpa)
group by remarks
/


/*--- 4 ---------Courses Offered

select count(distinct course_code) from focd
where semester_code=3 and semester_year=2006
/


select faculty_rt.faculty_initials as faculty,course_type,count(focd.course_code) as courses
from focd,fcp,faculty_rt
where focd.course_Seq_no=fcp.course_seq_no
and focd.faculty_code=faculty_rt.faculty_code
and semester_code=3 and semester_year=2006
group by course_type,faculty_initials
order by 1
/


/*---4 ------------ Student Courses --------------

select count(*) from src
where semester_code=3 and semester_year=2006
/

/*---5 ----- Class Average GPA Query -----------------


 select substr(reg_no,1,4) as class,
 count(substr(reg_no,1,4)) as st,
 sum(gradenumericvalue) as gp,
 round((sum(gradenumericvalue))/(count(substr(reg_no,1,4))),2) as avrg,src.tempcolfac
 from src,grades_rt
 where src.gradecharvalue=grades_rt.gradecharvalue
 and semester_code=3 and semester_year=2006
and src.gradecharvalue not in('W','I')
 group by substr(reg_no,1,4),src.tempcolfac
 order by 1 desc
/

Q_1
select count(distinct reg_no) from src
where semester_code=3 and semester_year=2006


Q_2
select count(distinct course_code) from focd
where semester_code=3 and semester_year=2006

Q_3

select count(*) from src
where semester_code=3 and semester_year=2006

you'll have to manually adjust items in Paper Layout