Subconsultas úteis

Neste capítulo você vai encaixar SELECT dentro de outro SELECT. O projeto de estudo é o Observatório do Recife, um núcleo fictício de monitoramento urbano e climático.

Seu ponto de partida

O exemplo deste capítulo é 10-subconsultas, no pacote que acompanha o volume; pratique sobre uma cópia da pasta inicio/. A raiz do exemplo é a referência desta etapa. Abrir ou rodar localmente não publica nada. Servidor, se precisar, só em 127.0.0.1.

Como praticar

Leia, confira o código, mude uma coisa, preveja, salve, rode de novo e confira. Investigue o erro antes de avançar.

Um resultado antes de outro conceito

Abra o banco observatorio.db e rode a consulta de referência. Veja linhas reais do Observatório antes de memorizar sintaxe.

Faça na sua cópia

A implementação completa está no pacote de exemplos que acompanha este volume.

-- Capítulo 10 — subconsulta
SELECT codigo FROM estacoes
WHERE estacao_id IN (
  SELECT estacao_id FROM leituras WHERE chuva_mm > 2
);

Mude uma coisa e preveja

Altere um número de filtro ou o LIMIT (se houver) e preveja quantas linhas voltam.

Anote o que espera observar antes de salvar e rodar de novo.

Recarregue e confira

O resultado muda só no que você pediu. Se der erro de sintaxe, leia a mensagem do SQLite.

Se não acontecer

Confira se salvou a cópia certa e se o caminho dos dados aponta para dados/ dentro da pasta do exemplo.

Erro comum

Esquecer a vírgula entre colunas, usar = com texto sem aspas ou apontar para o banco errado quebra a consulta. Confira o arquivo aberto.

Faça na sua cópia

A implementação completa está no pacote de exemplos que acompanha este volume.

-- Capítulo 10 — subconsulta
SELECT codigo FROM estacoes
WHERE estacao_id IN (
  SELECT estacao_id FROM leituras WHERE chuva_mm > 2
);

Mude uma coisa e preveja

Introduza de propósito um erro (remova uma vírgula) e leia a mensagem.

Anote o que espera observar antes de salvar e rodar de novo.

Recarregue e confira

A mensagem aponta a linha. Restaure o arquivo da referência se precisar.

Se não acontecer

Confira se salvou a cópia certa e se o caminho dos dados aponta para dados/ dentro da pasta do exemplo.

Conferir no contexto do Observatório

Toda consulta responde a uma pergunta de monitoramento: quem mediu, quando, quanto choveu, qual alerta.

Faça na sua cópia

A implementação completa está no pacote de exemplos que acompanha este volume.

-- Capítulo 10 — subconsulta
SELECT codigo FROM estacoes
WHERE estacao_id IN (
  SELECT estacao_id FROM leituras WHERE chuva_mm > 2
);

Mude uma coisa e preveja

Escreva em uma frase a pergunta que a consulta responde.

Anote o que espera observar antes de salvar e rodar de novo.

Recarregue e confira

Se a frase e o resultado não combinam, ajuste a consulta — não os dados reais de ninguém; aqui tudo é fictício.

Se não acontecer

Confira se salvou a cópia certa e se o caminho dos dados aponta para dados/ dentro da pasta do exemplo.

Atividades

Responda antes de olhar as respostas comentadas no final.

1. Qual o objetivo deste capítulo?

2. O banco de estudo fica em qual arquivo?

3. Como rodar sem interface gráfica?

Antes de avançar

• Você conseguiu encaixar SELECT dentro de outro SELECT.
• A cópia inicio/ roda sem depender de conta paga.
• Você distingue arquivo local, resultado na tela e publicação pública.

Respostas comentadas

Atividade 1

encaixar SELECT dentro de outro SELECT

Atividade 2

observatorio.db na pasta do exemplo.

Atividade 3

python rodar.py ou sqlite3 observatorio.db < consultas.sql.

O arquivo completo

Referência do exemplo 10-subconsultas, reproduzida aqui para que você possa conferir sem sair da página.

rodar.py

# -*- coding: utf-8 -*-
"""Roda consultas.sql contra observatorio.db (local)."""
from pathlib import Path
import re
import sqlite3

root = Path(__file__).resolve().parent
db = root / "observatorio.db"
raw = (root / "consultas.sql").read_text(encoding="utf-8")
# remove comentarios de linha para classificar o comando
def limpar(stmt: str) -> str:
    linhas = []
    for line in stmt.splitlines():
        if "--" in line:
            line = line[: line.index("--")]
        if line.strip():
            linhas.append(line)
    return "\n".join(linhas).strip()

con = sqlite3.connect(db)
con.row_factory = sqlite3.Row
print(f"Banco: {db}")
for i, chunk in enumerate((s for s in raw.split(";") if s.strip()), 1):
    stmt = limpar(chunk)
    if not stmt:
        continue
    head = stmt.lstrip().split(None, 1)[0].upper()
    if head in {"SELECT", "WITH", "PRAGMA"}:
        rows = con.execute(stmt).fetchall()
        print(f"\n-- consulta {i} ({len(rows)} linhas) --")
        if rows:
            print(" | ".join(rows[0].keys()))
            for r in rows[:20]:
                print(" | ".join(str(r[k]) for k in r.keys()))
    else:
        con.execute(stmt)
        con.commit()
        print(f"\n-- comando {i} executado ({head}) --")
con.close()

consultas.sql

-- Capítulo 10 — subconsulta
SELECT codigo FROM estacoes
WHERE estacao_id IN (
  SELECT estacao_id FROM leituras WHERE chuva_mm > 2
);

SELECT * FROM leituras
WHERE temp_c > (SELECT AVG(temp_c) FROM leituras);