INF551 Homework #3: SQL Solved

Description

Please use Python 3.7 for all assignments.

We will use the “world” database (https://dev.mysql.com/doc/index-other.html) for this homework. Note that it is not the “world-x” database. Please download the SQL script from the above link. Create and populate a “world” database in your MySQL server (either on EC2 or locally on your laptop).

1. [40 points] Write a Python script “export.py” that connects to the “world” database on your MySQL server (using user name “inf551” and password “inf551”) and exports the tables in CSV files, one file per table. Name the file after the table: city.csv, country.csv, and countrylanguage.csv. The files should have the same formats as the ones provided to you in the first homework. In other words, it should have a header (1^st line) and followed by rows in the table, with columns quoted if values are strings and separated by comma. For example, here shows the content of city.csv.

 

# ID, Name, CountryCode, District, Population

‘1’, ‘Kabul’, ‘AFG’, ‘Kabol’, ‘1780000’

‘2’, ‘Qandahar’, ‘AFG’, ‘Qandahar’, ‘237500’

‘3’, ‘Herat’, ‘AFG’, ‘Herat’, ‘186800’

…

 

Note that your “export.py” should work in general, that is, it can export contents of databases other than “world”. Other databases will be used to test your “export.py”.

You are required to use the “information_schema” which stores the metadata about the databases (to see details, execute “use information_schema” and “show tables”). In particular, use “tables” and “columns” in this database to find out which tables the world database has and a list of columns for each table in that database.

Execution format: python export.py “world”

You may use python SQL connector library for this task.

Q1 Submission: export.py

 

2. [30 points] Write an SQL query/view for each of the following tasks.
   1. Find the 10 most populated cities in the United States (country name). Output names of such cities and their populations in the descending order of populations.
   2. Find name, continent, and population of countries whose name contains “united” and population is at least 1 million. (note the “like” operator in SQL is NOT case sensitive).

INF 551 – Spring 2020

1. Find names of countries which do not have any languages recorded (in the country language table).
   1. Using subquery
   2. Using outer join
2. Find names of countries which have at least 10 unofficial languages. Output the countries by the descending order of the number of such languages.
3. Create a view “LangCnt” that lists country code and the number of languages (both official and unofficial). Use the view to find the names of countries which has the largest number of both official and unofficial languages.
4. Find the top 10 languages by populations of countries where they are official languages, but only the countries whose populations are over 1 million are counted. For example, English may be spoked in 5 countries as official language and 4 of them each has a population of 2 million and one 500K. Then the total population count for English would 8 million.

 

Q2 Submission: submit a SQL script for each sub-question, named q2-a.sql, q2-b.sql, q3-ci, q3-cii, etc. The script contains the SQL query/view. Also submit a text file showing all the output for each query.

 

3. [30 points] Write a Python script “top10.py” (that does not use SQL query, so no SQL connected should be used) that takes the country.csv and countrylanguage.csv (these are in the same format as produced in question 1) as the input, and answers the query in question q2.f (i.e., finding top 10 languages). It should output the same result as the SQL query answer of the question.

 

Include a discussion (in the comments of your code) on the complexity and efficiency of your algorithm. If particular, assume country table has m rows and countrylanguage has n rows. What is the complexity of your algorithm? Can you further improve its efficiency?

Execution format: python top10.py country.csv countrylanguage.csv

Q3 Submission: top10.py

 

Final Submission: Please include all three submissions above in one folder and zip it as

Firstname_Lastname.zip. Please note wrong submission format or execution format will result in the deduction of 5 points.