def all_school():
'''Renders the query result with the following data:
- name of mentor
- name of school
- country of school
Included schools without mentor
Oredered by "mentors.id"'''
query = """
SELECT mentors.first_name AS "Mentor First Name",
mentors.last_name AS "Mentor Last Name",
schools.name AS "School",
schools.city AS "City"
FROM mentors
RIGHT JOIN schools
ON mentors.city = schools.city
ORDER BY mentors.id;
"""
query_result = functions.database_query(query)
return render_template(
'query_result.html',
query_result=query_result,
)
def applicants():
'''Renders the query result with the following data:
- first name of applicant
- application code of applicant
- date of application creation
Only for applications later than 2016-01-01
Oredered by "applicants_mentors.creation_date" in descending'''
query = """
SELECT applicants.first_name AS "Applicant First Name",
applicants.application_code AS "Applicant Code",
applicants_mentors.creation_date AS "Application Date"
FROM applicants
INNER JOIN applicants_mentors
ON applicants.id = applicants_mentors.applicant_id
WHERE creation_date > '2016-01-01' OR creation_date ISNULL
ORDER BY applicants_mentors.creation_date DESC;
"""
query_result = functions.database_query(query)
return render_template(
'query_result.html',
query_result=query_result,
)
def appliciants_and_mentors():
'''Renders the query result with the following data:
- first name of applicant
- application code of applicant
- name of assigned mentor
Included applicants without assigned mentor
Oredered by "applicants.id"'''
query = """
SELECT applicants.first_name AS "Applicant First Name",
applicants.application_code AS "Apllicant Code",
mentors.first_name AS "Mentor First Name",
mentors.last_name AS "Mentor Last Name"
FROM applicants
FULL JOIN applicants_mentors
ON applicants.id = applicants_mentors.applicant_id
LEFT JOIN mentors
ON applicants_mentors.mentor_id = mentors.id
ORDER BY applicants.id ASC
"""
query_result = functions.database_query(query)
return render_template(
'query_result.html',
query_result=query_result,
)
def mentors_by_country():
'''Renders the query result with the following data:
- country
- number of mentors (by country)
Oredered by "schools.country"'''
query = """
SELECT schools.country AS "Country",
COUNT(mentors.id) AS "Number of Mentors"
FROM mentors
FULL JOIN schools
ON mentors.city = schools.city
GROUP BY schools.country
ORDER BY schools.country;
"""
query_result = functions.database_query(query)
return render_template(
'query_result.html',
query_result=query_result,
)
def contacts():
'''Renders the query result with the following data:
- name of school
- name of contact person
Oredered by "schools.country"'''
query = """
SELECT schools.name AS "School",
mentors.first_name AS "Contact First Name",
mentors.last_name "Contact Last Name"
FROM mentors
INNER JOIN schools
ON mentors.id = schools.contact_person
ORDER BY name ASC;
"""
query_result = functions.database_query(query)
return render_template(
'query_result.html',
query_result=query_result,
)