import sqlite3
conn = sqlite3.connect('university.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
major TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);
''')
conn.commit()
conn.close()
cursor.execute('''
CREATE TABLE grades (
student_id INTEGER,
course_name TEXT NOT NULL,
grade REAL NOT NULL,
FOREIGN KEY(student_id) REFERENCES students(id)
);
''')
conn.commit()
cursor.execute('''
INSERT INTO students (name, major, email) VALUES (?, ?, ?);
''', ('Alice Wang', 'Computer Science', 'alice@university.edu'))
cursor.execute('''
INSERT INTO grades (student_id, course_name, grade) VALUES (?, ?, ?);
''', (1, 'Data Structures', 90.5))
conn.commit()
cursor.execute('''
SELECT s.name, g.course_name, g.grade FROM students s JOIN grades g ON s.id = g.student_id;
''')
results = cursor.fetchall()
for row in results:
print(row)