import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE lop(ma TEXT PRIMARY KEY);
        CREATE TABLE dang_ky(id INTEGER PRIMARY KEY, ma_lop TEXT);
        INSERT INTO lop VALUES ('A'), ('B'), ('C');
        INSERT INTO dang_ky VALUES (1, 'A'), (2, 'A'), (3, 'B');
    """)
    ket_qua = db.execute("""
        SELECT l.ma, COUNT(d.id)
        FROM lop AS l LEFT JOIN dang_ky AS d ON d.ma_lop = l.ma
        GROUP BY l.ma
        ORDER BY l.ma
    """).fetchall()
assert ket_qua == [("A", 2), ("B", 1), ("C", 0)]
print(ket_qua)