-- +++++++++++++++++++++++++++++++++++++++++
-- SOLUZIONE ESERCITAZIONE 2 OBIETTIVO 2
-- +++++++++++++++++++++++++++++++++++++++++

-- SOLUZIONE OBIETTIVO 2.1
-- Funzione che verifica se il voto di laurea soddisfa il vincolo secondo il quale 
-- deve essere compreso tra 70 e 110

DROP FUNCTION IF EXISTS `VotoLaurea`;

DELIMITER $$

CREATE FUNCTION `VotoLaurea`(votoLaurea int(3)) RETURNS tinyint(1)
BEGIN
	DECLARE result bool;
	SET result = 1;
	IF votoLaurea < 70 OR votoLaurea > 110 THEN SET result = 0;
		ELSE SET result = 1;
	END IF;
	RETURN result;
END $$

DELIMITER ;

-- SOLUZIONE OBIETTIVO 2.2
-- Procedura che verifica se un piano di studio compare nella tabella prevede (vincolo di inclusione)

DROP PROCEDURE IF EXISTS proc_incl_pianodistudio_prevede;

DELIMITER $$

CREATE PROCEDURE proc_incl_pianodistudio_prevede(IN ps int(15), OUT isPresent bool)
BEGIN
SELECT count(*) INTO isPresent FROM prevede WHERE prevede.pianoDiStudio = ps;
END $$

DELIMITER ;

-- SOLUZIONE OBIETTIVO 2.3
-- Funzione che verifica se tutti gli esami previsti dal piano di studi di uno
-- studente che discute la tesi sono stati superati con profitto

DROP FUNCTION IF EXISTS PianoDiStudiCompletato;

DELIMITER $$
-- condizione che impone che il corso sia tra quelli previsti dal piano 
-- di studio dello studente che discute la relazione finale*/
-- condizione che impone che il corso non sia tra quelli superati con successo  
-- dallo studente che discute la relazione finale*/

CREATE FUNCTION PianoDiStudiCompletato(RelazioneFinale INT(15)) RETURNS bool
BEGIN
	DECLARE esamiPrevistiNonSuperati INT(10);
	SELECT count(*) INTO esamiPrevistiNonSuperati 
	FROM prevede
	WHERE prevede.pianoDiStudio=RelazioneFinale 
	AND prevede.corso IN (SELECT corso FROM prevede WHERE prevede.pianoDiStudio=RelazioneFinale) 
	AND prevede.corso NOT IN (SELECT corso FROM esame WHERE esame.studente=RelazioneFinale AND esame.voto>18);
	IF esamiPrevistiNonSuperati=0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF;
END $$

DELIMITER ;
