Python代写 - CSCA20 Lab 10 Advanced Features - Database
Objectives ● Practice nested queries ● Practice aggregate functions ● Practice joins File to submit: ***************Do not change the file name. You will get a 0 if you do so******************* Submit Data and starter code You will be using the same dataset used in lab9. You will find the starter code in Lecture 10 module to create and interact with the given datasets. The file contains several functions, and it is continuation of The create_table_region, create_table_country, print_tables functions are from your You can use your completed to complete these functions. The run_query function has been completed for you. You do not need to modify this function. Your task is to complete the remaining functions. Functions to complete Aggregate functions: get_total_stateless(db): The function takes db as input and returns the total count of stateless population in the world get_avg_stateless(db, region): The function returns the average of stateless population in given region in database db get_less_than_avg_country(db, region): Returns countries with population less than average population in that region Percent function: get_country_percentage(db, percent): Takes database db and percent as input. Returns countries with at least percent population of total population. For instance, if argument percent is 4, then the function should return countries that have at least 4% population of total stateless population. Nested function: get_country_lessthan_percent (db, percent): Returns Countries from the table StatelessCountByCountry such that countries have less than percent population of total population Hint: Use nested query Find out countries that do contain percent population of total population. Then exclude those countries using nested query. Refer to the example showed in class for nested query INNER JOIN: get_countries_percent_of_region(db, region, percent): Returns countries with population more than percent population for the given region. Hint: Use INNER JOIN to join both the tables StatelessCountByCountry and StatelessCountByRegion. Note: You are suggested (but not required) to use the function run_query() (which has already been implemented for you) in order to reduce the amount of repetitive copy-pasting. if __name__ == '__main__': This part of the file interacts with the database by calling all the functions you defined. The functions have been called for you. Make sure each function call prints the results it is supposed to. Submission Submit your file as on Quercus under Lecture 10 module.