def get_mentors_and_schools():
query = """SELECT mentors.first_name, mentors.last_name, schools.name, schools.country
FROM mentors
JOIN schools ON mentors.city = schools.city
ORDER BY mentors.id;"""
result = database.handle_data(query)
return result
def get_contacts():
query = """SELECT schools.name, mentors.first_name, mentors.last_name
FROM mentors
RIGHT JOIN schools ON mentors.id = schools.contact_person
ORDER BY schools.name;"""
result = database.handle_data(query)
return result
def get_applicants_by_creation():
query = """SELECT applicants.first_name, applicants.application_code, applicants_mentors.creation_date
FROM applicants
JOIN applicants_mentors ON applicants.id = applicants_mentors.applicant_id
WHERE applicants_mentors.creation_date > '2016-01-01'
ORDER BY applicants_mentors.creation_date DESC;"""
result = database.handle_data(query)
return result
def get_mentors_by_country():
query = """SELECT schools.country, COUNT(mentors.id) AS mentor_count
FROM mentors
JOIN schools ON mentors.city = schools.city
GROUP BY schools.country
ORDER BY schools.country;"""
result = database.handle_data(query)
return result
def get_applicants_and_mentors():
query = """SELECT a.first_name AS applicant_first, a.application_code,
m.first_name AS mentor_first , m.last_name AS mentor_last
FROM applicants a
LEFT JOIN applicants_mentors am ON am.applicant_id = a.id
LEFT JOIN mentors m ON m.id = am.mentor_id
ORDER BY a.id;"""
result = database.handle_data(query)
return result