country_club.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 52,
      "metadata": {
        "id": "V34ETfcGTBKE"
      },
      "outputs": [],
      "source": [
        "from sqlalchemy import create_engine\n",
        "import pandas as pd\n",
        "import datetime\n",
        "engine = create_engine('sqlite:///sqlite_db_pythonsqlite.db')\n",
        "con = engine.connect()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import files\n",
        "upload=files.upload()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "id": "y_hTXCkMV1aa",
        "outputId": "fc1a08e2-6f3c-42af-8bdf-4f36f5a245d9"
      },
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "     <input type=\"file\" id=\"files-b92fb443-851a-4822-9904-b1317e8aba10\" name=\"files[]\" multiple disabled\n",
              "        style=\"border:none\" />\n",
              "     <output id=\"result-b92fb443-851a-4822-9904-b1317e8aba10\">\n",
              "      Upload widget is only available when the cell has been executed in the\n",
              "      current browser session. Please rerun this cell to enable.\n",
              "      </output>\n",
              "      <script>// Copyright 2017 Google LLC\n",
              "//\n",
              "// Licensed under the Apache License, Version 2.0 (the \"License\");\n",
              "// you may not use this file except in compliance with the License.\n",
              "// You may obtain a copy of the License at\n",
              "//\n",
              "//      http://www.apache.org/licenses/LICENSE-2.0\n",
              "//\n",
              "// Unless required by applicable law or agreed to in writing, software\n",
              "// distributed under the License is distributed on an \"AS IS\" BASIS,\n",
              "// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
              "// See the License for the specific language governing permissions and\n",
              "// limitations under the License.\n",
              "\n",
              "/**\n",
              " * @fileoverview Helpers for google.colab Python module.\n",
              " */\n",
              "(function(scope) {\n",
              "function span(text, styleAttributes = {}) {\n",
              "  const element = document.createElement('span');\n",
              "  element.textContent = text;\n",
              "  for (const key of Object.keys(styleAttributes)) {\n",
              "    element.style[key] = styleAttributes[key];\n",
              "  }\n",
              "  return element;\n",
              "}\n",
              "\n",
              "// Max number of bytes which will be uploaded at a time.\n",
              "const MAX_PAYLOAD_SIZE = 100 * 1024;\n",
              "\n",
              "function _uploadFiles(inputId, outputId) {\n",
              "  const steps = uploadFilesStep(inputId, outputId);\n",
              "  const outputElement = document.getElementById(outputId);\n",
              "  // Cache steps on the outputElement to make it available for the next call\n",
              "  // to uploadFilesContinue from Python.\n",
              "  outputElement.steps = steps;\n",
              "\n",
              "  return _uploadFilesContinue(outputId);\n",
              "}\n",
              "\n",
              "// This is roughly an async generator (not supported in the browser yet),\n",
              "// where there are multiple asynchronous steps and the Python side is going\n",
              "// to poll for completion of each step.\n",
              "// This uses a Promise to block the python side on completion of each step,\n",
              "// then passes the result of the previous step as the input to the next step.\n",
              "function _uploadFilesContinue(outputId) {\n",
              "  const outputElement = document.getElementById(outputId);\n",
              "  const steps = outputElement.steps;\n",
              "\n",
              "  const next = steps.next(outputElement.lastPromiseValue);\n",
              "  return Promise.resolve(next.value.promise).then((value) => {\n",
              "    // Cache the last promise value to make it available to the next\n",
              "    // step of the generator.\n",
              "    outputElement.lastPromiseValue = value;\n",
              "    return next.value.response;\n",
              "  });\n",
              "}\n",
              "\n",
              "/**\n",
              " * Generator function which is called between each async step of the upload\n",
              " * process.\n",
              " * @param {string} inputId Element ID of the input file picker element.\n",
              " * @param {string} outputId Element ID of the output display.\n",
              " * @return {!Iterable<!Object>} Iterable of next steps.\n",
              " */\n",
              "function* uploadFilesStep(inputId, outputId) {\n",
              "  const inputElement = document.getElementById(inputId);\n",
              "  inputElement.disabled = false;\n",
              "\n",
              "  const outputElement = document.getElementById(outputId);\n",
              "  outputElement.innerHTML = '';\n",
              "\n",
              "  const pickedPromise = new Promise((resolve) => {\n",
              "    inputElement.addEventListener('change', (e) => {\n",
              "      resolve(e.target.files);\n",
              "    });\n",
              "  });\n",
              "\n",
              "  const cancel = document.createElement('button');\n",
              "  inputElement.parentElement.appendChild(cancel);\n",
              "  cancel.textContent = 'Cancel upload';\n",
              "  const cancelPromise = new Promise((resolve) => {\n",
              "    cancel.onclick = () => {\n",
              "      resolve(null);\n",
              "    };\n",
              "  });\n",
              "\n",
              "  // Wait for the user to pick the files.\n",
              "  const files = yield {\n",
              "    promise: Promise.race([pickedPromise, cancelPromise]),\n",
              "    response: {\n",
              "      action: 'starting',\n",
              "    }\n",
              "  };\n",
              "\n",
              "  cancel.remove();\n",
              "\n",
              "  // Disable the input element since further picks are not allowed.\n",
              "  inputElement.disabled = true;\n",
              "\n",
              "  if (!files) {\n",
              "    return {\n",
              "      response: {\n",
              "        action: 'complete',\n",
              "      }\n",
              "    };\n",
              "  }\n",
              "\n",
              "  for (const file of files) {\n",
              "    const li = document.createElement('li');\n",
              "    li.append(span(file.name, {fontWeight: 'bold'}));\n",
              "    li.append(span(\n",
              "        `(${file.type || 'n/a'}) - ${file.size} bytes, ` +\n",
              "        `last modified: ${\n",
              "            file.lastModifiedDate ? file.lastModifiedDate.toLocaleDateString() :\n",
              "                                    'n/a'} - `));\n",
              "    const percent = span('0% done');\n",
              "    li.appendChild(percent);\n",
              "\n",
              "    outputElement.appendChild(li);\n",
              "\n",
              "    const fileDataPromise = new Promise((resolve) => {\n",
              "      const reader = new FileReader();\n",
              "      reader.onload = (e) => {\n",
              "        resolve(e.target.result);\n",
              "      };\n",
              "      reader.readAsArrayBuffer(file);\n",
              "    });\n",
              "    // Wait for the data to be ready.\n",
              "    let fileData = yield {\n",
              "      promise: fileDataPromise,\n",
              "      response: {\n",
              "        action: 'continue',\n",
              "      }\n",
              "    };\n",
              "\n",
              "    // Use a chunked sending to avoid message size limits. See b/62115660.\n",
              "    let position = 0;\n",
              "    do {\n",
              "      const length = Math.min(fileData.byteLength - position, MAX_PAYLOAD_SIZE);\n",
              "      const chunk = new Uint8Array(fileData, position, length);\n",
              "      position += length;\n",
              "\n",
              "      const base64 = btoa(String.fromCharCode.apply(null, chunk));\n",
              "      yield {\n",
              "        response: {\n",
              "          action: 'append',\n",
              "          file: file.name,\n",
              "          data: base64,\n",
              "        },\n",
              "      };\n",
              "\n",
              "      let percentDone = fileData.byteLength === 0 ?\n",
              "          100 :\n",
              "          Math.round((position / fileData.byteLength) * 100);\n",
              "      percent.textContent = `${percentDone}% done`;\n",
              "\n",
              "    } while (position < fileData.byteLength);\n",
              "  }\n",
              "\n",
              "  // All done.\n",
              "  yield {\n",
              "    response: {\n",
              "      action: 'complete',\n",
              "    }\n",
              "  };\n",
              "}\n",
              "\n",
              "scope.google = scope.google || {};\n",
              "scope.google.colab = scope.google.colab || {};\n",
              "scope.google.colab._files = {\n",
              "  _uploadFiles,\n",
              "  _uploadFilesContinue,\n",
              "};\n",
              "})(self);\n",
              "</script> "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Saving Bookings.csv to Bookings.csv\n",
            "Saving Facilities.csv to Facilities.csv\n",
            "Saving Members.csv to Members.csv\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from sqlalchemy import create_engine\n",
        "import pandas as pd\n",
        "import datetime\n",
        "engine = create_engine('sqlite:///sqlite_db_pythonsqlite.db')\n",
        "#engine = create_engine('sqlite:///sql.db')\n",
        "con = engine.connect()\n",
        "\n",
        "#members.head\n",
        "#/content/sqlite_db_pythonsqlite.db\n",
        "#/content/Members.csv\n",
        "#rs1= con.execute('select * from Members')\n",
        "#rs1 = con.execute(\"SELECT * FROM Members\")\n",
        "#rs2 = con.execute(\"SELECT * FROM Bookings;\")\n",
        "#rs3 = con.execute(\"SELECT * FROM Facilities;\")\n",
        "#members = pd.DataFrame(rs1.fetchall())\n",
        "#members.columns = rs1.keys()\n",
        "#bookings = pd.DataFrame(rs2.fetchall())\n",
        "#bookings.columns = rs2.keys()\n",
        "#facilities = pd.DataFrame(rs3.fetchall())\n",
        "#facilities.columns = rs3.keys()\n",
        "#members.head()\n",
        "\n",
        "#/content/sqlite_db_pythonsqlite.db"
      ],
      "metadata": {
        "id": "9ROuKZiFTVcj"
      },
      "execution_count": 50,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mem_file_read=pd.read_csv('/content/Members.csv')\n",
        "mem_file_read.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "M-ZJG-rSXcX2",
        "outputId": "59658de1-500a-4737-ea03-5a5e28e0394e"
      },
      "execution_count": 72,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   memid   surname firstname                       address  zipcode  \\\n",
              "0      0     GUEST     GUEST                         GUEST        0   \n",
              "1      1     Smith    Darren    8 Bloomsbury Close, Boston     4321   \n",
              "2      2     Smith     Tracy  8 Bloomsbury Close, New York     4321   \n",
              "3      3    Rownam       Tim        23 Highway Way, Boston    23423   \n",
              "4      4  Joplette    Janice    20 Crossing Road, New York      234   \n",
              "\n",
              "        telephone  recommendedby             joindate  \n",
              "0  (000) 000-0000            NaN  2012-07-01 00:00:00  \n",
              "1    555-555-5555            NaN  2012-07-02 12:02:05  \n",
              "2    555-555-5555            NaN  2012-07-02 12:08:23  \n",
              "3  (844) 693-0723            NaN  2012-07-03 09:32:15  \n",
              "4  (833) 942-4710            1.0  2012-07-03 10:25:05  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-11b38026-b756-49eb-bfca-4d7474bd52cd\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>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",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-11b38026-b756-49eb-bfca-4d7474bd52cd')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-11b38026-b756-49eb-bfca-4d7474bd52cd button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-11b38026-b756-49eb-bfca-4d7474bd52cd');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 72
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "table_names = engine.table_names()\n",
        "print(table_names)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zE0edxMlkTys",
        "outputId": "94212749-c22b-417a-a0c6-bf9ddf222881"
      },
      "execution_count": 67,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[]\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:1: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release.  Please refer to Inspector.get_table_names(). (deprecated since: 1.4)\n",
            "  \"\"\"Entry point for launching an IPython kernel.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df=pd.read_sql_query('Select * From Bookings',con)\n",
        "df"
      ],
      "metadata": {
        "id": "UzknLvt9TD1w"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "''' Q10: Produce a list of facilities with a total revenue less than 1000.\n",
        "The output of facility name and total revenue, sorted by revenue. Remember\n",
        "that there's a different cost for guests and members! '''\n",
        "\n",
        "import pandas as pd\n",
        "import sqlite3 as sql\n",
        "countryclub_db = 'sqlite_db_pythonsqlite.db'\n",
        "connection = sql.connect(countryclub_db)"
      ],
      "metadata": {
        "id": "hxyJVfr-TNHW"
      },
      "execution_count": 74,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#query11 = 'SELECT facility, SUM(total_cost) AS revenue FROM (SELECT firstname, surname, Facilities.name AS facility, CASE WHEN memid = 0 THEN \"guest\"ELSE \"member\" END AS person_type, CASE WHEN memid = 0 THEN guestcost*slots ELSE membercost*slots END AS total_cost FROM Bookings JOIN Members USING(memid) JOIN Facilities USING(facid)) AS total_cost_per_booking GROUP BY facility HAVING SUM(total_cost) < 1000 ORDER BY SUM(total_cost) DESC'\n",
        "query = 'SELECT name, total_revenue FROM (SELECT name, SUM(CASE WHEN Bookings.memid = 0 THEN Facilities.guestcost * slots ELSE Facilities.membercost * slots END) as total_revenue FROM Bookings\tJOIN Members ON Bookings.memid = Members.memid JOIN Facilities ON Bookings.facid = Facilities.facid GROUP BY name) as Facilities_Revenue WHERE total_revenue < 1000 ORDER BY total_revenue DESC'\n",
        "\n",
        "'''\n",
        "#query = \n",
        "SELECT name, total_revenue\n",
        "FROM (\n",
        "\tSELECT name, \n",
        "\tSUM(\n",
        "\t\tCASE WHEN Bookings.memid = 0\n",
        "\t\tTHEN Facilities.guestcost * slots\n",
        "\t\tELSE Facilities.membercost * slots\n",
        "\t\tEND\n",
        "\t) as total_revenue\n",
        "\tFROM Bookings\n",
        "\tJOIN Members\n",
        "\t\tON Bookings.memid = Members.memid\n",
        "\tJOIN Facilities\n",
        "\t\tON Bookings.facid = Facilities.facid\n",
        "\tGROUP BY name\n",
        ") as Facilities_Revenue\n",
        "WHERE total_revenue < 1000\n",
        "ORDER BY total_revenue DESC;\n",
        "\n",
        "'''"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 88
        },
        "id": "A90O5tJlw-KS",
        "outputId": "6dfd7a63-7de3-4fbe-9032-fd36873382c4"
      },
      "execution_count": 87,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'\\n#query = \\nSELECT name, total_revenue\\nFROM (\\n\\tSELECT name, \\n\\tSUM(\\n\\t\\tCASE WHEN Bookings.memid = 0\\n\\t\\tTHEN Facilities.guestcost * slots\\n\\t\\tELSE Facilities.membercost * slots\\n\\t\\tEND\\n\\t) as total_revenue\\n\\tFROM Bookings\\n\\tJOIN Members\\n\\t\\tON Bookings.memid = Members.memid\\n\\tJOIN Facilities\\n\\t\\tON Bookings.facid = Facilities.facid\\n\\tGROUP BY name\\n) as Facilities_Revenue\\nWHERE total_revenue < 1000\\nORDER BY total_revenue DESC;\\n\\n'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 87
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df = pd.read_sql_query(query, con)\n",
        "df"
      ],
      "metadata": {
        "id": "dQsxbCfcxVze"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "''' Q11: Produce a report of members and who recommended them in alphabetic surname,firstname order '''\n",
        "\n",
        "query_q11 = 'SELECT firstname, surname, recommendedby FROM Members WHERE recommendedby != 0 ORDER BY surname, firstname'\n",
        "\n",
        "'''\n",
        "#query = \n",
        "SELECT firstname, surname, recommendedby\n",
        "FROM Members\n",
        "WHERE recommendedby != 0\n",
        "ORDER BY surname, firstname;\n",
        "'''"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "LpxqXLd_xYxE",
        "outputId": "c5ae91c4-27f0-4008-f41c-0f970ce595dd"
      },
      "execution_count": 89,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'\\nquery = \\nSELECT firstname, surname, recommendedby\\nFROM Members\\nWHERE recommendedby != 0\\nORDER BY surname, firstname;\\n'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 89
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df = ps.read_sql_query(query_q11,con)\n",
        "df"
      ],
      "metadata": {
        "id": "Wm24afJVzHQx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "''' Q12: Find the facilities with their usage by member, but not guests '''\n",
        "\n",
        "query_q12 = 'SELECT firstname, surname, name AS facility FROM Bookings AS b INNER JOIN Facilities as f USING(facid) INNER JOIN Members AS m USING(memid) WHERE firstname NOT LIKE \"GUEST\" AND surname NOT LIKE \"GUEST\" ORDER BY surname, firstname'\n",
        "\n",
        "'''\n",
        "#query = \n",
        "/* Q12: Find the facilities with their usage by member, but not guests */\n",
        "SELECT firstname, surname, name AS facility\n",
        "FROM Bookings AS b\n",
        "INNER JOIN Facilities as f\n",
        "USING(facid)\n",
        "INNER JOIN Members AS m\n",
        "USING(memid)\n",
        "WHERE firstname NOT LIKE 'GUEST' AND surname NOT LIKE 'GUEST'\n",
        "ORDER BY surname, firstname;\n",
        "'''"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 70
        },
        "id": "AuRlX5wWzf1S",
        "outputId": "cf147d05-5f4c-4126-c211-a8ec743f7887"
      },
      "execution_count": 90,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "\"\\nquery = \\n/* Q12: Find the facilities with their usage by member, but not guests */\\nSELECT firstname, surname, name AS facility\\nFROM Bookings AS b\\nINNER JOIN Facilities as f\\nUSING(facid)\\nINNER JOIN Members AS m\\nUSING(memid)\\nWHERE firstname NOT LIKE 'GUEST' AND surname NOT LIKE 'GUEST'\\nORDER BY surname, firstname;\\n\""
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 90
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df = pd.read_sql_query(query_q12,con)\n",
        "df"
      ],
      "metadata": {
        "id": "Jj6JGnk3z_IZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "''' Q13: Find the facilities usage by month, but not guests '''\n",
        "\n",
        "query_q13 = 'SELECT name AS facility, MONTH(starttime) AS Month FROM Bookings AS b INNER JOIN Facilities as f USING(facid) INNER JOIN Members AS m USING(memid) WHERE firstname NOT LIKE \"GUEST\" AND surname NOT LIKE \"GUEST\"'\n",
        "\n",
        "\n",
        "'''\n",
        "query = \n",
        "SELECT name AS facility, MONTH(starttime) AS Month\n",
        "FROM Bookings AS b\n",
        "INNER JOIN Facilities as f\n",
        "USING(facid)\n",
        "INNER JOIN Members AS m\n",
        "USING(memid)\n",
        "WHERE firstname NOT LIKE 'GUEST' AND surname NOT LIKE 'GUEST';\n",
        "'''"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 53
        },
        "id": "utS1hhQ60Ouj",
        "outputId": "5400bd9b-7b93-4103-fb2b-0d8da036f93d"
      },
      "execution_count": 91,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "\"\\nquery = \\nSELECT name AS facility, MONTH(starttime) AS Month\\nFROM Bookings AS b\\nINNER JOIN Facilities as f\\nUSING(facid)\\nINNER JOIN Members AS m\\nUSING(memid)\\nWHERE firstname NOT LIKE 'GUEST' AND surname NOT LIKE 'GUEST';\\n\""
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 91
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df=pd.read_sql_query(query_q13,con)\n",
        "df"
      ],
      "metadata": {
        "id": "DhK_Gwmg0MmZ"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}