Advertisement
Guest User

Untitled

a guest
Dec 12th, 2017
78
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.16 KB | None | 0 0
  1. /*DROP VIEW StudentsFollowing;
  2. DROP VIEW FinishedCourses;
  3. DROP VIEW Registrations;
  4. DROP VIEW CourseQueuePosition;
  5. DROP VIEW PathToGraduation;
  6. DROP VIEW UnreadMandatory;
  7. DROP VIEW PassedCourses;
  8. */
  9. CREATE VIEW StudentsFollowing AS
  10. SELECT studentName, s.nationalID, s.progName, COALESCE(branchName, 'no branch') AS branch
  11. FROM Students s
  12. LEFT JOIN AttendingBranch a
  13. ON s.nationalID = a.nationalID;
  14.  
  15. CREATE VIEW FinishedCourses AS
  16. SELECT studentName, s.nationalID, c.code, courseName, grade, credits
  17. FROM Students s, Courses c, CompletedCourses cc
  18. WHERE c.code = cc.code AND
  19. s.nationalID = cc.nationalID
  20. ORDER BY studentName ASC;
  21.  
  22. CREATE VIEW Registrations AS
  23. SELECT *, 'registered' AS status
  24. FROM RegisteredCourses
  25. UNION
  26. SELECT code, nationalID, 'waiting'
  27. FROM WaitingList;
  28.  
  29. CREATE VIEW PassedCourses AS
  30. SELECT code, nationalID, grade, credits
  31. FROM FinishedCourses
  32. WHERE grade != 'U';
  33.  
  34. CREATE VIEW UnreadMandatory AS
  35. SELECT a.nationalID, b.code
  36. FROM AttendingBranch a
  37. JOIN B_MandatoryCourses b
  38. ON a.branchName = b.branchName
  39.  
  40. UNION
  41.  
  42. SELECT s.nationalID, p.code
  43. FROM Students s
  44. JOIN P_MandatoryCourses p
  45. ON s.progName = p.progName
  46.  
  47. EXCEPT
  48.  
  49. SELECT pc.nationalID, pc.code
  50. FROM PassedCourses pc;
  51.  
  52. CREATE VIEW PathToGraduation AS
  53. WITH
  54. totalCredits AS
  55. (SELECT nationalID, SUM(p.credits) as credits
  56. FROM PassedCourses p
  57. GROUP BY nationalID)
  58. ,
  59. nr_unreadMandatoryCourses AS
  60. (SELECT nationalID, COUNT(um.code) AS mandatoryCoursesLeft
  61. FROM UnreadMandatory um
  62. GROUP BY nationalID)
  63. ,
  64. creditsInMath AS
  65. (SELECT nationalID, SUM(p.credits) AS mathCredits
  66. FROM PassedCourses p
  67. LEFT JOIN HasTypes t
  68. ON p.code = t.code
  69. WHERE t.type = 'Mathematical'
  70. GROUP BY nationalID)
  71. ,
  72. creditsInResearch AS
  73. (SELECT nationalID, SUM(p.credits) AS researchCredits
  74. FROM PassedCourses p
  75. LEFT JOIN HasTypes t
  76. ON p.code = t.code
  77. WHERE t.type = 'Research'
  78. GROUP BY nationalID)
  79. ,
  80. nr_seminarCoursesRead AS
  81. (SELECT nationalID, COUNT(p.code) AS seminarsRead
  82. FROM PassedCourses p
  83. LEFT JOIN HasTypes t
  84. ON p.code = t.code
  85. WHERE t.type = 'Seminar'
  86. GROUP BY nationalID),
  87. hasPickedBranch AS
  88. (SELECT nationalID, branch
  89. FROM StudentsFollowing)
  90.  
  91.  
  92. SELECT s.nationalID,
  93. COALESCE(credits,0) AS totalCredits,
  94. COALESCE(mandatoryCoursesLeft,0) AS mandatoryCoursesLeft,
  95. COALESCE(mathCredits,0) AS mathCredits,
  96. COALESCE(researchCredits,0) AS researchCredits,
  97. COALESCE(seminarsRead,0) AS seminarsRead,
  98. CASE WHEN
  99. mandatoryCoursesLeft is null
  100. AND mathCredits >= 20
  101. AND researchCredits >= 10
  102. AND seminarsRead > 0
  103. AND (s.nationalID = hasPickedBranch.nationalID
  104. AND hasPickedBranch.branch != 'no branch')
  105. THEN 'Yes'
  106. ELSE 'No'
  107. END AS canGraduate
  108.  
  109. FROM Students s
  110. LEFT JOIN totalCredits tc
  111. ON s.nationalID = tc.nationalID
  112. LEFT JOIN nr_unreadMandatoryCourses umc
  113. ON s.nationalID = umc.nationalID
  114. LEFT JOIN creditsInMath cim
  115. ON s.nationalID = cim.nationalID
  116. LEFT JOIN creditsInResearch cir
  117. ON s.nationalID = cir.nationalID
  118. LEFT JOIN nr_seminarCoursesRead scr
  119. ON s.nationalID = scr.nationalID;
  120.  
  121. CREATE VIEW CourseQueuePosition AS
  122. SELECT *
  123. FROM WaitingList wl
  124. ORDER BY code, positionInQueue;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement