Created
February 24, 2015 12:14
-
-
Save m0ar/2a028ef75ae3345eeee4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment