6. Data Manipulation Language (DML).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e0428797-0114-4957-89bb-02f5143abce0",
   "metadata": {},
   "source": [
    "# 6. Data Manipulation Language (DML)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c5be03e-4289-48d1-9975-756b8dd97af1",
   "metadata": {},
   "source": [
    "Data Manipulation Language (DML) in MySQL consists of SQL commands that are used to manipulate and **interact with the data** stored in a MySQL database. DML commands primarily focus on querying, inserting, updating, and deleting data within database tables. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d8b9ba15-ef05-45cf-b37d-0f19e8e1d7cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandasql import sqldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "dc8ebf70-5f97-4e93-b114-a243d8bd3e5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "authors = pd.read_csv('CSV/authors.csv')\n",
    "books = pd.read_csv('CSV/books.csv')\n",
    "customers = pd.read_csv('CSV/customers.csv')\n",
    "payments = pd.read_csv('CSV/payments.csv')\n",
    "authorsbooks = pd.read_csv('CSV/authorsbooks.csv')\n",
    "reviews = pd.read_csv('CSV/reviews.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "260b1004-3153-471d-936b-d9c734716244",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## SELECT statement\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dcb0df3-7ccc-404a-8efb-c534fb1d87dc",
   "metadata": {},
   "source": [
    "**ORDER BY**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9b90e16-3b25-4237-8031-8088fb721d0c",
   "metadata": {},
   "source": [
    "`SELECT` is used to query data in the database. Select command, or statement allows the user to extract data from tables, based on specific criteria. It is processed according to the following sequence:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dd9cb71-86f0-4726-8bd4-c358e6709660",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT (DISTINCT) item(s)\r\n",
    "FROM  table(s)\r\n",
    "WHERE  predicate\r\n",
    "GROUP BY  field(s)\r\n",
    "ORDER BY fields"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f96abf4e-91ae-47b3-8da8-14da56027db6",
   "metadata": {},
   "source": [
    "Let's retrieve a list of Authors and let's order them by their nationality:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "016b04ff-75fc-4a06-8922-14cec951d068",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlauthors1 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "bd768d07-f455-40aa-9a51-a310f00df4b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "query1 = \"\"\"\n",
    "    SELECT author_name, nationality\n",
    "    FROM authors\n",
    "    ORDER BY nationality;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3bc1ecce-4950-4aca-9075-2fa2951109c7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        author_name          nationality\n",
      "0        John Smith             American\n",
      "1      Olivia Gomez             Anguilla\n",
      "2      Rafael Gomez  Antigua and Barbuda\n",
      "3    Luis Rodriguez            Argentina\n",
      "4    Isabella Lopez          Argentinian\n",
      "..              ...                  ...\n",
      "215      Lucia Diaz              Uruguay\n",
      "216    Lucas Wilson              Vanuatu\n",
      "217     Pedro Silva            Venezuela\n",
      "218      Elena Soto           Venezuelan\n",
      "219      Dong Hoang           Vietnamese\n",
      "\n",
      "[220 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "result1 = pysqlauthors1(query1)\n",
    "print(result1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "814eb9d7-7443-44fd-bea9-6a10979bfe58",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## SELECT statement with WHERE criteria"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f535be57-982c-4ac3-8e33-0296a4c1f11d",
   "metadata": {},
   "source": [
    "### where + between"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "616438c9-4299-446a-ad32-d61c20ff724a",
   "metadata": {},
   "source": [
    "We will select all the books who`s price is between 10 and 20 Euros:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b7dc4f45-b565-42c1-bb8c-02fdae578aef",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlbooks1 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "52531001-408a-4ad0-8fa7-8ed53acec5ec",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                           title  price\n",
      "0                 Legends of the Northern Lights  10.35\n",
      "1                          Fjords and Fairytales  16.08\n",
      "2   The Art of Tradition: Celebrations Worldwide  17.58\n",
      "3   Cultural Cornerstones: Practices and Customs  14.65\n",
      "4                       The Philosophy of Wisdom  15.24\n",
      "5                         The Works of Aristotle  17.03\n",
      "6                              The Unseen Threat  17.34\n",
      "7                              Romantic Getaways  16.78\n",
      "8                               Cherished Dreams  10.88\n",
      "9               Quick and Easy Weeknight Dinners  10.70\n",
      "10                          African Safari Tales  18.69\n",
      "11                                Chasing Dreams  10.73\n",
      "12                                  Hearts Afire  11.21\n",
      "13                            The Forbidden Love  11.29\n",
      "14                             Steam and Shadows  19.10\n",
      "15                            Clockwork Carnival  13.09\n",
      "16                              Hoops and Dreams  17.98\n",
      "17                               Surfing Safaris  16.16\n",
      "18                              The Lean Startup  13.79\n",
      "19                               Business Ethics  14.49\n",
      "20                      Digital Marketing Trends  17.00\n",
      "21                 Sales and Negotiation Tactics  18.79\n",
      "22                   The Art of Entrepreneurship  19.30\n",
      "23                      Investing in Your Future  19.68\n",
      "24                              The Tao Te Ching  13.50\n",
      "25                          The Book of the Dead  11.06\n",
      "26                                    The Talmud  17.16\n",
      "27                                   The I Ching  17.24\n",
      "28                         The Book of Awakening  15.98\n",
      "29                                        Walden  17.34\n",
      "30                             The End of Nature  18.22\n",
      "31                                 The Overstory  19.11\n",
      "32                       The Ecology of Commerce  14.55\n",
      "33                            The Weather Makers  18.42\n",
      "34                                Eternal Echoes  12.41\n",
      "35                                 Aurora Rhymes  12.99\n",
      "36                                Unicorn Dreams  15.09\n",
      "37                           Lilly of the Valley  12.50\n"
     ]
    }
   ],
   "source": [
    "query2 = \"\"\"SELECT title, price\n",
    "    FROM books\n",
    "    WHERE price \n",
    "    BETWEEN 10 AND 20\"\"\"\n",
    "\n",
    "result2 = pysqlbooks1(query2)\n",
    "print(result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8997bae6-703c-4aae-8712-c78c942ef852",
   "metadata": {},
   "source": [
    "### where + not between"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe8235ab-4d7e-4914-b62e-9999d1ad452a",
   "metadata": {},
   "source": [
    "Let's select books who's rating is not between 1 and 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "33962bb8-a41a-4282-ac50-1207f220e6fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlbooks2 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "7f0bbd87-a103-456d-9b91-ff14d0915de8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                               title  avarage_rating\n",
      "0              The Starry Chronicles             4.5\n",
      "1          The Lost City of Atlantis             4.7\n",
      "2     Journey to the Cosmic Frontier             4.4\n",
      "3       The Quest for Eternal Wisdom             4.3\n",
      "4          The Time Traveler's Diary             4.6\n",
      "..                               ...             ...\n",
      "383  The Adventure of Mr. Teddy Bear             4.5\n",
      "384              The Magical Kingdom             4.4\n",
      "385             Mystery in Candyland             4.3\n",
      "386          The Brave Little Knight             4.2\n",
      "387              Alice in Whimsyland             4.1\n",
      "\n",
      "[388 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "query3 = \"\"\"SELECT title, avarage_rating\n",
    "    FROM books\n",
    "    WHERE avarage_rating \n",
    "    NOT BETWEEN 1 AND 4\"\"\"\n",
    "\n",
    "result3 = pysqlbooks2(query3)\n",
    "print(result3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fa953c1-629b-4d53-ac9d-6ca73b24c937",
   "metadata": {},
   "source": [
    "### where + or"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "329b1831-9fda-49e9-93c2-aedc8f371c12",
   "metadata": {},
   "source": [
    "Let`s select authors whos nationality is Latvian or Spanish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9613b17e-d10e-469e-86b3-a3333861ad46",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlauthors2 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "2e981f53-1983-4164-a893-122165738f38",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          author_name nationality\n",
      "0        Maria García     Spanish\n",
      "1     Carlos Martínez     Spanish\n",
      "2   Ricardo Fernandez     Spanish\n",
      "3        Maria Garcia     Spanish\n",
      "4      Sara Fernández     Spanish\n",
      "5     Javier González     Spanish\n",
      "6         Diego Perez     Spanish\n",
      "7       Carmen Torres     Spanish\n",
      "8       Lucia Morales     Spanish\n",
      "9      Andris Bērziņš     Latvian\n",
      "10      Laima Ozoliņa     Latvian\n",
      "11    Jānis Pētersons     Latvian\n",
      "12       Inese Sīmane     Latvian\n",
      "13     Mārtiņš Ķirsis     Latvian\n",
      "14       Zane Bērziņa     Latvian\n",
      "15     Valdis Salmiņš     Latvian\n",
      "16       Līga Kalniņa     Latvian\n",
      "17     Pēteris Auziņš     Latvian\n",
      "18  Silvija Riekstiņa     Latvian\n",
      "19    Elena Rodriguez     Spanish\n",
      "20    Isabel Martinez     Spanish\n",
      "21     Luis Velazquez     Spanish\n"
     ]
    }
   ],
   "source": [
    "query4 = \"\"\"SELECT author_name, nationality\n",
    "    FROM authors\n",
    "    WHERE nationality = 'Latvian' \n",
    "    OR nationality = 'Spanish' \"\"\"\n",
    "\n",
    "result4 = pysqlauthors2(query4)\n",
    "print(result4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1947e52-95a9-4aa9-9d05-cffe21af01cd",
   "metadata": {},
   "source": [
    "### where + in"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60e40c61-1c5a-47ca-ac75-dad5f7e545f2",
   "metadata": {},
   "source": [
    "Select customers who are from Spain or Germany, or UK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "c884da83-c203-4518-8535-afb9f613625e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlcustomers1 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "3e10f565-4d96-4b95-8dc7-fd54dbb7560a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   first_name  last_name country\n",
      "0        John      Smith      UK\n",
      "1      Sophia      Brown   Spain\n",
      "2      Olivia  Rodriguez  Latvia\n",
      "3   Charlotte    Collins  Latvia\n",
      "4     Alessia      Ricci   Spain\n",
      "5     Antonio     Moreno   Spain\n",
      "6     Luciano      Serra   Spain\n",
      "7      Matteo      Gallo   Spain\n",
      "8    Riccardo      Rossi   Spain\n",
      "9        Luna      Perez   Spain\n",
      "10    Alessio    Martini   Spain\n",
      "11        Eva   Lombardi   Spain\n",
      "12      Elena    Ivanova   Spain\n",
      "13       Luis     García   Spain\n",
      "14       Sara      Lopez   Spain\n",
      "15      Elena    Ivanova   Spain\n",
      "16       Luis     García   Spain\n",
      "17       Sara      Lopez   Spain\n",
      "18  Sebastian     Larsen  Latvia\n",
      "19       Ance       Soko   Spain\n"
     ]
    }
   ],
   "source": [
    "query5 = \"\"\"SELECT first_name, last_name, country\n",
    "    FROM customers\n",
    "    WHERE country IN ('Latvia' , 'Spain', 'UK')\"\"\"\n",
    "\n",
    "result5 = pysqlcustomers1(query5)\n",
    "print(result5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c145e25a-1db0-4362-acb2-85b47d1afd7b",
   "metadata": {},
   "source": [
    "### where + NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1a2e498-7395-4869-a23b-bda3a3d8868b",
   "metadata": {},
   "source": [
    "Select books who`s review comments are NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "aa6fdbab-7b0d-4e83-ae55-a462c3d9e3ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlbooks3 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "8824103b-4dec-4d41-bd94-b8791739a3e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 title avarage_rating\n",
      "0  Lilly of the Valley           None\n",
      "1       One nightstand           None\n"
     ]
    }
   ],
   "source": [
    "query6 = \"\"\"SELECT title, avarage_rating\n",
    "    FROM books\n",
    "    WHERE avarage_rating IS NULL\"\"\"\n",
    "\n",
    "result6 = pysqlbooks3(query6)\n",
    "print(result6)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e86216f7-d368-4eba-8085-d72acaa955cb",
   "metadata": {},
   "source": [
    "### where + NOT NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a61a3aa1-4ba7-4bb4-aaea-6ff455bfb36a",
   "metadata": {},
   "source": [
    "Retrieve data about books that doesn`t have NULL value in their avarage_rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "53052526-3979-4619-85f7-4243cbec0031",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlbooks4 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "bbedd696-ed98-4e2e-8123-2e6e85b76d44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                 title  avarage_rating\n",
      "0                The Starry Chronicles             4.5\n",
      "1      Secrets of the Enchanted Forest             2.8\n",
      "2            The Lost City of Atlantis             4.7\n",
      "3       Journey to the Cosmic Frontier             4.4\n",
      "4         The Quest for Eternal Wisdom             4.3\n",
      "..                                 ...             ...\n",
      "516      The Space Adventures of Ziggy             3.0\n",
      "517      The Daring Adventures of Lucy             3.0\n",
      "518  The Mystery of the Missing Cookie             3.0\n",
      "519                     Unicorn Dreams             2.0\n",
      "520                    The second hope             4.0\n",
      "\n",
      "[521 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "query7 = \"\"\"SELECT title, avarage_rating\n",
    "    FROM books\n",
    "    WHERE avarage_rating IS NOT NULL\"\"\"\n",
    "\n",
    "result7 = pysqlbooks4(query7)\n",
    "print(result7)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55a58f36-b22f-4aa9-af32-77df297c748f",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## Using wildcards in the LIKE clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfc29035-0f19-4a6a-b23f-02116b49c6d4",
   "metadata": {},
   "source": [
    "The `LIKE` keyword selects rows containing fields that match specified portions of character strings. LIKE is used with char, varchar, text, datetime and smalldatetime data. A wildcard allows the user to match fields that contain certain letters."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5dc8dfed-de3b-4cfe-83a9-23cc47a777a2",
   "metadata": {},
   "source": [
    "| Symbol | Meaning |\n",
    "|--------|---------|\n",
    "|    %   |any string of zero or more characters|\n",
    "|   _    |any single character|\n",
    "| [  ]     |any single character within the specified range ([a-f], [abcdf])|\n",
    "| [^]    |any single character not within the specified range ([^a-f] or [^abcdf])|"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6541a1a-9841-429d-9fc5-2e9c113c1c4d",
   "metadata": {},
   "source": [
    "Let`s select all the last names of customers that start with \"An\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "466a0c1f-adc1-49ee-9add-9e007702456e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqlcustomers2 = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "12b8d1b2-d65f-4b38-9645-3e46f3d39def",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   customer_id first_name last_name\n",
      "0          128   Patricia  Anderson\n",
      "1          172    Kwabena    Ankrah\n",
      "2          232    Adriano   Andrade\n"
     ]
    }
   ],
   "source": [
    "query8 = \"\"\"SELECT customer_id, first_name, last_name\n",
    "    FROM customers\n",
    "    WHERE last_name LIKE 'An%'\"\"\"\n",
    "\n",
    "result8 = pysqlcustomers2(query8)\n",
    "print(result8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "054f5acd-66aa-446d-b658-e28569b276c2",
   "metadata": {},
   "source": [
    "Let`s select all the last names of customers that ends with the letterss \"on\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "64c5e632-b697-4405-9368-d0b7efe821ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   first_name   last_name\n",
      "0       Alice     Johnson\n",
      "1      Olivia      Wilson\n",
      "2         Mia     Johnson\n",
      "3       Alice     Johnson\n",
      "4      Olivia      Wilson\n",
      "5         Mia     Johnson\n",
      "6       Ethan     Johnson\n",
      "7        Mary     Johnson\n",
      "8     Michael      Wilson\n",
      "9    Patricia    Anderson\n",
      "10  Elizabeth     Jackson\n",
      "11       Emma     Johnson\n",
      "12      James      Wilson\n",
      "13       Ella      Wilson\n",
      "14     Olivia     Johnson\n",
      "15      Riley    Thompson\n",
      "16       Lucy      Wilson\n",
      "17       Emma   Johansson\n",
      "18      Hanna  Gustavsson\n"
     ]
    }
   ],
   "source": [
    "pysqlcustomers3 = lambda q: sqldf(q, globals())\n",
    "\n",
    "query9 = \"\"\"SELECT first_name, last_name\n",
    "    FROM customers\n",
    "    WHERE last_name LIKE '%on'\"\"\"\n",
    "\n",
    "result9 = pysqlcustomers3(query9)\n",
    "print(result9)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "221c398c-9308-40d1-85f7-84fb77edba5b",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## SELECT statement with ORDER BY clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6aef6080-a673-4b9f-929d-26f104e67369",
   "metadata": {},
   "source": [
    "`ORDER BY` clause is used to sort the records in the resulting list. Use ASC to sort the results in ascending order and DESC to sort the results in descending order.\n",
    "By default ORDER BY is sorted in ASC manner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "736b1649-63d4-4b2f-b351-570dff3f5319",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    first_name last_name    country\n",
      "0        Nadia  Mokhtari    Algeria\n",
      "1      Eduardo     Gomez  Argentina\n",
      "2      William     Adams    Armenia\n",
      "3         Liam     Smith  Australia\n",
      "4       Olivia   Johnson  Australia\n",
      "..         ...       ...        ...\n",
      "305       Minh    Nguyen    Vietnam\n",
      "306        Mai      Tran    Vietnam\n",
      "307      Phong    Nguyen    Vietnam\n",
      "308       Quoc      Pham    Vietnam\n",
      "309      Nadia    Zahran      Yemen\n",
      "\n",
      "[310 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "pysqlcustomers4 = lambda q: sqldf(q, globals())\n",
    "\n",
    "query10 = \"\"\"SELECT first_name, last_name, country\n",
    "    FROM customers\n",
    "    ORDER BY country\"\"\"\n",
    "\n",
    "result10 = pysqlcustomers4(query10)\n",
    "print(result10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "032f8cb8-cacb-4549-a75a-083d4bf62627",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    first_name last_name    country\n",
      "0        Nadia    Zahran      Yemen\n",
      "1         Minh    Nguyen    Vietnam\n",
      "2          Mai      Tran    Vietnam\n",
      "3        Phong    Nguyen    Vietnam\n",
      "4         Quoc      Pham    Vietnam\n",
      "..         ...       ...        ...\n",
      "305     Sophie    Martin  Australia\n",
      "306       Lucy    Wilson  Australia\n",
      "307    William     Adams    Armenia\n",
      "308    Eduardo     Gomez  Argentina\n",
      "309      Nadia  Mokhtari    Algeria\n",
      "\n",
      "[310 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "pysqlcustomers5 = lambda q: sqldf(q, globals())\n",
    "\n",
    "query11 = \"\"\"SELECT first_name, last_name, country\n",
    "    FROM customers\n",
    "    ORDER BY country DESC\"\"\"\n",
    "\n",
    "result11 = pysqlcustomers5(query11)\n",
    "print(result11)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f5761e6-931e-45dc-b209-f0d5bd513683",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## SELECT statement with GROUP BY clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3401c8d8-e3f5-4243-8e57-0d2c508f6bd4",
   "metadata": {},
   "source": [
    "The `GROUP BY` clause is used to create one output row per each group and produces summary values for the selected columns,"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1b62eba-3780-4bd1-a645-803ee3fde585",
   "metadata": {},
   "source": [
    "Let's group payment methods:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e5381543-0e59-471d-bb4b-55743a50bdc0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  payment_method\n",
      "0     App Wallet\n",
      "1    Credit Card\n",
      "2         PayPal\n"
     ]
    }
   ],
   "source": [
    "pysqlpayments1 = lambda q: sqldf(q, globals())\n",
    "\n",
    "query12 = \"\"\"SELECT payment_method\n",
    "    FROM payments\n",
    "    GROUP BY 1\"\"\"\n",
    "\n",
    "result12 = pysqlpayments1(query12)\n",
    "print(result12)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54ceec5a-c571-4cce-8cd1-36d53d3fee5d",
   "metadata": {},
   "source": [
    "### Using COUNT with GROUP BY clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "002caed6-9cb7-48b8-8878-7971fb49d489",
   "metadata": {},
   "source": [
    "Let's see how many authors there are by country, or let's group authors by country:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "606e852e-3805-40f8-8ead-6f93179cd821",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             nationality  quantity\n",
      "0               American         1\n",
      "1               Anguilla         1\n",
      "2    Antigua and Barbuda         1\n",
      "3              Argentina         1\n",
      "4            Argentinian         3\n",
      "..                   ...       ...\n",
      "102              Uruguay         1\n",
      "103              Vanuatu         1\n",
      "104            Venezuela         1\n",
      "105           Venezuelan         1\n",
      "106           Vietnamese         1\n",
      "\n",
      "[107 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "pysqlauthors3 = lambda q: sqldf(q, globals())\n",
    "\n",
    "query13 = \"\"\"SELECT nationality, count(authors_id) AS quantity\n",
    "    FROM authors\n",
    "    GROUP BY 1\"\"\"\n",
    "\n",
    "result13 = pysqlauthors3(query13)\n",
    "print(result13)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e806f07-a87e-4b83-9f26-af5585f29446",
   "metadata": {},
   "source": [
    "### Using AVG and SUM with GROUP BY"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1dfd14bd-0c84-45db-b31a-780ebfb2a2fa",
   "metadata": {},
   "source": [
    "We can use the `AVG` function to give us the average of any group, and `SUM` to give the total."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b02c8ea-e6cf-4673-b057-25786fdbbaff",
   "metadata": {},
   "source": [
    "Let`s make a  query that calculates and displays the average payment amount and the total sum of payment amounts for each distinct payment method, and that orders the results by the average payment amount in ascending order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "41c71e67-6071-4cad-beca-1f087bc4c004",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  payment_method  avg_amount  sum_amount\n",
      "0     App Wallet      777.99   515030.72\n",
      "1         PayPal      793.75   505620.16\n",
      "2    Credit Card      799.66   569356.76\n"
     ]
    }
   ],
   "source": [
    "pysqlpayments2 = lambda q: sqldf(q, globals())\n",
    "\n",
    "query14 = \"\"\"SELECT payment_method, ROUND(AVG(amount),2) AS avg_amount, ROUND(SUM(amount),2) AS sum_amount\n",
    "    FROM payments\n",
    "    GROUP BY 1\n",
    "    ORDER BY avg_amount\"\"\"\n",
    "\n",
    "result14 = pysqlpayments2(query14)\n",
    "print(result14)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f471b30a-9efc-46ad-b043-bfb186b235c8",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## Restricting rows with HAVING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83d755b6-bb48-49d1-8e86-64ec33946dfc",
   "metadata": {},
   "source": [
    "The `HAVING` clause can be used to restrict rows. It is similar to the WHERE condition except HAVING can include the aggregate function; the WHERE cannot do this. The HAVING clause behaves like the WHERE clause, but is applicable to groups. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "809a288a-1c14-456e-a7a9-95c2f99f6f9e",
   "metadata": {},
   "source": [
    "Make a query that calculates book quantity associated with each author, but the HAVING clause filters only those authors who have more than 4 books associated with them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "5eda15de-b863-4f99-8c75-1f6a76497c6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    authors_id  book_quantity\n",
      "0           20              6\n",
      "1           27              5\n",
      "2           41              5\n",
      "3          106              5\n",
      "4          115              5\n",
      "5          118              6\n",
      "6          127              6\n",
      "7          138              5\n",
      "8          142              5\n",
      "9          160              5\n",
      "10         164              5\n",
      "11         168              6\n",
      "12         186              7\n",
      "13         189              6\n",
      "14         218              7\n"
     ]
    }
   ],
   "source": [
    "pysqlauthorsbooks1 = lambda q: sqldf(q, globals())\n",
    "\n",
    "query15 = \"\"\"SELECT authors_id, COUNT(book_id) AS book_quantity\n",
    "    FROM authorsbooks\n",
    "    GROUP BY 1\n",
    "    HAVING book_quantity>4\"\"\"\n",
    "\n",
    "result15 = pysqlauthorsbooks1(query15)\n",
    "print(result15)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "816d4ace-16ea-4042-b07a-241ba6195910",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## INSERT statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9060cff-de8a-4fa8-82ff-1946b445955d",
   "metadata": {},
   "source": [
    "The `INSERT` statement adds rows to a table.  \n",
    "- INSERT specifies the table or view that data will be inserted into\n",
    "- Column_list lists columns that will be affected by the INSERT\n",
    "- If a column is ommited, each value must be provided\n",
    "- If you are including columns, they can be listed in any order\n",
    "- VALUES specifies the data that you want to insert into the table. VALUE is required.clause."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70de0d18-b6b1-42f2-a788-43baa059056b",
   "metadata": {},
   "source": [
    "```sql\n",
    "INSERT [INTO] Table_name | view name [column_list]\r\n",
    "DEFAULT VALUES | values_list | select statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fbf1b63-5386-4d2f-9116-03b3e3bb795b",
   "metadata": {},
   "source": [
    "This example inserts values into authors table:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae833d85-4023-4492-bd38-c07cb8ad6593",
   "metadata": {},
   "source": [
    "\n",
    "```sql\n",
    "INSERT INTO authors (author_name, birth_date, nationality)\n",
    "VALUES\n",
    "    ('John Smith', '1980-05-15', 'American'),\n",
    "    ('Maria García', '1991-12-10', 'Spanish'),\n",
    "    ('Hiroshi Tanaka', '1975-03-22', 'Japanese'),\n",
    " ..."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96598ae8-383f-498e-8c6b-623c6d1f5157",
   "metadata": {},
   "source": [
    "### Inserting rows with SELECT statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e88af4d7-ddb6-49e5-a83e-1f6ae3c3fdaa",
   "metadata": {},
   "source": [
    "We can sometimes create a small temporary table from a large table. For this, we can insert rows with a SELECT statement. When using this command, there is no validation for uniqueness."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "609e2a1a-43c2-4395-af3f-63faf929a542",
   "metadata": {},
   "source": [
    "Create the temporary table:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d16abe68-45d8-432c-9d50-325ad2b2170d",
   "metadata": {},
   "source": [
    "```sql\n",
    "CREATE TEMPORARY TABLE authors_temp (\n",
    "    id INT PRIMARY KEY AUTO_INCREMENT,\n",
    "    first_name VARCHAR(50),\n",
    "    last_name VARCHAR(50),\n",
    "    nationality VARCHAR(50)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9af525d7-2e0d-4d2b-a4f6-89ffba869107",
   "metadata": {},
   "source": [
    "Inserting data into permament table:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37a03c3f-579d-4d5f-a3fd-e03db1b04b63",
   "metadata": {},
   "source": [
    "```sql\n",
    "INSERT INTO authors \n",
    "SELECT * FROM authors_temp;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5b235a9-b092-4787-8ad4-cee9154ce832",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## UPDATE statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1937fb9-c042-4e13-9c8c-a82b1f1253dc",
   "metadata": {},
   "source": [
    "The `UPDATE` statement changes data in existing rows either by adding new data or modifying existing data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aaac3454-ea51-4a78-aa8c-36e0339869bb",
   "metadata": {},
   "source": [
    "Be careful to use WHERE clause!!! If not, the changes will be applied to all the selected table."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f306fad9-299a-4c02-821b-8878b7af399d",
   "metadata": {},
   "source": [
    "In this case all the authors nationality will be set to Latvian:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddff01fe-96cc-4c4a-b39f-d63cc4aba869",
   "metadata": {},
   "source": [
    "```sql\n",
    "UPDATE authors\n",
    "SET nationality = 'Latvian';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "591052db-10bf-4c80-b529-425f1676ad10",
   "metadata": {},
   "source": [
    "You probably are looking for something like this:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e865786-6a3c-4769-befe-1c2f2c5ab79c",
   "metadata": {},
   "source": [
    "```sql\n",
    "UPDATE authors\n",
    "SET nationality = 'Latvian'\n",
    "WHERE author_id = 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eff2e813-d880-4268-bd2d-c82adcbb837e",
   "metadata": {},
   "source": [
    "As authors id is unique, this UPDATE will only affect the author with ID 3 and change it`s nationality to Latvian, instead of affecting all the table."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a155976c-302e-40ed-9e0f-cdfb6b2ce103",
   "metadata": {},
   "source": [
    "Christmas is coming, so let's apply 5% discount to all the book prices:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57fbdf41-0e2f-45da-8821-dc150a63f8ff",
   "metadata": {},
   "source": [
    "```sql\n",
    "UPDATE books\r\n",
    "SET price = price * 0.95;\r\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86a1f065-bec1-489e-ab97-a95521dcb42a",
   "metadata": {},
   "source": [
    "### Including subqueries in an UPDATE statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7e025da-c1c3-4838-ae17-34388564d282",
   "metadata": {},
   "source": [
    "Supposedly, we want to update the avarage_rating for a specific book in the books table based on the ratings provided in the reviews table for that book (identified by book_id). We can use a subquery to calculate the average rating and update the book's avarage_rating:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dc29fbe-17d4-4344-b4fe-c3b245d85a43",
   "metadata": {},
   "source": [
    "```sql\n",
    "UPDATE books\r\n",
    "SET avarage_rating = (\r\n",
    "    SELECT AVG(rating) \r\n",
    "    FROM reviews \r\n",
    "    WHERE reviews.book_id = books.book_id\r\n",
    ")\r\n",
    "WHERE book_ update\r\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9554c3cc-ad52-42d8-bf5f-563a1cfdfd0f",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## DELETE statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d09392f2-7d65-4e04-aaea-d63b839bb5ed",
   "metadata": {},
   "source": [
    "The `DELETE` statement removes rows from a record set. DELETE names the table or view that holds the rows that will be deleted and only one table or row may be listed at a time. WHERE is  a standard WHERE clause that limits the deletion to select records."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47afc69c-d119-438d-8069-796f09aa1881",
   "metadata": {},
   "source": [
    "```sql\n",
    "DELETE [FROM] {table_name | view_name }\r\n",
    "[WHERE clause]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83de2913-102d-45eb-a2e6-0429b59978f6",
   "metadata": {},
   "source": [
    "If you ommit WHERE clause, all rows in the table are removed (except for indexes, constraints and the table itself)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d985f2c1-1eba-44a6-a5f6-8fcdbc4af6f3",
   "metadata": {},
   "source": [
    "What follows are three different DELETE statements that can be used:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad326eaa-ebcf-4812-b5e0-9bfd14ab5688",
   "metadata": {},
   "source": [
    "- Deleting all rows from a table:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e862ddd2-41c3-4106-b888-fe56a86397f9",
   "metadata": {},
   "source": [
    "```sql\n",
    "DELETE \n",
    "FROM authors;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15d1415d-594c-4e41-a133-95d894307dfb",
   "metadata": {},
   "source": [
    "- Deleting selected rows:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bafe5be-6c21-41c2-92c4-06923bbc7d4e",
   "metadata": {},
   "source": [
    "```sql\n",
    "DELETE FROM authors\n",
    "WHERE author_id = 123;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "801f2dce-5d44-4096-8938-cb601a0682e5",
   "metadata": {},
   "source": [
    "- Deleting rows based on a value in a subquery:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c6e04fa-92a5-4785-8987-7f08afacd6e8",
   "metadata": {},
   "source": [
    "```sql\n",
    "DELETE FROM payments\n",
    "WHERE order_id IN\n",
    "(SELECT order_id FROM orders WHERE status = 'Cancelled');"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8f795c8-9230-4a65-becc-6aea949e1fb9",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## BUILT-IN function"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3660a2f3-cb73-40d0-b953-30baced4db01",
   "metadata": {},
   "source": [
    "There are plenty of built-in functions in MySQL that facilitates data manipulation. Here I will sum up the ones that  I find very useful in daily life of someone who works with MySQL:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54579b5a-f282-40fe-b053-630d5d9860b2",
   "metadata": {},
   "source": [
    "### MATH functions:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1f25d6f-c7af-4e6f-82f0-b9ca40a9ff7c",
   "metadata": {},
   "source": [
    "|FUNCTION|USE|\n",
    "|--------|---|\n",
    "|ROUND() | Rounds a number to a specified number of decimal places|\n",
    "|FLOOR() | Returns the largest integer less than or equal to the number|\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fdd4f4e-dcd1-4595-845d-b86d567d4977",
   "metadata": {},
   "source": [
    "### STRING functions:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a3e3700-a344-43c3-903c-2723de1dc247",
   "metadata": {},
   "source": [
    "|FUNCTION|USE|\n",
    "|--------|---|\n",
    "|CONCAT() | Concatenates two or more strings|\n",
    "|LENGTH() | Returns the length of a string|\n",
    "|UPPER()  | Converts a string to uppercase|\n",
    "|LOWER()  | Converts a string to lowercase|\n",
    "|SUBSTRING()| Extracts a substring from a string|\n",
    "|REPLACE() | Replaces occurrences of a substring with another substring|"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e93ecde-3ef4-4db0-80d9-554eebcb593c",
   "metadata": {},
   "source": [
    "### DATE & TIME functions:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e13bd1bb-e39a-4b02-a01a-159b7423e594",
   "metadata": {},
   "source": [
    "|FUNCTION|USE|\n",
    "|--------|---|\n",
    "|NOW() | Returns the current date and time|\n",
    "|DATE()| Extracts the date part from a datetime|\n",
    "|TIME()| Extracts the time part from a datetime|\n",
    "|DATE_FORMAT()| Formats a datetime value as a string|\n",
    "|DATEDIFF()| Returns the number of days between two dates|\n",
    "|DATEADD()| Adds and increments date values |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d18c20a-21b8-4a04-b9dc-86cbc99f6ce7",
   "metadata": {},
   "source": [
    "### AGGREGATE functions:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8687566e-325b-47bf-84f8-2af057b42261",
   "metadata": {},
   "source": [
    "|FUNCTION|USE|\n",
    "|--------|---|\n",
    "|SUM() | Calculates the sum of set of values|\n",
    "|AVG()| Calculates the avarage of set of values|\n",
    "|COUNT()| Counts the number of rows or non-null values|\n",
    "|MAX()| Returns the maximum value in a set of values|\n",
    "|MIN()|  Returns the minimum value in a set of values|\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c35c6fd7-0f43-48d2-9895-c8580b148eb8",
   "metadata": {},
   "source": [
    "### CONDITIONAL functions:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cc82d86-81a6-4c4b-bf50-1b43866a4a64",
   "metadata": {},
   "source": [
    "|FUNCTION|USE|\n",
    "|--------|---|\n",
    "|IF() | Returns one value if a condition is true, and another value if it's false|\n",
    "|CASE WHEN()| Allows you to perform conditional operations in a SQL query|"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a9d7688-4912-49b6-9d83-dcf5e6ddcbf1",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## JOINING tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f70cd5cf-4e85-492d-bda7-67df8866d926",
   "metadata": {},
   "source": [
    "Joining two or more tables is the process of comparing the data in specified columns and using the comparison results to form a new table from the rows that qualify.fied."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "792fd1f5-f477-4a14-b221-ced23c8eb12e",
   "metadata": {},
   "source": [
    "A join statement:\n",
    "- specifies a column from each table\n",
    "- compares the values in those columns row by row\n",
    "- combines rows with qualifying values into a new row"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1faa879e-ccc6-4898-b10c-d2edd3a54938",
   "metadata": {},
   "source": [
    "Although the comparison is usually for equality – values that match exactly – other types of joins can also be specified."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b496f89b-1973-4c90-903e-19273b3f1130",
   "metadata": {},
   "source": [
    "### INNER join"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47b6de21-81eb-491a-9a72-6284e50ea166",
   "metadata": {},
   "source": [
    "An inner join connects two tables on a column with the same data type. Only the rows where the column values match are returned; unmatched rows are discarded."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fa84e7f-33b3-49bb-8551-1e41496fffc3",
   "metadata": {},
   "source": [
    "This query retrieves author names and the titles of books they have authored, and it will only return records where there are matching entries in all three tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "b361b4e8-4775-42e3-af0d-21a59c5493f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       author_name                           title\n",
      "0    Adriana Perez          The Ultimate BBQ Guide\n",
      "1    Adriana Perez                       The Torah\n",
      "2        Ahmed Ali  Infinite Realms of Imagination\n",
      "3        Ahmed Ali              Echoes of Eternity\n",
      "4        Ahmed Ali               The Secret Garden\n",
      "..             ...                             ...\n",
      "536  Yusuf Erdogan           Equestrian Excellence\n",
      "537   Zane Bērziņa            Metaphysical Musings\n",
      "538   Zane Bērziņa             The Haunting Melody\n",
      "539   Zane Bērziņa             Lilly of the Valley\n",
      "540     Zoe Turner          The Mysterious Mansion\n",
      "\n",
      "[541 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "query_combined = \"\"\"SELECT a.author_name, b.title\n",
    "FROM authors a \n",
    "INNER JOIN authorsbooks ab\n",
    "ON a.authors_id = ab.authors_id\n",
    "INNER JOIN books b\n",
    "ON b.book_id = ab.book_id\n",
    "ORDER BY 1\"\"\"\n",
    "\n",
    "result_combined = ps.sqldf(query_combined, globals())\n",
    "print(result_combined)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cd7689f-85e1-4aad-bd92-42b52736e0e0",
   "metadata": {},
   "source": [
    "### LEFT join"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "152d7810-97dc-459d-98ca-1c6a13521f5a",
   "metadata": {},
   "source": [
    "A `LEFT JOIN` specifies that all left outer rows be returned. All rows from the left table that did not meet the condition specified are included in the results set, and output columns from the other table are set to NULL."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b521a03-7e6e-4a73-8357-050d98120ca1",
   "metadata": {},
   "source": [
    "Using a LEFT JOIN to show all the values from authors, all the names for authors and their corresponding book ids and book titles. In my database all the authors have atleast one book associated, but in the opposite case, if there were no books associated to an author, in book id and book title NULL would be represented."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "96863a5c-fc33-41a4-b041-7a7d6da22eb3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        author_name  book_id                                            title\n",
      "0        John Smith      1.0                            The Starry Chronicles\n",
      "1        John Smith    256.0                                   Chasing Dreams\n",
      "2      Maria García      2.0                  Secrets of the Enchanted Forest\n",
      "3      Maria García    398.0                               The Book of Mormon\n",
      "4      Maria García    407.0                              The Gospel of Judas\n",
      "..              ...      ...                                              ...\n",
      "537      Sofia Soto    388.0  Business Growth Strategies for the 21st Century\n",
      "538      Sofia Soto    433.0                            An Inconvenient Truth\n",
      "539      Sofia Soto    455.0                                         Collapse\n",
      "540  Luis Velazquez    113.0                              The Quest for Truth\n",
      "541  Remedios Vegas    527.0                                  The second hope\n",
      "\n",
      "[542 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "query_combined2 = \"\"\"SELECT a.author_name, b.book_id, b.title\n",
    "FROM authors a \n",
    "LEFT JOIN authorsbooks ab\n",
    "ON a.authors_id = ab.authors_id\n",
    "LEFT JOIN books b\n",
    "ON b.book_id = ab.book_id\"\"\"\n",
    "\n",
    "result_combined2 = ps.sqldf(query_combined2, globals())\n",
    "print(result_combined2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27c01a6d-bddf-430b-81c7-597c31e65159",
   "metadata": {},
   "source": [
    "### RIGHT join"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da45208b-e037-44c4-86d1-58407a54aa97",
   "metadata": {},
   "source": [
    "A `RIGHT JOIN` includes, in its result set, all rows from the right table that did not meet the condition specified. Output columns that correspond to the other table are set to NULL."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cf3e249-b4f8-4c33-86f3-d8f5a6fd1061",
   "metadata": {},
   "source": [
    "Use a RIGHT JOIN to show all the books who doesn`t have reviews:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "d82e785f-e8bb-4509-9e34-bfd294357c90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     book_id review_id\n",
      "0          1      None\n",
      "1          4      None\n",
      "2          8      None\n",
      "3          9      None\n",
      "4         12      None\n",
      "..       ...       ...\n",
      "314      516      None\n",
      "315      517      None\n",
      "316      520      None\n",
      "317      522      None\n",
      "318      523      None\n",
      "\n",
      "[319 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "query_combined3 = \"\"\"SELECT b.book_id, r.review_id\n",
    "FROM reviews r \n",
    "RIGHT JOIN books b\n",
    "ON b.book_id = r.book_id\n",
    "WHERE review_id IS NULL\"\"\"\n",
    "\n",
    "result_combined3 = ps.sqldf(query_combined3, globals())\n",
    "print(result_combined3)"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}