/*EX1*/
DELIMITER //
CREATE TRIGGER EXERCICI1INSERT AFTER INSERT ON NOTES FOR EACH ROW

	BEGIN
		IF (select count(*) from notes_alumne where dni = new.dni)=0 then

			IF ((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25) / (select count(*) from notes where dni=new.dni)>=5 then

			INSERT INTO NOTES_ALUMNE VALUES (new.dni, (((select sum(nota) from notes where codi like 'E%' and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25)) / (select count(*) from notes where dni=new.dni), 'A');

			else

			INSERT INTO NOTES_ALUMNE VALUES (new.dni, (((select sum(nota) from notes where codi like 'E%' and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25) / (select count(*) from notes where dni=new.dni)), 'S');

			end if;
        else

		IF ((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25) / (select count(*) from notes where dni=new.dni)>=5 then

			update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25) / (select count(*) from notes where dni=new.dni)) where dni=new.dni;

			update notes_alumne set codi_nota = 'A' where dni=new.dni;

        else

			update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25) / (select count(*) from notes where dni=new.dni)) where dni=new.dni;

			update notes_alumne set codi_nota = 'S' where dni=new.dni;

			end if;

        end if;
    END //

DELIMITER ;