# Prova de SQL 23-09 — Cafeteria *hipster* **Instruções gerais** - Use o banco de dados fornecido. - Execute consultas SQL no schema indicado por cada bloco de questões. - Responda cada questão com a(s) consulta(s) SQL solicitada(s) e, quando pedirem, comente / interprete o resultado. Use o formulário: Para responder [https://forms.gle/FM9xqTrde3mTnoBL6] --- ## Imagem docker Rode o banco de dados com o comando: ```bash docker run -e POSTGRES_PASSWORD=123456 leogloriainfnet/prova-2309 ``` Acesse o container com: ```bash docker exec -it <container_id> psql -U postgres ``` --- ## Introdução Você foi contratado por uma rede de **cafeterias hipster** que deseja entender melhor seu cardápio de bebidas especiais. O catálogo está registrado no banco de dados em duas tabelas: - `hipster.cafes` – cafés especiais - `hipster.chas` – chás artesanais Cada tabela possui a seguinte estrutura básica: - `id` – identificador da bebida - `nome` – nome comercial da bebida - `origem` – país de origem dos grãos ou folhas - `metodo` – método de preparo (V60, Espresso, Cold Brew, Infusão, etc.) - `preco` – valor em reais Além disso, a tabela `hipster.cafes` possui um campo adicional: - `dados (JSONB)` – informações extras sobre o café (torra, notas sensoriais, `organico`, etc.) Seu desafio é explorar esse conjunto de dados e responder às questões propostas utilizando consultas SQL. --- # Questões ## Questões sobre `hipster.cafes` / `hipster.chas` (Q1–Q13) 1. **(Q1)** Liste todos os cafés preparados no método **Espresso** ou cujo preço seja menor que **15**. 2. **(Q2)** Liste todos os cafés que **não** são de origem **Brasil** e cujo preço seja maior que **18**. 3. **(Q3)** Liste todos os cafés cuja origem seja **Etiópia** ou **Quênia**, e cujo preço seja diferente de **19**. 4. **(Q4)** Liste os cafés cuja origem seja **Colômbia** ou cujo preço esteja entre **18** e **22**, ordenando pelo preço crescente. 5. **(Q5)** Liste os cafés cujo nome contenha a palavra **Latte**. 6. **(Q6)** Liste os cafés com método **V60** ou **Chemex**, e preço maior ou igual a **19**. 7. **(Q7)** Exiba `id`, `nome` e `metodo_exibido`, onde `metodo_exibido` mostra **"Indefinido"** quando o valor original for `NULL`. Liste apenas os cafés cujo `metodo_exibido` seja **"Indefinido"** ou **"Outros"**. 8. **(Q8)** Exiba `nome`, `preco` e uma coluna calculada `faixa` que classifique cada café como: - **barato**: `preco < 15` - **medio**: `15 <= preco <= 20` - **caro**: `preco > 20` Mostre apenas cafés classificados como **medio** ou **caro**. 9. **(Q9)** Liste os cafés cuja `origem` esteja em **Brasil, Peru ou Etiópia**, e cujo `metodo` **não** esteja em **Outros** ou **Cold Brew**. 10. **(Q10)** Liste todas as **origens distintas** de cafés cujo `preco >= 18` **ou** cujo `metodo = 'Espresso'`, **excluindo** a origem **Brasil**. 11. **(Q11)** Alguns nomes de bebidas podem aparecer em mais de um lugar do catálogo. Liste todos os **nomes de bebidas** em um único resultado e **explique** se o conjunto final deve mostrar cada bebida apenas uma vez ou permitir repetições. Justifique sua escolha. 12. **(Q12)** Gere novamente a lista de nomes de bebidas em um único resultado, mas agora **siga a lógica contrária** da questão anterior. Compare os resultados obtidos e explique a diferença em termos de: - a) quantidade de registros - b) impacto na interpretação do catálogo de bebidas 13. **(Q13a / Q13b)** - **Q13a**: Liste o `nome` e a `torra` de todos os cafés que sejam marcados como **orgânicos** (`organico = true`) no campo `dados` (JSONB). - **Q13b**: Liste o `nome` de todos os cafés que possuam registrada a chave `"notas"` no campo `dados`. --- ## Questões sobre `comercio.pedidos` (Q14–Q18) > **Importante:** nas questões 14 a 18 use exclusivamente a tabela `comercio.pedidos`. 14. **(Q14)** Agrupe os pedidos do **ano de 2025** por **status**, trazendo: - total de pedidos - soma de valores Ordene pelo maior faturamento. 15. **(Q15)** A tabela `comercio.pedidos` possui milhões de registros e é frequentemente consultada por **data de criação** e por **status**. Crie índices apropriados nessas colunas para acelerar as consultas. 16. **(Q16)** Para os últimos **12 meses**, mostre por mês: - número de clientes distintos - total de pedidos realizados no mês 17. **(Q17)** Use uma **CTE** para calcular o valor total e a quantidade de pedidos por **status**. Na consulta final, apresente também o **ticket médio** (valor médio por pedido). 18. **(Q18)** Considere um relatório de **vendas mensais** calculado a partir de uma grande quantidade de registros de pedidos. Esse relatório é acessado pelo gerente **apenas uma vez por mês** e **não precisa** refletir imediatamente pedidos recém-inseridos. a) Escreva a consulta SQL que retorna o **total de vendas por mês** nos últimos **36 meses**. b) Explique: neste cenário, é mais indicado usar uma **VIEW** ou uma **MATERIALIZED VIEW**? Justifique. c) Se a escolha for **MATERIALIZED VIEW**, mostre como criá-la e como atualizá-la. d) Se a escolha fosse **VIEW**, qual seria a principal vantagem e a principal desvantagem? --- ## Questões finais — Estatística e medidas robustas 19. **(Q19)** *Valor típico* — tabela `academia.salarios` Considere a tabela `academia.salarios`. a) Escreva uma consulta que calcule a **média** dos salários. b) Escreva uma consulta que mostre o **menor** salário e o **maior** salário. c) Analise os três resultados (média, menor e maior). Eles representam bem o salário típico da equipe? Explique. d) Proponha e implemente em SQL uma forma alternativa de representar o **salário típico**, **sem** usar funções estatísticas prontas (ex.: **sem** `percentile_cont`). e) Compare os resultados e explique qual medida é mais adequada para este conjunto de dados. 20. **(Q20)** *Intervalo de confiança (95%)* — tabela `estatistica.notas` Considere a tabela `estatistica.notas`. a) Escreva uma consulta que calcule a **média** e o **desvio padrão amostral** das notas. b) Calcule o **erro padrão da média** (`desvio / √n`). c) Considere nível de confiança **95%** (valor crítico **1.96**). Calcule os limites inferior e superior do intervalo de confiança da média. d) Interprete o resultado em termos práticos. --- # Critérios de avaliação - **ND :** < 59 pts - **D :** 60–83 pts - **DM :** 84–105 pts - **DML (máximo):** 106 + pts