Exercício adicional 29/07 - Medição de temperatura

--1 - Retorne o nome de todos os funcionários ao lado do
--nome da cidade de sua residência (obs. alguns
--funcionários não informam sua cidade de residência)
SELECT
funcionario.nome_funcionario,
cidade.nome_cidade
FROM funcionario
LEFT OUTER JOIN cidade ON
funcionario.cod_cidade_residencia = cidade.cod_cidade

--2 - Retorne o nome de todos os pontos de medição ao lado
--do nome de sua cidade
SELECT
local_medicao.nome_local_medicao,
cidade.nome_cidade
FROM local_medicao
INNER JOIN cidade ON
local_medicao.cod_cidade = cidade.cod_cidade

--3 - Retorne a o nome do local de medição, horário de
--medição e a temperatura medida em todos os pontos de
--medição localizados nos municípios de Florianópolis e
--Palhoça no dia 28/07/2013 entre as 12:00 e 18:00.
SELECT
local_medicao.nome_local_medicao,
medicao.horario_medicao,
medicao.valor_temperatura
FROM medicao
INNER JOIN local_medicao ON
medicao.cod_local_medicao = local_medicao.cod_local_medicao
INNER JOIN cidade ON
local_medicao.cod_cidade = cidade.cod_cidade
WHERE
cidade.nome_cidade in ('Florianópolis', 'Palhoça')
AND medicao.horario_medicao >= '2013-07-28 12:00:00'
AND medicao.horario_medicao <= '2013-07-28 18:00:00'
--16 registros
--4 - Retorne o valor da menor temperatura medida no estado
SELECT
MIN(valor_temperatura) as menor_temperatura
FROM medicao

--curiosidade: select para retornar onde esta temperatura
--foi medida
SELECT
*
FROM medicao
INNER JOIN local_medicao ON
medicao.cod_local_medicao = local_medicao.cod_local_medicao
WHERE valor_temperatura = (
SELECT
MIN(valor_temperatura) as menor_temperatura
FROM medicao
)


--5 - Retorne a média de todas as temperaturas registradas
--em Florianópolis no dia 28/07/2013
SELECT
AVG(medicao.valor_temperatura)
FROM medicao
INNER JOIN local_medicao ON
medicao.cod_local_medicao = local_medicao.cod_local_medicao
INNER JOIN cidade ON
local_medicao.cod_cidade = cidade.cod_cidade
WHERE
cidade.nome_cidade = 'Florianópolis'
AND medicao.horario_medicao >= '2013-07-28 00:00:00'
AND medicao.horario_medicao < '2013-07-29 00:00:00'


--6 - Retorne o nome de todos os funcionários que são
--responsáveis por um ponto de medição em uma cidade
--diferente da sua residência
SELECT DISTINCT
funcionario.nome_funcionario
FROM funcionario
INNER JOIN local_medicao ON
local_medicao.cod_funcionario_responsavel = funcionario.cod_funcionario
WHERE
local_medicao.cod_cidade <> funcionario.cod_cidade_residencia

--7 - Retorne a maior temperatura registrada em São Joaquim
SELECT
MAX(valor_temperatura)
FROM medicao
INNER JOIN local_medicao ON
medicao.cod_local_medicao = local_medicao.cod_local_medicao
INNER JOIN cidade ON
local_medicao.cod_cidade = cidade.cod_cidade
WHERE
cidade.nome_cidade = 'São Joaquim'

-- 8 - Retorne a quantidade de medições realizadas em
--cada ponto de medição
select
local_medicao.cod_local_medicao,
local_medicao.nome_local_medicao,
COUNT(*)
FROM medicao
INNER JOIN local_medicao ON
medicao.cod_local_medicao = local_medicao.cod_local_medicao
GROUP BY
local_medicao.cod_local_medicao,
local_medicao.nome_local_medicao

--para considerar locais sem medição:
select
local_medicao.cod_local_medicao,
local_medicao.nome_local_medicao,
COUNT(medicao.cod_medicao)
FROM local_medicao
LEFT OUTER JOIN medicao ON
medicao.cod_local_medicao = local_medicao.cod_local_medicao
GROUP BY
local_medicao.cod_local_medicao,
local_medicao.nome_local_medicao

--9 - Parabéns! Você foi contratado para trabalhar com as
--medições de temperatura. Para isso, você será o
--responsável por um novo ponto de medição localizado
--em uma destas cidades:

Joinville
Blumenau
São José
Criciúma
Chapecó
Itajaí
Lages
Jaraguá do Sul
Palhoça
Balneário Camboriú
Brusque
Tubarão
São Bento do Sul
Caçador
Concórdia
Camboriú
Navegantes
Rio do Sul
Araranguá
Biguaçu
Gaspar
Indaial
Mafra
Canoinhas
Içara
Itapema
Videira
Xanxerê
São Francisco do Sul

--9.1 Cadastre a cidade onde você deseja trabalhar.
--Verifique antes se ela já não existe no banco de dados.
--Caso exista, escolha outra cidade.
INSERT INTO cidade(nome_cidade)
VALUES('Indaial');

SELECT * FROM cidade
--9.2 Cadastre a você mesmo como novo funcionário
INSERT INTO funcionario(nome_funcionario, cod_cidade_residencia)
VALUES('Carlos West', 1);

--9.3 Cadastre um ponto de medição localizado na cidade
--onde você vai trabalhar. Como você será o responsável,
--você mesmo pode escolher o nome do ponto!

INSERT INTO local_medicao (nome_local_medicao, cod_cidade,
cod_funcionario_responsavel, valor_latitude, valor_longitude)
VALUES('Centro de Indaial', 5, 5, 28.1234567, 48.7654321);

--9.4 Cadastre as medições de temperatura que você fez hoje
--(pode inventar os valores)

INSERT INTO medicao(horario_medicao, cod_local_medicao,
valor_temperatura)
VALUES ('2013-07-30 16:30:00', 7, 20);

select * from local_medicao

select * from medicao where cod_local_medicao in (5,6)

insert into medicao(horario_medicao, cod_local_medicao, valor_temperatura)
values ('2013-07-28 15:00:00', 6, 10);