CREATE VIEW StudentsFollowing AS (
	SELECT id, Students.programme, branch
	FROM Students LEFT JOIN Belongs_To_Branch ON id = student
);

CREATE VIEW FinishedCourses AS (
	SELECT id, name, course, grade
	FROM Students JOIN (
		SELECT student, course, grade
		FROM Passed
		UNION
		SELECT student, course, 'U'
		FROM Failed
	)
	ON (id = student)
);

CREATE VIEW Registrations AS (
	SELECT student, course, 'registered' as status
	FROM Registered_to
	UNION
	SELECT student, limitedcourse, 'waiting' as status
	FROM Queues_to
);

CREATE VIEW CourseQueuePositions AS (
	SELECT limitedcourse, student, placeinline as place_in_line
	FROM Queues_to
);

CREATE VIEW PassedCourses AS (
	SELECT student, course
	FROM Passed
);

CREATE VIEW UnreadMandatory AS (
	SELECT Students.id, course
	FROM Students JOIN Programme_has_mandatory
	ON (Students.programme = Programme_has_mandatory.programme)

	UNION

	SELECT id, course
	FROM (
		SELECT id, Students.programme, branch as ilikemonkeys
		FROM Students LEFT JOIN (
			SELECT student, programme, branch
			FROM Belongs_To_Branch
		)
		ON (id = student)
	)
	JOIN Branch_has_mandatory

	ON (Branch_has_mandatory.branch = ilikemonkeys)

	MINUS

	SELECT student, course
	FROM Passed
);

CREATE VIEW PathToGraduation AS

		WITH PassedCourseCredits AS
		(
	 	SELECT student, course, credit
		FROM PassedCourses JOIN (
			SELECT code, credit
			FROM Courses
			) ON (course = code)
		),

		StudentTotalCredits AS
		(
		SELECT student, NVL(SUM(credit), 0) as total_credits
		FROM PassedCourseCredits
		GROUP BY student
		),

		LeftMandatory AS
		(
		SELECT id as student, NVL(COUNT(course), 0) as left_mandatory
		FROM UnreadMandatory
		GROUP BY id
		),

		BranchRecommendedCredits AS
		(
		SELECT student, course as course2, credit
		FROM PassedCourseCredits NATURAL JOIN (
			Branch_recommends
			)
		),

		StudentBranchCredits AS
		(
		SELECT student, NVL(SUM(credit), 0) as branch_credit
		FROM BranchRecommendedCredits
		GROUP BY student
		),

		CoursesWithTypes AS
		(
		SELECT code, credit, coursetype
		FROM Courses JOIN (
			Has_type )
		ON  (course = code)
		),

		PassedCoursesWithTypes AS
		(
		SELECT student, code, credit, coursetype
		FROM Passed LEFT JOIN CoursesWithTypes
		ON (course = code)
		),

		MathCourseCredits AS
		(
		SELECT student, NVL(SUM(credit), 0) as math_credit
		FROM PassedCoursesWithTypes
		WHERE (coursetype = 'Mathematical')
		GROUP BY student
		),

		ResearchCourseCredits AS
		(
		SELECT student, NVL(SUM(credit), 0) as research_credit
		FROM PassedCoursesWithTypes
		WHERE (coursetype = 'Research')
		GROUP BY student
		),

		SeminarCoursesRead AS
		(
		SELECT student, NVL(COUNT(code), 0) as seminar_count
		FROM PassedCoursesWithTypes
		WHERE (coursetype = 'Seminar')
		GROUP BY student
		),

		PreSummary AS
		(
		SELECT *
		FROM
		StudentTotalCredits
			NATURAL FULL OUTER JOIN
			LeftMandatory
			NATURAL FULL OUTER JOIN
			StudentBranchCredits
			NATURAL FULL OUTER JOIN
			MathCourseCredits
			NATURAL FULL OUTER JOIN
			ResearchCourseCredits
			NATURAL FULL OUTER JOIN
			SeminarCoursesRead
		),

		Qualified AS
		(
		SELECT student,
	    CASE
		WHEN seminar_count >= 1
        	AND research_credit >= 10
       		AND math_credit >= 20
        	AND branch_credit >= 10

            THEN 'Qualified'
      	    ELSE 'Not Qualified'
	    END as qualified
		FROM PreSummary
		)






		SELECT *
		FROM
			StudentTotalCredits
			NATURAL FULL OUTER JOIN
			LeftMandatory
			NATURAL FULL OUTER JOIN
			StudentBranchCredits
			NATURAL FULL OUTER JOIN
			MathCourseCredits
			NATURAL FULL OUTER JOIN
			ResearchCourseCredits
			NATURAL FULL OUTER JOIN
			SeminarCoursesRead
			NATURAL FULL OUTER JOIN
			Qualified