• SELECT (seleciona as colunas)
• FROM (seleciona as tabelas)
• JOIN (faz a junção entre tabelas)
• USING (determina a chave primária e estrangeira)
• WHERE (estabelece condições)
• GROUP BY (agrupa por um atributo, quando há agregação)
• ORDER BY (DESC OU ASC) (ordena os resultados)
• AS (renomeia as colunas ou tabelas)

• = (igual)

• e < (maior, menor)

• = e <= (maior igual, menor igual)

• != (diferente)
• LIKE e ILIKE (parecido)
• ‘%’ (cadeia de caracteres)

• SUM (soma)
• AVG (média)
• MIN (mínimo)
• MAX (máximo)

• cv_cases (date, week, cases, new_cases, deaths, new_deaths, cod_mun)

  cod_mum referencia municipios

• municipios (cod_mun, leitos, pop, renda, leitos_sus_reg, geom, cod_uf)

  cod_uf referencia uf

• uf (cod_uf, geom)

select sum(new_deaths) 
from cv_cases;

select sum(new_cases) as casos, sum(new_deaths) as mortes
from cv_cases;

select state, sum(new_cases) as casos, sum(new_deaths) as mortes
from cv_cases
group by state
order by mortes desc;

select date, sum(new_deaths) as mortes
from cv_cases
group by date
order by date desc;

select date, sum(new_deaths) as mortes
from cv_cases
where state='RS'
group by date
order by date desc;

• Não existe a informação de população…

  cod_mun	state	week	weekd	year	date	cases	deaths	new_cases	new_deaths	id
  4300034	RS	8	7	2021	2021-02-28	72	0	4	0	409514
  4300059	RS	8	7	2021	2021-02-28	307	7	14	0	409515
  4300109	RS	8	7	2021	2021-02-28	563	4	62	0	409516
  4300208	RS	8	7	2021	2021-02-28	528	5	2	1	409517

• O que fazer?

SELECT nome_mun, date, deaths as mortes, pop as população, 
deaths*100000/pop as taxa
FROM cv_cases c JOIN municipios m
using (cod_mun);

• st_makepoint

SELECT nome_mun, date, deaths as mortes, pop as população, geometry,
deaths*100000/pop as taxa
FROM cv_cases c JOIN municipios m
using (cod_mun);

• Isto está correto?

select sum(new_deaths) as mortes, sum (pop) as população, 
sum(new_deaths)*100000/sum(pop) as taxa_mortalidade
from cv_cases join municipios using (cod_mun);

• É necessário usar as mortes cumulativas, apenas para a última data disponível

select sum(deaths) as mortes, sum (pop) as população, 
sum(deaths)*100000/sum(pop) as taxa_mortalidade
from cv_cases join municipios using (cod_mun)
where date='2021-10-31';

• Ou fazer uma subconsulta

select 
  (select sum(new_deaths) as mortes from cv_cases) as mortes, 
  (select sum (pop) as população from municipios) as pop,
  (select sum(new_deaths) as mortes from cv_cases)*100000/
  (select sum (pop) as população from municipios);