<jupyter_start><jupyter_text>Analyzing CIA World Factbook with SQLSQL is the short for Structured Query Language. It is a domain-specific language used in programming and designed for managing data held in a relational database management systems. According to [Wikipedia](https://en.wikipedia.org/wiki/SQL), it is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.In this project, we'll work with data from the CIA World Factbook, a compendium of statistics about all of the countries on Earth, using only SQL. According to the [CIA website](https://www.cia.gov/library/publications/the-world-factbook/index.html) the World Factbook provides information on the history, people and society, government, economy, energy, geography, communications, transportation, military, and transnational issues for 267 world entities.We'll work with a reduced version of this database that can downloaded [here](https://dsserver-prod-resources-1.s3.amazonaws.com/257/factbook.db). The objective of the project is just to write some SQL queries to extract some interesting information from the CIA Factbook about the countries on Earth. ![](https://www.imagemhost.com.br/images/2020/04/06/world.png)Image: Background vector created by evening_tao - www.freepik.com First, let's set up the SQL environment and the open the database.<jupyter_code>%%capture
%load_ext sql
%sql sqlite:///factbook.db<jupyter_output><empty_output><jupyter_text>Now let's take a look in the tables inside the database.<jupyter_code>%%sql
SELECT * FROM sqlite_master 
WHERE type = 'table';<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>This database only has one table called facts and that's the one we're using. Let's check its first 5 rows.<jupyter_code>%%sql
SELECT * FROM facts 
LIMIT 5<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>Now, let's see the largest and the smallest population and the highest and lowest population growth ratio.<jupyter_code>%%sql
SELECT
    MAX(population) max_pop, 
    MIN(population) min_pop, 
    MAX(population_growth) max_pop_growth, 
    MIN(population_growth) min_pop_growth
FROM facts;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>Populations of over seven billion people and of zero people does not seem to be right. Thta's because the table has a 'World' row and a 'Antartic' row. These rows might lead us to some mistakes, for example, if we calculated the average population, the world's population of over 7 billion people and the Antartic's population of 0 would make us have wrong results. That is why we'll delete these rows. Then, we'll be able to see that China is the most populated country with over 1.3 billion people and the Pitcairn Islands is the least populated country with only 48 people.<jupyter_code>%%sql
DELETE FROM facts 
WHERE id = 261
%%sql
DELETE FROM facts 
WHERE id = 250<jupyter_output>* sqlite:///factbook.db
0 rows affected.<jupyter_text>Now we're looking for the most populated country.<jupyter_code>%%sql
SELECT *, MAX(population) FROM facts;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>And now for the least populated one.<jupyter_code>%%sql 
SELECT *, MIN(population) FROM facts;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>Now let's see the average poopulation and average area.<jupyter_code>%%sql
SELECT 
    AVG(population) avg_pop, 
    AVG(area) avg_area 
FROM facts;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>We'll now write a query that will show us the the countries whose populations are above average and whose area are below average. Those countries have a high population density.<jupyter_code>%%sql
SELECT * FROM facts
WHERE population > (SELECT AVG(population) FROM facts)
AND area < (SELECT AVG(area) FROM facts);<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>Thinking about the countries areas, the next query will count how many countries have have the majority of their area covered with water.<jupyter_code>%%sql
SELECT COUNT(*) 
FROM facts
WHERE area_water > area_land;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>And only two of them do.So we'll query the top ten countries with the highest ratio of their total land covered by water.<jupyter_code>%%sql
SELECT 
    name, 
    CAST(area_water AS FLOAT) / CAST(area AS FLOAT) ratio 
FROM facts
ORDER BY ratio DESC
LIMIT 10;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>We can see that only the British Indian Ocean Territory and the Virgin Islands have more water than land. Two countries, as expected. The next two queries will show us the top ten countries with the highest and lowest birth rates.<jupyter_code>%%sql
SELECT 
    name, 
    birth_rate
FROM facts
ORDER BY birth_rate DESC
LIMIT 10;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>It's interesting to notice that all the top ten countries with the highs birth rates are placed in Africa. Some countries do not have the information for birth rate, so to find the lowest birth rates top ten, we'll filter the countries with birth rate greater than 0.<jupyter_code>%%sql
SELECT 
    name, 
    birth_rate
FROM facts
WHERE birth_rate > 0
ORDER BY birth_rate 
LIMIT 10;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>These countries are basically placed in Europe and Asia, with the exception of the north american Saint-Pierre et Miquelon. Now let's see the countries with highest death rate.<jupyter_code>%%sql
SELECT 
    name, 
    death_rate
FROM facts
ORDER BY death_rate DESC
LIMIT 10;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>In this top ten there are only european, asian and african countries.Now let's see the lowest death rates, also filtering the ones greater than 0.<jupyter_code>%%sql
SELECT 
    name, 
    death_rate
FROM facts
WHERE death_rate > 0
ORDER BY death_rate
LIMIT 10;<jupyter_output>* sqlite:///factbook.db
Done.<jupyter_text>Notice that with the exception of the north american Turks and Caicos Islands, all the countries with lowest death rates are placed in Asia. Finally, we'll write a query that will calculate the  difference between the birth rate and the death rate of each country. We'll have this data ordered in descending order.<jupyter_code>%%sql
SELECT 
    name, 
    CAST(birth_rate as float) - cast(death_rate as float) birth_death_dif 
FROM facts
WHERE birth_death_dif > 0
ORDER BY birth_death_dif DESC<jupyter_output>* sqlite:///factbook.db
Done.
{'fraction_non_alphanumeric': 0.07478797224363917, 'fraction_numerical': 0.007093292212798767, 'mean_word_length': 5.078725398313027, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 55, 'https://': 4, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '73865', 'license': 'MIT', 'n_tokens_mistral': 1994, 'n_tokens_neox': 1842, 'n_words': 785, 'path': 'Analyzing CIA World Factbook with SQL.ipynb', 'repo_name': 'kuleafenu/data_science', 'size': '6485'}