SQLite_QuickStart.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Quick Start\n",
    "\n",
    "This quick start is a complete example to show how to\n",
    "- create a SQLite database; \n",
    "- creat a data table;\n",
    "- insert data into the table;\n",
    "- query data from the table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a SQLite Database\n",
    "\n",
    "For practice purposes, it's advisable to create your own database, so that you are free to perform any operations on it. If using the SQLite shell, we can apply the ***open*** command to both create a SQLite database or open it in case it already exists just like:\n",
    "\n",
    "> sqlite> .open testdb\n",
    "\n",
    "Similaryly, we can use ipython-sql to the same thing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @writers.db3'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///writers.db3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table\n",
    "\n",
    "***%%sql*** let you use multiple SQL statements inside a single cell. \n",
    "\n",
    "It is now time to create one using a standard SQL command – CREATE TABLE. If the table already existed in the database, an error will pop up. In addition, we set ***PRIMARY KEY*** on USERID to prevent from inserting duplicate writers into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE writer(\n",
    "    FirstName VARCHAR(50) NOT NULL,\n",
    "    LastName VARCHAR(50) NOT NULL,  \n",
    "    USERID int  NOT NULL UNIQUE, \n",
    "    PRIMARY KEY (USERID)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add data to the table\n",
    "\n",
    "The table we have just created is empty. Now we insert some sample data inside it. To populate this data in the form of rows, we use the command INSERT."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);\n",
    "INSERT INTO writer VALUES ('Lin', 'Han', 1996);\n",
    "INSERT INTO writer VALUES ('Peter', 'Brecht', 1978);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write the First Query\n",
    "\n",
    "Let us now turn our attention to writing a simple query to check the results of our previous operations in which we created a table and inserted three rows of data into it. For this, we would use the command called SELECT.\n",
    "\n",
    "we can put the query result into a variable such as the following sqlres."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>FirstName</th>\n",
       "        <th>LastName</th>\n",
       "        <th>USERID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>William</td>\n",
       "        <td>Shakespeare</td>\n",
       "        <td>1616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lin</td>\n",
       "        <td>Han</td>\n",
       "        <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter</td>\n",
       "        <td>Brecht</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('William', 'Shakespeare', 1616),\n",
       " ('Lin', 'Han', 1996),\n",
       " ('Peter', 'Brecht', 1978)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlres = %sql SELECT * from writer\n",
    "sqlres"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You also can select the specific colummns using their names just like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>FirstName</th>\n",
       "        <th>LastName</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>William</td>\n",
       "        <td>Shakespeare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lin</td>\n",
       "        <td>Han</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter</td>\n",
       "        <td>Brecht</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('William', 'Shakespeare'), ('Lin', 'Han'), ('Peter', 'Brecht')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlres = %sql SELECT FirstName, LastName from writer\n",
    "sqlres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE grades(\n",
    "    studentid int NOT NULL,\n",
    "    course VARCHAR(50) NOT NULL,  \n",
    "    grade DOUBLE NOT NULL, \n",
    "    credits int NOT NULL\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>FirstName</th>\n",
       "        <th>LastName</th>\n",
       "        <th>USERID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>William</td>\n",
       "        <td>Shakespeare</td>\n",
       "        <td>1616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lin</td>\n",
       "        <td>Han</td>\n",
       "        <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter</td>\n",
       "        <td>Brecht</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('William', 'Shakespeare', 1616),\n",
       " ('Lin', 'Han', 1996),\n",
       " ('Peter', 'Brecht', 1978)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlres = %sql SELECT * from writer\n",
    "sqlres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>studentid</th>\n",
       "        <th>course</th>\n",
       "        <th>grade</th>\n",
       "        <th>credits</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlres = %sql SELECT * from grades\n",
    "sqlres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO grades VALUES (1, 'PY205', 3.8, 3);\n",
    "INSERT INTO grades VALUES (1, 'PY411', 3.2, 4);\n",
    "INSERT INTO grades VALUES (1, 'PY412', 3.1, 4);\n",
    "INSERT INTO grades VALUES (1, 'E101', 3.8, 2);\n",
    "INSERT INTO grades VALUES (1, 'CS216', 3.5, 3);\n",
    "INSERT INTO grades VALUES (2, 'PY205', 3.1, 3);\n",
    "INSERT INTO grades VALUES (2, 'PY411', 2.8, 4);\n",
    "INSERT INTO grades VALUES (2, 'PY412', 2.4, 4);\n",
    "INSERT INTO grades VALUES (2, 'E101', 2.9, 2);\n",
    "INSERT INTO grades VALUES (2, 'CS216', 3.3, 3);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>studentid</th>\n",
       "        <th>course</th>\n",
       "        <th>grade</th>\n",
       "        <th>credits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>PY205</td>\n",
       "        <td>3.8</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>PY411</td>\n",
       "        <td>3.2</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>PY412</td>\n",
       "        <td>3.1</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>E101</td>\n",
       "        <td>3.8</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>CS216</td>\n",
       "        <td>3.5</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>PY205</td>\n",
       "        <td>3.1</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>PY411</td>\n",
       "        <td>2.8</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>PY412</td>\n",
       "        <td>2.4</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>E101</td>\n",
       "        <td>2.9</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>CS216</td>\n",
       "        <td>3.3</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'PY205', 3.8, 3),\n",
       " (1, 'PY411', 3.2, 4),\n",
       " (1, 'PY412', 3.1, 4),\n",
       " (1, 'E101', 3.8, 2),\n",
       " (1, 'CS216', 3.5, 3),\n",
       " (2, 'PY205', 3.1, 3),\n",
       " (2, 'PY411', 2.8, 4),\n",
       " (2, 'PY412', 2.4, 4),\n",
       " (2, 'E101', 2.9, 2),\n",
       " (2, 'CS216', 3.3, 3)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlres = %sql SELECT * from grades\n",
    "sqlres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE demographies(\n",
    "    studentid int NOT NULL,\n",
    "    gender VARCHAR(50) NOT NULL,  \n",
    "    credits int NOT NULL\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO demographies VALUES (1, 'm', 22);\n",
    "INSERT INTO demographies VALUES (2, 'f', 19);\n",
    "INSERT INTO demographies VALUES (3, 'm', 20);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///writers.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>studentid</th>\n",
       "        <th>gender</th>\n",
       "        <th>credits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>m</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>f</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>m</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'm', 22), (2, 'f', 19), (3, 'm', 20)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * from demographies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}