import sqlite3

conn = sqlite3.connect('coursesdb.sqlite')
cur = conn.cursor()

cur.executescript('''
CREATE TABLE IF NOT EXISTS User (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  name TEXT,
  email TEXT
);

CREATE TABLE IF NOT EXISTS Course (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  title TEXT
);

CREATE TABLE IF NOT EXISTS Member (
  user_id INTEGER,
  course_id INTEGER,
  role INTEGER,
  PRIMARY KEY (user_id, course_id)
)

''')

cur.execute("INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org')")
cur.execute("INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org')")
cur.execute("INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org')")

cur.execute("INSERT INTO Course (title) VALUES ('Python')")
cur.execute("INSERT INTO Course (title) VALUES ('SQL')")
cur.execute("INSERT INTO Course (title) VALUES ('PHP')")

cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1)")
cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0)")
cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0)")

cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0)")
cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1)")

cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1)")
cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0)")

conn.commit()

print(list(cur.execute('''
SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name
''')))

conn.close()