17-relational_databases.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<link rel=\"stylesheet\" href=\"static/hyrule.css\" type=\"text/css\">"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<link rel=\"stylesheet\" href=\"static/hyrule.css\" type=\"text/css\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Assignment - Relational Databases; SQL syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connecting via Python\n",
    "We'll be using a pandas connector alongside SQLAlchemy to connect to this database. Please follow slack for instructions, however the syntax for connecting should be as follows:\n",
    "\n",
    "```python\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "cnx = create_engine('postgresql://username:password@ip_address:port/dbname')\n",
    "```\n",
    "\n",
    "for queries we'll use the pandas syntax:\n",
    "\n",
    "```python\n",
    "pd.read_sql_query(query, connection)\n",
    "```\n",
    "\n",
    "The tables we'll need are below. If you need to look at columns, we can use this function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yearid</th>\n",
       "      <th>teamid</th>\n",
       "      <th>lgid</th>\n",
       "      <th>playerid</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> barkele01</td>\n",
       "      <td> 870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> bedrost01</td>\n",
       "      <td> 550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> benedbr01</td>\n",
       "      <td> 545000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td>  campri01</td>\n",
       "      <td> 633333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> ceronri01</td>\n",
       "      <td> 625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> chambch01</td>\n",
       "      <td> 800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> dedmoje01</td>\n",
       "      <td> 150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> forstte01</td>\n",
       "      <td> 483333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> garbege01</td>\n",
       "      <td> 772000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td> 1985</td>\n",
       "      <td> ATL</td>\n",
       "      <td> NL</td>\n",
       "      <td> harpete01</td>\n",
       "      <td> 250000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   yearid teamid lgid   playerid  salary\n",
       "0    1985    ATL   NL  barkele01  870000\n",
       "1    1985    ATL   NL  bedrost01  550000\n",
       "2    1985    ATL   NL  benedbr01  545000\n",
       "3    1985    ATL   NL   campri01  633333\n",
       "4    1985    ATL   NL  ceronri01  625000\n",
       "5    1985    ATL   NL  chambch01  800000\n",
       "6    1985    ATL   NL  dedmoje01  150000\n",
       "7    1985    ATL   NL  forstte01  483333\n",
       "8    1985    ATL   NL  garbege01  772000\n",
       "9    1985    ATL   NL  harpete01  250000"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "cnx = create_engine('placeholder')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playerid</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0  </th>\n",
       "      <td> barkele01</td>\n",
       "      <td>  870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1  </th>\n",
       "      <td> bedrost01</td>\n",
       "      <td>  550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2  </th>\n",
       "      <td> benedbr01</td>\n",
       "      <td>  545000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3  </th>\n",
       "      <td>  campri01</td>\n",
       "      <td>  633333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4  </th>\n",
       "      <td> ceronri01</td>\n",
       "      <td>  625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5  </th>\n",
       "      <td> chambch01</td>\n",
       "      <td>  800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6  </th>\n",
       "      <td> garbege01</td>\n",
       "      <td>  772000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7  </th>\n",
       "      <td> hornebo01</td>\n",
       "      <td> 1500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8  </th>\n",
       "      <td> mumphje01</td>\n",
       "      <td>  775000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9  </th>\n",
       "      <td> murphda05</td>\n",
       "      <td> 1625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10 </th>\n",
       "      <td> oberkke01</td>\n",
       "      <td>  616667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11 </th>\n",
       "      <td> ramirra01</td>\n",
       "      <td>  750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 </th>\n",
       "      <td> suttebr01</td>\n",
       "      <td> 1354167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13 </th>\n",
       "      <td> washicl01</td>\n",
       "      <td>  800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14 </th>\n",
       "      <td> boddimi01</td>\n",
       "      <td>  625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15 </th>\n",
       "      <td> dempsri01</td>\n",
       "      <td>  512500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16 </th>\n",
       "      <td> flanami01</td>\n",
       "      <td>  641667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17 </th>\n",
       "      <td>  lacyle01</td>\n",
       "      <td>  725000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18 </th>\n",
       "      <td>  lynnfr01</td>\n",
       "      <td> 1090000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19 </th>\n",
       "      <td> martide01</td>\n",
       "      <td>  560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20 </th>\n",
       "      <td> mcgresc01</td>\n",
       "      <td>  547143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21 </th>\n",
       "      <td> murraed02</td>\n",
       "      <td> 1472819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22 </th>\n",
       "      <td> ripkeca01</td>\n",
       "      <td>  800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23 </th>\n",
       "      <td> roeniga01</td>\n",
       "      <td>  558333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24 </th>\n",
       "      <td> stewasa01</td>\n",
       "      <td>  581250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25 </th>\n",
       "      <td> armasto01</td>\n",
       "      <td>  915000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26 </th>\n",
       "      <td> boggswa01</td>\n",
       "      <td> 1000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27 </th>\n",
       "      <td> bucknbi01</td>\n",
       "      <td>  747500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28 </th>\n",
       "      <td> clearma01</td>\n",
       "      <td>  580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29 </th>\n",
       "      <td> easlemi01</td>\n",
       "      <td>  650000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td> driesda01</td>\n",
       "      <td>  767500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td> krukomi01</td>\n",
       "      <td>  630000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td> leonaje01</td>\n",
       "      <td>  900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td> mintogr01</td>\n",
       "      <td>  865000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td> trillma01</td>\n",
       "      <td>  709714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td> allenne01</td>\n",
       "      <td>  750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td> andujjo01</td>\n",
       "      <td> 1030000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td> cedence01</td>\n",
       "      <td>  562500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td> clarkja01</td>\n",
       "      <td> 1555000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td> dejesiv01</td>\n",
       "      <td>  700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td> forscbo01</td>\n",
       "      <td>  583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>  herrto01</td>\n",
       "      <td>  560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td> porteda02</td>\n",
       "      <td>  740000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td> smithlo01</td>\n",
       "      <td>  740000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td> smithoz01</td>\n",
       "      <td> 1440000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td> harrato01</td>\n",
       "      <td>  650000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td> hootobu01</td>\n",
       "      <td>  565000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td> houghch01</td>\n",
       "      <td>  636667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td> johnscl01</td>\n",
       "      <td>  683333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td> parrila01</td>\n",
       "      <td>  560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td> stewada01</td>\n",
       "      <td>  505000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td> tananfr01</td>\n",
       "      <td>  590000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>  wardga01</td>\n",
       "      <td>  720000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td> alexado01</td>\n",
       "      <td>  875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td> caudibi01</td>\n",
       "      <td>  833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td> clancji01</td>\n",
       "      <td>  580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td> garcida01</td>\n",
       "      <td>  740000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td> mosebll01</td>\n",
       "      <td>  587500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td> stiebda01</td>\n",
       "      <td> 1000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td> upshawi01</td>\n",
       "      <td>  730000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>220 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      playerid   salary\n",
       "0    barkele01   870000\n",
       "1    bedrost01   550000\n",
       "2    benedbr01   545000\n",
       "3     campri01   633333\n",
       "4    ceronri01   625000\n",
       "5    chambch01   800000\n",
       "6    garbege01   772000\n",
       "7    hornebo01  1500000\n",
       "8    mumphje01   775000\n",
       "9    murphda05  1625000\n",
       "10   oberkke01   616667\n",
       "11   ramirra01   750000\n",
       "12   suttebr01  1354167\n",
       "13   washicl01   800000\n",
       "14   boddimi01   625000\n",
       "15   dempsri01   512500\n",
       "16   flanami01   641667\n",
       "17    lacyle01   725000\n",
       "18    lynnfr01  1090000\n",
       "19   martide01   560000\n",
       "20   mcgresc01   547143\n",
       "21   murraed02  1472819\n",
       "22   ripkeca01   800000\n",
       "23   roeniga01   558333\n",
       "24   stewasa01   581250\n",
       "25   armasto01   915000\n",
       "26   boggswa01  1000000\n",
       "27   bucknbi01   747500\n",
       "28   clearma01   580000\n",
       "29   easlemi01   650000\n",
       "..         ...      ...\n",
       "190  driesda01   767500\n",
       "191  krukomi01   630000\n",
       "192  leonaje01   900000\n",
       "193  mintogr01   865000\n",
       "194  trillma01   709714\n",
       "195  allenne01   750000\n",
       "196  andujjo01  1030000\n",
       "197  cedence01   562500\n",
       "198  clarkja01  1555000\n",
       "199  dejesiv01   700000\n",
       "200  forscbo01   583333\n",
       "201   herrto01   560000\n",
       "202  porteda02   740000\n",
       "203  smithlo01   740000\n",
       "204  smithoz01  1440000\n",
       "205  harrato01   650000\n",
       "206  hootobu01   565000\n",
       "207  houghch01   636667\n",
       "208  johnscl01   683333\n",
       "209  parrila01   560000\n",
       "210  stewada01   505000\n",
       "211  tananfr01   590000\n",
       "212   wardga01   720000\n",
       "213  alexado01   875000\n",
       "214  caudibi01   833333\n",
       "215  clancji01   580000\n",
       "216  garcida01   740000\n",
       "217  mosebll01   587500\n",
       "218  stiebda01  1000000\n",
       "219  upshawi01   730000\n",
       "\n",
       "[220 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "query = \"\"\"SELECT playerid,\n",
    "salary \n",
    "FROM salaries \n",
    "where yearid = 1985\n",
    "and salary > 500000; \"\"\"\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Your turn:\n",
    "Tables you'll need:\n",
    "\n",
    "```\n",
    "allstarfull\n",
    "fielding\n",
    "salaries\n",
    "schools\n",
    "collegeplaying\n",
    "teams\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "allstarfull\n",
      "Index([u'playerid', u'yearid', u'gamenum', u'gameid', u'teamid', u'lgid', u'gp', u'startingpos'], dtype='object')\n",
      "fielding\n",
      "Index([u'playerid', u'yearid', u'stint', u'teamid', u'lgid', u'pos', u'g', u'gs', u'innouts', u'po', u'a', u'e', u'dp', u'pb', u'wp', u'sb', u'cs', u'zr'], dtype='object')\n",
      "salaries\n",
      "Index([u'yearid', u'teamid', u'lgid', u'playerid', u'salary'], dtype='object')\n",
      "schools\n",
      "Index([u'schoolid', u'schoolname', u'schoolcity', u'schoolstate', u'schoolnick'], dtype='object')\n",
      "collegeplaying\n",
      "Index([u'playerid', u'schoolid', u'yearid'], dtype='object')\n",
      "teams\n",
      "Index([u'yearid', u'lgid', u'teamid', u'franchid', u'divid', u'rank', u'g', u'ghome', u'w', u'l', u'divwin', u'wcwin', u'lgwin', u'wswin', u'r', u'ab', u'h', u'h2b', u'h3b', u'hr', u'bb', u'so', u'sb', u'cs', u'hbp', u'sf', u'ra', u'er', u'era', u'cg', u'sho', u'sv', u'ipouts', u'ha', u'hra', u'bba', u'soa', u'e', u'dp', u'fp', u'name', u'park', u'attendance', u'bpf', u'ppf', u'teamidbr', u'teamidlahman45', u'teamidretro'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "def show_columns(table, con):\n",
    "    from pandas import read_sql_table\n",
    "    print table\n",
    "    return read_sql_table(table, con).columns\n",
    "print show_columns(\"allstarfull\", cnx)\n",
    "print show_columns(\"fielding\", cnx)\n",
    "print show_columns(\"salaries\", cnx)\n",
    "print show_columns(\"schools\", cnx)\n",
    "print show_columns(\"collegeplaying\", cnx)\n",
    "print show_columns(\"teams\", cnx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do your best to answer the following questions! They are sorted from simplest to most difficult in terms of SQL execution. If you'd like to practice your pandas syntax, submit both your SQL and pandas code (assuming tables were dataframes)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playerid</th>\n",
       "      <th>yearid</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0  </th>\n",
       "      <td> barkele01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1  </th>\n",
       "      <td> bedrost01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2  </th>\n",
       "      <td> benedbr01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  545000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3  </th>\n",
       "      <td>  campri01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  633333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4  </th>\n",
       "      <td> ceronri01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5  </th>\n",
       "      <td> chambch01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6  </th>\n",
       "      <td> garbege01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  772000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7  </th>\n",
       "      <td> hornebo01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8  </th>\n",
       "      <td> mumphje01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  775000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9  </th>\n",
       "      <td> murphda05</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10 </th>\n",
       "      <td> oberkke01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  616667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11 </th>\n",
       "      <td> ramirra01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 </th>\n",
       "      <td> suttebr01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1354167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13 </th>\n",
       "      <td> washicl01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14 </th>\n",
       "      <td> boddimi01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  625000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15 </th>\n",
       "      <td> dempsri01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  512500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16 </th>\n",
       "      <td> flanami01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  641667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17 </th>\n",
       "      <td>  lacyle01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  725000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18 </th>\n",
       "      <td>  lynnfr01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1090000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19 </th>\n",
       "      <td> martide01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20 </th>\n",
       "      <td> mcgresc01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  547143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21 </th>\n",
       "      <td> murraed02</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1472819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22 </th>\n",
       "      <td> ripkeca01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23 </th>\n",
       "      <td> roeniga01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  558333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24 </th>\n",
       "      <td> stewasa01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  581250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25 </th>\n",
       "      <td> armasto01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  915000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26 </th>\n",
       "      <td> boggswa01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27 </th>\n",
       "      <td> bucknbi01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  747500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28 </th>\n",
       "      <td> clearma01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29 </th>\n",
       "      <td> easlemi01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  650000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td> driesda01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  767500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td> krukomi01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  630000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td> leonaje01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td> mintogr01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  865000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td> trillma01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  709714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td> allenne01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td> andujjo01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1030000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td> cedence01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  562500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td> clarkja01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1555000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td> dejesiv01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td> forscbo01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>  herrto01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td> porteda02</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  740000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td> smithlo01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  740000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td> smithoz01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1440000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td> harrato01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  650000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td> hootobu01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  565000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td> houghch01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  636667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td> johnscl01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  683333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td> parrila01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td> stewada01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  505000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td> tananfr01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  590000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>  wardga01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  720000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td> alexado01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td> caudibi01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td> clancji01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td> garcida01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  740000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td> mosebll01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  587500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td> stiebda01</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 1000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td> upshawi01</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  730000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>220 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      playerid  yearid   salary\n",
       "0    barkele01    1985   870000\n",
       "1    bedrost01    1985   550000\n",
       "2    benedbr01    1985   545000\n",
       "3     campri01    1985   633333\n",
       "4    ceronri01    1985   625000\n",
       "5    chambch01    1985   800000\n",
       "6    garbege01    1985   772000\n",
       "7    hornebo01    1985  1500000\n",
       "8    mumphje01    1985   775000\n",
       "9    murphda05    1985  1625000\n",
       "10   oberkke01    1985   616667\n",
       "11   ramirra01    1985   750000\n",
       "12   suttebr01    1985  1354167\n",
       "13   washicl01    1985   800000\n",
       "14   boddimi01    1985   625000\n",
       "15   dempsri01    1985   512500\n",
       "16   flanami01    1985   641667\n",
       "17    lacyle01    1985   725000\n",
       "18    lynnfr01    1985  1090000\n",
       "19   martide01    1985   560000\n",
       "20   mcgresc01    1985   547143\n",
       "21   murraed02    1985  1472819\n",
       "22   ripkeca01    1985   800000\n",
       "23   roeniga01    1985   558333\n",
       "24   stewasa01    1985   581250\n",
       "25   armasto01    1985   915000\n",
       "26   boggswa01    1985  1000000\n",
       "27   bucknbi01    1985   747500\n",
       "28   clearma01    1985   580000\n",
       "29   easlemi01    1985   650000\n",
       "..         ...     ...      ...\n",
       "190  driesda01    1985   767500\n",
       "191  krukomi01    1985   630000\n",
       "192  leonaje01    1985   900000\n",
       "193  mintogr01    1985   865000\n",
       "194  trillma01    1985   709714\n",
       "195  allenne01    1985   750000\n",
       "196  andujjo01    1985  1030000\n",
       "197  cedence01    1985   562500\n",
       "198  clarkja01    1985  1555000\n",
       "199  dejesiv01    1985   700000\n",
       "200  forscbo01    1985   583333\n",
       "201   herrto01    1985   560000\n",
       "202  porteda02    1985   740000\n",
       "203  smithlo01    1985   740000\n",
       "204  smithoz01    1985  1440000\n",
       "205  harrato01    1985   650000\n",
       "206  hootobu01    1985   565000\n",
       "207  houghch01    1985   636667\n",
       "208  johnscl01    1985   683333\n",
       "209  parrila01    1985   560000\n",
       "210  stewada01    1985   505000\n",
       "211  tananfr01    1985   590000\n",
       "212   wardga01    1985   720000\n",
       "213  alexado01    1985   875000\n",
       "214  caudibi01    1985   833333\n",
       "215  clancji01    1985   580000\n",
       "216  garcida01    1985   740000\n",
       "217  mosebll01    1985   587500\n",
       "218  stiebda01    1985  1000000\n",
       "219  upshawi01    1985   730000\n",
       "\n",
       "[220 rows x 3 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. Show all playerids and salaries with a salary in the year 1985 above 500k.\n",
    "query = \"\"\"SELECT playerid,s salary \n",
    "from salaries\n",
    "where \n",
    "yearid = '1985'\n",
    "and salary > 500000\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yearid</th>\n",
       "      <th>teamid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0  </th>\n",
       "      <td> 1871</td>\n",
       "      <td> PH1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1  </th>\n",
       "      <td> 1872</td>\n",
       "      <td> BS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2  </th>\n",
       "      <td> 1873</td>\n",
       "      <td> BS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3  </th>\n",
       "      <td> 1874</td>\n",
       "      <td> BS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4  </th>\n",
       "      <td> 1875</td>\n",
       "      <td> BS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5  </th>\n",
       "      <td> 1876</td>\n",
       "      <td> CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6  </th>\n",
       "      <td> 1877</td>\n",
       "      <td> BSN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7  </th>\n",
       "      <td> 1878</td>\n",
       "      <td> BSN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8  </th>\n",
       "      <td> 1879</td>\n",
       "      <td> PRO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9  </th>\n",
       "      <td> 1880</td>\n",
       "      <td> CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10 </th>\n",
       "      <td> 1881</td>\n",
       "      <td> CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11 </th>\n",
       "      <td> 1882</td>\n",
       "      <td> CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 </th>\n",
       "      <td> 1882</td>\n",
       "      <td> CN2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13 </th>\n",
       "      <td> 1883</td>\n",
       "      <td> BSN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14 </th>\n",
       "      <td> 1883</td>\n",
       "      <td> PH4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15 </th>\n",
       "      <td> 1884</td>\n",
       "      <td> NY4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16 </th>\n",
       "      <td> 1884</td>\n",
       "      <td> PRO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17 </th>\n",
       "      <td> 1884</td>\n",
       "      <td> SLU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18 </th>\n",
       "      <td> 1885</td>\n",
       "      <td> CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19 </th>\n",
       "      <td> 1885</td>\n",
       "      <td> SL4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20 </th>\n",
       "      <td> 1886</td>\n",
       "      <td> CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21 </th>\n",
       "      <td> 1886</td>\n",
       "      <td> SL4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22 </th>\n",
       "      <td> 1887</td>\n",
       "      <td> DTN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23 </th>\n",
       "      <td> 1887</td>\n",
       "      <td> SL4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24 </th>\n",
       "      <td> 1888</td>\n",
       "      <td> NY1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25 </th>\n",
       "      <td> 1888</td>\n",
       "      <td> SL4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26 </th>\n",
       "      <td> 1889</td>\n",
       "      <td> BR3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27 </th>\n",
       "      <td> 1889</td>\n",
       "      <td> NY1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28 </th>\n",
       "      <td> 1890</td>\n",
       "      <td> BRO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29 </th>\n",
       "      <td> 1890</td>\n",
       "      <td> BSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td> 2010</td>\n",
       "      <td> CIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td> 2010</td>\n",
       "      <td> MIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td> 2010</td>\n",
       "      <td> PHI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td> 2010</td>\n",
       "      <td> SFN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <td> 2010</td>\n",
       "      <td> TBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td> 2010</td>\n",
       "      <td> TEX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382</th>\n",
       "      <td> 2011</td>\n",
       "      <td> ARI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td> 2011</td>\n",
       "      <td> DET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384</th>\n",
       "      <td> 2011</td>\n",
       "      <td> MIL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td> 2011</td>\n",
       "      <td> NYA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td> 2011</td>\n",
       "      <td> PHI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td> 2011</td>\n",
       "      <td> TEX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td> 2012</td>\n",
       "      <td> CIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td> 2012</td>\n",
       "      <td> DET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td> 2012</td>\n",
       "      <td> NYA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td> 2012</td>\n",
       "      <td> OAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td> 2012</td>\n",
       "      <td> SFN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td> 2012</td>\n",
       "      <td> WAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td> 2013</td>\n",
       "      <td> ATL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td> 2013</td>\n",
       "      <td> BOS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td> 2013</td>\n",
       "      <td> DET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td> 2013</td>\n",
       "      <td> LAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td> 2013</td>\n",
       "      <td> OAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td> 2013</td>\n",
       "      <td> SLN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td> 2014</td>\n",
       "      <td> BAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401</th>\n",
       "      <td> 2014</td>\n",
       "      <td> DET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td> 2014</td>\n",
       "      <td> LAA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403</th>\n",
       "      <td> 2014</td>\n",
       "      <td> LAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>404</th>\n",
       "      <td> 2014</td>\n",
       "      <td> SLN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>405</th>\n",
       "      <td> 2014</td>\n",
       "      <td> WAS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>406 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     yearid teamid\n",
       "0      1871    PH1\n",
       "1      1872    BS1\n",
       "2      1873    BS1\n",
       "3      1874    BS1\n",
       "4      1875    BS1\n",
       "5      1876    CHN\n",
       "6      1877    BSN\n",
       "7      1878    BSN\n",
       "8      1879    PRO\n",
       "9      1880    CHN\n",
       "10     1881    CHN\n",
       "11     1882    CHN\n",
       "12     1882    CN2\n",
       "13     1883    BSN\n",
       "14     1883    PH4\n",
       "15     1884    NY4\n",
       "16     1884    PRO\n",
       "17     1884    SLU\n",
       "18     1885    CHN\n",
       "19     1885    SL4\n",
       "20     1886    CHN\n",
       "21     1886    SL4\n",
       "22     1887    DTN\n",
       "23     1887    SL4\n",
       "24     1888    NY1\n",
       "25     1888    SL4\n",
       "26     1889    BR3\n",
       "27     1889    NY1\n",
       "28     1890    BRO\n",
       "29     1890    BSP\n",
       "..      ...    ...\n",
       "376    2010    CIN\n",
       "377    2010    MIN\n",
       "378    2010    PHI\n",
       "379    2010    SFN\n",
       "380    2010    TBA\n",
       "381    2010    TEX\n",
       "382    2011    ARI\n",
       "383    2011    DET\n",
       "384    2011    MIL\n",
       "385    2011    NYA\n",
       "386    2011    PHI\n",
       "387    2011    TEX\n",
       "388    2012    CIN\n",
       "389    2012    DET\n",
       "390    2012    NYA\n",
       "391    2012    OAK\n",
       "392    2012    SFN\n",
       "393    2012    WAS\n",
       "394    2013    ATL\n",
       "395    2013    BOS\n",
       "396    2013    DET\n",
       "397    2013    LAN\n",
       "398    2013    OAK\n",
       "399    2013    SLN\n",
       "400    2014    BAL\n",
       "401    2014    DET\n",
       "402    2014    LAA\n",
       "403    2014    LAN\n",
       "404    2014    SLN\n",
       "405    2014    WAS\n",
       "\n",
       "[406 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2. Show the team for each year that had a rank of 1.\n",
    "query = \"\"\"SELECT yearid,\n",
    "teamid\n",
    "from teams where rank = 1 \"\"\"\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td> 15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0     15"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#3. How many schools are in schoolstate of CT?\n",
    "\n",
    "query = \"\"\"SELECT count(*) \n",
    "from schools where schoolstate = 'CT'\"\"\"\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>schoolstate</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0 </th>\n",
       "      <td> PA</td>\n",
       "      <td>  72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 </th>\n",
       "      <td> AZ</td>\n",
       "      <td>  14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2 </th>\n",
       "      <td> FL</td>\n",
       "      <td>  58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3 </th>\n",
       "      <td> LA</td>\n",
       "      <td>  17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4 </th>\n",
       "      <td> NM</td>\n",
       "      <td>   7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5 </th>\n",
       "      <td> NC</td>\n",
       "      <td>  39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6 </th>\n",
       "      <td> OR</td>\n",
       "      <td>  17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7 </th>\n",
       "      <td> VT</td>\n",
       "      <td>   4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8 </th>\n",
       "      <td> MS</td>\n",
       "      <td>  22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9 </th>\n",
       "      <td> AR</td>\n",
       "      <td>  17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td> IL</td>\n",
       "      <td>  61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td> MO</td>\n",
       "      <td>  32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td> IN</td>\n",
       "      <td>  24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td> HI</td>\n",
       "      <td>   3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td> WY</td>\n",
       "      <td>   1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td> UT</td>\n",
       "      <td>  10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td> MI</td>\n",
       "      <td>  31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td> KS</td>\n",
       "      <td>  27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td> MD</td>\n",
       "      <td>  25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td> GA</td>\n",
       "      <td>  25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td> DC</td>\n",
       "      <td>   7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td> MN</td>\n",
       "      <td>  15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td> WI</td>\n",
       "      <td>  16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td> OH</td>\n",
       "      <td>  42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td> NE</td>\n",
       "      <td>  12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td> CT</td>\n",
       "      <td>  15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td> NV</td>\n",
       "      <td>   3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td> OK</td>\n",
       "      <td>  23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td> AL</td>\n",
       "      <td>  33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td> CA</td>\n",
       "      <td> 136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td> WV</td>\n",
       "      <td>  13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td> CO</td>\n",
       "      <td>  11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td> DE</td>\n",
       "      <td>   4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td> ND</td>\n",
       "      <td>   2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td> KY</td>\n",
       "      <td>  17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td> WA</td>\n",
       "      <td>  25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td> ME</td>\n",
       "      <td>   8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td> RI</td>\n",
       "      <td>   7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td> TN</td>\n",
       "      <td>  39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td> VA</td>\n",
       "      <td>  27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td> SD</td>\n",
       "      <td>   1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td> NH</td>\n",
       "      <td>   5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td> IA</td>\n",
       "      <td>  26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td> SC</td>\n",
       "      <td>  19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td> NY</td>\n",
       "      <td>  55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td> MA</td>\n",
       "      <td>  28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td> TX</td>\n",
       "      <td>  80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td> ID</td>\n",
       "      <td>   8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td> NJ</td>\n",
       "      <td>  24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   schoolstate  count\n",
       "0           PA     72\n",
       "1           AZ     14\n",
       "2           FL     58\n",
       "3           LA     17\n",
       "4           NM      7\n",
       "5           NC     39\n",
       "6           OR     17\n",
       "7           VT      4\n",
       "8           MS     22\n",
       "9           AR     17\n",
       "10          IL     61\n",
       "11          MO     32\n",
       "12          IN     24\n",
       "13          HI      3\n",
       "14          WY      1\n",
       "15          UT     10\n",
       "16          MI     31\n",
       "17          KS     27\n",
       "18          MD     25\n",
       "19          GA     25\n",
       "20          DC      7\n",
       "21          MN     15\n",
       "22          WI     16\n",
       "23          OH     42\n",
       "24          NE     12\n",
       "25          CT     15\n",
       "26          NV      3\n",
       "27          OK     23\n",
       "28          AL     33\n",
       "29          CA    136\n",
       "30          WV     13\n",
       "31          CO     11\n",
       "32          DE      4\n",
       "33          ND      2\n",
       "34          KY     17\n",
       "35          WA     25\n",
       "36          ME      8\n",
       "37          RI      7\n",
       "38          TN     39\n",
       "39          VA     27\n",
       "40          SD      1\n",
       "41          NH      5\n",
       "42          IA     26\n",
       "43          SC     19\n",
       "44          NY     55\n",
       "45          MA     28\n",
       "46          TX     80\n",
       "47          ID      8\n",
       "48          NJ     24"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#4. How many schools are there in each state?\n",
    "\n",
    "query = \"\"\"SELECT schoolstate, count(schoolstate)\n",
    "from schools \n",
    "group by schoolstate\"\"\"\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>teamid</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0 </th>\n",
       "      <td> NYA</td>\n",
       "      <td> 3283119334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 </th>\n",
       "      <td> BOS</td>\n",
       "      <td> 2432700935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2 </th>\n",
       "      <td> LAN</td>\n",
       "      <td> 2237766703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3 </th>\n",
       "      <td> PHI</td>\n",
       "      <td> 1982355800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4 </th>\n",
       "      <td> NYN</td>\n",
       "      <td> 1965737231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5 </th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1882946784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6 </th>\n",
       "      <td> CHN</td>\n",
       "      <td> 1859833315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7 </th>\n",
       "      <td> SLN</td>\n",
       "      <td> 1775631332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8 </th>\n",
       "      <td> DET</td>\n",
       "      <td> 1771197687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9 </th>\n",
       "      <td> CHA</td>\n",
       "      <td> 1699865143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td> SFN</td>\n",
       "      <td> 1696242921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td> SEA</td>\n",
       "      <td> 1673143431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td> TEX</td>\n",
       "      <td> 1670090498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td> BAL</td>\n",
       "      <td> 1630200038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 1570875353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td> CIN</td>\n",
       "      <td> 1434775348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td> HOU</td>\n",
       "      <td> 1408093157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td> CLE</td>\n",
       "      <td> 1405016128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td> COL</td>\n",
       "      <td> 1293910639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td> MIN</td>\n",
       "      <td> 1270237124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td> ARI</td>\n",
       "      <td> 1209975228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td> OAK</td>\n",
       "      <td> 1186554453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td> LAA</td>\n",
       "      <td> 1184671686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td> SDN</td>\n",
       "      <td> 1177499962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td> KCA</td>\n",
       "      <td> 1177466968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td> MIL</td>\n",
       "      <td> 1048161053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td> PIT</td>\n",
       "      <td>  994547218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td> TBA</td>\n",
       "      <td>  768110875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td> WAS</td>\n",
       "      <td>  715360021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td> FLO</td>\n",
       "      <td>  675016005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td> ANA</td>\n",
       "      <td>  468091973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td> MON</td>\n",
       "      <td>  408203761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td> CAL</td>\n",
       "      <td>  271978930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td> ML4</td>\n",
       "      <td>  233645804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td> MIA</td>\n",
       "      <td>  193516800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td> SFG</td>\n",
       "      <td>  143510167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td> NYM</td>\n",
       "      <td>   54806990</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   teamid         sum\n",
       "0     NYA  3283119334\n",
       "1     BOS  2432700935\n",
       "2     LAN  2237766703\n",
       "3     PHI  1982355800\n",
       "4     NYN  1965737231\n",
       "5     ATL  1882946784\n",
       "6     CHN  1859833315\n",
       "7     SLN  1775631332\n",
       "8     DET  1771197687\n",
       "9     CHA  1699865143\n",
       "10    SFN  1696242921\n",
       "11    SEA  1673143431\n",
       "12    TEX  1670090498\n",
       "13    BAL  1630200038\n",
       "14    TOR  1570875353\n",
       "15    CIN  1434775348\n",
       "16    HOU  1408093157\n",
       "17    CLE  1405016128\n",
       "18    COL  1293910639\n",
       "19    MIN  1270237124\n",
       "20    ARI  1209975228\n",
       "21    OAK  1186554453\n",
       "22    LAA  1184671686\n",
       "23    SDN  1177499962\n",
       "24    KCA  1177466968\n",
       "25    MIL  1048161053\n",
       "26    PIT   994547218\n",
       "27    TBA   768110875\n",
       "28    WAS   715360021\n",
       "29    FLO   675016005\n",
       "30    ANA   468091973\n",
       "31    MON   408203761\n",
       "32    CAL   271978930\n",
       "33    ML4   233645804\n",
       "34    MIA   193516800\n",
       "35    SFG   143510167\n",
       "36    NYM    54806990"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#5. What was the total spend on salaries by each team, each year?\n",
    "\n",
    "query = \"\"\"SELECT teamid, sum(salary)\n",
    "from\n",
    "salaries\n",
    "group by teamid\n",
    "order by sum(salary) desc\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, cnx)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yearid</th>\n",
       "      <th>playerid</th>\n",
       "      <th>salary</th>\n",
       "      <th>pos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> bloomwi01</td>\n",
       "      <td>  1900000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 </th>\n",
       "      <td> 2012</td>\n",
       "      <td>  drewst01</td>\n",
       "      <td>  7750000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2 </th>\n",
       "      <td> 2012</td>\n",
       "      <td>  drewst01</td>\n",
       "      <td>  7750000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> mcdonjo03</td>\n",
       "      <td>  1500000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> pastoty01</td>\n",
       "      <td>   480000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> pradoma01</td>\n",
       "      <td>  4750000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> wilsoja02</td>\n",
       "      <td>  1000000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> andinro01</td>\n",
       "      <td>  1300000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> flahery01</td>\n",
       "      <td>   480000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9 </th>\n",
       "      <td> 2012</td>\n",
       "      <td> hardyjj01</td>\n",
       "      <td>  7416666</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td> 2012</td>\n",
       "      <td> avilemi01</td>\n",
       "      <td>  1200000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td> 2012</td>\n",
       "      <td> puntoni01</td>\n",
       "      <td>  1500000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td> 2012</td>\n",
       "      <td> escobed01</td>\n",
       "      <td>   480000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td> 2012</td>\n",
       "      <td> escobed01</td>\n",
       "      <td>   480000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td> 2012</td>\n",
       "      <td> lillibr01</td>\n",
       "      <td>   500000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td> 2012</td>\n",
       "      <td> ramiral03</td>\n",
       "      <td>  5000000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td> 2012</td>\n",
       "      <td> barneda01</td>\n",
       "      <td>   500000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td> 2012</td>\n",
       "      <td> castrst01</td>\n",
       "      <td>   567000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td> 2012</td>\n",
       "      <td> cozarza01</td>\n",
       "      <td>   480000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td> 2012</td>\n",
       "      <td> valdewi01</td>\n",
       "      <td>   930000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td> 2012</td>\n",
       "      <td> cabreas01</td>\n",
       "      <td>  4550000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td> 2012</td>\n",
       "      <td> donalja01</td>\n",
       "      <td>   484000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td> 2012</td>\n",
       "      <td> hannaja01</td>\n",
       "      <td>  1135000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td> 2012</td>\n",
       "      <td> herrejo03</td>\n",
       "      <td>   482000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td> 2012</td>\n",
       "      <td> nelsoch01</td>\n",
       "      <td>   480000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td> 2012</td>\n",
       "      <td> scutama01</td>\n",
       "      <td>  6000000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td> 2012</td>\n",
       "      <td> tulowtr01</td>\n",
       "      <td>  8250000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td> 2012</td>\n",
       "      <td> peraljh01</td>\n",
       "      <td>  5500000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td> 2012</td>\n",
       "      <td> santira01</td>\n",
       "      <td>  2100000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td> 2012</td>\n",
       "      <td> worthda01</td>\n",
       "      <td>   482000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td> 2012</td>\n",
       "      <td> martimi02</td>\n",
       "      <td>   486500</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td> 2012</td>\n",
       "      <td> rolliji01</td>\n",
       "      <td> 11000000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td> 2012</td>\n",
       "      <td> barmecl01</td>\n",
       "      <td>  5000000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td> 2012</td>\n",
       "      <td> harrijo05</td>\n",
       "      <td>   484000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td> 2012</td>\n",
       "      <td> navarya01</td>\n",
       "      <td>   483000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td> 2012</td>\n",
       "      <td> bartlja01</td>\n",
       "      <td>  5500000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td> 2012</td>\n",
       "      <td> forsylo01</td>\n",
       "      <td>   481400</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td> 2012</td>\n",
       "      <td> parrian01</td>\n",
       "      <td>   481000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td> 2012</td>\n",
       "      <td> kawasmu01</td>\n",
       "      <td>   625000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td> 2012</td>\n",
       "      <td>  ryanbr01</td>\n",
       "      <td>  1750000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td> 2012</td>\n",
       "      <td> burriem01</td>\n",
       "      <td>   625000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td> 2012</td>\n",
       "      <td> crawfbr01</td>\n",
       "      <td>   481000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td> 2012</td>\n",
       "      <td> descada01</td>\n",
       "      <td>   495000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td> 2012</td>\n",
       "      <td> furcara01</td>\n",
       "      <td>  6500000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td> 2012</td>\n",
       "      <td> greenty02</td>\n",
       "      <td>   483000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td> 2012</td>\n",
       "      <td> greenty02</td>\n",
       "      <td>   483000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td> 2012</td>\n",
       "      <td> brignre01</td>\n",
       "      <td>   490600</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td> 2012</td>\n",
       "      <td> johnsel02</td>\n",
       "      <td>   486100</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td> 2012</td>\n",
       "      <td> rodrise01</td>\n",
       "      <td>   492800</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td> 2012</td>\n",
       "      <td> zobribe01</td>\n",
       "      <td>  4687300</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td> 2012</td>\n",
       "      <td> andruel01</td>\n",
       "      <td>  2625000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td> 2012</td>\n",
       "      <td> gonzaal03</td>\n",
       "      <td>   750000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td> 2012</td>\n",
       "      <td> youngmi02</td>\n",
       "      <td> 16174974</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td> 2012</td>\n",
       "      <td> escobyu01</td>\n",
       "      <td>  5000000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td> 2012</td>\n",
       "      <td> lawribr01</td>\n",
       "      <td>   482500</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td> 2012</td>\n",
       "      <td> vizquom01</td>\n",
       "      <td>   750000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td> 2012</td>\n",
       "      <td> derosma01</td>\n",
       "      <td>   800000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td> 2012</td>\n",
       "      <td> desmoia01</td>\n",
       "      <td>   512500</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td> 2012</td>\n",
       "      <td> espinda01</td>\n",
       "      <td>   506000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td> 2012</td>\n",
       "      <td> lombast02</td>\n",
       "      <td>   481000</td>\n",
       "      <td> SS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    yearid   playerid    salary pos\n",
       "0     2012  bloomwi01   1900000  SS\n",
       "1     2012   drewst01   7750000  SS\n",
       "2     2012   drewst01   7750000  SS\n",
       "3     2012  mcdonjo03   1500000  SS\n",
       "4     2012  pastoty01    480000  SS\n",
       "5     2012  pradoma01   4750000  SS\n",
       "6     2012  wilsoja02   1000000  SS\n",
       "7     2012  andinro01   1300000  SS\n",
       "8     2012  flahery01    480000  SS\n",
       "9     2012  hardyjj01   7416666  SS\n",
       "10    2012  avilemi01   1200000  SS\n",
       "11    2012  puntoni01   1500000  SS\n",
       "12    2012  escobed01    480000  SS\n",
       "13    2012  escobed01    480000  SS\n",
       "14    2012  lillibr01    500000  SS\n",
       "15    2012  ramiral03   5000000  SS\n",
       "16    2012  barneda01    500000  SS\n",
       "17    2012  castrst01    567000  SS\n",
       "18    2012  cozarza01    480000  SS\n",
       "19    2012  valdewi01    930000  SS\n",
       "20    2012  cabreas01   4550000  SS\n",
       "21    2012  donalja01    484000  SS\n",
       "22    2012  hannaja01   1135000  SS\n",
       "23    2012  herrejo03    482000  SS\n",
       "24    2012  nelsoch01    480000  SS\n",
       "25    2012  scutama01   6000000  SS\n",
       "26    2012  tulowtr01   8250000  SS\n",
       "27    2012  peraljh01   5500000  SS\n",
       "28    2012  santira01   2100000  SS\n",
       "29    2012  worthda01    482000  SS\n",
       "..     ...        ...       ...  ..\n",
       "60    2012  martimi02    486500  SS\n",
       "61    2012  rolliji01  11000000  SS\n",
       "62    2012  barmecl01   5000000  SS\n",
       "63    2012  harrijo05    484000  SS\n",
       "64    2012  navarya01    483000  SS\n",
       "65    2012  bartlja01   5500000  SS\n",
       "66    2012  forsylo01    481400  SS\n",
       "67    2012  parrian01    481000  SS\n",
       "68    2012  kawasmu01    625000  SS\n",
       "69    2012   ryanbr01   1750000  SS\n",
       "70    2012  burriem01    625000  SS\n",
       "71    2012  crawfbr01    481000  SS\n",
       "72    2012  descada01    495000  SS\n",
       "73    2012  furcara01   6500000  SS\n",
       "74    2012  greenty02    483000  SS\n",
       "75    2012  greenty02    483000  SS\n",
       "76    2012  brignre01    490600  SS\n",
       "77    2012  johnsel02    486100  SS\n",
       "78    2012  rodrise01    492800  SS\n",
       "79    2012  zobribe01   4687300  SS\n",
       "80    2012  andruel01   2625000  SS\n",
       "81    2012  gonzaal03    750000  SS\n",
       "82    2012  youngmi02  16174974  SS\n",
       "83    2012  escobyu01   5000000  SS\n",
       "84    2012  lawribr01    482500  SS\n",
       "85    2012  vizquom01    750000  SS\n",
       "86    2012  derosma01    800000  SS\n",
       "87    2012  desmoia01    512500  SS\n",
       "88    2012  espinda01    506000  SS\n",
       "89    2012  lombast02    481000  SS\n",
       "\n",
       "[90 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#6. Find all of the salaries of shortstops (fieldings, pos) for the year 2012.\n",
    "\n",
    "query = \"\"\"SELECT s.yearid, s.playerid, s.salary, f.pos\n",
    "from\n",
    "salaries as s\n",
    "INNER JOIN fielding f on f.playerid = s.playerid\n",
    "and f.yearid = s.yearid\n",
    "where f.pos = 'SS'\n",
    "and f.yearid = 2012\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, cnx)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playerid</th>\n",
       "      <th>first_year</th>\n",
       "      <th>last_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0    </th>\n",
       "      <td> aardsda01</td>\n",
       "      <td> 2004</td>\n",
       "      <td> 2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1    </th>\n",
       "      <td> aaronha01</td>\n",
       "      <td> 1954</td>\n",
       "      <td> 1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2    </th>\n",
       "      <td> aaronto01</td>\n",
       "      <td> 1962</td>\n",
       "      <td> 1971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3    </th>\n",
       "      <td>  aasedo01</td>\n",
       "      <td> 1977</td>\n",
       "      <td> 1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4    </th>\n",
       "      <td>  abadan01</td>\n",
       "      <td> 2001</td>\n",
       "      <td> 2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5    </th>\n",
       "      <td>  abadfe01</td>\n",
       "      <td> 2010</td>\n",
       "      <td> 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6    </th>\n",
       "      <td> abadijo01</td>\n",
       "      <td> 1875</td>\n",
       "      <td> 1875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7    </th>\n",
       "      <td> abbated01</td>\n",
       "      <td> 1897</td>\n",
       "      <td> 1910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8    </th>\n",
       "      <td> abbeybe01</td>\n",
       "      <td> 1892</td>\n",
       "      <td> 1896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9    </th>\n",
       "      <td> abbeych01</td>\n",
       "      <td> 1893</td>\n",
       "      <td> 1897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10   </th>\n",
       "      <td> abbotda01</td>\n",
       "      <td> 1890</td>\n",
       "      <td> 1890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11   </th>\n",
       "      <td> abbotfr01</td>\n",
       "      <td> 1903</td>\n",
       "      <td> 1905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12   </th>\n",
       "      <td> abbotgl01</td>\n",
       "      <td> 1973</td>\n",
       "      <td> 1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13   </th>\n",
       "      <td> abbotje01</td>\n",
       "      <td> 1997</td>\n",
       "      <td> 2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14   </th>\n",
       "      <td> abbotji01</td>\n",
       "      <td> 1989</td>\n",
       "      <td> 1999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15   </th>\n",
       "      <td> abbotku01</td>\n",
       "      <td> 1993</td>\n",
       "      <td> 2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16   </th>\n",
       "      <td> abbotky01</td>\n",
       "      <td> 1991</td>\n",
       "      <td> 1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17   </th>\n",
       "      <td> abbotod01</td>\n",
       "      <td> 1910</td>\n",
       "      <td> 1910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18   </th>\n",
       "      <td> abbotpa01</td>\n",
       "      <td> 1990</td>\n",
       "      <td> 2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19   </th>\n",
       "      <td>  aberal01</td>\n",
       "      <td> 1950</td>\n",
       "      <td> 1957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20   </th>\n",
       "      <td> abercda01</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 1871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21   </th>\n",
       "      <td> abercre01</td>\n",
       "      <td> 2006</td>\n",
       "      <td> 2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22   </th>\n",
       "      <td> abernbi01</td>\n",
       "      <td> 1952</td>\n",
       "      <td> 1952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23   </th>\n",
       "      <td> abernbr01</td>\n",
       "      <td> 2001</td>\n",
       "      <td> 2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24   </th>\n",
       "      <td> abernte01</td>\n",
       "      <td> 1942</td>\n",
       "      <td> 1944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25   </th>\n",
       "      <td> abernte02</td>\n",
       "      <td> 1955</td>\n",
       "      <td> 1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26   </th>\n",
       "      <td> abernwo01</td>\n",
       "      <td> 1946</td>\n",
       "      <td> 1947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27   </th>\n",
       "      <td> aberscl01</td>\n",
       "      <td> 1947</td>\n",
       "      <td> 1949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28   </th>\n",
       "      <td> ablesha01</td>\n",
       "      <td> 1905</td>\n",
       "      <td> 1911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29   </th>\n",
       "      <td> abnersh01</td>\n",
       "      <td> 1987</td>\n",
       "      <td> 1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18184</th>\n",
       "      <td> zimmejo01</td>\n",
       "      <td> 1999</td>\n",
       "      <td> 1999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18185</th>\n",
       "      <td> zimmejo02</td>\n",
       "      <td> 2009</td>\n",
       "      <td> 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18186</th>\n",
       "      <td> zimmero01</td>\n",
       "      <td> 1945</td>\n",
       "      <td> 1945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18187</th>\n",
       "      <td> zimmery01</td>\n",
       "      <td> 2005</td>\n",
       "      <td> 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18188</th>\n",
       "      <td>  zinkch01</td>\n",
       "      <td> 2008</td>\n",
       "      <td> 2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18189</th>\n",
       "      <td>  zinkwa01</td>\n",
       "      <td> 1921</td>\n",
       "      <td> 1921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18190</th>\n",
       "      <td>  zinnfr01</td>\n",
       "      <td> 1888</td>\n",
       "      <td> 1888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18191</th>\n",
       "      <td>  zinngu01</td>\n",
       "      <td> 1911</td>\n",
       "      <td> 1915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18192</th>\n",
       "      <td>  zinnji01</td>\n",
       "      <td> 1919</td>\n",
       "      <td> 1929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18193</th>\n",
       "      <td> zinsebi01</td>\n",
       "      <td> 1944</td>\n",
       "      <td> 1944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18194</th>\n",
       "      <td> zinteal01</td>\n",
       "      <td> 2002</td>\n",
       "      <td> 2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18195</th>\n",
       "      <td> zipfebu01</td>\n",
       "      <td> 1961</td>\n",
       "      <td> 1962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18196</th>\n",
       "      <td>  ziskri01</td>\n",
       "      <td> 1971</td>\n",
       "      <td> 1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18197</th>\n",
       "      <td>  zitoba01</td>\n",
       "      <td> 2000</td>\n",
       "      <td> 2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18198</th>\n",
       "      <td> zitzmbi01</td>\n",
       "      <td> 1919</td>\n",
       "      <td> 1929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18199</th>\n",
       "      <td> zmiched01</td>\n",
       "      <td> 1910</td>\n",
       "      <td> 1911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18200</th>\n",
       "      <td> zobribe01</td>\n",
       "      <td> 2006</td>\n",
       "      <td> 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18201</th>\n",
       "      <td> zoccope01</td>\n",
       "      <td> 2003</td>\n",
       "      <td> 2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18202</th>\n",
       "      <td> zoldasa01</td>\n",
       "      <td> 1944</td>\n",
       "      <td> 1952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18203</th>\n",
       "      <td> zoskyed01</td>\n",
       "      <td> 1991</td>\n",
       "      <td> 1999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18204</th>\n",
       "      <td> zuberbi01</td>\n",
       "      <td> 1936</td>\n",
       "      <td> 1947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18205</th>\n",
       "      <td> zuberjo01</td>\n",
       "      <td> 1996</td>\n",
       "      <td> 1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18206</th>\n",
       "      <td> zuletju01</td>\n",
       "      <td> 2000</td>\n",
       "      <td> 2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18207</th>\n",
       "      <td> zumayjo01</td>\n",
       "      <td> 2006</td>\n",
       "      <td> 2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18208</th>\n",
       "      <td> zuninmi01</td>\n",
       "      <td> 2013</td>\n",
       "      <td> 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18209</th>\n",
       "      <td> zupcibo01</td>\n",
       "      <td> 1991</td>\n",
       "      <td> 1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18210</th>\n",
       "      <td>  zupofr01</td>\n",
       "      <td> 1957</td>\n",
       "      <td> 1961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18211</th>\n",
       "      <td> zuvelpa01</td>\n",
       "      <td> 1982</td>\n",
       "      <td> 1991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18212</th>\n",
       "      <td> zuverge01</td>\n",
       "      <td> 1951</td>\n",
       "      <td> 1959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18213</th>\n",
       "      <td> zwilldu01</td>\n",
       "      <td> 1910</td>\n",
       "      <td> 1916</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18214 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        playerid  first_year  last_year\n",
       "0      aardsda01        2004       2013\n",
       "1      aaronha01        1954       1976\n",
       "2      aaronto01        1962       1971\n",
       "3       aasedo01        1977       1990\n",
       "4       abadan01        2001       2003\n",
       "5       abadfe01        2010       2014\n",
       "6      abadijo01        1875       1875\n",
       "7      abbated01        1897       1910\n",
       "8      abbeybe01        1892       1896\n",
       "9      abbeych01        1893       1897\n",
       "10     abbotda01        1890       1890\n",
       "11     abbotfr01        1903       1905\n",
       "12     abbotgl01        1973       1984\n",
       "13     abbotje01        1997       2001\n",
       "14     abbotji01        1989       1999\n",
       "15     abbotku01        1993       2001\n",
       "16     abbotky01        1991       1996\n",
       "17     abbotod01        1910       1910\n",
       "18     abbotpa01        1990       2004\n",
       "19      aberal01        1950       1957\n",
       "20     abercda01        1871       1871\n",
       "21     abercre01        2006       2008\n",
       "22     abernbi01        1952       1952\n",
       "23     abernbr01        2001       2005\n",
       "24     abernte01        1942       1944\n",
       "25     abernte02        1955       1972\n",
       "26     abernwo01        1946       1947\n",
       "27     aberscl01        1947       1949\n",
       "28     ablesha01        1905       1911\n",
       "29     abnersh01        1987       1992\n",
       "...          ...         ...        ...\n",
       "18184  zimmejo01        1999       1999\n",
       "18185  zimmejo02        2009       2014\n",
       "18186  zimmero01        1945       1945\n",
       "18187  zimmery01        2005       2014\n",
       "18188   zinkch01        2008       2008\n",
       "18189   zinkwa01        1921       1921\n",
       "18190   zinnfr01        1888       1888\n",
       "18191   zinngu01        1911       1915\n",
       "18192   zinnji01        1919       1929\n",
       "18193  zinsebi01        1944       1944\n",
       "18194  zinteal01        2002       2004\n",
       "18195  zipfebu01        1961       1962\n",
       "18196   ziskri01        1971       1983\n",
       "18197   zitoba01        2000       2013\n",
       "18198  zitzmbi01        1919       1929\n",
       "18199  zmiched01        1910       1911\n",
       "18200  zobribe01        2006       2014\n",
       "18201  zoccope01        2003       2003\n",
       "18202  zoldasa01        1944       1952\n",
       "18203  zoskyed01        1991       1999\n",
       "18204  zuberbi01        1936       1947\n",
       "18205  zuberjo01        1996       1998\n",
       "18206  zuletju01        2000       2001\n",
       "18207  zumayjo01        2006       2010\n",
       "18208  zuninmi01        2013       2014\n",
       "18209  zupcibo01        1991       1994\n",
       "18210   zupofr01        1957       1961\n",
       "18211  zuvelpa01        1982       1991\n",
       "18212  zuverge01        1951       1959\n",
       "18213  zwilldu01        1910       1916\n",
       "\n",
       "[18214 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#7. What is the first and last year played for each player?\n",
    "\n",
    "query = \"\"\"SELECT playerid,\n",
    "min(yearid) as first_year,\n",
    "max(yearid) as last_year\n",
    "from\n",
    "fielding\n",
    "group by playerid\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playerid</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td> aaronha01</td>\n",
       "      <td> 25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    playerid  count\n",
       "0  aaronha01     25"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#8. Who has played the most all star games?\n",
    "query = \"\"\"\n",
    "SELECT playerid,  COUNT (gameid) \n",
    "FROM allstarfull   \n",
    "GROUP BY playerid\n",
    "order by count desc\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT playerid, COUNT (gameid)  \n",
    "FROM allstarfull  GROUP BY playerid  \n",
    "HAVING COUNT (gameid)=(  \n",
    "SELECT MAX (count)  \n",
    "FROM (  \n",
    "SELECT playerid,  COUNT (gameid) \n",
    "FROM allstarfull   \n",
    "GROUP BY playerid ) as \"table\");\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "pd.read_sql_query(query, cnx)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>schoolid</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td> texas</td>\n",
       "      <td> 107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  schoolid  count\n",
       "0    texas    107"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#9. Which school has generated the most distinct players?\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT schoolid, COUNT (distinct(playerid))  \n",
    "FROM collegeplaying  GROUP BY schoolid  \n",
    "HAVING COUNT (distinct(playerid)) =(\n",
    "SELECT MAX (count)\n",
    "FROM\n",
    "(SELECT schoolid, count(distinct(playerid))\n",
    "from collegeplaying \n",
    "group by schoolid\n",
    "order by count desc) \n",
    "as \"table\")\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(query, cnx)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>schoolid</th>\n",
       "      <th>avg_starting_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td> kennesawst</td>\n",
       "      <td> 2443690.285714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     schoolid  avg_starting_salary\n",
       "0  kennesawst       2443690.285714"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#10. Which school has generated the most expensive players? (expensive defined by their first year's salary).\n",
    "\n",
    "# to get starting salary per player\n",
    "query = \"\"\"\n",
    "select s.playerid, \n",
    "s.yearid, \n",
    "salary \n",
    "from \n",
    "salaries as s\n",
    "inner join\n",
    "(select playerid, \n",
    "MIN(yearid)\n",
    "from salaries\n",
    "group by playerid\n",
    "order by playerid) as \"p\"\n",
    "on p.playerid = s.playerid\n",
    "and p.min = s.yearid\n",
    "order by s.playerid, s.yearid\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "# join players to each school they attended and their average starting salary\n",
    "query = \"\"\"\n",
    "select c.schoolid, AVG(start.salary) as avg_starting_salary\n",
    "from \n",
    "collegeplaying as c\n",
    "inner join\n",
    "(select s.playerid, \n",
    "s.yearid, \n",
    "salary \n",
    "from \n",
    "salaries as s\n",
    "inner join\n",
    "(select playerid, \n",
    "MIN(yearid)\n",
    "from salaries\n",
    "group by playerid\n",
    "order by playerid) as \"p\"\n",
    "on p.playerid = s.playerid\n",
    "and p.min = s.yearid) as \"start\"\n",
    "on c.playerid = start.playerid\n",
    "group by c.schoolid\n",
    "order by avg_starting_salary desc\n",
    "limit 1\n",
    "\"\"\"\n",
    "\n",
    "# this attributes starting salary to more than one school if player attended more than one school\n",
    "\n",
    "pd.read_sql_query(query, cnx)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>teamid</th>\n",
       "      <th>yearid</th>\n",
       "      <th>salary</th>\n",
       "      <th>salary_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0  </th>\n",
       "      <td> ARI</td>\n",
       "      <td> pradoma01</td>\n",
       "      <td> 11000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1  </th>\n",
       "      <td> ARI</td>\n",
       "      <td>  hillaa01</td>\n",
       "      <td> 11000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2  </th>\n",
       "      <td> ARI</td>\n",
       "      <td> montemi01</td>\n",
       "      <td> 10000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3  </th>\n",
       "      <td> ARI</td>\n",
       "      <td> arroybr01</td>\n",
       "      <td>  9500000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4  </th>\n",
       "      <td> ARI</td>\n",
       "      <td>  rossco01</td>\n",
       "      <td>  9500000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5  </th>\n",
       "      <td> ATL</td>\n",
       "      <td> uptonju01</td>\n",
       "      <td> 14250000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6  </th>\n",
       "      <td> ATL</td>\n",
       "      <td> santaer01</td>\n",
       "      <td> 14100000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7  </th>\n",
       "      <td> ATL</td>\n",
       "      <td> uptonbj01</td>\n",
       "      <td> 13450000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8  </th>\n",
       "      <td> ATL</td>\n",
       "      <td> ugglada01</td>\n",
       "      <td> 13000000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9  </th>\n",
       "      <td> ATL</td>\n",
       "      <td> kimbrcr01</td>\n",
       "      <td>  7000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10 </th>\n",
       "      <td> BAL</td>\n",
       "      <td> markani01</td>\n",
       "      <td> 15000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11 </th>\n",
       "      <td> BAL</td>\n",
       "      <td> jonesad01</td>\n",
       "      <td> 13000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 </th>\n",
       "      <td> BAL</td>\n",
       "      <td> jimenub01</td>\n",
       "      <td> 11250000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13 </th>\n",
       "      <td> BAL</td>\n",
       "      <td> davisch02</td>\n",
       "      <td> 10350000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14 </th>\n",
       "      <td> BAL</td>\n",
       "      <td>  cruzne02</td>\n",
       "      <td>  8000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15 </th>\n",
       "      <td> BOS</td>\n",
       "      <td> napolmi01</td>\n",
       "      <td> 16000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16 </th>\n",
       "      <td> BOS</td>\n",
       "      <td> ortizda01</td>\n",
       "      <td> 15000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17 </th>\n",
       "      <td> BOS</td>\n",
       "      <td> victosh01</td>\n",
       "      <td> 13000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18 </th>\n",
       "      <td> BOS</td>\n",
       "      <td> lestejo01</td>\n",
       "      <td> 13000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19 </th>\n",
       "      <td> BOS</td>\n",
       "      <td> pedrodu01</td>\n",
       "      <td> 12500000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20 </th>\n",
       "      <td> CHA</td>\n",
       "      <td>  dunnad01</td>\n",
       "      <td> 15000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21 </th>\n",
       "      <td> CHA</td>\n",
       "      <td> danksjo01</td>\n",
       "      <td> 14250000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22 </th>\n",
       "      <td> CHA</td>\n",
       "      <td> ramiral03</td>\n",
       "      <td>  9500000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23 </th>\n",
       "      <td> CHA</td>\n",
       "      <td> abreujo02</td>\n",
       "      <td>  7000000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24 </th>\n",
       "      <td> CHA</td>\n",
       "      <td> deazaal01</td>\n",
       "      <td>  4250000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25 </th>\n",
       "      <td> CHN</td>\n",
       "      <td> jacksed01</td>\n",
       "      <td> 11000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26 </th>\n",
       "      <td> CHN</td>\n",
       "      <td> hammeja01</td>\n",
       "      <td>  6000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27 </th>\n",
       "      <td> CHN</td>\n",
       "      <td> castrst01</td>\n",
       "      <td>  5000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28 </th>\n",
       "      <td> CHN</td>\n",
       "      <td> schiena01</td>\n",
       "      <td>  5000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29 </th>\n",
       "      <td> CHN</td>\n",
       "      <td> villaca01</td>\n",
       "      <td>  5000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td> SFG</td>\n",
       "      <td> peavyja01</td>\n",
       "      <td> 14500000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td> SFG</td>\n",
       "      <td> poseybu01</td>\n",
       "      <td> 12500000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td> SFG</td>\n",
       "      <td> hudsoti01</td>\n",
       "      <td> 11000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td> SFN</td>\n",
       "      <td>  cainma01</td>\n",
       "      <td> 20000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td> SLN</td>\n",
       "      <td> wainwad01</td>\n",
       "      <td> 19500000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td> SLN</td>\n",
       "      <td> hollima01</td>\n",
       "      <td> 17000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td> SLN</td>\n",
       "      <td> peraljh01</td>\n",
       "      <td> 15500000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td> SLN</td>\n",
       "      <td> lackejo01</td>\n",
       "      <td> 15250000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td> SLN</td>\n",
       "      <td> molinya01</td>\n",
       "      <td> 15000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td> TBA</td>\n",
       "      <td> priceda01</td>\n",
       "      <td> 14000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td> TBA</td>\n",
       "      <td>  bellhe01</td>\n",
       "      <td>  9000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td> TBA</td>\n",
       "      <td> longoev01</td>\n",
       "      <td>  7500000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td> TBA</td>\n",
       "      <td> zobribe01</td>\n",
       "      <td>  7000000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td> TBA</td>\n",
       "      <td> escobyu01</td>\n",
       "      <td>  5000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td> TEX</td>\n",
       "      <td> fieldpr01</td>\n",
       "      <td> 24000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td> TEX</td>\n",
       "      <td> beltrad01</td>\n",
       "      <td> 17000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td> TEX</td>\n",
       "      <td>  choosh01</td>\n",
       "      <td> 14000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td> TEX</td>\n",
       "      <td>  riosal01</td>\n",
       "      <td> 12500000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td> TEX</td>\n",
       "      <td> darviyu01</td>\n",
       "      <td> 10000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td> TOR</td>\n",
       "      <td> reyesjo01</td>\n",
       "      <td> 16000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td> TOR</td>\n",
       "      <td> bautijo02</td>\n",
       "      <td> 14000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td> TOR</td>\n",
       "      <td> dickera01</td>\n",
       "      <td> 12000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td> TOR</td>\n",
       "      <td> encared01</td>\n",
       "      <td>  9000000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td> TOR</td>\n",
       "      <td> cabreme01</td>\n",
       "      <td>  8000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td> TOR</td>\n",
       "      <td> morrobr01</td>\n",
       "      <td>  8000000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td> WAS</td>\n",
       "      <td> werthja01</td>\n",
       "      <td> 20000000</td>\n",
       "      <td> 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td> WAS</td>\n",
       "      <td> zimmery01</td>\n",
       "      <td> 14000000</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td> WAS</td>\n",
       "      <td> larocad01</td>\n",
       "      <td> 12000000</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td> WAS</td>\n",
       "      <td> soriara01</td>\n",
       "      <td> 11000000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td> WAS</td>\n",
       "      <td> gonzagi01</td>\n",
       "      <td>  8500000</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>156 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    teamid     yearid    salary  salary_rank\n",
       "0      ARI  pradoma01  11000000            1\n",
       "1      ARI   hillaa01  11000000            1\n",
       "2      ARI  montemi01  10000000            3\n",
       "3      ARI  arroybr01   9500000            4\n",
       "4      ARI   rossco01   9500000            4\n",
       "5      ATL  uptonju01  14250000            1\n",
       "6      ATL  santaer01  14100000            2\n",
       "7      ATL  uptonbj01  13450000            3\n",
       "8      ATL  ugglada01  13000000            4\n",
       "9      ATL  kimbrcr01   7000000            5\n",
       "10     BAL  markani01  15000000            1\n",
       "11     BAL  jonesad01  13000000            2\n",
       "12     BAL  jimenub01  11250000            3\n",
       "13     BAL  davisch02  10350000            4\n",
       "14     BAL   cruzne02   8000000            5\n",
       "15     BOS  napolmi01  16000000            1\n",
       "16     BOS  ortizda01  15000000            2\n",
       "17     BOS  victosh01  13000000            3\n",
       "18     BOS  lestejo01  13000000            3\n",
       "19     BOS  pedrodu01  12500000            5\n",
       "20     CHA   dunnad01  15000000            1\n",
       "21     CHA  danksjo01  14250000            2\n",
       "22     CHA  ramiral03   9500000            3\n",
       "23     CHA  abreujo02   7000000            4\n",
       "24     CHA  deazaal01   4250000            5\n",
       "25     CHN  jacksed01  11000000            1\n",
       "26     CHN  hammeja01   6000000            2\n",
       "27     CHN  castrst01   5000000            3\n",
       "28     CHN  schiena01   5000000            3\n",
       "29     CHN  villaca01   5000000            3\n",
       "..     ...        ...       ...          ...\n",
       "126    SFG  peavyja01  14500000            3\n",
       "127    SFG  poseybu01  12500000            4\n",
       "128    SFG  hudsoti01  11000000            5\n",
       "129    SFN   cainma01  20000000            1\n",
       "130    SLN  wainwad01  19500000            1\n",
       "131    SLN  hollima01  17000000            2\n",
       "132    SLN  peraljh01  15500000            3\n",
       "133    SLN  lackejo01  15250000            4\n",
       "134    SLN  molinya01  15000000            5\n",
       "135    TBA  priceda01  14000000            1\n",
       "136    TBA   bellhe01   9000000            2\n",
       "137    TBA  longoev01   7500000            3\n",
       "138    TBA  zobribe01   7000000            4\n",
       "139    TBA  escobyu01   5000000            5\n",
       "140    TEX  fieldpr01  24000000            1\n",
       "141    TEX  beltrad01  17000000            2\n",
       "142    TEX   choosh01  14000000            3\n",
       "143    TEX   riosal01  12500000            4\n",
       "144    TEX  darviyu01  10000000            5\n",
       "145    TOR  reyesjo01  16000000            1\n",
       "146    TOR  bautijo02  14000000            2\n",
       "147    TOR  dickera01  12000000            3\n",
       "148    TOR  encared01   9000000            4\n",
       "149    TOR  cabreme01   8000000            5\n",
       "150    TOR  morrobr01   8000000            5\n",
       "151    WAS  werthja01  20000000            1\n",
       "152    WAS  zimmery01  14000000            2\n",
       "153    WAS  larocad01  12000000            3\n",
       "154    WAS  soriara01  11000000            4\n",
       "155    WAS  gonzagi01   8500000            5\n",
       "\n",
       "[156 rows x 4 columns]"
      ]
     },
     "execution_count": 224,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#11. Show the 5 most expensive salaries for each team in the year 2014.\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT * FROM\n",
    "(SELECT teamid, \n",
    "playerid\n",
    "yearid,\n",
    "salary,\n",
    "rank() over (PARTITION BY teamid ORDER BY salary desc) as \"salary_rank\"\n",
    "from salaries\n",
    "where yearid = '2014') as \"rank\"\n",
    "where salary_rank < 6\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>teamid</th>\n",
       "      <th>yearid</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 1997</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 1998</td>\n",
       "      <td> 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 1999</td>\n",
       "      <td> 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 2000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 2001</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 2002</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 2003</td>\n",
       "      <td> 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7  </th>\n",
       "      <td> ANA</td>\n",
       "      <td> 2004</td>\n",
       "      <td> 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8  </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 1998</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9  </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 1999</td>\n",
       "      <td> 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2001</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2002</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2003</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2004</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2005</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2006</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2007</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2008</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2009</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2010</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2011</td>\n",
       "      <td> 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2012</td>\n",
       "      <td> 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2013</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24 </th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25 </th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1985</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26 </th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1986</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27 </th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1987</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28 </th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1988</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29 </th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1989</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 1995</td>\n",
       "      <td> 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>830</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 1996</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>831</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 1997</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>832</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 1998</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>833</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 1999</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>834</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2000</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>835</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2001</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>836</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2002</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>837</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2003</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>838</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2004</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>839</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2005</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>840</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2006</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>841</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2007</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2008</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>843</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2009</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>844</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2010</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2011</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>846</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2012</td>\n",
       "      <td> 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>847</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2013</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>848</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>849</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2005</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2006</td>\n",
       "      <td> 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>851</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2007</td>\n",
       "      <td> 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>852</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2008</td>\n",
       "      <td> 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>853</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2009</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>854</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2010</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>855</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2011</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>856</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2012</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2013</td>\n",
       "      <td> 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>859 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    teamid  yearid  count\n",
       "0      ANA    1997      4\n",
       "1      ANA    1998      7\n",
       "2      ANA    1999      6\n",
       "3      ANA    2000      4\n",
       "4      ANA    2001      3\n",
       "5      ANA    2002      5\n",
       "6      ANA    2003      6\n",
       "7      ANA    2004      7\n",
       "8      ARI    1998      5\n",
       "9      ARI    1999      7\n",
       "10     ARI    2000      4\n",
       "11     ARI    2001      5\n",
       "12     ARI    2002      5\n",
       "13     ARI    2003      4\n",
       "14     ARI    2004      5\n",
       "15     ARI    2005      5\n",
       "16     ARI    2006      3\n",
       "17     ARI    2007      5\n",
       "18     ARI    2008      4\n",
       "19     ARI    2009      5\n",
       "20     ARI    2010      3\n",
       "21     ARI    2011      6\n",
       "22     ARI    2012      7\n",
       "23     ARI    2013      5\n",
       "24     ARI    2014      6\n",
       "25     ATL    1985      3\n",
       "26     ATL    1986      3\n",
       "27     ATL    1987      3\n",
       "28     ATL    1988      2\n",
       "29     ATL    1989      3\n",
       "..     ...     ...    ...\n",
       "829    TOR    1995      7\n",
       "830    TOR    1996      2\n",
       "831    TOR    1997      4\n",
       "832    TOR    1998      5\n",
       "833    TOR    1999      4\n",
       "834    TOR    2000      4\n",
       "835    TOR    2001      3\n",
       "836    TOR    2002      2\n",
       "837    TOR    2003      2\n",
       "838    TOR    2004      2\n",
       "839    TOR    2005      2\n",
       "840    TOR    2006      2\n",
       "841    TOR    2007      5\n",
       "842    TOR    2008      5\n",
       "843    TOR    2009      4\n",
       "844    TOR    2010      2\n",
       "845    TOR    2011      5\n",
       "846    TOR    2012      2\n",
       "847    TOR    2013      4\n",
       "848    TOR    2014      4\n",
       "849    WAS    2005      3\n",
       "850    WAS    2006      3\n",
       "851    WAS    2007      6\n",
       "852    WAS    2008      7\n",
       "853    WAS    2009      5\n",
       "854    WAS    2010      4\n",
       "855    WAS    2011      4\n",
       "856    WAS    2012      4\n",
       "857    WAS    2013      5\n",
       "858    WAS    2014      4\n",
       "\n",
       "[859 rows x 3 columns]"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 12. Partition the average salaries by team and year, against year. \n",
    "# Find players that were paid more than 1 standard deviation above the average salary for that team and year. \n",
    "# Show a count by playerid.\n",
    "\n",
    "#show the players that were paid > 1 stddev more than the average\n",
    "query = \"\"\"\n",
    "select * from salaries s\n",
    "left join\n",
    "(SELECT teamid, \n",
    "yearid,\n",
    "AVG(salary),\n",
    "STDDEV(salary)\n",
    "from salaries\n",
    "group by\n",
    "teamid, yearid\n",
    "order by teamid, yearid) as \"m\" \n",
    "on s.teamid = m.teamid \n",
    "and s.yearid = m.yearid\n",
    "where s.salary > m.avg + m.stddev\n",
    "order by s.teamid, s.yearid\n",
    "\"\"\"\n",
    "\n",
    "#show the counts for those players per team per year\n",
    "query = \"\"\"\n",
    "select s.teamid, s.yearid, count(playerid) from salaries s\n",
    "left join\n",
    "(SELECT teamid, \n",
    "yearid,\n",
    "AVG(salary),\n",
    "STDDEV(salary)\n",
    "from salaries\n",
    "group by\n",
    "teamid, yearid\n",
    "order by teamid, yearid) as \"m\" \n",
    "on s.teamid = m.teamid \n",
    "and s.yearid = m.yearid\n",
    "where s.salary > m.avg + m.stddev\n",
    "group by s.teamid, s.yearid\n",
    "order by s.teamid, s.yearid\"\"\"\n",
    "\n",
    "\n",
    "pd.read_sql_query(query, cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>teamid</th>\n",
       "      <th>yearid</th>\n",
       "      <th>win</th>\n",
       "      <th>games</th>\n",
       "      <th>percent_win</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0   </th>\n",
       "      <td> PH1</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 21</td>\n",
       "      <td>  28</td>\n",
       "      <td> 0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1   </th>\n",
       "      <td> CH1</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 19</td>\n",
       "      <td>  28</td>\n",
       "      <td> 0.678571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2   </th>\n",
       "      <td> BS1</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 20</td>\n",
       "      <td>  31</td>\n",
       "      <td> 0.645161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3   </th>\n",
       "      <td> NY2</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 16</td>\n",
       "      <td>  33</td>\n",
       "      <td> 0.484848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4   </th>\n",
       "      <td> WS3</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 15</td>\n",
       "      <td>  32</td>\n",
       "      <td> 0.468750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5   </th>\n",
       "      <td> TRO</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 13</td>\n",
       "      <td>  29</td>\n",
       "      <td> 0.448276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6   </th>\n",
       "      <td> FW1</td>\n",
       "      <td> 1871</td>\n",
       "      <td>  7</td>\n",
       "      <td>  19</td>\n",
       "      <td> 0.368421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7   </th>\n",
       "      <td> CL1</td>\n",
       "      <td> 1871</td>\n",
       "      <td> 10</td>\n",
       "      <td>  29</td>\n",
       "      <td> 0.344828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8   </th>\n",
       "      <td> RC1</td>\n",
       "      <td> 1871</td>\n",
       "      <td>  4</td>\n",
       "      <td>  25</td>\n",
       "      <td> 0.160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9   </th>\n",
       "      <td> BS1</td>\n",
       "      <td> 1872</td>\n",
       "      <td> 39</td>\n",
       "      <td>  48</td>\n",
       "      <td> 0.812500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10  </th>\n",
       "      <td> PH1</td>\n",
       "      <td> 1872</td>\n",
       "      <td> 30</td>\n",
       "      <td>  47</td>\n",
       "      <td> 0.638298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11  </th>\n",
       "      <td> NY2</td>\n",
       "      <td> 1872</td>\n",
       "      <td> 34</td>\n",
       "      <td>  56</td>\n",
       "      <td> 0.607143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12  </th>\n",
       "      <td> BL1</td>\n",
       "      <td> 1872</td>\n",
       "      <td> 35</td>\n",
       "      <td>  58</td>\n",
       "      <td> 0.603448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13  </th>\n",
       "      <td> TRO</td>\n",
       "      <td> 1872</td>\n",
       "      <td> 15</td>\n",
       "      <td>  25</td>\n",
       "      <td> 0.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14  </th>\n",
       "      <td> CL1</td>\n",
       "      <td> 1872</td>\n",
       "      <td>  6</td>\n",
       "      <td>  22</td>\n",
       "      <td> 0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15  </th>\n",
       "      <td> BR2</td>\n",
       "      <td> 1872</td>\n",
       "      <td>  9</td>\n",
       "      <td>  37</td>\n",
       "      <td> 0.243243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16  </th>\n",
       "      <td> WS3</td>\n",
       "      <td> 1872</td>\n",
       "      <td>  2</td>\n",
       "      <td>   9</td>\n",
       "      <td> 0.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17  </th>\n",
       "      <td> MID</td>\n",
       "      <td> 1872</td>\n",
       "      <td>  5</td>\n",
       "      <td>  24</td>\n",
       "      <td> 0.208333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18  </th>\n",
       "      <td> BR1</td>\n",
       "      <td> 1872</td>\n",
       "      <td>  3</td>\n",
       "      <td>  29</td>\n",
       "      <td> 0.103448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19  </th>\n",
       "      <td> WS4</td>\n",
       "      <td> 1872</td>\n",
       "      <td>  0</td>\n",
       "      <td>  11</td>\n",
       "      <td> 0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20  </th>\n",
       "      <td> BS1</td>\n",
       "      <td> 1873</td>\n",
       "      <td> 43</td>\n",
       "      <td>  60</td>\n",
       "      <td> 0.716667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21  </th>\n",
       "      <td> PH2</td>\n",
       "      <td> 1873</td>\n",
       "      <td> 36</td>\n",
       "      <td>  53</td>\n",
       "      <td> 0.679245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22  </th>\n",
       "      <td> BL1</td>\n",
       "      <td> 1873</td>\n",
       "      <td> 34</td>\n",
       "      <td>  57</td>\n",
       "      <td> 0.596491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23  </th>\n",
       "      <td> NY2</td>\n",
       "      <td> 1873</td>\n",
       "      <td> 29</td>\n",
       "      <td>  53</td>\n",
       "      <td> 0.547170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24  </th>\n",
       "      <td> PH1</td>\n",
       "      <td> 1873</td>\n",
       "      <td> 28</td>\n",
       "      <td>  52</td>\n",
       "      <td> 0.538462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25  </th>\n",
       "      <td> BR2</td>\n",
       "      <td> 1873</td>\n",
       "      <td> 17</td>\n",
       "      <td>  55</td>\n",
       "      <td> 0.309091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26  </th>\n",
       "      <td> WS5</td>\n",
       "      <td> 1873</td>\n",
       "      <td>  8</td>\n",
       "      <td>  39</td>\n",
       "      <td> 0.205128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27  </th>\n",
       "      <td> ELI</td>\n",
       "      <td> 1873</td>\n",
       "      <td>  2</td>\n",
       "      <td>  23</td>\n",
       "      <td> 0.086957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28  </th>\n",
       "      <td> BL4</td>\n",
       "      <td> 1873</td>\n",
       "      <td>  0</td>\n",
       "      <td>   6</td>\n",
       "      <td> 0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29  </th>\n",
       "      <td> BS1</td>\n",
       "      <td> 1874</td>\n",
       "      <td> 52</td>\n",
       "      <td>  71</td>\n",
       "      <td> 0.732394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2745</th>\n",
       "      <td> LAA</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 98</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.604938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2746</th>\n",
       "      <td> WAS</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 96</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.592593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747</th>\n",
       "      <td> BAL</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 96</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.592593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2748</th>\n",
       "      <td> LAN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 94</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.580247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2749</th>\n",
       "      <td> DET</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 90</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2750</th>\n",
       "      <td> SLN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 90</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2751</th>\n",
       "      <td> KCA</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 89</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.549383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2752</th>\n",
       "      <td> SFN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 88</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.543210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2753</th>\n",
       "      <td> OAK</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 88</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.543210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2754</th>\n",
       "      <td> PIT</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 88</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.543210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2755</th>\n",
       "      <td> SEA</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 87</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.537037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2756</th>\n",
       "      <td> CLE</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 85</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.524691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2757</th>\n",
       "      <td> NYA</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 84</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.518519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2758</th>\n",
       "      <td> TOR</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 83</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.512346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2759</th>\n",
       "      <td> MIL</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 82</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.506173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2760</th>\n",
       "      <td> NYN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 79</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.487654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2761</th>\n",
       "      <td> ATL</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 79</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.487654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2762</th>\n",
       "      <td> MIA</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 77</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.475309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2763</th>\n",
       "      <td> TBA</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 77</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.475309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2764</th>\n",
       "      <td> SDN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 77</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.475309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2765</th>\n",
       "      <td> CIN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 76</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.469136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2766</th>\n",
       "      <td> PHI</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 73</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.450617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2767</th>\n",
       "      <td> CHA</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 73</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.450617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2768</th>\n",
       "      <td> CHN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 73</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.450617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2769</th>\n",
       "      <td> BOS</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 71</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.438272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2770</th>\n",
       "      <td> MIN</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 70</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.432099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2771</th>\n",
       "      <td> HOU</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 70</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.432099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2772</th>\n",
       "      <td> TEX</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 67</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.413580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2773</th>\n",
       "      <td> COL</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 66</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.407407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2774</th>\n",
       "      <td> ARI</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 64</td>\n",
       "      <td> 162</td>\n",
       "      <td> 0.395062</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2775 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     teamid  yearid  win  games  percent_win\n",
       "0       PH1    1871   21     28     0.750000\n",
       "1       CH1    1871   19     28     0.678571\n",
       "2       BS1    1871   20     31     0.645161\n",
       "3       NY2    1871   16     33     0.484848\n",
       "4       WS3    1871   15     32     0.468750\n",
       "5       TRO    1871   13     29     0.448276\n",
       "6       FW1    1871    7     19     0.368421\n",
       "7       CL1    1871   10     29     0.344828\n",
       "8       RC1    1871    4     25     0.160000\n",
       "9       BS1    1872   39     48     0.812500\n",
       "10      PH1    1872   30     47     0.638298\n",
       "11      NY2    1872   34     56     0.607143\n",
       "12      BL1    1872   35     58     0.603448\n",
       "13      TRO    1872   15     25     0.600000\n",
       "14      CL1    1872    6     22     0.272727\n",
       "15      BR2    1872    9     37     0.243243\n",
       "16      WS3    1872    2      9     0.222222\n",
       "17      MID    1872    5     24     0.208333\n",
       "18      BR1    1872    3     29     0.103448\n",
       "19      WS4    1872    0     11     0.000000\n",
       "20      BS1    1873   43     60     0.716667\n",
       "21      PH2    1873   36     53     0.679245\n",
       "22      BL1    1873   34     57     0.596491\n",
       "23      NY2    1873   29     53     0.547170\n",
       "24      PH1    1873   28     52     0.538462\n",
       "25      BR2    1873   17     55     0.309091\n",
       "26      WS5    1873    8     39     0.205128\n",
       "27      ELI    1873    2     23     0.086957\n",
       "28      BL4    1873    0      6     0.000000\n",
       "29      BS1    1874   52     71     0.732394\n",
       "...     ...     ...  ...    ...          ...\n",
       "2745    LAA    2014   98    162     0.604938\n",
       "2746    WAS    2014   96    162     0.592593\n",
       "2747    BAL    2014   96    162     0.592593\n",
       "2748    LAN    2014   94    162     0.580247\n",
       "2749    DET    2014   90    162     0.555556\n",
       "2750    SLN    2014   90    162     0.555556\n",
       "2751    KCA    2014   89    162     0.549383\n",
       "2752    SFN    2014   88    162     0.543210\n",
       "2753    OAK    2014   88    162     0.543210\n",
       "2754    PIT    2014   88    162     0.543210\n",
       "2755    SEA    2014   87    162     0.537037\n",
       "2756    CLE    2014   85    162     0.524691\n",
       "2757    NYA    2014   84    162     0.518519\n",
       "2758    TOR    2014   83    162     0.512346\n",
       "2759    MIL    2014   82    162     0.506173\n",
       "2760    NYN    2014   79    162     0.487654\n",
       "2761    ATL    2014   79    162     0.487654\n",
       "2762    MIA    2014   77    162     0.475309\n",
       "2763    TBA    2014   77    162     0.475309\n",
       "2764    SDN    2014   77    162     0.475309\n",
       "2765    CIN    2014   76    162     0.469136\n",
       "2766    PHI    2014   73    162     0.450617\n",
       "2767    CHA    2014   73    162     0.450617\n",
       "2768    CHN    2014   73    162     0.450617\n",
       "2769    BOS    2014   71    162     0.438272\n",
       "2770    MIN    2014   70    162     0.432099\n",
       "2771    HOU    2014   70    162     0.432099\n",
       "2772    TEX    2014   67    162     0.413580\n",
       "2773    COL    2014   66    162     0.407407\n",
       "2774    ARI    2014   64    162     0.395062\n",
       "\n",
       "[2775 rows x 5 columns]"
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#13. Calculate the win percentage. convert w and g into numerics (floats) to do so. (w::numeric, for example)\n",
    "query = \"\"\"\n",
    "select teamid, \n",
    "yearid,\n",
    "w as win,\n",
    "g as games,\n",
    "CAST(w AS float)/CAST(g AS float) as percent_win \n",
    "from teams\n",
    "order by yearid, percent_win desc\"\"\"\n",
    "#query = \"\"\"select * from teams\"\"\"\n",
    "\n",
    "pd.read_sql_query(query, cnx)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yearid</th>\n",
       "      <th>real_rank</th>\n",
       "      <th>spend_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>yearid</th>\n",
       "      <td> 1.000000</td>\n",
       "      <td>-0.290759</td>\n",
       "      <td> 0.095775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>real_rank</th>\n",
       "      <td>-0.290759</td>\n",
       "      <td> 1.000000</td>\n",
       "      <td>-0.317516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>spend_rank</th>\n",
       "      <td> 0.095775</td>\n",
       "      <td>-0.317516</td>\n",
       "      <td> 1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              yearid  real_rank  spend_rank\n",
       "yearid      1.000000  -0.290759    0.095775\n",
       "real_rank  -0.290759   1.000000   -0.317516\n",
       "spend_rank  0.095775  -0.317516    1.000000"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 14. rank() the total spend by team each year against their actual rank that year. \n",
    "# Is there a correlation of spend to performance?\n",
    "\n",
    "# rank of team spend per year\n",
    "\n",
    "query = \"\"\"\n",
    "select s.*,\n",
    "rank() over (partition by yearid order by s.sum)\n",
    "from \n",
    "(select teamid, \n",
    "yearid, \n",
    "sum(salary) as \"sum\"\n",
    "from salaries\n",
    "group by teamid, yearid\n",
    "order by teamid, yearid) as \"s\" \"\"\"\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT t.teamid, t.yearid, t.rank as real_rank, r.rank as spend_rank\n",
    "from teams as t\n",
    "left join\n",
    "(select s.*,\n",
    "rank() over (partition by yearid order by s.sum)\n",
    "from \n",
    "(select teamid, \n",
    "yearid, \n",
    "sum(salary) as \"sum\"\n",
    "from salaries\n",
    "group by teamid, yearid\n",
    "order by teamid, yearid) as \"s\") as \"r\"\n",
    "on r.teamid = t.teamid\n",
    "and r.yearid = t.yearid \"\"\"\n",
    "\n",
    "\n",
    "df = pd.read_sql_query(query, cnx)\n",
    "df.corr()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Shows that there is a negative correlation between the real rank of the team versus the amount the team spends \n",
    "on salaries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>teamid</th>\n",
       "      <th>yearid</th>\n",
       "      <th>real_rank</th>\n",
       "      <th>spend_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0   </th>\n",
       "      <td> SEA</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  6</td>\n",
       "      <td>  1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1   </th>\n",
       "      <td> MIN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  4</td>\n",
       "      <td>  2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2   </th>\n",
       "      <td> CLE</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  7</td>\n",
       "      <td>  3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3   </th>\n",
       "      <td> TEX</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  7</td>\n",
       "      <td>  4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4   </th>\n",
       "      <td> SFN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  6</td>\n",
       "      <td>  5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5   </th>\n",
       "      <td> CIN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  2</td>\n",
       "      <td>  6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6   </th>\n",
       "      <td> TOR</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7   </th>\n",
       "      <td> OAK</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  4</td>\n",
       "      <td>  8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8   </th>\n",
       "      <td> PIT</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  6</td>\n",
       "      <td>  9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9   </th>\n",
       "      <td> KCA</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  1</td>\n",
       "      <td> 10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10  </th>\n",
       "      <td> MON</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  3</td>\n",
       "      <td> 11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11  </th>\n",
       "      <td> CHA</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  3</td>\n",
       "      <td> 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12  </th>\n",
       "      <td> HOU</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  3</td>\n",
       "      <td> 13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13  </th>\n",
       "      <td> PHI</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  5</td>\n",
       "      <td> 14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14  </th>\n",
       "      <td> DET</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  3</td>\n",
       "      <td> 15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15  </th>\n",
       "      <td> NYN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  2</td>\n",
       "      <td> 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16  </th>\n",
       "      <td> BOS</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  5</td>\n",
       "      <td> 17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17  </th>\n",
       "      <td> LAN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  1</td>\n",
       "      <td> 18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18  </th>\n",
       "      <td> SDN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  3</td>\n",
       "      <td> 19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19  </th>\n",
       "      <td> ML4</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  6</td>\n",
       "      <td> 20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20  </th>\n",
       "      <td> BAL</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  4</td>\n",
       "      <td> 21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21  </th>\n",
       "      <td> SLN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  1</td>\n",
       "      <td> 22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22  </th>\n",
       "      <td> CHN</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  4</td>\n",
       "      <td> 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23  </th>\n",
       "      <td> NYA</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  2</td>\n",
       "      <td> 24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24  </th>\n",
       "      <td> CAL</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  2</td>\n",
       "      <td> 25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25  </th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1985</td>\n",
       "      <td>  5</td>\n",
       "      <td> 26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26  </th>\n",
       "      <td> SEA</td>\n",
       "      <td> 1986</td>\n",
       "      <td>  7</td>\n",
       "      <td>  1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27  </th>\n",
       "      <td> TEX</td>\n",
       "      <td> 1986</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28  </th>\n",
       "      <td> CLE</td>\n",
       "      <td> 1986</td>\n",
       "      <td>  5</td>\n",
       "      <td>  3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29  </th>\n",
       "      <td> MIN</td>\n",
       "      <td> 1986</td>\n",
       "      <td>  6</td>\n",
       "      <td>  4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2745</th>\n",
       "      <td> CIN</td>\n",
       "      <td> 1972</td>\n",
       "      <td>  1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2746</th>\n",
       "      <td> SLN</td>\n",
       "      <td> 1913</td>\n",
       "      <td>  8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747</th>\n",
       "      <td> SLN</td>\n",
       "      <td> 1958</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2748</th>\n",
       "      <td> BAL</td>\n",
       "      <td> 1957</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2749</th>\n",
       "      <td> CIN</td>\n",
       "      <td> 1956</td>\n",
       "      <td>  3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2750</th>\n",
       "      <td> WS1</td>\n",
       "      <td> 1960</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2751</th>\n",
       "      <td> KCA</td>\n",
       "      <td> 1978</td>\n",
       "      <td>  1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2752</th>\n",
       "      <td> SDN</td>\n",
       "      <td> 1972</td>\n",
       "      <td>  6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2753</th>\n",
       "      <td> CHA</td>\n",
       "      <td> 1939</td>\n",
       "      <td>  4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2754</th>\n",
       "      <td> SEA</td>\n",
       "      <td> 1983</td>\n",
       "      <td>  7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2755</th>\n",
       "      <td> CHN</td>\n",
       "      <td> 1885</td>\n",
       "      <td>  1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2756</th>\n",
       "      <td> PH3</td>\n",
       "      <td> 1875</td>\n",
       "      <td> 11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2757</th>\n",
       "      <td> BOS</td>\n",
       "      <td> 1977</td>\n",
       "      <td>  2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2758</th>\n",
       "      <td> LAN</td>\n",
       "      <td> 1965</td>\n",
       "      <td>  1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2759</th>\n",
       "      <td> SLA</td>\n",
       "      <td> 1912</td>\n",
       "      <td>  7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2760</th>\n",
       "      <td> DET</td>\n",
       "      <td> 1948</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2761</th>\n",
       "      <td> NYN</td>\n",
       "      <td> 1981</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2762</th>\n",
       "      <td> ML4</td>\n",
       "      <td> 1974</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2763</th>\n",
       "      <td> ATL</td>\n",
       "      <td> 1970</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2764</th>\n",
       "      <td> CHN</td>\n",
       "      <td> 1941</td>\n",
       "      <td>  6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2765</th>\n",
       "      <td> PHI</td>\n",
       "      <td> 1929</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2766</th>\n",
       "      <td> PHI</td>\n",
       "      <td> 1946</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2767</th>\n",
       "      <td> CIN</td>\n",
       "      <td> 1925</td>\n",
       "      <td>  3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2768</th>\n",
       "      <td> WS1</td>\n",
       "      <td> 1921</td>\n",
       "      <td>  4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2769</th>\n",
       "      <td> PIT</td>\n",
       "      <td> 1915</td>\n",
       "      <td>  5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2770</th>\n",
       "      <td> WS7</td>\n",
       "      <td> 1884</td>\n",
       "      <td> 13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2771</th>\n",
       "      <td> CIN</td>\n",
       "      <td> 1916</td>\n",
       "      <td>  7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2772</th>\n",
       "      <td> BRO</td>\n",
       "      <td> 1901</td>\n",
       "      <td>  3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2773</th>\n",
       "      <td> SLA</td>\n",
       "      <td> 1927</td>\n",
       "      <td>  7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2774</th>\n",
       "      <td> SLN</td>\n",
       "      <td> 1902</td>\n",
       "      <td>  6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2775 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     teamid  yearid  real_rank  spend_rank\n",
       "0       SEA    1985          6           1\n",
       "1       MIN    1985          4           2\n",
       "2       CLE    1985          7           3\n",
       "3       TEX    1985          7           4\n",
       "4       SFN    1985          6           5\n",
       "5       CIN    1985          2           6\n",
       "6       TOR    1985          1           7\n",
       "7       OAK    1985          4           8\n",
       "8       PIT    1985          6           9\n",
       "9       KCA    1985          1          10\n",
       "10      MON    1985          3          11\n",
       "11      CHA    1985          3          12\n",
       "12      HOU    1985          3          13\n",
       "13      PHI    1985          5          14\n",
       "14      DET    1985          3          15\n",
       "15      NYN    1985          2          16\n",
       "16      BOS    1985          5          17\n",
       "17      LAN    1985          1          18\n",
       "18      SDN    1985          3          19\n",
       "19      ML4    1985          6          20\n",
       "20      BAL    1985          4          21\n",
       "21      SLN    1985          1          22\n",
       "22      CHN    1985          4          23\n",
       "23      NYA    1985          2          24\n",
       "24      CAL    1985          2          25\n",
       "25      ATL    1985          5          26\n",
       "26      SEA    1986          7           1\n",
       "27      TEX    1986          2           2\n",
       "28      CLE    1986          5           3\n",
       "29      MIN    1986          6           4\n",
       "...     ...     ...        ...         ...\n",
       "2745    CIN    1972          1         NaN\n",
       "2746    SLN    1913          8         NaN\n",
       "2747    SLN    1958          5         NaN\n",
       "2748    BAL    1957          5         NaN\n",
       "2749    CIN    1956          3         NaN\n",
       "2750    WS1    1960          5         NaN\n",
       "2751    KCA    1978          1         NaN\n",
       "2752    SDN    1972          6         NaN\n",
       "2753    CHA    1939          4         NaN\n",
       "2754    SEA    1983          7         NaN\n",
       "2755    CHN    1885          1         NaN\n",
       "2756    PH3    1875         11         NaN\n",
       "2757    BOS    1977          2         NaN\n",
       "2758    LAN    1965          1         NaN\n",
       "2759    SLA    1912          7         NaN\n",
       "2760    DET    1948          5         NaN\n",
       "2761    NYN    1981          5         NaN\n",
       "2762    ML4    1974          5         NaN\n",
       "2763    ATL    1970          5         NaN\n",
       "2764    CHN    1941          6         NaN\n",
       "2765    PHI    1929          5         NaN\n",
       "2766    PHI    1946          5         NaN\n",
       "2767    CIN    1925          3         NaN\n",
       "2768    WS1    1921          4         NaN\n",
       "2769    PIT    1915          5         NaN\n",
       "2770    WS7    1884         13         NaN\n",
       "2771    CIN    1916          7         NaN\n",
       "2772    BRO    1901          3         NaN\n",
       "2773    SLA    1927          7         NaN\n",
       "2774    SLN    1902          6         NaN\n",
       "\n",
       "[2775 rows x 4 columns]"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  }
 ],
 "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
}