Mini_Project_SQL_with_Spark (1).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": 6,
   "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-5d73adbec9e77c00073ea543: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 0x7faf740306a0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyspark.sql.context.SQLContext at 0x7faf74030828>"
      ]
     },
     "execution_count": 7,
     "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": 8,
   "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": 4,
   "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": 9,
   "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": 9,
     "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": 10,
   "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": 11,
   "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": 12,
   "metadata": {
    "collapsed": true
   },
   "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": 13,
   "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": 13,
     "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": 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>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": 14,
     "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": 19,
   "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>0</td>\n",
       "      <td>Tennis Court 1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>10000</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>25.0</td>\n",
       "      <td>8000</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>80.0</td>\n",
       "      <td>4000</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>80.0</td>\n",
       "      <td>4000</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>17.5</td>\n",
       "      <td>5000</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   facid            name  membercost  guestcost  initialoutlay  \\\n",
       "0      0  Tennis Court 1         5.0       25.0          10000   \n",
       "1      1  Tennis Court 2         5.0       25.0           8000   \n",
       "2      4  Massage Room 1         9.9       80.0           4000   \n",
       "3      5  Massage Room 2         9.9       80.0           4000   \n",
       "4      6    Squash Court         3.5       17.5           5000   \n",
       "\n",
       "   monthlymaintenance  \n",
       "0                 200  \n",
       "1                 200  \n",
       "2                3000  \n",
       "3                3000  \n",
       "4                  80  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                   SELECT * \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": 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>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": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                  SELECT COUNT(*) \n",
    "                    FROM facilities \n",
    "                    WHERE membercost= 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": 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>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": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                  SELECT facid, name, membercost, monthlymaintenance\n",
    "                    FROM facilities \n",
    "                   where membercost > 0 and membercost < (20/100)* monthlymaintenance\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": 29,
   "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": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                    SELECT *\n",
    "                    FROM facilities \n",
    "                    where facid IN (1, 5)\n",
    "                   \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": 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>name</th>\n",
       "      <th>monthlymaintenance</th>\n",
       "      <th>category</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   category\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": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                   SELECT name, monthlymaintenance,\n",
    "                        CASE WHEN facilities.monthlymaintenance <= 100 THEN 'cheap'\n",
    "                            ELSE 'expensive' END AS category\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": 36,
   "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": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                    SELECT firstname,surname \n",
    "                    FROM members where memid = (select max(memid) from members)\n",
    "                    \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": 41,
   "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>member_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anne - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anne - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Burton - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Burton - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Charles - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Charles - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Darren - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>David - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>David - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Douglas - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Erica - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Florence - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Florence - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>GUEST - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GUEST - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Gerald - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Gerald - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Henrietta - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Jack - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Jack - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Janice - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Janice - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Jemima - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Jemima - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Joan - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>John - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>John - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Matthew - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Millicent - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Nancy - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Nancy - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Ponder - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Ponder - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Ramnaresh - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Ramnaresh - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Tim - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Tim - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Timothy - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Timothy - Tennis Court 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Tracy - Tennis Court 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Tracy - Tennis Court 2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   member_name\n",
       "0        Anne - Tennis Court 1\n",
       "1        Anne - Tennis Court 2\n",
       "2      Burton - Tennis Court 1\n",
       "3      Burton - Tennis Court 2\n",
       "4     Charles - Tennis Court 1\n",
       "5     Charles - Tennis Court 2\n",
       "6      Darren - Tennis Court 2\n",
       "7       David - Tennis Court 1\n",
       "8       David - Tennis Court 2\n",
       "9     Douglas - Tennis Court 1\n",
       "10      Erica - Tennis Court 1\n",
       "11   Florence - Tennis Court 1\n",
       "12   Florence - Tennis Court 2\n",
       "13      GUEST - Tennis Court 1\n",
       "14      GUEST - Tennis Court 2\n",
       "15     Gerald - Tennis Court 1\n",
       "16     Gerald - Tennis Court 2\n",
       "17  Henrietta - Tennis Court 2\n",
       "18       Jack - Tennis Court 1\n",
       "19       Jack - Tennis Court 2\n",
       "20     Janice - Tennis Court 1\n",
       "21     Janice - Tennis Court 2\n",
       "22     Jemima - Tennis Court 1\n",
       "23     Jemima - Tennis Court 2\n",
       "24       Joan - Tennis Court 1\n",
       "25       John - Tennis Court 1\n",
       "26       John - Tennis Court 2\n",
       "27    Matthew - Tennis Court 1\n",
       "28  Millicent - Tennis Court 2\n",
       "29      Nancy - Tennis Court 1\n",
       "30      Nancy - Tennis Court 2\n",
       "31     Ponder - Tennis Court 1\n",
       "32     Ponder - Tennis Court 2\n",
       "33  Ramnaresh - Tennis Court 1\n",
       "34  Ramnaresh - Tennis Court 2\n",
       "35        Tim - Tennis Court 1\n",
       "36        Tim - Tennis Court 2\n",
       "37    Timothy - Tennis Court 1\n",
       "38    Timothy - Tennis Court 2\n",
       "39      Tracy - Tennis Court 1\n",
       "40      Tracy - Tennis Court 2"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                   SELECT concat(m.firstname,' - ' ,f.name) as member_name\n",
    "                   from members as m, bookings as b, facilities as f\n",
    "                   where m.memid = b.memid\n",
    "                   and f.facid = b.facid\n",
    "                   and f.name like'%Tennis Court%'                   \n",
    "                   group by  member_name\n",
    "                   order by  member_name\n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>0</td>\n",
       "      <td>GUEST</td>\n",
       "      <td>GUEST</td>\n",
       "      <td>GUEST</td>\n",
       "      <td>0</td>\n",
       "      <td>(000) 000-0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Darren</td>\n",
       "      <td>8 Bloomsbury Close, Boston</td>\n",
       "      <td>4321</td>\n",
       "      <td>555-555-5555</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-07-02 12:02:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Tracy</td>\n",
       "      <td>8 Bloomsbury Close, New York</td>\n",
       "      <td>4321</td>\n",
       "      <td>555-555-5555</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-07-02 12:08:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Rownam</td>\n",
       "      <td>Tim</td>\n",
       "      <td>23 Highway Way, Boston</td>\n",
       "      <td>23423</td>\n",
       "      <td>(844) 693-0723</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-07-03 09:32:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Joplette</td>\n",
       "      <td>Janice</td>\n",
       "      <td>20 Crossing Road, New York</td>\n",
       "      <td>234</td>\n",
       "      <td>(833) 942-4710</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2012-07-03 10:25:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Butters</td>\n",
       "      <td>Gerald</td>\n",
       "      <td>1065 Huntingdon Avenue, Boston</td>\n",
       "      <td>56754</td>\n",
       "      <td>(844) 078-4130</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2012-07-09 10:44:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Tracy</td>\n",
       "      <td>Burton</td>\n",
       "      <td>3 Tunisia Drive, Boston</td>\n",
       "      <td>45678</td>\n",
       "      <td>(822) 354-9973</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-07-15 08:52:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Dare</td>\n",
       "      <td>Nancy</td>\n",
       "      <td>6 Hunting Lodge Way, Boston</td>\n",
       "      <td>10383</td>\n",
       "      <td>(833) 776-4001</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2012-07-25 08:59:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>Boothe</td>\n",
       "      <td>Tim</td>\n",
       "      <td>3 Bloomsbury Close, Reading, 00234</td>\n",
       "      <td>234</td>\n",
       "      <td>(811) 433-2547</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2012-07-25 16:02:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>Stibbons</td>\n",
       "      <td>Ponder</td>\n",
       "      <td>5 Dragons Way, Winchester</td>\n",
       "      <td>87630</td>\n",
       "      <td>(833) 160-3900</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2012-07-25 17:09:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>Owen</td>\n",
       "      <td>Charles</td>\n",
       "      <td>52 Cheshire Grove, Winchester, 28563</td>\n",
       "      <td>28563</td>\n",
       "      <td>(855) 542-5251</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2012-08-03 19:42:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>Jones</td>\n",
       "      <td>David</td>\n",
       "      <td>976 Gnats Close, Reading</td>\n",
       "      <td>33862</td>\n",
       "      <td>(844) 536-8036</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2012-08-06 16:32:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>Baker</td>\n",
       "      <td>Anne</td>\n",
       "      <td>55 Powdery Street, Boston</td>\n",
       "      <td>80743</td>\n",
       "      <td>844-076-5141</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2012-08-10 14:23:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>Farrell</td>\n",
       "      <td>Jemima</td>\n",
       "      <td>103 Firth Avenue, North Reading</td>\n",
       "      <td>57392</td>\n",
       "      <td>(855) 016-0163</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-08-10 14:28:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Jack</td>\n",
       "      <td>252 Binkington Way, Boston</td>\n",
       "      <td>69302</td>\n",
       "      <td>(822) 163-3254</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2012-08-10 16:22:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>Bader</td>\n",
       "      <td>Florence</td>\n",
       "      <td>264 Ursula Drive, Westford</td>\n",
       "      <td>84923</td>\n",
       "      <td>(833) 499-3527</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2012-08-10 17:52:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>Baker</td>\n",
       "      <td>Timothy</td>\n",
       "      <td>329 James Street, Reading</td>\n",
       "      <td>58393</td>\n",
       "      <td>833-941-0824</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2012-08-15 10:34:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>Pinker</td>\n",
       "      <td>David</td>\n",
       "      <td>5 Impreza Road, Boston</td>\n",
       "      <td>65332</td>\n",
       "      <td>811 409-6734</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2012-08-16 11:32:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20</td>\n",
       "      <td>Genting</td>\n",
       "      <td>Matthew</td>\n",
       "      <td>4 Nunnington Place, Wingfield, Boston</td>\n",
       "      <td>52365</td>\n",
       "      <td>(811) 972-1377</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2012-08-19 14:55:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>21</td>\n",
       "      <td>Mackenzie</td>\n",
       "      <td>Anna</td>\n",
       "      <td>64 Perkington Lane, Reading</td>\n",
       "      <td>64577</td>\n",
       "      <td>(822) 661-2898</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2012-08-26 09:32:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>22</td>\n",
       "      <td>Coplin</td>\n",
       "      <td>Joan</td>\n",
       "      <td>85 Bard Street, Bloomington, Boston</td>\n",
       "      <td>43533</td>\n",
       "      <td>(822) 499-2232</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2012-08-29 08:32:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>24</td>\n",
       "      <td>Sarwin</td>\n",
       "      <td>Ramnaresh</td>\n",
       "      <td>12 Bullington Lane, Boston</td>\n",
       "      <td>65464</td>\n",
       "      <td>(822) 413-1470</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2012-09-01 08:44:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>26</td>\n",
       "      <td>Jones</td>\n",
       "      <td>Douglas</td>\n",
       "      <td>976 Gnats Close, Reading</td>\n",
       "      <td>11986</td>\n",
       "      <td>844 536-8036</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2012-09-02 18:43:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>27</td>\n",
       "      <td>Rumney</td>\n",
       "      <td>Henrietta</td>\n",
       "      <td>3 Burkington Plaza, Boston</td>\n",
       "      <td>78533</td>\n",
       "      <td>(822) 989-8876</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2012-09-05 08:42:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>28</td>\n",
       "      <td>Farrell</td>\n",
       "      <td>David</td>\n",
       "      <td>437 Granite Farm Road, Westford</td>\n",
       "      <td>43532</td>\n",
       "      <td>(855) 755-9876</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-09-15 08:22:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>29</td>\n",
       "      <td>Worthington-Smyth</td>\n",
       "      <td>Henry</td>\n",
       "      <td>55 Jagbi Way, North Reading</td>\n",
       "      <td>97676</td>\n",
       "      <td>(855) 894-3758</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2012-09-17 12:27:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>30</td>\n",
       "      <td>Purview</td>\n",
       "      <td>Millicent</td>\n",
       "      <td>641 Drudgery Close, Burnington, Boston</td>\n",
       "      <td>34232</td>\n",
       "      <td>(855) 941-9786</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2012-09-18 19:04:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>33</td>\n",
       "      <td>Tupperware</td>\n",
       "      <td>Hyacinth</td>\n",
       "      <td>33 Cheerful Plaza, Drake Road, Westford</td>\n",
       "      <td>68666</td>\n",
       "      <td>(822) 665-5327</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-09-18 19:32:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>35</td>\n",
       "      <td>Hunt</td>\n",
       "      <td>John</td>\n",
       "      <td>5 Bullington Lane, Boston</td>\n",
       "      <td>54333</td>\n",
       "      <td>(899) 720-6978</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2012-09-19 11:32:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>36</td>\n",
       "      <td>Crumpet</td>\n",
       "      <td>Erica</td>\n",
       "      <td>Crimson Road, North Reading</td>\n",
       "      <td>75655</td>\n",
       "      <td>(811) 732-4816</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2012-09-22 08:36:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</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>NaN</td>\n",
       "      <td>2012-09-26 18:08:45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    memid            surname  firstname  \\\n",
       "0       0              GUEST      GUEST   \n",
       "1       1              Smith     Darren   \n",
       "2       2              Smith      Tracy   \n",
       "3       3             Rownam        Tim   \n",
       "4       4           Joplette     Janice   \n",
       "5       5            Butters     Gerald   \n",
       "6       6              Tracy     Burton   \n",
       "7       7               Dare      Nancy   \n",
       "8       8             Boothe        Tim   \n",
       "9       9           Stibbons     Ponder   \n",
       "10     10               Owen    Charles   \n",
       "11     11              Jones      David   \n",
       "12     12              Baker       Anne   \n",
       "13     13            Farrell     Jemima   \n",
       "14     14              Smith       Jack   \n",
       "15     15              Bader   Florence   \n",
       "16     16              Baker    Timothy   \n",
       "17     17             Pinker      David   \n",
       "18     20            Genting    Matthew   \n",
       "19     21          Mackenzie       Anna   \n",
       "20     22             Coplin       Joan   \n",
       "21     24             Sarwin  Ramnaresh   \n",
       "22     26              Jones    Douglas   \n",
       "23     27             Rumney  Henrietta   \n",
       "24     28            Farrell      David   \n",
       "25     29  Worthington-Smyth      Henry   \n",
       "26     30            Purview  Millicent   \n",
       "27     33         Tupperware   Hyacinth   \n",
       "28     35               Hunt       John   \n",
       "29     36            Crumpet      Erica   \n",
       "30     37              Smith     Darren   \n",
       "\n",
       "                                    address  zipcode       telephone  \\\n",
       "0                                     GUEST        0  (000) 000-0000   \n",
       "1                8 Bloomsbury Close, Boston     4321    555-555-5555   \n",
       "2              8 Bloomsbury Close, New York     4321    555-555-5555   \n",
       "3                    23 Highway Way, Boston    23423  (844) 693-0723   \n",
       "4                20 Crossing Road, New York      234  (833) 942-4710   \n",
       "5            1065 Huntingdon Avenue, Boston    56754  (844) 078-4130   \n",
       "6                   3 Tunisia Drive, Boston    45678  (822) 354-9973   \n",
       "7               6 Hunting Lodge Way, Boston    10383  (833) 776-4001   \n",
       "8        3 Bloomsbury Close, Reading, 00234      234  (811) 433-2547   \n",
       "9                 5 Dragons Way, Winchester    87630  (833) 160-3900   \n",
       "10     52 Cheshire Grove, Winchester, 28563    28563  (855) 542-5251   \n",
       "11                 976 Gnats Close, Reading    33862  (844) 536-8036   \n",
       "12                55 Powdery Street, Boston    80743    844-076-5141   \n",
       "13          103 Firth Avenue, North Reading    57392  (855) 016-0163   \n",
       "14               252 Binkington Way, Boston    69302  (822) 163-3254   \n",
       "15               264 Ursula Drive, Westford    84923  (833) 499-3527   \n",
       "16                329 James Street, Reading    58393    833-941-0824   \n",
       "17                   5 Impreza Road, Boston    65332    811 409-6734   \n",
       "18    4 Nunnington Place, Wingfield, Boston    52365  (811) 972-1377   \n",
       "19              64 Perkington Lane, Reading    64577  (822) 661-2898   \n",
       "20      85 Bard Street, Bloomington, Boston    43533  (822) 499-2232   \n",
       "21               12 Bullington Lane, Boston    65464  (822) 413-1470   \n",
       "22                 976 Gnats Close, Reading    11986    844 536-8036   \n",
       "23               3 Burkington Plaza, Boston    78533  (822) 989-8876   \n",
       "24          437 Granite Farm Road, Westford    43532  (855) 755-9876   \n",
       "25              55 Jagbi Way, North Reading    97676  (855) 894-3758   \n",
       "26   641 Drudgery Close, Burnington, Boston    34232  (855) 941-9786   \n",
       "27  33 Cheerful Plaza, Drake Road, Westford    68666  (822) 665-5327   \n",
       "28                5 Bullington Lane, Boston    54333  (899) 720-6978   \n",
       "29              Crimson Road, North Reading    75655  (811) 732-4816   \n",
       "30           3 Funktown, Denzington, Boston    66796  (822) 577-3541   \n",
       "\n",
       "    recommendedby            joindate  \n",
       "0             NaN 2012-07-01 00:00:00  \n",
       "1             NaN 2012-07-02 12:02:05  \n",
       "2             NaN 2012-07-02 12:08:23  \n",
       "3             NaN 2012-07-03 09:32:15  \n",
       "4             1.0 2012-07-03 10:25:05  \n",
       "5             1.0 2012-07-09 10:44:09  \n",
       "6             NaN 2012-07-15 08:52:55  \n",
       "7             4.0 2012-07-25 08:59:12  \n",
       "8             3.0 2012-07-25 16:02:35  \n",
       "9             6.0 2012-07-25 17:09:05  \n",
       "10            1.0 2012-08-03 19:42:37  \n",
       "11            4.0 2012-08-06 16:32:55  \n",
       "12            9.0 2012-08-10 14:23:22  \n",
       "13            NaN 2012-08-10 14:28:01  \n",
       "14            1.0 2012-08-10 16:22:05  \n",
       "15            9.0 2012-08-10 17:52:03  \n",
       "16           13.0 2012-08-15 10:34:25  \n",
       "17           13.0 2012-08-16 11:32:47  \n",
       "18            5.0 2012-08-19 14:55:55  \n",
       "19            1.0 2012-08-26 09:32:05  \n",
       "20           16.0 2012-08-29 08:32:41  \n",
       "21           15.0 2012-09-01 08:44:42  \n",
       "22           11.0 2012-09-02 18:43:05  \n",
       "23           20.0 2012-09-05 08:42:35  \n",
       "24            NaN 2012-09-15 08:22:05  \n",
       "25            2.0 2012-09-17 12:27:15  \n",
       "26            2.0 2012-09-18 19:04:01  \n",
       "27            NaN 2012-09-18 19:32:05  \n",
       "28           30.0 2012-09-19 11:32:45  \n",
       "29            2.0 2012-09-22 08:36:38  \n",
       "30            NaN 2012-09-26 18:08:45  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                   SELECT * from members\n",
    "                   \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": 58,
   "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>member_name</th>\n",
       "      <th>cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Massage Room 2 - GUEST</td>\n",
       "      <td>320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Massage Room 1 - GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tennis Court 2 - GUEST</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tennis Court 1 - GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tennis Court 2 - GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Squash Court - GUEST</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Massage Room 1 - Jemima</td>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Squash Court - GUEST</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               member_name   cost\n",
       "0   Massage Room 2 - GUEST  320.0\n",
       "1   Massage Room 1 - GUEST  160.0\n",
       "2   Tennis Court 2 - GUEST  150.0\n",
       "3   Tennis Court 1 - GUEST   75.0\n",
       "4   Tennis Court 2 - GUEST   75.0\n",
       "5     Squash Court - GUEST   70.0\n",
       "6  Massage Room 1 - Jemima   39.6\n",
       "7     Squash Court - GUEST   35.0"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                   select concat(f.name,' - ' ,m.firstname) as member_name, \n",
    "                       CASE WHEN b.memid = 0  \n",
    "                            THEN guestcost * slots\n",
    "                            ELSE membercost * slots\n",
    "                            END AS cost\n",
    "\n",
    "                   from members as m, bookings as b, facilities as f\n",
    "                   where m.memid = b.memid and \n",
    "                         f.facid = b.facid and                   \n",
    "                         date(b.starttime) = '2012-09-14'\n",
    "                   \n",
    "                   group by cost, member_name\n",
    "                   having cost > 30\n",
    "                   order by cost desc \n",
    "                   \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": 61,
   "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>member_name</th>\n",
       "      <th>cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Massage Room 2 - GUEST</td>\n",
       "      <td>320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Massage Room 1 - GUEST</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tennis Court 2 - GUEST</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tennis Court 1 - GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tennis Court 2 - GUEST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Squash Court - GUEST</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Massage Room 1 - Jemima</td>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Squash Court - GUEST</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               member_name   cost\n",
       "0   Massage Room 2 - GUEST  320.0\n",
       "1   Massage Room 1 - GUEST  160.0\n",
       "2   Tennis Court 2 - GUEST  150.0\n",
       "3   Tennis Court 1 - GUEST   75.0\n",
       "4   Tennis Court 2 - GUEST   75.0\n",
       "5     Squash Court - GUEST   70.0\n",
       "6  Massage Room 1 - Jemima   39.6\n",
       "7     Squash Court - GUEST   35.0"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                    select member_name, cost\n",
    "                    from \n",
    "                    (select\n",
    "                            concat(f.name,' - ' ,m.firstname) as member_name, \n",
    "                            CASE WHEN b.memid = 0  \n",
    "                            THEN guestcost * slots\n",
    "                            ELSE membercost * slots\n",
    "                            END AS cost\n",
    "\n",
    "                            from members as m, bookings as b, facilities as f\n",
    "                            where m.memid = b.memid and \n",
    "                            f.facid = b.facid and                   \n",
    "                            date(b.starttime) = '2012-09-14'\n",
    "                   \n",
    "                            \n",
    "                            group by cost, member_name\n",
    "                            having cost > 30\n",
    "                    )                 \n",
    "                    order by cost desc                \n",
    "                     \n",
    "                    \n",
    "                    \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": 62,
   "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>revenue</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>Snooker Table</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Table Tennis</td>\n",
       "      <td>180.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name  revenue\n",
       "0     Pool Table    270.0\n",
       "1  Snooker Table    240.0\n",
       "2   Table Tennis    180.0"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = run_sql('''\n",
    "                    SELECT f.name,(((guestcost * guestslot) + (membercost * memberslot))) as revenue                                               \n",
    "                            \n",
    "                    FROM facilities as f,\n",
    "                     (select facid, sum(guest) as guestslot,sum(member) as memberslot\n",
    "                     from(\n",
    "                     SELECT facid,\n",
    "                        (CASE WHEN memid = 0  \n",
    "                         THEN sum(slots) \n",
    "                         END) as guest,\n",
    "                        (CASE WHEN memid > 0  \n",
    "                         THEN sum(slots)\n",
    "                         END) as member\n",
    "                     FROM bookings\n",
    "                     group by facid,memid)\n",
    "                     group by facid) as b\n",
    "                    WHERE f.facid = b.facid\n",
    "                    group by name,guestcost, membercost, guestslot, memberslot,f.facid,initialoutlay,monthlymaintenance\n",
    "                    having revenue < 1000 \n",
    "                    order by name\n",
    "                    \n",
    "                    \n",
    "                 ''')\n",
    "result.toPandas()"
   ]
  }
 ],
 "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
}