CREATE TABLE CONCORRENTI(
idConcorrente INT PRIMARY KEY,
nome VARCHAR(30),
cognome VARCHAR(30),
data_nascita DATE,
stato VARCHAR(30),
regione VARCHAR(30),
provincia VARCHAR(30),
nickname VARCHAR(20),
email VARCHAR(50)
);

CREATE TABLE LINGUAGGI(
idLinguaggio INT PRIMARY KEY,
nomeLinguaggio VARCHAR(20)
);

CREATE TABLE PROGRAMMI(
idProgramma INT PRIMARY KEY,
titolo VARCHAR(50),
codice TEXT,
descrizione TEXT,
compilatore VARCHAR(30),
versione VARCHAR(10),
sistemaOperativo VARCHAR(30),
dataInvio DATE,
idConcorrente INT REFERENCES CONCORRENTI(idConcorrente),
idLinguaggio INT REFERENCES LINGUAGGI(idLinguaggio) 
);


INSERT INTO CONCORRENTI VALUES
(1,'Luca','Rossi','12-03-2009','Italia','Emilia-Romagna','Bologna',
'CodeMaster','luca@gmail.com'),

(2,'Anna','Verdi','10-03-2009','Italia','Lombardia','Milano',
'JavaGirl','anna@gmail.com'),

(3,'Marco','Bianchi','06-03-2012','Italia','Lazio','Roma',
'CppKing','marco@gmail.com'),

(4,'John','Smith','02-03-2009','USA','California','Los Angeles',
'PythonBoy','john@gmail.com'),

(5,'Sara','Neri','07-03-2010','Italia','Toscana','Firenze',
'WebSara','sara@gmail.com');


INSERT INTO LINGUAGGI VALUES
(1,'C'),
(2,'C++'),
(3,'Java'),
(4,'Python'),
(5,'Pascal');


INSERT INTO PROGRAMMI VALUES
(
101,
'Calculator',
'codice sorgente...',
'Programma che effettua operazioni base',
'GCC',
'12.0',
'Linux',
'2025-06-01',
1,
1
);

INSERT INTO PROGRAMMI VALUES
(
102,
'Student Manager',
'codice sorgente...',
'Gestione studenti',
'JDK',
'17',
'Windows',
'2025-06-03',
2,
3
);

INSERT INTO PROGRAMMI VALUES
(
103,
'Sorting Algorithm',
'codice sorgente...',
'Algoritmo di ordinamento',
'Dev C++',
'5.11',
'Windows',
'2025-06-05',
3,
2
);

INSERT INTO PROGRAMMI VALUES
(
104,
'Snake Game',
'codice sorgente...',
'Gioco Snake',
'Python',
'3.12',
'Linux',
'2025-06-07',
4,
4
);

INSERT INTO PROGRAMMI VALUES
(
105,
'Website Login',
'codice sorgente...',
'Piccolo sistema di login',
'NetBeans',
'19',
'Windows',
'2025-06-09',
5,
3
);



--A) Elenco di concorrenti iscritti suddiviso per stato /regione/provincia in ordine di eta' che hanno postato un
--programma sorgente nell'ultima settimana.

SELECT stato, regione, provincia, cognome, nome, data_nascita
FROM concorrenti c, programmi p
WHERE c.idConcorrente=p.idConcorrente
AND NOW()-dataInvio > interval '7 days'
ORDER BY stato, regione, provincia, data_nascita ;


--B) Numero di programmi sorgenti postati da un certo stato, codificati in un certo linguaggio, suddivisi in
--regioni.

SELECT regione, COUNT(*) as totale
FROM concorrenti c, programmi p
WHERE c.idConcorrente=p.idConcorrente
AND stato='Italia'
AND idLinguaggio=3
GROUP BY regione
ORDER BY regione;
