{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d18c0fa9-7b5e-4a07-b851-e8bb3396a25d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79ffe145-2d60-4a7d-8a0f-874526175c1f",
   "metadata": {},
   "source": [
    "# ZAGI Sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0ea4227-43f3-44ec-8365-5618eea7eca3",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection('ZAGI-Sales.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "216952f5-0893-4f93-87b8-34b98c8f5e3e",
   "metadata": {},
   "source": [
    "### Which products sell below the average price?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b2b844a-b9ec-41b5-b382-d45771e6e70f",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT AVG(product_price)\n",
    "    FROM   product\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f92e21f-52e7-40fa-aa9d-35c3d907a84f",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT product_id, product_name, product_price\n",
    "    FROM   product\n",
    "    WHERE  product_price < (\n",
    "        SELECT AVG(product_price)\n",
    "        FROM   product\n",
    "    )\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "698cc138-930c-4563-9cb9-99ab8fa275c1",
   "metadata": {},
   "source": [
    "### Which products have more than 3 items sold in all sales transactions?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebe953a1-4e34-413f-a622-4c43f52088da",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT product_id\n",
    "    FROM sold_via\n",
    "    GROUP BY product_id\n",
    "    HAVING SUM(no_of_items) > 3\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20900a8d-7115-4ef2-84c6-825fa0aead2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT product_id, product_name, product_price\n",
    "    FROM product\n",
    "    WHERE product_id IN (\n",
    "        SELECT product_id\n",
    "        FROM sold_via\n",
    "        GROUP BY product_id\n",
    "        HAVING SUM(no_of_items) > 3\n",
    "    );\n",
    "\"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e088f742-21e3-44cb-9bd3-7cac7080cb12",
   "metadata": {},
   "source": [
    "### Which products were each sold in more than one sales transaction?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "788c8ab6-71b8-44bf-9bdb-4639c1baa7fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT product_id\n",
    "    FROM sold_via\n",
    "    GROUP BY product_id\n",
    "    HAVING COUNT(*) > 1\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c0b0c15-4da4-49ba-a532-7da5c5f345b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT product_id, product_name, product_price\n",
    "    FROM product\n",
    "    WHERE product_id IN (\n",
    "         SELECT product_id\n",
    "         FROM sold_via\n",
    "         GROUP BY product_id\n",
    "         HAVING COUNT(*) > 1\n",
    "    );\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e0b3032-a8ba-451a-b91d-af659376ef35",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "389ff930-34b1-43ce-b85c-7ca7a65054a9",
   "metadata": {},
   "source": [
    "# Building"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16f34dc0-6987-481d-9d56-2590bf5dfe06",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection('building.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb6153a1-e898-4aa0-a23b-4830e361013a",
   "metadata": {},
   "source": [
    "### Which buildings have managers living in them?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1013cabb-ec86-4781-9494-120b122ab0d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT *\n",
    "    FROM building b, manager m\n",
    "    WHERE b.buildingid = m.mresbuildingid\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a752dc4e-2b22-4b4b-a0ec-dcd9ade856be",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT *\n",
    "    FROM building b\n",
    "    WHERE EXISTS (\n",
    "        SELECT *\n",
    "        FROM manager m\n",
    "        WHERE b.buildingid = m.mresbuildingid\n",
    "    )\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dce3d4c1-6302-43b1-ab9c-0785ec03f7f7",
   "metadata": {},
   "source": [
    "### Which buildings do **not** have managers living in them?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f960f362-5cc2-455d-aa1a-d1d22d94079e",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT *\n",
    "    FROM building b\n",
    "    WHERE NOT EXISTS (\n",
    "        SELECT *\n",
    "        FROM manager m\n",
    "        WHERE b.buildingid = m.mresbuildingid\n",
    "    )\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3351809f-08c8-4939-a7f4-da2f531d975f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bed4b1ff-4339-4f5a-89c3-bd8f901ac17b",
   "metadata": {},
   "source": [
    "# School"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7be46d7-2092-49fb-9d82-353acf427a70",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection('school.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2da29fe-a118-4e38-91b4-22e413792f2b",
   "metadata": {},
   "source": [
    "### Who are the classmates of Tim Nova?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b2ae884-b277-416b-943c-f8167df67d39",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Which classes does Tim Nova take?\n",
    "\n",
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT code, subject \n",
    "    FROM student, class, takes \n",
    "    WHERE last = 'Nova' \n",
    "    AND first = 'Tim' \n",
    "    AND id = student_id \n",
    "    AND code = class_code    \n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c256dbb-70c0-4246-b6dc-613a7e0d4057",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only select the class codes of Tim Nova?\n",
    "\n",
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT code \n",
    "    FROM student, class, takes \n",
    "    WHERE last = 'Nova' \n",
    "    AND first = 'Tim' \n",
    "    AND id = student_id \n",
    "    AND code = class_code    \n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "350fffb5-8b18-4c8b-aa26-5b960db18c43",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Which classes do the other students take?\n",
    "\n",
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT id, first, last, code\n",
    "    FROM student, class, takes\n",
    "    WHERE id = student_id \n",
    "    AND code = class_code \n",
    "    AND last != 'Nova' \n",
    "    AND first != 'Tim'   \n",
    "    ORDER BY last\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60bdcaf4-87f0-466f-8bd6-a88684e782b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only include the other student's classes\n",
    "# that are in the list of Tim Nova's classes.\n",
    "\n",
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT id, first, last, code\n",
    "    FROM student, class, takes\n",
    "    WHERE id = student_id \n",
    "    AND code = class_code \n",
    "    AND last != 'Nova' \n",
    "    AND first != 'Tim' \n",
    "    AND class_code IN (\n",
    "        SELECT code\n",
    "        FROM student, class, takes \n",
    "        WHERE last = 'Nova' \n",
    "        AND first = 'Tim' \n",
    "        AND id = student_id \n",
    "        AND code = class_code\n",
    "    )    \n",
    "    ORDER BY last\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c70ae8d-2fac-4098-b478-00521cba4540",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2d181d2-f405-4fe9-abae-aef1fb1cc69c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}