-- New Questions --
-- 1. -- 
-- Find the number of female students in the 'Computer Science' 
-- department who are in the fourth year of study.

drop table Query1Ans;

-- The answer table
create table Query1Ans(
	num  INTEGER
);

insert into Query1Ans(
select count(s.sid) as num
from student s, department d
where s.dcode = d.dcode and
d.dname = 'Computer Science' and
s.sex = 'F' and
s.yearofstudy = '4');

-- 2.--
-- Find the department with the highest number of
-- instructors who do not have a PhD degree. If there is a tie, return all
-- tied departments.

drop view deptNonPhDs;
drop table Query2Ans;

--The answer table
create table Query2Ans(
 	dname char(20)
);

--Department and the number of instructors without a 'PhD'
create view deptNonPhDs(dept, numNonPhDs) as 
	select dcode as dept, count(*) as numNonPhDs  
	from instructor 
	where idegree != 'PhD' 
	group by dcode;

--Department with the highest number of non 'PhD' instructors
insert into Query2Ans(
select d.dname
from deptNonPhDs dp1, department d
where dp1.numNonPhDs >= (select max(dp2.numNonPhDs) 
		 from deptNonPhDs dp2) and
      dp1.dept=d.dcode
);

-- 3.--
-- For each department find the best student. The student rank is determined by the 
--average grade of all of the courses they have completed. Courses for
--the current semester should not be included. If there is a tie for
--first place, include all students in the tie.
 
drop table Query3Ans;
drop view currentCourse CASCADE;
drop view studentAvg CASCADE;
drop view firstStudent CASCADE;

--The answer table
create table Query3Ans(
	dept char(20),
	sid integer,
	sfirstname char(20),
	slastname char(20),
	avgGrade numeric(5,2)
);

--The current courses
create view currentCourse(csid) as
	select cs.csid
	from courseSection cs
	where cs.year>=(select max(year) 
			from courseSection)
			and 
	      cs.semester>=(select max(semester)
			    from courseSection);

--Students and their avg. grades except for the current courses
create view studentAvg(sid,avg) as
	select sc.sid as sid, avg(grade) as avg
	from studentcourse sc
	where sc.csid not in (select csid
			      from currentCourse)
	group by sc.sid;

--The first students
create view firstStudent(sid,dcode,avg) as
	select sa.sid as sid, s.dcode as dcode, sa.avg as avg
	from studentavg sa, student s
	where s.sid = sa.sid and sa.avg >=(select max(sa1.avg)
			from studentavg sa1, student s1
			where sa1.sid = s1.sid and s.dcode = s1.dcode);

insert into Query3Ans(
	select d.dname, fs.sid, st.sfirstname, st.slastname, fs.avg
	from firstStudent fs, student st, department d
	where fs.sid=st.sid and st.dcode = d.dcode
	);



-- 4.--
-- Find the year between 2001 and 2006 in which the 'Computer Science' department had
--the highest course enrollment, compared to other years. Course 
--enrollement for a year is calculated as the sum of the 
--total number of students enrolled in each course for all courses
--in all semesters for that year.

drop table Query4Ans;
drop view yearEnroll CASCADE;

--The answer table
create table Query4Ans(
	year integer,
	enrollment integer
);

--Years and the number of enrollments for each year for 
--'Computer Science' deparment
create view yearEnroll(year, number) as
	select year, count(*)
	from studentCourse sc, courseSection cs, department d
	where sc.csid=cs.csid and cs.dcode=d.dcode and 
	d.dname='Computer Science' and year >= 2001 and year <= 2006 
	group by year;

--The year in which the 'Computer Science' department had
--the highest course enrollment
insert into Query4Ans(
	select year, number
	from yearEnroll
	where number >=(select max(number)
		        from yearEnroll)
	);


-- 5. --
-- Find all the courses in 'Computer Science' department that are taught ONLY in summer
-- semester. 

drop table Query5Ans;

create table Query5Ans(
	courseName CHAR(20)
);

insert into Query5Ans(
	select distinct(c.cname) as courseName
	from course c, department d, courseSection cs
	where c.dcode = d.dcode and 
	d.dname = 'Computer Science' and 
	c.cid = cs.cid and 
	cs.semester = '5'
	except
	select distinct(c1.cname) as courseName
	from course c1, department d1, courseSection cs1
	where c1.dcode = d1.dcode and 
	d1.dname = 'Computer Science' and 
	c1.cid = cs1.cid and 
	(cs1.semester = '9' or cs1.semester = '1')
);

-- 6. --
--List all the students and courses  for all situations where 
--a student has taken a course without previously having taken
--the prerequisites. Report a student as many times as the number 
--of courses with no prerequisites that they have taken  
--(but not for each prerequisite).

drop view hasPrereqsSol CASCADE;
drop view studCourseDetailSol CASCADE;
drop view needPrereqs CASCADE;
drop view partialAnswer CASCADE;
drop table Query6Ans;

--Create the answer table
create table Query6Ans(
	fname char(20),
	lname char(20),
	cname char(20),
	year integer,
	semester integer
);

--Courses that is/has taken by students
create view studCourseDetailSol(sid, cid, dcode,year, semester) as
	select sc.sid, cs.cid, cs.dcode, cs.year, cs.semester
	from studentCourse sc, courseSection cs
	where sc.csid=cs.csid;

--Courses and their prerequisites taken by students
create view hasPrereqsSol(sid, cid, dcode, year, semester) as
	select distinct sd1.sid, sd1.cid,sd1.dcode, sd1.year, sd1.semester
	from studCourseDetailSol sd1, prerequisites pq, studCourseDetailSol sd2
	where sd1.cid=pq.cid and sd1.dcode=pq.dcode and 
	pq.pcid=sd2.cid and pq.pdcode=sd2.dcode and sd1.sid=sd2.sid and
	((sd2.year<sd1.year) or (sd2.year=sd1.year and sd2.semester<sd1.semester));

--Courses taken by student and their prerequisites 
create view needPrereqs(sid, cid, dcode, year, semester) as
	select sd.sid, sd.cid, sd.dcode, sd.year,sd.semester
	from studCourseDetailSol sd, prerequisites pq
	where sd.cid=pq.cid and sd.dcode=pq.dcode;

--What is requested
create view partialAnswer(sid, cid, dcode, year, semester) as
	select *
	from needPrereqs
	except
	select *
	from hasPrereqsSol;

insert into Query6Ans(
	select s.sfirstname as fname, s.slastname as lname, c.cname, pa.year, pa.semester
	from partialAnswer pa, student s, course c
	where pa.sid = s.sid and pa.cid = c.cid);

-- 7. --
--Of all the courses offered in the 'Computer Science' 
--department which had an enrollment of at least 3 students 
--find the course with the highest average marks and 
--the course with the lowest average marks.  Include all 
--tied results. (Hint: Do not need to consider the current semester which does not yet have any marks.)

drop view courseAvg;
drop view currentCourse; 
drop view partialAns;
drop table Query7Ans;

--The answer table
create table Query7Ans(
	cname char(20),
	semester integer,
	year integer,
	avgmark numeric(5,2)
);		


create view currentCourse(csid) as
        select cs.csid
        from courseSection cs
        where cs.year>=(select max(year)
                        from courseSection)
                        and
              cs.semester>=(select max(semester)
                            from courseSection);
 
create view courseAvg(csid, avgmark) as 
	select csn.csid, avg(sc.grade) as avgmark
	from courseSection csn, studentCourse sc, course c, department d
	where c.cid = csn.cid and c.dcode = d.dcode and d.dname = 'Computer Science' and 
	csn.csid = sc.csid and csn.csid not in (select * from currentCourse)
	group by csn.csid
	having count(*) >= 3;

create view partialAns(csid, avgmark) as
	select ca.csid, ca.avgmark
	from courseAvg ca
	where ca.avgmark >= (select max(avgmark) from courseAvg)
	union
	select ca.csid, ca.avgmark
	from courseAvg ca
	where ca.avgmark <= (select min(avgmark) from courseAvg);

  
insert into Query7Ans(
   	select c.cname, csn.semester, csn.year, pa.avgmark
	from course c, courseSection csn, partialAns pa
	where c.cid = csn.cid and c.dcode = csn.dcode and csn.csid = pa.csid); 

