CREATE TABLE squadra  (
codice 	         CHAR    ( 6) primary key,
nome		 	 VARCHAR (30)
);

CREATE TABLE calciatore  (
codice 	         INTEGER    primary key,
squadra			 CHAR	 (6) REFERENCES squadra(codice),
cognome			 VARCHAR (20),
nome		 	 VARCHAR (20),
data_nascita	 DATE,
stipendio		 DECIMAL (10)
);

INSERT INTO squadra VALUES ('S00001', 'juventus FC');
INSERT INTO squadra VALUES ('S00002', 'internazionale');
INSERT INTO squadra VALUES ('S00003', 'InterMilan');

INSERT INTO calciatore VALUES (1, 'S00001', 'Cortesi', 'Geppeo', '11-3-1996', 30);
INSERT INTO calciatore VALUES (2, 'S00001', 'Gigi', 'Marca', '12-2-1995', 27);
INSERT INTO calciatore VALUES (3, 'S00001', 'Blu', 'Salvatore', '12-6-1997', 26);
INSERT INTO calciatore VALUES (4, 'S00002', 'Pierotti', 'Michelangelo', '11-6-1994', 57);
INSERT INTO calciatore VALUES (5, 'S00002', 'Mastrota', 'Giorgio', '2-10-1983', 17);
INSERT INTO calciatore VALUES (6, 'S00002', 'Viola', 'Paolo', '1-2-1980', 45);
INSERT INTO calciatore VALUES (7, 'S00003', 'Rossi', 'Tommaso', '5-9-1993', 21);
INSERT INTO calciatore VALUES (8, 'S00003', 'Verdi', 'Bruno', '3-8-1992', 89);
INSERT INTO calciatore VALUES (9, 'S00003', 'Capaldi', 'Pietro', '4-9-1985', 56);

--A) elenco calciatori, data una squadra
--SELECT cognome, nome
--    FROM calciatore
--    WHERE squadra = 'S00001'
--    ORDER BY cognome;

--B) Età media dei calciatori di una squadra
--SELECT AVG('12-01-2026' - data_nascita) / 365 AS "eta_media"
--    FROM calciatore
--    WHERE squadra = 'S00001';

--SELECT AVG(AGE(CURRENT_TIMESTAMP, data_nascita)) AS "eta_media"
--    FROM calciatore
--    WHERE squadra = 'S00001';

--C) 1)Tutti i calciatori, con la loro squadra
--SELECT squadra.nome, cognome, calciatore.nome
--    FROM calciatore, squadra
--    WHERE calciatore.squadra = squadra.codice
--    ORDER BY squadra.nome, cognome;

--C) 2)Tutti i calciatori, con la loro squadra di appartenenza, che hanno uno stipendio mensile superiore a una certa cifra
--SELECT squadra.nome,  stipendio / 12 AS "stipendio_mensile", cognome, calciatore.nome
--    FROM calciatore, squadra
--    WHERE calciatore.squadra = squadra.codice AND stipendio / 12 > 3
--    ORDER BY squadra.nome, stipendio_mensile, cognome;

--D) Le squadre che, in un anno spendono per i loro calciatori (per gli stipendi) piu' di una certa cifra
--SELECT squadra.nome, SUM(stipendio) AS "spesa_totale"
--    FROM squadra, calciatore
--    WHERE squadra.codice = calciatore.squadra
--    GROUP BY squadra.nome
--    HAVING SUM(stipendio) > 100
--    ORDER BY squadra.nome;

--E) Il calciatore piu’ giovane

-- Funziona, ma potrebbe perdere dei dati!
--SELECT data_nascita, cognome, nome
--    FROM calciatore
--    ORDER BY data_nascita DESC
--    LIMIT 1;

--SELECT data_nascita, cognome, nome
--    FROM calciatore
--    WHERE data_nascita = (SELECT MAX(data_nascita) FROM calciatore);
