sql/sql_with_spark/Mini_Project_SQL_with_Spark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL at Scale with Spark SQL\n",
    "\n",
    "Welcome to the SQL mini project. For this project, you will use the Domino Data Lab Platform and work through a series of exercises using Spark SQL. The dataset size may not be too big but the intent here is to familiarize yourself with the Spark SQL interface which scales easily to huge datasets, without you having to worry about changing your SQL queries. \n",
    "\n",
    "The data you need is present in the mini-project folder in the form of three CSV files. You need to make sure that these datasets are uploaded and present in the same directory as this notebook file, since we will be importing these files in Spark and create the following tables under the __`country_club`__ database using Spark SQL.\n",
    "\n",
    "1. The __`bookings`__ table,\n",
    "2. The __`facilities`__ table, and\n",
    "3. The __`members`__ table.\n",
    "\n",
    "You will be uploading these datasets shortly into Spark to understand how to create a database within minutes! Once the database and the tables are populated, you will be focusing on the mini-project questions.\n",
    "\n",
    "In the mini project, you'll be asked a series of questions. You can solve them using the Domino platform, but for the final deliverable, please download this notebook as an IPython notebook (__`File -> Export -> IPython Notebook`__) and upload it to your GitHub."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking Existence of Spark Environment Variables\n",
    "\n",
    "Make sure your notebook is loaded using a PySpark Workspace. If you open up a regular Jupyter workspace the following variables might not exist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - hive</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://domino-run-5d119564c9e77c000720ce10:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v2.4.0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>PySparkShell</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7f243d273550>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyspark.sql.context.SQLContext at 0x7f243d273400>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlContext"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run the following if you failed to open a notebook in the PySpark Workspace\n",
    "\n",
    "This will work assuming you are using Spark in the cloud on domino or you might need to configure with your own spark instance if you are working offline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "if 'sc' not in locals():\n",
    "    from pyspark.context import SparkContext\n",
    "    from pyspark.sql.context import SQLContext\n",
    "    from pyspark.sql.session import SparkSession\n",
    "    \n",
    "    sc = SparkContext()\n",
    "    sqlContext = SQLContext(sc)\n",
    "    spark = SparkSession(sc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a utility function to run SQL commands\n",
    "\n",
    "Instead of typing the same python functions repeatedly, we build a small function where you can just pass your query to get results.\n",
    "\n",
    "- Remember we are using Spark SQL in PySpark\n",
    "- We can't run multiple SQL statements in one go (no semi-colon ';' separated SQL statements)\n",
    "- We can run multi-line SQL queries (but still has to be a single statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def run_sql(statement):\n",
    "    try:\n",
    "        result = sqlContext.sql(statement)\n",
    "    except Exception as e:\n",
    "        print(e.desc, '\\n', e.stackTrace)\n",
    "        return\n",
    "    return result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating the Database\n",
    "\n",
    "We will first create our database in which we will be creating our three tables of interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>databaseName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>country_club</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>default</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   databaseName\n",
       "0  country_club\n",
       "1       default"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_sql('drop database if exists country_club cascade')\n",
    "run_sql('create database country_club')\n",
    "dbs = run_sql('show databases')\n",
    "dbs.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating the Tables\n",
    "\n",
    "In this section, we will be creating the three tables of interest and populate them with the data from the CSV files already available to you.\n",
    "\n",
    "To get started, first make sure you have already uploaded the three CSV files and they are present in the same directory as the notebook.\n",
    "\n",
    "Once you have done this, please remember to execute the following code to build the dataframes which will be saved as tables in our database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# File location and type\n",
    "file_location_bookings = \"./Bookings.csv\"\n",
    "file_location_facilities = \"./Facilities.csv\"\n",
    "file_location_members = \"./Members.csv\"\n",
    "\n",
    "file_type = \"csv\"\n",
    "\n",
    "# CSV options\n",
    "infer_schema = \"true\"\n",
    "first_row_is_header = \"true\"\n",
    "delimiter = \",\"\n",
    "\n",
    "# The applied options are for CSV files. For other file types, these will be ignored.\n",
    "bookings_df = (spark.read.format(file_type) \n",
    "                    .option(\"inferSchema\", infer_schema) \n",
    "                    .option(\"header\", first_row_is_header) \n",
    "                    .option(\"sep\", delimiter) \n",
    "                    .load(file_location_bookings))\n",
    "\n",
    "facilities_df = (spark.read.format(file_type) \n",
    "                      .option(\"inferSchema\", infer_schema) \n",
    "                      .option(\"header\", first_row_is_header) \n",
    "                      .option(\"sep\", delimiter) \n",
    "                      .load(file_location_facilities))\n",
    "\n",
    "members_df = (spark.read.format(file_type) \n",
    "                      .option(\"inferSchema\", infer_schema) \n",
    "                      .option(\"header\", first_row_is_header) \n",
    "                      .option(\"sep\", delimiter) \n",
    "                      .load(file_location_members))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Viewing the dataframe schemas\n",
    "\n",
    "We can take a look at the schemas of our potential tables to be written to our database soon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bookings Schema\n",
      "root\n",
      " |-- bookid: integer (nullable = true)\n",
      " |-- facid: integer (nullable = true)\n",
      " |-- memid: integer (nullable = true)\n",
      " |-- starttime: timestamp (nullable = true)\n",
      " |-- slots: integer (nullable = true)\n",
      "\n",
      "Facilities Schema\n",
      "root\n",
      " |-- facid: integer (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- membercost: double (nullable = true)\n",
      " |-- guestcost: double (nullable = true)\n",
      " |-- initialoutlay: integer (nullable = true)\n",
      " |-- monthlymaintenance: integer (nullable = true)\n",
      "\n",
      "Members Schema\n",
      "root\n",
      " |-- memid: integer (nullable = true)\n",
      " |-- surname: string (nullable = true)\n",
      " |-- firstname: string (nullable = true)\n",
      " |-- address: string (nullable = true)\n",
      " |-- zipcode: integer (nullable = true)\n",
      " |-- telephone: string (nullable = true)\n",
      " |-- recommendedby: integer (nullable = true)\n",
      " |-- joindate: timestamp (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('Bookings Schema')\n",
    "bookings_df.printSchema()\n",
    "print('Facilities Schema')\n",
    "facilities_df.printSchema()\n",
    "print('Members Schema')\n",
    "members_df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create permanent tables\n",
    "We will be creating three permanent tables here in our __`country_club`__ database as we discussed previously with the following code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "permanent_table_name_bookings = \"country_club.Bookings\"\n",
    "bookings_df.write.format(\"parquet\").saveAsTable(permanent_table_name_bookings)\n",
    "\n",
    "permanent_table_name_facilities = \"country_club.Facilities\"\n",
    "facilities_df.write.format(\"parquet\").saveAsTable(permanent_table_name_facilities)\n",
    "\n",
    "permanent_table_name_members = \"country_club.Members\"\n",
    "members_df.write.format(\"parquet\").saveAsTable(permanent_table_name_members)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Refresh tables and check them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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",
       "      <th>tableName</th>\n",
       "      <th>isTemporary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>country_club</td>\n",
       "      <td>bookings</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>country_club</td>\n",
       "      <td>facilities</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>country_club</td>\n",
       "      <td>members</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       database   tableName  isTemporary\n",
       "0  country_club    bookings        False\n",
       "1  country_club  facilities        False\n",
       "2  country_club     members        False"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_sql('use country_club')\n",
    "run_sql('REFRESH table bookings')\n",
    "run_sql('REFRESH table facilities')\n",
    "run_sql('REFRESH table members')\n",
    "tbls = run_sql('show tables')\n",
    "tbls.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test a sample SQL query\n",
    "\n",
    "__Note:__ You can use multi-line SQL queries (but still a single statement) as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bookid</th>\n",
       "      <th>facid</th>\n",
       "      <th>memid</th>\n",
       "      <th>starttime</th>\n",
       "      <th>slots</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2012-07-03 11:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2012-07-03 08:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-07-03 18:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   bookid  facid  memid           starttime  slots\n",
       "0       0      3      1 2012-07-03 11:00:00      2\n",
       "1       1      4      1 2012-07-03 08:00:00      2\n",
       "2       2      6      0 2012-07-03 18:00:00      2"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                    SELECT * \n",
    "                    FROM bookings \n",
    "                    LIMIT 3\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Your Turn: Solve the following questions with Spark SQL\n",
    "\n",
    "- Make use of the `run_sql(...)` function as seen in the previous example\n",
    "- You can write multi-line SQL queries but it has to be a single statement (no use of semi-colons ';')\n",
    "- Make use of the `toPandas()` function as depicted in the previous example to display the query results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q1: Some of the facilities charge a fee to members, but some do not. Please list the names of the facilities that do."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Massage Room 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Massage Room 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Squash Court</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             name\n",
       "0  Tennis Court 1\n",
       "1  Tennis Court 2\n",
       "2  Massage Room 1\n",
       "3  Massage Room 2\n",
       "4    Squash Court"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT name\n",
    "                 FROM facilities \n",
    "                 WHERE membercost > 0.0 \n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Q2: How many facilities do not charge a fee to members?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(1)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(1)\n",
       "0         4"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT COUNT(*)\n",
    "                 FROM facilities \n",
    "                 WHERE membercost = 0.0\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q3: How can you produce a list of facilities that charge a fee to members, where the fee is less than 20% of the facility's monthly maintenance cost? \n",
    "#### Return the facid, facility name, member cost, and monthly maintenance of the facilities in question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>facid</th>\n",
       "      <th>name</th>\n",
       "      <th>membercost</th>\n",
       "      <th>monthlymaintenance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>9.9</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Massage Room 2</td>\n",
       "      <td>9.9</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Squash Court</td>\n",
       "      <td>3.5</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   facid            name  membercost  monthlymaintenance\n",
       "0      0  Tennis Court 1         5.0                 200\n",
       "1      1  Tennis Court 2         5.0                 200\n",
       "2      4  Massage Room 1         9.9                3000\n",
       "3      5  Massage Room 2         9.9                3000\n",
       "4      6    Squash Court         3.5                  80"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                SELECT facid, name,\n",
    "                membercost, monthlymaintenance\n",
    "                FROM facilities\n",
    "                WHERE membercost > 0.0\n",
    "                AND ((membercost * 100)/monthlymaintenance) < 20.0\n",
    "                ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q4: How can you retrieve the details of facilities with ID 1 and 5? Write the query without using the OR operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>facid</th>\n",
       "      <th>name</th>\n",
       "      <th>membercost</th>\n",
       "      <th>guestcost</th>\n",
       "      <th>initialoutlay</th>\n",
       "      <th>monthlymaintenance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>8000</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Massage Room 2</td>\n",
       "      <td>9.9</td>\n",
       "      <td>80.0</td>\n",
       "      <td>4000</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   facid            name  membercost  guestcost  initialoutlay  \\\n",
       "0      1  Tennis Court 2         5.0       25.0           8000   \n",
       "1      5  Massage Room 2         9.9       80.0           4000   \n",
       "\n",
       "   monthlymaintenance  \n",
       "0                 200  \n",
       "1                3000  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                SELECT *\n",
    "                FROM facilities\n",
    "                WHERE facid in (1, 5)\n",
    "                ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q5: How can you produce a list of facilities, with each labelled as 'cheap' or 'expensive', depending on if their monthly maintenance cost is more than $100? \n",
    "#### Return the name and monthly maintenance of the facilities in question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>monthlymaintenance</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>200</td>\n",
       "      <td>expensive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>200</td>\n",
       "      <td>expensive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Badminton Court</td>\n",
       "      <td>50</td>\n",
       "      <td>cheap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Table Tennis</td>\n",
       "      <td>10</td>\n",
       "      <td>cheap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>3000</td>\n",
       "      <td>expensive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Massage Room 2</td>\n",
       "      <td>3000</td>\n",
       "      <td>expensive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Squash Court</td>\n",
       "      <td>80</td>\n",
       "      <td>cheap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Snooker Table</td>\n",
       "      <td>15</td>\n",
       "      <td>cheap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Pool Table</td>\n",
       "      <td>15</td>\n",
       "      <td>cheap</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  monthlymaintenance       type\n",
       "0   Tennis Court 1                 200  expensive\n",
       "1   Tennis Court 2                 200  expensive\n",
       "2  Badminton Court                  50      cheap\n",
       "3     Table Tennis                  10      cheap\n",
       "4   Massage Room 1                3000  expensive\n",
       "5   Massage Room 2                3000  expensive\n",
       "6     Squash Court                  80      cheap\n",
       "7    Snooker Table                  15      cheap\n",
       "8       Pool Table                  15      cheap"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT name, monthlymaintenance,\n",
    "                 IF(monthlymaintenance > 100, 'expensive','cheap') as type\n",
    "                 FROM facilities\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q6: You'd like to get the first and last name of the last member(s) who signed up. Do not use the LIMIT clause for your solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>firstname</th>\n",
       "      <th>surname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Darren</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  firstname surname\n",
       "0    Darren   Smith"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT firstname, surname\n",
    "                 FROM members\n",
    "                 WHERE joindate = (SELECT MAX(joindate) FROM members)\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>memid</th>\n",
       "      <th>surname</th>\n",
       "      <th>firstname</th>\n",
       "      <th>address</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>telephone</th>\n",
       "      <th>recommendedby</th>\n",
       "      <th>joindate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Darren</td>\n",
       "      <td>3 Funktown, Denzington, Boston</td>\n",
       "      <td>66796</td>\n",
       "      <td>(822) 577-3541</td>\n",
       "      <td>None</td>\n",
       "      <td>2012-09-26 18:08:45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   memid surname firstname                         address  zipcode  \\\n",
       "0     37   Smith    Darren  3 Funktown, Denzington, Boston    66796   \n",
       "\n",
       "        telephone recommendedby            joindate  \n",
       "0  (822) 577-3541          None 2012-09-26 18:08:45  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT *\n",
    "                 FROM members\n",
    "                 WHERE joindate = (SELECT MAX(joindate) FROM members)\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Q7: How can you produce a list of all members who have used a tennis court?\n",
    "- Include in your output the name of the court, and the name of the member formatted as a single column. \n",
    "- Ensure no duplicate data\n",
    "- Also order by the member name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CourtName</th>\n",
       "      <th>MemberName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Anne Baker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Anne Baker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Burton Tracy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Burton Tracy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Charles Owen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Charles Owen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Darren Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>David Farrell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>David Farrell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>David Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>David Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>David Pinker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Douglas Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Erica Crumpet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Florence Bader</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Florence Bader</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Gerald Butters</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Gerald Butters</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Henrietta Rumney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Jack Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Jack Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Janice Joplette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Janice Joplette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Jemima Farrell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Jemima Farrell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Joan Coplin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>John Hunt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>John Hunt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Matthew Genting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Millicent Purview</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Nancy Dare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Nancy Dare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Ponder Stibbons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Ponder Stibbons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Ramnaresh Sarwin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Ramnaresh Sarwin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Tim Boothe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Tim Boothe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Tim Rownam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Tim Rownam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Timothy Baker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Timothy Baker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>Tracy Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>Tracy Smith</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         CourtName         MemberName\n",
       "0   Tennis Court 1         Anne Baker\n",
       "1   Tennis Court 2         Anne Baker\n",
       "2   Tennis Court 1       Burton Tracy\n",
       "3   Tennis Court 2       Burton Tracy\n",
       "4   Tennis Court 1       Charles Owen\n",
       "5   Tennis Court 2       Charles Owen\n",
       "6   Tennis Court 2       Darren Smith\n",
       "7   Tennis Court 1      David Farrell\n",
       "8   Tennis Court 2      David Farrell\n",
       "9   Tennis Court 1        David Jones\n",
       "10  Tennis Court 2        David Jones\n",
       "11  Tennis Court 1       David Pinker\n",
       "12  Tennis Court 1      Douglas Jones\n",
       "13  Tennis Court 1      Erica Crumpet\n",
       "14  Tennis Court 1     Florence Bader\n",
       "15  Tennis Court 2     Florence Bader\n",
       "16  Tennis Court 1        GUEST GUEST\n",
       "17  Tennis Court 2        GUEST GUEST\n",
       "18  Tennis Court 1     Gerald Butters\n",
       "19  Tennis Court 2     Gerald Butters\n",
       "20  Tennis Court 2   Henrietta Rumney\n",
       "21  Tennis Court 1         Jack Smith\n",
       "22  Tennis Court 2         Jack Smith\n",
       "23  Tennis Court 1    Janice Joplette\n",
       "24  Tennis Court 2    Janice Joplette\n",
       "25  Tennis Court 1     Jemima Farrell\n",
       "26  Tennis Court 2     Jemima Farrell\n",
       "27  Tennis Court 1        Joan Coplin\n",
       "28  Tennis Court 1          John Hunt\n",
       "29  Tennis Court 2          John Hunt\n",
       "30  Tennis Court 1    Matthew Genting\n",
       "31  Tennis Court 2  Millicent Purview\n",
       "32  Tennis Court 1         Nancy Dare\n",
       "33  Tennis Court 2         Nancy Dare\n",
       "34  Tennis Court 1    Ponder Stibbons\n",
       "35  Tennis Court 2    Ponder Stibbons\n",
       "36  Tennis Court 1   Ramnaresh Sarwin\n",
       "37  Tennis Court 2   Ramnaresh Sarwin\n",
       "38  Tennis Court 1         Tim Boothe\n",
       "39  Tennis Court 2         Tim Boothe\n",
       "40  Tennis Court 1         Tim Rownam\n",
       "41  Tennis Court 2         Tim Rownam\n",
       "42  Tennis Court 1      Timothy Baker\n",
       "43  Tennis Court 2      Timothy Baker\n",
       "44  Tennis Court 1        Tracy Smith\n",
       "45  Tennis Court 2        Tracy Smith"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT DISTINCT f.name as CourtName,\n",
    "                 CONCAT(m.firstname, ' ',  m.surname) as MemberName\n",
    "                 FROM members m\n",
    "                 INNER JOIN bookings b ON m.memid = b.memid \n",
    "                 INNER JOIN facilities f ON b.facid = f.facid\n",
    "                 WHERE f.facid in (0, 1)\n",
    "                 ORDER BY MemberName, CourtName\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q8: How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? \n",
    "\n",
    "- Remember that guests have different costs to members (the listed costs are per half-hour 'slot')\n",
    "- The guest user's ID is always 0. \n",
    "\n",
    "#### Include in your output the name of the facility, the name of the member formatted as a single column, and the cost.\n",
    "\n",
    "- Order by descending cost, and do not use any subqueries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FacilityName</th>\n",
       "      <th>MemberName</th>\n",
       "      <th>cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Massage Room 2</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Squash Court</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>Jemima Farrell</td>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Squash Court</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Squash Court</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      FacilityName      MemberName   cost\n",
       "0   Massage Room 2     GUEST GUEST  320.0\n",
       "1   Massage Room 1     GUEST GUEST  160.0\n",
       "2   Massage Room 1     GUEST GUEST  160.0\n",
       "3   Massage Room 1     GUEST GUEST  160.0\n",
       "4   Tennis Court 2     GUEST GUEST  150.0\n",
       "5   Tennis Court 1     GUEST GUEST   75.0\n",
       "6   Tennis Court 1     GUEST GUEST   75.0\n",
       "7   Tennis Court 2     GUEST GUEST   75.0\n",
       "8     Squash Court     GUEST GUEST   70.0\n",
       "9   Massage Room 1  Jemima Farrell   39.6\n",
       "10    Squash Court     GUEST GUEST   35.0\n",
       "11    Squash Court     GUEST GUEST   35.0"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT f.name AS FacilityName,\n",
    "                 CONCAT(m.firstname, ' ',  m.surname) AS MemberName,\n",
    "                 CASE\n",
    "                     WHEN m.memid = 0 THEN f.guestcost * b.slots\n",
    "                     ELSE f.membercost * b.slots\n",
    "                     END AS cost\n",
    "                 FROM members m\n",
    "                 INNER JOIN bookings b ON m.memid = b.memid \n",
    "                 INNER JOIN facilities f ON b.facid = f.facid\n",
    "                 WHERE cast(b.starttime as date) = '2012-09-14'\n",
    "                 AND\n",
    "                 CASE\n",
    "                     WHEN m.memid = 0 THEN f.guestcost * b.slots\n",
    "                     ELSE f.membercost * b.slots\n",
    "                 END > 30\n",
    "                 ORDER BY cost DESC\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q9: This time, produce the same result as in Q8, but using a subquery."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FacilityName</th>\n",
       "      <th>MemberName</th>\n",
       "      <th>cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Massage Room 2</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tennis Court 2</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Squash Court</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Massage Room 1</td>\n",
       "      <td>Jemima Farrell</td>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Squash Court</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Squash Court</td>\n",
       "      <td>GUEST GUEST</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      FacilityName      MemberName   cost\n",
       "0   Massage Room 2     GUEST GUEST  320.0\n",
       "1   Massage Room 1     GUEST GUEST  160.0\n",
       "2   Massage Room 1     GUEST GUEST  160.0\n",
       "3   Massage Room 1     GUEST GUEST  160.0\n",
       "4   Tennis Court 2     GUEST GUEST  150.0\n",
       "5   Tennis Court 1     GUEST GUEST   75.0\n",
       "6   Tennis Court 1     GUEST GUEST   75.0\n",
       "7   Tennis Court 2     GUEST GUEST   75.0\n",
       "8     Squash Court     GUEST GUEST   70.0\n",
       "9   Massage Room 1  Jemima Farrell   39.6\n",
       "10    Squash Court     GUEST GUEST   35.0\n",
       "11    Squash Court     GUEST GUEST   35.0"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT T.* FROM\n",
    "                    (\n",
    "                        SELECT f.name AS FacilityName,\n",
    "                        CONCAT(m.firstname, ' ',  m.surname) AS MemberName,\n",
    "                        CASE\n",
    "                            WHEN m.memid = 0 THEN f.guestcost * b.slots\n",
    "                            ELSE f.membercost * b.slots\n",
    "                        END AS cost\n",
    "                        FROM members m\n",
    "                        INNER JOIN bookings b ON m.memid = b.memid \n",
    "                        INNER JOIN facilities f ON b.facid = f.facid\n",
    "                        WHERE cast(b.starttime as date) = '2012-09-14'\n",
    "                    ) AS T\n",
    "                  WHERE T.cost > 30\n",
    "                  ORDER BY T.cost DESC\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q10: Produce a list of facilities with a total revenue less than 1000.\n",
    "- The output should have facility name and total revenue, sorted by revenue. \n",
    "- Remember that there's a different cost for guests and members!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FacilityName</th>\n",
       "      <th>TotalRevenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pool Table</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Table Tennis</td>\n",
       "      <td>180.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Snooker Table</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FacilityName  TotalRevenue\n",
       "0     Pool Table         270.0\n",
       "1   Table Tennis         180.0\n",
       "2  Snooker Table         240.0"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                 SELECT * FROM\n",
    "                     (\n",
    "                         SELECT FacilityName, SUM(Cost) AS TotalRevenue\n",
    "                         FROM\n",
    "                             (\n",
    "                                 SELECT f.name AS FacilityName,\n",
    "                                 CASE\n",
    "                                     WHEN m.memid = 0 THEN f.guestcost * b.slots\n",
    "                                     ELSE f.membercost * b.slots\n",
    "                                     END AS Cost\n",
    "                                 FROM members m\n",
    "                                 INNER JOIN bookings b ON m.memid = b.memid \n",
    "                                 INNER JOIN facilities f ON b.facid = f.facid\n",
    "                             )\n",
    "                         GROUP BY FacilityName\n",
    "                      )\n",
    "                      WHERE TotalRevenue < 1000\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.3"
  },
  "name": "Mini_Project_SQL_with_Spark_Solutions",
  "notebookId": 1931807081501703
 },
 "nbformat": 4,
 "nbformat_minor": 1
}