<jupyter_start><jupyter_text>SELECT from WORLD<jupyter_code>library(tidyverse)
library(DBI)
library(getPass)
drv <- switch(Sys.info()['sysname'],
             Windows="PostgreSQL Unicode(x64)",
             Darwin="/usr/local/lib/psqlodbcw.so",
             Linux="PostgreSQL")
con <- dbConnect(
  odbc::odbc(),
  driver = drv,
  Server = "localhost",
  Database = "sqlzoo",
  UID = "postgres",
  PWD = getPass("Password?"),
  Port = 5432
)
options(repr.matrix.max.rows=20)<jupyter_output>-- [1mAttaching packages[22m --------------------------------------- tidyverse 1.3.0 --

[32mv[39m [34mggplot2[39m 3.3.0     [32mv[39m [34mpurrr  [39m 0.3.3
[32mv[39m [34mtibble [39m 3.0.0     [32mv[39m [34mdplyr  [39m 0.8.5
[32mv[39m [34mtidyr  [39m 1.0.2     [32mv[39m [34mstringr[39m 1.4.0
[32mv[39m [34mreadr  [39m 1.3.1     [32mv[39m [34mforcats[39m 0.5.0

-- [1mConflicts[22m ------------------------------------------ tidyverse_conflicts() --
[31mx[39m [34mdplyr[39m::[32mfilter()[39m masks [34mstats[39m::filter()
[31mx[39m [34mdplyr[39m::[32mlag()[39m    masks [34mstats[39m::lag()<jupyter_text>1. Introduction[Read the notes about this table](https://sqlzoo.net/wiki/Read_the_notes_about_this_table.). Observe the result of running this SQL command to show the name, continent and population of all countries.<jupyter_code>world <- dbReadTable(con, 'world')
world %>% 
    select(name, continent, population)<jupyter_output><empty_output><jupyter_text>2. Large Countries[How to use WHERE to filter records](https://sqlzoo.net/wiki/WHERE_filters). Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.<jupyter_code>world %>% 
    filter(population>=2e8) %>%
    select(name)<jupyter_output><empty_output><jupyter_text>3. Per capita GDPGive the `name` and the **per capita GDP** for those countries with a `population` of at least 200 million.> _HELP:How to calculate per capita GDP_   > per capita GDP is the GDP divided by the population GDP/population<jupyter_code>world %>% 
    filter(population>=2e8) %>% 
    mutate(pc_gdp=gdp/population) %>%
    select(name, pc_gdp)<jupyter_output><empty_output><jupyter_text>4. South America In millionsShow the `name` and `population` in millions for the countries of the `continent` 'South America'. Divide the population by 1000000 to get population in millions.<jupyter_code>world %>% 
    filter(continent=='South America') %>% 
    mutate(popl=population/1e6) %>%
    select(name, popl)<jupyter_output><empty_output><jupyter_text>5. France, Germany, ItalyShow the `name` and `population` for France, Germany, Italy<jupyter_code>world %>% 
    filter(name %in% c('France', 'Germany', 'Italy')) %>% 
    select(name, population)<jupyter_output><empty_output><jupyter_text>6. UnitedShow the countries which have a `name` that includes the word 'United'<jupyter_code>world %>% 
    filter(str_detect(name, 'United')) %>% 
    select(name)<jupyter_output><empty_output><jupyter_text>7. Two ways to be bigTwo ways to be big: A country is **big** if it has an area of more than 3 million sq km or it has a population of more than 250 million.**Show the countries that are big by area or big by population. Show name, population and area.**<jupyter_code>world %>% 
    filter(population >= 2.5e8 | area >= 3e6) %>% 
    select(name, population, area)<jupyter_output><empty_output><jupyter_text>8. One or the other (but not both)**Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.**- Australia has a big area but a small population, it should be **included**.- Indonesia has a big population but a small area, it should be **included**.- China has a big population **and** big area, it should be **excluded**.- United Kingdom has a small population and a small area, it should be **excluded**.<jupyter_code>world %>% 
    filter(xor(area>3e6, population>2.5e8)) %>% 
    select(name, population, area)<jupyter_output><empty_output><jupyter_text>9. RoundingShow the `name` and `population` in millions and the GDP in billions for the countries of the `continent` 'South America'. Use the [ROUND](https://sqlzoo.net/wiki/ROUND) function to show the values to two decimal places.**For South America show population in millions and GDP in billions both to 2 decimal places.**> _Millions and billions_    > Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.<jupyter_code>world %>% 
    filter(continent=='South America') %>% 
    mutate(popl=round(population/1e6, 2), gdp_b=round(gdp/1e9, 2)) %>% 
    select(name, popl, gdp_b)<jupyter_output><empty_output><jupyter_text>10. Trillion dollar economiesShow the `name` and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.**Show per-capita GDP for the trillion dollar countries to the nearest $1000.**<jupyter_code>world %>% 
    filter(gdp>=1e12) %>%
    mutate(pc_gdp=round(gdp/(population*1000), 0)*1000) %>% 
    select(name, pc_gdp)<jupyter_output><empty_output><jupyter_text>11. Name and capital have the same lengthGreece has capital Athens.Each of the strings 'Greece', and 'Athens' has 6 characters.**Show the name and capital where the name and the capital have the same number of characters.**- You can use the [LENGTH](https://sqlzoo.net/wiki/LENGTH) function to find the number of characters in a string<jupyter_code>world %>% 
    filter(nchar(name)==nchar(capital)) %>% 
    select(name, capital)<jupyter_output><empty_output><jupyter_text>12. Matching name and capitalThe capital of Sweden is Stockholm. Both words start with the letter 'S'.**Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.**- You can use the function [LEFT](https://sqlzoo.net/wiki/LEFT) to isolate the first character.- You can use  as the **NOT EQUALS** operator.<jupyter_code>world %>% 
    filter(str_sub(name, 1, 1)==str_sub(capital, 1, 1) & 
           name != capital) %>%
    select(name, capital)<jupyter_output><empty_output><jupyter_text>13. All the vowels**Equatorial Guinea** and **Dominican Republic** have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.**Find the country that has all the vowels and no spaces in its name.**- You can use the phrase name `NOT LIKE '%a%'` to exclude characters from your results.- The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'<jupyter_code>world %>% 
    filter(str_detect(name, 'a') & 
           str_detect(name, 'e') & 
           str_detect(name, 'i') &
           str_detect(name, 'o') & 
           str_detect(name, 'u') & 
           str_detect(name, ' ', negate=TRUE)) %>%
    select(name)
dbDisconnect(con)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13788696393070152, 'fraction_numerical': 0.038341380289690426, 'mean_word_length': 4.442812982998454, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 96, 'https://': 5, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '184957', 'license': 'MIT', 'n_tokens_mistral': 2562, 'n_tokens_neox': 2210, 'n_words': 721, 'path': 'R/02 SELECT from World.ipynb', 'repo_name': 'madlogos/sqlzoo', 'size': '7042'}