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

Este exercício deve ser rodado na base de dados chamada 'temperatura'.
Para quem quiser fazer este exercício em casa, é necessário antes rodar o seguinte script:

Código:


CREATE TABLE cidade (
  cod_cidade   SERIAL NOT NULL PRIMARY KEY,
  nome_cidade   VARCHAR(100) NOT NULL
);

CREATE TABLE funcionario (
  cod_funcionario   SERIAL NOT NULL PRIMARY KEY,
  nome_funcionario   VARCHAR(100) NOT NULL,
  cod_cidade_residencia   INTEGER REFERENCES cidade(cod_cidade)
);


CREATE TABLE local_medicao (
  cod_local_medicao   SERIAL NOT NULL PRIMARY KEY,
  nome_local_medicao   VARCHAR(100) NOT NULL,
  cod_cidade      INTEGER NOT NULL REFERENCES cidade(cod_cidade),
  cod_funcionario_responsavel   INTEGER NOT NULL REFERENCES funcionario(cod_funcionario),
  valor_latitude   NUMERIC(10,7) NOT NULL,
  valor_longitude   NUMERIC(10,7) NOT NULL
);

CREATE TABLE medicao (
  cod_medicao   SERIAL NOT NULL PRIMARY KEY,
  horario_medicao   TIMESTAMP,
  cod_local_medicao   INTEGER NOT NULL REFERENCES local_medicao(cod_local_medicao),
  valor_temperatura   NUMERIC(5, 2)
);

INSERT INTO cidade (nome_cidade)
VALUES ('Florianópolis');

INSERT INTO cidade (nome_cidade)
VALUES ('Palhoça');

INSERT INTO cidade (nome_cidade)
VALUES ('São Joaquim');

INSERT INTO cidade (nome_cidade)
VALUES ('Urubici');

INSERT INTO funcionario (nome_funcionario, cod_cidade_residencia)
VALUES('Charles Wust', 1);

INSERT INTO funcionario (nome_funcionario, cod_cidade_residencia)
VALUES('José da Silva', 1);

INSERT INTO funcionario (nome_funcionario, cod_cidade_residencia)
VALUES('Marcelo Schmidt', NULL);

INSERT INTO funcionario (nome_funcionario, cod_cidade_residencia)
VALUES('Joaquim Kretzer', 4);

INSERT INTO local_medicao(nome_local_medicao, cod_cidade, cod_funcionario_responsavel, valor_latitude, valor_longitude)
VALUES ('Itacorubi', 1, 1, 27.5966667, 48.5463889);

INSERT INTO local_medicao(nome_local_medicao, cod_cidade, cod_funcionario_responsavel, valor_latitude, valor_longitude)
VALUES ('Ponte Hercílio Luz', 1, 1, 27.5966778, 48.5463895);

INSERT INTO local_medicao(nome_local_medicao, cod_cidade, cod_funcionario_responsavel, valor_latitude, valor_longitude)
VALUES ('Morro do Cambirela', 2, 1, 27.6452778, 48.6677778);

INSERT INTO local_medicao(nome_local_medicao, cod_cidade, cod_funcionario_responsavel, valor_latitude, valor_longitude)
VALUES ('Joaquina', 1, 2, 27.5966738, 48.5463695);

INSERT INTO local_medicao(nome_local_medicao, cod_cidade, cod_funcionario_responsavel, valor_latitude, valor_longitude)
VALUES ('São Joaquim I', 3, 3, 28.2938889, 49.9316667);

INSERT INTO local_medicao(nome_local_medicao, cod_cidade, cod_funcionario_responsavel, valor_latitude, valor_longitude)
VALUES ('São Joaquim II', 3, 3, 28.2938569, 49.9316637);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 12:30:00', 1, 18.2);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 17:00:00', 1, 16.1);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 22:00:00', 1, 11.4);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 12:30:00', 2, 18.0);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 17:00:00', 2, 16.0);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 22:00:00', 2, 11.1);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 12:30:00', 3, 15.0);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 17:00:00', 3, 12.1);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 22:00:00', 3, 7.3);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 12:30:00', 4, 18.1);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 17:00:00', 4, 16.3);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 22:00:00', 4, 11.5);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 17:00:00', 5, 13.0);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 22:00:00', 5, 10.1);

INSERT INTO medicao (horario_medicao, cod_local_medicao,  valor_temperatura)
VALUES ('2013-07-28 12:30:00', 5, 14.0);


Questões
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)

2 - Retorne o nome de todos os pontos de medição ao lado do nome de sua 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 entre os no dia 28/07/2013 entre as 12:00 e 18:00.

4 - Retorne o valor da menor temperatura medida no estado

5 - Retorne a média de todas as temperaturas registradas em Florianópolis no dia 28/07/2013

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

7 - Retorne a maior temperatura registrada em São Joaquim

8 - Retorne a quantidade de medições realizadas em cada ponto de medição

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.
9.2 Cadastre a você mesmo como novo funcionário
9.3 Cadastre um ponto de medição localizado em na cidade onde você vai trabalhar. Como você será o responsável, você mesmo pode escolher o nome do ponto!
9.4 Cadastre as medições de temperatura que você fez hoje (pode inventar os valores)