DekkerBass

procedures.sql entr-3

Dec 26th, 2020 (edited)
860
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --RF-001
  2. DELIMITER//
  3. CREATE OR REPLACE PROCEDURE procAddQuali (m DECIMAL (4,2), gC VARCHAR(30),
  4.                                                             wH BOOLEAN ,idSt INT , idSubject INT )
  5.     BEGIN
  6.         DECLARE idGr INT;
  7.         SET idGr=(SELECT groupId FROM ViewGroupsSubjectsStudents
  8.             WHERE idSubj=subjectId LIMIT 1);
  9.                 INSERT INTO qualification (VALUE,gradeCall,withHonours,studentId,groupId)
  10.                 VALUES (v,Gc,wH,idSt,idGr);
  11.             END //
  12. DELIMITER ;
  13.  
  14.  
  15. --RF-002
  16.  
  17. DELIMITER//
  18. CREATE OR REPLACE PROCEDURE procAppointmentsDay(dw VARCHAR(30),idT INT)
  19. BEGIN
  20. SELECT * FROM ViewTutorialsAppointments NATURAL JOIN students WHERE teacherId=idT AND dayWeek=dW;
  21.    
  22.             END //
  23. DELIMITER ;
  24.  
  25.  
  26. --RF-003
  27.  
  28.  
  29. DELIMITER//
  30. CREATE OR REPLACE PROCEDURE procAssignSubject(idT INT,idG INT,tL INT)
  31.     BEGIN
  32.         INSERT INTO teachersGroups (teachingLoad,teacherId,groupId)
  33.             VALUES (tL,idT,idG);
  34.             END //
  35. DELIMITER ;
  36.  
  37. --RF-004
  38.  
  39.  
  40. CREATE OR REPLACE ViewTeachingLoadTeacher AS
  41.     SELECT teacherId,YEAR,SUM(teachingLoad) FROM teachersGroups
  42. NATURAL JOIN groups GROUP BY teacherId HAVING YEAR=YEAR(CURDATE());
  43.  
  44. --RF-005
  45.  
  46. DELIMITER //
  47. CREATE OR REPLACE FUNCTION teacherCredits (idSub INT,acYear INT)
  48. RETURN INT
  49.     BEGIN
  50.     DECLARE idT INT ;
  51.     SET idT=(SELECT teacherId FROM ViewTgg WHERE (subjectId=idSub AND YEAR =acYear) GROUP BY teacherId
  52.                         ORDER BY SUM (teachingLoad)  DESC LIMIT 1);
  53.             END //
  54. DELIMITER ;
  55.  
  56. --RF-006
  57. DELIMITER//
  58. CREATE OR REPLACE PROCEDURE procDeleteGrades (studentDni CHAR (9))
  59. BEGIN
  60.     DECLARE id INT;
  61.     SET id= (SELECT studentId FROM students WHERE dni=studentDni);
  62.     DELETE FROM grades WHERE studentId=id;
  63. END //
  64. DELIMITER ;
  65.  
  66. --RF 0036
  67.  
  68. CREATE OR REPLACE ViewListStudents AS
  69.     SELECT firstNameSt,surnameSt,subjectId,groupId FROM students
  70. NATURAL JOIN (groupsstudents NATURAL JOIN groups ) ORDER BY firstNameSt;
  71.  
  72. --RF-008
  73. CREATE OR REPLACE ViewListStudentsMayor AS
  74.     SELECT firstNameSt,surnameSt,subjectId,groupId FROM students
  75. NATURAL JOIN (groupsstudents NATURAL JOIN groups ) WHERE accessMethod= 'Mayor' ORDER BY firstNameSt;
  76.  
  77. --RF-009
  78.  
  79. CREATE OR REPLACE ViewDegreesCourse AS
  80.     SELECT subjectName,acronym,credits,type FROM subjects
  81. NATURAL JOIN degrees WHERE degreeId=3 AND course=2;
  82.  
  83. --RF-011
  84. DELIMITER//
  85. CREATE OR REPLACE FUNCTION aVgGrade(studentId INT) RETURNS DOUBLE
  86.     BEGIN
  87.         RETURN (SELECT AVG(VALUE)
  88.                 FROM grades
  89.                 WHERE grades.studentId=studentId);
  90. END //
  91. DELIMITER ;
  92.  
  93. ---
  94.  
  95. CREATE OR REPLACE VIEW ViewGroupsTeachers AS
  96.     SELECT teacherId,groupId,subjectId FROM teacherGroups NATURAL JOIN
  97.  
  98.  
RAW Paste Data