--------------
--1. DONE
--------------

SELECT num_concelho, num_regiao
FROM venda_farmacia, instituicao natural join regiao
WHERE data_registo = current_date and inst = nome_inst
group by num_concelho, num_regiao
having sum(preco) >= all (
      SELECT sum(preco)
      FROM venda_farmacia, instituicao natural join regiao
      WHERE data_registo = current_date and inst = nome_inst 
      group by num_concelho, num_regiao);

--------------
--2. DONE
--------------

SELECT nome_reg, nome_med, MAX(count)
FROM (medico
      natural join (SELECT num_cedula, nome_reg, COUNT(num_cedula)
                    FROM (SELECT *
                          FROM (SELECT num_cedula, num_doente, data_presc AS data_consulta
                                FROM prescricao) AS presc
                          where (data_consulta>='2019-01-01' AND data_consulta<='2019-06-30')) AS presc1
                          natural join ((SELECT nome_reg, nome_inst AS nome_instituicao
                                          FROM (instituicao natural join regiao)) AS reg
                              natural join consulta)
                GROUP BY (num_cedula, nome_reg)) AS presccont)
GROUP BY (nome_reg, nome_med);

-------------
--3. DONE
-------------

SELECT m1.nome_med 
FROM medico m1
Where NOT EXISTS (
        SELECT i.nome_inst 
        FROM instituicao i natural join concelho c
        WHERE c.nome_con = 'Arouca' and i.tipo='farmacia' 
        EXCEPT 
        SELECT v2.inst 
        FROM venda_farmacia v2
          natural join prescricao_venda p1
          natural join instituicao i2
          natural join concelho c2
        WHERE p1.num_cedula = m1.num_cedula
                and c2.nome_con = 'Arouca'
                and i2.tipo = 'farmacia'
                and v2.substancia='Aspirina' 
                and extract(YEAR FROM v2.data_registo)= extract(YEAR from CURRENT_DATE)
    ) ;

-------------
--4. DONE
-------------

SELECT distinct (num_doente)
FROM ( SELECT num_doente 
       FROM analise a
       WHERE extract(MONTH from a.data_registo) = extract(MONTH from CURRENT_DATE) and 
                extract(YEAR from a.data_registo) = extract(YEAR from CURRENT_DATE)
       EXCEPT
       SELECT num_doente 
       FROM venda_farmacia v natural join prescricao_venda p
       WHERE extract(MONTH from v.data_registo) = extract(MONTH from CURRENT_DATE)        
     ) as pacientes_com_levantementos;
