ex04-Constrain the Number of Rows Returned by a SELECT Query.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ex04-Constrain the Number of Rows Returned by a SELECT Query\n",
    "\n",
    "Often it is not convenient to show all rows on screen as you have to scroll. We can use use SQLite ***LIMIT*** clause to constrain the number of rows returned by a query. This can be used for testing or to prevent consuming excessive resources if the query returns more information than expected.\n",
    "\n",
    "The following illustrates the syntax of the LIMIT clause:\n",
    "- SELECT column_list FROM table_name LIMIT row_count;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Connet to the given database of demo.db3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'Connected: @data/demo.db3'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///data/demo.db3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Retrieving the first 10 rows of the table of watershed_yearly\n",
    "\n",
    "Add the LIMIT clause to the SELECT statement to get exact 10 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>YR</th>\n",
       "        <th>PREC_mm</th>\n",
       "        <th>SURQ_mm</th>\n",
       "        <th>LATQ_mm</th>\n",
       "        <th>GWQ_mm</th>\n",
       "        <th>PERCOLA_mm</th>\n",
       "        <th>TILEQ_mm</th>\n",
       "        <th>SW_mm</th>\n",
       "        <th>ET_mm</th>\n",
       "        <th>PET_mm</th>\n",
       "        <th>WYLD_mm</th>\n",
       "        <th>SYLD_tons</th>\n",
       "        <th>NO3_SURQ</th>\n",
       "        <th>NO3_LATQ</th>\n",
       "        <th>NO3_PERC</th>\n",
       "        <th>NO3_CROP</th>\n",
       "        <th>N_ORG</th>\n",
       "        <th>P_SOL</th>\n",
       "        <th>P_ORG</th>\n",
       "        <th>TILENO3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>895.605102539</td>\n",
       "        <td>130.357467651</td>\n",
       "        <td>51.3343925476</td>\n",
       "        <td>406.073944092</td>\n",
       "        <td>443.198486328</td>\n",
       "        <td>0.0</td>\n",
       "        <td>18819.7070312</td>\n",
       "        <td>236.983001709</td>\n",
       "        <td>840.687438965</td>\n",
       "        <td>609.706787109</td>\n",
       "        <td>91.0802612305</td>\n",
       "        <td>0.0329368636012</td>\n",
       "        <td>0.0641731917858</td>\n",
       "        <td>5.61589002609</td>\n",
       "        <td>7.33991575241</td>\n",
       "        <td>11.8574647903</td>\n",
       "        <td>0.00822062138468</td>\n",
       "        <td>1.46167123318</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1982</td>\n",
       "        <td>884.670654297</td>\n",
       "        <td>92.2126541138</td>\n",
       "        <td>50.3476905823</td>\n",
       "        <td>367.410705566</td>\n",
       "        <td>424.678375244</td>\n",
       "        <td>0.0</td>\n",
       "        <td>19425.8359375</td>\n",
       "        <td>256.79083252</td>\n",
       "        <td>872.074707031</td>\n",
       "        <td>530.024597168</td>\n",
       "        <td>64.8097839355</td>\n",
       "        <td>0.00797836109996</td>\n",
       "        <td>0.0491805076599</td>\n",
       "        <td>4.23980903625</td>\n",
       "        <td>7.67650604248</td>\n",
       "        <td>10.8738355637</td>\n",
       "        <td>0.00769735872746</td>\n",
       "        <td>1.34378290176</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1983</td>\n",
       "        <td>816.660522461</td>\n",
       "        <td>129.163101196</td>\n",
       "        <td>54.4730834961</td>\n",
       "        <td>445.333068848</td>\n",
       "        <td>462.763244629</td>\n",
       "        <td>0.0</td>\n",
       "        <td>18476.1953125</td>\n",
       "        <td>237.705551147</td>\n",
       "        <td>797.047363281</td>\n",
       "        <td>652.815795898</td>\n",
       "        <td>92.0479278564</td>\n",
       "        <td>0.137908875942</td>\n",
       "        <td>0.048595353961</td>\n",
       "        <td>3.88206338882</td>\n",
       "        <td>7.30162715912</td>\n",
       "        <td>12.2022075653</td>\n",
       "        <td>0.015354366973</td>\n",
       "        <td>1.51960933208</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1984</td>\n",
       "        <td>867.57434082</td>\n",
       "        <td>95.579460144</td>\n",
       "        <td>46.9486465454</td>\n",
       "        <td>347.244842529</td>\n",
       "        <td>408.539703369</td>\n",
       "        <td>0.0</td>\n",
       "        <td>17727.7128906</td>\n",
       "        <td>223.089752197</td>\n",
       "        <td>911.256347656</td>\n",
       "        <td>510.379333496</td>\n",
       "        <td>63.0908203125</td>\n",
       "        <td>0.0147083466873</td>\n",
       "        <td>0.0440557599068</td>\n",
       "        <td>5.1414809227</td>\n",
       "        <td>6.49211072922</td>\n",
       "        <td>10.941608429</td>\n",
       "        <td>0.0115751996636</td>\n",
       "        <td>1.37424087524</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1985</td>\n",
       "        <td>637.725524902</td>\n",
       "        <td>75.5576019287</td>\n",
       "        <td>45.3368835449</td>\n",
       "        <td>358.295318604</td>\n",
       "        <td>361.485229492</td>\n",
       "        <td>0.0</td>\n",
       "        <td>15840.5400391</td>\n",
       "        <td>236.321350098</td>\n",
       "        <td>990.855773926</td>\n",
       "        <td>499.339904785</td>\n",
       "        <td>49.6306915283</td>\n",
       "        <td>0.0547064580023</td>\n",
       "        <td>0.0465554222465</td>\n",
       "        <td>3.11303496361</td>\n",
       "        <td>6.52255487442</td>\n",
       "        <td>8.95903396606</td>\n",
       "        <td>0.0107987476513</td>\n",
       "        <td>1.11508142948</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1986</td>\n",
       "        <td>733.841247559</td>\n",
       "        <td>65.6301651001</td>\n",
       "        <td>36.7625923157</td>\n",
       "        <td>241.938842773</td>\n",
       "        <td>281.668334961</td>\n",
       "        <td>0.0</td>\n",
       "        <td>16059.4345703</td>\n",
       "        <td>243.030563354</td>\n",
       "        <td>977.513427734</td>\n",
       "        <td>358.093688965</td>\n",
       "        <td>48.7893180847</td>\n",
       "        <td>0.00105105829425</td>\n",
       "        <td>0.0405588187277</td>\n",
       "        <td>2.88651585579</td>\n",
       "        <td>6.50589036942</td>\n",
       "        <td>7.87006807327</td>\n",
       "        <td>0.0132865281776</td>\n",
       "        <td>0.970182478428</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1987</td>\n",
       "        <td>1007.89447021</td>\n",
       "        <td>110.286323547</td>\n",
       "        <td>62.2374191284</td>\n",
       "        <td>445.539459229</td>\n",
       "        <td>493.004302979</td>\n",
       "        <td>0.0</td>\n",
       "        <td>19541.8691406</td>\n",
       "        <td>256.957000732</td>\n",
       "        <td>841.005554199</td>\n",
       "        <td>641.211853027</td>\n",
       "        <td>76.0285491943</td>\n",
       "        <td>0.0135909151286</td>\n",
       "        <td>0.0521680526435</td>\n",
       "        <td>3.61477136612</td>\n",
       "        <td>5.98732089996</td>\n",
       "        <td>14.5784883499</td>\n",
       "        <td>0.0195789299905</td>\n",
       "        <td>1.81566786766</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1988</td>\n",
       "        <td>895.846618652</td>\n",
       "        <td>149.652359009</td>\n",
       "        <td>61.4932556152</td>\n",
       "        <td>450.495697021</td>\n",
       "        <td>493.116424561</td>\n",
       "        <td>0.0</td>\n",
       "        <td>17205.8496094</td>\n",
       "        <td>220.839523315</td>\n",
       "        <td>891.133300781</td>\n",
       "        <td>686.127441406</td>\n",
       "        <td>101.297874451</td>\n",
       "        <td>0.025784092024</td>\n",
       "        <td>0.0441541187465</td>\n",
       "        <td>3.24130725861</td>\n",
       "        <td>5.89249038696</td>\n",
       "        <td>13.9894580841</td>\n",
       "        <td>0.0306758396327</td>\n",
       "        <td>1.74153852463</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1989</td>\n",
       "        <td>930.10546875</td>\n",
       "        <td>111.371284485</td>\n",
       "        <td>57.6666145325</td>\n",
       "        <td>418.17276001</td>\n",
       "        <td>474.92477417</td>\n",
       "        <td>0.0</td>\n",
       "        <td>17915.3359375</td>\n",
       "        <td>208.58026123</td>\n",
       "        <td>805.931884766</td>\n",
       "        <td>609.695983887</td>\n",
       "        <td>74.7958450317</td>\n",
       "        <td>0.0163879580796</td>\n",
       "        <td>0.0473672002554</td>\n",
       "        <td>3.69201421738</td>\n",
       "        <td>5.31333684921</td>\n",
       "        <td>10.2292861938</td>\n",
       "        <td>0.0255201626569</td>\n",
       "        <td>1.2660586834</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1990</td>\n",
       "        <td>751.455383301</td>\n",
       "        <td>102.002082825</td>\n",
       "        <td>54.3105697632</td>\n",
       "        <td>409.75189209</td>\n",
       "        <td>435.158782959</td>\n",
       "        <td>0.0</td>\n",
       "        <td>16842.5175781</td>\n",
       "        <td>213.632339478</td>\n",
       "        <td>966.489074707</td>\n",
       "        <td>589.345031738</td>\n",
       "        <td>73.0428695679</td>\n",
       "        <td>0.00709386356175</td>\n",
       "        <td>0.0415425337851</td>\n",
       "        <td>3.07702064514</td>\n",
       "        <td>5.87268829346</td>\n",
       "        <td>9.76843261719</td>\n",
       "        <td>0.0252027902752</td>\n",
       "        <td>1.20551073551</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1981, 895.6051025390625, 130.3574676513672, 51.33439254760742, 406.0739440917969, 443.198486328125, 0.0, 18819.70703125, 236.98300170898438, 840.6874389648438, 609.706787109375, 91.08026123046875, 0.03293686360120773, 0.06417319178581238, 5.615890026092529, 7.339915752410889, 11.857464790344238, 0.008220621384680271, 1.461671233177185, 0.0),\n",
       " (1982, 884.670654296875, 92.21265411376953, 50.34769058227539, 367.41070556640625, 424.6783752441406, 0.0, 19425.8359375, 256.79083251953125, 872.07470703125, 530.0245971679688, 64.80978393554688, 0.00797836109995842, 0.04918050765991211, 4.239809036254883, 7.676506042480469, 10.873835563659668, 0.007697358727455139, 1.343782901763916, 0.0),\n",
       " (1983, 816.6605224609375, 129.16310119628906, 54.47308349609375, 445.33306884765625, 462.76324462890625, 0.0, 18476.1953125, 237.70555114746094, 797.04736328125, 652.8157958984375, 92.04792785644531, 0.13790887594223022, 0.048595353960990906, 3.882063388824463, 7.301627159118652, 12.202207565307617, 0.015354366973042488, 1.5196093320846558, 0.0),\n",
       " (1984, 867.5743408203125, 95.57946014404297, 46.948646545410156, 347.2448425292969, 408.5397033691406, 0.0, 17727.712890625, 223.08975219726562, 911.25634765625, 510.37933349609375, 63.0908203125, 0.01470834668725729, 0.0440557599067688, 5.141480922698975, 6.492110729217529, 10.941608428955078, 0.011575199663639069, 1.3742408752441406, 0.0),\n",
       " (1985, 637.7255249023438, 75.55760192871094, 45.336883544921875, 358.2953186035156, 361.4852294921875, 0.0, 15840.5400390625, 236.32135009765625, 990.8557739257812, 499.33990478515625, 49.63069152832031, 0.05470645800232887, 0.046555422246456146, 3.113034963607788, 6.522554874420166, 8.959033966064453, 0.010798747651278973, 1.1150814294815063, 0.0),\n",
       " (1986, 733.8412475585938, 65.63016510009766, 36.76259231567383, 241.9388427734375, 281.6683349609375, 0.0, 16059.4345703125, 243.0305633544922, 977.513427734375, 358.09368896484375, 48.7893180847168, 0.0010510582942515612, 0.040558818727731705, 2.8865158557891846, 6.505890369415283, 7.870068073272705, 0.013286528177559376, 0.970182478427887, 0.0),\n",
       " (1987, 1007.8944702148438, 110.28632354736328, 62.23741912841797, 445.5394592285156, 493.0043029785156, 0.0, 19541.869140625, 256.9570007324219, 841.0055541992188, 641.2118530273438, 76.02854919433594, 0.013590915128588676, 0.05216805264353752, 3.6147713661193848, 5.987320899963379, 14.57848834991455, 0.019578929990530014, 1.8156678676605225, 0.0),\n",
       " (1988, 895.8466186523438, 149.65235900878906, 61.493255615234375, 450.4956970214844, 493.1164245605469, 0.0, 17205.849609375, 220.8395233154297, 891.13330078125, 686.12744140625, 101.2978744506836, 0.025784092023968697, 0.04415411874651909, 3.241307258605957, 5.892490386962891, 13.989458084106445, 0.030675839632749557, 1.7415385246276855, 0.0),\n",
       " (1989, 930.10546875, 111.37128448486328, 57.6666145324707, 418.1727600097656, 474.9247741699219, 0.0, 17915.3359375, 208.58026123046875, 805.931884765625, 609.6959838867188, 74.79584503173828, 0.016387958079576492, 0.04736720025539398, 3.692014217376709, 5.3133368492126465, 10.229286193847656, 0.025520162656903267, 1.2660586833953857, 0.0),\n",
       " (1990, 751.4553833007812, 102.00208282470703, 54.310569763183594, 409.75189208984375, 435.1587829589844, 0.0, 16842.517578125, 213.63233947753906, 966.4890747070312, 589.3450317382812, 73.0428695678711, 0.007093863561749458, 0.04154253378510475, 3.0770206451416016, 5.872688293457031, 9.7684326171875, 0.025202790275216103, 1.2055107355117798, 0.0)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * From watershed_yearly LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "\n",
    "Some DBMSs offer non-standard syntax either instead of or in addition to SQL standard syntax. Variants of the simple ***limit*** query for different DBMSes can be found at https://en.wikipedia.org/wiki/Select_(SQL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}