SQL_commands_in_pySpark.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initialize the spark on jupyter notebook\n",
    "\n",
    "import findspark\n",
    "findspark.init()\n",
    "\n",
    "\n",
    "# To create the dataframe in pyspark \n",
    "\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.types import StructType, StructField, StringType, IntegerType, FloatType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initializing the sparksesstion \n",
    "\n",
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .appName(\"Python Spark SQL basic example\") \\\n",
    "    .config(\"spark.some.config.option\", \"some-value\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "schema = StructType([StructField('_c0', IntegerType(), True), \n",
    "                      StructField('Country', StringType(), True), \n",
    "                      StructField('Year', IntegerType(), True), \n",
    "                      StructField('GDP', FloatType(), True)\n",
    "                     ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----------+----+---------+\n",
      "|_c0|    Country|Year|      GDP|\n",
      "+---+-----------+----+---------+\n",
      "|  0|Afghanistan|2015| 584.2592|\n",
      "|  1|Afghanistan|2014|612.69653|\n",
      "|  2|Afghanistan|2013|  631.745|\n",
      "|  3|Afghanistan|2012|  669.959|\n",
      "|  4|Afghanistan|2011| 63.53723|\n",
      "+---+-----------+----+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Redefining the dataframe with correct column labels \n",
    "\n",
    "df1 = spark.read.csv('country_data.csv', header=True, schema=schema)\n",
    "df1.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can do SQL operations on the dataframe by creating \n",
    "# a temporary view \n",
    "\n",
    "df1.createOrReplaceTempView('GDP')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|    Country|\n",
      "+-----------+\n",
      "|Afghanistan|\n",
      "|Afghanistan|\n",
      "|Afghanistan|\n",
      "|Afghanistan|\n",
      "|Afghanistan|\n",
      "+-----------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Now pass the SQL command as a string \n",
    "\n",
    "# Selecting one column from dataframe \n",
    "\n",
    "spark.sql('select Country from GDP').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---------+\n",
      "|    Country|      GDP|\n",
      "+-----------+---------+\n",
      "|Afghanistan| 584.2592|\n",
      "|Afghanistan|612.69653|\n",
      "|Afghanistan|  631.745|\n",
      "|Afghanistan|  669.959|\n",
      "|Afghanistan| 63.53723|\n",
      "+-----------+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Selecting multiple columns from dataframe \n",
    "\n",
    "spark.sql('select Country, GDP from GDP').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---------------+\n",
      "|    Country|length(Country)|\n",
      "+-----------+---------------+\n",
      "|Afghanistan|             11|\n",
      "|Afghanistan|             11|\n",
      "|Afghanistan|             11|\n",
      "|Afghanistan|             11|\n",
      "|Afghanistan|             11|\n",
      "+-----------+---------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# length counts the length of the string \n",
    "\n",
    "spark.sql('select Country, length(Country) from GDP').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+----------------------+\n",
      "|    Country|left(gdp.`Country`, 3)|\n",
      "+-----------+----------------------+\n",
      "|Afghanistan|                   Afg|\n",
      "|Afghanistan|                   Afg|\n",
      "|Afghanistan|                   Afg|\n",
      "|Afghanistan|                   Afg|\n",
      "|Afghanistan|                   Afg|\n",
      "+-----------+----------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Selecting first three letters from the country \n",
    "\n",
    "spark.sql(\"select Country, left(Country, 3) from GDP\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---------+\n",
      "|Year|      GDP|\n",
      "+----+---------+\n",
      "|2015| 743.7653|\n",
      "|2014|  76.2387|\n",
      "|2013|688.61725|\n",
      "|2012| 681.7926|\n",
      "|2011| 692.1167|\n",
      "+----+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# applying condition on selection\n",
    "\n",
    "spark.sql(\"select Year, GDP from GDP where Country = 'Nepal'\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|    Country|\n",
      "+-----------+\n",
      "| Bangladesh|\n",
      "|   Cambodia|\n",
      "|  Mauritius|\n",
      "|Philippines|\n",
      "|     Rwanda|\n",
      "+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# applying mathematical condition on selection\n",
    "\n",
    "spark.sql('select Country from GDP where GDP < 10').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-------------------+----+---------+\n",
      "|_c0|            Country|Year|      GDP|\n",
      "+---+-------------------+----+---------+\n",
      "|  4|        Afghanistan|2011| 63.53723|\n",
      "| 20|            Albania|2011|4437.1787|\n",
      "| 36|            Algeria|2011|5432.2524|\n",
      "| 52|             Angola|2011| 4299.129|\n",
      "| 68|Antigua and Barbuda|2011| 11929.35|\n",
      "+---+-------------------+----+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Star selects all the columns from the dataframe \n",
    "\n",
    "spark.sql(\"select * from GDP where Year = 2011\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+----+\n",
      "|            Country|Year|\n",
      "+-------------------+----+\n",
      "|        Afghanistan|2000|\n",
      "|            Albania|2000|\n",
      "|            Algeria|2000|\n",
      "|             Angola|2000|\n",
      "|Antigua and Barbuda|2000|\n",
      "+-------------------+----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Select multiple column with condition\n",
    "\n",
    "spark.sql(\"select Country, Year from GDP where Year = 2000\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+----+---------+\n",
      "|    Country|Year|      GDP|\n",
      "+-----------+----+---------+\n",
      "|      Japan|2000| 38532.49|\n",
      "| Luxembourg|2000|48735.996|\n",
      "|     Norway|2000|38146.715|\n",
      "|Switzerland|2000|37813.234|\n",
      "+-----------+----+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Selecting multiple columns with multiple conditions \n",
    "\n",
    "spark.sql(\"select Country, Year, GDP from GDP\\\n",
    "            where Year = 2000 and GDP > 35000\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+\n",
      "|Country|\n",
      "+-------+\n",
      "| Zambia|\n",
      "| Zambia|\n",
      "| Zambia|\n",
      "| Zambia|\n",
      "| Zambia|\n",
      "+-------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Selecting countries whose name starts from Z\n",
    "\n",
    "spark.sql(\"select Country from GDP where Country like 'Z%'\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|   Country|\n",
      "+----------+\n",
      "|Seychelles|\n",
      "|Seychelles|\n",
      "|Seychelles|\n",
      "|Seychelles|\n",
      "|Seychelles|\n",
      "+----------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Selecting country not having vowel a in its name and starting with S\n",
    "\n",
    "spark.sql(\"select Country from GDP\\\n",
    "            where Country not like '%a%' \\\n",
    "            and Country like 'S%'\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---------+\n",
      "|Year|      GDP|\n",
      "+----+---------+\n",
      "|2010|456.51248|\n",
      "|2009|3838.4343|\n",
      "|2008|3471.2476|\n",
      "|2007|2695.3662|\n",
      "|2006|299.22968|\n",
      "+----+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Use of between \n",
    "\n",
    "spark.sql(\"select Year, GDP from GDP \\\n",
    "            where Country = 'China' \\\n",
    "            and Year between 2005 and 2010\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+---------+\n",
      "|Country|      GDP|\n",
      "+-------+---------+\n",
      "|  India|438.86462|\n",
      "|  Nepal|231.42554|\n",
      "+-------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Use of in \n",
    "\n",
    "spark.sql(\"select Country, GDP from GDP \\\n",
    "            where Country in ('Nepal', 'India') \\\n",
    "            and Year = 2000\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------------------------+\n",
      "|   Country|(Country IN (Pakistan, India))|\n",
      "+----------+------------------------------+\n",
      "|      Cuba|                         false|\n",
      "|Mauritania|                         false|\n",
      "|  Djibouti|                         false|\n",
      "|  Slovenia|                         false|\n",
      "+----------+------------------------------+\n",
      "only showing top 4 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Use of in outside condition can be used to create yes no question \n",
    "\n",
    "spark.sql(\"select distinct Country, Country in ('Pakistan', 'India') from GDP\").show(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-------+----+---------+\n",
      "| _c0|Country|Year|      GDP|\n",
      "+----+-------+----+---------+\n",
      "| 536|   Chad|2007| 81.68388|\n",
      "| 535|   Chad|2008| 929.7724|\n",
      "| 666|   Cuba|2006|4669.6914|\n",
      "|1248|   Iraq|2001|     null|\n",
      "| 528|   Chad|2015| 777.2488|\n",
      "+----+-------+----+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Use of distinct \n",
    "\n",
    "spark.sql(\"select distinct * from GDP where length(Country) < 5\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+--------+----+---------+\n",
      "| _c0| Country|Year|      GDP|\n",
      "+----+--------+----+---------+\n",
      "|1266|  Israel|2015| 35729.37|\n",
      "|1277|  Israel|2004|19888.172|\n",
      "|2060|Portugal|2010|22538.654|\n",
      "|1825|   Nepal|2003|253.72412|\n",
      "|2064|Portugal|2006|19821.445|\n",
      "+----+--------+----+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# A combination for fun \n",
    "\n",
    "spark.sql(\"select distinct * from GDP where Country like '%l'\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----------+----+---------+\n",
      "| _c0|    Country|Year|      GDP|\n",
      "+----+-----------+----+---------+\n",
      "|2071|      Qatar|2015| 66346.52|\n",
      "| 112|  Australia|2015|56554.387|\n",
      "|2313|  Singapore|2015| 53629.74|\n",
      "|1829|Netherlands|2015|44292.883|\n",
      "+----+-----------+----+---------+\n",
      "only showing top 4 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Order by \n",
    "\n",
    "spark.sql(\"select * from GDP where Year = 2015 order by GDP desc\").show(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can create another temp view \n",
    "\n",
    "df1.createOrReplaceTempView('Country')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---------+\n",
      "|    Country|      GDP|\n",
      "+-----------+---------+\n",
      "|     Rwanda|5.6687264|\n",
      "| Bangladesh|4.6135745|\n",
      "|   Cambodia| 3.685949|\n",
      "|  Mauritius| 8.376432|\n",
      "|Philippines|  1.68135|\n",
      "+-----------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# We can select from COuntry now  \n",
    "\n",
    "spark.sql('SELECT DISTINCT Country, GDP FROM Country\\\n",
    "            WHERE GDP < 10').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "|            Country|\n",
      "+-------------------+\n",
      "|        Afghanistan|\n",
      "|            Albania|\n",
      "|            Algeria|\n",
      "|             Angola|\n",
      "|Antigua and Barbuda|\n",
      "+-------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# GDP is still working \n",
    "\n",
    "spark.sql('SELECT Country FROM GDP\\\n",
    "            WHERE Year=2010').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|             Country|\n",
      "+--------------------+\n",
      "|United Republic o...|\n",
      "|United States of ...|\n",
      "|United Arab Emirates|\n",
      "|United Kingdom of...|\n",
      "+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# How many country name start with United?\n",
    "\n",
    "spark.sql(\"select distinct Country from GDP where Country like 'United%'\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|    Country|\n",
      "+-----------+\n",
      "|  Singapore|\n",
      "|    Germany|\n",
      "|     Norway|\n",
      "|    Ireland|\n",
      "|    Iceland|\n",
      "|Switzerland|\n",
      "|     Canada|\n",
      "|  Australia|\n",
      "|    Austria|\n",
      "+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Subquerry \n",
    "# Which country in 2010 had GDP larger than the GDP of Germany in 2015?\n",
    "\n",
    "spark.sql(\"select distinct Country from GDP \\\n",
    "          where Year = 2010 and \\\n",
    "          GDP > (select GDP from GDP \\\n",
    "                          where Country = 'Germany'\\\n",
    "                          and Year = '2015')\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----------+----+---------+\n",
      "| _c0|    Country|Year|      GDP|\n",
      "+----+-----------+----+---------+\n",
      "|2036|Philippines|2002|  1.68135|\n",
      "| 479|   Cambodia|2000| 3.685949|\n",
      "| 205| Bangladesh|2002|4.6135745|\n",
      "+----+-----------+----+---------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Lowest GDP which is not null \n",
    "\n",
    "spark.sql(\"select * from GDP where GDP > 0 order by GDP asc\").show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+---------------+\n",
      "|Country|length(Country)|\n",
      "+-------+---------------+\n",
      "|   Fiji|              4|\n",
      "|   Mali|              4|\n",
      "|   Chad|              4|\n",
      "|   Oman|              4|\n",
      "|   Cuba|              4|\n",
      "|   Togo|              4|\n",
      "|   Iraq|              4|\n",
      "|   Niue|              4|\n",
      "|   Peru|              4|\n",
      "|  Sudan|              5|\n",
      "+-------+---------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Countries with the shortest name \n",
    "\n",
    "spark.sql(\"select distinct Country, length(Country) from GDP \\\n",
    "            where length(Country)>0 \\\n",
    "            order by length(Country)\").show(10) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----+\n",
      "|             Country|Year|\n",
      "+--------------------+----+\n",
      "|United Kingdom of...|2015|\n",
      "|United Kingdom of...|2011|\n",
      "|United Kingdom of...|2014|\n",
      "|United Kingdom of...|2013|\n",
      "+--------------------+----+\n",
      "only showing top 4 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Longest country name \n",
    "\n",
    "spark.sql(\"select Country, Year from GDP order by length(Country) desc\").show(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(Country='United Kingdom of Great Britain and Northern Ireland', Year=2015)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Viewing the full name \n",
    "\n",
    "spark.sql(\"select Country, Year from GDP order by length(Country) desc\").first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+\n",
      "|        sum(GDP)|\n",
      "+----------------+\n",
      "|725111.341104269|\n",
      "+----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Total GDP of the world in 2000\n",
    "\n",
    "spark.sql(\"select sum(GDP) from GDP where Year = 2000\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "|        mean(GDP)|\n",
      "+-----------------+\n",
      "|6202.344459533691|\n",
      "+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Average GDP of Chile \n",
    "\n",
    "spark.sql(\"select mean(GDP) from GDP where Country = 'Chile'\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------+\n",
      "|count(DISTINCT Country)|\n",
      "+-----------------------+\n",
      "|                    193|\n",
      "+-----------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Number of countires in the world \n",
    "\n",
    "spark.sql(\"select count(distinct Country) from GDP\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+\n",
      "|       sum(GDP)|\n",
      "+---------------+\n",
      "|972.72705078125|\n",
      "+---------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Total GDP of India and Pakistan in 2000\n",
    "\n",
    "spark.sql(\"select sum(GDP) from GDP \\\n",
    "            where Country in ('India', 'Pakistan')\\\n",
    "            and Year = 2000\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|count(Country)|\n",
      "+--------------+\n",
      "|             5|\n",
      "+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Number of countries with GDP more than 30000 in 2000\n",
    "\n",
    "spark.sql(\"select count(Country) from GDP \\\n",
    "            where Year = 2000 \\\n",
    "            and GDP > 30000\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}