Projeto: Notas da Escola

A Lúcia, amiga da Ana, é professora numa escola municipal de Teresina. No fim de cada bimestre ela passa uma tarde com a planilha: soma as notas, divide, confere quem passou, quem vai para a recuperação, e monta o boletim de cada aluno à mão. Neste projeto você constrói o banco de notas da turma dela e faz o SQL trabalhar: médias, situação de cada aluno, o boletim em colunas, o melhor de cada matéria e até o aluno que faltou a uma prova. Tudo em versões, cada uma com uma missão no editor.

Você vai aprender
  • A modelar alunos, disciplinas e notas, com uma chave primária de três colunas.
  • A calcular médias por aluno e por disciplina com AVG e GROUP BY.
  • A transformar a média em "aprovado", "recuperação" ou "reprovado" com CASE.
  • A montar o boletim com uma coluna por disciplina (a "tabela cruzada").
  • A achar a prova que faltou com CROSS JOIN e decidir quanto ela vale com COALESCE.
  • A achar o melhor aluno de cada disciplina com RANK() OVER (PARTITION BY …) e a proteger as notas com CHECK.

A ideia: da planilha às três tabelas

A planilha da Lúcia tem uma linha por aluno e uma coluna por prova: "Mat 1º bim", "Mat 2º bim", "Port 1º bim"… Parece prático, mas cada matéria nova é uma coluna nova, e cada bimestre também. Num banco, a gente guarda cada nota como uma linha, dizendo de quem é, de que matéria e de que bimestre:

TabelaUma linha é…Exemplo
alunosum aluno da turma1, Bianca Reis
disciplinasuma matéria1, Matemática
notasuma nota de um aluno, numa matéria, num bimestrealuno 1, disciplina 1, bimestre 1, nota 8.5

Com uma nota por linha, uma matéria nova é só mais uma linha em disciplinas; nenhuma tabela muda de forma. E o boletim "em colunas" da planilha não se perde: na versão 4 você vai ver que o SQL monta essas colunas na hora de consultar.

Como as caixas deste projeto funcionam

A escola é um banco novo. O editor, a cada Rodar, começa do zero (com a Loja Aurora carregada, que aqui não atrapalha: ela não tem tabelas com estes nomes). Por isso toda caixa começa com a montagem: os CREATE TABLE e os INSERT da turma, sempre iguais. A consulta da versão vem depois do comentário -- a consulta.

Versão 1: a turma no banco

Cinco alunos, três disciplinas e as notas dos dois primeiros bimestres:

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina, n.bimestre, n.nota
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
WHERE a.id = 1;
nota REAL NOT NULL
Nota com casa decimal (7.5, 8.5), por isso REAL. Toda linha de notas tem uma nota: uma prova que não aconteceu simplesmente não tem linha.
PRIMARY KEY (aluno_id, disciplina_id, bimestre)
Uma chave de três colunas: o mesmo aluno, na mesma matéria, no mesmo bimestre, só pode ter uma nota. Nenhuma das três sozinha identifica a linha (aula PRIMARY KEY).
REFERENCES alunos(id), REFERENCES disciplinas(id)
As chaves estrangeiras: não dá para lançar nota de um aluno que não existe (aula FOREIGN KEY).
INSERT INTO notas VALUES (1,1,1,8.5), …
Cada parêntese é aluno, disciplina, bimestre e nota, nessa ordem. A linha do Felipe (aluno 4) tem um buraco de propósito: ele faltou à prova de Português do 2º bimestre.
WHERE a.id = 1
A consulta mostra só as 6 notas da Bianca. São 29 notas no total: 5 alunos × 3 matérias × 2 bimestres seriam 30, menos a prova que o Felipe perdeu.

Missão: a coordenação pediu as notas de Matemática do 2º bimestre de toda a turma. Troque o filtro da consulta: mostre o nome do aluno e a nota, da maior para a menor.

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina, n.bimestre, n.nota
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
WHERE a.id = 1;

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, n.nota
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
WHERE d.nome = 'Matemática' AND n.bimestre = 2
ORDER BY n.nota DESC;

Filtrar por d.nome = 'Matemática' ou por n.disciplina_id = 1 dá o mesmo resultado. Pelo nome fica mais legível; pelo número não quebra se alguém renomear a disciplina para "Matemática Básica".

Versão 2: as médias

A média de cada aluno em cada matéria é a média das notas dos bimestres. É um AVG agrupado por duas coisas, aluno e disciplina (aulas AVG() e Agrupando por Mais de uma Coluna):

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina,
       AVG(n.nota) AS media,
       COUNT(*)    AS provas
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
GROUP BY a.id, d.id;
GROUP BY a.id, d.id
Um grupo para cada par aluno + disciplina: 5 × 3 = 15 grupos, 15 linhas.
AVG(n.nota)
A média das notas do grupo. A Bianca tirou 8.5 e 9.0 em Matemática: média 8.75.
COUNT(*) AS provas
Quantas notas entraram na média. Repare no Felipe em Português: 1 prova só, e média 6.0. O AVG não sabe que faltou uma; ele divide pelo que tem. A versão 5 trata disso.

Missão: a Lúcia quer a média geral de cada aluno, juntando todas as matérias: uma linha por aluno, com o nome e a média arredondada em 2 casas (aula Funções Numéricas), da maior para a menor.

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina, AVG(n.nota) AS media
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
GROUP BY a.id, d.id;

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, ROUND(AVG(n.nota), 2) AS media
FROM notas n
JOIN alunos a ON a.id = n.aluno_id
GROUP BY a.id
ORDER BY media DESC;

A Bianca lidera, com 8.5. Um detalhe que vale uma conversa com a coordenação: a média do Felipe sai 6.1 porque o AVG fez a média das 5 notas que ele tem. Se a regra da escola for "média das médias de cada matéria", a conta é outra, e dá 6.08. Os dois números são "a média do Felipe"; o SQL faz qualquer um, mas alguém precisa dizer qual é o oficial.

Versão 3: aprovado, recuperação ou reprovado

A regra da escola: média 7 ou mais, aprovado; de 5 até menos de 7, recuperação; abaixo de 5, reprovado. Transformar número em palavra, por faixas, é o trabalho do CASE (aula CASE):

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina,
       AVG(n.nota) AS media,
       CASE
           WHEN AVG(n.nota) >= 7 THEN 'aprovado'
           WHEN AVG(n.nota) >= 5 THEN 'recuperação'
           ELSE 'reprovado'
       END AS situacao
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
GROUP BY a.id, d.id;
CASE WHEN … THEN … END
Testa as condições de cima para baixo e para na primeira verdadeira.
WHEN AVG(n.nota) >= 7
A primeira faixa. Quem tem 7 ou mais para aqui.
WHEN AVG(n.nota) >= 5
Só chega aqui quem tem menos de 7, então não precisa escrever "e menor que 7": a ordem já garante.
ELSE 'reprovado'
Quem não passou em nenhuma condição: menos de 5.

Repare na Eduarda em Matemática e Ciências: média 7.0, exatamente no limite, e aprovada, por causa do >=. Com > ela iria para a recuperação. Em regra de nota, o sinal é tudo.

Missão: a lista que a coordenação quer ver é a dos que não passaram direto: só as linhas em recuperação ou reprovado. O filtro é sobre uma média, então vai no HAVING (aula HAVING). Mantenha as colunas aluno, disciplina, media e situacao.

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina,
       AVG(n.nota) AS media,
       CASE
           WHEN AVG(n.nota) >= 7 THEN 'aprovado'
           WHEN AVG(n.nota) >= 5 THEN 'recuperação'
           ELSE 'reprovado'
       END AS situacao
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
GROUP BY a.id, d.id;

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina,
       AVG(n.nota) AS media,
       CASE
           WHEN AVG(n.nota) >= 7 THEN 'aprovado'
           WHEN AVG(n.nota) >= 5 THEN 'recuperação'
           ELSE 'reprovado'
       END AS situacao
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
GROUP BY a.id, d.id
HAVING AVG(n.nota) < 7;

Cinco casos: o Diego em Matemática (recuperação) e Ciências (reprovado), o Felipe em Matemática (reprovado) e Português (recuperação), e a Gabriela em Ciências (recuperação). Também funciona HAVING situacao <> 'aprovado', usando o apelido; o SQLite aceita apelido no HAVING, mas nem todo banco aceita, e o AVG(n.nota) < 7 funciona em todos.

Versão 4: o boletim em colunas

A família não quer 15 linhas; quer o boletim de sempre, uma linha por aluno e uma coluna por matéria. O truque é pôr um CASE dentro do AVG: em cada coluna, só as notas daquela matéria entram na conta.

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno,
       AVG(CASE WHEN n.disciplina_id = 1 THEN n.nota END) AS matematica,
       AVG(CASE WHEN n.disciplina_id = 2 THEN n.nota END) AS portugues
FROM notas n
JOIN alunos a ON a.id = n.aluno_id
GROUP BY a.id;
CASE WHEN n.disciplina_id = 1 THEN n.nota END
Para uma nota de Matemática, devolve a nota. Para qualquer outra, como não há ELSE, devolve NULL.
AVG( … )
O AVG ignora os NULL. Então ele faz a média só das notas de Matemática do aluno. É esse "ignorar NULL" que faz o truque funcionar (aula O Valor NULL).
GROUP BY a.id
Agora o grupo é só o aluno: uma linha cada.

Esse formato se chama tabela cruzada (ou pivot): as linhas de uma coluna viram colunas. Alguns bancos têm um comando próprio para isso; o CASE dentro da agregação funciona em todos.

Missão: o boletim está sem Ciências (disciplina 3). Acrescente a coluna ciencias e arredonde as três médias em 1 casa decimal com ROUND(…, 1).

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno,
       AVG(CASE WHEN n.disciplina_id = 1 THEN n.nota END) AS matematica,
       AVG(CASE WHEN n.disciplina_id = 2 THEN n.nota END) AS portugues
FROM notas n
JOIN alunos a ON a.id = n.aluno_id
GROUP BY a.id;

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno,
       ROUND(AVG(CASE WHEN n.disciplina_id = 1 THEN n.nota END), 1) AS matematica,
       ROUND(AVG(CASE WHEN n.disciplina_id = 2 THEN n.nota END), 1) AS portugues,
       ROUND(AVG(CASE WHEN n.disciplina_id = 3 THEN n.nota END), 1) AS ciencias
FROM notas n
JOIN alunos a ON a.id = n.aluno_id
GROUP BY a.id;

O preço desse formato: cada matéria nova pede uma linha nova no SELECT. O banco continua guardando uma nota por linha (fácil de crescer); só a apresentação é em colunas.

Versão 5: a prova que faltou

O Felipe faltou à prova de Português do 2º bimestre e não fez segunda chamada. A regra da escola: prova não feita vale zero. Mas o AVG não enxerga o que não existe: para ele, o Felipe tem média 6.0 em Português, com uma prova só.

Primeiro, é preciso achar as provas que faltam. Para isso, monte a lista de todas as provas que deveriam existir (cada aluno × cada disciplina × cada bimestre) e veja quais não têm nota. O CROSS JOIN faz todas as combinações (aula O que é um JOIN):

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
WITH bimestres(b) AS (VALUES (1), (2))
SELECT a.nome AS aluno, d.nome AS disciplina, bimestres.b AS bimestre
FROM alunos a
CROSS JOIN disciplinas d
CROSS JOIN bimestres
LEFT JOIN notas n
       ON n.aluno_id = a.id
      AND n.disciplina_id = d.id
      AND n.bimestre = bimestres.b
WHERE n.nota IS NULL;
WITH bimestres(b) AS (VALUES (1), (2))
Uma tabelinha provisória de uma coluna, b, com os valores 1 e 2. O VALUES cria linhas sem precisar de CREATE TABLE (aula CTE (WITH)).
alunos CROSS JOIN disciplinas CROSS JOIN bimestres
Todas as combinações: 5 × 3 × 2 = 30 provas que deveriam existir.
LEFT JOIN notas n ON … AND … AND …
Para cada prova esperada, procura a nota. As três condições ficam no ON, para não sumir com ninguém (aula LEFT JOIN).
WHERE n.nota IS NULL
Fica só com as provas sem nota.

Uma linha: Felipe Araújo, Português, 2º bimestre. Agora, com a mesma grade completa, a falta pode virar zero: COALESCE(n.nota, 0) devolve a nota quando ela existe, e 0 quando é NULL (aula IFNULL e COALESCE).

Missão: calcule as médias do Felipe (aluno 4) em cada disciplina com a regra da escola: prova que falta vale zero. Use a grade completa da caixa, troque o WHERE por WHERE a.id = 4, agrupe por disciplina e faça a média de COALESCE(n.nota, 0). Mostre aluno, disciplina e media.

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
WITH bimestres(b) AS (VALUES (1), (2))
SELECT a.nome AS aluno, d.nome AS disciplina, bimestres.b AS bimestre
FROM alunos a
CROSS JOIN disciplinas d
CROSS JOIN bimestres
LEFT JOIN notas n
       ON n.aluno_id = a.id
      AND n.disciplina_id = d.id
      AND n.bimestre = bimestres.b
WHERE n.nota IS NULL;

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
WITH bimestres(b) AS (VALUES (1), (2))
SELECT a.nome AS aluno, d.nome AS disciplina,
       AVG(COALESCE(n.nota, 0)) AS media
FROM alunos a
CROSS JOIN disciplinas d
CROSS JOIN bimestres
LEFT JOIN notas n
       ON n.aluno_id = a.id
      AND n.disciplina_id = d.id
      AND n.bimestre = bimestres.b
WHERE a.id = 4
GROUP BY a.id, d.id;

Em Português, a média do Felipe cai de 6.0 para 3.0: de recuperação para reprovado. Uma decisão de regra (zero ou "não conta") mudou a situação do aluno; por isso essa regra tem de estar escrita, e o SQL tem de seguir a escrita. Compare as três formas de tratar a falta:

RegraSQLFelipe em Português
A falta não conta (média do que existe)AVG(n.nota) direto em notas6.0 (recuperação)
A falta vale zerograde completa + AVG(COALESCE(n.nota, 0))3.0 (reprovado)
Só mostrar quem tem faltagrade completa + WHERE n.nota IS NULLaparece na lista

Versão 6: o melhor de cada disciplina

Na festa de fim de ano, a escola premia a maior média de cada matéria. "A maior" de cada grupo é um ranking que recomeça em cada disciplina: RANK() OVER (PARTITION BY …) (aulas PARTITION BY e ROW_NUMBER, RANK e DENSE_RANK).

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
WITH medias AS (
    SELECT a.nome AS aluno, d.nome AS disciplina, AVG(n.nota) AS media
    FROM notas n
    JOIN alunos a      ON a.id = n.aluno_id
    JOIN disciplinas d ON d.id = n.disciplina_id
    GROUP BY a.id, d.id
)
SELECT disciplina, aluno, media,
       RANK() OVER (PARTITION BY disciplina ORDER BY media DESC) AS posicao
FROM medias
ORDER BY disciplina, posicao;
WITH medias AS ( … )
As 15 médias da versão 2, com nome.
PARTITION BY disciplina
Divide as linhas em três "turmas" de ranking, uma por disciplina. A contagem volta a 1 em cada uma.
ORDER BY media DESC
Dentro de cada disciplina, a maior média fica com o 1.

Missão: deixe só os premiados: uma linha por disciplina, com disciplina, aluno e media de quem ficou em 1º lugar. O WHERE não enxerga o RANK da mesma consulta; ponha o ranking dentro de mais um WITH (ou de uma subconsulta) e filtre posicao = 1 por fora.

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
WITH medias AS (
    SELECT a.nome AS aluno, d.nome AS disciplina, AVG(n.nota) AS media
    FROM notas n
    JOIN alunos a      ON a.id = n.aluno_id
    JOIN disciplinas d ON d.id = n.disciplina_id
    GROUP BY a.id, d.id
)
SELECT disciplina, aluno, media,
       RANK() OVER (PARTITION BY disciplina ORDER BY media DESC) AS posicao
FROM medias
ORDER BY disciplina, posicao;

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
WITH medias AS (
    SELECT a.nome AS aluno, d.nome AS disciplina, AVG(n.nota) AS media
    FROM notas n
    JOIN alunos a      ON a.id = n.aluno_id
    JOIN disciplinas d ON d.id = n.disciplina_id
    GROUP BY a.id, d.id
),
ranking AS (
    SELECT disciplina, aluno, media,
           RANK() OVER (PARTITION BY disciplina ORDER BY media DESC) AS posicao
    FROM medias
)
SELECT disciplina, aluno, media
FROM ranking
WHERE posicao = 1;

A Gabriela leva Matemática (9.75), a Eduarda, Português (9.25), e a Bianca, Ciências (9.25). Se dois alunos empatassem na maior média, o RANK daria 1 aos dois, e os dois apareceriam: é justo com quem empatou. Com ROW_NUMBER(), só um sairia, escolhido sem critério.

Versão 7: notas que o banco não aceita

Um erro de digitação clássico: a Lúcia quis lançar 7.5 e digitou 75. O banco de hoje aceita, e a média da Bianca em Matemática vira 41.75. Uma regra CHECK faz o banco recusar qualquer nota fora de 0 a 10 (aula CHECK).

Missão: rode como está: o 75 entra, e a média da Bianca em Matemática fica absurda. Acrescente CHECK (nota BETWEEN 0 AND 10) à coluna nota da tabela e rode de novo: o banco tem de recusar o 75 (aula BETWEEN).

CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL,
    disciplina_id INTEGER NOT NULL,
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES (1, 1, 1, 8.5);
INSERT INTO notas VALUES (1, 1, 2, 75);   -- era para ser 7.5

SELECT aluno_id, disciplina_id, AVG(nota) AS media
FROM notas
GROUP BY aluno_id, disciplina_id;

CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL,
    disciplina_id INTEGER NOT NULL,
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL CHECK (nota BETWEEN 0 AND 10),
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES (1, 1, 1, 8.5);
INSERT INTO notas VALUES (1, 1, 2, 75);   -- era para ser 7.5

SELECT aluno_id, disciplina_id, AVG(nota) AS media
FROM notas
GROUP BY aluno_id, disciplina_id;

A mensagem é CHECK constraint failed: nota BETWEEN 0 AND 10. De quebra, essa mesma regra barra outro erro, que você vai ver a seguir: a nota digitada com vírgula.

Quando dá errado

A nota com vírgula

Na planilha, a Lúcia escreve "7,5". Copiando para o SQL, sem a regra da versão 7:

CREATE TABLE notas (aluno_id INTEGER, nota REAL NOT NULL);
INSERT INTO notas VALUES (1, '7,5'), (1, 8.0);

SELECT nota, typeof(nota) AS tipo FROM notas;
SELECT SUM(nota) AS soma FROM notas;

O que aconteceu: sem mensagem nenhuma, '7,5' ficou guardado como texto (typeof diz text): com vírgula, o SQLite não reconhece um número e guarda o que recebeu (aula Os Tipos no SQLite). Nas contas, ele aproveita só o começo do texto, o 7. A soma deu 15.0 em vez de 15.5.

Como consertar: em SQL, o separador decimal é sempre o ponto: 7.5. E o CHECK (nota BETWEEN 0 AND 10) da versão 7 recusa o texto '7,5' também, porque, para o SQLite, um texto nunca está "entre" dois números.

A média que perdeu a metade

Na planilha antiga, havia uma coluna para cada bimestre, e a média se fazia "na mão": (nota1 + nota2) / 2. Com notas inteiras:

SELECT (7 + 8) / 2   AS inteiros,
       (7 + 8) / 2.0 AS com_ponto,
       CAST(7 + 8 AS REAL) / 2 AS com_cast;

O que aconteceu: (7 + 8) / 2 deu 7, não 7.5. Quando os dois lados da divisão são inteiros, o SQLite faz divisão inteira e joga a parte decimal fora, sem avisar. Um aluno com 7 e 8 ficaria com 7, no limite, quando merecia 7.5.

Como consertar: divida por 2.0, converta com CAST(… AS REAL), ou use AVG, que sempre devolve número com decimais. Guardar a nota como REAL, como neste projeto, já evita o problema na maioria das contas (aula Funções Numéricas).

A nota lançada duas vezes 
-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a Lúcia relançou a nota da Bianca, de Matemática, 1º bimestre
INSERT INTO notas VALUES (1, 1, 1, 6.0);

O que aconteceu: UNIQUE constraint failed: notas.aluno_id, notas.disciplina_id, notas.bimestre. A chave de três colunas fez o trabalho: já existe nota da Bianca em Matemática no 1º bimestre.

Como consertar: se a ideia era corrigir a nota, é um UPDATE: UPDATE notas SET nota = 6.0 WHERE aluno_id = 1 AND disciplina_id = 1 AND bimestre = 1; (aula UPDATE). Sem a chave, a nota repetida entraria, e a média viraria a de três provas.

Agrupou por aluno e mostrou a disciplina 
-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome, d.nome AS disciplina, ROUND(AVG(n.nota), 2) AS media
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
GROUP BY a.id;

O que aconteceu: a tabela diz "Matemática" em todas as linhas, mas as médias são as gerais, das três matérias. O GROUP BY é só por aluno; a coluna disciplina ficou "solta", e o SQLite pôs um valor qualquer do grupo. Ninguém avisou, e o resultado mente.

Como consertar: toda coluna do SELECT fora de uma agregação tem de estar no GROUP BY. Ou agrupe por a.id, d.id (média por matéria), ou tire a coluna disciplina (média geral). O PostgreSQL e o SQL Server recusariam esta consulta com erro.

Nos outros bancos

A divisão inteira não é só do SQLite: o PostgreSQL e o SQL Server também fazem 15 / 2 = 7 com inteiros. O MySQL é a exceção: lá, / sempre dá decimal (7.5000), e a divisão inteira tem operador próprio, DIV. A tabela cruzada com CASE dentro do AVG funciona nos quatro; o SQL Server tem também o operador PIVOT, e o PostgreSQL, a extensão tablefunc. Mais em As Diferenças entre os Bancos.

Referência rápida

Tarefa da escolaSQL
Uma nota por aluno, matéria e bimestrePRIMARY KEY (aluno_id, disciplina_id, bimestre)
Média por aluno e matériaAVG(n.nota) … GROUP BY a.id, d.id
Situação por faixasCASE WHEN AVG(n.nota) >= 7 THEN 'aprovado' WHEN … ELSE … END
Filtrar pela médiaHAVING AVG(n.nota) < 7
Boletim em colunasAVG(CASE WHEN n.disciplina_id = 1 THEN n.nota END) AS matematica
Todas as provas esperadasalunos CROSS JOIN disciplinas CROSS JOIN bimestres
Falta vale zeroAVG(COALESCE(n.nota, 0))
O melhor de cada matériaRANK() OVER (PARTITION BY disciplina ORDER BY media DESC)
Nota só de 0 a 10nota REAL NOT NULL CHECK (nota BETWEEN 0 AND 10)
Dividir sem perder decimais(a + b) / 2.0 ou AVG

Exercícios

1. A coluna do boletim

Complete para a coluna de Português (disciplina 2) do boletim:

AVG(CASE WHEN n.disciplina_id = 2 n.nota END) AS portugues

2. Sem perder o meio ponto

Complete para a divisão dar 7.5:

SELECT (7 + 8) / ;

3. Por que a tabela notas guarda uma nota por linha, em vez de uma coluna para cada matéria e bimestre?

4. O CASE tem WHEN media >= 7 THEN 'aprovado' e depois WHEN media >= 5 THEN 'recuperação'. Que situação ganha uma média 8?

5. O Felipe tem uma nota 6.0 em Português e faltou à outra prova. Quanto dá AVG(n.nota) direto na tabela notas?

6. Que valor fica guardado se você inserir '7,5' numa coluna REAL sem CHECK?

Desafio: o resultado final da turma

Missão: a direção quer um número só por situação: quantas médias (aluno + matéria) ficaram em cada uma. Monte uma consulta com as colunas situacao e quantos, uma linha por situação. Dica: ponha a consulta da versão 3 num WITH e agrupe por situacao do lado de fora.

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
SELECT a.nome AS aluno, d.nome AS disciplina, AVG(n.nota) AS media
FROM notas n
JOIN alunos a      ON a.id = n.aluno_id
JOIN disciplinas d ON d.id = n.disciplina_id
GROUP BY a.id, d.id;

-- montagem da escola (igual em toda caixa)
CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO alunos VALUES (1, 'Bianca Reis'), (2, 'Diego Martins'),
    (3, 'Eduarda Lopes'), (4, 'Felipe Araújo'), (5, 'Gabriela Sá');
CREATE TABLE disciplinas (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO disciplinas VALUES (1, 'Matemática'), (2, 'Português'), (3, 'Ciências');
CREATE TABLE notas (
    aluno_id      INTEGER NOT NULL REFERENCES alunos(id),
    disciplina_id INTEGER NOT NULL REFERENCES disciplinas(id),
    bimestre      INTEGER NOT NULL,
    nota          REAL NOT NULL,
    PRIMARY KEY (aluno_id, disciplina_id, bimestre)
);
INSERT INTO notas VALUES
(1,1,1,8.5), (1,1,2,9.0), (1,2,1,7.0), (1,2,2,8.0), (1,3,1,9.5), (1,3,2,9.0),
(2,1,1,5.0), (2,1,2,6.0), (2,2,1,6.5), (2,2,2,7.5), (2,3,1,4.0), (2,3,2,5.0),
(3,1,1,7.0), (3,1,2,7.0), (3,2,1,9.0), (3,2,2,9.5), (3,3,1,8.0), (3,3,2,6.0),
(4,1,1,3.5), (4,1,2,5.5), (4,2,1,6.0),               (4,3,1,7.5), (4,3,2,8.0),
(5,1,1,10.0),(5,1,2,9.5), (5,2,1,8.5), (5,2,2,9.0), (5,3,1,6.5), (5,3,2,7.0);
-- a consulta
WITH situacoes AS (
    SELECT CASE
               WHEN AVG(n.nota) >= 7 THEN 'aprovado'
               WHEN AVG(n.nota) >= 5 THEN 'recuperação'
               ELSE 'reprovado'
           END AS situacao
    FROM notas n
    GROUP BY n.aluno_id, n.disciplina_id
)
SELECT situacao, COUNT(*) AS quantos
FROM situacoes
GROUP BY situacao;

De 15 médias, 10 aprovadas, 3 em recuperação e 2 reprovadas. Com a regra da falta valendo zero (versão 5), seria uma recuperação a menos e uma reprovação a mais.

Resumo

  • Guarde uma nota por linha (aluno, disciplina, bimestre, nota), com chave primária nas três primeiras colunas. O formato "uma coluna por matéria" fica para a consulta.
  • AVG com GROUP BY a.id, d.id dá a média por matéria; só GROUP BY a.id, a média geral. Toda coluna fora da agregação vai no GROUP BY.
  • CASE WHEN … THEN … END transforma a média em situação; a ordem dos WHEN e o sinal (>= ou >) decidem os casos de limite.
  • O boletim em colunas é AVG(CASE WHEN … THEN nota END), que funciona porque AVG ignora NULL.
  • O que não existe não entra no AVG: para achar e contar a prova que faltou, monte a grade com CROSS JOIN, junte com LEFT JOIN e decida com COALESCE.
  • Proteja as notas com CHECK (nota BETWEEN 0 AND 10), use ponto como separador decimal e cuidado com a divisão de inteiros: 15 / 2 = 7.