-- =====================================================
-- BANCO DE DADOS ESCOLAR - ENSINO MEDIO
-- Projeto organizado conforme as etapas 1 a 9
-- Compatibilidade: SQLite / MyCompiler
-- =====================================================

PRAGMA foreign_keys = ON;

-- ETAPA 2 - CRIACAO DAS TABELAS

-- Limpeza para permitir executar o script novamente.
DROP TABLE IF EXISTS notas;
DROP TABLE IF EXISTS alunos;
DROP TABLE IF EXISTS turmas;
DROP TABLE IF EXISTS professores;
DROP TABLE IF EXISTS disciplinas;
DROP TABLE IF EXISTS areas;

-- Tabela 1: areas do conhecimento
CREATE TABLE areas (
    id_area INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL
);

-- Tabela 2: disciplinas
CREATE TABLE disciplinas (
    id_disciplina INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    id_area INTEGER NOT NULL,
    FOREIGN KEY (id_area) REFERENCES areas(id_area)
);

-- Tabela 3: professores
CREATE TABLE professores (
    id_professor INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

-- Tabela 4: turmas
CREATE TABLE turmas (
    id_turma INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    ano_escolar INTEGER NOT NULL
        CHECK (ano_escolar BETWEEN 1 AND 3)
);

-- Tabela 5: alunos
CREATE TABLE alunos (
    id_aluno INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    id_turma INTEGER NOT NULL,
    FOREIGN KEY (id_turma) REFERENCES turmas(id_turma)
);

-- Tabela 6: notas
CREATE TABLE notas (
    id_nota INTEGER PRIMARY KEY AUTOINCREMENT,
    id_aluno INTEGER NOT NULL,
    id_disciplina INTEGER NOT NULL,
    id_professor INTEGER NOT NULL,
    bimestre INTEGER NOT NULL
        CHECK (bimestre BETWEEN 1 AND 4),
    nota REAL NOT NULL
        CHECK (nota BETWEEN 0 AND 10),

    FOREIGN KEY (id_aluno) REFERENCES alunos(id_aluno),
    FOREIGN KEY (id_disciplina) REFERENCES disciplinas(id_disciplina),
    FOREIGN KEY (id_professor) REFERENCES professores(id_professor)
);

-- ETAPA 3 - CADASTRO DAS AREAS

INSERT INTO areas (nome) VALUES
('Linguagens e suas Tecnologias'),
('Matemática e suas Tecnologias'),
('Ciências da Natureza e suas Tecnologias'),
('Ciências Humanas e Sociais Aplicadas');

-- Conferencia das areas
SELECT * FROM areas ORDER BY id_area;

-- ETAPA 4 - CADASTRO DAS DISCIPLINAS

INSERT INTO disciplinas (nome, id_area) VALUES
('Língua Portuguesa', 1),
('Língua Inglesa', 1),
('Matemática', 2),
('Biologia', 3),
('Física', 3),
('História', 4),
('Geografia', 4),
('Sociologia', 4);

SELECT * FROM disciplinas ORDER BY id_disciplina;

-- CADASTRO DOS PROFESSORES
-- Um professor representando cada area.

INSERT INTO professores (nome, email) VALUES
('Ana Souza', 'ana.souza@escola.com'),
('Carlos Lima', 'carlos.lima@escola.com'),
('Marina Alves', 'marina.alves@escola.com'),
('Paulo Mendes', 'paulo.mendes@escola.com');

SELECT * FROM professores ORDER BY id_professor;

-- CADASTRO DAS TURMAS

INSERT INTO turmas (nome, ano_escolar) VALUES
('1A', 1),
('2A', 2),
('3A', 3);

SELECT * FROM turmas ORDER BY id_turma;

-- CADASTRO DOS ALUNOS

INSERT INTO alunos (nome, id_turma) VALUES
('Lucas Santos', 1),
('Beatriz Oliveira', 1),
('Rafael Costa', 2),
('Julia Martins', 2),
('Pedro Almeida', 3),
('Camila Ferreira', 3);

SELECT * FROM alunos ORDER BY id_aluno;

-- ETAPA 5 - CADASTRO DE 18 NOTAS
-- Os IDs correspondem aos registros cadastrados acima.

INSERT INTO notas
(id_aluno, id_disciplina, id_professor, bimestre, nota)
VALUES
(1, 1, 1, 1, 8.5),
(1, 3, 2, 1, 7.0),
(2, 2, 1, 1, 5.5),
(2, 4, 3, 1, 9.0),
(3, 5, 3, 1, 6.0),
(3, 6, 4, 1, 7.5),
(4, 7, 4, 1, 8.0),
(5, 8, 4, 1, 6.5),
(6, 1, 1, 1, 9.5),

(1, 1, 1, 2, 9.0),
(1, 3, 2, 2, 8.0),
(2, 2, 1, 2, 6.5),
(2, 4, 3, 2, 8.5),
(3, 5, 3, 2, 7.0),
(4, 7, 4, 2, 9.0),

(1, 1, 1, 3, 8.0),
(2, 2, 1, 3, 7.5),
(5, 8, 4, 4, 7.0);

-- Conferencia das notas
SELECT * FROM notas ORDER BY id_nota;

-- ETAPA 6 - RELATORIO COM JOIN
-- Combina as seis tabelas.

SELECT
    a.nome AS aluno,
    t.nome AS turma,
    d.nome AS disciplina,
    ar.nome AS area_do_conhecimento,
    p.nome AS professor,
    n.bimestre,
    n.nota

FROM notas AS n

INNER JOIN alunos AS a
    ON n.id_aluno = a.id_aluno

INNER JOIN turmas AS t
    ON a.id_turma = t.id_turma

INNER JOIN disciplinas AS d
    ON n.id_disciplina = d.id_disciplina

INNER JOIN areas AS ar
    ON d.id_area = ar.id_area

INNER JOIN professores AS p
    ON n.id_professor = p.id_professor

ORDER BY a.nome, d.nome, n.bimestre;

-- ETAPA 7 - FILTROS COM WHERE

-- Consulta 1: notas do primeiro bimestre.

SELECT
    a.nome AS aluno,
    d.nome AS disciplina,
    n.bimestre,
    n.nota
FROM notas AS n
INNER JOIN alunos AS a
    ON n.id_aluno = a.id_aluno
INNER JOIN disciplinas AS d
    ON n.id_disciplina = d.id_disciplina
WHERE n.bimestre = 1
ORDER BY a.nome, d.nome;

-- Consulta 2: alunos com notas abaixo de 6.

SELECT
    a.nome AS aluno,
    d.nome AS disciplina,
    n.bimestre,
    n.nota
FROM notas AS n
INNER JOIN alunos AS a
    ON n.id_aluno = a.id_aluno
INNER JOIN disciplinas AS d
    ON n.id_disciplina = d.id_disciplina
WHERE n.nota < 6
ORDER BY a.nome, d.nome, n.bimestre;

-- Consulta 3: notas entre 7 e 10 em ordem decrescente.

SELECT
    a.nome AS aluno,
    d.nome AS disciplina,
    n.bimestre,
    n.nota
FROM notas AS n
INNER JOIN alunos AS a
    ON n.id_aluno = a.id_aluno
INNER JOIN disciplinas AS d
    ON n.id_disciplina = d.id_disciplina
WHERE n.nota BETWEEN 7 AND 10
ORDER BY n.nota DESC;

-- ETAPA 8 - MEDIAS COM AVG, GROUP BY E ROUND

-- Media por aluno e disciplina.

SELECT
    a.nome AS aluno,
    d.nome AS disciplina,
    ROUND(AVG(n.nota), 2) AS media,
    COUNT(n.id_nota) AS quantidade_de_notas

FROM notas AS n

INNER JOIN alunos AS a
    ON n.id_aluno = a.id_aluno

INNER JOIN disciplinas AS d
    ON n.id_disciplina = d.id_disciplina

GROUP BY
    a.id_aluno,
    a.nome,
    d.id_disciplina,
    d.nome

ORDER BY a.nome, d.nome;

-- Media por disciplina.

SELECT
    d.nome AS disciplina,
    ROUND(AVG(n.nota), 2) AS media,
    COUNT(n.id_nota) AS quantidade_de_notas

FROM notas AS n

INNER JOIN disciplinas AS d
    ON n.id_disciplina = d.id_disciplina

GROUP BY d.id_disciplina, d.nome

ORDER BY d.nome;

-- ETAPA 9 - RELATORIO ACADEMICO FINAL

SELECT
    a.nome AS aluno,
    t.nome AS turma,
    d.nome AS disciplina,
    ar.nome AS area,

    ROUND(AVG(n.nota), 2) AS media_das_notas,

    COUNT(n.id_nota) AS quantidade_de_notas,

    CASE
        WHEN AVG(n.nota) >= 6
            THEN 'Aprovado'
        ELSE 'Recuperacao'
    END AS situacao

FROM notas AS n

INNER JOIN alunos AS a
    ON n.id_aluno = a.id_aluno

INNER JOIN turmas AS t
    ON a.id_turma = t.id_turma

INNER JOIN disciplinas AS d
    ON n.id_disciplina = d.id_disciplina

INNER JOIN areas AS ar
    ON d.id_area = ar.id_area

GROUP BY
    a.id_aluno,
    a.nome,
    t.id_turma,
    t.nome,
    d.id_disciplina,
    d.nome,
    ar.id_area,
    ar.nome

ORDER BY a.nome, d.nome;

-- FIM DO PROJETO