ESERCIZIO 1
===========



IF (SELECT AVG(Stipendio) FROM Professori 
    WHERE id IN (SELECT Professore FROM Corsi WHERE Attivato = 1)) < 15000

	UPDATE Professori
	SET Stipendio = Stipendio *1.1
	WHERE Id IN (SELECT Professore FROM Corsi WHERE Attivato = 1)

ELSE

	UPDATE Professori
	SET Stipendio = Stipendio *1.05
	WHERE Id IN (SELECT Professore FROM Corsi WHERE Attivato = 1)
	
SELECT Cognome, Nome, Stipendio FROM Professori 
WHERE id IN (SELECT Professore FROM Corsi WHERE Attivato = 1)
ORDER BY Stipendio



ESERCIZIO 2
===========

DECLARE @Cognome VARCHAR(20)
DECLARE @nome VARCHAR(20)
DECLARE @Stipendio NUMERIC

IF (SELECT AVG(Stipendio) FROM Professori 
    WHERE id IN (SELECT Professore FROM Corsi WHERE Attivato = 1)) < 15000

	UPDATE Professori
	SET Stipendio = Stipendio *1.1
	WHERE Id IN (SELECT Professore FROM Corsi WHERE Attivato = 1)

ELSE

	UPDATE Professori
	SET Stipendio = Stipendio *1.05
	WHERE Id IN (SELECT Professore FROM Corsi WHERE Attivato = 1)
	
DECLARE c CURSOR FOR 	SELECT Cognome, Nome, Stipendio FROM Professori 
			WHERE id IN (SELECT Professore 
			             FROM Corsi 
				     WHERE Attivato = 1)
			ORDER BY Stipendio

OPEN c
FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio
WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT 'Cognome: ' + @Cognome + ' Nome: ' + @Nome + 
		      ' Stipendio: ' + CAST(@Stipendio AS VARCHAR(20))
		FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio
	END
CLOSE c
DEALLOCATE c


ESERCIZIO 3
===========


DECLARE @Max NUMERIC
DECLARE @Nome VARCHAR(20)
DECLARE @Cognome VARCHAR(20)
DECLARE @Stipendio NUMERIC
DECLARE @Scarto NUMERIC

SET @Max = (SELECT MAX(Stipendio) FROM Professori 
            WHERE id IN (SELECT Professore 
	                 FROM Corsi 
			 GROUP BY Professore 
			 HAVING COUNT(*) = 1) OR
		id NOT IN (SELECT Professore FROM Corsi))

IF @Max > 15000
	UPDATE Professori
	SET Stipendio = Stipendio * 0.85
	WHERE Stipendio = @Max
ELSE
	UPDATE Professori
	SET Stipendio = Stipendio * 0.95
	WHERE Stipendio = @Max

DECLARE c CURSOR FOR 	SELECT Cognome, Nome, Stipendio, @Max - Stipendio 
			FROM Professori ORDER BY Stipendio DESC

OPEN c
FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT 'Cognome: ' + @Cognome + ' Nome: ' + @Nome + 
		      ' Stipendio: ' + CAST(@Stipendio AS VARCHAR(10)) + 
		      ' Scarto: ' + CAST(@Scarto AS VARCHAR(10))
		FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
	END
CLOSE c
DEALLOCATE c

 
ESERCIZIO 4
===========

CREATE PROCEDURE AggiornaStip1
AS
DECLARE @Max NUMERIC
DECLARE @Nome VARCHAR(20)
DECLARE @Cognome VARCHAR(20)
DECLARE @Stipendio NUMERIC
DECLARE @Scarto NUMERIC

SET @Max = (SELECT MAX(Stipendio) FROM Professori 
            WHERE id IN (SELECT Professore 
	                 FROM Corsi 
			 GROUP BY Professore 
			 HAVING COUNT(*) = 1) OR
		   id NOT IN (SELECT Professore FROM Corsi))

IF @Max > 15000
	UPDATE Professori
	SET Stipendio = Stipendio * 0.85
	WHERE Stipendio = @Max
ELSE
	UPDATE Professori
	SET Stipendio = Stipendio * 0.95
	WHERE Stipendio = @Max

DECLARE c CURSOR FOR 	SELECT Cognome, Nome, Stipendio, @Max - Stipendio 
			FROM Professori ORDER BY Stipendio DESC

OPEN c
FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT 'Cognome: ' + @Cognome + ' Nome: ' + @Nome + 
		      ' Stipendio: ' + CAST(@Stipendio AS VARCHAR(10)) + 
		      ' Scarto: ' + CAST(@Scarto AS VARCHAR(10))
		FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
	END
CLOSE c
DEALLOCATE c


Esecuzione
----------

EXEC AggiornaStip1

ESERCIZIO 5 a)
==============

CREATE PROCEDURE AggiornaStip2 @Perc1 NUMERIC, @Perc2 NUMERIC
AS
DECLARE @Max NUMERIC
DECLARE @Nome VARCHAR(20)
DECLARE @Cognome VARCHAR(20)
DECLARE @Stipendio NUMERIC
DECLARE @Scarto NUMERIC

IF @Perc1 >= 0 AND @Perc1 <= 100 AND  @Perc2 >= 0 AND @Perc2 <= 100
	BEGIN
	   PRINT 'Parametri corretti'
	   SET @Max = (SELECT MAX(Stipendio) FROM Professori 
                    WHERE id IN (SELECT Professore 
			         FROM Corsi 
				 GROUP BY Professore 
				 HAVING COUNT(*) = 1) OR
			  id NOT IN (SELECT Professore FROM Corsi))

	    IF @Max > 15000
		UPDATE Professori
		SET Stipendio = Stipendio * (1 - @Perc1/100)
		WHERE Stipendio = @Max
	    ELSE
		UPDATE Professori
		SET Stipendio = Stipendio * (1 - @Perc2/100)
		WHERE Stipendio = @Max

	    DECLARE c CURSOR FOR SELECT Cognome, Nome, Stipendio, @Max - Stipendio 
				 FROM Professori ORDER BY Stipendio DESC

	    OPEN c
	    FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
	    WHILE (@@FETCH_STATUS = 0)
		BEGIN
		   PRINT 'Cognome: ' + @Cognome + ' Nome: ' + @Nome + 
		         ' Stipendio: ' +  CAST(@Stipendio AS VARCHAR(10)) + 
			 ' Scarto: ' + CAST(@Scarto AS VARCHAR(10))
		   FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
		END
	    CLOSE c
	    DEALLOCATE c
	END
ELSE
	PRINT 'Parametri non corretti'

Esecuzione
----------

EXEC AggiornaStip2 20,40



ESERCIZIO 5 b)
==============

CREATE PROCEDURE CambiaDenCorso @Den_old VARCHAR(20), @Den_new VARCHAR(20)
AS
IF NOT EXISTS (SELECT * FROM Corsi WHERE Denominazione = @Den_old) AND 
               @Den_old NOT IN (SELECT * FROM ErroriCorsi)
	INSERT INTO ErroriCorsi
	VALUES (@Den_old)
ELSE
	UPDATE Corsi
	SET Denominazione = @Den_new
	WHERE Denominazione = @Den_old

Esecuzione
----------

EXEC CambiaDenCorso 'Fisica 1', 'Fisica'



ESERCIZIO 6 a)
==============


CREATE PROCEDURE AggiornaStip3 @Perc1 NUMERIC, @Perc2 NUMERIC, @Result NUMERIC OUTPUT
AS

DECLARE @Nome VARCHAR(20)
DECLARE @Cognome VARCHAR(20)
DECLARE @Stipendio NUMERIC
DECLARE @Scarto NUMERIC

IF @Perc1 >= 0 AND @Perc1 <= 100 AND  @Perc2 >= 0 AND @Perc2 <= 100
	BEGIN
		PRINT 'Parametri corretti'
		SET @Result = (SELECT MAX(Stipendio) FROM Professori 
            		    WHERE id IN (SELECT Professore FROM Corsi 
			                 GROUP BY Professore 
					 HAVING COUNT(*) = 1) OR
				id NOT IN (SELECT Professore FROM Corsi))


		IF @Result > 15000
			UPDATE Professori
			SET Stipendio = Stipendio * (1 - @Perc1/100)
			WHERE Stipendio = @Result
		ELSE
			UPDATE Professori
			SET Stipendio = Stipendio * (1 - @Perc2/100)
			WHERE Stipendio = @Result

		DECLARE c CURSOR FOR 	SELECT Cognome, Nome, Stipendio, @Result - Stipendio 
					FROM Professori ORDER BY Stipendio DESC

		OPEN c
		FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
		WHILE (@@FETCH_STATUS = 0)
		  BEGIN
			PRINT 'Cognome: ' + @Cognome + ' Nome: ' + @Nome + 
			      ' Stipendio: ' +  CAST(@Stipendio AS VARCHAR(10)) + 
			      ' Scarto: ' + CAST(@Scarto AS VARCHAR(10))
			FETCH NEXT FROM c INTO @Cognome, @Nome, @Stipendio, @Scarto
		  END
		CLOSE c
		DEALLOCATE c
	END
ELSE
	PRINT 'Parametri non corretti'

Esecuzione
----------

DECLARE @My_Res NUMERIC
EXEC AggiornaStip3 20,40, @Result = @My_Res OUTPUT
PRINT 'Stipendio Massimo: ' + CAST(@My_Res AS VARCHAR(10))



ESERCIZIO 6 b)
==============

CREATE PROCEDURE ListaLaureati @Data Datetime, @Num INTEGER OUTPUT
AS

DECLARE @Nome VARCHAR(20)
DECLARE @Cognome VARCHAR(20)
DECLARE @DataLaurea Datetime

DECLARE c CURSOR FOR 	SELECT Nome, Cognome, DataLaurea
			FROM Studenti, Laureati
			WHERE Matricola = MatricolaS AND DataLaurea > @Data
			ORDER BY DataLaurea
OPEN c
FETCH NEXT FROM c INTO @Nome, @Cognome, @DataLaurea
WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT  'Nome: ' + @Nome + ' Cognome: ' + @Cognome + ' Data Laurea: '  + 
		        CAST(@DataLaurea AS VARCHAR(10))
		FETCH NEXT FROM c INTO @Nome, @Cognome, @DataLaurea
	END

CLOSE C
DEALLOCATE C
SET @Num = (SELECT COUNT(*) 
			FROM Studenti, Laureati
			WHERE Matricola = MatricolaS AND DataLaurea > @Data)

Esecuzione
-----------
DECLARE @My_Num NUMERIC
EXEC ListaLaureati '5/22/99', @Num = @My_Num OUTPUT
PRINT 'Numero di laureati: ' + CAST(@My_Num AS VARCHAR(10))


ESERCIZIO 7
===========

CREATE PROCEDURE InserisciEsame1 @Matr VARCHAR, @Corso VARCHAR, @Data datetime, @Voto INTEGER
AS
INSERT INTO Esami
VALUES (@Matr, @Corso, @Data, @Voto)
IF @@ERROR <> 0
	PRINT 'Errore generato: ' + @@ERROR

ESERCIZIO 8
===========
CREATE PROCEDURE InserisciEsame2 @Matr1 VARCHAR, @Corso1 VARCHAR, @Voto1 INTEGER
AS
declare @d datetime
set @d = GETDATE()
EXEC InserisciEsame1 @Matr1, @Corso1, @d, @Voto1