set serveroutput on size 32000

--Procedure to print students in a department, given the deptid
create or replace procedure students_in_department(dept IN number) as

cnt number;
CURSOR stu_cur is select sname from Student where deptid=dept;
stu_rec stu_cur%rowtype;

begin
	cnt := 1;
	for stu_rec in stu_cur loop
		dbms_output.put_line(cnt || '. ' || stu_rec.sname);
		cnt := cnt + 1;
	end loop;
end students_in_department;
/

--actually run the procedure
begin
students_in_department(1);
end;
/




--Procedure to print student names taking a specific class
create or replace procedure students_in_class(class_name IN Enrolled.cname%TYPE) as

stu_number number;
stu_name varchar(20);

CURSOR enr_cur is select snum from Enrolled where cname=class_name;
enr_rec enr_cur%rowtype;

begin
	for enr_rec in enr_cur loop
		stu_number := enr_rec.snum;
		select sname into stu_name from Student where snum=stu_number;
		dbms_output.put_line(stu_name);
	end loop;
end students_in_class;
/

begin
students_in_class('ENG400');
end;
/


--Procedure to insert a faculty to the database
create or replace procedure add_faculty(fid IN number, fname IN varchar, deptid IN number) as

begin
INSERT INTO Faculty Values(fid, fname, deptid);
end add_faculty;
/

select * from Faculty;

begin
add_faculty(2010, 'B.Bhargava',1);
end;
/

select * from Faculty;



--Procedure to print the average ages of students for each department
create or replace procedure average_ages as
avg_age number;
num_students number;
dept Department.dname%TYPE;

CURSOR dept_cur is select distinct deptid from Student;

CURSOR age_cur(did IN number) is select age from Student where deptid=did;

dept_rec dept_cur%rowtype;
age_rec age_cur%rowtype;

begin
	for dept_rec in dept_cur loop
		avg_age := 0;
		num_students := 0;
		for age_rec in age_cur(dept_rec.deptid) loop
			avg_age := avg_age + age_rec.age;
			num_students := num_students + 1;
		end loop;
		avg_age := avg_age/num_students;
		select dname into dept from Department where deptid=dept_rec.deptid;
		dbms_output.put_line(dept || ' || ' || avg_age);
	end loop;
end average_ages;
/

begin
average_ages;
end;
/