home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
ftp.disi.unige.it
/
2015-02-11.ftp.disi.unige.it.tar
/
ftp.disi.unige.it
/
pub
/
.person
/
MesitiM
/
teach
/
sol7.txt
< prev
next >
Wrap
Text File
|
2003-05-05
|
9KB
|
342 lines
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