/*
Gestione Circolo di Pesca
-------------------------
Un circolo di pesca presso un laghetto, vanta di numerosi iscritti pescatori.
Il circolo deve tenere traccia degli iscritti, del pagamento della quota annuale,
e delle attivita' del circolo: infatti vengono periodicamente organizzate gare
di pesca per gli iscritti (solo gare interne).
I pescatori del circolo debbono vedere via internet le gare in programma,
potersi iscrivere alle gare o annullare l'iscrizione.
Una o piu' gare possono essere annullate per mancanza di iscritti / mal tempo/ etc.
Si deve poter avere sempre a disposizione le seguenti informazioni: (Query)

1 Iscritti alle gare effettuate con relativo numero di pesci pescati e peso 
del pescato in kg.
2 Vincitore di una gara, per peso / numero pesci.
3 Elenco delle gare annullate e motivazione
4 Pagamento effettuato della quota annuale
5 Scadenza della licenza di pesca dei pescatori.

Inoltre fornire le istruzioni sql per:

A Annullare l'iscrizione di un pescatore ad una gara

B annullare la gara e cancellare l'elenco degli iscritti a quella gara
*/


DROP TABLE Partecipante; --Tabella che contiene la coppia codice_pescatore e codice_gara
DROP TABLE Gara;
DROP TABLE Pescatore;

CREATE TABLE Gara(
GG_codiceEvento INTEGER primary key, -- progressivo: 1,2, .. N
GG_data 		DATE,
GG_descrizione  VARCHAR(100), 
GG_orario 		TIME,
GG_annullata 	CHAR(1) CHECK (GG_annullata in('S','N'))  -- Fa  controllo che si possa inserire solo S o solo N
);


CREATE TABLE Pescatore(
PE_codiceFiscale 	CHAR (16)    primary key,
PE_nominativo 		VARCHAR (40) not NULL,
PE_quota 			CHAR (1)     CHECK (PE_quota in('S','N')), 
PE_scadenzaLicenza 	DATE
);

CREATE TABLE Partecipante(
PA_codicePescatore 	CHAR(16) references Pescatore(PE_codiceFiscale),
PA_codiceGara 		INTEGER  references Gara(GG_codiceEvento),
PA_pesoPesci 		INTEGER,
PA_numeroPesci 		INTEGER,
primary key (PA_codicePescatore,PA_codiceGara)--Chiave primaria composta 
);

--popolo gare
INSERT into Gara VALUES(1,'5/03/2015', 'Gara di pesca in velocità', '10:30:00', 'N');
INSERT into Gara VALUES(2,'5/04/2015', 'Gara di pesca in coppie', '10:30:00', 'N');
INSERT into Gara VALUES(3,'2/05/2015', 'Gara di pesca in peso pescato', '11:30:00', 'N');

INSERT into Gara VALUES(4,'5/06/2013',  'Gara di pesca a squadre - Pioggia', '10:30:00', 'S');
INSERT into Gara VALUES(5,'1/07/2014', 'Gara di pesca per pesce piu'' grosso - Vento', '10:30:00', 'S');

--popolo pescatori
INSERT into Pescatore VALUES('PWEMVN40O9E345PJ', 'Mario Verdi', 'S', '2/01/2016');
INSERT into Pescatore VALUES('PEMFV989T5YECNML', 'Bruno Miele', 'S', '2/06/2018');
INSERT into Pescatore VALUES('XYZQFAAO9E345PJB', 'Mario Gialli','N', '2/01/2015');
INSERT into Pescatore VALUES('XYZQFAAO9E445PJB', 'Bruno Rossi', 'N', '2/06/2014');
INSERT into Pescatore VALUES('PWEMVN40O9E345BB', 'Mario Neri',  'S', '1/01/2016');
INSERT into Pescatore VALUES('PEMFV989T5YE7NML', 'Bruno Scuri', 'S', '2/06/2019');
INSERT into Pescatore VALUES('PWEMVN40O9E345PK', 'Giorgio Mei', 'N', '2/01/2017');
INSERT into Pescatore VALUES('PEMFV990T5YECNML', 'Leo Bianchi', 'S', '1/06/2018');

--popolo Partecipanti
INSERT into Partecipante VALUES('PWEMVN40O9E345PJ', 1, 5,25);
INSERT into Partecipante VALUES('PEMFV989T5YECNML', 1, 4,12);
INSERT into Partecipante VALUES('XYZQFAAO9E345PJB', 1, 10,27);

INSERT into Partecipante VALUES('PEMFV989T5YE7NML', 3, 10,27);
INSERT into Partecipante VALUES('PWEMVN40O9E345PK', 3, 11,29);
INSERT into Partecipante VALUES('PEMFV990T5YECNML', 3, 1,1);

INSERT into Partecipante VALUES('PWEMVN40O9E345PJ', 2, 5,25);
INSERT into Partecipante VALUES('PEMFV989T5YECNML', 2, 4,12);



--1
SELECT 
    gg_codiceevento,
    gg_descrizione,
    gg_data,
    pe_nominativo,
    pa_numeropesci,
    pa_pesopesci
FROM gara , pescatore, partecipante
WHERE GG_codiceEvento = PA_codiceGara AND PA_codicePescatore= PE_codiceFiscale
AND gg_annullata = 'N'
ORDER BY gg_codiceevento, pe_nominativo;

--2a

SELECT pe_nominativo, pa_pesopesci
FROM partecipante, pescatore
WHERE pa_codicePescatore = pe_codiceFiscale
AND pa_codicegara = 3
AND pa_pesopesci = (SELECT MAX(pa_pesopesci) FROM partecipante WHERE pa_codicegara = 3)

--2b

SELECT pe_nominativo, pa_numeropesci
FROM partecipante, pescatore
WHERE pa_codicePescatore = pe_codiceFiscale
AND pa_codicegara = 1
AND pa_numeropesci = (SELECT MAX(pa_numeropesci) FROM partecipante WHERE pa_codicegara = 1)


