1. Basic Statements.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basic Statements\n",
    "\n",
    "# Database\n",
    "\n",
    "From this notebook up to the third one, we make use of the dvdrental database. This database contains the records of a DVD rental store. There are 15 tables in this database, here are some of them are descriptions:\n",
    "\n",
    "* _actor_: Stores actors data including fileds such as first name and last name.\n",
    "* _ film _: Stores films data including fields such as title, release date, etc.\n",
    "* _payment_: Stores the records of the customer’s payments.\n",
    "* _customer_: Sotres information about the customers, such as first name, last name, email, etc.\n",
    "\n",
    "In this notebook we study some use case examples of the most simple SQL statments. We use Python as our main programming language and make use of libraries such as Pandas ans Psycopg2 to present the results of the queries.\n",
    "\n",
    "First, we call the pandas and psycopg2 libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg2 as pg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to create a connection object, which will allow us to comunicate with the data base."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = pg2.connect(database = 'dvdrental', user = 'postgres', password = 'password')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to present the results in a clearer manner we define the next function, whichs makes use of the connection object. This function receives the SQL query and returns the result in a pandas DataFrame object. We can also especify, via the rows argument, the number of rows to be returned, by default the function returns ten rows. Given that this notebook is just for demostrative purposes, we've decide to display a little sample of the tables to avoid memory issues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_data(query, rows = 10):\n",
    "\n",
    "    with connection.cursor() as cursor:\n",
    "        cursor. execute(query)\n",
    "\n",
    "        if rows == 'all':\n",
    "            raw_data = cursor.fetchall()\n",
    "        else:\n",
    "            raw_data = cursor.fetchmany(rows) \n",
    "\n",
    "        col_names = [col_desc[0] for col_desc in cursor.description]\n",
    "        data = pd.DataFrame(raw_data, columns = col_names)\n",
    "\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT Statement\n",
    "\n",
    "## Ex. 1\n",
    "\n",
    "As our first example, we'll ask for the actor table form the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>actor_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Penelope</td>\n",
       "      <td>Guiness</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Nick</td>\n",
       "      <td>Wahlberg</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Ed</td>\n",
       "      <td>Chase</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Davis</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Johnny</td>\n",
       "      <td>Lollobrigida</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Bette</td>\n",
       "      <td>Nicholson</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Grace</td>\n",
       "      <td>Mostel</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Matthew</td>\n",
       "      <td>Johansson</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Joe</td>\n",
       "      <td>Swank</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Christian</td>\n",
       "      <td>Gable</td>\n",
       "      <td>2013-05-26 14:47:57.620</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   actor_id first_name     last_name             last_update\n",
       "0         1   Penelope       Guiness 2013-05-26 14:47:57.620\n",
       "1         2       Nick      Wahlberg 2013-05-26 14:47:57.620\n",
       "2         3         Ed         Chase 2013-05-26 14:47:57.620\n",
       "3         4   Jennifer         Davis 2013-05-26 14:47:57.620\n",
       "4         5     Johnny  Lollobrigida 2013-05-26 14:47:57.620\n",
       "5         6      Bette     Nicholson 2013-05-26 14:47:57.620\n",
       "6         7      Grace        Mostel 2013-05-26 14:47:57.620\n",
       "7         8    Matthew     Johansson 2013-05-26 14:47:57.620\n",
       "8         9        Joe         Swank 2013-05-26 14:47:57.620\n",
       "9        10  Christian         Gable 2013-05-26 14:47:57.620"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_1 = 'SELECT * FROM actor'\n",
    "actor_table = get_data(query_1)\n",
    "\n",
    "actor_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 2\n",
    "\n",
    "We can grab only a subset of columns out of the table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>actor_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Penelope</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nick</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ed</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jennifer</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Johnny</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Bette</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grace</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Matthew</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Joe</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Christian</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name  actor_id\n",
       "0   Penelope         1\n",
       "1       Nick         2\n",
       "2         Ed         3\n",
       "3   Jennifer         4\n",
       "4     Johnny         5\n",
       "5      Bette         6\n",
       "6      Grace         7\n",
       "7    Matthew         8\n",
       "8        Joe         9\n",
       "9  Christian        10"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_2 = 'SELECT first_name, actor_id FROM actor'\n",
    "two_columns = get_data(query_2)\n",
    "\n",
    "two_columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DISTINCT \n",
    "\n",
    "## Ex 3.\n",
    "\n",
    "It may be the case that we wanted to list only the distinct vlaues in a certain column, we can use the DISTINCT keyword to fullfil this task. Suppose we wanted to know the different category_ids of the movies that are in the film database, to respond to this question we can use the following code "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>category_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   category_id\n",
       "0            4\n",
       "1           14\n",
       "2            3\n",
       "3           10\n",
       "4            7\n",
       "5           13\n",
       "6            9\n",
       "7            1\n",
       "8            5\n",
       "9            2"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_3 = 'SELECT DISTINCT(category_id) FROM film_category'\n",
    "movie_categories = get_data(query_3)\n",
    "\n",
    "movie_categories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COUNT Function \n",
    "\n",
    "The count function simply returns back the number of rows in a table.\n",
    "\n",
    "## Ex. 4\n",
    "\n",
    "Using the COUNT function we can calculate how many movies are in our database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0   1000"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_4 = 'SELECT COUNT(film_id) FROM film'\n",
    "amount_movies = get_data(query_4)\n",
    "\n",
    "amount_movies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 5 \n",
    "\n",
    "Now we can not only know the distinct movie category_ids, but also calculate the amount the number of categories with a simple query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0     16"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_5 = 'SELECT COUNT(DISTINCT(category_id)) FROM film_category'\n",
    "amount_categories = get_data(query_5)\n",
    "\n",
    "amount_categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# WHERE Statement\n",
    "\n",
    "The WHERE statemenr allows us tu specify conditions on the data to be returned.\n",
    "\n",
    "## Ex. 6\n",
    "\n",
    "For example, we could be interested in getting the information contained in the cutomer table but only for the active customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>524</td>\n",
       "      <td>1</td>\n",
       "      <td>Jared</td>\n",
       "      <td>Ely</td>\n",
       "      <td>jared.ely@sakilacustomer.org</td>\n",
       "      <td>530</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mary</td>\n",
       "      <td>Smith</td>\n",
       "      <td>mary.smith@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>patricia.johnson@sakilacustomer.org</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Linda</td>\n",
       "      <td>Williams</td>\n",
       "      <td>linda.williams@sakilacustomer.org</td>\n",
       "      <td>7</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Barbara</td>\n",
       "      <td>Jones</td>\n",
       "      <td>barbara.jones@sakilacustomer.org</td>\n",
       "      <td>8</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Elizabeth</td>\n",
       "      <td>Brown</td>\n",
       "      <td>elizabeth.brown@sakilacustomer.org</td>\n",
       "      <td>9</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Davis</td>\n",
       "      <td>jennifer.davis@sakilacustomer.org</td>\n",
       "      <td>10</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Miller</td>\n",
       "      <td>maria.miller@sakilacustomer.org</td>\n",
       "      <td>11</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>Susan</td>\n",
       "      <td>Wilson</td>\n",
       "      <td>susan.wilson@sakilacustomer.org</td>\n",
       "      <td>12</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Moore</td>\n",
       "      <td>margaret.moore@sakilacustomer.org</td>\n",
       "      <td>13</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0          524         1      Jared       Ely   \n",
       "1            1         1       Mary     Smith   \n",
       "2            2         1   Patricia   Johnson   \n",
       "3            3         1      Linda  Williams   \n",
       "4            4         2    Barbara     Jones   \n",
       "5            5         1  Elizabeth     Brown   \n",
       "6            6         2   Jennifer     Davis   \n",
       "7            7         1      Maria    Miller   \n",
       "8            8         2      Susan    Wilson   \n",
       "9            9         2   Margaret     Moore   \n",
       "\n",
       "                                 email  address_id  activebool create_date  \\\n",
       "0         jared.ely@sakilacustomer.org         530        True  2006-02-14   \n",
       "1        mary.smith@sakilacustomer.org           5        True  2006-02-14   \n",
       "2  patricia.johnson@sakilacustomer.org           6        True  2006-02-14   \n",
       "3    linda.williams@sakilacustomer.org           7        True  2006-02-14   \n",
       "4     barbara.jones@sakilacustomer.org           8        True  2006-02-14   \n",
       "5   elizabeth.brown@sakilacustomer.org           9        True  2006-02-14   \n",
       "6    jennifer.davis@sakilacustomer.org          10        True  2006-02-14   \n",
       "7      maria.miller@sakilacustomer.org          11        True  2006-02-14   \n",
       "8      susan.wilson@sakilacustomer.org          12        True  2006-02-14   \n",
       "9    margaret.moore@sakilacustomer.org          13        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  \n",
       "3 2013-05-26 14:49:45.738       1  \n",
       "4 2013-05-26 14:49:45.738       1  \n",
       "5 2013-05-26 14:49:45.738       1  \n",
       "6 2013-05-26 14:49:45.738       1  \n",
       "7 2013-05-26 14:49:45.738       1  \n",
       "8 2013-05-26 14:49:45.738       1  \n",
       "9 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_6 = '''\n",
    "          SELECT * FROM customer\n",
    "          WHERE active = 1\n",
    "          '''\n",
    "\n",
    "active_costumers = get_data(query_6)\n",
    "active_costumers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 7\n",
    "We can also count the number of active costumers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0    584"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_7 = '''\n",
    "          SELECT COUNT(*) FROM customer\n",
    "          WHERE active = 1\n",
    "          '''\n",
    "\n",
    "num_active_costumers = get_data(query_7)\n",
    "num_active_costumers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 8\n",
    "\n",
    "Using the logical opetators AND, OR and NOT we can impose several conditions on the data to be returned. For example, we could ask for the number of active costumers whose name is Nancy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0      1"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_8 = '''\n",
    "          SELECT COUNT(*) FROM customer\n",
    "          WHERE active = 1 and first_name = 'Nancy'\n",
    "          '''\n",
    "\n",
    "custom_query = get_data(query_8)\n",
    "custom_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ORDER BY Statement\n",
    "\n",
    "With the ORDER BY statement we can sort the data based on a column value in either ascending or descending order.\n",
    "\n",
    "## Ex. 9\n",
    "\n",
    "For example, we can order the customer table and order by the first_name in ascending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>375</td>\n",
       "      <td>2</td>\n",
       "      <td>Aaron</td>\n",
       "      <td>Selby</td>\n",
       "      <td>aaron.selby@sakilacustomer.org</td>\n",
       "      <td>380</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>367</td>\n",
       "      <td>1</td>\n",
       "      <td>Adam</td>\n",
       "      <td>Gooch</td>\n",
       "      <td>adam.gooch@sakilacustomer.org</td>\n",
       "      <td>372</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>525</td>\n",
       "      <td>2</td>\n",
       "      <td>Adrian</td>\n",
       "      <td>Clary</td>\n",
       "      <td>adrian.clary@sakilacustomer.org</td>\n",
       "      <td>531</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>217</td>\n",
       "      <td>2</td>\n",
       "      <td>Agnes</td>\n",
       "      <td>Bishop</td>\n",
       "      <td>agnes.bishop@sakilacustomer.org</td>\n",
       "      <td>221</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>389</td>\n",
       "      <td>1</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Kahn</td>\n",
       "      <td>alan.kahn@sakilacustomer.org</td>\n",
       "      <td>394</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>352</td>\n",
       "      <td>1</td>\n",
       "      <td>Albert</td>\n",
       "      <td>Crouse</td>\n",
       "      <td>albert.crouse@sakilacustomer.org</td>\n",
       "      <td>357</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>568</td>\n",
       "      <td>2</td>\n",
       "      <td>Alberto</td>\n",
       "      <td>Henning</td>\n",
       "      <td>alberto.henning@sakilacustomer.org</td>\n",
       "      <td>574</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>454</td>\n",
       "      <td>2</td>\n",
       "      <td>Alex</td>\n",
       "      <td>Gresham</td>\n",
       "      <td>alex.gresham@sakilacustomer.org</td>\n",
       "      <td>459</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>439</td>\n",
       "      <td>2</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Fennell</td>\n",
       "      <td>alexander.fennell@sakilacustomer.org</td>\n",
       "      <td>444</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>423</td>\n",
       "      <td>2</td>\n",
       "      <td>Alfred</td>\n",
       "      <td>Casillas</td>\n",
       "      <td>alfred.casillas@sakilacustomer.org</td>\n",
       "      <td>428</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0          375         2      Aaron     Selby   \n",
       "1          367         1       Adam     Gooch   \n",
       "2          525         2     Adrian     Clary   \n",
       "3          217         2      Agnes    Bishop   \n",
       "4          389         1       Alan      Kahn   \n",
       "5          352         1     Albert    Crouse   \n",
       "6          568         2    Alberto   Henning   \n",
       "7          454         2       Alex   Gresham   \n",
       "8          439         2  Alexander   Fennell   \n",
       "9          423         2     Alfred  Casillas   \n",
       "\n",
       "                                  email  address_id  activebool create_date  \\\n",
       "0        aaron.selby@sakilacustomer.org         380        True  2006-02-14   \n",
       "1         adam.gooch@sakilacustomer.org         372        True  2006-02-14   \n",
       "2       adrian.clary@sakilacustomer.org         531        True  2006-02-14   \n",
       "3       agnes.bishop@sakilacustomer.org         221        True  2006-02-14   \n",
       "4          alan.kahn@sakilacustomer.org         394        True  2006-02-14   \n",
       "5      albert.crouse@sakilacustomer.org         357        True  2006-02-14   \n",
       "6    alberto.henning@sakilacustomer.org         574        True  2006-02-14   \n",
       "7       alex.gresham@sakilacustomer.org         459        True  2006-02-14   \n",
       "8  alexander.fennell@sakilacustomer.org         444        True  2006-02-14   \n",
       "9    alfred.casillas@sakilacustomer.org         428        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  \n",
       "3 2013-05-26 14:49:45.738       1  \n",
       "4 2013-05-26 14:49:45.738       1  \n",
       "5 2013-05-26 14:49:45.738       1  \n",
       "6 2013-05-26 14:49:45.738       1  \n",
       "7 2013-05-26 14:49:45.738       1  \n",
       "8 2013-05-26 14:49:45.738       1  \n",
       "9 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_9 = '''\n",
    "          SELECT * FROM customer\n",
    "          ORDER BY first_name ASC\n",
    "          '''\n",
    "\n",
    "custom_query = get_data(query_9)\n",
    "custom_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ex. 10\n",
    "\n",
    "Another thing we can do es sort the data based on multiple columns. Forv example, let's say that we want to sort the customer table based on the store_id in descending order and then the first_name in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>375</td>\n",
       "      <td>2</td>\n",
       "      <td>Aaron</td>\n",
       "      <td>Selby</td>\n",
       "      <td>aaron.selby@sakilacustomer.org</td>\n",
       "      <td>380</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>525</td>\n",
       "      <td>2</td>\n",
       "      <td>Adrian</td>\n",
       "      <td>Clary</td>\n",
       "      <td>adrian.clary@sakilacustomer.org</td>\n",
       "      <td>531</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>217</td>\n",
       "      <td>2</td>\n",
       "      <td>Agnes</td>\n",
       "      <td>Bishop</td>\n",
       "      <td>agnes.bishop@sakilacustomer.org</td>\n",
       "      <td>221</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>568</td>\n",
       "      <td>2</td>\n",
       "      <td>Alberto</td>\n",
       "      <td>Henning</td>\n",
       "      <td>alberto.henning@sakilacustomer.org</td>\n",
       "      <td>574</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>454</td>\n",
       "      <td>2</td>\n",
       "      <td>Alex</td>\n",
       "      <td>Gresham</td>\n",
       "      <td>alex.gresham@sakilacustomer.org</td>\n",
       "      <td>459</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>439</td>\n",
       "      <td>2</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Fennell</td>\n",
       "      <td>alexander.fennell@sakilacustomer.org</td>\n",
       "      <td>444</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>423</td>\n",
       "      <td>2</td>\n",
       "      <td>Alfred</td>\n",
       "      <td>Casillas</td>\n",
       "      <td>alfred.casillas@sakilacustomer.org</td>\n",
       "      <td>428</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>567</td>\n",
       "      <td>2</td>\n",
       "      <td>Alfredo</td>\n",
       "      <td>Mcadams</td>\n",
       "      <td>alfredo.mcadams@sakilacustomer.org</td>\n",
       "      <td>573</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>412</td>\n",
       "      <td>2</td>\n",
       "      <td>Allen</td>\n",
       "      <td>Butterfield</td>\n",
       "      <td>allen.butterfield@sakilacustomer.org</td>\n",
       "      <td>417</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>228</td>\n",
       "      <td>2</td>\n",
       "      <td>Allison</td>\n",
       "      <td>Stanley</td>\n",
       "      <td>allison.stanley@sakilacustomer.org</td>\n",
       "      <td>232</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name    last_name  \\\n",
       "0          375         2      Aaron        Selby   \n",
       "1          525         2     Adrian        Clary   \n",
       "2          217         2      Agnes       Bishop   \n",
       "3          568         2    Alberto      Henning   \n",
       "4          454         2       Alex      Gresham   \n",
       "5          439         2  Alexander      Fennell   \n",
       "6          423         2     Alfred     Casillas   \n",
       "7          567         2    Alfredo      Mcadams   \n",
       "8          412         2      Allen  Butterfield   \n",
       "9          228         2    Allison      Stanley   \n",
       "\n",
       "                                  email  address_id  activebool create_date  \\\n",
       "0        aaron.selby@sakilacustomer.org         380        True  2006-02-14   \n",
       "1       adrian.clary@sakilacustomer.org         531        True  2006-02-14   \n",
       "2       agnes.bishop@sakilacustomer.org         221        True  2006-02-14   \n",
       "3    alberto.henning@sakilacustomer.org         574        True  2006-02-14   \n",
       "4       alex.gresham@sakilacustomer.org         459        True  2006-02-14   \n",
       "5  alexander.fennell@sakilacustomer.org         444        True  2006-02-14   \n",
       "6    alfred.casillas@sakilacustomer.org         428        True  2006-02-14   \n",
       "7    alfredo.mcadams@sakilacustomer.org         573        True  2006-02-14   \n",
       "8  allen.butterfield@sakilacustomer.org         417        True  2006-02-14   \n",
       "9    allison.stanley@sakilacustomer.org         232        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  \n",
       "3 2013-05-26 14:49:45.738       1  \n",
       "4 2013-05-26 14:49:45.738       1  \n",
       "5 2013-05-26 14:49:45.738       1  \n",
       "6 2013-05-26 14:49:45.738       1  \n",
       "7 2013-05-26 14:49:45.738       1  \n",
       "8 2013-05-26 14:49:45.738       1  \n",
       "9 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_10 = '''\n",
    "          SELECT * FROM customer\n",
    "          ORDER BY store_id DESC, first_name ASC \n",
    "          '''\n",
    "\n",
    "custom_query = get_data(query_10)\n",
    "custom_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LIMIT Statement\n",
    "\n",
    "The limit statement is used to restricy the number of rows retunred by a query. The limit value defines the number of top rows to be returned.\n",
    "\n",
    "## Ex. 11 \n",
    "\n",
    "For example, we can choose to only ask for the first five rows in the costumer table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>524</td>\n",
       "      <td>1</td>\n",
       "      <td>Jared</td>\n",
       "      <td>Ely</td>\n",
       "      <td>jared.ely@sakilacustomer.org</td>\n",
       "      <td>530</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mary</td>\n",
       "      <td>Smith</td>\n",
       "      <td>mary.smith@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>patricia.johnson@sakilacustomer.org</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Linda</td>\n",
       "      <td>Williams</td>\n",
       "      <td>linda.williams@sakilacustomer.org</td>\n",
       "      <td>7</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Barbara</td>\n",
       "      <td>Jones</td>\n",
       "      <td>barbara.jones@sakilacustomer.org</td>\n",
       "      <td>8</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0          524         1      Jared       Ely   \n",
       "1            1         1       Mary     Smith   \n",
       "2            2         1   Patricia   Johnson   \n",
       "3            3         1      Linda  Williams   \n",
       "4            4         2    Barbara     Jones   \n",
       "\n",
       "                                 email  address_id  activebool create_date  \\\n",
       "0         jared.ely@sakilacustomer.org         530        True  2006-02-14   \n",
       "1        mary.smith@sakilacustomer.org           5        True  2006-02-14   \n",
       "2  patricia.johnson@sakilacustomer.org           6        True  2006-02-14   \n",
       "3    linda.williams@sakilacustomer.org           7        True  2006-02-14   \n",
       "4     barbara.jones@sakilacustomer.org           8        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  \n",
       "3 2013-05-26 14:49:45.738       1  \n",
       "4 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_11 = '''\n",
    "          SELECT * FROM customer\n",
    "          LIMIT 5\n",
    "          '''\n",
    "\n",
    "first_five = get_data(query_11)\n",
    "first_five"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 12 \n",
    "\n",
    "We can use the LIMIT statement in combination with the ORDER BY statement, to ask, for example, for the five most recent purchases un the paymenyt table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>payment_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>staff_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>payment_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31920</td>\n",
       "      <td>269</td>\n",
       "      <td>2</td>\n",
       "      <td>12610</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31917</td>\n",
       "      <td>267</td>\n",
       "      <td>2</td>\n",
       "      <td>12066</td>\n",
       "      <td>7.98</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31918</td>\n",
       "      <td>267</td>\n",
       "      <td>2</td>\n",
       "      <td>13713</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31919</td>\n",
       "      <td>269</td>\n",
       "      <td>1</td>\n",
       "      <td>13025</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31921</td>\n",
       "      <td>274</td>\n",
       "      <td>1</td>\n",
       "      <td>13486</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payment_id  customer_id  staff_id  rental_id amount  \\\n",
       "0       31920          269         2      12610   0.00   \n",
       "1       31917          267         2      12066   7.98   \n",
       "2       31918          267         2      13713   0.00   \n",
       "3       31919          269         1      13025   3.98   \n",
       "4       31921          274         1      13486   0.99   \n",
       "\n",
       "                payment_date  \n",
       "0 2007-05-14 13:44:29.996577  \n",
       "1 2007-05-14 13:44:29.996577  \n",
       "2 2007-05-14 13:44:29.996577  \n",
       "3 2007-05-14 13:44:29.996577  \n",
       "4 2007-05-14 13:44:29.996577  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_12 = '''\n",
    "          SELECT * FROM payment\n",
    "          ORDER BY payment_date DESC  \n",
    "          LIMIT 5\n",
    "          '''\n",
    "\n",
    "recent_pur = get_data(query_12)\n",
    "recent_pur"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 13\n",
    "\n",
    "We can also ask for for the five most recent purchases whose amount is different from zero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>payment_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>staff_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>payment_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31922</td>\n",
       "      <td>279</td>\n",
       "      <td>2</td>\n",
       "      <td>13538</td>\n",
       "      <td>4.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31917</td>\n",
       "      <td>267</td>\n",
       "      <td>2</td>\n",
       "      <td>12066</td>\n",
       "      <td>7.98</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31919</td>\n",
       "      <td>269</td>\n",
       "      <td>1</td>\n",
       "      <td>13025</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31921</td>\n",
       "      <td>274</td>\n",
       "      <td>1</td>\n",
       "      <td>13486</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31923</td>\n",
       "      <td>282</td>\n",
       "      <td>2</td>\n",
       "      <td>15430</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2007-05-14 13:44:29.996577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payment_id  customer_id  staff_id  rental_id amount  \\\n",
       "0       31922          279         2      13538   4.99   \n",
       "1       31917          267         2      12066   7.98   \n",
       "2       31919          269         1      13025   3.98   \n",
       "3       31921          274         1      13486   0.99   \n",
       "4       31923          282         2      15430   0.99   \n",
       "\n",
       "                payment_date  \n",
       "0 2007-05-14 13:44:29.996577  \n",
       "1 2007-05-14 13:44:29.996577  \n",
       "2 2007-05-14 13:44:29.996577  \n",
       "3 2007-05-14 13:44:29.996577  \n",
       "4 2007-05-14 13:44:29.996577  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_13 = '''\n",
    "          SELECT * FROM payment\n",
    "          WHERE amount != 0\n",
    "          ORDER BY payment_date DESC  \n",
    "          LIMIT 5\n",
    "          '''\n",
    "\n",
    "recent_pur = get_data(query_13)\n",
    "recent_pur"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BETWEEN Statement\n",
    "\n",
    "We use the BETWEEN statement to select rows form teh data whose value on a column lies between a given range.\n",
    "\n",
    "Ex. 14\n",
    "\n",
    "Using the BETWEEN statement we can find out what payments were made between a certain range, let's say 5\\$ and 8\\$.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>payment_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>staff_id</th>\n",
       "      <th>rental_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>payment_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17503</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>1520</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-15 22:25:46.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17505</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>1849</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-16 22:41:45.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17507</td>\n",
       "      <td>341</td>\n",
       "      <td>2</td>\n",
       "      <td>3130</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-20 17:31:48.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17508</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>3382</td>\n",
       "      <td>5.99</td>\n",
       "      <td>2007-02-21 12:33:49.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17509</td>\n",
       "      <td>342</td>\n",
       "      <td>2</td>\n",
       "      <td>2190</td>\n",
       "      <td>5.99</td>\n",
       "      <td>2007-02-17 23:58:17.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17510</td>\n",
       "      <td>342</td>\n",
       "      <td>1</td>\n",
       "      <td>2914</td>\n",
       "      <td>5.99</td>\n",
       "      <td>2007-02-20 02:11:44.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>17513</td>\n",
       "      <td>343</td>\n",
       "      <td>1</td>\n",
       "      <td>1564</td>\n",
       "      <td>6.99</td>\n",
       "      <td>2007-02-16 01:15:33.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17516</td>\n",
       "      <td>343</td>\n",
       "      <td>2</td>\n",
       "      <td>2461</td>\n",
       "      <td>6.99</td>\n",
       "      <td>2007-02-18 18:26:38.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>17526</td>\n",
       "      <td>346</td>\n",
       "      <td>1</td>\n",
       "      <td>1994</td>\n",
       "      <td>5.99</td>\n",
       "      <td>2007-02-17 09:35:32.996577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>17533</td>\n",
       "      <td>347</td>\n",
       "      <td>1</td>\n",
       "      <td>3326</td>\n",
       "      <td>7.99</td>\n",
       "      <td>2007-02-21 07:33:16.996577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payment_id  customer_id  staff_id  rental_id amount  \\\n",
       "0       17503          341         2       1520   7.99   \n",
       "1       17505          341         1       1849   7.99   \n",
       "2       17507          341         2       3130   7.99   \n",
       "3       17508          341         1       3382   5.99   \n",
       "4       17509          342         2       2190   5.99   \n",
       "5       17510          342         1       2914   5.99   \n",
       "6       17513          343         1       1564   6.99   \n",
       "7       17516          343         2       2461   6.99   \n",
       "8       17526          346         1       1994   5.99   \n",
       "9       17533          347         1       3326   7.99   \n",
       "\n",
       "                payment_date  \n",
       "0 2007-02-15 22:25:46.996577  \n",
       "1 2007-02-16 22:41:45.996577  \n",
       "2 2007-02-20 17:31:48.996577  \n",
       "3 2007-02-21 12:33:49.996577  \n",
       "4 2007-02-17 23:58:17.996577  \n",
       "5 2007-02-20 02:11:44.996577  \n",
       "6 2007-02-16 01:15:33.996577  \n",
       "7 2007-02-18 18:26:38.996577  \n",
       "8 2007-02-17 09:35:32.996577  \n",
       "9 2007-02-21 07:33:16.996577  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_14 = '''\n",
    "          SELECT * FROM payment\n",
    "          WHERE amount BETWEEN 5 AND 8\n",
    "          '''\n",
    "\n",
    "payments = get_data(query_14)\n",
    "payments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 15\n",
    "\n",
    "If we want to know the total nomber of these transactions we can do it with the next query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2838</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0   2838"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_15 = '''\n",
    "          SELECT COUNT(*) FROM payment\n",
    "          WHERE amount BETWEEN 5 AND 8\n",
    "          '''\n",
    "\n",
    "payments = get_data(query_15)\n",
    "payments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ex. 16\n",
    "\n",
    "We can laso deny the BETWEEN statement. Let's ask for the amount of payments that were not made within the 5\\$ and 8\\$ range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11758</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0  11758"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_16 = '''\n",
    "          SELECT COUNT(*) FROM payment\n",
    "          WHERE amount NOT BETWEEN 5 AND 8\n",
    "          '''\n",
    "\n",
    "payments = get_data(query_16)\n",
    "payments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IN Statement\n",
    "\n",
    "The IN statement allows us to grab form a table only the rows whose value on a column matches any value in a list of multiple options.\n",
    "\n",
    "## Ex. 17 \n",
    "\n",
    "For example, we can select form the customer table only the rows where the first_name is John, Nancy or Peter.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>Nancy</td>\n",
       "      <td>Thomas</td>\n",
       "      <td>nancy.thomas@sakilacustomer.org</td>\n",
       "      <td>16</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>300</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>Farnsworth</td>\n",
       "      <td>john.farnsworth@sakilacustomer.org</td>\n",
       "      <td>305</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Menard</td>\n",
       "      <td>peter.menard@sakilacustomer.org</td>\n",
       "      <td>346</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name   last_name  \\\n",
       "0           12         1      Nancy      Thomas   \n",
       "1          300         1       John  Farnsworth   \n",
       "2          341         1      Peter      Menard   \n",
       "\n",
       "                                email  address_id  activebool create_date  \\\n",
       "0     nancy.thomas@sakilacustomer.org          16        True  2006-02-14   \n",
       "1  john.farnsworth@sakilacustomer.org         305        True  2006-02-14   \n",
       "2     peter.menard@sakilacustomer.org         346        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_17 = '''\n",
    "          SELECT * FROM customer\n",
    "          WHERE first_name IN ('John', 'Nancy', 'Peter')\n",
    "          '''\n",
    "\n",
    "jonh_nancy_peter = get_data(query_17)\n",
    "jonh_nancy_peter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LIKE Statement\n",
    "\n",
    "The LIKE statement helps us to select data based on whether its values on a given column match a certain pattern, we can only perform this operation on string type data.\n",
    "\n",
    "We can create these patterns using the % and _ characters:\n",
    "\n",
    "* % matches any sequence of characters\n",
    "* _ matches any single characters\n",
    "\n",
    "The LIKE statement is CASE-SENSITIVE, this means that when trying to match the srting wiht the pattern SQL will take into acocunt whether the letters are lowercase letters or uppercase letters. In contras with the ILIKE statement thats CASE-INSENSITIVE.\n",
    "\n",
    "## Ex. 18\n",
    "\n",
    "A use case of the LIKE statement would be to select the rows from the customer table where the first_name starts with the letter A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>Angela</td>\n",
       "      <td>Hernandez</td>\n",
       "      <td>angela.hernandez@sakilacustomer.org</td>\n",
       "      <td>33</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>Amy</td>\n",
       "      <td>Lopez</td>\n",
       "      <td>amy.lopez@sakilacustomer.org</td>\n",
       "      <td>36</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>Anna</td>\n",
       "      <td>Hill</td>\n",
       "      <td>anna.hill@sakilacustomer.org</td>\n",
       "      <td>37</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>Amanda</td>\n",
       "      <td>Carter</td>\n",
       "      <td>amanda.carter@sakilacustomer.org</td>\n",
       "      <td>44</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>Ann</td>\n",
       "      <td>Evans</td>\n",
       "      <td>ann.evans@sakilacustomer.org</td>\n",
       "      <td>52</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51</td>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>Stewart</td>\n",
       "      <td>alice.stewart@sakilacustomer.org</td>\n",
       "      <td>55</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>Ashley</td>\n",
       "      <td>Richardson</td>\n",
       "      <td>ashley.richardson@sakilacustomer.org</td>\n",
       "      <td>67</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>81</td>\n",
       "      <td>1</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>andrea.henderson@sakilacustomer.org</td>\n",
       "      <td>85</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>Anne</td>\n",
       "      <td>Powell</td>\n",
       "      <td>anne.powell@sakilacustomer.org</td>\n",
       "      <td>89</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>97</td>\n",
       "      <td>2</td>\n",
       "      <td>Annie</td>\n",
       "      <td>Russell</td>\n",
       "      <td>annie.russell@sakilacustomer.org</td>\n",
       "      <td>101</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name   last_name  \\\n",
       "0           29         2     Angela   Hernandez   \n",
       "1           32         1        Amy       Lopez   \n",
       "2           33         2       Anna        Hill   \n",
       "3           40         2     Amanda      Carter   \n",
       "4           48         1        Ann       Evans   \n",
       "5           51         1      Alice     Stewart   \n",
       "6           63         1     Ashley  Richardson   \n",
       "7           81         1     Andrea   Henderson   \n",
       "8           85         2       Anne      Powell   \n",
       "9           97         2      Annie     Russell   \n",
       "\n",
       "                                  email  address_id  activebool create_date  \\\n",
       "0   angela.hernandez@sakilacustomer.org          33        True  2006-02-14   \n",
       "1          amy.lopez@sakilacustomer.org          36        True  2006-02-14   \n",
       "2          anna.hill@sakilacustomer.org          37        True  2006-02-14   \n",
       "3      amanda.carter@sakilacustomer.org          44        True  2006-02-14   \n",
       "4          ann.evans@sakilacustomer.org          52        True  2006-02-14   \n",
       "5      alice.stewart@sakilacustomer.org          55        True  2006-02-14   \n",
       "6  ashley.richardson@sakilacustomer.org          67        True  2006-02-14   \n",
       "7   andrea.henderson@sakilacustomer.org          85        True  2006-02-14   \n",
       "8        anne.powell@sakilacustomer.org          89        True  2006-02-14   \n",
       "9      annie.russell@sakilacustomer.org         101        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  \n",
       "3 2013-05-26 14:49:45.738       1  \n",
       "4 2013-05-26 14:49:45.738       1  \n",
       "5 2013-05-26 14:49:45.738       1  \n",
       "6 2013-05-26 14:49:45.738       1  \n",
       "7 2013-05-26 14:49:45.738       1  \n",
       "8 2013-05-26 14:49:45.738       1  \n",
       "9 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_18 = '''\n",
    "          SELECT * FROM customer\n",
    "          WHERE first_name LIKE 'A%'\n",
    "          '''\n",
    "\n",
    "A_names = get_data(query_18)\n",
    "A_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 19\n",
    "\n",
    "We can also deny the LIKE statement to get the rows where the first_name doesn't stary with A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>activebool</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "      <th>active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>524</td>\n",
       "      <td>1</td>\n",
       "      <td>Jared</td>\n",
       "      <td>Ely</td>\n",
       "      <td>jared.ely@sakilacustomer.org</td>\n",
       "      <td>530</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mary</td>\n",
       "      <td>Smith</td>\n",
       "      <td>mary.smith@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>patricia.johnson@sakilacustomer.org</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Linda</td>\n",
       "      <td>Williams</td>\n",
       "      <td>linda.williams@sakilacustomer.org</td>\n",
       "      <td>7</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Barbara</td>\n",
       "      <td>Jones</td>\n",
       "      <td>barbara.jones@sakilacustomer.org</td>\n",
       "      <td>8</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Elizabeth</td>\n",
       "      <td>Brown</td>\n",
       "      <td>elizabeth.brown@sakilacustomer.org</td>\n",
       "      <td>9</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Davis</td>\n",
       "      <td>jennifer.davis@sakilacustomer.org</td>\n",
       "      <td>10</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Miller</td>\n",
       "      <td>maria.miller@sakilacustomer.org</td>\n",
       "      <td>11</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>Susan</td>\n",
       "      <td>Wilson</td>\n",
       "      <td>susan.wilson@sakilacustomer.org</td>\n",
       "      <td>12</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Moore</td>\n",
       "      <td>margaret.moore@sakilacustomer.org</td>\n",
       "      <td>13</td>\n",
       "      <td>True</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2013-05-26 14:49:45.738</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0          524         1      Jared       Ely   \n",
       "1            1         1       Mary     Smith   \n",
       "2            2         1   Patricia   Johnson   \n",
       "3            3         1      Linda  Williams   \n",
       "4            4         2    Barbara     Jones   \n",
       "5            5         1  Elizabeth     Brown   \n",
       "6            6         2   Jennifer     Davis   \n",
       "7            7         1      Maria    Miller   \n",
       "8            8         2      Susan    Wilson   \n",
       "9            9         2   Margaret     Moore   \n",
       "\n",
       "                                 email  address_id  activebool create_date  \\\n",
       "0         jared.ely@sakilacustomer.org         530        True  2006-02-14   \n",
       "1        mary.smith@sakilacustomer.org           5        True  2006-02-14   \n",
       "2  patricia.johnson@sakilacustomer.org           6        True  2006-02-14   \n",
       "3    linda.williams@sakilacustomer.org           7        True  2006-02-14   \n",
       "4     barbara.jones@sakilacustomer.org           8        True  2006-02-14   \n",
       "5   elizabeth.brown@sakilacustomer.org           9        True  2006-02-14   \n",
       "6    jennifer.davis@sakilacustomer.org          10        True  2006-02-14   \n",
       "7      maria.miller@sakilacustomer.org          11        True  2006-02-14   \n",
       "8      susan.wilson@sakilacustomer.org          12        True  2006-02-14   \n",
       "9    margaret.moore@sakilacustomer.org          13        True  2006-02-14   \n",
       "\n",
       "              last_update  active  \n",
       "0 2013-05-26 14:49:45.738       1  \n",
       "1 2013-05-26 14:49:45.738       1  \n",
       "2 2013-05-26 14:49:45.738       1  \n",
       "3 2013-05-26 14:49:45.738       1  \n",
       "4 2013-05-26 14:49:45.738       1  \n",
       "5 2013-05-26 14:49:45.738       1  \n",
       "6 2013-05-26 14:49:45.738       1  \n",
       "7 2013-05-26 14:49:45.738       1  \n",
       "8 2013-05-26 14:49:45.738       1  \n",
       "9 2013-05-26 14:49:45.738       1  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_19 = '''\n",
    "          SELECT * FROM customer\n",
    "          WHERE first_name NOT LIKE 'A%'\n",
    "          '''\n",
    "\n",
    "no_A_names = get_data(query_19)\n",
    "no_A_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To conclude this notebook we must close the connection to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.3 64-bit ('sql': conda)",
   "language": "python",
   "name": "python38364bitsqlcondac46ff261eb2d405f8ac307a3c81d2935"
  },
  "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}