DROP DATABASE IF EXISTS stati;

CREATE DATABASE stati;

\c stati;

CREATE TABLE CITTA (
    NomeCitta CHAR PRIMARY KEY,
    NomeStato CHAR NOT NULL,
    NAbita INTEGER
);

INSERT INTO CITTA VALUES ('A', 'X', '300');

INSERT INTO CITTA VALUES ('B', 'X', '200');

INSERT INTO CITTA VALUES ('C', 'Y', '200');

INSERT INTO CITTA VALUES ('D', 'Y', '100');

INSERT INTO CITTA VALUES ('E', 'Y', '200');

-- Consegna 1
SELECT C.NomeCitta, C.NAbita, C.NomeStato
FROM CITTA AS C
WHERE C.NAbita = ANY(SELECT MAX(NAbita) FROM CITTA AS CS WHERE CS.NomeStato=C.NomeStato GROUP BY NomeStato)
GROUP BY NomeStato, NomeCitta;

-- \i 'C://Users/pizzo/Desktop/Databases/Es3.sql'
CREATE DATABASE prova;