Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*DROP VIEW StudentsFollowing;
- DROP VIEW FinishedCourses;
- DROP VIEW Registrations;
- DROP VIEW CourseQueuePosition;
- DROP VIEW PathToGraduation;
- DROP VIEW UnreadMandatory;
- DROP VIEW PassedCourses;
- */
- CREATE VIEW StudentsFollowing AS
- SELECT studentName, s.nationalID, s.progName, COALESCE(branchName, 'no branch') AS branch
- FROM Students s
- LEFT JOIN AttendingBranch a
- ON s.nationalID = a.nationalID;
- CREATE VIEW FinishedCourses AS
- SELECT studentName, s.nationalID, c.code, courseName, grade, credits
- FROM Students s, Courses c, CompletedCourses cc
- WHERE c.code = cc.code AND
- s.nationalID = cc.nationalID
- ORDER BY studentName ASC;
- CREATE VIEW Registrations AS
- SELECT *, 'registered' AS status
- FROM RegisteredCourses
- UNION
- SELECT code, nationalID, 'waiting'
- FROM WaitingList;
- CREATE VIEW PassedCourses AS
- SELECT code, nationalID, grade, credits
- FROM FinishedCourses
- WHERE grade != 'U';
- CREATE VIEW UnreadMandatory AS
- SELECT a.nationalID, b.code
- FROM AttendingBranch a
- JOIN B_MandatoryCourses b
- ON a.branchName = b.branchName
- UNION
- SELECT s.nationalID, p.code
- FROM Students s
- JOIN P_MandatoryCourses p
- ON s.progName = p.progName
- EXCEPT
- SELECT pc.nationalID, pc.code
- FROM PassedCourses pc;
- CREATE VIEW PathToGraduation AS
- WITH
- totalCredits AS
- (SELECT nationalID, SUM(p.credits) as credits
- FROM PassedCourses p
- GROUP BY nationalID)
- ,
- nr_unreadMandatoryCourses AS
- (SELECT nationalID, COUNT(um.code) AS mandatoryCoursesLeft
- FROM UnreadMandatory um
- GROUP BY nationalID)
- ,
- creditsInMath AS
- (SELECT nationalID, SUM(p.credits) AS mathCredits
- FROM PassedCourses p
- LEFT JOIN HasTypes t
- ON p.code = t.code
- WHERE t.type = 'Mathematical'
- GROUP BY nationalID)
- ,
- creditsInResearch AS
- (SELECT nationalID, SUM(p.credits) AS researchCredits
- FROM PassedCourses p
- LEFT JOIN HasTypes t
- ON p.code = t.code
- WHERE t.type = 'Research'
- GROUP BY nationalID)
- ,
- nr_seminarCoursesRead AS
- (SELECT nationalID, COUNT(p.code) AS seminarsRead
- FROM PassedCourses p
- LEFT JOIN HasTypes t
- ON p.code = t.code
- WHERE t.type = 'Seminar'
- GROUP BY nationalID),
- hasPickedBranch AS
- (SELECT nationalID, branch
- FROM StudentsFollowing)
- SELECT s.nationalID,
- COALESCE(credits,0) AS totalCredits,
- COALESCE(mandatoryCoursesLeft,0) AS mandatoryCoursesLeft,
- COALESCE(mathCredits,0) AS mathCredits,
- COALESCE(researchCredits,0) AS researchCredits,
- COALESCE(seminarsRead,0) AS seminarsRead,
- CASE WHEN
- mandatoryCoursesLeft is null
- AND mathCredits >= 20
- AND researchCredits >= 10
- AND seminarsRead > 0
- AND (s.nationalID = hasPickedBranch.nationalID
- AND hasPickedBranch.branch != 'no branch')
- THEN 'Yes'
- ELSE 'No'
- END AS canGraduate
- FROM Students s
- LEFT JOIN totalCredits tc
- ON s.nationalID = tc.nationalID
- LEFT JOIN nr_unreadMandatoryCourses umc
- ON s.nationalID = umc.nationalID
- LEFT JOIN creditsInMath cim
- ON s.nationalID = cim.nationalID
- LEFT JOIN creditsInResearch cir
- ON s.nationalID = cir.nationalID
- LEFT JOIN nr_seminarCoursesRead scr
- ON s.nationalID = scr.nationalID;
- CREATE VIEW CourseQueuePosition AS
- SELECT *
- FROM WaitingList wl
- ORDER BY code, positionInQueue;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement