f3k1yVTq

1import sqlite3
2
3conn = sqlite3.connect('coursesdb.sqlite')
4cur = conn.cursor()
5
6cur.executescript('''
7CREATE TABLE IF NOT EXISTS User (
8  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
9  name TEXT,
10  email TEXT
11);
12
13CREATE TABLE IF NOT EXISTS Course (
14  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
15  title TEXT
16);
17
18CREATE TABLE IF NOT EXISTS Member (
19  user_id INTEGER,
20  course_id INTEGER,
21  role INTEGER,
22  PRIMARY KEY (user_id, course_id)
23)
24
25''')
26
27cur.execute("INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org')")
28cur.execute("INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org')")
29cur.execute("INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org')")
30
31cur.execute("INSERT INTO Course (title) VALUES ('Python')")
32cur.execute("INSERT INTO Course (title) VALUES ('SQL')")
33cur.execute("INSERT INTO Course (title) VALUES ('PHP')")
34
35cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1)")
36cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0)")
37cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0)")
38
39cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0)")
40cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1)")
41
42cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1)")
43cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0)")
44
45conn.commit()
46
47print(list(cur.execute('''
48SELECT User.name, Member.role, Course.title
49FROM User JOIN Member JOIN Course
50ON Member.user_id = User.id AND Member.course_id = Course.id
51ORDER BY Course.title, Member.role DESC, User.name
52''')))
53
54conn.close()