---
# System prepended metadata

title: Prova de SQL — Cafeteria *hipster*

---

# 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
