{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: None@None'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activity 6-2\n",
    "------------\n",
    "\n",
    "MVDS.  First load the following table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS courses;\n",
    "CREATE TABLE courses (course TEXT, staff TEXT, student TEXT);\n",
    "INSERT INTO courses VALUES ('CS949','Amy','Bob');\n",
    "INSERT INTO courses VALUES ('CS145','Chris','Deb');\n",
    "INSERT INTO courses VALUES ('CS145','Chris','Eli');\n",
    "INSERT INTO courses VALUES ('CS145','Firas','Deb');\n",
    "--INSERT INTO courses VALUES ('CS145','Firas','Bob');\n",
    "INSERT INTO courses VALUES ('CS145','Firas','Eli');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 1\n",
    "\n",
    "We developed some appealingly simple python tools for this lecture & last, but let's switch back to SQL quickly- write a query which returns **no values _only if_** the MVD course to staff holds\n",
    "\n",
    "Then, comment out the insert statement(s) above so that the MVD does hold (do you remember how to comment out lines in SQL from earlier activities?  It's super useful!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>course</th>\n",
       "        <th>staff</th>\n",
       "        <th>student</th>\n",
       "        <th>course_1</th>\n",
       "        <th>staff_1</th>\n",
       "        <th>student_1</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM courses AS c1, courses AS c2\n",
    "WHERE c1.course = c2.course AND NOT EXISTS (\n",
    "    SELECT *\n",
    "    FROM courses AS c3\n",
    "    WHERE c3.course = c1.course\n",
    "        AND c3.staff = c1.staff\n",
    "        AND c3.student = c2.student);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}