-- Contoh praktis: rekap nilai per mahasiswa
SELECT 
    m.nim,
    m.nama,
    COUNT(n.id) as jumlah_matkul,
    AVG(n.nilai) as rata_rata,
    CASE 
        WHEN AVG(n.nilai) >= 80 THEN 'A'
        WHEN AVG(n.nilai) >= 70 THEN 'B'
        ELSE 'C'
    END as grade
FROM mahasiswa m
LEFT JOIN nilai n ON m.id = n.mahasiswa_id
GROUP BY m.id, m.nim, m.nama
ORDER BY rata_rata DESC;