{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Table\n",
    "\n",
    "Before getting into action with respect to basic DML and queries or CRUD operations, we need to prepare tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/Ejuzd4QQsHg?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/Ejuzd4QQsHg?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this time we have not covered DDL yet. All database operations related to managing tables come under DDL.\n",
    "\n",
    "For now, let's just create the table by copy pasting below `CREATE TABLE` statement. We will get into concepts as part of the subsequent sections.\n",
    "\n",
    "* Connect to the database.\n",
    "* Create the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db\n"
     ]
    }
   ],
   "source": [
    "%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>table_type</th>\n",
       "        <th>self_referencing_column_name</th>\n",
       "        <th>reference_generation</th>\n",
       "        <th>user_defined_type_catalog</th>\n",
       "        <th>user_defined_type_schema</th>\n",
       "        <th>user_defined_type_name</th>\n",
       "        <th>is_insertable_into</th>\n",
       "        <th>is_typed</th>\n",
       "        <th>commit_action</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>courses</td>\n",
       "        <td>BASE TABLE</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>BASE TABLE</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('itversity_sms_db', 'public', 'courses', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n",
       " ('itversity_sms_db', 'public', 'users', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM information_schema.tables \n",
    "WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "DROP TABLE IF EXISTS users;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>table_type</th>\n",
       "        <th>self_referencing_column_name</th>\n",
       "        <th>reference_generation</th>\n",
       "        <th>user_defined_type_catalog</th>\n",
       "        <th>user_defined_type_schema</th>\n",
       "        <th>user_defined_type_name</th>\n",
       "        <th>is_insertable_into</th>\n",
       "        <th>is_typed</th>\n",
       "        <th>commit_action</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>courses</td>\n",
       "        <td>BASE TABLE</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('itversity_sms_db', 'public', 'courses', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM information_schema.tables \n",
    "WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE users (\n",
    "    user_id SERIAL PRIMARY KEY,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN DEFAULT FALSE,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n",
    "    is_active BOOLEAN DEFAULT FALSE,\n",
    "    create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Let us validate the objects that are created in the underlying database. We can either run query against **information_schema** or use Database Explorer in **SQL Workbench** or even `psql`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>table_type</th>\n",
       "        <th>self_referencing_column_name</th>\n",
       "        <th>reference_generation</th>\n",
       "        <th>user_defined_type_catalog</th>\n",
       "        <th>user_defined_type_schema</th>\n",
       "        <th>user_defined_type_name</th>\n",
       "        <th>is_insertable_into</th>\n",
       "        <th>is_typed</th>\n",
       "        <th>commit_action</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>BASE TABLE</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('itversity_sms_db', 'public', 'users', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM information_schema.tables \n",
    "WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>column_name</th>\n",
       "        <th>ordinal_position</th>\n",
       "        <th>column_default</th>\n",
       "        <th>is_nullable</th>\n",
       "        <th>data_type</th>\n",
       "        <th>character_maximum_length</th>\n",
       "        <th>character_octet_length</th>\n",
       "        <th>numeric_precision</th>\n",
       "        <th>numeric_precision_radix</th>\n",
       "        <th>numeric_scale</th>\n",
       "        <th>datetime_precision</th>\n",
       "        <th>interval_type</th>\n",
       "        <th>interval_precision</th>\n",
       "        <th>character_set_catalog</th>\n",
       "        <th>character_set_schema</th>\n",
       "        <th>character_set_name</th>\n",
       "        <th>collation_catalog</th>\n",
       "        <th>collation_schema</th>\n",
       "        <th>collation_name</th>\n",
       "        <th>domain_catalog</th>\n",
       "        <th>domain_schema</th>\n",
       "        <th>domain_name</th>\n",
       "        <th>udt_catalog</th>\n",
       "        <th>udt_schema</th>\n",
       "        <th>udt_name</th>\n",
       "        <th>scope_catalog</th>\n",
       "        <th>scope_schema</th>\n",
       "        <th>scope_name</th>\n",
       "        <th>maximum_cardinality</th>\n",
       "        <th>dtd_identifier</th>\n",
       "        <th>is_self_referencing</th>\n",
       "        <th>is_identity</th>\n",
       "        <th>identity_generation</th>\n",
       "        <th>identity_start</th>\n",
       "        <th>identity_increment</th>\n",
       "        <th>identity_maximum</th>\n",
       "        <th>identity_minimum</th>\n",
       "        <th>identity_cycle</th>\n",
       "        <th>is_generated</th>\n",
       "        <th>generation_expression</th>\n",
       "        <th>is_updatable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>user_id</td>\n",
       "        <td>1</td>\n",
       "        <td>nextval(&#x27;users_user_id_seq&#x27;::regclass)</td>\n",
       "        <td>NO</td>\n",
       "        <td>integer</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>32</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>int4</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>user_first_name</td>\n",
       "        <td>2</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>character varying</td>\n",
       "        <td>30</td>\n",
       "        <td>120</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>varchar</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>user_last_name</td>\n",
       "        <td>3</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>character varying</td>\n",
       "        <td>30</td>\n",
       "        <td>120</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>varchar</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>user_email_id</td>\n",
       "        <td>4</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>character varying</td>\n",
       "        <td>50</td>\n",
       "        <td>200</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>varchar</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>user_email_validated</td>\n",
       "        <td>5</td>\n",
       "        <td>false</td>\n",
       "        <td>YES</td>\n",
       "        <td>boolean</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>bool</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>5</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>user_password</td>\n",
       "        <td>6</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>character varying</td>\n",
       "        <td>200</td>\n",
       "        <td>800</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>varchar</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>user_role</td>\n",
       "        <td>7</td>\n",
       "        <td>&#x27;U&#x27;::character varying</td>\n",
       "        <td>NO</td>\n",
       "        <td>character varying</td>\n",
       "        <td>1</td>\n",
       "        <td>4</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>varchar</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>7</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>is_active</td>\n",
       "        <td>8</td>\n",
       "        <td>false</td>\n",
       "        <td>YES</td>\n",
       "        <td>boolean</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>bool</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>8</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>create_ts</td>\n",
       "        <td>9</td>\n",
       "        <td>CURRENT_TIMESTAMP</td>\n",
       "        <td>YES</td>\n",
       "        <td>timestamp without time zone</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>timestamp</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>9</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>public</td>\n",
       "        <td>users</td>\n",
       "        <td>last_updated_ts</td>\n",
       "        <td>10</td>\n",
       "        <td>CURRENT_TIMESTAMP</td>\n",
       "        <td>YES</td>\n",
       "        <td>timestamp without time zone</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>itversity_sms_db</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>timestamp</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>10</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('itversity_sms_db', 'public', 'users', 'user_id', 1, \"nextval('users_user_id_seq'::regclass)\", 'NO', 'integer', None, None, 32, 2, 0, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'int4', None, None, None, None, '1', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'user_first_name', 2, None, 'NO', 'character varying', 30, 120, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'varchar', None, None, None, None, '2', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'user_last_name', 3, None, 'NO', 'character varying', 30, 120, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'varchar', None, None, None, None, '3', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'user_email_id', 4, None, 'NO', 'character varying', 50, 200, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'varchar', None, None, None, None, '4', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'user_email_validated', 5, 'false', 'YES', 'boolean', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'bool', None, None, None, None, '5', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'user_password', 6, None, 'YES', 'character varying', 200, 800, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'varchar', None, None, None, None, '6', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'user_role', 7, \"'U'::character varying\", 'NO', 'character varying', 1, 4, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'varchar', None, None, None, None, '7', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'is_active', 8, 'false', 'YES', 'boolean', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'bool', None, None, None, None, '8', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'create_ts', 9, 'CURRENT_TIMESTAMP', 'YES', 'timestamp without time zone', None, None, None, None, None, 6, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'timestamp', None, None, None, None, '9', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('itversity_sms_db', 'public', 'users', 'last_updated_ts', 10, 'CURRENT_TIMESTAMP', 'YES', 'timestamp without time zone', None, None, None, None, None, 6, None, None, None, None, None, None, None, None, None, None, None, 'itversity_sms_db', 'pg_catalog', 'timestamp', None, None, None, None, '10', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM information_schema.columns \n",
    "WHERE table_name = 'users'\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>user_id</th>\n",
       "        <th>user_first_name</th>\n",
       "        <th>user_last_name</th>\n",
       "        <th>user_email_id</th>\n",
       "        <th>user_email_validated</th>\n",
       "        <th>user_password</th>\n",
       "        <th>user_role</th>\n",
       "        <th>is_active</th>\n",
       "        <th>create_ts</th>\n",
       "        <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users"
   ]
  }
 ],
 "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.6.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}