# Lista de Revisão para Prova de SQL
Dois contextos fictícios com tabelas ricas e questões variadas para praticar JOINS, agregações, HAVING, WINDOW FUNCTIONS e CTEs.

## Informações:
Use a imagem: leogloriainfnet/revisao_prova_sql

O Arquivo de DDL q foi usado para criar é:

SET search_path TO cartografia, public;


-- Tabela de territórios mapeados (ilhas, desertos, fendas, etc.)
CREATE TABLE cartografia.territorios (
    id              SERIAL PRIMARY KEY,
    nome            VARCHAR(150) NOT NULL UNIQUE,
    tipo            VARCHAR(50)  NOT NULL,      -- ex: 'ilhas', 'planície'
    risco_nivel     INTEGER      NOT NULL
        CHECK (risco_nivel BETWEEN 0 AND 5)
);

-- Tabela de cartógrafos da guilda
CREATE TABLE cartografia.cartografos (
    id              SERIAL PRIMARY KEY,
    nome            VARCHAR(150) NOT NULL,
    senioridade     VARCHAR(20)  NOT NULL
        CHECK (senioridade IN ('junior', 'pleno', 'senior'))
);

-- Versões diárias (ou eventuais) da posição dos territórios
CREATE TABLE cartografia.versoes_territorio (
    id                  BIGSERIAL PRIMARY KEY,
    territorio_id       INTEGER     NOT NULL REFERENCES cartografia.territorios (id),
    data_ref            DATE        NOT NULL,
    pos_x               NUMERIC(10,3) NOT NULL,
    pos_y               NUMERIC(10,3) NOT NULL,
    nivel_instabilidade INTEGER     NOT NULL
        CHECK (nivel_instabilidade BETWEEN 0 AND 10),
    cartografo_id       INTEGER     NOT NULL REFERENCES cartografia.cartografos (id),


    CONSTRAINT chk_instabilidade_territorio
        CHECK (
            nivel_instabilidade <= 5
            OR territorio_id IS NOT NULL  
        )
);


CREATE TABLE cartografia.visitas_territorio (
    id            BIGSERIAL PRIMARY KEY,
    territorio_id INTEGER NOT NULL REFERENCES cartografia.territorios (id),
    data_visita   DATE    NOT NULL,
    visitantes    INTEGER NOT NULL
        CHECK (visitantes >= 0)
);


----------------------------------------------------------------------
-- SCHEMA 2: ARQUIVO SONORO DOS ANIMAIS QUE NUNCA EXISTIRAM
----------------------------------------------------------------------

CREATE SCHEMA IF NOT EXISTS arquivo_sonoro;

SET search_path TO arquivo_sonoro, public;

-- Tabela de criaturas míticas/gravadas
CREATE TABLE arquivo_sonoro.criaturas (
    id              SERIAL PRIMARY KEY,
    nome            VARCHAR(150) NOT NULL UNIQUE,
    classe          VARCHAR(50)  NOT NULL,     -- ex: 'etéreo', 'elemental'
    nivel_ameaca    INTEGER      NOT NULL
        CHECK (nivel_ameaca BETWEEN 1 AND 5)
);

-- Tabela de habitats impossíveis
CREATE TABLE arquivo_sonoro.habitats (
    id              SERIAL PRIMARY KEY,
    nome            VARCHAR(150) NOT NULL UNIQUE,
    tipo            VARCHAR(50)  NOT NULL,     -- ex: 'abissal', 'aéreo'
    dimensao        VARCHAR(50)  NOT NULL      -- ex: 'profundo', 'anfíbio'
);

-- Tabela de gravações de áudio das criaturas
CREATE TABLE arquivo_sonoro.gravacoes (
    id              BIGSERIAL PRIMARY KEY,
    criatura_id     INTEGER      NOT NULL REFERENCES arquivo_sonoro.criaturas (id),
    habitat_id      INTEGER      NOT NULL REFERENCES arquivo_sonoro.habitats (id),
    data_gravacao   TIMESTAMP    NOT NULL,
    frequencia_hz   NUMERIC(10,2) NOT NULL
        CHECK (frequencia_hz > 0),
    intensidade_db  NUMERIC(5,2)  NOT NULL,   -- pode passar de 100 dB
    pesquisador     VARCHAR(150)  NOT NULL
);

## Contexto 1 – Guilda dos Cartógrafos Improváveis
Schema: cartografia

A Guilda registra territórios instáveis, versões de suas posições ao longo do tempo e o fluxo de visitantes que passam por esses lugares improváveis.

### Tabelas

### cartografia.territorios
id – identificador
nome – nome do território
tipo – tipo (ilha, arquipélago, atol…)
risco_nivel – risco de 0 a 5

### cartografia.cartografos
id – identificador
nome – nome do cartógrafo
senioridade – junior, pleno ou senior

### cartografia.versoes_territorio
id
territorio_id (FK)
data_ref
pos_x, pos_y
nivel_instabilidade (0 a 10)
cartografo_id (FK)

### cartografia.visitas_territorio
id
territorio_id (FK)
data_visita
visitantes

---

# Questões – Contexto 1 (Cartografia)

1. Liste todos os territórios com o total de visitantes recebidos, ordenando do maior para o menor.
2. Liste, para cada tipo de território (tipo), o total de visitantes somando todos os territórios daquele tipo.
3. Para a Ilha Farol Errante, liste os 3 dias com maior número de visitantes.
4. Para cada território, calcule a média de visitantes por visita e mostre apenas aqueles com média superior a 80 visitantes.
5. Liste os territórios que nunca aparecem na tabela visitas_territorio.
6. Para cada nível de risco (risco_nivel), some o total de visitantes de todos os territórios daquele nível.
7. Mostre o nome do cartógrafo e a quantidade de registros em versoes_territorio que ele produziu.
8. Para cada cartógrafo, calcule a média de nivel_instabilidade de todas as versões que ele registrou.
9. Encontre os cartógrafos que nunca mapearam territórios com risco_nivel < 3.
10. Usando window functions, numere todas as versões de cada território em ordem cronológica (versao_n).
11. Para cada linha de versoes_territorio, calcule o deslocamento em relação à versão anterior do mesmo território utilizando distância euclidiana.
12. Descubra qual território apresenta o maior deslocamento total somando todos os deslocamentos entre versões consecutivas.
13. Para cada território, liste sua última posição registrada com base em data_ref e id.
14. Classifique cada dia registrado em visitas_territorio como CHEIO, NORMAL ou VAZIO de acordo com o total diário de visitantes.
15. Liste as 5 maiores combinações (territorio, data_visita) pelo total de visitantes.

---

## Contexto 2 – Arquivo Sonoro dos Animais que Nunca Existiram
Schema: arquivo_sonoro

Trata-se de um laboratório que registra gravações de criaturas impossíveis, encontradas em habitats igualmente improváveis.

### Tabelas

### arquivo_sonoro.criaturas
id
nome
classe
nivel_ameaca (1–5)

### arquivo_sonoro.habitats
id
nome
tipo
dimensao

### arquivo_sonoro.gravacoes
id
criatura_id (FK)
habitat_id (FK)
data_gravacao
frequencia_hz
intensidade_db
pesquisador

---

# Questões – Contexto 2 (Arquivo Sonoro)

1. Liste todas as criaturas com o total de gravações associadas, ordenando da mais gravada para a menos gravada.
2. Liste todos os habitats com o total de gravações feitas neles.
3. Para cada criatura, calcule a média de intensidade_db.
4. Liste as criaturas que não possuem nenhuma gravação registrada em gravacoes.
5. Liste os habitats cuja média de frequencia_hz é superior a 10.000 Hz.
6. Liste cada pesquisador e o total de gravações que ele realizou.
7. Para cada pesquisador, liste quantas criaturas diferentes ele já gravou.
8. Para cada criatura, liste a frequência mínima, máxima e média.
9. Liste as 5 gravações com maior intensidade (intensidade_db) mostrando criatura, habitat, pesquisador e data.
10. Numere as gravações de cada criatura em ordem de intensidade decrescente utilizando window functions (rank_intensidade).
11. Calcule a média móvel de 3 gravações para intensidade_db de cada criatura utilizando window functions.
12. Liste todas as gravações feitas no habitat Abismo de Eco Contínuo para criaturas de nivel_ameaca = 5.
13. Liste, para cada combinação de classe da criatura e tipo do habitat, a quantidade total de gravações.
14. Para cada criatura, liste a data da primeira e da última gravação.
15. Para cada habitat, calcule a proporção de gravações da criatura Serafim de Ruído Branco em relação ao total do habitat.