import sqlite3

conn = sqlite3.connect('forum.sqlite')
cursor = conn.cursor()

cursor.execute('''SELECT name,sql FROM sqlite_master WHERE type = "table" ''')
print(cursor.fetchone())
print('\n')
print(cursor.fetchone())
print('\n')
print(cursor.fetchone())
print('\n')

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())



#3.4
print('\n\n\n3.4')
cursor.execute("SELECT * FROM topics")
print(cursor.fetchall())

#3.5
print('\n\n\n3.5')
cursor.execute("SELECT * FROM posts")
print(cursor.fetchall())

#3.6.1
print('\n\n\n3.6.1')
cursor.execute("SELECT topic_name,users.name,message FROM (users JOIN posts ON posts.id_author = users.id_user) JOIN topics ON posts.id_topic = topics.id_topic")
print(cursor.fetchall())

#3.6.2
print('\n\n\n3.6.2')
cursor.execute("SELECT name,COUNT(*) FROM users JOIN topics ON id_author = id_user GROUP BY name")
print(cursor.fetchall())

#3.7
print('\n\n\n3.7')
cursor.execute("SELECT name,COUNT(*) FROM users JOIN posts ON id_author = id_user GROUP BY name")
print(cursor.fetchall())

#3.8
print('\n\n\n3.8')
cursor.execute("SELECT * FROM (users JOIN posts ON posts.id_author = users.id_user) JOIN topics ON posts.id_topic = topics.id_topic")
print(cursor.fetchall())

conn.close()