SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<b><font size=100>SQL</font></b>\n",
    "<br>\n",
    "<br>\n",
    "By अंkur गोswami\n",
    "____"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "hide_input": true
   },
   "outputs": [],
   "source": [
    "import sqlite3 as sql\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "hide_input": true
   },
   "outputs": [],
   "source": [
    "class Query:\n",
    "    def __init__(self, path):\n",
    "        self.path=path\n",
    "    def run(self, query):\n",
    "        conn=sql.connect(self.path)\n",
    "        cur=conn.execute(query)\n",
    "        result=cur.fetchall()\n",
    "        conn.close()\n",
    "        return pd.DataFrame(result,columns=[i[0] for i in cur.description])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "hide_input": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "query=Query('./data/sql/DEPT_EMP.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SELECT Clause\n",
    "The SELECT statement is used to select data FROM a database. The data returned is stored in a result table, called the result-set."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP;___"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "hide_input": true,
    "tags": [
     "hide-input"
    ]
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7369</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7902.0</td>\n",
       "      <td>93/6/13</td>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7499</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>98/8/15</td>\n",
       "      <td>1600</td>\n",
       "      <td>300.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7521</td>\n",
       "      <td>WARD</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>96/3/26</td>\n",
       "      <td>1250</td>\n",
       "      <td>500.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7788</td>\n",
       "      <td>SCOTT</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566.0</td>\n",
       "      <td>96/3/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7839</td>\n",
       "      <td>KING</td>\n",
       "      <td>PRESIDENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90/6/9</td>\n",
       "      <td>5000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7844</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>95/6/4</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7876</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7788.0</td>\n",
       "      <td>99/6/4</td>\n",
       "      <td>1100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7900</td>\n",
       "      <td>JAMES</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>00/6/23</td>\n",
       "      <td>950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>7934</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7782.0</td>\n",
       "      <td>00/1/21</td>\n",
       "      <td>1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7902</td>\n",
       "      <td>FORD</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566.0</td>\n",
       "      <td>97/12/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7654</td>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>98/12/5</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    EMPNO   ENAME        JOB     MGR  HIREDATE   SAL    COMM  DEPTNO\n",
       "0    7369   SMITH      CLERK  7902.0   93/6/13   800     0.0      20\n",
       "1    7499   ALLEN   SALESMAN  7698.0   98/8/15  1600   300.0      30\n",
       "2    7521    WARD   SALESMAN  7698.0   96/3/26  1250   500.0      30\n",
       "3    7566   JONES    MANAGER  7839.0  95/10/31  2975     NaN      20\n",
       "4    7698   BLAKE    MANAGER  7839.0   92/6/11  2850     NaN      30\n",
       "5    7782   CLARK    MANAGER  7839.0   93/5/14  2450     NaN      10\n",
       "6    7788   SCOTT    ANALYST  7566.0    96/3/5  3000     NaN      20\n",
       "7    7839    KING  PRESIDENT     NaN    90/6/9  5000     0.0      10\n",
       "8    7844  TURNER   SALESMAN  7698.0    95/6/4  1500     0.0      30\n",
       "9    7876   ADAMS      CLERK  7788.0    99/6/4  1100     NaN      20\n",
       "10   7900   JAMES      CLERK  7698.0   00/6/23   950     NaN      30\n",
       "11   7934  MILLER      CLERK  7782.0   00/1/21  1300     NaN      10\n",
       "12   7902    FORD    ANALYST  7566.0   97/12/5  3000     NaN      20\n",
       "13   7654  MARTIN   SALESMAN  7698.0   98/12/5  1250  1400.0      30"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('SELECT * FROM EMP;')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT SAL FROM EMP;___"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "hide_input": true
   },
   "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>SAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SAL\n",
       "0    800\n",
       "1   1600\n",
       "2   1250\n",
       "3   2975\n",
       "4   2850\n",
       "5   2450\n",
       "6   3000\n",
       "7   5000\n",
       "8   1500\n",
       "9   1100\n",
       "10   950\n",
       "11  1300\n",
       "12  3000\n",
       "13  1250"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('SELECT SAL FROM EMP;')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, SAL FROM EMP;___"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "      <th>SAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>1600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WARD</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES</td>\n",
       "      <td>2975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>2850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>2450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ENAME   SAL\n",
       "0    SMITH   800\n",
       "1    ALLEN  1600\n",
       "2     WARD  1250\n",
       "3    JONES  2975\n",
       "4    BLAKE  2850\n",
       "5    CLARK  2450\n",
       "6    SCOTT  3000\n",
       "7     KING  5000\n",
       "8   TURNER  1500\n",
       "9    ADAMS  1100\n",
       "10   JAMES   950\n",
       "11  MILLER  1300\n",
       "12    FORD  3000\n",
       "13  MARTIN  1250"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('SELECT ENAME, SAL FROM EMP;')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## WHERE Clause\n",
    "The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comparison Operators:\n",
    "Equal to: `=`<br>\n",
    "Not equal to: `!=` and `<>`<br>\n",
    "Greater than: `>`<br>\n",
    "Less than: `<`<br>\n",
    "Greater than or equal to: `>=`<br>\n",
    "Less than or equal to: `<=`<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP <br>\n",
    "WHERE JOB = 'MANAGER';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>None</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO  ENAME      JOB   MGR  HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7566  JONES  MANAGER  7839  95/10/31  2975  None      20\n",
       "1   7698  BLAKE  MANAGER  7839   92/6/11  2850  None      30\n",
       "2   7782  CLARK  MANAGER  7839   93/5/14  2450  None      10"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB='MANAGER';''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## AND operator\n",
    "The AND operator displays a record if all the conditions separated by AND are TRUE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE JOB='SALESMAN'<br>\n",
    "AND SAL>=1300;<br>___"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7499</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>98/8/15</td>\n",
       "      <td>1600</td>\n",
       "      <td>300</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7844</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>95/6/4</td>\n",
       "      <td>1500</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME       JOB   MGR HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7499   ALLEN  SALESMAN  7698  98/8/15  1600   300      30\n",
       "1   7844  TURNER  SALESMAN  7698   95/6/4  1500     0      30"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB='SALESMAN'\n",
    "            AND SAL>=1300;\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## OR operator\n",
    "The OR operator displays a record if any of the conditions separated by OR is TRUE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE JOB='SALESMAN'<br>\n",
    "OR SAL>=1300;<br>___"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7499</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>98/8/15</td>\n",
       "      <td>1600</td>\n",
       "      <td>300.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7521</td>\n",
       "      <td>WARD</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>96/3/26</td>\n",
       "      <td>1250</td>\n",
       "      <td>500.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7788</td>\n",
       "      <td>SCOTT</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566.0</td>\n",
       "      <td>96/3/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7839</td>\n",
       "      <td>KING</td>\n",
       "      <td>PRESIDENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90/6/9</td>\n",
       "      <td>5000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7844</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>95/6/4</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7934</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7782.0</td>\n",
       "      <td>00/1/21</td>\n",
       "      <td>1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7902</td>\n",
       "      <td>FORD</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566.0</td>\n",
       "      <td>97/12/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7654</td>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>98/12/5</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    EMPNO   ENAME        JOB     MGR  HIREDATE   SAL    COMM  DEPTNO\n",
       "0    7499   ALLEN   SALESMAN  7698.0   98/8/15  1600   300.0      30\n",
       "1    7521    WARD   SALESMAN  7698.0   96/3/26  1250   500.0      30\n",
       "2    7566   JONES    MANAGER  7839.0  95/10/31  2975     NaN      20\n",
       "3    7698   BLAKE    MANAGER  7839.0   92/6/11  2850     NaN      30\n",
       "4    7782   CLARK    MANAGER  7839.0   93/5/14  2450     NaN      10\n",
       "5    7788   SCOTT    ANALYST  7566.0    96/3/5  3000     NaN      20\n",
       "6    7839    KING  PRESIDENT     NaN    90/6/9  5000     0.0      10\n",
       "7    7844  TURNER   SALESMAN  7698.0    95/6/4  1500     0.0      30\n",
       "8    7934  MILLER      CLERK  7782.0   00/1/21  1300     NaN      10\n",
       "9    7902    FORD    ANALYST  7566.0   97/12/5  3000     NaN      20\n",
       "10   7654  MARTIN   SALESMAN  7698.0   98/12/5  1250  1400.0      30"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB='SALESMAN'\n",
    "            OR SAL>=1300;\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME FROM EMP<br>\n",
    "WHERE (JOB <> MANAGER AND JOB != SALESMAN)<br>\n",
    "AND SAL >= 2000;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SCOTT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>KING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FORD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ENAME\n",
       "0  SCOTT\n",
       "1   KING\n",
       "2   FORD"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME FROM EMP\n",
    "            WHERE (JOB <> 'MANAGER' AND JOB != 'SALESMAN')\n",
    "            AND SAL >= 2000;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## IN operator\n",
    "The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE JOB IN ('ANALYST','CLERK');___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7369</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7902</td>\n",
       "      <td>93/6/13</td>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7788</td>\n",
       "      <td>SCOTT</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566</td>\n",
       "      <td>96/3/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7876</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7788</td>\n",
       "      <td>99/6/4</td>\n",
       "      <td>1100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7900</td>\n",
       "      <td>JAMES</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7698</td>\n",
       "      <td>00/6/23</td>\n",
       "      <td>950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7934</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7782</td>\n",
       "      <td>00/1/21</td>\n",
       "      <td>1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7902</td>\n",
       "      <td>FORD</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566</td>\n",
       "      <td>97/12/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME      JOB   MGR HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7369   SMITH    CLERK  7902  93/6/13   800   0.0      20\n",
       "1   7788   SCOTT  ANALYST  7566   96/3/5  3000   NaN      20\n",
       "2   7876   ADAMS    CLERK  7788   99/6/4  1100   NaN      20\n",
       "3   7900   JAMES    CLERK  7698  00/6/23   950   NaN      30\n",
       "4   7934  MILLER    CLERK  7782  00/1/21  1300   NaN      10\n",
       "5   7902    FORD  ANALYST  7566  97/12/5  3000   NaN      20"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB IN ('ANALYST','CLERK');\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BETWEEN operator\n",
    "The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE HIREDATE BETWEEN '93/5/14' AND '99/6/4';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7369</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7902</td>\n",
       "      <td>93/6/13</td>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7499</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>98/8/15</td>\n",
       "      <td>1600</td>\n",
       "      <td>300.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7521</td>\n",
       "      <td>WARD</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>96/3/26</td>\n",
       "      <td>1250</td>\n",
       "      <td>500.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7788</td>\n",
       "      <td>SCOTT</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566</td>\n",
       "      <td>96/3/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7844</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>95/6/4</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7876</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7788</td>\n",
       "      <td>99/6/4</td>\n",
       "      <td>1100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7902</td>\n",
       "      <td>FORD</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566</td>\n",
       "      <td>97/12/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7654</td>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>98/12/5</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME       JOB   MGR  HIREDATE   SAL    COMM  DEPTNO\n",
       "0   7369   SMITH     CLERK  7902   93/6/13   800     0.0      20\n",
       "1   7499   ALLEN  SALESMAN  7698   98/8/15  1600   300.0      30\n",
       "2   7521    WARD  SALESMAN  7698   96/3/26  1250   500.0      30\n",
       "3   7566   JONES   MANAGER  7839  95/10/31  2975     NaN      20\n",
       "4   7782   CLARK   MANAGER  7839   93/5/14  2450     NaN      10\n",
       "5   7788   SCOTT   ANALYST  7566    96/3/5  3000     NaN      20\n",
       "6   7844  TURNER  SALESMAN  7698    95/6/4  1500     0.0      30\n",
       "7   7876   ADAMS     CLERK  7788    99/6/4  1100     NaN      20\n",
       "8   7902    FORD   ANALYST  7566   97/12/5  3000     NaN      20\n",
       "9   7654  MARTIN  SALESMAN  7698   98/12/5  1250  1400.0      30"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE HIREDATE BETWEEN '93/5/14' AND '99/6/4';\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NOT operator\n",
    "The NOT operator displays a record if the condition(s) is NOT TRUE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE JOB NOT IN ('ANALYST','CLERK');___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7499</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>98/8/15</td>\n",
       "      <td>1600</td>\n",
       "      <td>300.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7521</td>\n",
       "      <td>WARD</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>96/3/26</td>\n",
       "      <td>1250</td>\n",
       "      <td>500.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7839</td>\n",
       "      <td>KING</td>\n",
       "      <td>PRESIDENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90/6/9</td>\n",
       "      <td>5000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7844</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>95/6/4</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7654</td>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>98/12/5</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME        JOB     MGR  HIREDATE   SAL    COMM  DEPTNO\n",
       "0   7499   ALLEN   SALESMAN  7698.0   98/8/15  1600   300.0      30\n",
       "1   7521    WARD   SALESMAN  7698.0   96/3/26  1250   500.0      30\n",
       "2   7566   JONES    MANAGER  7839.0  95/10/31  2975     NaN      20\n",
       "3   7698   BLAKE    MANAGER  7839.0   92/6/11  2850     NaN      30\n",
       "4   7782   CLARK    MANAGER  7839.0   93/5/14  2450     NaN      10\n",
       "5   7839    KING  PRESIDENT     NaN    90/6/9  5000     0.0      10\n",
       "6   7844  TURNER   SALESMAN  7698.0    95/6/4  1500     0.0      30\n",
       "7   7654  MARTIN   SALESMAN  7698.0   98/12/5  1250  1400.0      30"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB NOT IN ('ANALYST','CLERK');\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE HIREDATE NOT BETWEEN '93/5/14' AND '99/6/4';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7839</td>\n",
       "      <td>KING</td>\n",
       "      <td>PRESIDENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90/6/9</td>\n",
       "      <td>5000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7900</td>\n",
       "      <td>JAMES</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7698.0</td>\n",
       "      <td>00/6/23</td>\n",
       "      <td>950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7934</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7782.0</td>\n",
       "      <td>00/1/21</td>\n",
       "      <td>1300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME        JOB     MGR HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7698   BLAKE    MANAGER  7839.0  92/6/11  2850   NaN      30\n",
       "1   7839    KING  PRESIDENT     NaN   90/6/9  5000   0.0      10\n",
       "2   7900   JAMES      CLERK  7698.0  00/6/23   950   NaN      30\n",
       "3   7934  MILLER      CLERK  7782.0  00/1/21  1300   NaN      10"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE HIREDATE NOT BETWEEN '93/5/14' AND '99/6/4';\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## IS operator\n",
    "Equivalent to `=` operator."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP <br>\n",
    "WHERE JOB IS 'MANAGER';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>None</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO  ENAME      JOB   MGR  HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7566  JONES  MANAGER  7839  95/10/31  2975  None      20\n",
       "1   7698  BLAKE  MANAGER  7839   92/6/11  2850  None      30\n",
       "2   7782  CLARK  MANAGER  7839   93/5/14  2450  None      10"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB='MANAGER';''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NULL values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP <br>\n",
    "WHERE COMM IS NULL;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>None</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7788</td>\n",
       "      <td>SCOTT</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566</td>\n",
       "      <td>96/3/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7876</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7788</td>\n",
       "      <td>99/6/4</td>\n",
       "      <td>1100</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7900</td>\n",
       "      <td>JAMES</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7698</td>\n",
       "      <td>00/6/23</td>\n",
       "      <td>950</td>\n",
       "      <td>None</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7934</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7782</td>\n",
       "      <td>00/1/21</td>\n",
       "      <td>1300</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7902</td>\n",
       "      <td>FORD</td>\n",
       "      <td>ANALYST</td>\n",
       "      <td>7566</td>\n",
       "      <td>97/12/5</td>\n",
       "      <td>3000</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME      JOB   MGR  HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7566   JONES  MANAGER  7839  95/10/31  2975  None      20\n",
       "1   7698   BLAKE  MANAGER  7839   92/6/11  2850  None      30\n",
       "2   7782   CLARK  MANAGER  7839   93/5/14  2450  None      10\n",
       "3   7788   SCOTT  ANALYST  7566    96/3/5  3000  None      20\n",
       "4   7876   ADAMS    CLERK  7788    99/6/4  1100  None      20\n",
       "5   7900   JAMES    CLERK  7698   00/6/23   950  None      30\n",
       "6   7934  MILLER    CLERK  7782   00/1/21  1300  None      10\n",
       "7   7902    FORD  ANALYST  7566   97/12/5  3000  None      20"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE COMM IS NULL;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP <br>\n",
    "WHERE COMM = NULL;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO]\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE COMM=NULL;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Note:__ `IS` & `=` are not equivalent in case of a null value."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LIKE Clause\n",
    "The LIKE operator is used in a WHERE clause to search for a specified pattern in a column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Wildcards"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %\n",
    "Represents zero or more characters."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE JOB LIKE '%MAN';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7499</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>98/8/15</td>\n",
       "      <td>1600</td>\n",
       "      <td>300</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7521</td>\n",
       "      <td>WARD</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>96/3/26</td>\n",
       "      <td>1250</td>\n",
       "      <td>500</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7844</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>95/6/4</td>\n",
       "      <td>1500</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7654</td>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>98/12/5</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME       JOB   MGR HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7499   ALLEN  SALESMAN  7698  98/8/15  1600   300      30\n",
       "1   7521    WARD  SALESMAN  7698  96/3/26  1250   500      30\n",
       "2   7844  TURNER  SALESMAN  7698   95/6/4  1500     0      30\n",
       "3   7654  MARTIN  SALESMAN  7698  98/12/5  1250  1400      30"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB LIKE '%MAN';\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE JOB LIKE 'MAN%';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>None</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO  ENAME      JOB   MGR  HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7566  JONES  MANAGER  7839  95/10/31  2975  None      20\n",
       "1   7698  BLAKE  MANAGER  7839   92/6/11  2850  None      30\n",
       "2   7782  CLARK  MANAGER  7839   93/5/14  2450  None      10"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB LIKE 'MAN%';\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE JOB LIKE '%MAN%';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7499</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>98/8/15</td>\n",
       "      <td>1600</td>\n",
       "      <td>300.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7521</td>\n",
       "      <td>WARD</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>96/3/26</td>\n",
       "      <td>1250</td>\n",
       "      <td>500.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7698</td>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>92/6/11</td>\n",
       "      <td>2850</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7782</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>93/5/14</td>\n",
       "      <td>2450</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7844</td>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>95/6/4</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7654</td>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SALESMAN</td>\n",
       "      <td>7698</td>\n",
       "      <td>98/12/5</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO   ENAME       JOB   MGR  HIREDATE   SAL    COMM  DEPTNO\n",
       "0   7499   ALLEN  SALESMAN  7698   98/8/15  1600   300.0      30\n",
       "1   7521    WARD  SALESMAN  7698   96/3/26  1250   500.0      30\n",
       "2   7566   JONES   MANAGER  7839  95/10/31  2975     NaN      20\n",
       "3   7698   BLAKE   MANAGER  7839   92/6/11  2850     NaN      30\n",
       "4   7782   CLARK   MANAGER  7839   93/5/14  2450     NaN      10\n",
       "5   7844  TURNER  SALESMAN  7698    95/6/4  1500     0.0      30\n",
       "6   7654  MARTIN  SALESMAN  7698   98/12/5  1250  1400.0      30"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE JOB LIKE '%MAN%';\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### _\n",
    "Represents a single character"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT * FROM EMP<br>\n",
    "WHERE ENAME LIKE '\\_\\_\\_\\_S';___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hide_input": true
   },
   "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>EMPNO</th>\n",
       "      <th>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "      <th>MGR</th>\n",
       "      <th>HIREDATE</th>\n",
       "      <th>SAL</th>\n",
       "      <th>COMM</th>\n",
       "      <th>DEPTNO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7566</td>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>7839</td>\n",
       "      <td>95/10/31</td>\n",
       "      <td>2975</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7876</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7788</td>\n",
       "      <td>99/6/4</td>\n",
       "      <td>1100</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7900</td>\n",
       "      <td>JAMES</td>\n",
       "      <td>CLERK</td>\n",
       "      <td>7698</td>\n",
       "      <td>00/6/23</td>\n",
       "      <td>950</td>\n",
       "      <td>None</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMPNO  ENAME      JOB   MGR  HIREDATE   SAL  COMM  DEPTNO\n",
       "0   7566  JONES  MANAGER  7839  95/10/31  2975  None      20\n",
       "1   7876  ADAMS    CLERK  7788    99/6/4  1100  None      20\n",
       "2   7900  JAMES    CLERK  7698   00/6/23   950  None      30"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT * FROM EMP\n",
    "            WHERE ENAME LIKE '____S';\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hide_input": true
   },
   "source": [
    "## ALIAS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME AS Employee, SAL AS Salary, COMM AS Commision <br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "hide_input": true,
    "scrolled": false
   },
   "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>Employee</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Commision</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>1600</td>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WARD</td>\n",
       "      <td>1250</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES</td>\n",
       "      <td>2975</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>2850</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>2450</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING</td>\n",
       "      <td>5000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>1100</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>950</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>1300</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Employee  Salary  Commision\n",
       "0     SMITH     800        0.0\n",
       "1     ALLEN    1600      300.0\n",
       "2      WARD    1250      500.0\n",
       "3     JONES    2975        NaN\n",
       "4     BLAKE    2850        NaN\n",
       "5     CLARK    2450        NaN\n",
       "6     SCOTT    3000        NaN\n",
       "7      KING    5000        0.0\n",
       "8    TURNER    1500        0.0\n",
       "9     ADAMS    1100        NaN\n",
       "10    JAMES     950        NaN\n",
       "11   MILLER    1300        NaN\n",
       "12     FORD    3000        NaN\n",
       "13   MARTIN    1250     1400.0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME AS Employee, SAL AS Salary, COMM AS Commision\n",
    "            FROM EMP;\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Note:__ Cannot use whitespaces while aliasing columns. Instead use underscore in place of whitespace or put column alias names in double quotes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hide_input": false
   },
   "source": [
    "__Note:__ Not writing `AS` will still work. But it's a convention to do aliasing using `AS`, as it is more-readable & works with most databases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME \"Employee name\", SAL Salary, COMM Commision <br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "hide_input": true,
    "scrolled": false
   },
   "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>Employee name</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Commision</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>1600</td>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WARD</td>\n",
       "      <td>1250</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES</td>\n",
       "      <td>2975</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>2850</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>2450</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING</td>\n",
       "      <td>5000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>1100</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>950</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>1300</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD</td>\n",
       "      <td>3000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>1250</td>\n",
       "      <td>1400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Employee name  Salary  Commision\n",
       "0          SMITH     800        0.0\n",
       "1          ALLEN    1600      300.0\n",
       "2           WARD    1250      500.0\n",
       "3          JONES    2975        NaN\n",
       "4          BLAKE    2850        NaN\n",
       "5          CLARK    2450        NaN\n",
       "6          SCOTT    3000        NaN\n",
       "7           KING    5000        0.0\n",
       "8         TURNER    1500        0.0\n",
       "9          ADAMS    1100        NaN\n",
       "10         JAMES     950        NaN\n",
       "11        MILLER    1300        NaN\n",
       "12          FORD    3000        NaN\n",
       "13        MARTIN    1250     1400.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME \"Employee name\", SAL Salary, COMM Commision \n",
    "            FROM EMP;\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hide_input": true
   },
   "source": [
    "## CONCATENATION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using ||:\n",
    "`||` is used to concatenate two or more strings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT 'My Name is ' || ENAME<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "hide_input": true,
    "scrolled": false
   },
   "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>'My Name is ' || ENAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>My Name is SMITH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>My Name is ALLEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>My Name is WARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>My Name is JONES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>My Name is BLAKE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>My Name is CLARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>My Name is SCOTT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>My Name is KING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>My Name is TURNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>My Name is ADAMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>My Name is JAMES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>My Name is MILLER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>My Name is FORD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>My Name is MARTIN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   'My Name is ' || ENAME\n",
       "0        My Name is SMITH\n",
       "1        My Name is ALLEN\n",
       "2         My Name is WARD\n",
       "3        My Name is JONES\n",
       "4        My Name is BLAKE\n",
       "5        My Name is CLARK\n",
       "6        My Name is SCOTT\n",
       "7         My Name is KING\n",
       "8       My Name is TURNER\n",
       "9        My Name is ADAMS\n",
       "10       My Name is JAMES\n",
       "11      My Name is MILLER\n",
       "12        My Name is FORD\n",
       "13      My Name is MARTIN"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT 'My Name is ' || ENAME\n",
    "            FROM EMP;\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hide_input": true
   },
   "source": [
    "___SELECT ENAME || ' makes $' || SAL || ' per month.' AS \"Generated Report\"<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "hide_input": true,
    "scrolled": false
   },
   "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>Generated Report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH is a CLERK and makes $800 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN is a SALESMAN and makes $1600 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WARD is a SALESMAN and makes $1250 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES is a MANAGER and makes $2975 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE is a MANAGER and makes $2850 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK is a MANAGER and makes $2450 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SCOTT is a ANALYST and makes $3000 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING is a PRESIDENT and makes $5000 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TURNER is a SALESMAN and makes $1500 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ADAMS is a CLERK and makes $1100 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JAMES is a CLERK and makes $950 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MILLER is a CLERK and makes $1300 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD is a ANALYST and makes $3000 per month.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MARTIN is a SALESMAN and makes $1250 per month.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Generated Report\n",
       "0        SMITH is a CLERK and makes $800 per month.\n",
       "1    ALLEN is a SALESMAN and makes $1600 per month.\n",
       "2     WARD is a SALESMAN and makes $1250 per month.\n",
       "3     JONES is a MANAGER and makes $2975 per month.\n",
       "4     BLAKE is a MANAGER and makes $2850 per month.\n",
       "5     CLARK is a MANAGER and makes $2450 per month.\n",
       "6     SCOTT is a ANALYST and makes $3000 per month.\n",
       "7    KING is a PRESIDENT and makes $5000 per month.\n",
       "8   TURNER is a SALESMAN and makes $1500 per month.\n",
       "9       ADAMS is a CLERK and makes $1100 per month.\n",
       "10       JAMES is a CLERK and makes $950 per month.\n",
       "11     MILLER is a CLERK and makes $1300 per month.\n",
       "12     FORD is a ANALYST and makes $3000 per month.\n",
       "13  MARTIN is a SALESMAN and makes $1250 per month."
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME || ' is a ' || JOB || ' and makes $' || SAL || ' per month.' AS \"Generated Report\"\n",
    "            FROM EMP;\n",
    "            ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using CONCAT():\n",
    "CONCAT() takes 2 values as parameters and returns a concatenated result."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SORTING"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, JOB<br>\n",
    "FROM EMP<br>\n",
    "ORDER BY ENAME;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "      <th>JOB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>CLERK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SALESMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FORD</td>\n",
       "      <td>ANALYST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>CLERK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>JONES</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING</td>\n",
       "      <td>PRESIDENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SALESMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>CLERK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>ANALYST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>CLERK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>SALESMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>WARD</td>\n",
       "      <td>SALESMAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ENAME        JOB\n",
       "0    ADAMS      CLERK\n",
       "1    ALLEN   SALESMAN\n",
       "2    BLAKE    MANAGER\n",
       "3    CLARK    MANAGER\n",
       "4     FORD    ANALYST\n",
       "5    JAMES      CLERK\n",
       "6    JONES    MANAGER\n",
       "7     KING  PRESIDENT\n",
       "8   MARTIN   SALESMAN\n",
       "9   MILLER      CLERK\n",
       "10   SCOTT    ANALYST\n",
       "11   SMITH      CLERK\n",
       "12  TURNER   SALESMAN\n",
       "13    WARD   SALESMAN"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME, JOB\n",
    "            FROM EMP\n",
    "            ORDER BY ENAME;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Note:__ Sorting is implicitly Ascending in SQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explicit Ascending order:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, JOB<br>\n",
    "FROM EMP<br>\n",
    "ORDER BY SAL ASC;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "      <th>SAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WARD</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>1600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>2450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>2850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JONES</td>\n",
       "      <td>2975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>KING</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ENAME   SAL\n",
       "0    SMITH   800\n",
       "1    JAMES   950\n",
       "2    ADAMS  1100\n",
       "3     WARD  1250\n",
       "4   MARTIN  1250\n",
       "5   MILLER  1300\n",
       "6   TURNER  1500\n",
       "7    ALLEN  1600\n",
       "8    CLARK  2450\n",
       "9    BLAKE  2850\n",
       "10   JONES  2975\n",
       "11   SCOTT  3000\n",
       "12    FORD  3000\n",
       "13    KING  5000"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME, SAL\n",
    "            FROM EMP\n",
    "            ORDER BY SAL ASC;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Descending order:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, JOB<br>\n",
    "FROM EMP<br>\n",
    "ORDER BY SAL DESC;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "      <th>SAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KING</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FORD</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES</td>\n",
       "      <td>2975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>2850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>2450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>1600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WARD</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ENAME   SAL\n",
       "0     KING  5000\n",
       "1    SCOTT  3000\n",
       "2     FORD  3000\n",
       "3    JONES  2975\n",
       "4    BLAKE  2850\n",
       "5    CLARK  2450\n",
       "6    ALLEN  1600\n",
       "7   TURNER  1500\n",
       "8   MILLER  1300\n",
       "9     WARD  1250\n",
       "10  MARTIN  1250\n",
       "11   ADAMS  1100\n",
       "12   JAMES   950\n",
       "13   SMITH   800"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME, SAL\n",
    "            FROM EMP\n",
    "            ORDER BY SAL DESC;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Multilevel Sorting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT DEPTNO, SAL, ENAME<br>\n",
    "FROM EMP<br>\n",
    "ORDER BY DEPTNO, SAL DESC, ENAME;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "hide_input": true
   },
   "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>DEPTNO</th>\n",
       "      <th>SAL</th>\n",
       "      <th>ENAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>5000</td>\n",
       "      <td>KING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>2450</td>\n",
       "      <td>CLARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>1300</td>\n",
       "      <td>MILLER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>3000</td>\n",
       "      <td>FORD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>3000</td>\n",
       "      <td>SCOTT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20</td>\n",
       "      <td>2975</td>\n",
       "      <td>JONES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20</td>\n",
       "      <td>1100</td>\n",
       "      <td>ADAMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20</td>\n",
       "      <td>800</td>\n",
       "      <td>SMITH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>30</td>\n",
       "      <td>2850</td>\n",
       "      <td>BLAKE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>30</td>\n",
       "      <td>1600</td>\n",
       "      <td>ALLEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>30</td>\n",
       "      <td>1500</td>\n",
       "      <td>TURNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>30</td>\n",
       "      <td>1250</td>\n",
       "      <td>MARTIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>30</td>\n",
       "      <td>1250</td>\n",
       "      <td>WARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>30</td>\n",
       "      <td>950</td>\n",
       "      <td>JAMES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    DEPTNO   SAL   ENAME\n",
       "0       10  5000    KING\n",
       "1       10  2450   CLARK\n",
       "2       10  1300  MILLER\n",
       "3       20  3000    FORD\n",
       "4       20  3000   SCOTT\n",
       "5       20  2975   JONES\n",
       "6       20  1100   ADAMS\n",
       "7       20   800   SMITH\n",
       "8       30  2850   BLAKE\n",
       "9       30  1600   ALLEN\n",
       "10      30  1500  TURNER\n",
       "11      30  1250  MARTIN\n",
       "12      30  1250    WARD\n",
       "13      30   950   JAMES"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT DEPTNO, SAL, ENAME\n",
    "            FROM EMP\n",
    "            ORDER BY DEPTNO, SAL DESC, ENAME;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Single Row Functions Functions\n",
    "Can be used with SELECT as well as WHERE clause."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LOWER()\n",
    "To convert data into lowercase."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT LOWER(ENAME) AS Name, HIREDATE<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "hide_input": true
   },
   "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>Name</th>\n",
       "      <th>HIREDATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>smith</td>\n",
       "      <td>93/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>allen</td>\n",
       "      <td>98/8/15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ward</td>\n",
       "      <td>96/3/26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>jones</td>\n",
       "      <td>95/10/31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>blake</td>\n",
       "      <td>92/6/11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>clark</td>\n",
       "      <td>93/5/14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>scott</td>\n",
       "      <td>96/3/5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>king</td>\n",
       "      <td>90/6/9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>turner</td>\n",
       "      <td>95/6/4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>adams</td>\n",
       "      <td>99/6/4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>james</td>\n",
       "      <td>00/6/23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>miller</td>\n",
       "      <td>00/1/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ford</td>\n",
       "      <td>97/12/5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>martin</td>\n",
       "      <td>98/12/5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Name  HIREDATE\n",
       "0    smith   93/6/13\n",
       "1    allen   98/8/15\n",
       "2     ward   96/3/26\n",
       "3    jones  95/10/31\n",
       "4    blake   92/6/11\n",
       "5    clark   93/5/14\n",
       "6    scott    96/3/5\n",
       "7     king    90/6/9\n",
       "8   turner    95/6/4\n",
       "9    adams    99/6/4\n",
       "10   james   00/6/23\n",
       "11  miller   00/1/21\n",
       "12    ford   97/12/5\n",
       "13  martin   98/12/5"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT LOWER(ENAME) AS Name, HIREDATE\n",
    "            FROM EMP;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UPPER()\n",
    "To convert data into uppercase."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## INITCAP()\n",
    "Captilizes first letter of every word in the string passed to it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DISTINCT()\n",
    "Returns unique values in a column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT DISTINCT(JOB)<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "hide_input": true
   },
   "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>JOB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CLERK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SALESMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANALYST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PRESIDENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         JOB\n",
       "0      CLERK\n",
       "1   SALESMAN\n",
       "2    MANAGER\n",
       "3    ANALYST\n",
       "4  PRESIDENT"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT DISTINCT(JOB)\n",
    "            FROM EMP;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LENGTH()\n",
    "Returns length of a string."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, LENGTH(ENAME)<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "      <th>LENGTH(ENAME)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WARD</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ENAME  LENGTH(ENAME)\n",
       "0    SMITH              5\n",
       "1    ALLEN              5\n",
       "2     WARD              4\n",
       "3    JONES              5\n",
       "4    BLAKE              5\n",
       "5    CLARK              5\n",
       "6    SCOTT              5\n",
       "7     KING              4\n",
       "8   TURNER              6\n",
       "9    ADAMS              5\n",
       "10   JAMES              5\n",
       "11  MILLER              6\n",
       "12    FORD              4\n",
       "13  MARTIN              6"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT ENAME, LENGTH(ENAME)\n",
    "            FROM EMP;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## COUNT()\n",
    "Returns count of values in a column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT COUNT(ENAME)<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "hide_input": true
   },
   "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(ENAME)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(ENAME)\n",
       "0            14"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run('''SELECT COUNT(ENAME)\n",
    "            FROM EMP;\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DUAL Table\n",
    "The DUAL is special one row, one column table present by default in all __Oracle__ databases. The owner of DUAL is SYS (SYS owns the data dictionary, therefore DUAL is part of the data dictionary.) but DUAL can be accessed by every user. It can be modified  to have more than one column but it always contains one row."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SUBSTR()\n",
    "Takes in 3 arguments, i.e. the string, the starting index & the number of characters to be extracted (if not provided then goes on till end). It returns the subtring of the string given to it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, SUBSTR(ENAME, 2, 3) AS SUBSTRING<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "      <th>SUBSTRING</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>MIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>LLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WARD</td>\n",
       "      <td>ARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES</td>\n",
       "      <td>ONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>LAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>LAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>COT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING</td>\n",
       "      <td>ING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>URN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>DAM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>AME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>ILL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD</td>\n",
       "      <td>ORD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>ART</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ENAME SUBSTRING\n",
       "0    SMITH       MIT\n",
       "1    ALLEN       LLE\n",
       "2     WARD       ARD\n",
       "3    JONES       ONE\n",
       "4    BLAKE       LAK\n",
       "5    CLARK       LAR\n",
       "6    SCOTT       COT\n",
       "7     KING       ING\n",
       "8   TURNER       URN\n",
       "9    ADAMS       DAM\n",
       "10   JAMES       AME\n",
       "11  MILLER       ILL\n",
       "12    FORD       ORD\n",
       "13  MARTIN       ART"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run(\"\"\"SELECT ENAME, SUBSTR(ENAME, 2, 3) AS SUBSTRING\n",
    "            FROM EMP;\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LPAD()\n",
    "Takes in 3 arguments, i.e. the string that we want to pad, length of string after padding & the character (whitespace by default) to pad with. It returns the left padded string."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, LPAD(ENAME, 8, '$') AS PADDED<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RPAD()\n",
    "Takes in 3 arguments, i.e. the string that we want to pad, length of string after padding & the character (whitespace by default) to pad with. It returns the right padded string."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ROUND()\n",
    "Takes in 2 arguments, i.e. the value to round off & the number (1 by default) of decimal places. It returns the rounded off number."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___SELECT ENAME, ROUND(100.2253,2)<br>\n",
    "FROM EMP;___<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "hide_input": true
   },
   "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>ENAME</th>\n",
       "      <th>ROUND(100.2253,2)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WARD</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JONES</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLAKE</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CLARK</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SCOTT</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>KING</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TURNER</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>JAMES</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MILLER</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FORD</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>MARTIN</td>\n",
       "      <td>100.23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ENAME  ROUND(100.2253,2)\n",
       "0    SMITH             100.23\n",
       "1    ALLEN             100.23\n",
       "2     WARD             100.23\n",
       "3    JONES             100.23\n",
       "4    BLAKE             100.23\n",
       "5    CLARK             100.23\n",
       "6    SCOTT             100.23\n",
       "7     KING             100.23\n",
       "8   TURNER             100.23\n",
       "9    ADAMS             100.23\n",
       "10   JAMES             100.23\n",
       "11  MILLER             100.23\n",
       "12    FORD             100.23\n",
       "13  MARTIN             100.23"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.run(\"\"\"SELECT ENAME, ROUND(100.2253,2)\n",
    "            FROM EMP;\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Insertion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=conn.execute(\"insert into Employee values(1,'Ankur Goswami',19,200000);\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=conn.execute(\"insert into Employee values(2,'Murtaza Nazir',21,10000)\")\n",
    "cur=conn.execute(\"insert into Employee(empid,empname,age) values(3,'RJ Rahul',21)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=conn.execute(\"insert into Employee values(4,'Naveed Unjum',21,0)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Displaying"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000),\n",
       " (2, 'Murtaza Nazir', 21, 10000),\n",
       " (3, 'RJ Rahul', 21, 10000),\n",
       " (4, 'Naveed Unjum', 21, 0)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deletion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=conn.execute(\"delete from Employee where empid=4\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000),\n",
       " (2, 'Murtaza Nazir', 21, 10000),\n",
       " (3, 'RJ Rahul', 21, 10000)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=conn.execute(\"insert into Employee values(4,'Naveed Unjum',21,0)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=conn.execute(\"update Employee set empname='Naveed Unjum(Unemployed)' where salary=0\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000),\n",
       " (2, 'Murtaza Nazir', 21, 10000),\n",
       " (3, 'RJ Rahul', 21, 10000),\n",
       " (4, 'Naveed Unjum(Unemployed)', 21, 0)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Adding Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=conn.execute(\"alter table Employee add column dept varchar(3)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000, None),\n",
       " (2, 'Murtaza Nazir', 21, 10000, None),\n",
       " (3, 'RJ Rahul', 21, 10000, None),\n",
       " (4, 'Naveed Unjum(Unemployed)', 21, 0, None)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "near \"drop\": syntax error",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-15-a4e0b6d3eb60>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mcur\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"alter table Employee drop dept\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m: near \"drop\": syntax error"
     ]
    }
   ],
   "source": [
    "cur=conn.execute(\"alter table Employee drop dept\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000, None),\n",
       " (2, 'Murtaza Nazir', 21, 10000, None),\n",
       " (3, 'RJ Rahul', 21, 10000, None),\n",
       " (4, 'Naveed Unjum(Unemployed)', 21, 0, None)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Regex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000, None), (5, 'aNKUR gOSWAMI', 19, 50000, None)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee where empname like \"%KUR%\"')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4, 'Naveed Unjum(Unemployed)', 21, 0, None)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee where empname like \"n%)\"')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000, None),\n",
       " (2, 'Murtaza Nazir', 21, 10000, None),\n",
       " (3, 'RJ Rahul', 21, 10000, None),\n",
       " (5, 'aNKUR gOSWAMI', 19, 50000, None)]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee where empname like \"%r%\"')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3, 'RJ Rahul', 21, 10000, None)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee where empname like \"%u_\"')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Ankur Goswami', 19, 200000, None),\n",
       " (2, 'Murtaza Nazir', 21, 10000, None),\n",
       " (3, 'RJ Rahul', 21, 10000, None),\n",
       " (5, 'aNKUR gOSWAMI', 19, 50000, None)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur=conn.execute('select * from Employee where empname not like \"______________%\"')\n",
    "cur.fetchall()"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}