SQLite_Tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLite Tutorial\n",
    "\n",
    "Welcome to my SQLite tutorial, but it is also a generic SQL primer. It is aimed at those familiar with programming but with no experience with relational databases. It covers using SQL as a data storage and query system, assuming you already have datasets in CSV format that you would like to query and analyze.\n",
    "\n",
    "A future tutorial will cover how to interface with SQLite from Python and use a relational database as a data storage and manipulation layer for your programs.\n",
    "\n",
    "This is a Jupyter notebook with a SQLite backend. We'll be typing commands here into a sqlite3 shell, which you can follow along at home by executing the `sqlite3` command on your command line, or by installing Jupyter and the [SQLite backend](https://github.com/brownan/sqlite3-kernel).\n",
    "\n",
    "When the ``sqlite3`` command is executed with no parameters, we interface with an in-memory database that disappears when you exit. It's handy for experimenting, but we have to create all our tables and import all our data each time.\n",
    "\n",
    "So let's get started!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Relational Databases\n",
    "\n",
    "First, a bit about relational databases.\n",
    "\n",
    "* A relational database organizes its data into *tables*, sometimes called *relations*.\n",
    "* Each table is made up of a fixed number of *columns* and zero or more *rows*\n",
    "* Each column has a name, and generally has a data type (SQLite is a bit lenient on data types, but a good database design keeps a uniform type on every row in a column)\n",
    "* A table usually has a *primary key*—a column or set of columns that is used to uniquely identify a row. Each row primary key must be unique througout that table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So let's get started by importing some data. Let's say we have a CSV file that holds students and their grades in certain courses:\n",
    "\n",
    "```\n",
    "studentid,course,grade,credits\n",
    "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\n",
    "```\n",
    "Both students took the same courses. Student 1 did pretty well, student 2 not as well. Grades are assigned a numerical value with A being a 4, B being a 3, etc.\n",
    "\n",
    "If this data is in a file called ``grades.csv`` then we can import it with the following commands:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": []
    }
   ],
   "source": [
    ".mode csv\n",
    ".import grades.csv grades"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Commands starting with a ``.`` are special commands, interpreted by the sqlite shell itself; they are not SQL. These commands don't have any output but change how later commands work. You can use ``.help`` for a full list of special commands, or see [the sqlite3 docs on special commands](https://www.sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have to set mode used for importing with the ``.mode`` special command. This tells sqlite that files it's importing are CSV files.\n",
    "\n",
    "The second parameter to the ``.import`` command is the table name. Since it doesn't exist, it will be created. The first row of the csv file will be used as the column names."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting data\n",
    "We can now select the table and see what's in it using a SELECT statement, which is how you query data from a SQL database. Shown below is one of the simplest SELECT statements. It simply shows all rows in the given table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1,PY205,3.8,3\r\n",
      "1,PY411,3.2,4\r\n",
      "1,PY412,3.1,4\r\n",
      "1,E101,3.8,2\r\n",
      "1,CS216,3.5,3\r\n",
      "2,PY205,3.1,3\r\n",
      "2,PY411,2.8,4\r\n",
      "2,PY412,2.4,4\r\n",
      "2,E101,2.9,2\r\n",
      "2,CS216,3.3,3\r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's not very pretty output for us to view, so let's run these commands to tune the output mode:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": []
    }
   ],
   "source": [
    ".mode columns\n",
    ".headers on"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits   \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3         \r\n",
      "1           PY411       3.2         4         \r\n",
      "1           PY412       3.1         4         \r\n",
      "1           E101        3.8         2         \r\n",
      "1           CS216       3.5         3         \r\n",
      "2           PY205       3.1         3         \r\n",
      "2           PY411       2.8         4         \r\n",
      "2           PY412       2.4         4         \r\n",
      "2           E101        2.9         2         \r\n",
      "2           CS216       3.3         3         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check out the [SELECT documentation](https://www.sqlite.org/lang_select.html) and take a look at the syntax flowchart. It's not important to understand it all now, but keep that flowchart handy as we learn more concepts.\n",
    "\n",
    "The important bits to notice are the different clauses:\n",
    "* **SELECT** - lists the columns to return in the results, or ``*`` for all columns. These can actually be any expression, which you can use to compute values, as we'll see in a bit.\n",
    "* **FROM** - defines which tables to use as input to the query. If you have more than one table, then the tables are joined. More on joins later.\n",
    "* **WHERE** - Defines conditional expressions that filter the rows from the input tables\n",
    "* **GROUP BY** - Used in aggregate queries. More on this later.\n",
    "* **HAVING** - Expressions used to filter groups on an aggregate query.\n",
    "* **ORDER BY** - Expressions used to sort the results before returning them\n",
    "* **LIMIT** - Places limits or returns a slice of the results.\n",
    "\n",
    "We will be explaining each of these clauses as we introduce their respective concepts."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering rows with a WHERE clause\n",
    "You can filter rows using a WHERE clause in your query. You can use simple comparison expressions here. You can have multiple conditions separated by ``AND`` and ``OR`` keywords."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits   \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3         \r\n",
      "1           PY411       3.2         4         \r\n",
      "1           PY412       3.1         4         \r\n",
      "1           E101        3.8         2         \r\n",
      "1           CS216       3.5         3         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades WHERE studentid=1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits   \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3         \r\n",
      "2           PY205       3.1         3         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades WHERE course='PY205';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits   \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "2           E101        2.9         2         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades WHERE studentid=2 AND course='E101';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits   \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3         \r\n",
      "1           E101        3.8         2         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades WHERE grade>3.5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Expressions and the SELECT clause\n",
    "You can list specific columns you want in your results in the SELECT clause, seprated by columns. Actually, you can put any expression you want in there, computing results from multiple columns.\n",
    "\n",
    "From the [SQLite documentation](https://www.sqlite.org/lang_select.html#resultset): \n",
    "\n",
    "> The list of expressions between the SELECT and FROM keywords is known as the result expression list. If a result expression is the special expression \"*\" then all columns in the input data are substituted for that one expression."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   grade     \r\n",
      "----------  ----------\r\n",
      "1           3.8       \r\n",
      "1           3.2       \r\n",
      "1           3.1       \r\n",
      "1           3.8       \r\n",
      "1           3.5       \r\n",
      "2           3.1       \r\n",
      "2           2.8       \r\n",
      "2           2.4       \r\n",
      "2           2.9       \r\n",
      "2           3.3       \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, grade FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This example shows computing a column from an expression. The grade points earned for a course is the grade earned times the credits. This quantity is used in computing grade point averages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade*credits\r\n",
      "----------  ----------  -------------\r\n",
      "1           PY205       11.4         \r\n",
      "1           PY411       12.8         \r\n",
      "1           PY412       12.4         \r\n",
      "1           E101        7.6          \r\n",
      "1           CS216       10.5         \r\n",
      "2           PY205       9.3          \r\n",
      "2           PY411       11.2         \r\n",
      "2           PY412       9.6          \r\n",
      "2           E101        5.8          \r\n",
      "2           CS216       9.9          \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, course, grade*credits FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can change the result column name by giving an alternative name like so. This is useful for clarity, but also you can refer to these names later in the query in the WHERE clause and a few other clauses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      gradepoints\r\n",
      "----------  ----------  -----------\r\n",
      "1           PY205       11.4       \r\n",
      "1           PY411       12.8       \r\n",
      "1           PY412       12.4       \r\n",
      "1           E101        7.6        \r\n",
      "1           CS216       10.5       \r\n",
      "2           PY205       9.3        \r\n",
      "2           PY411       11.2       \r\n",
      "2           PY412       9.6        \r\n",
      "2           E101        5.8        \r\n",
      "2           CS216       9.9        \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, course, grade*credits AS gradepoints FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, take a look at the reference page on [Core Functions](https://www.sqlite.org/lang_corefunc.html) and the reference page on [Expressions](https://www.sqlite.org/lang_expr.html). The core functions can be used in any expression, and the expression syntax has other features. Here are two examples.\n",
    "\n",
    "Using a CASE expression to transform numerical grades to letters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      lettergrade\r\n",
      "----------  ----------  -----------\r\n",
      "1           PY205       A          \r\n",
      "1           PY411       B          \r\n",
      "1           PY412       B          \r\n",
      "1           E101        A          \r\n",
      "1           CS216       B          \r\n",
      "2           PY205       B          \r\n",
      "2           PY411       B          \r\n",
      "2           PY412       C          \r\n",
      "2           E101        B          \r\n",
      "2           CS216       B          \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, course,\n",
    "       CASE WHEN grade<0.66 THEN 'F'\n",
    "            WHEN grade<1.66 THEN 'D'\n",
    "            WHEN grade<2.66 THEN 'C'\n",
    "            WHEN grade<3.66 THEN 'B'\n",
    "            ELSE 'A' END\n",
    "                AS lettergrade\n",
    "    FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using a LIKE expression in the WHERE clause to match all Physics classes. LIKE expressions do pattern matching using the percent sign as a wildcard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits   \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3         \r\n",
      "1           PY411       3.2         4         \r\n",
      "1           PY412       3.1         4         \r\n",
      "2           PY205       3.1         3         \r\n",
      "2           PY411       2.8         4         \r\n",
      "2           PY412       2.4         4         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades WHERE course LIKE \"PY%\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you know how to do simple selects and queries on a table, filtering results and doing some simple computations. Take some time to play with various expressions and queries in a shell until you feel comfortable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping and Aggregate Queries\n",
    "So you can now select rows from the table and compute expressions per-row, but what if you want to compute a student's GPA from all their courses? Because each (student,course) pair is its own row, we can't computer a GPA because that involves data from more than one row.\n",
    "\n",
    "SQL provides a grouping mechanism that allows you to do aggregate computations on each group. This is done with the GROUP BY clause, which goes after the FROM and WHERE clauses.\n",
    "\n",
    "The GROUP BY clause takes one or more expressions, separated by commas, that define the groups. Typically, these expressions will be column names but you can compute values with expressions, too.\n",
    "\n",
    "SQL will then take each row and assign it a group based on the GROUP BY expression(s). **The results of the query will be one row per group.** From the [SQLite docs](https://www.sqlite.org/lang_select.html#resultset):\n",
    "> [...] each of the expressions specified as part of the GROUP BY clause is evaluated for each row of the dataset. Each row is then assigned to a \"group\" based on the results; rows for which the results of evaluating the GROUP BY expressions are the same get assigned to the same group.\n",
    "\n",
    "Using a GROUP BY clause makes the query into an *aggregate query*. In your SELECT clause, you can then use one or more [aggregate functions](https://www.sqlite.org/lang_aggfunc.html). The aggregate functions operate over each row in the group. So, for example, you can compute the average grade per student like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   AVG(grade)\r\n",
      "----------  ----------\r\n",
      "1           3.48      \r\n",
      "2           2.9       \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, AVG(grade) FROM grades GROUP BY studentid;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   COUNT(course)\r\n",
      "----------  -------------\r\n",
      "1           5            \r\n",
      "2           5            \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, COUNT(course) FROM grades GROUP BY studentid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we know enough to use grouping to compute each student's GPA:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   GPA       \r\n",
      "----------  ----------\r\n",
      "1           3.41875   \r\n",
      "2           2.8625    \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, SUM(grade*credits)/SUM(credits) AS GPA FROM grades GROUP BY studentid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This takes the total gradepoints of each group and divides them by the total credits in that group. Since the grouping is by studentid, each student is a group, and each group contains the rows for that student. So any aggregate functions operate on the rows for each student independently."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you combine the WHERE clause with an aggregate query, the WHERE clause filters the input rows before grouping. So here is the GPA of all students just for their Physics classes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   GPA             \r\n",
      "----------  ----------------\r\n",
      "1           3.32727272727273\r\n",
      "2           2.73636363636364\r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, SUM(grade*credits)/SUM(credits) AS GPA\n",
    "    FROM grades\n",
    "    WHERE course LIKE \"PY%\"\n",
    "    GROUP BY studentid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you add a HAVING clause, this filters the groups. It's essentially like the WHERE clause except it applies *after* grouping instead of before. You can use aggregate expressions in the HAVING clause.\n",
    "\n",
    "This selects students that have a GPA greater than 3. Notice how we don't actually select the GPA into the results, we just get the studentids, and calculate the GPA in the HAVING clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid \r\n",
      "----------\r\n",
      "1         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid FROM grades GROUP BY studentid HAVING SUM(grade*credits)/SUM(credits) >= 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also refer to a column name in other clauses. So if you *do* want the GPA in the results, to avoid having to duplicate the expression in both clauses, give it a name and refer to it by name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   GPA       \r\n",
      "----------  ----------\r\n",
      "1           3.41875   \r\n",
      "2           2.8625    \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, SUM(grade*credits)/SUM(credits) AS GPA \n",
    "    FROM grades\n",
    "    GROUP BY studentid\n",
    "    HAVING GPA >= 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Two more notes on Aggregate Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1.** if you use an *aggregate function* but don't use a GROUP BY clause, the query is still an aggregate query, but with one large group containing every row.\n",
    "\n",
    "This is the total GPA of all students, which may or may not be meaningful."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "GPA       \r\n",
      "----------\r\n",
      "3.140625  \r\n"
     ]
    }
   ],
   "source": [
    "SELECT SUM(grade*credits)/SUM(credits) AS GPA FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2.** If you select on a column that is neither an aggregate function nor a column named in the GROUP BY, you're essentially asking about a column for which there may be multiple results. This is allowed, but since the results only give one row per group, SQLite will pick an arbitrary row within the group to return, which is usually not meaningful.\n",
    "\n",
    "From the [SQLite docs](https://www.sqlite.org/lang_select.html#resultset):\n",
    "> Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.\n",
    "\n",
    "\n",
    "For example, this query groups by students and also selects the course column. But since each student takes more than one course, this will return an arbitrary course for each student."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course    \r\n",
      "----------  ----------\r\n",
      "1           PY205     \r\n",
      "2           PY205     \r\n"
     ]
    }
   ],
   "source": [
    "SELECT studentid, course FROM grades GROUP BY studentid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Joins are one of the most powerful features of SQL, but are also tricky to master. They allow you to join two or more tables of data together. Before we get started, we'll need another table of data.\n",
    "\n",
    "Let's import the contents of demographics.csv into our database, this one with just 3 rows:\n",
    "\n",
    "```\n",
    "studentid,gender,age\n",
    "1,m,22\n",
    "2,f,19\n",
    "3,m,20\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": []
    }
   ],
   "source": [
    ".mode csv\n",
    ".import demographics.csv demographics\n",
    ".mode columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   gender      age       \r\n",
      "----------  ----------  ----------\r\n",
      "1           m           22        \r\n",
      "2           f           19        \r\n",
      "3           m           20        \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM demographics;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can join the tables together. At its core, a join is simply a cartesian-product of the rows in two input tables. So for every row in one input table, every row in the second table will be returned.\n",
    "\n",
    "From the [SQLite docs](https://www.sqlite.org/lang_select.html#fromclause):\n",
    "> There is a row in the cartesian product dataset formed by combining each unique combination of a row from the left-hand and right-hand datasets. In other words, if the left-hand dataset consists of Nleft rows of Mleft columns, and the right-hand dataset of Nright rows of Mright columns, then the cartesian product is a dataset of Nleft×Nright rows, each containing Mleft+Mright columns.\n",
    "\n",
    "The following is a nonsensical query demonstrating this, but it's an important mental model to have for how joins work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits     studentid   gender      age       \r\n",
      "----------  ----------  ----------  ----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3           1           m           22        \r\n",
      "1           PY205       3.8         3           2           f           19        \r\n",
      "1           PY205       3.8         3           3           m           20        \r\n",
      "1           PY411       3.2         4           1           m           22        \r\n",
      "1           PY411       3.2         4           2           f           19        \r\n",
      "1           PY411       3.2         4           3           m           20        \r\n",
      "1           PY412       3.1         4           1           m           22        \r\n",
      "1           PY412       3.1         4           2           f           19        \r\n",
      "1           PY412       3.1         4           3           m           20        \r\n",
      "1           E101        3.8         2           1           m           22        \r\n",
      "1           E101        3.8         2           2           f           19        \r\n",
      "1           E101        3.8         2           3           m           20        \r\n",
      "1           CS216       3.5         3           1           m           22        \r\n",
      "1           CS216       3.5         3           2           f           19        \r\n",
      "1           CS216       3.5         3           3           m           20        \r\n",
      "2           PY205       3.1         3           1           m           22        \r\n",
      "2           PY205       3.1         3           2           f           19        \r\n",
      "2           PY205       3.1         3           3           m           20        \r\n",
      "2           PY411       2.8         4           1           m           22        \r\n",
      "2           PY411       2.8         4           2           f           19        \r\n",
      "2           PY411       2.8         4           3           m           20        \r\n",
      "2           PY412       2.4         4           1           m           22        \r\n",
      "2           PY412       2.4         4           2           f           19        \r\n",
      "2           PY412       2.4         4           3           m           20        \r\n",
      "2           E101        2.9         2           1           m           22        \r\n",
      "2           E101        2.9         2           2           f           19        \r\n",
      "2           E101        2.9         2           3           m           20        \r\n",
      "2           CS216       3.3         3           1           m           22        \r\n",
      "2           CS216       3.3         3           2           f           19        \r\n",
      "2           CS216       3.3         3           3           m           20        \r\n"
     ]
    }
   ],
   "source": [
    "SELECT g.*, d.* FROM grades g JOIN demographics d;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that there are 10 rows in the grades table, and 3 rows in the demographics table, so the result set has 30 rows. Rows in the first table are repeated for each row in the second table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This obviously is not useful, but we can filter this out by selecting only where the studentid column of the first table is equal to the studentid column of the second table. This is called a *join condition* and will get us the demographics and grades of the respective students."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits     gender      age       \r\n",
      "----------  ----------  ----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3           m           22        \r\n",
      "1           PY411       3.2         4           m           22        \r\n",
      "1           PY412       3.1         4           m           22        \r\n",
      "1           E101        3.8         2           m           22        \r\n",
      "1           CS216       3.5         3           m           22        \r\n",
      "2           PY205       3.1         3           f           19        \r\n",
      "2           PY411       2.8         4           f           19        \r\n",
      "2           PY412       2.4         4           f           19        \r\n",
      "2           E101        2.9         2           f           19        \r\n",
      "2           CS216       3.3         3           f           19        \r\n"
     ]
    }
   ],
   "source": [
    "SELECT g.*, d.gender, d.age\n",
    "    FROM grades g JOIN demographics d ON g.studentid=d.studentid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This performs a regular join, also known as an *inner join* or *cross join*. Only rows that match the join condition are returned.\n",
    "\n",
    "Note: In SQLite, there is no difference for an inner join in putting the join condition after the ON keyword or putting it in the WHERE clause. It makes a difference for other types of joins and other database systems though, so it's a good idea to put your join condition in the JOIN clause using the ON keyword, instead of the WHERE clause!\n",
    "\n",
    "Note: there is a difference between inner joins and cross joins in other database systems, but they are equivalent in SQLite. Inner joins are usually what you want, which is why they are the default when you just say \"JOIN\". You can even just separate table names with a comma and leave the join keyword out entirely."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joins and grouping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Joins work with groups just as you'd expect. All joins happen before grouping, so you can get a student's demographic information along with their GPA in one query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   GPA         gender      age       \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "1           3.41875     m           22        \r\n",
      "2           2.8625      f           19        \r\n"
     ]
    }
   ],
   "source": [
    "SELECT g.studentid, SUM(g.grade*g.credits)/SUM(g.credits) AS GPA, d.gender, d.age\n",
    "    FROM grades g JOIN demographics d ON g.studentid=d.studentid\n",
    "    GROUP BY g.studentid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "If you're wondering why we couldn't just put the demographics data into the grades table, there are a couple of reasons. First is just the principle of having data organized into tables of a single purpose. But more importantly is to reduce duplication of data. If you had demographic data in the grades table, it would duplicate all the demographic data for every course a student takes, which makes it harder to change, and introduces consistency issues if the data doesn't match in every row. It's better to have one authorative source for data and let the database worry about joining and cross referencing when necessary.\n",
    "\n",
    "The rule of thumb is to look at what's unique in a table. For grades, it's the tuple of (studentid, course) columns. There should never be two rows with the same (studentid, course). (In a real system, you would also have a semester identifier in there because students can and do take courses over again)\n",
    "\n",
    "For the demographics table, the unique column is just the \"studentid\" column.\n",
    "\n",
    "We call the unique columns in a table a \"key\", and usually you can identify a single *primary key*. If two datasets have different primary keys, then they typically should belong in different tables. If two datasets have the same primary key, then it may make sense to combine them into one table, but organizationally it may still make sense to keep them separate.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Outer Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Left Outer joins are the other join type supported by SQLite. It's not as useful but it's still good to know about it.\n",
    "\n",
    "An inner join as seen above is easy to think about as a simple filter on the cartesian product of two tables. A left outer join, however, is useful when you want to return *every* row on the left-hand table *whether or not* a matching row is found on the right-hand table.\n",
    "\n",
    "Notice the demographics table we created above had a third student in it, but that student wasn't in any of our results, because he didn't match any rows in the grades table.\n",
    "\n",
    "Here's how we can get every student, and if they have one, their GPA:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   GPA         gender      age       \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "3                       m           20        \r\n",
      "1           3.41875     m           22        \r\n",
      "2           2.8625      f           19        \r\n"
     ]
    }
   ],
   "source": [
    "SELECT d.studentid, SUM(g.grade*g.credits)/sum(g.credits) AS GPA, d.gender, d.age\n",
    "    FROM demographics d LEFT JOIN grades g ON d.studentid=g.studentid\n",
    "    GROUP BY g.studentid;\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And there you have it. Student 3's GPA is left blank because he didn't have any matching courses in the grades table.\n",
    "\n",
    "Note that we had to put the demographics table first in the FROM clause because that's the table that we want every row of, even if no matching row is found in the other table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The DISTINCT keyword"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   grade       course    \r\n",
      "----------  ----------  ----------\r\n",
      "1           3.8         PY205     \r\n",
      "1           3.2         PY411     \r\n",
      "1           3.1         PY412     \r\n",
      "1           3.8         E101      \r\n",
      "1           3.5         CS216     \r\n",
      "2           3.1         PY205     \r\n",
      "2           2.8         PY411     \r\n",
      "2           2.4         PY412     \r\n",
      "2           2.9         E101      \r\n",
      "2           3.3         CS216     \r\n"
     ]
    }
   ],
   "source": [
    "SELECT DISTINCT studentid, grade, course FROM grades;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ordering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits   \r\n",
      "----------  ----------  ----------  ----------\r\n",
      "1           PY412       3.1         4         \r\n",
      "2           PY412       2.4         4         \r\n",
      "1           PY411       3.2         4         \r\n",
      "2           PY411       2.8         4         \r\n",
      "1           PY205       3.8         3         \r\n",
      "2           PY205       3.1         3         \r\n",
      "1           E101        3.8         2         \r\n",
      "2           E101        2.9         2         \r\n",
      "1           CS216       3.5         3         \r\n",
      "2           CS216       3.3         3         \r\n"
     ]
    }
   ],
   "source": [
    "SELECT * FROM grades\n",
    "ORDER BY course DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Limits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "studentid   course      grade       credits     gender      age       \r\n",
      "----------  ----------  ----------  ----------  ----------  ----------\r\n",
      "1           PY205       3.8         3           m           22        \r\n",
      "1           PY411       3.2         4           m           22        \r\n",
      "1           PY412       3.1         4           m           22        \r\n",
      "1           E101        3.8         2           m           22        \r\n",
      "1           CS216       3.5         3           m           22        \r\n",
      "2           PY205       3.1         3           f           19        \r\n"
     ]
    }
   ],
   "source": [
    "SELECT g.*, d.gender, d.age\n",
    "    FROM grades g JOIN demographics d ON g.studentid=d.studentid LIMIT 6;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": 1
}