7.06. Using logical and comparison operators [Notebook].ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using logical and comparison operators\n",
    "© ExploreAI Academy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ⚠️ This notebook will not run on Google Colab because it cannot connect to a local database. Please make sure that this notebook is running on the same local machine as your MySQL Workbench installation and MySQL `united_nations` database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Learning Objectives\n",
    "\n",
    "- Understand the concept of logic and comparison operators in SQL.\n",
    "- Learn how to use the WHERE clause to filter data based on specific conditions.\n",
    "- Understand the use of IN, BETWEEN, and other SQL operators to streamline queries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview\n",
    "\n",
    "In this notebook, we will explore the use of logic and comparison operators in SQL to filter and extract specific data from a dataset. These operators are essential tools in SQL that allow us to narrow down our data based on certain conditions.\n",
    "\n",
    "We will be working with the `united_nations.Access_to_Basic_Services` table, which contains information about different countries, their GDP, access to basic services, and more."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connecting to our MySQL database\n",
    "\n",
    "Since we have a MySQL database, we can connect to it using mysql and pymysql."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load and activate the SQL extension to allows us to execute SQL in a Jupyter notebook\n",
    "\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# establish a connection to the local database using the '%sql' magic command,\n",
    "# replace 'password' with our connection password and `db_name` with our database name\n",
    "\n",
    "%sql mysql+pymysql://root:184253@localhost:3306/united_nations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "### Task 1: Basic Filtering with Comparison Operators\n",
    "\n",
    "You will be working with the `united_nations.Access_to_Basic_Services` table.\n",
    "The columns to `SELECT` include \n",
    "- `Country_name`\n",
    "- `Time_period`\n",
    "- `Pct_managed_drinking_water_services`\n",
    "- `Pct_managed_sanitation_services`\n",
    "- `Est_population_in_millions`\n",
    "- and `Est_gdp_in_billions`\n",
    "\n",
    "\n",
    "Use the `WHERE` clause to filter out records of specific countries: \n",
    "- `Nigeria`, \n",
    "- `Ethiopia`, \n",
    "- `Congo`, \n",
    "- `Egypt`, \n",
    "- `Tanzania`, \n",
    "- `Kenya`, \n",
    "- and `South Africa`\n",
    "- for the time period between `2019` and `2020`.\n",
    "Utilize the `OR` operator to include multiple countries in your filter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "32 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>country_name</th>\n",
       "            <th>time_period</th>\n",
       "            <th>pct_managed_drinking_water_services</th>\n",
       "            <th>Pct_managed_sanitation_services</th>\n",
       "            <th>Est_population_in_millions</th>\n",
       "            <th>Est_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2015</td>\n",
       "            <td>99.33</td>\n",
       "            <td>96.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2016</td>\n",
       "            <td>99.33</td>\n",
       "            <td>96.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2017</td>\n",
       "            <td>99.33</td>\n",
       "            <td>96.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2018</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2019</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2020</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2015</td>\n",
       "            <td>52.00</td>\n",
       "            <td>10.67</td>\n",
       "            <td>102.471895</td>\n",
       "            <td>64.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2016</td>\n",
       "            <td>53.33</td>\n",
       "            <td>11.00</td>\n",
       "            <td>105.293228</td>\n",
       "            <td>74.30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2017</td>\n",
       "            <td>54.67</td>\n",
       "            <td>11.00</td>\n",
       "            <td>108.197950</td>\n",
       "            <td>81.77</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2018</td>\n",
       "            <td>55.67</td>\n",
       "            <td>11.33</td>\n",
       "            <td>111.129438</td>\n",
       "            <td>84.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2019</td>\n",
       "            <td>57.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>114.120594</td>\n",
       "            <td>95.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2020</td>\n",
       "            <td>58.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>117.190911</td>\n",
       "            <td>107.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2015</td>\n",
       "            <td>64.33</td>\n",
       "            <td>33.00</td>\n",
       "            <td>46.851488</td>\n",
       "            <td>70.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2016</td>\n",
       "            <td>65.00</td>\n",
       "            <td>33.00</td>\n",
       "            <td>47.894670</td>\n",
       "            <td>74.82</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2017</td>\n",
       "            <td>65.67</td>\n",
       "            <td>33.00</td>\n",
       "            <td>48.948137</td>\n",
       "            <td>82.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2018</td>\n",
       "            <td>65.67</td>\n",
       "            <td>33.33</td>\n",
       "            <td>49.953304</td>\n",
       "            <td>92.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2019</td>\n",
       "            <td>66.33</td>\n",
       "            <td>33.33</td>\n",
       "            <td>50.951450</td>\n",
       "            <td>100.38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.00</td>\n",
       "            <td>33.67</td>\n",
       "            <td>51.985780</td>\n",
       "            <td>100.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2015</td>\n",
       "            <td>66.33</td>\n",
       "            <td>17.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2016</td>\n",
       "            <td>67.67</td>\n",
       "            <td>17.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2017</td>\n",
       "            <td>68.67</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2018</td>\n",
       "            <td>68.67</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2019</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2019</td>\n",
       "            <td>91.67</td>\n",
       "            <td>78.00</td>\n",
       "            <td>58.087055</td>\n",
       "            <td>388.53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2020</td>\n",
       "            <td>92.00</td>\n",
       "            <td>78.67</td>\n",
       "            <td>58.801927</td>\n",
       "            <td>337.62</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2015</td>\n",
       "            <td>69.00</td>\n",
       "            <td>38.67</td>\n",
       "            <td>183.995785</td>\n",
       "            <td>493.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2016</td>\n",
       "            <td>71.00</td>\n",
       "            <td>39.33</td>\n",
       "            <td>188.666931</td>\n",
       "            <td>404.65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2017</td>\n",
       "            <td>72.33</td>\n",
       "            <td>40.00</td>\n",
       "            <td>193.495907</td>\n",
       "            <td>375.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2018</td>\n",
       "            <td>74.00</td>\n",
       "            <td>41.00</td>\n",
       "            <td>198.387623</td>\n",
       "            <td>421.74</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2019</td>\n",
       "            <td>75.67</td>\n",
       "            <td>41.67</td>\n",
       "            <td>203.304492</td>\n",
       "            <td>448.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>42.67</td>\n",
       "            <td>208.327405</td>\n",
       "            <td>432.20</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Egypt', 2015, Decimal('99.33'), Decimal('96.33'), None, None),\n",
       " ('Egypt', 2016, Decimal('99.33'), Decimal('96.33'), None, None),\n",
       " ('Egypt', 2017, Decimal('99.33'), Decimal('96.33'), None, None),\n",
       " ('Egypt', 2018, Decimal('99.33'), Decimal('97.00'), None, None),\n",
       " ('Egypt', 2019, Decimal('99.33'), Decimal('97.00'), None, None),\n",
       " ('Egypt', 2020, Decimal('99.33'), Decimal('97.67'), None, None),\n",
       " ('Ethiopia', 2015, Decimal('52.00'), Decimal('10.67'), Decimal('102.471895'), Decimal('64.59')),\n",
       " ('Ethiopia', 2016, Decimal('53.33'), Decimal('11.00'), Decimal('105.293228'), Decimal('74.30')),\n",
       " ('Ethiopia', 2017, Decimal('54.67'), Decimal('11.00'), Decimal('108.197950'), Decimal('81.77')),\n",
       " ('Ethiopia', 2018, Decimal('55.67'), Decimal('11.33'), Decimal('111.129438'), Decimal('84.27')),\n",
       " ('Ethiopia', 2019, Decimal('57.00'), Decimal('11.67'), Decimal('114.120594'), Decimal('95.91')),\n",
       " ('Ethiopia', 2020, Decimal('58.00'), Decimal('11.67'), Decimal('117.190911'), Decimal('107.66')),\n",
       " ('Kenya', 2015, Decimal('64.33'), Decimal('33.00'), Decimal('46.851488'), Decimal('70.12')),\n",
       " ('Kenya', 2016, Decimal('65.00'), Decimal('33.00'), Decimal('47.894670'), Decimal('74.82')),\n",
       " ('Kenya', 2017, Decimal('65.67'), Decimal('33.00'), Decimal('48.948137'), Decimal('82.04')),\n",
       " ('Kenya', 2018, Decimal('65.67'), Decimal('33.33'), Decimal('49.953304'), Decimal('92.20')),\n",
       " ('Kenya', 2019, Decimal('66.33'), Decimal('33.33'), Decimal('50.951450'), Decimal('100.38')),\n",
       " ('Kenya', 2020, Decimal('67.00'), Decimal('33.67'), Decimal('51.985780'), Decimal('100.67')),\n",
       " ('Congo', 2015, Decimal('66.33'), Decimal('17.00'), None, None),\n",
       " ('Congo', 2016, Decimal('67.67'), Decimal('17.33'), None, None),\n",
       " ('Congo', 2017, Decimal('68.67'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2018, Decimal('68.67'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2019, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2020, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('South Africa', 2019, Decimal('91.67'), Decimal('78.00'), Decimal('58.087055'), Decimal('388.53')),\n",
       " ('South Africa', 2020, Decimal('92.00'), Decimal('78.67'), Decimal('58.801927'), Decimal('337.62')),\n",
       " ('Nigeria', 2015, Decimal('69.00'), Decimal('38.67'), Decimal('183.995785'), Decimal('493.03')),\n",
       " ('Nigeria', 2016, Decimal('71.00'), Decimal('39.33'), Decimal('188.666931'), Decimal('404.65')),\n",
       " ('Nigeria', 2017, Decimal('72.33'), Decimal('40.00'), Decimal('193.495907'), Decimal('375.75')),\n",
       " ('Nigeria', 2018, Decimal('74.00'), Decimal('41.00'), Decimal('198.387623'), Decimal('421.74')),\n",
       " ('Nigeria', 2019, Decimal('75.67'), Decimal('41.67'), Decimal('203.304492'), Decimal('448.12')),\n",
       " ('Nigeria', 2020, Decimal('77.33'), Decimal('42.67'), Decimal('208.327405'), Decimal('432.20'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "  country_name,\n",
    "  time_period,\n",
    "  pct_managed_drinking_water_services,\n",
    "  Pct_managed_sanitation_services,\n",
    "  Est_population_in_millions,\n",
    "  Est_gdp_in_billions\n",
    "FROM\n",
    "  access_to_basic_services\n",
    "WHERE\n",
    "  country_name = 'Nigeria'\n",
    "  OR country_name = 'Ethiopia'\n",
    "  OR country_name = 'Congo'\n",
    "  OR country_name = 'Egypt'\n",
    "  OR country_name = 'Tanzania'\n",
    "  OR country_name = 'Kenya'\n",
    "  OR country_name = 'South Africa'\n",
    "  AND time_period BETWEEN 2019 AND 2020\n",
    "\n",
    "-- Add your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 2: Streamlining with the IN Operator\n",
    "\n",
    "Building on the first task, you might have noticed that using multiple `OR` operators can make the query lengthy and a bit complicated. The 'IN' operator offers a more efficient way to achieve the same result.\n",
    "The `IN` operator allows you to specify multiple values in a `WHERE` clause, making the query more concise.\n",
    "Try to retrieve data for the same list of countries for the same time range, but this time, use the `IN` operator to simplify your query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>country_name</th>\n",
       "            <th>time_period</th>\n",
       "            <th>pct_managed_drinking_water_services</th>\n",
       "            <th>Pct_managed_sanitation_services</th>\n",
       "            <th>Est_population_in_millions</th>\n",
       "            <th>Est_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2019</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2020</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2019</td>\n",
       "            <td>57.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>114.120594</td>\n",
       "            <td>95.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2020</td>\n",
       "            <td>58.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>117.190911</td>\n",
       "            <td>107.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2019</td>\n",
       "            <td>66.33</td>\n",
       "            <td>33.33</td>\n",
       "            <td>50.951450</td>\n",
       "            <td>100.38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.00</td>\n",
       "            <td>33.67</td>\n",
       "            <td>51.985780</td>\n",
       "            <td>100.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2019</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2019</td>\n",
       "            <td>91.67</td>\n",
       "            <td>78.00</td>\n",
       "            <td>58.087055</td>\n",
       "            <td>388.53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2020</td>\n",
       "            <td>92.00</td>\n",
       "            <td>78.67</td>\n",
       "            <td>58.801927</td>\n",
       "            <td>337.62</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2019</td>\n",
       "            <td>75.67</td>\n",
       "            <td>41.67</td>\n",
       "            <td>203.304492</td>\n",
       "            <td>448.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>42.67</td>\n",
       "            <td>208.327405</td>\n",
       "            <td>432.20</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Egypt', 2019, Decimal('99.33'), Decimal('97.00'), None, None),\n",
       " ('Egypt', 2020, Decimal('99.33'), Decimal('97.67'), None, None),\n",
       " ('Ethiopia', 2019, Decimal('57.00'), Decimal('11.67'), Decimal('114.120594'), Decimal('95.91')),\n",
       " ('Ethiopia', 2020, Decimal('58.00'), Decimal('11.67'), Decimal('117.190911'), Decimal('107.66')),\n",
       " ('Kenya', 2019, Decimal('66.33'), Decimal('33.33'), Decimal('50.951450'), Decimal('100.38')),\n",
       " ('Kenya', 2020, Decimal('67.00'), Decimal('33.67'), Decimal('51.985780'), Decimal('100.67')),\n",
       " ('Congo', 2019, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2020, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('South Africa', 2019, Decimal('91.67'), Decimal('78.00'), Decimal('58.087055'), Decimal('388.53')),\n",
       " ('South Africa', 2020, Decimal('92.00'), Decimal('78.67'), Decimal('58.801927'), Decimal('337.62')),\n",
       " ('Nigeria', 2019, Decimal('75.67'), Decimal('41.67'), Decimal('203.304492'), Decimal('448.12')),\n",
       " ('Nigeria', 2020, Decimal('77.33'), Decimal('42.67'), Decimal('208.327405'), Decimal('432.20'))]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "  country_name,\n",
    "  time_period,\n",
    "  pct_managed_drinking_water_services,\n",
    "  Pct_managed_sanitation_services,\n",
    "  Est_population_in_millions,\n",
    "  Est_gdp_in_billions\n",
    "FROM\n",
    "  access_to_basic_services\n",
    "WHERE\n",
    "  country_name IN ('Nigeria', 'Ethiopia', 'Congo', 'Egypt', 'Tanzania', 'Kenya', 'South Africa')\n",
    "AND time_period BETWEEN 2019 AND 2020\n",
    "\n",
    "-- Add your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3: Combining Multiple Conditions\n",
    "\n",
    "The `<=` operator is a comparison operator that checks if the value on the left is less than or equal to the value on the right. In this task, you'll use it to filter countries based on specific service percentages.\n",
    "You'll continue working with the `united_nations.Access_to_Basic_Services` table.\n",
    "The columns to be selected for this task are \n",
    "- `Region`, \n",
    "- `Country_name`, \n",
    "- `Time_period`, \n",
    "- `Pct_managed_drinking_water_services`, \n",
    "- `Pct_managed_sanitation_services`, \n",
    "- `Est_population_in_millions`, and \n",
    "- `Est_gdp_in_billions`.\n",
    "\n",
    "Your goal is to extract data for the year 2020, focusing on countries where less than or equal to 50% of the population has access to sanitation services. This will help identify countries with critical sanitation needs.\n",
    "By using the `<=` operator with the `Pct_managed_sanitation_services` column, you can filter out countries that exceed this threshold, leaving only those with the most pressing sanitation challenges."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "41 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Region</th>\n",
       "            <th>Country_name</th>\n",
       "            <th>Time_period</th>\n",
       "            <th>Pct_managed_drinking_water_services</th>\n",
       "            <th>Pct_managed_sanitation_services</th>\n",
       "            <th>Est_population_in_millions</th>\n",
       "            <th>Est_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>Haiti</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.00</td>\n",
       "            <td>36.00</td>\n",
       "            <td>11.306801</td>\n",
       "            <td>14.51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northern Africa and Western Asia</td>\n",
       "            <td>Sudan</td>\n",
       "            <td>2020</td>\n",
       "            <td>62.33</td>\n",
       "            <td>40.33</td>\n",
       "            <td>44.440486</td>\n",
       "            <td>27.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Papua New Guinea</td>\n",
       "            <td>2020</td>\n",
       "            <td>56.67</td>\n",
       "            <td>27.67</td>\n",
       "            <td>9.749640</td>\n",
       "            <td>23.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Solomon Islands</td>\n",
       "            <td>2020</td>\n",
       "            <td>72.33</td>\n",
       "            <td>44.67</td>\n",
       "            <td>0.691191</td>\n",
       "            <td>1.54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Kiribati</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.00</td>\n",
       "            <td>45.33</td>\n",
       "            <td>0.126463</td>\n",
       "            <td>0.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Burundi</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.33</td>\n",
       "            <td>44.33</td>\n",
       "            <td>12.220227</td>\n",
       "            <td>2.65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2020</td>\n",
       "            <td>58.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>117.190911</td>\n",
       "            <td>107.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Kenya</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.00</td>\n",
       "            <td>33.67</td>\n",
       "            <td>51.985780</td>\n",
       "            <td>100.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Madagascar</td>\n",
       "            <td>2020</td>\n",
       "            <td>56.33</td>\n",
       "            <td>13.00</td>\n",
       "            <td>28.225177</td>\n",
       "            <td>13.05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Malawi</td>\n",
       "            <td>2020</td>\n",
       "            <td>74.33</td>\n",
       "            <td>28.67</td>\n",
       "            <td>19.377061</td>\n",
       "            <td>12.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Mozambique</td>\n",
       "            <td>2020</td>\n",
       "            <td>66.67</td>\n",
       "            <td>40.33</td>\n",
       "            <td>31.178239</td>\n",
       "            <td>14.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Somalia</td>\n",
       "            <td>2020</td>\n",
       "            <td>57.33</td>\n",
       "            <td>40.00</td>\n",
       "            <td>16.537016</td>\n",
       "            <td>6.88</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>South Sudan</td>\n",
       "            <td>2020</td>\n",
       "            <td>48.33</td>\n",
       "            <td>22.33</td>\n",
       "            <td>10.606227</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Uganda</td>\n",
       "            <td>2020</td>\n",
       "            <td>61.00</td>\n",
       "            <td>21.67</td>\n",
       "            <td>44.404611</td>\n",
       "            <td>37.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>United Republic of Tanzania</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.00</td>\n",
       "            <td>34.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Zambia</td>\n",
       "            <td>2020</td>\n",
       "            <td>66.67</td>\n",
       "            <td>32.67</td>\n",
       "            <td>18.927715</td>\n",
       "            <td>18.11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Zimbabwe</td>\n",
       "            <td>2020</td>\n",
       "            <td>68.00</td>\n",
       "            <td>36.33</td>\n",
       "            <td>15.669666</td>\n",
       "            <td>21.51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Angola</td>\n",
       "            <td>2020</td>\n",
       "            <td>52.33</td>\n",
       "            <td>47.00</td>\n",
       "            <td>33.428486</td>\n",
       "            <td>53.62</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Cameroon</td>\n",
       "            <td>2020</td>\n",
       "            <td>64.00</td>\n",
       "            <td>43.00</td>\n",
       "            <td>26.491087</td>\n",
       "            <td>40.77</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Central African Republic</td>\n",
       "            <td>2020</td>\n",
       "            <td>38.33</td>\n",
       "            <td>15.00</td>\n",
       "            <td>5.343020</td>\n",
       "            <td>2.33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Chad</td>\n",
       "            <td>2020</td>\n",
       "            <td>52.67</td>\n",
       "            <td>18.67</td>\n",
       "            <td>16.644701</td>\n",
       "            <td>10.72</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Democratic Republic of the Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>47.67</td>\n",
       "            <td>15.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Gabon</td>\n",
       "            <td>2020</td>\n",
       "            <td>73.33</td>\n",
       "            <td>47.00</td>\n",
       "            <td>2.292573</td>\n",
       "            <td>15.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Sao Tome and Principe</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>46.00</td>\n",
       "            <td>0.218641</td>\n",
       "            <td>0.47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Lesotho</td>\n",
       "            <td>2020</td>\n",
       "            <td>76.33</td>\n",
       "            <td>49.67</td>\n",
       "            <td>2.254100</td>\n",
       "            <td>2.23</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Namibia</td>\n",
       "            <td>2020</td>\n",
       "            <td>83.67</td>\n",
       "            <td>35.00</td>\n",
       "            <td>2.489098</td>\n",
       "            <td>10.58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Benin</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.33</td>\n",
       "            <td>17.33</td>\n",
       "            <td>12.643123</td>\n",
       "            <td>15.65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Burkina Faso</td>\n",
       "            <td>2020</td>\n",
       "            <td>53.33</td>\n",
       "            <td>25.00</td>\n",
       "            <td>21.522626</td>\n",
       "            <td>17.93</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Côte d&#x27;Ivoire</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.67</td>\n",
       "            <td>34.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Gambia</td>\n",
       "            <td>2020</td>\n",
       "            <td>79.33</td>\n",
       "            <td>44.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Ghana</td>\n",
       "            <td>2020</td>\n",
       "            <td>84.67</td>\n",
       "            <td>23.00</td>\n",
       "            <td>32.180401</td>\n",
       "            <td>70.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Guinea</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.33</td>\n",
       "            <td>32.33</td>\n",
       "            <td>13.205153</td>\n",
       "            <td>14.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Guinea-Bissau</td>\n",
       "            <td>2020</td>\n",
       "            <td>60.00</td>\n",
       "            <td>19.33</td>\n",
       "            <td>2.015828</td>\n",
       "            <td>1.43</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Liberia</td>\n",
       "            <td>2020</td>\n",
       "            <td>75.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>5.087584</td>\n",
       "            <td>3.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Mali</td>\n",
       "            <td>2020</td>\n",
       "            <td>83.67</td>\n",
       "            <td>46.00</td>\n",
       "            <td>21.224040</td>\n",
       "            <td>17.47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Mauritania</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.33</td>\n",
       "            <td>48.00</td>\n",
       "            <td>4.498604</td>\n",
       "            <td>8.41</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Niger</td>\n",
       "            <td>2020</td>\n",
       "            <td>57.33</td>\n",
       "            <td>24.67</td>\n",
       "            <td>24.333639</td>\n",
       "            <td>13.74</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>42.67</td>\n",
       "            <td>208.327405</td>\n",
       "            <td>432.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Sierra Leone</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.00</td>\n",
       "            <td>17.33</td>\n",
       "            <td>8.233970</td>\n",
       "            <td>4.06</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Togo</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.67</td>\n",
       "            <td>20.00</td>\n",
       "            <td>8.442580</td>\n",
       "            <td>7.57</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Latin America and the Caribbean', 'Haiti', 2020, Decimal('65.00'), Decimal('36.00'), Decimal('11.306801'), Decimal('14.51')),\n",
       " ('Northern Africa and Western Asia', 'Sudan', 2020, Decimal('62.33'), Decimal('40.33'), Decimal('44.440486'), Decimal('27.03')),\n",
       " ('Oceania', 'Papua New Guinea', 2020, Decimal('56.67'), Decimal('27.67'), Decimal('9.749640'), Decimal('23.85')),\n",
       " ('Oceania', 'Solomon Islands', 2020, Decimal('72.33'), Decimal('44.67'), Decimal('0.691191'), Decimal('1.54')),\n",
       " ('Oceania', 'Kiribati', 2020, Decimal('77.00'), Decimal('45.33'), Decimal('0.126463'), Decimal('0.18')),\n",
       " ('Sub-Saharan Africa', 'Burundi', 2020, Decimal('70.33'), Decimal('44.33'), Decimal('12.220227'), Decimal('2.65')),\n",
       " ('Sub-Saharan Africa', 'Ethiopia', 2020, Decimal('58.00'), Decimal('11.67'), Decimal('117.190911'), Decimal('107.66')),\n",
       " ('Sub-Saharan Africa', 'Kenya', 2020, Decimal('67.00'), Decimal('33.67'), Decimal('51.985780'), Decimal('100.67')),\n",
       " ('Sub-Saharan Africa', 'Madagascar', 2020, Decimal('56.33'), Decimal('13.00'), Decimal('28.225177'), Decimal('13.05')),\n",
       " ('Sub-Saharan Africa', 'Malawi', 2020, Decimal('74.33'), Decimal('28.67'), Decimal('19.377061'), Decimal('12.18')),\n",
       " ('Sub-Saharan Africa', 'Mozambique', 2020, Decimal('66.67'), Decimal('40.33'), Decimal('31.178239'), Decimal('14.03')),\n",
       " ('Sub-Saharan Africa', 'Somalia', 2020, Decimal('57.33'), Decimal('40.00'), Decimal('16.537016'), Decimal('6.88')),\n",
       " ('Sub-Saharan Africa', 'South Sudan', 2020, Decimal('48.33'), Decimal('22.33'), Decimal('10.606227'), None),\n",
       " ('Sub-Saharan Africa', 'Uganda', 2020, Decimal('61.00'), Decimal('21.67'), Decimal('44.404611'), Decimal('37.60')),\n",
       " ('Sub-Saharan Africa', 'United Republic of Tanzania', 2020, Decimal('65.00'), Decimal('34.00'), None, None),\n",
       " ('Sub-Saharan Africa', 'Zambia', 2020, Decimal('66.67'), Decimal('32.67'), Decimal('18.927715'), Decimal('18.11')),\n",
       " ('Sub-Saharan Africa', 'Zimbabwe', 2020, Decimal('68.00'), Decimal('36.33'), Decimal('15.669666'), Decimal('21.51')),\n",
       " ('Sub-Saharan Africa', 'Angola', 2020, Decimal('52.33'), Decimal('47.00'), Decimal('33.428486'), Decimal('53.62')),\n",
       " ('Sub-Saharan Africa', 'Cameroon', 2020, Decimal('64.00'), Decimal('43.00'), Decimal('26.491087'), Decimal('40.77')),\n",
       " ('Sub-Saharan Africa', 'Central African Republic', 2020, Decimal('38.33'), Decimal('15.00'), Decimal('5.343020'), Decimal('2.33')),\n",
       " ('Sub-Saharan Africa', 'Chad', 2020, Decimal('52.67'), Decimal('18.67'), Decimal('16.644701'), Decimal('10.72')),\n",
       " ('Sub-Saharan Africa', 'Congo', 2020, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('Sub-Saharan Africa', 'Democratic Republic of the Congo', 2020, Decimal('47.67'), Decimal('15.33'), None, None),\n",
       " ('Sub-Saharan Africa', 'Gabon', 2020, Decimal('73.33'), Decimal('47.00'), Decimal('2.292573'), Decimal('15.31')),\n",
       " ('Sub-Saharan Africa', 'Sao Tome and Principe', 2020, Decimal('77.33'), Decimal('46.00'), Decimal('0.218641'), Decimal('0.47')),\n",
       " ('Sub-Saharan Africa', 'Lesotho', 2020, Decimal('76.33'), Decimal('49.67'), Decimal('2.254100'), Decimal('2.23')),\n",
       " ('Sub-Saharan Africa', 'Namibia', 2020, Decimal('83.67'), Decimal('35.00'), Decimal('2.489098'), Decimal('10.58')),\n",
       " ('Sub-Saharan Africa', 'Benin', 2020, Decimal('65.33'), Decimal('17.33'), Decimal('12.643123'), Decimal('15.65')),\n",
       " ('Sub-Saharan Africa', 'Burkina Faso', 2020, Decimal('53.33'), Decimal('25.00'), Decimal('21.522626'), Decimal('17.93')),\n",
       " ('Sub-Saharan Africa', \"Côte d'Ivoire\", 2020, Decimal('70.67'), Decimal('34.67'), None, None),\n",
       " ('Sub-Saharan Africa', 'Gambia', 2020, Decimal('79.33'), Decimal('44.33'), None, None),\n",
       " ('Sub-Saharan Africa', 'Ghana', 2020, Decimal('84.67'), Decimal('23.00'), Decimal('32.180401'), Decimal('70.04')),\n",
       " ('Sub-Saharan Africa', 'Guinea', 2020, Decimal('67.33'), Decimal('32.33'), Decimal('13.205153'), Decimal('14.18')),\n",
       " ('Sub-Saharan Africa', 'Guinea-Bissau', 2020, Decimal('60.00'), Decimal('19.33'), Decimal('2.015828'), Decimal('1.43')),\n",
       " ('Sub-Saharan Africa', 'Liberia', 2020, Decimal('75.00'), Decimal('17.67'), Decimal('5.087584'), Decimal('3.04')),\n",
       " ('Sub-Saharan Africa', 'Mali', 2020, Decimal('83.67'), Decimal('46.00'), Decimal('21.224040'), Decimal('17.47')),\n",
       " ('Sub-Saharan Africa', 'Mauritania', 2020, Decimal('70.33'), Decimal('48.00'), Decimal('4.498604'), Decimal('8.41')),\n",
       " ('Sub-Saharan Africa', 'Niger', 2020, Decimal('57.33'), Decimal('24.67'), Decimal('24.333639'), Decimal('13.74')),\n",
       " ('Sub-Saharan Africa', 'Nigeria', 2020, Decimal('77.33'), Decimal('42.67'), Decimal('208.327405'), Decimal('432.20')),\n",
       " ('Sub-Saharan Africa', 'Sierra Leone', 2020, Decimal('65.00'), Decimal('17.33'), Decimal('8.233970'), Decimal('4.06')),\n",
       " ('Sub-Saharan Africa', 'Togo', 2020, Decimal('70.67'), Decimal('20.00'), Decimal('8.442580'), Decimal('7.57'))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "  Region, \n",
    "  Country_name, \n",
    "  Time_period, \n",
    "  Pct_managed_drinking_water_services, \n",
    "  Pct_managed_sanitation_services, \n",
    "  Est_population_in_millions, \n",
    "  Est_gdp_in_billions\n",
    "FROM\n",
    "  access_to_basic_services\n",
    "WHERE\n",
    "  (Time_period = 2020)\n",
    "  AND (Pct_managed_sanitation_services <= 50)\n",
    "  \n",
    "-- Add your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Solutions\n",
    "\n",
    "### Task 1: Basic Filtering with Comparison Operators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "32 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Country_name</th>\n",
       "            <th>Time_period</th>\n",
       "            <th>Pct_managed_drinking_water_services</th>\n",
       "            <th>Pct_managed_sanitation_services</th>\n",
       "            <th>Est_population_in_millions</th>\n",
       "            <th>Est_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2015</td>\n",
       "            <td>99.33</td>\n",
       "            <td>96.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2016</td>\n",
       "            <td>99.33</td>\n",
       "            <td>96.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2017</td>\n",
       "            <td>99.33</td>\n",
       "            <td>96.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2018</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2019</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2020</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2015</td>\n",
       "            <td>52.00</td>\n",
       "            <td>10.67</td>\n",
       "            <td>102.471895</td>\n",
       "            <td>64.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2016</td>\n",
       "            <td>53.33</td>\n",
       "            <td>11.00</td>\n",
       "            <td>105.293228</td>\n",
       "            <td>74.30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2017</td>\n",
       "            <td>54.67</td>\n",
       "            <td>11.00</td>\n",
       "            <td>108.197950</td>\n",
       "            <td>81.77</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2018</td>\n",
       "            <td>55.67</td>\n",
       "            <td>11.33</td>\n",
       "            <td>111.129438</td>\n",
       "            <td>84.27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2019</td>\n",
       "            <td>57.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>114.120594</td>\n",
       "            <td>95.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2020</td>\n",
       "            <td>58.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>117.190911</td>\n",
       "            <td>107.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2015</td>\n",
       "            <td>64.33</td>\n",
       "            <td>33.00</td>\n",
       "            <td>46.851488</td>\n",
       "            <td>70.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2016</td>\n",
       "            <td>65.00</td>\n",
       "            <td>33.00</td>\n",
       "            <td>47.894670</td>\n",
       "            <td>74.82</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2017</td>\n",
       "            <td>65.67</td>\n",
       "            <td>33.00</td>\n",
       "            <td>48.948137</td>\n",
       "            <td>82.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2018</td>\n",
       "            <td>65.67</td>\n",
       "            <td>33.33</td>\n",
       "            <td>49.953304</td>\n",
       "            <td>92.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2019</td>\n",
       "            <td>66.33</td>\n",
       "            <td>33.33</td>\n",
       "            <td>50.951450</td>\n",
       "            <td>100.38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.00</td>\n",
       "            <td>33.67</td>\n",
       "            <td>51.985780</td>\n",
       "            <td>100.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2015</td>\n",
       "            <td>66.33</td>\n",
       "            <td>17.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2016</td>\n",
       "            <td>67.67</td>\n",
       "            <td>17.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2017</td>\n",
       "            <td>68.67</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2018</td>\n",
       "            <td>68.67</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2019</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2019</td>\n",
       "            <td>91.67</td>\n",
       "            <td>78.00</td>\n",
       "            <td>58.087055</td>\n",
       "            <td>388.53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2020</td>\n",
       "            <td>92.00</td>\n",
       "            <td>78.67</td>\n",
       "            <td>58.801927</td>\n",
       "            <td>337.62</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2015</td>\n",
       "            <td>69.00</td>\n",
       "            <td>38.67</td>\n",
       "            <td>183.995785</td>\n",
       "            <td>493.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2016</td>\n",
       "            <td>71.00</td>\n",
       "            <td>39.33</td>\n",
       "            <td>188.666931</td>\n",
       "            <td>404.65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2017</td>\n",
       "            <td>72.33</td>\n",
       "            <td>40.00</td>\n",
       "            <td>193.495907</td>\n",
       "            <td>375.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2018</td>\n",
       "            <td>74.00</td>\n",
       "            <td>41.00</td>\n",
       "            <td>198.387623</td>\n",
       "            <td>421.74</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2019</td>\n",
       "            <td>75.67</td>\n",
       "            <td>41.67</td>\n",
       "            <td>203.304492</td>\n",
       "            <td>448.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>42.67</td>\n",
       "            <td>208.327405</td>\n",
       "            <td>432.20</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Egypt', 2015, Decimal('99.33'), Decimal('96.33'), None, None),\n",
       " ('Egypt', 2016, Decimal('99.33'), Decimal('96.33'), None, None),\n",
       " ('Egypt', 2017, Decimal('99.33'), Decimal('96.33'), None, None),\n",
       " ('Egypt', 2018, Decimal('99.33'), Decimal('97.00'), None, None),\n",
       " ('Egypt', 2019, Decimal('99.33'), Decimal('97.00'), None, None),\n",
       " ('Egypt', 2020, Decimal('99.33'), Decimal('97.67'), None, None),\n",
       " ('Ethiopia', 2015, Decimal('52.00'), Decimal('10.67'), Decimal('102.471895'), Decimal('64.59')),\n",
       " ('Ethiopia', 2016, Decimal('53.33'), Decimal('11.00'), Decimal('105.293228'), Decimal('74.30')),\n",
       " ('Ethiopia', 2017, Decimal('54.67'), Decimal('11.00'), Decimal('108.197950'), Decimal('81.77')),\n",
       " ('Ethiopia', 2018, Decimal('55.67'), Decimal('11.33'), Decimal('111.129438'), Decimal('84.27')),\n",
       " ('Ethiopia', 2019, Decimal('57.00'), Decimal('11.67'), Decimal('114.120594'), Decimal('95.91')),\n",
       " ('Ethiopia', 2020, Decimal('58.00'), Decimal('11.67'), Decimal('117.190911'), Decimal('107.66')),\n",
       " ('Kenya', 2015, Decimal('64.33'), Decimal('33.00'), Decimal('46.851488'), Decimal('70.12')),\n",
       " ('Kenya', 2016, Decimal('65.00'), Decimal('33.00'), Decimal('47.894670'), Decimal('74.82')),\n",
       " ('Kenya', 2017, Decimal('65.67'), Decimal('33.00'), Decimal('48.948137'), Decimal('82.04')),\n",
       " ('Kenya', 2018, Decimal('65.67'), Decimal('33.33'), Decimal('49.953304'), Decimal('92.20')),\n",
       " ('Kenya', 2019, Decimal('66.33'), Decimal('33.33'), Decimal('50.951450'), Decimal('100.38')),\n",
       " ('Kenya', 2020, Decimal('67.00'), Decimal('33.67'), Decimal('51.985780'), Decimal('100.67')),\n",
       " ('Congo', 2015, Decimal('66.33'), Decimal('17.00'), None, None),\n",
       " ('Congo', 2016, Decimal('67.67'), Decimal('17.33'), None, None),\n",
       " ('Congo', 2017, Decimal('68.67'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2018, Decimal('68.67'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2019, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2020, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('South Africa', 2019, Decimal('91.67'), Decimal('78.00'), Decimal('58.087055'), Decimal('388.53')),\n",
       " ('South Africa', 2020, Decimal('92.00'), Decimal('78.67'), Decimal('58.801927'), Decimal('337.62')),\n",
       " ('Nigeria', 2015, Decimal('69.00'), Decimal('38.67'), Decimal('183.995785'), Decimal('493.03')),\n",
       " ('Nigeria', 2016, Decimal('71.00'), Decimal('39.33'), Decimal('188.666931'), Decimal('404.65')),\n",
       " ('Nigeria', 2017, Decimal('72.33'), Decimal('40.00'), Decimal('193.495907'), Decimal('375.75')),\n",
       " ('Nigeria', 2018, Decimal('74.00'), Decimal('41.00'), Decimal('198.387623'), Decimal('421.74')),\n",
       " ('Nigeria', 2019, Decimal('75.67'), Decimal('41.67'), Decimal('203.304492'), Decimal('448.12')),\n",
       " ('Nigeria', 2020, Decimal('77.33'), Decimal('42.67'), Decimal('208.327405'), Decimal('432.20'))]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "Country_name, \t \n",
    "Time_period, \n",
    "Pct_managed_drinking_water_services, \n",
    "Pct_managed_sanitation_services, \n",
    "Est_population_in_millions,\n",
    "Est_gdp_in_billions  \n",
    "FROM \n",
    "united_nations.Access_to_Basic_Services \n",
    "WHERE \n",
    "Country_name = 'Nigeria' \n",
    "OR Country_name = 'Ethiopia' \n",
    "OR Country_name = 'Congo' \n",
    "OR Country_name = 'Egypt' \n",
    "OR Country_name = 'Tanzania' \n",
    "OR Country_name = 'Kenya' \n",
    "OR Country_name ='South Africa'\n",
    "AND Time_period BETWEEN 2019 AND 2020;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 2: Streamlining with the IN Operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Country_name</th>\n",
       "            <th>Time_period</th>\n",
       "            <th>Pct_managed_drinking_water_services</th>\n",
       "            <th>Pct_managed_sanitation_services</th>\n",
       "            <th>Est_population_in_millions</th>\n",
       "            <th>Est_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2019</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Egypt</td>\n",
       "            <td>2020</td>\n",
       "            <td>99.33</td>\n",
       "            <td>97.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2019</td>\n",
       "            <td>57.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>114.120594</td>\n",
       "            <td>95.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2020</td>\n",
       "            <td>58.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>117.190911</td>\n",
       "            <td>107.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2019</td>\n",
       "            <td>66.33</td>\n",
       "            <td>33.33</td>\n",
       "            <td>50.951450</td>\n",
       "            <td>100.38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kenya</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.00</td>\n",
       "            <td>33.67</td>\n",
       "            <td>51.985780</td>\n",
       "            <td>100.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2019</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2019</td>\n",
       "            <td>91.67</td>\n",
       "            <td>78.00</td>\n",
       "            <td>58.087055</td>\n",
       "            <td>388.53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Africa</td>\n",
       "            <td>2020</td>\n",
       "            <td>92.00</td>\n",
       "            <td>78.67</td>\n",
       "            <td>58.801927</td>\n",
       "            <td>337.62</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2019</td>\n",
       "            <td>75.67</td>\n",
       "            <td>41.67</td>\n",
       "            <td>203.304492</td>\n",
       "            <td>448.12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>42.67</td>\n",
       "            <td>208.327405</td>\n",
       "            <td>432.20</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Egypt', 2019, Decimal('99.33'), Decimal('97.00'), None, None),\n",
       " ('Egypt', 2020, Decimal('99.33'), Decimal('97.67'), None, None),\n",
       " ('Ethiopia', 2019, Decimal('57.00'), Decimal('11.67'), Decimal('114.120594'), Decimal('95.91')),\n",
       " ('Ethiopia', 2020, Decimal('58.00'), Decimal('11.67'), Decimal('117.190911'), Decimal('107.66')),\n",
       " ('Kenya', 2019, Decimal('66.33'), Decimal('33.33'), Decimal('50.951450'), Decimal('100.38')),\n",
       " ('Kenya', 2020, Decimal('67.00'), Decimal('33.67'), Decimal('51.985780'), Decimal('100.67')),\n",
       " ('Congo', 2019, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('Congo', 2020, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('South Africa', 2019, Decimal('91.67'), Decimal('78.00'), Decimal('58.087055'), Decimal('388.53')),\n",
       " ('South Africa', 2020, Decimal('92.00'), Decimal('78.67'), Decimal('58.801927'), Decimal('337.62')),\n",
       " ('Nigeria', 2019, Decimal('75.67'), Decimal('41.67'), Decimal('203.304492'), Decimal('448.12')),\n",
       " ('Nigeria', 2020, Decimal('77.33'), Decimal('42.67'), Decimal('208.327405'), Decimal('432.20'))]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "Country_name, \t \n",
    "Time_period, \n",
    "Pct_managed_drinking_water_services, \n",
    "Pct_managed_sanitation_services, \n",
    "Est_population_in_millions, \n",
    "Est_gdp_in_billions \n",
    "FROM \n",
    "united_nations.Access_to_Basic_Services \n",
    "WHERE \n",
    "Country_name IN (\n",
    "        'Nigeria', \n",
    "        'Ethiopia' , \n",
    "        'Congo', \n",
    "        'Egypt', \n",
    "        'Tanzania', \n",
    "        'Kenya', \n",
    "        'South Africa'\n",
    " )\n",
    "AND Time_period BETWEEN 2019 AND 2020;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3: Combining Multiple Conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "41 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Region</th>\n",
       "            <th>Country_name</th>\n",
       "            <th>Time_period</th>\n",
       "            <th>Pct_managed_drinking_water_services</th>\n",
       "            <th>Pct_managed_sanitation_services</th>\n",
       "            <th>Est_population_in_millions</th>\n",
       "            <th>Est_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>Haiti</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.00</td>\n",
       "            <td>36.00</td>\n",
       "            <td>11.306801</td>\n",
       "            <td>14.51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northern Africa and Western Asia</td>\n",
       "            <td>Sudan</td>\n",
       "            <td>2020</td>\n",
       "            <td>62.33</td>\n",
       "            <td>40.33</td>\n",
       "            <td>44.440486</td>\n",
       "            <td>27.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Papua New Guinea</td>\n",
       "            <td>2020</td>\n",
       "            <td>56.67</td>\n",
       "            <td>27.67</td>\n",
       "            <td>9.749640</td>\n",
       "            <td>23.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Solomon Islands</td>\n",
       "            <td>2020</td>\n",
       "            <td>72.33</td>\n",
       "            <td>44.67</td>\n",
       "            <td>0.691191</td>\n",
       "            <td>1.54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Kiribati</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.00</td>\n",
       "            <td>45.33</td>\n",
       "            <td>0.126463</td>\n",
       "            <td>0.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Burundi</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.33</td>\n",
       "            <td>44.33</td>\n",
       "            <td>12.220227</td>\n",
       "            <td>2.65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Ethiopia</td>\n",
       "            <td>2020</td>\n",
       "            <td>58.00</td>\n",
       "            <td>11.67</td>\n",
       "            <td>117.190911</td>\n",
       "            <td>107.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Kenya</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.00</td>\n",
       "            <td>33.67</td>\n",
       "            <td>51.985780</td>\n",
       "            <td>100.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Madagascar</td>\n",
       "            <td>2020</td>\n",
       "            <td>56.33</td>\n",
       "            <td>13.00</td>\n",
       "            <td>28.225177</td>\n",
       "            <td>13.05</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Malawi</td>\n",
       "            <td>2020</td>\n",
       "            <td>74.33</td>\n",
       "            <td>28.67</td>\n",
       "            <td>19.377061</td>\n",
       "            <td>12.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Mozambique</td>\n",
       "            <td>2020</td>\n",
       "            <td>66.67</td>\n",
       "            <td>40.33</td>\n",
       "            <td>31.178239</td>\n",
       "            <td>14.03</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Somalia</td>\n",
       "            <td>2020</td>\n",
       "            <td>57.33</td>\n",
       "            <td>40.00</td>\n",
       "            <td>16.537016</td>\n",
       "            <td>6.88</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>South Sudan</td>\n",
       "            <td>2020</td>\n",
       "            <td>48.33</td>\n",
       "            <td>22.33</td>\n",
       "            <td>10.606227</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Uganda</td>\n",
       "            <td>2020</td>\n",
       "            <td>61.00</td>\n",
       "            <td>21.67</td>\n",
       "            <td>44.404611</td>\n",
       "            <td>37.60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>United Republic of Tanzania</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.00</td>\n",
       "            <td>34.00</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Zambia</td>\n",
       "            <td>2020</td>\n",
       "            <td>66.67</td>\n",
       "            <td>32.67</td>\n",
       "            <td>18.927715</td>\n",
       "            <td>18.11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Zimbabwe</td>\n",
       "            <td>2020</td>\n",
       "            <td>68.00</td>\n",
       "            <td>36.33</td>\n",
       "            <td>15.669666</td>\n",
       "            <td>21.51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Angola</td>\n",
       "            <td>2020</td>\n",
       "            <td>52.33</td>\n",
       "            <td>47.00</td>\n",
       "            <td>33.428486</td>\n",
       "            <td>53.62</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Cameroon</td>\n",
       "            <td>2020</td>\n",
       "            <td>64.00</td>\n",
       "            <td>43.00</td>\n",
       "            <td>26.491087</td>\n",
       "            <td>40.77</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Central African Republic</td>\n",
       "            <td>2020</td>\n",
       "            <td>38.33</td>\n",
       "            <td>15.00</td>\n",
       "            <td>5.343020</td>\n",
       "            <td>2.33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Chad</td>\n",
       "            <td>2020</td>\n",
       "            <td>52.67</td>\n",
       "            <td>18.67</td>\n",
       "            <td>16.644701</td>\n",
       "            <td>10.72</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>69.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Democratic Republic of the Congo</td>\n",
       "            <td>2020</td>\n",
       "            <td>47.67</td>\n",
       "            <td>15.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Gabon</td>\n",
       "            <td>2020</td>\n",
       "            <td>73.33</td>\n",
       "            <td>47.00</td>\n",
       "            <td>2.292573</td>\n",
       "            <td>15.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Sao Tome and Principe</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>46.00</td>\n",
       "            <td>0.218641</td>\n",
       "            <td>0.47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Lesotho</td>\n",
       "            <td>2020</td>\n",
       "            <td>76.33</td>\n",
       "            <td>49.67</td>\n",
       "            <td>2.254100</td>\n",
       "            <td>2.23</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Namibia</td>\n",
       "            <td>2020</td>\n",
       "            <td>83.67</td>\n",
       "            <td>35.00</td>\n",
       "            <td>2.489098</td>\n",
       "            <td>10.58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Benin</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.33</td>\n",
       "            <td>17.33</td>\n",
       "            <td>12.643123</td>\n",
       "            <td>15.65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Burkina Faso</td>\n",
       "            <td>2020</td>\n",
       "            <td>53.33</td>\n",
       "            <td>25.00</td>\n",
       "            <td>21.522626</td>\n",
       "            <td>17.93</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Côte d&#x27;Ivoire</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.67</td>\n",
       "            <td>34.67</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Gambia</td>\n",
       "            <td>2020</td>\n",
       "            <td>79.33</td>\n",
       "            <td>44.33</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Ghana</td>\n",
       "            <td>2020</td>\n",
       "            <td>84.67</td>\n",
       "            <td>23.00</td>\n",
       "            <td>32.180401</td>\n",
       "            <td>70.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Guinea</td>\n",
       "            <td>2020</td>\n",
       "            <td>67.33</td>\n",
       "            <td>32.33</td>\n",
       "            <td>13.205153</td>\n",
       "            <td>14.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Guinea-Bissau</td>\n",
       "            <td>2020</td>\n",
       "            <td>60.00</td>\n",
       "            <td>19.33</td>\n",
       "            <td>2.015828</td>\n",
       "            <td>1.43</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Liberia</td>\n",
       "            <td>2020</td>\n",
       "            <td>75.00</td>\n",
       "            <td>17.67</td>\n",
       "            <td>5.087584</td>\n",
       "            <td>3.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Mali</td>\n",
       "            <td>2020</td>\n",
       "            <td>83.67</td>\n",
       "            <td>46.00</td>\n",
       "            <td>21.224040</td>\n",
       "            <td>17.47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Mauritania</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.33</td>\n",
       "            <td>48.00</td>\n",
       "            <td>4.498604</td>\n",
       "            <td>8.41</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Niger</td>\n",
       "            <td>2020</td>\n",
       "            <td>57.33</td>\n",
       "            <td>24.67</td>\n",
       "            <td>24.333639</td>\n",
       "            <td>13.74</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Nigeria</td>\n",
       "            <td>2020</td>\n",
       "            <td>77.33</td>\n",
       "            <td>42.67</td>\n",
       "            <td>208.327405</td>\n",
       "            <td>432.20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Sierra Leone</td>\n",
       "            <td>2020</td>\n",
       "            <td>65.00</td>\n",
       "            <td>17.33</td>\n",
       "            <td>8.233970</td>\n",
       "            <td>4.06</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Togo</td>\n",
       "            <td>2020</td>\n",
       "            <td>70.67</td>\n",
       "            <td>20.00</td>\n",
       "            <td>8.442580</td>\n",
       "            <td>7.57</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Latin America and the Caribbean', 'Haiti', 2020, Decimal('65.00'), Decimal('36.00'), Decimal('11.306801'), Decimal('14.51')),\n",
       " ('Northern Africa and Western Asia', 'Sudan', 2020, Decimal('62.33'), Decimal('40.33'), Decimal('44.440486'), Decimal('27.03')),\n",
       " ('Oceania', 'Papua New Guinea', 2020, Decimal('56.67'), Decimal('27.67'), Decimal('9.749640'), Decimal('23.85')),\n",
       " ('Oceania', 'Solomon Islands', 2020, Decimal('72.33'), Decimal('44.67'), Decimal('0.691191'), Decimal('1.54')),\n",
       " ('Oceania', 'Kiribati', 2020, Decimal('77.00'), Decimal('45.33'), Decimal('0.126463'), Decimal('0.18')),\n",
       " ('Sub-Saharan Africa', 'Burundi', 2020, Decimal('70.33'), Decimal('44.33'), Decimal('12.220227'), Decimal('2.65')),\n",
       " ('Sub-Saharan Africa', 'Ethiopia', 2020, Decimal('58.00'), Decimal('11.67'), Decimal('117.190911'), Decimal('107.66')),\n",
       " ('Sub-Saharan Africa', 'Kenya', 2020, Decimal('67.00'), Decimal('33.67'), Decimal('51.985780'), Decimal('100.67')),\n",
       " ('Sub-Saharan Africa', 'Madagascar', 2020, Decimal('56.33'), Decimal('13.00'), Decimal('28.225177'), Decimal('13.05')),\n",
       " ('Sub-Saharan Africa', 'Malawi', 2020, Decimal('74.33'), Decimal('28.67'), Decimal('19.377061'), Decimal('12.18')),\n",
       " ('Sub-Saharan Africa', 'Mozambique', 2020, Decimal('66.67'), Decimal('40.33'), Decimal('31.178239'), Decimal('14.03')),\n",
       " ('Sub-Saharan Africa', 'Somalia', 2020, Decimal('57.33'), Decimal('40.00'), Decimal('16.537016'), Decimal('6.88')),\n",
       " ('Sub-Saharan Africa', 'South Sudan', 2020, Decimal('48.33'), Decimal('22.33'), Decimal('10.606227'), None),\n",
       " ('Sub-Saharan Africa', 'Uganda', 2020, Decimal('61.00'), Decimal('21.67'), Decimal('44.404611'), Decimal('37.60')),\n",
       " ('Sub-Saharan Africa', 'United Republic of Tanzania', 2020, Decimal('65.00'), Decimal('34.00'), None, None),\n",
       " ('Sub-Saharan Africa', 'Zambia', 2020, Decimal('66.67'), Decimal('32.67'), Decimal('18.927715'), Decimal('18.11')),\n",
       " ('Sub-Saharan Africa', 'Zimbabwe', 2020, Decimal('68.00'), Decimal('36.33'), Decimal('15.669666'), Decimal('21.51')),\n",
       " ('Sub-Saharan Africa', 'Angola', 2020, Decimal('52.33'), Decimal('47.00'), Decimal('33.428486'), Decimal('53.62')),\n",
       " ('Sub-Saharan Africa', 'Cameroon', 2020, Decimal('64.00'), Decimal('43.00'), Decimal('26.491087'), Decimal('40.77')),\n",
       " ('Sub-Saharan Africa', 'Central African Republic', 2020, Decimal('38.33'), Decimal('15.00'), Decimal('5.343020'), Decimal('2.33')),\n",
       " ('Sub-Saharan Africa', 'Chad', 2020, Decimal('52.67'), Decimal('18.67'), Decimal('16.644701'), Decimal('10.72')),\n",
       " ('Sub-Saharan Africa', 'Congo', 2020, Decimal('69.00'), Decimal('17.67'), None, None),\n",
       " ('Sub-Saharan Africa', 'Democratic Republic of the Congo', 2020, Decimal('47.67'), Decimal('15.33'), None, None),\n",
       " ('Sub-Saharan Africa', 'Gabon', 2020, Decimal('73.33'), Decimal('47.00'), Decimal('2.292573'), Decimal('15.31')),\n",
       " ('Sub-Saharan Africa', 'Sao Tome and Principe', 2020, Decimal('77.33'), Decimal('46.00'), Decimal('0.218641'), Decimal('0.47')),\n",
       " ('Sub-Saharan Africa', 'Lesotho', 2020, Decimal('76.33'), Decimal('49.67'), Decimal('2.254100'), Decimal('2.23')),\n",
       " ('Sub-Saharan Africa', 'Namibia', 2020, Decimal('83.67'), Decimal('35.00'), Decimal('2.489098'), Decimal('10.58')),\n",
       " ('Sub-Saharan Africa', 'Benin', 2020, Decimal('65.33'), Decimal('17.33'), Decimal('12.643123'), Decimal('15.65')),\n",
       " ('Sub-Saharan Africa', 'Burkina Faso', 2020, Decimal('53.33'), Decimal('25.00'), Decimal('21.522626'), Decimal('17.93')),\n",
       " ('Sub-Saharan Africa', \"Côte d'Ivoire\", 2020, Decimal('70.67'), Decimal('34.67'), None, None),\n",
       " ('Sub-Saharan Africa', 'Gambia', 2020, Decimal('79.33'), Decimal('44.33'), None, None),\n",
       " ('Sub-Saharan Africa', 'Ghana', 2020, Decimal('84.67'), Decimal('23.00'), Decimal('32.180401'), Decimal('70.04')),\n",
       " ('Sub-Saharan Africa', 'Guinea', 2020, Decimal('67.33'), Decimal('32.33'), Decimal('13.205153'), Decimal('14.18')),\n",
       " ('Sub-Saharan Africa', 'Guinea-Bissau', 2020, Decimal('60.00'), Decimal('19.33'), Decimal('2.015828'), Decimal('1.43')),\n",
       " ('Sub-Saharan Africa', 'Liberia', 2020, Decimal('75.00'), Decimal('17.67'), Decimal('5.087584'), Decimal('3.04')),\n",
       " ('Sub-Saharan Africa', 'Mali', 2020, Decimal('83.67'), Decimal('46.00'), Decimal('21.224040'), Decimal('17.47')),\n",
       " ('Sub-Saharan Africa', 'Mauritania', 2020, Decimal('70.33'), Decimal('48.00'), Decimal('4.498604'), Decimal('8.41')),\n",
       " ('Sub-Saharan Africa', 'Niger', 2020, Decimal('57.33'), Decimal('24.67'), Decimal('24.333639'), Decimal('13.74')),\n",
       " ('Sub-Saharan Africa', 'Nigeria', 2020, Decimal('77.33'), Decimal('42.67'), Decimal('208.327405'), Decimal('432.20')),\n",
       " ('Sub-Saharan Africa', 'Sierra Leone', 2020, Decimal('65.00'), Decimal('17.33'), Decimal('8.233970'), Decimal('4.06')),\n",
       " ('Sub-Saharan Africa', 'Togo', 2020, Decimal('70.67'), Decimal('20.00'), Decimal('8.442580'), Decimal('7.57'))]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "Region,\n",
    "Country_name, \t \n",
    "Time_period, \n",
    "Pct_managed_drinking_water_services, \n",
    "Pct_managed_sanitation_services, \n",
    "Est_population_in_millions, \n",
    "Est_gdp_in_billions \n",
    "FROM \n",
    "united_nations.Access_to_Basic_Services \n",
    "WHERE \n",
    "Time_period = 2020\n",
    "AND Pct_managed_sanitation_services <= 50;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div align=\"center\" style=\" font-size: 80%; text-align: center; margin: 0 auto\">\n",
    "<img src=\"https://raw.githubusercontent.com/Explore-AI/Pictures/master/ExploreAI_logos/EAI_Blue_Dark.png\"  style=\"width:200px\";/>\n",
    "</div>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}