Mission 7 Performing JOIN operations in MySQL database using python.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mission 7: Performing different JOIN operations in MySQL database using python \n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Little Lemon needs to carry out the following tasks with their datasets: \n",
    "\n",
    "* Determine the final bill amount for each customer who attended the restaurant today \n",
    "* Identify the type of cuisine that each item in their menu belongs to. \n",
    "* Complete these tasks using “JOIN” operations on the restaurant’s MySQL database using Python. "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 1:\n",
    "\n",
    "Little Lemon needs the following information for each of the items in their menu: \n",
    "\n",
    "* The name of each item in the menu \n",
    "* Each menu item’s type\n",
    "* Each menu item’s cuisine\n",
    "* The price of each item in the menu "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MenuItems + Menus\n",
      "('Name', 'type', 'Price', 'Cuisine')\n",
      "('Olives', 'Starters', 5, 'Greek')\n",
      "('Greek salad', 'Main Courses', 15, 'Greek')\n",
      "('Greek yoghurt', 'Desserts', 7, 'Greek')\n",
      "('Athens White wine', 'Drinks', 25, 'Greek')\n",
      "('Minestrone', 'Starters', 8, 'Italian')\n",
      "('Pizza', 'Main Courses', 15, 'Italian')\n",
      "('Cheesecake', 'Desserts', 4, 'Italian')\n",
      "('Turkish Coffee', 'Drinks', 10, 'Italian')\n",
      "('Falafel', 'Starters', 7, 'Turkish')\n",
      "('Ice cream', 'Desserts', 6, 'Turkish')\n",
      "('Turkish Coffee', 'Drinks', 10, 'Turkish')\n",
      "('Kabasa', 'Main Courses', 17, 'Turkish')\n"
     ]
    }
   ],
   "source": [
    "# performing inner join on Menu table and MenuItems table\n",
    "join1 = \"\"\"select MenuItems.Name, MenuItems.type, MenuItems.Price, Menus.Cuisine \n",
    "from MenuItems inner join Menus ON MenuItems.ItemID = Menus.ItemID\"\"\"\n",
    "cursor.execute(join1)\n",
    "results = cursor.fetchall()\n",
    "columns=cursor.column_names\n",
    "print(\"MenuItems + Menus\")\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 2:\n",
    "\n",
    "Little Lemon notice that there are several menu items missing from the output of the previous task. \n",
    "\n",
    "Identify these missing items by using a `JOIN` operation in Python to return data for the missing records. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing content from Menus\n",
      "('ItemID', 'Name', 'Type', 'Price', 'MenuID', 'Cuisine')\n",
      "(2, 'Flatbread', 'Starters', 5, None, None)\n",
      "(4, 'Tomato bread', 'Starters', 8, None, None)\n",
      "(6, 'Hummus', 'Starters', 5, None, None)\n",
      "(8, 'Bean soup', 'Main Courses', 12, None, None)\n",
      "(14, 'Corfu Red Wine', 'Drinks', 30, None, None)\n"
     ]
    }
   ],
   "source": [
    "# find missing items in Menus table usinga left join\n",
    "left_join = \"\"\" select MenuItems.ItemID, MenuItems.Name, MenuItems.Type, MenuItems.Price,\n",
    "Menus.MenuID,Menus.Cuisine from MenuItems left join Menus on MenuItems.ItemID = Menus.ItemID WHERE Cuisine IS NULL;\"\"\" \n",
    "cursor.execute(left_join)\n",
    "results = cursor.fetchall()\n",
    "columns = cursor.column_names\n",
    "print(\"Missing content from Menus\")\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print (result)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 3:\n",
    "\n",
    "Little Lemon restaurant need you to help them retrieve the following information from the `Bookings` and the `Orders` tables in their MySQL database using Python: \n",
    "\n",
    "* Booking ID \n",
    "* Table number \n",
    "* Guest first name \n",
    "* Server ID \n",
    "* Bill amount  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Booking and Orders Content\n",
      "('BookingID', 'TableNo', 'GuestFirstName', 'Billamount')\n",
      "(1, 12, 'Anna', 86)\n",
      "(2, 12, 'Joakim', 86)\n",
      "(3, 19, 'Vanessa', 37)\n",
      "(4, 15, 'Marcos', 37)\n",
      "(5, 5, 'Hiroki', 40)\n",
      "(6, 8, 'Diana', 43)\n"
     ]
    }
   ],
   "source": [
    "# retrieving information from the Bookings and the Orders tables\n",
    "join3 = \"\"\" select Bookings.BookingID, Bookings.TableNo, Bookings.GuestFirstName, Orders.Billamount \n",
    "from Bookings inner join Orders on Bookings.TableNo = Orders.TableNo;\"\"\"\n",
    "cursor.execute(join3)\n",
    "results = cursor.fetchall()\n",
    "columns = cursor.column_names\n",
    "print(\"Booking and Orders Content\")\n",
    "print (columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The cursor is closed.\n",
      "MySQL connection is closed.\n"
     ]
    }
   ],
   "source": [
    "if connection.is_connected():\n",
    "    cursor.close()\n",
    "    print(\"The cursor is closed.\")\n",
    "    connection.close()\n",
    "    print(\"MySQL connection is closed.\")\n",
    "else:\n",
    "    print(\"Connection is already closed\")"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
  },
  "kernelspec": {
   "display_name": "Python 3.8.10 64-bit",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}