Kurz_SQL_D1 c.ipynb

{
 "cells":[
  {
   "cell_type":"code",
   "source":[
    "-- Daj tam cele cislo\/int 1\n",
    "-- Jednoriadkovy komentar v jazyku SQL\n",
    "select 1;\n",
    "-- Daj tam retazec\/varchar Ahoj svet\n",
    "select \"Ahoj svet\";\n",
    "-- Daj tam float\/real 999.99 \n",
    "select 999.99;\n",
    "-- Daj tam date\/datum\n",
    "select \"2022-10-3\";\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ],
   "execution_count":7,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>2022-10-3<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>2022-10-3<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"U0V62VTTN9myHPW7WNVZzQ",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_1",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    
   ],
   "execution_count":null,
   "outputs":[
    
   ],
   "metadata":{
    "datalore":{
     "node_id":"nbTtQEh3KSOqo0qR6vjxZ1",
     "type":"CODE",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "-- Zoznam vsetkych databaz\n",
    "show databases;\n",
    "-- Zoznam vsetkych tabuliek\n",
    "show tables;\n",
    "-- Zoznam vsetkych pouzitelov\n",
    "show users;\n",
    "show views;"
   ],
   "execution_count":11,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>Tables_in_Kurz_SQL<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Album<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>City<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>DEPT<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Data_SQLZoo_Nobel_csv<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Data_SQLZoo_World_csv<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>EMP<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>T1<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>7<\/th>\n",
       "      <td>T10<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>8<\/th>\n",
       "      <td>T100<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>9<\/th>\n",
       "      <td>World<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>10<\/th>\n",
       "      <td>world_csv<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"NkgPw0ieWMOMgw94ngWrYA",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_2",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "-- Vypis nazov db, na ktoru si pripojeny\n",
    "select DATABASE();\n",
    "use Kurz_SQL;\n",
    "select DATABASE();\n"
   ],
   "execution_count":15,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>DATABASE()<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Kurz_SQL<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"sa1JxZCmAjVX2veUj7qxNV",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_3",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "-- select * from World;\n",
    "-- select name, area, population from World;\n",
    "-- select area, area\/1000, population, name  from World;\n",
    "-- Verzia MySQL\n",
    "select @@version;\n"
   ],
   "execution_count":22,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>@@version<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>5.7.34-37-log<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"35EILpmObDdL0jABTUGuoT",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_4",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "-- Operacie aritmeticke + - \/ *\n",
    "select 10-20;\n",
    "-- Delenie realne\n",
    "select 5\/2;\n",
    "-- Delenie celociselne\n",
    "select 5 div 2;\n",
    "-- Delenie so zvyskom\/modulo\n",
    "select 5 % 2;\n",
    "select 5.0 \/ 0.0;"
   ],
   "execution_count":29,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>5.0 \/ 0.0<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>None<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"HA0L2g0HDL1fdgdKhiHl6G",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_5",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "-- describe World;\n",
    "-- show columns from World;\n",
    "show fields from World;"
   ],
   "execution_count":32,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>Field<\/th>\n",
       "      <th>Type<\/th>\n",
       "      <th>Null<\/th>\n",
       "      <th>Key<\/th>\n",
       "      <th>Default<\/th>\n",
       "      <th>Extra<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>name<\/td>\n",
       "      <td>varchar(32)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>continent<\/td>\n",
       "      <td>varchar(13)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>area<\/td>\n",
       "      <td>int(11)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>population<\/td>\n",
       "      <td>int(11)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>gdp<\/td>\n",
       "      <td>varchar(11)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>capital<\/td>\n",
       "      <td>varchar(25)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>tld<\/td>\n",
       "      <td>varchar(3)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>7<\/th>\n",
       "      <td>flag<\/td>\n",
       "      <td>varchar(94)<\/td>\n",
       "      <td>YES<\/td>\n",
       "      <td><\/td>\n",
       "      <td>None<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"prvLdkdaR6xxxmWLNG2bk6",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_6",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "1. Introducing the world table of countries\n",
    "\n",
    "The example uses a WHERE clause to show the population of 'France'. Note that strings (pieces of text that are data) should be in 'single quotes';\n",
    "\n",
    "Modify it to show the population of Germany\/Slovakia\n",
    "*\/\n",
    "select population\n",
    "from World\n",
    "where name = 'Slovakia'\n"
   ],
   "execution_count":37,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>population<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>5415949<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"7qhLAPQeFk4xOThrMOb82n",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_7",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "2. Scandinavia\n",
    "\n",
    "Checking a list The word IN allows us to check if an item is in a list. The example shows the name and population for the countries 'Brazil', 'Russia', 'India' and 'China'.\n",
    "\n",
    "Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.\n",
    "*\/\n",
    "select name, population\n",
    "from World\n",
    "where name in ('Sweden', 'Norway', 'Denmark')\n",
    "-- order by population DESC -- ASC\n",
    "order by RAND()\n"
   ],
   "execution_count":46,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "      <th>population<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Sweden<\/td>\n",
       "      <td>9675885<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Norway<\/td>\n",
       "      <td>5124383<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Denmark<\/td>\n",
       "      <td>5634437<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"LE6l5iG7ZlF5PATZdwahJ8",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_8",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "3. Just the right size\n",
    "Which countries are not too small and not too big? BETWEEN allows range checking (range specified is inclusive of boundary values). The example below shows countries with an area of 250,000-300,000 sq. km. Modify it to show the country and the area for countries with an area between 200,000 and 250,000.\n",
    "*\/\n",
    "\n",
    "select name, area\n",
    "from World\n",
    "-- where area between 200000 and 250000;\n",
    "where area >= 200000 \n",
    "and area <= 250000;"
   ],
   "execution_count":51,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "      <th>area<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Belarus<\/td>\n",
       "      <td>207600<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Ghana<\/td>\n",
       "      <td>238533<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Guinea<\/td>\n",
       "      <td>245857<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Guyana<\/td>\n",
       "      <td>214969<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Laos<\/td>\n",
       "      <td>236800<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>Romania<\/td>\n",
       "      <td>238391<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>Uganda<\/td>\n",
       "      <td>241550<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>7<\/th>\n",
       "      <td>United Kingdom<\/td>\n",
       "      <td>242900<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"8YchspQLeWhCOvLN9CaIWw",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_9",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "select name, continent, area\n",
    "from World\n",
    "order by continent ASC, area DESC"
   ],
   "execution_count":54,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "      <th>continent<\/th>\n",
       "      <th>area<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Algeria<\/td>\n",
       "      <td>Africa<\/td>\n",
       "      <td>2381741<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Congo, Democratic Republic of<\/td>\n",
       "      <td>Africa<\/td>\n",
       "      <td>2345000<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Sudan<\/td>\n",
       "      <td>Africa<\/td>\n",
       "      <td>1886068<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Libya<\/td>\n",
       "      <td>Africa<\/td>\n",
       "      <td>1759540<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Chad<\/td>\n",
       "      <td>Africa<\/td>\n",
       "      <td>1284000<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>...<\/th>\n",
       "      <td>...<\/td>\n",
       "      <td>...<\/td>\n",
       "      <td>...<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>190<\/th>\n",
       "      <td>Ecuador<\/td>\n",
       "      <td>South America<\/td>\n",
       "      <td>276841<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>191<\/th>\n",
       "      <td>Guyana<\/td>\n",
       "      <td>South America<\/td>\n",
       "      <td>214969<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>192<\/th>\n",
       "      <td>Uruguay<\/td>\n",
       "      <td>South America<\/td>\n",
       "      <td>181034<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>193<\/th>\n",
       "      <td>Suriname<\/td>\n",
       "      <td>South America<\/td>\n",
       "      <td>163820<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>194<\/th>\n",
       "      <td>Saint Vincent and the Grenadines<\/td>\n",
       "      <td>South America<\/td>\n",
       "      <td>389<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<p>195 rows × 3 columns<\/p>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"ribtH2hZcUvVfPYDvRiT5u",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_10",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "SELECT Name, population\n",
    "from World\n",
    "-- where name in ('Sweden', 'Norway', 'Denmark')\n",
    "where name = 'Sweden'\n",
    "or name = 'Norway'\n",
    "or name = 'Denmark'"
   ],
   "execution_count":58,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>Name<\/th>\n",
       "      <th>population<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Denmark<\/td>\n",
       "      <td>5634437<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Norway<\/td>\n",
       "      <td>5124383<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Sweden<\/td>\n",
       "      <td>9675885<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"RGKygWN2qaHbXTLGHG6hIi",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_11",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "1. You can use WHERE name LIKE 'B%' to find the countries that start with \"B\".\n",
    "\n",
    "The % is a wild-card it can match any characters\n",
    "Find the country that start with Y\n",
    "*\/\n",
    "\n",
    "select name, capital\n",
    "from World\n",
    "-- where name like \"Afganistan\"\n",
    "-- where name like \"A%\"\n",
    "-- where name like \"%A\"\n",
    "-- where name like \"%A%\"\n",
    "where name like \"Y%\""
   ],
   "execution_count":68,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "      <th>capital<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Yemen<\/td>\n",
       "      <td>Sana‘a<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"MmEBvSr24DPy1Bx293eCXO",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_12",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "2. Find the countries that end with y\n",
    "*\/\n",
    "\n",
    "select name, capital\n",
    "from World\n",
    "where name like \"%y\""
   ],
   "execution_count":71,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "      <th>capital<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Germany<\/td>\n",
       "      <td>Bonn<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Hungary<\/td>\n",
       "      <td>Budapest<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Italy<\/td>\n",
       "      <td>Rome<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Norway<\/td>\n",
       "      <td>Oslo<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Paraguay<\/td>\n",
       "      <td>Asunción<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>Turkey<\/td>\n",
       "      <td>Ankara<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>Uruguay<\/td>\n",
       "      <td>Montevideo<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>7<\/th>\n",
       "      <td>Vatican City<\/td>\n",
       "      <td><\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"SSibxayO5LlPPvAAyHp3ZL",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_13",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "3. Luxembourg has an x - so does one other country. List them both.\n",
    "\n",
    "Find the countries that contain the letter x\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"%x%\""
   ],
   "execution_count":73,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Luxembourg<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Mexico<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"mwi7jya68GcWRwMZSxJdQR",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_14",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "4. Iceland, Switzerland end with land - but are there others?\n",
    "\n",
    "Find the countries that end with land\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"%land\""
   ],
   "execution_count":74,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Finland<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Iceland<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Ireland<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>New Zealand<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Poland<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>Swaziland<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>Switzerland<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>7<\/th>\n",
       "      <td>Thailand<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"X5aTZQ40DlcDkeeyulcbff",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_15",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "5. Columbia starts with a C and ends with ia - there are two more like this.\n",
    "\n",
    "Find the countries that start with C and end with ia\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"C%ia\""
   ],
   "execution_count":78,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Cambodia<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Colombia<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Croatia<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"IzwoZb14eif2xr0fmbjUYp",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_16",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "6. Greece has a double e - who has a double o?\n",
    "\n",
    "Find the country that has oo in the name\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"%oo%\""
   ],
   "execution_count":79,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Cameroon<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"P31bAIaj0hZasnHFziseQ8",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_17",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/* 7. Bahamas has three a - who else?\n",
    "\n",
    "Find the countries that have three or more a in the name\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"%a%a%a%\""
   ],
   "execution_count":80,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Afganistan<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Albania<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Antigua and Barbuda<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Australia<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Azerbaijan<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>Bahamas<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>Bosnia and Herzegovina<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>7<\/th>\n",
       "      <td>Canada<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>8<\/th>\n",
       "      <td>Central African Republic<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>9<\/th>\n",
       "      <td>Equatorial Guinea<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>10<\/th>\n",
       "      <td>Guatemala<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>11<\/th>\n",
       "      <td>Jamaica<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>12<\/th>\n",
       "      <td>Kazakhstan<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>13<\/th>\n",
       "      <td>Madagascar<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>14<\/th>\n",
       "      <td>Malaysia<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>15<\/th>\n",
       "      <td>Marshall Islands<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>16<\/th>\n",
       "      <td>Mauritania<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>17<\/th>\n",
       "      <td>Micronesia, Federated States of<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>18<\/th>\n",
       "      <td>Nicaragua<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>19<\/th>\n",
       "      <td>Panama<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>20<\/th>\n",
       "      <td>Papua New Guinea<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>21<\/th>\n",
       "      <td>Paraguay<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>22<\/th>\n",
       "      <td>Saint Vincent and the Grenadines<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>23<\/th>\n",
       "      <td>Saudi Arabia<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>24<\/th>\n",
       "      <td>Tanzania<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>25<\/th>\n",
       "      <td>Trinidad and Tobago<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>26<\/th>\n",
       "      <td>United Arab Emirates<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"77LqHV7ASU5nr8Ni4m919O",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_18",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "8. India and Angola have an n as the second character. You can use the underscore as a single character wildcard.\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"_n%\"\n",
    "AND name like \"a%\""
   ],
   "execution_count":82,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Andorra<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Angola<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Antigua and Barbuda<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"KFpL7NLeSWuzdQi9eznIDv",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_19",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "9. Lesotho and Moldova both have two o characters separated by two other characters.\n",
    "\n",
    "Find the countries that have two \"o\" characters separated by two others.\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"%o__o%\"\n",
    "\n"
   ],
   "execution_count":83,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Congo, Democratic Republic of<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Congo, Republic of<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Lesotho<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Moldova<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Mongolia<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>Morocco<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>Sao Tomé and Príncipe<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"U2WYWwDucpJ2cKRkgspCuR",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_20",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/*\n",
    "10. Cuba and Togo have four characters names.\n",
    "\n",
    "Find the countries that have exactly four characters.\n",
    "*\/\n",
    "\n",
    "select name\n",
    "from World\n",
    "where name like \"____\""
   ],
   "execution_count":84,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Chad<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Cuba<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>Fiji<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Iran<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>4<\/th>\n",
       "      <td>Iraq<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>5<\/th>\n",
       "      <td>Laos<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>6<\/th>\n",
       "      <td>Mali<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>7<\/th>\n",
       "      <td>Oman<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>8<\/th>\n",
       "      <td>Peru<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>9<\/th>\n",
       "      <td>Togo<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"2HvIQWbD4fFGJqJotEM9aV",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_21",
      "dialect":"mysql.8"
     }
    }
   }
  },
  {
   "cell_type":"code",
   "source":[
    "\/* 11. The capital of Luxembourg is Luxembourg. Show all the countries where the capital is the same as the name of the country\n",
    "\n",
    "Find the country where the name is the capital city.\n",
    "*\/\n",
    "\n",
    "SELECT name\n",
    "  FROM World\n",
    " WHERE name LIKE capital"
   ],
   "execution_count":87,
   "outputs":[
    {
     "data":{
      "text\/html":[
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "<\/style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th><\/th>\n",
       "      <th>name<\/th>\n",
       "    <\/tr>\n",
       "  <\/thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0<\/th>\n",
       "      <td>Djibouti<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>1<\/th>\n",
       "      <td>Luxembourg<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>2<\/th>\n",
       "      <td>San Marino<\/td>\n",
       "    <\/tr>\n",
       "    <tr>\n",
       "      <th>3<\/th>\n",
       "      <td>Singapore<\/td>\n",
       "    <\/tr>\n",
       "  <\/tbody>\n",
       "<\/table>\n",
       "<\/div>"
      ]
     },
     "metadata":{
      
     },
     "output_type":"display_data"
    }
   ],
   "metadata":{
    "datalore":{
     "node_id":"jc6Gz6PEFgMErmH4ualVjv",
     "type":"SQL",
     "hide_input_from_viewers":false,
     "hide_output_from_viewers":false,
     "sql_cell_properties":{
      "dbId":"FCwQXbeBnu6d8c7VnEMBT9",
      "variableName":"df_22",
      "dialect":"mysql.8"
     }
    }
   }
  }
 ],
 "metadata":{
  "kernelspec":{
   "display_name":"Python",
   "language":"python",
   "name":"python"
  },
  "datalore":{
   "version":1,
   "computation_mode":"JUPYTER",
   "package_manager":"pip",
   "base_environment":"default",
   "packages":[
    
   ]
  }
 },
 "nbformat":4,
 "nbformat_minor":4
}