gistfile1.sql

CREATE VIEW PathToGraduation AS 
      WITH  TotalCredits AS (SELECT id, SUM(credits) AS totalCredits 
                        FROM PASSEDCOURSES 
                        GROUP BY id),
                        
      CoursesToTake AS (SELECT id, COUNT(*) AS CoursesToTake 
                        FROM UNREADMANDATORY 
                        GROUP BY id),
                        
      MathCredits AS (SELECT id, SUM(credits) AS MathCredits 
                        FROM (PASSEDCOURSES JOIN HASCLASSIFICATIONS 
                        ON name = course) 
                        WHERE type = 'Math' 
                        GROUP BY id),
                        
      ResearchCredits AS (SELECT id, SUM(credits) AS ResearchCredits 
                        FROM (PASSEDCOURSES JOIN HASCLASSIFICATIONS 
                        ON name = course) 
                        WHERE type = 'Research' 
                        GROUP BY id),
                        
      SeminarCourses AS (SELECT id, COUNT(*) AS SeminarCourses 
                        FROM (PASSEDCOURSES JOIN HASCLASSIFICATIONS 
                        ON name = course) 
                        WHERE type = 'Seminar' 
                        GROUP BY id)
                                              
SELECT id,  
      COALESCE(TotalCredits, 0) TotalCredits, 
      COALESCE(CoursesToTake, 0) CoursesToTake, 
      COALESCE(MathCredits, 0) MathCredits, 
      COALESCE(ResearchCredits, 0) ResearchCredits, 
      COALESCE(SeminarCourses, 0) SeminarCourses,
      CASE WHEN (CoursesToTake = 0 
            AND MathCredits >= 20
            AND ResearchCredits >= 10
            AND SeminarCourses >= 1)  THEN 'true'
            ELSE 'false' 
      END AS "MayGraduate"
            
FROM (STUDENTS NATURAL LEFT JOIN TotalCredits 
      NATURAL LEFT JOIN CoursesToTake 
      NATURAL LEFT JOIN MathCredits 
      NATURAL LEFT JOIN ResearchCredits 
      NATURAL LEFT JOIN SeminarCourses);