"""Chapter 4 learner file: edit the SQL block, then rerun."""
import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("PRAGMA foreign_keys=ON")
assert connection.execute("PRAGMA foreign_keys").fetchone() == (1,)
connection.executescript("""
CREATE TABLE students (
    student_id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    program TEXT NOT NULL
);
CREATE TABLE courses (
    course_id TEXT NOT NULL PRIMARY KEY,
    title TEXT NOT NULL UNIQUE,
    credits INTEGER NOT NULL CHECK(credits > 0)
);
CREATE TABLE enrollments (
    enrollment_id TEXT NOT NULL PRIMARY KEY,
    student_id TEXT NOT NULL REFERENCES students(student_id),
    course_id TEXT NOT NULL REFERENCES courses(course_id),
    score INTEGER,
    UNIQUE(student_id, course_id)
);
INSERT INTO students VALUES
    ('S01','サリタ','国際ビジネス'),('S02','ラジャン','IT'),
    ('S03','ミン','観光'),('S04','教材用の学生','IT');
INSERT INTO courses VALUES
    ('C01','データベース基礎',2),('C02','表計算演習',1),('C03','情報倫理',1);
INSERT INTO enrollments VALUES
    ('E01','S01','C01',82),('E02','S02','C01',91),
    ('E03','S02','C02',78),('E04','S01','C03',NULL);
""")

# SQLを書く場所
sql = """SELECT COUNT(*) AS n_rows,
       COUNT(score) AS n_scored,
       SUM(score) AS score_sum,
       AVG(score) AS score_avg
FROM enrollments"""

params = ()
cursor = connection.execute(sql, params)
print("columns:", [item[0] for item in cursor.description])
for row in cursor.fetchall():
    print(row)
connection.close()