🔍 CAPÍTULO 14: CONSULTAS DQL, SELECT, FILTROS E AGREGAÇÕES

🎯 Objetivos de Aprendizagem

Ao final deste capítulo (estimativa: 2 horas de estudo autoguiado), você será capaz de:

• 🔹 Dominar a anatomia completa da instrução SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY e LIMIT/OFFSET.
• 🔹 Aplicar funções agregadoras essenciais: COUNT(), SUM(), AVG(), MIN() e MAX().
• 🔹 Diferenciar com precisão o papel do WHERE (filtro linha a linha antes do agrupamento) vs HAVING (filtro sobre o resultado agregado do grupo).
• 🔹 Construir queries analíticas e relatórios de fechamento corporativo em SQLAlchemy 2.0.

Dos milhares de comandos SQL rodando agora nos servidores da Amazon ou do Google, 90% deles são comandos SELECT. A Extração de Dados (DQL - Data Query Language) é o que permite que a diretoria tome decisões baseadas em fatos, e não em achismos. 🛡️🧩

🏢 O Cenário Prático (Seu Desafio)

O Diretor de Operações da TecProExpress quer um painel de controle. Ele diz: "Quero ver apenas os motoristas do estado de São Paulo, que ganham mais de R$ 3.000,00, organizados do maior salário para o menor, e quero o nome de quem tem 'Silva' no sobrenome".

"Seu desafio é transformar esse pedido humano em uma Query SQL matemática e cirúrgica, extraindo exatamente o que o diretor quer da base de dados, ignorando os milhares de outros registros irrelevantes."

🧠 Fundamentos: A Anatomia da Busca

O comando SELECT lê as tabelas de forma passiva. Ele nunca altera ou apaga um dado. Ele apenas tira uma "foto" e te mostra.

⚙️ Ordem Lógica de Execução do SGBD

Embora você escreva o comando começando com SELECT, o processador de consultas do banco de dados executa as etapas na seguinte ordem lógica:

flowchart LR
    A["1. FROM / JOIN<br>Carrega Tabelas"] --> B["2. WHERE<br>Filtra Linhas"]
    B --> C["3. GROUP BY<br>Agrupa Dados"]
    C --> D["4. HAVING<br>Filtra Grupos"]
    D --> E["5. SELECT<br>Projeta Colunas"]
    E --> F["6. ORDER BY<br>Ordena Linhas"]
    F --> G["7. LIMIT / OFFSET<br>Pagina Resultado"]

    style A fill:#e3f2fd,stroke:#1976d2
    style B fill:#fff3e0,stroke:#f57c00
    style E fill:#e8f5e9,stroke:#388e3c
    style F fill:#f3e5f5,stroke:#7b1fa2

1. Seleção Básica (O Coringa)

-- DDL/DML Base para os exemplos
-- CREATE TABLE motorista (id INT, nome VARCHAR(50), estado CHAR(2), salario DECIMAL(10,2));
-- INSERT INTO motorista VALUES (1, 'João Silva', 'SP', 3500.00), (2, 'Maria Souza', 'RJ', 2800.00);

-- O * (asterisco) significa "traga todas as colunas"
SELECT * FROM motorista;

-- Traga apenas colunas específicas (Melhor performance)
SELECT nome, salario FROM motorista;

2. O Filtro Matemático (WHERE)

Você não quer ver o banco inteiro, você quer filtrar linhas (Tuplas).

-- Filtro Exato
SELECT nome FROM motorista WHERE estado = 'SP';

-- Filtro Numérico Maior/Menor
SELECT nome FROM motorista WHERE salario > 3000.00;

3. Filtros Avançados de Padrão (LIKE e IN)

Para buscar textos parciais ou listas específicas.

-- O operador % funciona como um coringa (qualquer coisa antes, 'Silva', qualquer coisa depois)
SELECT nome FROM motorista WHERE nome LIKE '%Silva%';

-- O operador IN evita ter que escrever vários OR (estado='SP' OR estado='RJ')
SELECT nome FROM motorista WHERE estado IN ('SP', 'RJ', 'MG');

4. Ordenação (ORDER BY)

Como o resultado será apresentado na tela.

-- ASC = Crescente (A-Z ou 0-9) | DESC = Decrescente (Z-A ou 9-0)
SELECT nome, salario FROM motorista ORDER BY salario DESC;

📊 Pipeline de Execução Lógica do Comando SELECT

Embora você escreva o SELECT na primeira linha, o motor do banco de dados (Query Engine) processa a consulta na seguinte ordem interna:

flowchart TD
    S1["1. FROM & JOINs (Localiza as tabelas e junta os dados)"] --> S2["2. WHERE (Filtra as linhas individuais)"]
    S2 --> S3["3. GROUP BY (Agrupa registros em blocos)"]
    S3 --> S4["4. HAVING (Filtra os grupos calculados)"]
    S4 --> S5["5. SELECT (Projeta e formata as colunas solicitadas)"]
    S5 --> S6["6. ORDER BY (Ordena o conjunto resultante)"]
    S6 --> S7["7. LIMIT & OFFSET (Pagina os resultados para a aplicação)"]
    style S1 fill:#e3f2fd,stroke:#1e88e5
    style S2 fill:#fff3e0,stroke:#fb8c00
    style S5 fill:#e8f5e9,stroke:#43a047
    style S7 fill:#f3e5f5,stroke:#8e24aa

📖 Exemplo Guiado: O Relatório do Diretor

Vamos unir todas as cláusulas para resolver o desafio da TecProExpress.

A ordem de escrita é obrigatória no SQL:

1. SELECT (O que eu quero ver?)
2. FROM (De onde vem?)
3. WHERE (Quais são as condições?)
4. ORDER BY (Como organizo?)

🛠️ Código do Exemplo

SELECT nome, estado, salario 
FROM motorista 
WHERE estado = 'SP' 
  AND salario > 3000.00 
  AND nome LIKE '%Silva%'
ORDER BY salario DESC;

🔍 Detalhamento do Código:

• AND: O operador lógico exige que as três condições sejam verdadeiras simultaneamente para que a linha seja exibida na tela.
• ORDER BY salario DESC: O motorista que ganha R$ 4.000 aparecerá antes do que ganha R$ 3.500.

🛠️ Prática Obrigatória: Extração de Dados

Cenário: O relatório de Pacotes da TecProExpress.

1. Crie a tabela pacote com id, descricao, peso_kg e valor_frete.
2. Insira 4 pacotes com pesos e fretes diferentes.
3. Crie um SELECT que mostre apenas a descricao e o valor_frete dos pacotes que pesam entre 10 e 50 kg (BETWEEN), ordenados pelo frete mais barato primeiro.

🚀 Script de Seed (Gabarito DQL)

-- 1. Setup (DDL)
CREATE TABLE pacote (
    id INT PRIMARY KEY,
    descricao VARCHAR(100),
    peso_kg DECIMAL(5,2),
    valor_frete DECIMAL(10,2)
);

-- 2. Massa de Dados (DML)
INSERT INTO pacote VALUES (1, 'Televisão', 15.50, 150.00);
INSERT INTO pacote VALUES (2, 'Celular', 0.50, 30.00);
INSERT INTO pacote VALUES (3, 'Geladeira', 80.00, 400.00);
INSERT INTO pacote VALUES (4, 'Bicicleta', 20.00, 200.00);

-- 3. O Relatório (DQL)
SELECT descricao, valor_frete 
FROM pacote 
WHERE peso_kg BETWEEN 10.00 AND 50.00 
ORDER BY valor_frete ASC;

💻 Ponte Prática: Do SQL Manual ao SQLAlchemy 2.0 ORM

Como o SQLAlchemy 2.0 executa consultas SELECT parametrizadas e imunes a SQL Injection em Python?

🔴 1. A Abordagem Manual (Concatenação de Strings e Risco de SQL Injection)

No modelo procedural ingênuo, strings SQL são montadas com formatação de texto direta. Um usuário malicioso pode injetar comandos arbitrários no banco:

# ❌ ABORDAGEM VULNERÁVEL: SQL Injection clássico
termo_busca = "Silva' OR '1'='1"
# O comando resultante ignora o filtro e vaza todos os registros da empresa!
query_insegura = f"SELECT * FROM motoristas WHERE nome LIKE '%{termo_busca}%';"

🟢 2. A Abordagem com SQLAlchemy 2.0 (Consultas Declarativas e Parametrização Automática)

Com o SQLAlchemy 2.0, usamos a API select(). Todos os parâmetros passados no .where() são tratados estritamente como dados, tornando o código imune a injeções:

# ✅ ABORDAGEM MODERNA COM SQLALCHEMY 2.0: Consultas Tipadas e Seguras
from sqlalchemy import create_engine, String, Float, Integer, select, desc, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class MotoristaSelectModel(Base):
    __tablename__ = "motoristas_dql"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nome: Mapped[str] = mapped_column(String(100), nullable=False)
    estado: Mapped[str] = mapped_column(String(2), nullable=False)
    salario: Mapped[float] = mapped_column(Float, nullable=False)

    def __repr__(self) -> str:
        return f"Motorista(id={self.id}, nome='{self.nome}', UF='{self.estado}', Salario=R$ {self.salario:.2f})"

🛠️ Mini-Projeto 14 (BD): Painel Analítico de Faturamento e Filtros Parametrizados

Objetivo: Construir consultas complexas com filtros compostos, ordenação e funções agregadas (func.sum, func.avg) via SQLAlchemy 2.0.

📋 Pré-requisitos e Instalação

No terminal do seu ambiente virtual (PowerShell ou Bash), instale a biblioteca necessária:

pip install sqlalchemy

💻 Código Completo e Autocontido (miniprojeto_14_dql.py)

Crie o arquivo miniprojeto_14_dql.py e insira o código abaixo integralmente:

"""
Mini-Projeto 14: Painel Analítico de Faturamento e Filtros Parametrizados
Curso: GTI - Banco de Dados Relacionais e Engenharia de Software
Stack: Python 3.11+ | SQLAlchemy 2.0 | SQLite
"""
import os
from sqlalchemy import create_engine, String, Float, Integer, select, desc, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

# 1. Definição Declarativa do Schema DQL
class Base(DeclarativeBase):
    pass

class MotoristaSelectModel(Base):
    __tablename__ = "motoristas_dql"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nome: Mapped[str] = mapped_column(String(100), nullable=False)
    estado: Mapped[str] = mapped_column(String(2), nullable=False)
    salario: Mapped[float] = mapped_column(Float, nullable=False)

    def __repr__(self) -> str:
        return f"Motorista(id={self.id}, nome='{self.nome}', UF='{self.estado}', Salario=R$ {self.salario:.2f})"

# 2. Ponto de Entrada Executável
if __name__ == "__main__":
    DB_FILE = "tecpro_dql_demo.db"

    # Reset preventivo para garantir idempotência em testes repetidos
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    engine = create_engine(f"sqlite:///{DB_FILE}", echo=False)
    Base.metadata.create_all(bind=engine)

    # 1. Povoando a base com massa de motoristas
    with Session(engine) as session:
        m1 = MotoristaSelectModel(id=1, nome="João Silva", estado="SP", salario=3500.0)
        m2 = MotoristaSelectModel(id=2, nome="Maria Souza", estado="RJ", salario=2800.0)
        m3 = MotoristaSelectModel(id=3, nome="Carlos Eduardo Silva", estado="SP", salario=4200.0)
        m4 = MotoristaSelectModel(id=4, nome="Ana Cristina", estado="MG", salario=3100.0)
        session.add_all([m1, m2, m3, m4])
        session.commit()

    print("🔍 PAINEL ANALÍTICO DQL (CONSULTAS SELECT) - TECPROEXPRESS")
    print("=" * 65)

    with Session(engine) as session:
        # Relatório 1: Filtro Composto (SP, Salário > 3000, Sobrenome Silva, Ordenado DESC)
        termo = "Silva"
        stmt_filtro = (
            select(MotoristaSelectModel)
            .where(MotoristaSelectModel.estado == "SP")
            .where(MotoristaSelectModel.salario > 3000.0)
            .where(MotoristaSelectModel.nome.like(f"%{termo}%"))
            .order_by(desc(MotoristaSelectModel.salario))
        )
        print("1. [DQL] Motoristas de SP com 'Silva' e Salário > R$ 3.000 (Maior para o Menor):")
        for m in session.scalars(stmt_filtro).all():
            print(f"   • {m}")

        # Relatório 2: Métricas Agregadas (Média Salarial e Folha Total de SP)
        stmt_metricas = (
            select(
                func.count(MotoristaSelectModel.id),
                func.sum(MotoristaSelectModel.salario),
                func.avg(MotoristaSelectModel.salario)
            ).where(MotoristaSelectModel.estado == "SP")
        )
        total_mot, folha_total, media_salarial = session.execute(stmt_metricas).one()
        print("\n2. [AGREGAÇÕES] Resumo da Folha em SP:")
        print(f"   • Total de Motoristas em SP: {total_mot}")
        print(f"   • Folha Mensal Total: R$ {folha_total:.2f}")
        print(f"   • Média Salarial: R$ {media_salarial:.2f}")

    print("=" * 65)

🚀 Como Executar

Execute o script no terminal:

python miniprojeto_14_dql.py

🖥️ Saída Esperada no Console

🔍 PAINEL ANALÍTICO DQL (CONSULTAS SELECT) - TECPROEXPRESS
=================================================================
1. [DQL] Motoristas de SP com 'Silva' e Salário > R$ 3.000 (Maior para o Menor):
   • Motorista(id=3, nome='Carlos Eduardo Silva', UF='SP', Salario=R$ 4200.00)
   • Motorista(id=1, nome='João Silva', UF='SP', Salario=R$ 3500.00)

2. [AGREGAÇÕES] Resumo da Folha em SP:
   • Total de Motoristas em SP: 2
   • Folha Mensal Total: R$ 7700.00
   • Média Salarial: R$ 3850.00
=================================================================

💡 Checkpoint de Lógica