Notas de Alunos (com Banco de Dados)

1 Script do banco — banco.sql

CREATE DATABASE IF NOT EXISTS escola
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE escola;

CREATE TABLE IF NOT EXISTS alunos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    turma VARCHAR(30) NOT NULL,
    matricula VARCHAR(20) UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    aluno_id INT NOT NULL,
    disciplina VARCHAR(60) NOT NULL,
    nota DECIMAL(4,2) NOT NULL,
    bimestre INT NOT NULL,
    FOREIGN KEY (aluno_id) REFERENCES alunos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO alunos (nome, turma, matricula) VALUES
('Ana Silva', '1º Ano A', '2024001'),
('Bruno Costa', '1º Ano A', '2024002'),
('Carla Dias', '1º Ano A', '2024003'),
('Daniel Lima', '1º Ano B', '2024004');

INSERT INTO notas (aluno_id, disciplina, nota, bimestre) VALUES
(1, 'Matemática', 8.5, 1),
(1, 'Matemática', 9.0, 2),
(1, 'Português', 7.5, 1),
(1, 'Português', 8.0, 2),
(2, 'Matemática', 6.0, 1),
(2, 'Matemática', 7.0, 2),
(2, 'Português', 8.5, 1),
(2, 'Português', 9.0, 2),
(3, 'Matemática', 9.5, 1),
(3, 'Matemática', 10.0, 2);

2 Arquivo config.php

<?php
define('DB_HOST', 'localhost'); //MySQL Hostname//
define('DB_USER', 'root'); //MySQL Username//
define('DB_PASS', ''); //MySQL Password//
define('DB_NAME', 'escola'); //Database Name//

$conexao = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

if ($conexao->connect_error) {
    die("Erro: " . $conexao->connect_error);
}
$conexao->set_charset('utf8mb4');
?>

3 Arquivo index.php

<?php
require_once 'config.php';

// ==========================================================
// QUERY COM JOIN + GROUP BY + AVG
// ==========================================================
$sql = "SELECT 
            a.id,
            a.nome,
            a.turma,
            a.matricula,
            COUNT(n.id) AS total_notas,
            ROUND(AVG(n.nota), 2) AS media_geral
        FROM alunos a
        LEFT JOIN notas n ON n.aluno_id = a.id
        GROUP BY a.id, a.nome, a.turma, a.matricula
        ORDER BY media_geral DESC";

$alunos = $conexao->query($sql)->fetch_all(MYSQLI_ASSOC);

// Média geral da escola
$media_escola = $conexao->query(
    "SELECT ROUND(AVG(nota), 2) AS media FROM notas"
)->fetch_assoc()['media'];

// Total de notas lançadas
$total_notas = $conexao->query(
    "SELECT COUNT(*) AS total FROM notas"
)->fetch_assoc()['total'];
?>
<!DOCTYPE html>
<html lang="pt-BR">
<head>
    <meta charset="UTF-8">
    <title>Sistema de Notas</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <div class="container">
        <h1>🎓 Sistema de Notas</h1>
        <p class="subtitle">Boletim dos alunos com médias calculadas</p>

        <div class="kpis">
            <div class="kpi">
                <span class="kpi-icon">👥</span>
                <div>
                    <div class="kpi-value"><?php echo count($alunos); ?></div>
                    <div class="kpi-label">Alunos</div>
                </div>
            </div>
            <div class="kpi">
                <span class="kpi-icon">📝</span>
                <div>
                    <div class="kpi-value"><?php echo $total_notas; ?></div>
                    <div class="kpi-label">Notas</div>
                </div>
            </div>
            <div class="kpi highlight">
                <span class="kpi-icon">📊</span>
                <div>
                    <div class="kpi-value"><?php echo $media_escola; ?></div>
                    <div class="kpi-label">Média Geral</div>
                </div>
            </div>
        </div>

        <div class="table-wrapper">
            <table class="table">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Nome</th>
                        <th>Turma</th>
                        <th>Matrícula</th>
                        <th>Notas</th>
                        <th>Média</th>
                        <th>Situação</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($alunos as $i => $aluno): 
                        $media = (float) $aluno['media_geral'];
                        if ($media >= 7) {
                            $situacao = 'Aprovado';
                            $cor = '#10b981';
                        } elseif ($media >= 5) {
                            $situacao = 'Recuperação';
                            $cor = '#f59e0b';
                        } else {
                            $situacao = 'Reprovado';
                            $cor = '#ef4444';
                        }
                    ?>
                        <tr>
                            <td><?php echo $i + 1; ?></td>
                            <td><strong><?php echo htmlspecialchars($aluno['nome']); ?></strong></td>
                            <td><?php echo htmlspecialchars($aluno['turma']); ?></td>
                            <td><?php echo htmlspecialchars($aluno['matricula']); ?></td>
                            <td><?php echo $aluno['total_notas']; ?></td>
                            <td>
                                <strong style="color: <?php echo $cor; ?>; font-size: 1.05rem;">
                                    <?php echo number_format($media, 2, ',', '.'); ?>
                                </strong>
                            </td>
                            <td>
                                <span class="badge" style="background: <?php echo $cor; ?>20; color: <?php echo $cor; ?>;">
                                    <?php echo $situacao; ?>
                                </span>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</body>
</html>
<?php $conexao->close(); ?>

4 Arquivo style.css

/* style.css - Sistema de Notas */

* { margin: 0; padding: 0; box-sizing: border-box; }

body {
    font-family: 'Segoe UI', sans-serif;
    background: #f8fafc;
    padding: 30px 20px;
    min-height: 100vh;
}

.container {
    max-width: 1000px;
    margin: 0 auto;
}

h1 {
    color: #1e293b;
    font-size: 1.8rem;
    margin-bottom: 6px;
}

.subtitle {
    color: #64748b;
    margin-bottom: 25px;
}

/* KPIs */
.kpis {
    display: grid;
    grid-template-columns: repeat(3, 1fr);
    gap: 15px;
    margin-bottom: 25px;
}

.kpi {
    background: white;
    padding: 20px;
    border-radius: 10px;
    border-left: 4px solid #6366f1;
    box-shadow: 0 2px 8px rgba(0,0,0,0.05);
    display: flex;
    align-items: center;
    gap: 15px;
}

.kpi.highlight {
    background: #6366f1;
    color: white;
    border-left-color: #fbbf24;
}

.kpi.highlight .kpi-value { color: #fbbf24; }
.kpi.highlight .kpi-label { color: rgba(255,255,255,0.8); }

.kpi-icon { font-size: 2rem; }

.kpi-value {
    font-size: 1.7rem;
    font-weight: 800;
    color: #6366f1;
    line-height: 1;
    margin-bottom: 4px;
}

.kpi-label {
    font-size: 0.72rem;
    color: #64748b;
    text-transform: uppercase;
    letter-spacing: 1px;
    font-weight: 600;
}

/* Tabela */
.table-wrapper {
    background: white;
    border-radius: 10px;
    overflow: hidden;
    box-shadow: 0 2px 10px rgba(0,0,0,0.06);
}

.table {
    width: 100%;
    border-collapse: collapse;
}

.table thead {
    background: #1e293b;
    color: white;
}

.table th {
    padding: 14px 16px;
    text-align: left;
    font-size: 0.75rem;
    text-transform: uppercase;
    letter-spacing: 0.5px;
}

.table td {
    padding: 14px 16px;
    border-bottom: 1px solid #e2e8f0;
    color: #1e293b;
}

.table tbody tr:last-child td {
    border-bottom: none;
}

.table tbody tr:hover {
    background: #f8fafc;
}

.badge {
    display: inline-block;
    padding: 5px 12px;
    border-radius: 12px;
    font-size: 0.78rem;
    font-weight: 700;
}

/* Responsivo */
@media (max-width: 700px) {
    .kpis { grid-template-columns: 1fr; }
    .table-wrapper { overflow-x: auto; }
    .table { min-width: 700px; }
}