IntroSQLJoins.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Heroic Joins with SQL\n",
    "\n",
    "In this lab, we will explore the Marvel Superhero Universe using the concept of SQL `JOINS` (in particular, we'll be using `sqlite`). \n",
    "\n",
    "![Marvel Superheroes](figures/marvel.png) (from http://vignette1.wikia.nocookie.net/marveldatabase/images/e/e1/The_Marvel_Universe.png/revision/latest?cb=20110513164401)\n",
    "\n",
    "There are a **lot** of superheroes in the Marvel Universe, and a **lot** of superhero movies, so we'll just be examining a small portion today. As you'll see, we are storing the information about the superheroes separately from the information about the superhero movies. In addition, since superheroes frequently use aliases rather than their real names, it makes sense that we might want to store those real names separately from their superhero personae. \n",
    "\n",
    "When information is stored in separate tables, the way to recombine the information is to do a join. In SQL, JOIN is a means of combining fields from two tables by using values common to each.\n",
    "\n",
    "We commonly use Venn diagrams to represent the behavior of SQL joins, such as this one:   \n",
    "![SQL Joins](figures/sqljoins.jpg)\n",
    "\n",
    "Here are some additional resources that you may find helpful as you are learning about and practicing SQL JOINS (and using `sqlite`):\n",
    "\n",
    "- [Tutorials point on Joins](http://www.tutorialspoint.com/sql/sql-using-joins.htm)\n",
    "    - [Tutorials point specifically for sqlite](http://www.tutorialspoint.com/sqlite/)    \n",
    "    \n",
    "- [W3 schools on Joins](http://www.w3schools.com/sql/sql_join.asp)\n",
    "    - [W3 schools resources specifically for sqlite](http://www.w3resource.com/sqlite/index.php)    \n",
    "    \n",
    "- [A visual explanation of sql joins](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)\n",
    "\n",
    "\n",
    "## Get Connected\n",
    "\n",
    "Ok, let's get started. The first thing we need to do is import `sqlite`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we need to establish a connection to the database. In this case, I have already built a database for you called \"superheroes.db\", and it will be *your* job to implement different kinds of joins in order to answer the questions below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "DBPATH = 'dbmaker/superheroes.db'\n",
    "conn = sqlite3.connect(DBPATH)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is what the database looks like:\n",
    "\n",
    "![SuperheroesDB](figures/superheroesDB.png)\n",
    "\n",
    "Next we'll create a cursor object to help us execute our SQL statements:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JOIN statements we write will all follow roughly the same pattern that our SELECT statements followed in this morning's workshop. That is to say they will look something like:\n",
    "\n",
    "```python\n",
    "cursor.execute(\"SELECT [information] FROM [first_table] [SOME JOIN] [second_table] ON firsttable.id = secondtable.id\")\n",
    "print cursor.fetchall()\n",
    "```\n",
    "\n",
    "## Who's the star of the movie?\n",
    "\n",
    "How can we query our database to determine who is the star of the movie? In this case, let's use LEFT JOIN to pair the title of each movie with the name of its main character."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write the SQL code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What's your *real* name?\n",
    "\n",
    "We can also use LEFT JOIN to query our database to find out a superhero's real name.  Let's pair the real name of each character with their superhero alias."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write the SQL code here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Who's actually a superhero?\n",
    "\n",
    "Let's use an INNER JOIN to select only characters who have a superhero alias."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write the SQL code here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Which superheroes have their own movies?\n",
    "\n",
    "Not all superheroes have their own movies. Let's use another INNER JOIN to select only superheroes who have a lead role in a movie."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write the SQL code here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What's in a name?\n",
    "\n",
    "Which movies are named things other than (just) the name of the lead superhero? Let's experiment with using a LEFT OUTER JOIN to select only the movies that don't share the same name as the lead superhero.  Hint: you will also need a WHERE clause!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Write the SQL code here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "...and which superheroes don't have movies named (strictly) after them? Let's use another LEFT OUTER JOIN combined with a WHERE clause to select only the superheroes that *don't* share a name with a movie title."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write the SQL code here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stretch Goals\n",
    "\n",
    "### Right Joins and Full Outer Joins\n",
    "Actually... you can't do them in `sqlite`     \n",
    "\n",
    ":(\n",
    "\n",
    "If we were to use Postgres instead, what kinds of questions would we be able to ask of our superhero database using right and full outer joins? \n",
    "\n",
    "### Cartesian Products\n",
    "There be dragons!    \n",
    "\n",
    "What is a Cartesian product and why can they be potentially dangerous when we are writing SQL queries?   \n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}