def ml_teachers_with_salary():
df = pd.read_sql(
"""SELECT name, surname, t.id, country, speciality, salary
FROM teachers AS t
WHERE t.speciality == "ML"
AND t.salary > 1200
""", dbi.get_conn()).reset_index(drop=True)
print("\nML TEACHERS OVERS 1200\n", df.to_string())
def students_with_NLP_projects():
df = pd.read_sql(
"""SELECT name, surname, s.id, country, nameP, topic, grade, tch
FROM students AS s, projects AS p
WHERE s.id = p.st
AND p.topic = "NLP"
""", dbi.get_conn()).reset_index(drop=True)
print("\nSTUDENTS WTIH NLP PROJECTS\n", df.to_string())
def wrong_teachers():
print(
"\n WRONG TEACHERS:\n",
pd.read_sql(
"""SELECT s.name, s.surname, p.topic, t.name, t.surname, t.speciality
FROM students AS s, projects AS p, teachers AS t
WHERE s.id = p.st
AND p.tch = t.id
AND t.speciality != p.topic""",
dbi.get_conn()).to_string())
def fill_tables():
projects.to_sql("projects", dbi.get_conn(), if_exists='replace')
students.to_sql("students", dbi.get_conn(), if_exists='replace')
teachers.to_sql("teachers", dbi.get_conn(), if_exists='replace')
def join_tables():
df = pd.read_sql(
"""SELECT * FROM students
INNER JOIN projects
ON students.id = projects.st """, dbi.get_conn())
print(df.to_string())