procedures.sql entr-3

--RF-001
DELIMITER//
CREATE OR REPLACE PROCEDURE procAddQuali (m DECIMAL (4,2), gC VARCHAR(30),
                                                            wH BOOLEAN ,idSt INT , idSubject INT )
    BEGIN
        DECLARE idGr INT;
        SET idGr=(SELECT groupId FROM ViewGroupsSubjectsStudents
            WHERE idSubj=subjectId LIMIT 1);
                INSERT INTO qualification (VALUE,gradeCall,withHonours,studentId,groupId)
                VALUES (v,Gc,wH,idSt,idGr);
            END //
DELIMITER ;


--RF-002

DELIMITER//
CREATE OR REPLACE PROCEDURE procAppointmentsDay(dw VARCHAR(30),idT INT)
BEGIN
SELECT * FROM ViewTutorialsAppointments NATURAL JOIN students WHERE teacherId=idT AND dayWeek=dW;

            END //
DELIMITER ;


--RF-003


DELIMITER//
CREATE OR REPLACE PROCEDURE procAssignSubject(idT INT,idG INT,tL INT)
    BEGIN
        INSERT INTO teachersGroups (teachingLoad,teacherId,groupId)
            VALUES (tL,idT,idG);
            END //
DELIMITER ;

--RF-004


CREATE OR REPLACE ViewTeachingLoadTeacher AS
    SELECT teacherId,YEAR,SUM(teachingLoad) FROM teachersGroups
NATURAL JOIN groups GROUP BY teacherId HAVING YEAR=YEAR(CURDATE());

--RF-005

DELIMITER //
CREATE OR REPLACE FUNCTION teacherCredits (idSub INT,acYear INT)
RETURN INT
    BEGIN
    DECLARE idT INT ;
    SET idT=(SELECT teacherId FROM ViewTgg WHERE (subjectId=idSub AND YEAR =acYear) GROUP BY teacherId
                        ORDER BY SUM (teachingLoad)  DESC LIMIT 1);
            END //
DELIMITER ;

--RF-006
DELIMITER//
CREATE OR REPLACE PROCEDURE procDeleteGrades (studentDni CHAR (9))
BEGIN
    DECLARE id INT;
    SET id= (SELECT studentId FROM students WHERE dni=studentDni);
    DELETE FROM grades WHERE studentId=id;
END //
DELIMITER ;

--RF 0036

CREATE OR REPLACE ViewListStudents AS
    SELECT firstNameSt,surnameSt,subjectId,groupId FROM students
NATURAL JOIN (groupsstudents NATURAL JOIN groups ) ORDER BY firstNameSt;

--RF-008
CREATE OR REPLACE ViewListStudentsMayor AS
    SELECT firstNameSt,surnameSt,subjectId,groupId FROM students
NATURAL JOIN (groupsstudents NATURAL JOIN groups ) WHERE accessMethod= 'Mayor' ORDER BY firstNameSt;

--RF-009

CREATE OR REPLACE ViewDegreesCourse AS
    SELECT subjectName,acronym,credits,type FROM subjects
NATURAL JOIN degrees WHERE degreeId=3 AND course=2;

--RF-011
DELIMITER//
CREATE OR REPLACE FUNCTION aVgGrade(studentId INT) RETURNS DOUBLE
    BEGIN
        RETURN (SELECT AVG(VALUE)
                FROM grades
                WHERE grades.studentId=studentId);
END //
DELIMITER ;

---

CREATE OR REPLACE VIEW ViewGroupsTeachers AS
    SELECT teacherId,groupId,subjectId FROM teacherGroups NATURAL JOIN