{ "metadata": { }, "nbformat": 4, "nbformat_minor": 5, "cells": [ { "id": "metadata", "cell_type": "markdown", "source": "
This is not a tutorial like most GTN content but a fun exercise for you to play around and learn a bit about SQL in a more ‘practical’, and hopefully re-inforce the skills you covered in Basic and Advanced SQL skills. It makes use of the NUKnightLab/sql-mysteries SQL murder mystery project and released under open licenses:
\n\n\nComment: Source\nOriginal code for NUKnightLab/sql-mysteries is released under the MIT License.\nOriginal text and other content is released under Creative Commons CC BY-SA 4.0.
\n
Download the database and connector:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-1", "source": [ "# This preamble sets up the sql \"magic\" for jupyter. Use %%sql in your cells to write sql!\n", "!python3 -m pip install ipython-sql sqlalchemy\n", "!wget -c https://github.com/NUKnightLab/sql-mysteries/raw/master/sql-murder-mystery.db" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ ">Setup the database connection:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-3", "source": [ "import sqlalchemy\n", "engine = sqlalchemy.create_engine(\"sqlite:///sql-murder-mystery.db\")\n", "%load_ext sql\n", "%sql sqlite:///sql-murder-mystery.db\n", "%config SqlMagic.displaycon=False" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ ">Which tables are available to you? What columns do they contain? Here’s a handy reference for you:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-5", "source": [ "import pandas as pd\n", "from sqlalchemy import MetaData\n", "m = MetaData()\n", "m.reflect(engine)\n", "results = []\n", "for table in m.tables.values():\n", " results.append([table.name, ', '.join([c.name for c in table.c])])\n", "pd.set_option('display.max_colwidth', None)\n", "pd.DataFrame(results, columns=[\"Table\", \"Columns\"])" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ ">Begin your search for the truth
\n\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-7", "source": [ "%%sql\n", "select * from crime_scene_report limit 8;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ ">A crime has taken place and the police are both useless and corrupt and it\nis up to you and your community to solve the mystery. They failed to secure\ntheir database, and now their crime scene reports are public, it is time to\nfigure out who the murderer was.
\nYou know that the crime was a murder that occurred sometime on Jan.15,\n2018 and that it took place in SQL City.
\nAll the clues to this mystery are buried in a huge database, and you need to\nuse SQL to navigate through this vast network of information. Your first step\nto solving the mystery is to retrieve the corresponding crime scene report\nfrom the police department’s database. From there, you can use your SQL\nskills to find the murderer.
\n
Try using the ‘Insert → Cell Below’ functionality to keep track of important query results as you go!
\nWrite the following queries in your SQL environment to check whether you’ve found the right murderer:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-9", "source": [ "%%sql\n", "INSERT INTO solution VALUES (1, \"Insert the name of the person you found here\");\n", "SELECT value FROM solution;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ ">