Create table Noleggi
(
Velista varchar(5) Foreign key references velisti on delete no action on update cascade,
Barca varchar(6) Foreign key references barche on delete cascade on update cascade,
Data datetime,
Ruolo varchar(10) check (Ruolo in ('prodiere','timoniere','tattico','mozzo')) default 'mozzo',
Importo decimal(6,2) not null,
primary key (Velista,Barca,Data),
constraint PrezzoRuolo check (importo>=0) 
)

Insert into Noleggi values('ve005', 'ge2244','14/07/2003', 'Tattico',20);
Insert into Noleggi values('ve001', 'ge2244','14/07/2003', 'Tattico',20);
Insert into Noleggi values('ve002', 'ge2244','14/07/2003', 'Timoniere',50);
Insert into Noleggi values('ve010', 'ge2244','14/07/2003', 'Prodiere',50);
Insert into Noleggi values('ve007', 'ge2244','14/07/2003', 'Mozzo',20);
Insert into Noleggi values('ve005', 'ge2244','20/07/2003', 'Tattico',20);
Insert into Noleggi values('ve001', 'ge2244','20/07/2003', 'Tattico',20);
Insert into Noleggi values('ve002', 'ge2244','20/07/2003', 'Timoniere',50);
Insert into Noleggi values('ve001', 'ge1666','12/06/2003', 'Mozzo',20);
Insert into Noleggi values('ve001', 'pi0050','07/11/2002', 'Prodiere',50);
Insert into Noleggi values('ve004', 'pi0050','06/09/2002', 'Mozzo',20);
Insert into Noleggi values('ve003', 'pi0050','06/09/2002', 'Tattico',20);
Insert into Noleggi values('ve008', 'pi0050','06/09/2002', 'Timoniere',50);



create procedure NoleggioBarca @barca varchar(6) as
DECLARE @data datetime
DECLARE @Cognome VARCHAR(20)
DECLARE @nome VARCHAR(20)
DECLARE @importo DECIMAL(6,2)

if (@barca IN (select Noleggi.barca from Noleggi, Velisti 
            where Noleggi.Velista=Velisti.codice and velisti.brevetto=1))
begin     
     DECLARE c CURSOR FOR select data,nome,cognome,importo 
	                   from Velisti, Noleggi
	                   where Noleggi.Velista=Velisti.codice and Noleggi.barca=@barca
                           order by Noleggi.data 
      OPEN c
      FETCH NEXT FROM c INTO @data, @Cognome, @Nome, @importo
      WHILE (@@FETCH_STATUS = 0)
	BEGIN
	  PRINT 'Data: ' + CAST(@data AS VARCHAR(10)) + ' Cognome: ' + @Cognome + ' Nome: ' + @Nome + ' importo: ' + CAST(@importo AS VARCHAR(8))
	  FETCH NEXT FROM c INTO @Data, @Cognome, @Nome, @importo
	END
      CLOSE c
      DEALLOCATE c

      update Velisti 
      set bonus = bonus * 1.05 
      where eta>=18 and brevetto=1 and codice IN  (select velista 
                                                   from Noleggi 
                                                   where barca=@barca)
end
else 
    delete from barche where targa=@barca


exec NoleggioBarca @barca='pi0050'