{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Consultas de Selección: IN. LIKE, IS NULL \n",
    "\n",
    "## Conexión a la Base de Datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg2\n",
    "import sqlalchemy\n",
    "import matplotlib as plt\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "POSTGRES_ADDRESS = 'localhost' # Este es el servidor, puede ser una IP\n",
    "POSTGRES_PORT = '5432'\n",
    "POSTGRES_USERNAME = 'postgres' \n",
    "POSTGRES_PASSWORD = '1234' \n",
    "POSTGRES_DBNAME = 'dvdrental' \n",
    "\n",
    "# Ahora se configura la cadena de conexión.\n",
    "# Esta es una cadena que contiene los parámetros necesarios para establecer una\n",
    "# conexión con posgres.\n",
    "postgres_str = ('postgresql://{username}:{password}@{ipaddress}:{port}/{dbname}'.format(username=POSTGRES_USERNAME,\n",
    "                                                                                        password=POSTGRES_PASSWORD,\n",
    "                                                                                        ipaddress=POSTGRES_ADDRESS,\n",
    "                                                                                        port=POSTGRES_PORT,\n",
    "                                                                                        dbname=POSTGRES_DBNAME))\n",
    "# A continuación se crea la conexión\n",
    "cnx = create_engine(postgres_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El Operador IN\n",
    "\n",
    "Se utiliza el operador IN en la cláusula WHERE para comprobar si un valor coincide con algún valor en una lista de valores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>return_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>15145</td>\n",
       "      <td>2005-08-31 15:51:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>15315</td>\n",
       "      <td>2005-08-30 01:51:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>14743</td>\n",
       "      <td>2005-08-29 00:18:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>15298</td>\n",
       "      <td>2005-08-28 22:49:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>14475</td>\n",
       "      <td>2005-08-27 08:59:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>14825</td>\n",
       "      <td>2005-08-27 07:01:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>15907</td>\n",
       "      <td>2005-08-25 23:23:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>12963</td>\n",
       "      <td>2005-08-23 11:37:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>13176</td>\n",
       "      <td>2005-08-23 08:50:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>14762</td>\n",
       "      <td>2005-08-23 01:30:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>12250</td>\n",
       "      <td>2005-08-22 23:05:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>13068</td>\n",
       "      <td>2005-08-20 14:44:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2</td>\n",
       "      <td>11614</td>\n",
       "      <td>2005-08-20 07:04:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>11824</td>\n",
       "      <td>2005-08-19 10:11:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>11299</td>\n",
       "      <td>2005-08-10 16:40:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>10437</td>\n",
       "      <td>2005-08-10 12:12:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2</td>\n",
       "      <td>11177</td>\n",
       "      <td>2005-08-10 10:55:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2</td>\n",
       "      <td>11087</td>\n",
       "      <td>2005-08-10 10:37:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2</td>\n",
       "      <td>9236</td>\n",
       "      <td>2005-08-08 18:52:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2</td>\n",
       "      <td>9296</td>\n",
       "      <td>2005-08-08 11:57:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2</td>\n",
       "      <td>8230</td>\n",
       "      <td>2005-08-06 19:52:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2</td>\n",
       "      <td>9465</td>\n",
       "      <td>2005-08-06 16:43:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2</td>\n",
       "      <td>10466</td>\n",
       "      <td>2005-08-06 06:28:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2</td>\n",
       "      <td>9248</td>\n",
       "      <td>2005-08-05 11:19:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2</td>\n",
       "      <td>11256</td>\n",
       "      <td>2005-08-04 16:39:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>11367</td>\n",
       "      <td>2005-08-04 13:19:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2</td>\n",
       "      <td>9031</td>\n",
       "      <td>2005-08-04 10:45:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2</td>\n",
       "      <td>7376</td>\n",
       "      <td>2005-08-04 10:35:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2</td>\n",
       "      <td>10136</td>\n",
       "      <td>2005-08-03 19:44:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>9571</td>\n",
       "      <td>2005-08-02 23:26:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2</td>\n",
       "      <td>10918</td>\n",
       "      <td>2005-08-02 21:23:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2</td>\n",
       "      <td>7459</td>\n",
       "      <td>2005-08-02 21:07:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2</td>\n",
       "      <td>7346</td>\n",
       "      <td>2005-08-02 16:48:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2</td>\n",
       "      <td>8705</td>\n",
       "      <td>2005-08-02 16:01:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2</td>\n",
       "      <td>8598</td>\n",
       "      <td>2005-08-01 08:39:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1</td>\n",
       "      <td>8326</td>\n",
       "      <td>2005-08-01 05:16:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1</td>\n",
       "      <td>7273</td>\n",
       "      <td>2005-07-31 06:50:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1</td>\n",
       "      <td>8033</td>\n",
       "      <td>2005-07-30 17:56:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1</td>\n",
       "      <td>7841</td>\n",
       "      <td>2005-07-30 12:37:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1</td>\n",
       "      <td>8116</td>\n",
       "      <td>2005-07-29 22:54:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1</td>\n",
       "      <td>8074</td>\n",
       "      <td>2005-07-29 20:17:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2</td>\n",
       "      <td>5755</td>\n",
       "      <td>2005-07-19 17:02:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1</td>\n",
       "      <td>6163</td>\n",
       "      <td>2005-07-19 13:15:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1</td>\n",
       "      <td>5244</td>\n",
       "      <td>2005-07-14 14:01:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1</td>\n",
       "      <td>4526</td>\n",
       "      <td>2005-07-14 01:19:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1</td>\n",
       "      <td>5326</td>\n",
       "      <td>2005-07-13 18:02:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2</td>\n",
       "      <td>5636</td>\n",
       "      <td>2005-07-13 02:36:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1</td>\n",
       "      <td>4611</td>\n",
       "      <td>2005-07-12 13:25:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1</td>\n",
       "      <td>3284</td>\n",
       "      <td>2005-06-28 03:28:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1</td>\n",
       "      <td>1476</td>\n",
       "      <td>2005-06-25 02:26:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2</td>\n",
       "      <td>2128</td>\n",
       "      <td>2005-06-24 00:41:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1</td>\n",
       "      <td>1185</td>\n",
       "      <td>2005-06-23 02:42:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>1</td>\n",
       "      <td>2308</td>\n",
       "      <td>2005-06-22 03:36:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>1</td>\n",
       "      <td>2363</td>\n",
       "      <td>2005-06-19 17:40:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>1</td>\n",
       "      <td>1422</td>\n",
       "      <td>2005-06-19 15:54:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>1</td>\n",
       "      <td>1725</td>\n",
       "      <td>2005-06-17 21:05:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1</td>\n",
       "      <td>76</td>\n",
       "      <td>2005-06-03 12:00:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>1</td>\n",
       "      <td>573</td>\n",
       "      <td>2005-06-03 06:32:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2</td>\n",
       "      <td>320</td>\n",
       "      <td>2005-05-28 04:30:24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id  rental_id         return_date\n",
       "0             2      15145 2005-08-31 15:51:04\n",
       "1             1      15315 2005-08-30 01:51:46\n",
       "2             2      14743 2005-08-29 00:18:56\n",
       "3             1      15298 2005-08-28 22:49:37\n",
       "4             2      14475 2005-08-27 08:59:32\n",
       "5             1      14825 2005-08-27 07:01:57\n",
       "6             2      15907 2005-08-25 23:23:35\n",
       "7             2      12963 2005-08-23 11:37:04\n",
       "8             1      13176 2005-08-23 08:50:54\n",
       "9             1      14762 2005-08-23 01:30:57\n",
       "10            1      12250 2005-08-22 23:05:29\n",
       "11            1      13068 2005-08-20 14:44:16\n",
       "12            2      11614 2005-08-20 07:04:18\n",
       "13            1      11824 2005-08-19 10:11:54\n",
       "14            1      11299 2005-08-10 16:40:52\n",
       "15            1      10437 2005-08-10 12:12:04\n",
       "16            2      11177 2005-08-10 10:55:48\n",
       "17            2      11087 2005-08-10 10:37:41\n",
       "18            2       9236 2005-08-08 18:52:43\n",
       "19            2       9296 2005-08-08 11:57:13\n",
       "20            2       8230 2005-08-06 19:52:59\n",
       "21            2       9465 2005-08-06 16:43:53\n",
       "22            2      10466 2005-08-06 06:28:26\n",
       "23            2       9248 2005-08-05 11:19:11\n",
       "24            2      11256 2005-08-04 16:39:53\n",
       "25            1      11367 2005-08-04 13:19:38\n",
       "26            2       9031 2005-08-04 10:45:10\n",
       "27            2       7376 2005-08-04 10:35:02\n",
       "28            2      10136 2005-08-03 19:44:56\n",
       "29            1       9571 2005-08-02 23:26:18\n",
       "30            2      10918 2005-08-02 21:23:56\n",
       "31            2       7459 2005-08-02 21:07:20\n",
       "32            2       7346 2005-08-02 16:48:42\n",
       "33            2       8705 2005-08-02 16:01:29\n",
       "34            2       8598 2005-08-01 08:39:59\n",
       "35            1       8326 2005-08-01 05:16:49\n",
       "36            1       7273 2005-07-31 06:50:22\n",
       "37            1       8033 2005-07-30 17:56:23\n",
       "38            1       7841 2005-07-30 12:37:45\n",
       "39            1       8116 2005-07-29 22:54:07\n",
       "40            1       8074 2005-07-29 20:17:39\n",
       "41            2       5755 2005-07-19 17:02:56\n",
       "42            1       6163 2005-07-19 13:15:46\n",
       "43            1       5244 2005-07-14 14:01:07\n",
       "44            1       4526 2005-07-14 01:19:05\n",
       "45            1       5326 2005-07-13 18:02:01\n",
       "46            2       5636 2005-07-13 02:36:24\n",
       "47            1       4611 2005-07-12 13:25:56\n",
       "48            1       3284 2005-06-28 03:28:45\n",
       "49            1       1476 2005-06-25 02:26:46\n",
       "50            2       2128 2005-06-24 00:41:58\n",
       "51            1       1185 2005-06-23 02:42:12\n",
       "52            1       2308 2005-06-22 03:36:48\n",
       "53            1       2363 2005-06-19 17:40:59\n",
       "54            1       1422 2005-06-19 15:54:53\n",
       "55            1       1725 2005-06-17 21:05:57\n",
       "56            1         76 2005-06-03 12:00:37\n",
       "57            1        573 2005-06-03 06:32:23\n",
       "58            2        320 2005-05-28 04:30:24"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# conocer la información de alquiler de los ID de cliente 1 y 2\n",
    "\n",
    "sql = ''' \n",
    "SELECT customer_id, rental_id, return_date\n",
    "FROM rental\n",
    "WHERE customer_id IN (1, 2)\n",
    "ORDER BY return_date DESC;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>return_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>15145</td>\n",
       "      <td>2005-08-31 15:51:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>15315</td>\n",
       "      <td>2005-08-30 01:51:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>14743</td>\n",
       "      <td>2005-08-29 00:18:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>15298</td>\n",
       "      <td>2005-08-28 22:49:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>14475</td>\n",
       "      <td>2005-08-27 08:59:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>14825</td>\n",
       "      <td>2005-08-27 07:01:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>15907</td>\n",
       "      <td>2005-08-25 23:23:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>12963</td>\n",
       "      <td>2005-08-23 11:37:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>13176</td>\n",
       "      <td>2005-08-23 08:50:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>14762</td>\n",
       "      <td>2005-08-23 01:30:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>12250</td>\n",
       "      <td>2005-08-22 23:05:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>13068</td>\n",
       "      <td>2005-08-20 14:44:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2</td>\n",
       "      <td>11614</td>\n",
       "      <td>2005-08-20 07:04:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>11824</td>\n",
       "      <td>2005-08-19 10:11:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>11299</td>\n",
       "      <td>2005-08-10 16:40:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>10437</td>\n",
       "      <td>2005-08-10 12:12:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2</td>\n",
       "      <td>11177</td>\n",
       "      <td>2005-08-10 10:55:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2</td>\n",
       "      <td>11087</td>\n",
       "      <td>2005-08-10 10:37:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2</td>\n",
       "      <td>9236</td>\n",
       "      <td>2005-08-08 18:52:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2</td>\n",
       "      <td>9296</td>\n",
       "      <td>2005-08-08 11:57:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2</td>\n",
       "      <td>8230</td>\n",
       "      <td>2005-08-06 19:52:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2</td>\n",
       "      <td>9465</td>\n",
       "      <td>2005-08-06 16:43:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2</td>\n",
       "      <td>10466</td>\n",
       "      <td>2005-08-06 06:28:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2</td>\n",
       "      <td>9248</td>\n",
       "      <td>2005-08-05 11:19:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2</td>\n",
       "      <td>11256</td>\n",
       "      <td>2005-08-04 16:39:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>11367</td>\n",
       "      <td>2005-08-04 13:19:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2</td>\n",
       "      <td>9031</td>\n",
       "      <td>2005-08-04 10:45:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2</td>\n",
       "      <td>7376</td>\n",
       "      <td>2005-08-04 10:35:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2</td>\n",
       "      <td>10136</td>\n",
       "      <td>2005-08-03 19:44:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>9571</td>\n",
       "      <td>2005-08-02 23:26:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2</td>\n",
       "      <td>10918</td>\n",
       "      <td>2005-08-02 21:23:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2</td>\n",
       "      <td>7459</td>\n",
       "      <td>2005-08-02 21:07:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2</td>\n",
       "      <td>7346</td>\n",
       "      <td>2005-08-02 16:48:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2</td>\n",
       "      <td>8705</td>\n",
       "      <td>2005-08-02 16:01:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2</td>\n",
       "      <td>8598</td>\n",
       "      <td>2005-08-01 08:39:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1</td>\n",
       "      <td>8326</td>\n",
       "      <td>2005-08-01 05:16:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1</td>\n",
       "      <td>7273</td>\n",
       "      <td>2005-07-31 06:50:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1</td>\n",
       "      <td>8033</td>\n",
       "      <td>2005-07-30 17:56:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1</td>\n",
       "      <td>7841</td>\n",
       "      <td>2005-07-30 12:37:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1</td>\n",
       "      <td>8116</td>\n",
       "      <td>2005-07-29 22:54:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1</td>\n",
       "      <td>8074</td>\n",
       "      <td>2005-07-29 20:17:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2</td>\n",
       "      <td>5755</td>\n",
       "      <td>2005-07-19 17:02:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1</td>\n",
       "      <td>6163</td>\n",
       "      <td>2005-07-19 13:15:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1</td>\n",
       "      <td>5244</td>\n",
       "      <td>2005-07-14 14:01:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1</td>\n",
       "      <td>4526</td>\n",
       "      <td>2005-07-14 01:19:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1</td>\n",
       "      <td>5326</td>\n",
       "      <td>2005-07-13 18:02:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2</td>\n",
       "      <td>5636</td>\n",
       "      <td>2005-07-13 02:36:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1</td>\n",
       "      <td>4611</td>\n",
       "      <td>2005-07-12 13:25:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1</td>\n",
       "      <td>3284</td>\n",
       "      <td>2005-06-28 03:28:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1</td>\n",
       "      <td>1476</td>\n",
       "      <td>2005-06-25 02:26:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2</td>\n",
       "      <td>2128</td>\n",
       "      <td>2005-06-24 00:41:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1</td>\n",
       "      <td>1185</td>\n",
       "      <td>2005-06-23 02:42:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>1</td>\n",
       "      <td>2308</td>\n",
       "      <td>2005-06-22 03:36:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>1</td>\n",
       "      <td>2363</td>\n",
       "      <td>2005-06-19 17:40:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>1</td>\n",
       "      <td>1422</td>\n",
       "      <td>2005-06-19 15:54:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>1</td>\n",
       "      <td>1725</td>\n",
       "      <td>2005-06-17 21:05:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1</td>\n",
       "      <td>76</td>\n",
       "      <td>2005-06-03 12:00:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>1</td>\n",
       "      <td>573</td>\n",
       "      <td>2005-06-03 06:32:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2</td>\n",
       "      <td>320</td>\n",
       "      <td>2005-05-28 04:30:24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id  rental_id         return_date\n",
       "0             2      15145 2005-08-31 15:51:04\n",
       "1             1      15315 2005-08-30 01:51:46\n",
       "2             2      14743 2005-08-29 00:18:56\n",
       "3             1      15298 2005-08-28 22:49:37\n",
       "4             2      14475 2005-08-27 08:59:32\n",
       "5             1      14825 2005-08-27 07:01:57\n",
       "6             2      15907 2005-08-25 23:23:35\n",
       "7             2      12963 2005-08-23 11:37:04\n",
       "8             1      13176 2005-08-23 08:50:54\n",
       "9             1      14762 2005-08-23 01:30:57\n",
       "10            1      12250 2005-08-22 23:05:29\n",
       "11            1      13068 2005-08-20 14:44:16\n",
       "12            2      11614 2005-08-20 07:04:18\n",
       "13            1      11824 2005-08-19 10:11:54\n",
       "14            1      11299 2005-08-10 16:40:52\n",
       "15            1      10437 2005-08-10 12:12:04\n",
       "16            2      11177 2005-08-10 10:55:48\n",
       "17            2      11087 2005-08-10 10:37:41\n",
       "18            2       9236 2005-08-08 18:52:43\n",
       "19            2       9296 2005-08-08 11:57:13\n",
       "20            2       8230 2005-08-06 19:52:59\n",
       "21            2       9465 2005-08-06 16:43:53\n",
       "22            2      10466 2005-08-06 06:28:26\n",
       "23            2       9248 2005-08-05 11:19:11\n",
       "24            2      11256 2005-08-04 16:39:53\n",
       "25            1      11367 2005-08-04 13:19:38\n",
       "26            2       9031 2005-08-04 10:45:10\n",
       "27            2       7376 2005-08-04 10:35:02\n",
       "28            2      10136 2005-08-03 19:44:56\n",
       "29            1       9571 2005-08-02 23:26:18\n",
       "30            2      10918 2005-08-02 21:23:56\n",
       "31            2       7459 2005-08-02 21:07:20\n",
       "32            2       7346 2005-08-02 16:48:42\n",
       "33            2       8705 2005-08-02 16:01:29\n",
       "34            2       8598 2005-08-01 08:39:59\n",
       "35            1       8326 2005-08-01 05:16:49\n",
       "36            1       7273 2005-07-31 06:50:22\n",
       "37            1       8033 2005-07-30 17:56:23\n",
       "38            1       7841 2005-07-30 12:37:45\n",
       "39            1       8116 2005-07-29 22:54:07\n",
       "40            1       8074 2005-07-29 20:17:39\n",
       "41            2       5755 2005-07-19 17:02:56\n",
       "42            1       6163 2005-07-19 13:15:46\n",
       "43            1       5244 2005-07-14 14:01:07\n",
       "44            1       4526 2005-07-14 01:19:05\n",
       "45            1       5326 2005-07-13 18:02:01\n",
       "46            2       5636 2005-07-13 02:36:24\n",
       "47            1       4611 2005-07-12 13:25:56\n",
       "48            1       3284 2005-06-28 03:28:45\n",
       "49            1       1476 2005-06-25 02:26:46\n",
       "50            2       2128 2005-06-24 00:41:58\n",
       "51            1       1185 2005-06-23 02:42:12\n",
       "52            1       2308 2005-06-22 03:36:48\n",
       "53            1       2363 2005-06-19 17:40:59\n",
       "54            1       1422 2005-06-19 15:54:53\n",
       "55            1       1725 2005-06-17 21:05:57\n",
       "56            1         76 2005-06-03 12:00:37\n",
       "57            1        573 2005-06-03 06:32:23\n",
       "58            2        320 2005-05-28 04:30:24"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# conocer la información de alquiler de los ID de cliente 1 y 2\n",
    "# esta es equivalente a la anterior\n",
    "sql = ''' \n",
    "SELECT customer_id, rental_id, return_date\n",
    "FROM rental\n",
    "WHERE customer_id = 1 OR customer_id = 2\n",
    "ORDER BY return_date DESC;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>return_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>459</td>\n",
       "      <td>2</td>\n",
       "      <td>2005-05-28 19:40:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>408</td>\n",
       "      <td>3</td>\n",
       "      <td>2005-06-01 22:12:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>333</td>\n",
       "      <td>4</td>\n",
       "      <td>2005-06-03 01:43:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>5</td>\n",
       "      <td>2005-06-02 04:33:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>549</td>\n",
       "      <td>6</td>\n",
       "      <td>2005-05-27 01:32:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15980</th>\n",
       "      <td>74</td>\n",
       "      <td>16046</td>\n",
       "      <td>2005-08-27 18:02:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15981</th>\n",
       "      <td>114</td>\n",
       "      <td>16047</td>\n",
       "      <td>2005-08-25 02:48:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15982</th>\n",
       "      <td>103</td>\n",
       "      <td>16048</td>\n",
       "      <td>2005-08-31 21:33:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15983</th>\n",
       "      <td>393</td>\n",
       "      <td>16049</td>\n",
       "      <td>2005-08-30 01:01:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15984</th>\n",
       "      <td>130</td>\n",
       "      <td>1</td>\n",
       "      <td>2005-05-26 22:04:30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15985 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       customer_id  rental_id         return_date\n",
       "0              459          2 2005-05-28 19:40:33\n",
       "1              408          3 2005-06-01 22:12:39\n",
       "2              333          4 2005-06-03 01:43:41\n",
       "3              222          5 2005-06-02 04:33:21\n",
       "4              549          6 2005-05-27 01:32:07\n",
       "...            ...        ...                 ...\n",
       "15980           74      16046 2005-08-27 18:02:47\n",
       "15981          114      16047 2005-08-25 02:48:48\n",
       "15982          103      16048 2005-08-31 21:33:07\n",
       "15983          393      16049 2005-08-30 01:01:12\n",
       "15984          130          1 2005-05-26 22:04:30\n",
       "\n",
       "[15985 rows x 3 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se encuentra que todos los alquileres cuyo ID de cliente no es 1 o 2\n",
    "sql = ''' \n",
    "SELECT customer_id, rental_id, return_date\n",
    "FROM rental\n",
    "WHERE customer_id NOT IN (1, 2);\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>return_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>459</td>\n",
       "      <td>2</td>\n",
       "      <td>2005-05-28 19:40:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>408</td>\n",
       "      <td>3</td>\n",
       "      <td>2005-06-01 22:12:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>333</td>\n",
       "      <td>4</td>\n",
       "      <td>2005-06-03 01:43:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>222</td>\n",
       "      <td>5</td>\n",
       "      <td>2005-06-02 04:33:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>549</td>\n",
       "      <td>6</td>\n",
       "      <td>2005-05-27 01:32:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15980</th>\n",
       "      <td>74</td>\n",
       "      <td>16046</td>\n",
       "      <td>2005-08-27 18:02:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15981</th>\n",
       "      <td>114</td>\n",
       "      <td>16047</td>\n",
       "      <td>2005-08-25 02:48:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15982</th>\n",
       "      <td>103</td>\n",
       "      <td>16048</td>\n",
       "      <td>2005-08-31 21:33:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15983</th>\n",
       "      <td>393</td>\n",
       "      <td>16049</td>\n",
       "      <td>2005-08-30 01:01:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15984</th>\n",
       "      <td>130</td>\n",
       "      <td>1</td>\n",
       "      <td>2005-05-26 22:04:30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15985 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       customer_id  rental_id         return_date\n",
       "0              459          2 2005-05-28 19:40:33\n",
       "1              408          3 2005-06-01 22:12:39\n",
       "2              333          4 2005-06-03 01:43:41\n",
       "3              222          5 2005-06-02 04:33:21\n",
       "4              549          6 2005-05-27 01:32:07\n",
       "...            ...        ...                 ...\n",
       "15980           74      16046 2005-08-27 18:02:47\n",
       "15981          114      16047 2005-08-25 02:48:48\n",
       "15982          103      16048 2005-08-31 21:33:07\n",
       "15983          393      16049 2005-08-30 01:01:12\n",
       "15984          130          1 2005-05-26 22:04:30\n",
       "\n",
       "[15985 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Equivalente a la anterior\n",
    "sql = ''' \n",
    "SELECT customer_id, rental_id, return_date\n",
    "FROM rental\n",
    "WHERE customer_id <> 1 AND customer_id <> 2;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subconsultas con el operador IN\n",
    "\n",
    "Suponga que se desea consultar el nombre completo de las personas que tienen préstamos con fecha de devolución al 30 de mayo del 2005."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id\n",
       "0             5\n",
       "1            14\n",
       "2            16\n",
       "3            21\n",
       "4            23\n",
       "..          ...\n",
       "93          538\n",
       "94          554\n",
       "95          574\n",
       "96          575\n",
       "97          578\n",
       "\n",
       "[98 rows x 1 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Primero, consultamos lista de identificadores de clientes de la \n",
    "# tabla rental (prestamos) con la fecha de devolución 2005-05-27\n",
    "# Sin embargo, esta tabla no tiene los nombres de las personas\n",
    "\n",
    "sql = ''' \n",
    "SELECT customer_id\n",
    "FROM rental\n",
    "WHERE CAST(return_date AS DATE) = '2005-05-30'\n",
    "ORDER BY customer_id;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "      <th>full_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>Elizabeth Brown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>Betty White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>Sandra Martin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>Michelle Clark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>Sarah Lewis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>538</td>\n",
       "      <td>Ted Breaux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>554</td>\n",
       "      <td>Dwayne Olvera</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>574</td>\n",
       "      <td>Julian Vest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>575</td>\n",
       "      <td>Isaac Oglesby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>578</td>\n",
       "      <td>Willard Lumpkin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>94 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id        full_name\n",
       "0             5  Elizabeth Brown\n",
       "1            14      Betty White\n",
       "2            16    Sandra Martin\n",
       "3            21   Michelle Clark\n",
       "4            23      Sarah Lewis\n",
       "..          ...              ...\n",
       "89          538       Ted Breaux\n",
       "90          554    Dwayne Olvera\n",
       "91          574      Julian Vest\n",
       "92          575    Isaac Oglesby\n",
       "93          578  Willard Lumpkin\n",
       "\n",
       "[94 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Para obtener los nombres de las personas, la sentencia anterior se usa como subconsulta en IN...\n",
    "\n",
    "sql = ''' \n",
    "SELECT customer_id, first_name || ' ' || last_name as full_name\n",
    "FROM customer\n",
    "WHERE customer_id IN (\n",
    "                    SELECT customer_id\n",
    "                    FROM rental\n",
    "                    WHERE CAST (return_date AS DATE) = '2005-05-30'\n",
    ")\n",
    "ORDER BY customer_id;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "      <th>full_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>Elizabeth Brown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>Betty White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>Sandra Martin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>Michelle Clark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>Sarah Lewis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>538</td>\n",
       "      <td>Ted Breaux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>554</td>\n",
       "      <td>Dwayne Olvera</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>574</td>\n",
       "      <td>Julian Vest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>575</td>\n",
       "      <td>Isaac Oglesby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>578</td>\n",
       "      <td>Willard Lumpkin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id        full_name\n",
       "0             5  Elizabeth Brown\n",
       "1            14      Betty White\n",
       "2            16    Sandra Martin\n",
       "3            21   Michelle Clark\n",
       "4            23      Sarah Lewis\n",
       "..          ...              ...\n",
       "93          538       Ted Breaux\n",
       "94          554    Dwayne Olvera\n",
       "95          574      Julian Vest\n",
       "96          575    Isaac Oglesby\n",
       "97          578  Willard Lumpkin\n",
       "\n",
       "[98 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ¿Por qué la siguiente sentencia no entrega los mismos resultados?\n",
    "\n",
    "sql = ''' \n",
    "SELECT c.customer_id, c.first_name || ' ' || c.last_name as full_name\n",
    "FROM customer as c, ( SELECT customer_id\n",
    "                        FROM rental\n",
    "                        WHERE CAST (return_date AS DATE) = '2005-05-30'\n",
    "                     ) as ids\n",
    "WHERE ids.customer_id = c.customer_id\n",
    "ORDER BY customer_id;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>customer_id</th>\n",
       "      <th>full_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>Elizabeth Brown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>Betty White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>Sandra Martin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>Michelle Clark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>Sarah Lewis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>538</td>\n",
       "      <td>Ted Breaux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>554</td>\n",
       "      <td>Dwayne Olvera</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>574</td>\n",
       "      <td>Julian Vest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>575</td>\n",
       "      <td>Isaac Oglesby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>578</td>\n",
       "      <td>Willard Lumpkin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>94 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id        full_name\n",
       "0             5  Elizabeth Brown\n",
       "1            14      Betty White\n",
       "2            16    Sandra Martin\n",
       "3            21   Michelle Clark\n",
       "4            23      Sarah Lewis\n",
       "..          ...              ...\n",
       "89          538       Ted Breaux\n",
       "90          554    Dwayne Olvera\n",
       "91          574      Julian Vest\n",
       "92          575    Isaac Oglesby\n",
       "93          578  Willard Lumpkin\n",
       "\n",
       "[94 rows x 2 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sentencia equivalente a la operación IN\n",
    "\n",
    "sql = ''' \n",
    "SELECT c.customer_id, c.first_name || ' ' || c.last_name as full_name\n",
    "FROM customer as c, ( SELECT distinct customer_id\n",
    "                        FROM rental\n",
    "                        WHERE CAST (return_date AS DATE) = '2005-05-30'\n",
    "                     ) as ids\n",
    "WHERE ids.customer_id = c.customer_id\n",
    "ORDER BY customer_id;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Operadores LIKE y ILIKE\n",
    "\n",
    "Se usa para consultar datos usando coincidencias de patrones simples, mediante comodines (%) para buscar cualquier coincidencia con n cantidad de caracteres y guión bajo ( _ ) para un solo caracter.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albert</td>\n",
       "      <td>Crouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alberto</td>\n",
       "      <td>Henning</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name\n",
       "0     Albert    Crouse\n",
       "1    Alberto   Henning"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Buscar las coincidencias de los nombres que comiencen por Albert\n",
    "sql = ''' \n",
    "SELECT first_name, last_name\n",
    "FROM customer\n",
    "WHERE first_name LIKE 'Albert%'\n",
    "ORDER BY  first_name;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alberto</td>\n",
       "      <td>Henning</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name\n",
       "0    Alberto   Henning"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Buscar las coincidencias de los nombres que comiencen por Albert y finalicen con una letra\n",
    "sql = ''' \n",
    "SELECT first_name, last_name\n",
    "FROM customer\n",
    "WHERE first_name LIKE 'Albert_'\n",
    "ORDER BY  first_name;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cheryl</td>\n",
       "      <td>Murphy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sherri</td>\n",
       "      <td>Rhodes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sherry</td>\n",
       "      <td>Marshall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Theresa</td>\n",
       "      <td>Watson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name\n",
       "0     Cheryl    Murphy\n",
       "1     Sherri    Rhodes\n",
       "2     Sherry  Marshall\n",
       "3    Theresa    Watson"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Buscar las coincidencias de los nombres que comiencen por una letra\n",
    "# que contengan her y finalicen con ningun o varios caracteres\n",
    "\n",
    "sql = ''' \n",
    "SELECT first_name, last_name\n",
    "FROM customer\n",
    "WHERE first_name LIKE '_her%'\n",
    "ORDER BY  first_name;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Calvin</td>\n",
       "      <td>Martel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Carl</td>\n",
       "      <td>Artis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Carla</td>\n",
       "      <td>Gutierrez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carlos</td>\n",
       "      <td>Coughlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Carmen</td>\n",
       "      <td>Owens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>Willie</td>\n",
       "      <td>Markham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>519</th>\n",
       "      <td>Wilma</td>\n",
       "      <td>Richards</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>Yolanda</td>\n",
       "      <td>Weaver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521</th>\n",
       "      <td>Yvonne</td>\n",
       "      <td>Watkins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>Zachary</td>\n",
       "      <td>Hite</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>523 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    first_name  last_name\n",
       "0       Calvin     Martel\n",
       "1         Carl      Artis\n",
       "2        Carla  Gutierrez\n",
       "3       Carlos   Coughlin\n",
       "4       Carmen      Owens\n",
       "..         ...        ...\n",
       "518     Willie    Markham\n",
       "519      Wilma   Richards\n",
       "520    Yolanda     Weaver\n",
       "521     Yvonne    Watkins\n",
       "522    Zachary       Hite\n",
       "\n",
       "[523 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Encontrar los nombres que no comienzan por A o B\n",
    "sql = ''' \n",
    "SELECT first_name, last_name\n",
    "FROM customer\n",
    "WHERE first_name NOT LIKE 'A%' AND first_name NOT LIKE 'B%'\n",
    "ORDER BY  first_name\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Natalie</td>\n",
       "      <td>Meyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nathan</td>\n",
       "      <td>Runyon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Nathaniel</td>\n",
       "      <td>Adam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name\n",
       "0    Natalie     Meyer\n",
       "1     Nathan    Runyon\n",
       "2  Nathaniel      Adam"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ILIKE compara el valor sin distinción entre mayúsculas y minúsculas\n",
    "sql = ''' \n",
    "SELECT first_name, last_name\n",
    "FROM customer\n",
    "WHERE first_name ILIKE 'NAT%';\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Valores NULOS y Operador IS NULL\n",
    "\n",
    "NULL significa información faltante o no aplicable. NULL no es un valor, por lo tanto, no puede compararlo con otros valores como números o cadenas. La comparación de NULL con un valor siempre dará como resultado NULL, lo que significa un resultado desconocido.\n",
    "\n",
    "* NULL no es igual a NULL, esta operación siempre será NULL\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1c4349842e0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = '''\n",
    "CREATE TABLE contactos(\n",
    "    id INT GENERATED BY DEFAULT AS IDENTITY,\n",
    "    nombre VARCHAR(50) NOT NULL,\n",
    "    apellido VARCHAR(50) NOT NULL,\n",
    "    correo VARCHAR(255) NOT NULL,\n",
    "    telefono VARCHAR(15),\n",
    "    PRIMARY KEY (id)\n",
    ");\n",
    "'''\n",
    "\n",
    "cnx.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1c43170a700>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = '''\n",
    "INSERT INTO contactos(nombre, apellido, correo, telefono)\n",
    "    VALUES  ('Natalia','Rosas','natalia.rosas@rebrand.com', NULL),\n",
    "            ('Lilana','Posada','lily.pos@gmail.com','305 234 27 64'),\n",
    "            ('Lucio','Vasquez','lvasquez@gmail.com','305 234 34 65');\n",
    "'''\n",
    "\n",
    "cnx.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>id</th>\n",
       "      <th>nombre</th>\n",
       "      <th>apellido</th>\n",
       "      <th>correo</th>\n",
       "      <th>telefono</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id, nombre, apellido, correo, telefono]\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cuáles son los contactos que no tienen número de teléfono\n",
    "# No se retorna ninguna fila\n",
    "sql = ''' \n",
    "SELECT *\n",
    "FROM contactos\n",
    "WHERE telefono = NULL;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>id</th>\n",
       "      <th>nombre</th>\n",
       "      <th>apellido</th>\n",
       "      <th>correo</th>\n",
       "      <th>telefono</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Natalia</td>\n",
       "      <td>Rosas</td>\n",
       "      <td>natalia.rosas@rebrand.com</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id   nombre apellido                     correo telefono\n",
       "0   1  Natalia    Rosas  natalia.rosas@rebrand.com     None"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cuáles son los contactos que no tienen número de teléfono\n",
    "# No se retorna ninguna fila\n",
    "sql = ''' \n",
    "SELECT *\n",
    "FROM contactos\n",
    "WHERE telefono is NULL;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>id</th>\n",
       "      <th>nombre</th>\n",
       "      <th>apellido</th>\n",
       "      <th>correo</th>\n",
       "      <th>telefono</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Lilana</td>\n",
       "      <td>Posada</td>\n",
       "      <td>lily.pos@gmail.com</td>\n",
       "      <td>305 234 27 64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Lucio</td>\n",
       "      <td>Vasquez</td>\n",
       "      <td>lvasquez@gmail.com</td>\n",
       "      <td>305 234 34 65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  nombre apellido              correo       telefono\n",
       "0   2  Lilana   Posada  lily.pos@gmail.com  305 234 27 64\n",
       "1   3   Lucio  Vasquez  lvasquez@gmail.com  305 234 34 65"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cuáles son los contactos que tienen número de teléfono\n",
    "# No se retorna ninguna fila\n",
    "sql = ''' \n",
    "SELECT *\n",
    "FROM contactos\n",
    "WHERE telefono IS NOT NULL;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1c43172fb20>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = '''\n",
    "DROP TABLE contactos;\n",
    "'''\n",
    "\n",
    "cnx.execute(sql)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}