Created
November 11, 2020 17:53
-
-
Save tolmachina/d8826c3d01f7c37e6abb5fc15d8f342e to your computer and use it in GitHub Desktop.
Revisions
-
tolmachina created this gist
Nov 11, 2020 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,1690 @@ { "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "<center>\n", " <img src=\"https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/Logos/organization_logo/organization_logo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\" />\n", "</center>\n", "\n", "# Accessing Databases with SQL Magic\n", "\n", "Estaimted time needed: **15** minutes\n", "\n", "## Objectives\n", "\n", "After complting this lab you will be able to:\n", "\n", "- perform simplified database access using SQL \"magic\"\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### To communicate with SQL Databases from within a JupyterLab notebook, we can use the SQL \"magic\" provided by the [ipython-sql](https://github.com/catherinedevlin/ipython-sql) extension. \"Magic\" is JupyterLab's term for special commands that start with \"%\". Below, we'll use the _load_\\__ext_ magic to load the ipython-sql extension. In the lab environemnt provided in the course the ipython-sql extension is already installed and so is the ibm_db_sa driver.\n" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Now we have access to SQL magic. With our first SQL magic command, we'll connect to a Db2 database. However, in order to do that, you'll first need to retrieve or create your credentials to access your Db2 database.\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "<a ><img src = \"https://ibm.box.com/shared/static/uy78gy1uq3uj6fkvd4muzy5zcr62tb72.png\" width = 1000, align = \"center\"></a>\n", "\n", " <h5 align=center> This image shows the location of your connection string if you're using Db2 on IBM Cloud. If you're using another host the format is: username:password@hostname:port/database-name\n", " </h5>\n" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'Connected: zsl80828@BLUDB'" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Enter your Db2 credentials in the connection string below\n", "# Recall you created Service Credentials in Part III of the first lab of the course in Week 1\n", "# i.e. from the uri field in the Service Credentials copy everything after db2:// (but remove the double quote at the end)\n", "# for example, if your credentials are as in the screenshot above, you would write:\n", "# %sql ibm_db_sa://my-username:[email protected]:50000/BLUDB\n", "# Note the ibm_db_sa:// prefix instead of db2://\n", "# This is because JupyterLab's ipython-sql extension uses sqlalchemy (a python SQL toolkit)\n", "# which in turn uses IBM's sqlalchemy dialect: ibm_db_sa\n", "%sql ibm_db_sa://zsl80828:x36l7%40g9k19g7140@dashdb-txn-sbox-yp-lon02-06.services.eu-gb.bluemix.net:50000/BLUDB" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "jupyter": { "source_hidden": true } }, "outputs": [], "source": [ "#Replace the placeholder values with your actual Db2 hostname, username, and password:\n", "dsn_hostname = \"dashdb-txn-sbox-yp-lon02-06.services.eu-gb.bluemix.net\" # e.g.: \"dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\"\n", "dsn_uid = \"zsl80828\" # e.g. \"abc12345\"\n", "dsn_pwd = \"x36l7@g9k19g7140\" # e.g. \"7dBZ3wWt9XN6$o0J\"\n", "\n", "dsn_driver = \"{IBM DB2 ODBC DRIVER}\"\n", "dsn_database = \"BLUDB\" # e.g. \"BLUDB\"\n", "dsn_port = \"50000\" # e.g. \"50000\" \n", "dsn_protocol = \"TCPIP\" # i.e. \"TCPIP\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### For convenience, we can use %%sql (two %'s instead of one) at the top of a cell to indicate we want the entire cell to be treated as SQL. Let's use this to create a table and fill it with some test data for experimenting.\n" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://zsl80828:***@dashdb-txn-sbox-yp-lon02-06.services.eu-gb.bluemix.net:50000/BLUDB\n", "(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0601N The name of the object to be created is identical to the existing name \"ZSL80828.INTERNATIONAL_STUDENT_TEST_SCORES\" of type \"TABLE\". SQLSTATE=42710 SQLCODE=-601\n", "[SQL: CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (\n", "\tcountry VARCHAR(50),\n", "\tfirst_name VARCHAR(50),\n", "\tlast_name VARCHAR(50),\n", "\ttest_score INT\n", ");]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (\n", "\tcountry VARCHAR(50),\n", "\tfirst_name VARCHAR(50),\n", "\tlast_name VARCHAR(50),\n", "\ttest_score INT\n", ");\n", "INSERT INTO INTERNATIONAL_STUDENT_TEST_SCORES (country, first_name, last_name, test_score)\n", "VALUES\n", "('United States', 'Marshall', 'Bernadot', 54),\n", "('Ghana', 'Celinda', 'Malkin', 51),\n", "('Ukraine', 'Guillermo', 'Furze', 53),\n", "('Greece', 'Aharon', 'Tunnow', 48),\n", "('Russia', 'Bail', 'Goodwin', 46),\n", "('Poland', 'Cole', 'Winteringham', 49),\n", "('Sweden', 'Emlyn', 'Erricker', 55),\n", "('Russia', 'Cathee', 'Sivewright', 49),\n", "('China', 'Barny', 'Ingerson', 57),\n", "('Uganda', 'Sharla', 'Papaccio', 55),\n", "('China', 'Stella', 'Youens', 51),\n", "('Poland', 'Julio', 'Buesden', 48),\n", "('United States', 'Tiffie', 'Cosely', 58),\n", "('Poland', 'Auroora', 'Stiffell', 45),\n", "('China', 'Clarita', 'Huet', 52),\n", "('Poland', 'Shannon', 'Goulden', 45),\n", "('Philippines', 'Emylee', 'Privost', 50),\n", "('France', 'Madelina', 'Burk', 49),\n", "('China', 'Saunderson', 'Root', 58),\n", "('Indonesia', 'Bo', 'Waring', 55),\n", "('China', 'Hollis', 'Domotor', 45),\n", "('Russia', 'Robbie', 'Collip', 46),\n", "('Philippines', 'Davon', 'Donisi', 46),\n", "('China', 'Cristabel', 'Radeliffe', 48),\n", "('China', 'Wallis', 'Bartleet', 58),\n", "('Moldova', 'Arleen', 'Stailey', 38),\n", "('Ireland', 'Mendel', 'Grumble', 58),\n", "('China', 'Sallyann', 'Exley', 51),\n", "('Mexico', 'Kain', 'Swaite', 46),\n", "('Indonesia', 'Alonso', 'Bulteel', 45),\n", "('Armenia', 'Anatol', 'Tankus', 51),\n", "('Indonesia', 'Coralyn', 'Dawkins', 48),\n", "('China', 'Deanne', 'Edwinson', 45),\n", "('China', 'Georgiana', 'Epple', 51),\n", "('Portugal', 'Bartlet', 'Breese', 56),\n", "('Azerbaijan', 'Idalina', 'Lukash', 50),\n", "('France', 'Livvie', 'Flory', 54),\n", "('Malaysia', 'Nonie', 'Borit', 48),\n", "('Indonesia', 'Clio', 'Mugg', 47),\n", "('Brazil', 'Westley', 'Measor', 48),\n", "('Philippines', 'Katrinka', 'Sibbert', 51),\n", "('Poland', 'Valentia', 'Mounch', 50),\n", "('Norway', 'Sheilah', 'Hedditch', 53),\n", "('Papua New Guinea', 'Itch', 'Jubb', 50),\n", "('Latvia', 'Stesha', 'Garnson', 53),\n", "('Canada', 'Cristionna', 'Wadmore', 46),\n", "('China', 'Lianna', 'Gatward', 43),\n", "('Guatemala', 'Tanney', 'Vials', 48),\n", "('France', 'Alma', 'Zavittieri', 44),\n", "('China', 'Alvira', 'Tamas', 50),\n", "('United States', 'Shanon', 'Peres', 45),\n", "('Sweden', 'Maisey', 'Lynas', 53),\n", "('Indonesia', 'Kip', 'Hothersall', 46),\n", "('China', 'Cash', 'Landis', 48),\n", "('Panama', 'Kennith', 'Digance', 45),\n", "('China', 'Ulberto', 'Riggeard', 48),\n", "('Switzerland', 'Judy', 'Gilligan', 49),\n", "('Philippines', 'Tod', 'Trevaskus', 52),\n", "('Brazil', 'Herold', 'Heggs', 44),\n", "('Latvia', 'Verney', 'Note', 50),\n", "('Poland', 'Temp', 'Ribey', 50),\n", "('China', 'Conroy', 'Egdal', 48),\n", "('Japan', 'Gabie', 'Alessandone', 47),\n", "('Ukraine', 'Devlen', 'Chaperlin', 54),\n", "('France', 'Babbette', 'Turner', 51),\n", "('Czech Republic', 'Virgil', 'Scotney', 52),\n", "('Tajikistan', 'Zorina', 'Bedow', 49),\n", "('China', 'Aidan', 'Rudeyeard', 50),\n", "('Ireland', 'Saunder', 'MacLice', 48),\n", "('France', 'Waly', 'Brunstan', 53),\n", "('China', 'Gisele', 'Enns', 52),\n", "('Peru', 'Mina', 'Winchester', 48),\n", "('Japan', 'Torie', 'MacShirrie', 50),\n", "('Russia', 'Benjamen', 'Kenford', 51),\n", "('China', 'Etan', 'Burn', 53),\n", "('Russia', 'Merralee', 'Chaperlin', 38),\n", "('Indonesia', 'Lanny', 'Malam', 49),\n", "('Canada', 'Wilhelm', 'Deeprose', 54),\n", "('Czech Republic', 'Lari', 'Hillhouse', 48),\n", "('China', 'Ossie', 'Woodley', 52),\n", "('Macedonia', 'April', 'Tyer', 50),\n", "('Vietnam', 'Madelon', 'Dansey', 53),\n", "('Ukraine', 'Korella', 'McNamee', 52),\n", "('Jamaica', 'Linnea', 'Cannam', 43),\n", "('China', 'Mart', 'Coling', 52),\n", "('Indonesia', 'Marna', 'Causbey', 47),\n", "('China', 'Berni', 'Daintier', 55),\n", "('Poland', 'Cynthia', 'Hassell', 49),\n", "('Canada', 'Carma', 'Schule', 49),\n", "('Indonesia', 'Malia', 'Blight', 48),\n", "('China', 'Paulo', 'Seivertsen', 47),\n", "('Niger', 'Kaylee', 'Hearley', 54),\n", "('Japan', 'Maure', 'Jandak', 46),\n", "('Argentina', 'Foss', 'Feavers', 45),\n", "('Venezuela', 'Ron', 'Leggitt', 60),\n", "('Russia', 'Flint', 'Gokes', 40),\n", "('China', 'Linet', 'Conelly', 52),\n", "('Philippines', 'Nikolas', 'Birtwell', 57),\n", "('Australia', 'Eduard', 'Leipelt', 53)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Using Python Variables in your SQL Statements\n", "\n", "##### You can use python variables in your SQL statements by adding a \":\" prefix to your python variable names.\n", "\n", "##### For example, if I have a python variable `country` with a value of `\"Canada\"`, I can use this variable in a SQL query to find all the rows of students from Canada.\n" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://zsl80828:***@dashdb-txn-sbox-yp-lon02-06.services.eu-gb.bluemix.net:50000/BLUDB\n", "Done.\n", " * ibm_db_sa://zsl80828:***@dashdb-txn-sbox-yp-lon02-06.services.eu-gb.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/html": [ "<table>\n", " <tr>\n", " <th>country</th>\n", " <th>first_name</th>\n", " <th>last_name</th>\n", " <th>test_score</th>\n", " </tr>\n", " <tr>\n", " <td>United States</td>\n", " <td>Marshall</td>\n", " <td>Bernadot</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>Ghana</td>\n", " <td>Celinda</td>\n", " <td>Malkin</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Ukraine</td>\n", " <td>Guillermo</td>\n", " <td>Furze</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Sweden</td>\n", " <td>Emlyn</td>\n", " <td>Erricker</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Barny</td>\n", " <td>Ingerson</td>\n", " <td>57</td>\n", " </tr>\n", " <tr>\n", " <td>Uganda</td>\n", " <td>Sharla</td>\n", " <td>Papaccio</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Stella</td>\n", " <td>Youens</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>United States</td>\n", " <td>Tiffie</td>\n", " <td>Cosely</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Clarita</td>\n", " <td>Huet</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Saunderson</td>\n", " <td>Root</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Bo</td>\n", " <td>Waring</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Wallis</td>\n", " <td>Bartleet</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>Ireland</td>\n", " <td>Mendel</td>\n", " <td>Grumble</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Sallyann</td>\n", " <td>Exley</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Armenia</td>\n", " <td>Anatol</td>\n", " <td>Tankus</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Georgiana</td>\n", " <td>Epple</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Portugal</td>\n", " <td>Bartlet</td>\n", " <td>Breese</td>\n", " <td>56</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Livvie</td>\n", " <td>Flory</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Katrinka</td>\n", " <td>Sibbert</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Norway</td>\n", " <td>Sheilah</td>\n", " <td>Hedditch</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Latvia</td>\n", " <td>Stesha</td>\n", " <td>Garnson</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Sweden</td>\n", " <td>Maisey</td>\n", " <td>Lynas</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Tod</td>\n", " <td>Trevaskus</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Ukraine</td>\n", " <td>Devlen</td>\n", " <td>Chaperlin</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Babbette</td>\n", " <td>Turner</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Czech Republic</td>\n", " <td>Virgil</td>\n", " <td>Scotney</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Waly</td>\n", " <td>Brunstan</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Gisele</td>\n", " <td>Enns</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Russia</td>\n", " <td>Benjamen</td>\n", " <td>Kenford</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Etan</td>\n", " <td>Burn</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Canada</td>\n", " <td>Wilhelm</td>\n", " <td>Deeprose</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Ossie</td>\n", " <td>Woodley</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Vietnam</td>\n", " <td>Madelon</td>\n", " <td>Dansey</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Ukraine</td>\n", " <td>Korella</td>\n", " <td>McNamee</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Mart</td>\n", " <td>Coling</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Berni</td>\n", " <td>Daintier</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>Niger</td>\n", " <td>Kaylee</td>\n", " <td>Hearley</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>Venezuela</td>\n", " <td>Ron</td>\n", " <td>Leggitt</td>\n", " <td>60</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Linet</td>\n", " <td>Conelly</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Nikolas</td>\n", " <td>Birtwell</td>\n", " <td>57</td>\n", " </tr>\n", " <tr>\n", " <td>Australia</td>\n", " <td>Eduard</td>\n", " <td>Leipelt</td>\n", " <td>53</td>\n", " </tr>\n", "</table>" ], "text/plain": [ "[('United States', 'Marshall', 'Bernadot', 54),\n", " ('Ghana', 'Celinda', 'Malkin', 51),\n", " ('Ukraine', 'Guillermo', 'Furze', 53),\n", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('China', 'Barny', 'Ingerson', 57),\n", " ('Uganda', 'Sharla', 'Papaccio', 55),\n", " ('China', 'Stella', 'Youens', 51),\n", " ('United States', 'Tiffie', 'Cosely', 58),\n", " ('China', 'Clarita', 'Huet', 52),\n", " ('China', 'Saunderson', 'Root', 58),\n", " ('Indonesia', 'Bo', 'Waring', 55),\n", " ('China', 'Wallis', 'Bartleet', 58),\n", " ('Ireland', 'Mendel', 'Grumble', 58),\n", " ('China', 'Sallyann', 'Exley', 51),\n", " ('Armenia', 'Anatol', 'Tankus', 51),\n", " ('China', 'Georgiana', 'Epple', 51),\n", " ('Portugal', 'Bartlet', 'Breese', 56),\n", " ('France', 'Livvie', 'Flory', 54),\n", " ('Philippines', 'Katrinka', 'Sibbert', 51),\n", " ('Norway', 'Sheilah', 'Hedditch', 53),\n", " ('Latvia', 'Stesha', 'Garnson', 53),\n", " ('Sweden', 'Maisey', 'Lynas', 53),\n", " ('Philippines', 'Tod', 'Trevaskus', 52),\n", " ('Ukraine', 'Devlen', 'Chaperlin', 54),\n", " ('France', 'Babbette', 'Turner', 51),\n", " ('Czech Republic', 'Virgil', 'Scotney', 52),\n", " ('France', 'Waly', 'Brunstan', 53),\n", " ('China', 'Gisele', 'Enns', 52),\n", " ('Russia', 'Benjamen', 'Kenford', 51),\n", " ('China', 'Etan', 'Burn', 53),\n", " ('Canada', 'Wilhelm', 'Deeprose', 54),\n", " ('China', 'Ossie', 'Woodley', 52),\n", " ('Vietnam', 'Madelon', 'Dansey', 53),\n", " ('Ukraine', 'Korella', 'McNamee', 52),\n", " ('China', 'Mart', 'Coling', 52),\n", " ('China', 'Berni', 'Daintier', 55),\n", " ('Niger', 'Kaylee', 'Hearley', 54),\n", " ('Venezuela', 'Ron', 'Leggitt', 60),\n", " ('China', 'Linet', 'Conelly', 52),\n", " ('Philippines', 'Nikolas', 'Birtwell', 57),\n", " ('Australia', 'Eduard', 'Leipelt', 53)]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "country = \"Canada\"\n", "%sql select * from INTERNATIONAL_STUDENT_TEST_SCORES where country = :country\n", " \n", "%sql select * from INTERNATIONAL_STUDENT_TEST_SCORES where test_score > 50" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Assigning the Results of Queries to Python Variables\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### You can use the normal python assignment syntax to assign the results of your queries to python variables.\n", "\n", "##### For example, I have a SQL query to retrieve the distribution of test scores (i.e. how many students got each score). I can assign the result of this query to the variable `test_score_distribution` using the `=` operator.\n" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://zsl80828:***@dashdb-txn-sbox-yp-lon02-06.services.eu-gb.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/plain": [ "<bound method ResultSet.plot of [(38, Decimal('2')), (40, Decimal('1')), (43, Decimal('2')), (44, Decimal('2')), (45, Decimal('8')), (46, Decimal('7')), (47, Decimal('4')), (48, Decimal('14')), (49, Decimal('8')), (50, Decimal('10')), (51, Decimal('8')), (52, Decimal('8')), (53, Decimal('8')), (54, Decimal('5')), (55, Decimal('4')), (56, Decimal('1')), (57, Decimal('2')), (58, Decimal('4')), (60, Decimal('1'))]>" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "test_score_distribution = %sql SELECT test_score as \"Test Score\", count(*) as \"Frequency\" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;\n", "test_score_distribution\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Converting Query Results to DataFrames\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### You can easily convert a SQL query result to a pandas dataframe using the `DataFrame()` method. Dataframe objects are much more versatile than SQL query result objects. For example, we can easily graph our test score distribution after converting to a dataframe.\n" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Help on function barplot in module seaborn.categorical:\n", "\n", "barplot(x=None, y=None, hue=None, data=None, order=None, hue_order=None, estimator=<function mean at 0x7f21182fbc80>, ci=95, n_boot=1000, units=None, orient=None, color=None, palette=None, saturation=0.75, errcolor='.26', errwidth=None, capsize=None, dodge=True, ax=None, **kwargs)\n", " Show point estimates and confidence intervals as rectangular bars.\n", " \n", " A bar plot represents an estimate of central tendency for a numeric\n", " variable with the height of each rectangle and provides some indication of\n", " the uncertainty around that estimate using error bars. Bar plots include 0\n", " in the quantitative axis range, and they are a good choice when 0 is a\n", " meaningful value for the quantitative variable, and you want to make\n", " comparisons against it.\n", " \n", " For datasets where 0 is not a meaningful value, a point plot will allow you\n", " to focus on differences between levels of one or more categorical\n", " variables.\n", " \n", " It is also important to keep in mind that a bar plot shows only the mean\n", " (or other estimator) value, but in many cases it may be more informative to\n", " show the distribution of values at each level of the categorical variables.\n", " In that case, other approaches such as a box or violin plot may be more\n", " appropriate.\n", " \n", " \n", " Input data can be passed in a variety of formats, including:\n", " \n", " - Vectors of data represented as lists, numpy arrays, or pandas Series\n", " objects passed directly to the ``x``, ``y``, and/or ``hue`` parameters.\n", " - A \"long-form\" DataFrame, in which case the ``x``, ``y``, and ``hue``\n", " variables will determine how the data are plotted.\n", " - A \"wide-form\" DataFrame, such that each numeric column will be plotted.\n", " - An array or list of vectors.\n", " \n", " In most cases, it is possible to use numpy or Python objects, but pandas\n", " objects are preferable because the associated names will be used to\n", " annotate the axes. Additionally, you can use Categorical types for the\n", " grouping variables to control the order of plot elements. \n", " \n", " This function always treats one of the variables as categorical and\n", " draws data at ordinal positions (0, 1, ... n) on the relevant axis, even\n", " when the data has a numeric or date type.\n", " \n", " See the :ref:`tutorial <categorical_tutorial>` for more information. \n", " \n", " Parameters\n", " ----------\n", " x, y, hue : names of variables in ``data`` or vector data, optional\n", " Inputs for plotting long-form data. See examples for interpretation. \n", " data : DataFrame, array, or list of arrays, optional\n", " Dataset for plotting. If ``x`` and ``y`` are absent, this is\n", " interpreted as wide-form. Otherwise it is expected to be long-form. \n", " order, hue_order : lists of strings, optional\n", " Order to plot the categorical levels in, otherwise the levels are\n", " inferred from the data objects. \n", " estimator : callable that maps vector -> scalar, optional\n", " Statistical function to estimate within each categorical bin.\n", " ci : float or \"sd\" or None, optional\n", " Size of confidence intervals to draw around estimated values. If\n", " \"sd\", skip bootstrapping and draw the standard deviation of the\n", " observations. If ``None``, no bootstrapping will be performed, and\n", " error bars will not be drawn.\n", " n_boot : int, optional\n", " Number of bootstrap iterations to use when computing confidence\n", " intervals.\n", " units : name of variable in ``data`` or vector data, optional\n", " Identifier of sampling units, which will be used to perform a\n", " multilevel bootstrap and account for repeated measures design. \n", " orient : \"v\" | \"h\", optional\n", " Orientation of the plot (vertical or horizontal). This is usually\n", " inferred from the dtype of the input variables, but can be used to\n", " specify when the \"categorical\" variable is a numeric or when plotting\n", " wide-form data. \n", " color : matplotlib color, optional\n", " Color for all of the elements, or seed for a gradient palette. \n", " palette : palette name, list, or dict, optional\n", " Colors to use for the different levels of the ``hue`` variable. Should\n", " be something that can be interpreted by :func:`color_palette`, or a\n", " dictionary mapping hue levels to matplotlib colors. \n", " saturation : float, optional\n", " Proportion of the original saturation to draw colors at. Large patches\n", " often look better with slightly desaturated colors, but set this to\n", " ``1`` if you want the plot colors to perfectly match the input color\n", " spec. \n", " errcolor : matplotlib color\n", " Color for the lines that represent the confidence interval.\n", " errwidth : float, optional\n", " Thickness of error bar lines (and caps). \n", " capsize : float, optional\n", " Width of the \"caps\" on error bars.\n", " \n", " dodge : bool, optional\n", " When hue nesting is used, whether elements should be shifted along the\n", " categorical axis. \n", " ax : matplotlib Axes, optional\n", " Axes object to draw the plot onto, otherwise uses the current Axes. \n", " kwargs : key, value mappings\n", " Other keyword arguments are passed through to ``plt.bar`` at draw\n", " time.\n", " \n", " Returns\n", " -------\n", " ax : matplotlib Axes\n", " Returns the Axes object with the plot drawn onto it. \n", " \n", " See Also\n", " --------\n", " countplot : Show the counts of observations in each categorical bin. \n", " pointplot : Show point estimates and confidence intervals using scatterplot\n", " glyphs. \n", " catplot : Combine a categorical plot with a class:`FacetGrid`. \n", " \n", " Examples\n", " --------\n", " \n", " Draw a set of vertical bar plots grouped by a categorical variable:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> import seaborn as sns\n", " >>> sns.set(style=\"whitegrid\")\n", " >>> tips = sns.load_dataset(\"tips\")\n", " >>> ax = sns.barplot(x=\"day\", y=\"total_bill\", data=tips)\n", " \n", " Draw a set of vertical bars with nested grouping by a two variables:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(x=\"day\", y=\"total_bill\", hue=\"sex\", data=tips)\n", " \n", " Draw a set of horizontal bars:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(x=\"tip\", y=\"day\", data=tips)\n", " \n", " Control bar order by passing an explicit order:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(x=\"time\", y=\"tip\", data=tips,\n", " ... order=[\"Dinner\", \"Lunch\"])\n", " \n", " Use median as the estimate of central tendency:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> from numpy import median\n", " >>> ax = sns.barplot(x=\"day\", y=\"tip\", data=tips, estimator=median)\n", " \n", " Show the standard error of the mean with the error bars:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(x=\"day\", y=\"tip\", data=tips, ci=68)\n", " \n", " Show standard deviation of observations instead of a confidence interval:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(x=\"day\", y=\"tip\", data=tips, ci=\"sd\")\n", " \n", " Add \"caps\" to the error bars:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(x=\"day\", y=\"tip\", data=tips, capsize=.2)\n", " \n", " Use a different color palette for the bars:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(\"size\", y=\"total_bill\", data=tips,\n", " ... palette=\"Blues_d\")\n", " \n", " Use ``hue`` without changing bar position or width:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> tips[\"weekend\"] = tips[\"day\"].isin([\"Sat\", \"Sun\"])\n", " >>> ax = sns.barplot(x=\"day\", y=\"total_bill\", hue=\"weekend\",\n", " ... data=tips, dodge=False)\n", " \n", " Plot all bars in a single color:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(\"size\", y=\"total_bill\", data=tips,\n", " ... color=\"salmon\", saturation=.5)\n", " \n", " Use ``plt.bar`` keyword arguments to further change the aesthetic:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> ax = sns.barplot(\"day\", \"total_bill\", data=tips,\n", " ... linewidth=2.5, facecolor=(1, 1, 1, 0),\n", " ... errcolor=\".2\", edgecolor=\".2\")\n", " \n", " Use :func:`catplot` to combine a :func:`barplot` and a :class:`FacetGrid`.\n", " This allows grouping within additional categorical variables. Using\n", " :func:`catplot` is safer than using :class:`FacetGrid` directly, as it\n", " ensures synchronization of variable order across facets:\n", " \n", " .. plot::\n", " :context: close-figs\n", " \n", " >>> g = sns.catplot(x=\"sex\", y=\"total_bill\",\n", " ... hue=\"smoker\", col=\"time\",\n", " ... data=tips, kind=\"bar\",\n", " ... height=4, aspect=.7);\n", "\n" ] }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAAWkElEQVR4nO3de9Rl95zn8fcnqbiEECZPLi0phUVpLNdi2hiEyEjQCU00C8skqB5aiJZF0E3oZUajO27TdJrEJa5JJEM0EkaiZzWJilQiF5qmEAkJaR23Vh2+88fexZPHeZ5nn1Nnn1OV/X6tddaz9z57f3/fc86vvrXPvvxOqgpJ0nDsMu8EJEmzZeGXpIGx8EvSwFj4JWlgLPySNDBr5p1AF3vttVetW7du3mlI0k7lwgsv/GFVLSxdvlMU/nXr1rFp06Z5pyFJO5Uk3x613EM9kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SB6a3wJzkpyTVJLh3x3LFJKslefbUvSRqtzz3+dwOHLF2Y5ADgYOA7PbYtSVpGb4W/qj4PXDfiqROAlwD+EIAkzcFM79xNchjwvaq6OMlq624ENgKsXbt2BtlpR3LomUdPtN0nH//WKWci3fTM7ORukt2BVwCv7LJ+VZ1YVRuqasPCwu8MNSFJmtAsr+q5C3An4OIkW4D9gS8n2XeGOUjS4M3sUE9VfQXYe9t8W/w3VNUPZ5WDJKnfyzk/CHwBWJ/kyiTP6qstSVJ3ve3xV9VTV3l+XV9tS5KW5527kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgamt8Kf5KQk1yS5dNGyNyT5apJLkpyRZM++2pckjdbnHv+7gUOWLDsHuFdV3Rv4Z+BlPbYvSRqht8JfVZ8Hrluy7OyquqGd/SKwf1/tS5JGm+cx/qOATy73ZJKNSTYl2XTttdfOMC1JummbS+FP8grgBuD9y61TVSdW1Yaq2rCwsDC75CTpJm7NrBtM8kzgccBBVVWzbl+Shm6mhT/JIcBLgYdX1c9n2bYkqdHn5ZwfBL4ArE9yZZJnAW8D9gDOSbI5yTv6al+SNFpve/xV9dQRi9/VV3uSpG68c1eSBsbCL0kDY+GXpIGx8EvSwFj4JWlgLPySNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGx8EvSwFj4JWlgLPySNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGx8EvSwFj4JWlgeiv8SU5Kck2SSxctu32Sc5J8vf17u77alySN1uce/7uBQ5YsOw74bFXdFfhsOy9JmqHeCn9VfR64bsniw4H3tNPvAR7fV/uSpNHWzLi9farqaoCqujrJ3sutmGQjsBFg7dq1M0pP+l2PO/2kibY764lHTTkTaTp22JO7VXViVW2oqg0LCwvzTkeSbjJmXfh/kGQ/gPbvNTNuX5IGb9aF/2PAM9vpZwL/Z8btS9Lg9Xk55weBLwDrk1yZ5FnA64CDk3wdOLidlyTNUG8nd6vqqcs8dVBfbUqSVrfDntyVJPXDwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA9Op8Ce5V9+JSJJmo+se/zuSXJDkeUn27DMhSVK/OhX+qvqvwNOAA4BNST6Q5OBeM5Mk9aLzMf6q+jrw58BLgYcDb0ny1SR/1FdykqTp63qM/95JTgCuAB4J/GFV/X47fUKP+UmSpqzrePxvA/4eeHlV/WLbwqq6Ksmf95KZJKkXXQv/Y4BfVNWvAJLsAtyiqn5eVe/rLTtJ0tR1Pcb/GeCWi+Z3b5dJknYyXQv/Larqp9tm2und+0lJktSnroX/Z0nuv20myQOAX6ywviRpB9X1GP8xwKlJrmrn9wP+eNJGk7wIeDZQwFeAI6vq3yeNJ0nqrlPhr6ovJbk7sB4I8NWq+o9JGkxyB+AFwD2q6hdJPgI8BXj3JPEkSePpuscP8EBgXbvN/ZJQVe/djnZvmeQ/aM4VXLXK+pKkKelU+JO8D7gLsBn4Vbu4gLELf1V9L8kbge/QnCc4u6rOHtHmRmAjwNq1a8dtZlA+dPKjJ9ruKUd+esqZ7Hge+9G/nmi7T/zRi6ecCTzu1NMm2u6sI550o/nHnzbZBXVnPulRv5k+4vRLJopx6hPvPdF22rF03ePfQHNopra3wSS3Aw4H7gT8mObcwdOr6pTF61XVicCJABs2bNjudiVJja5X9VwK7DulNh8FfKuqrm3PE3wU+C9Tii1JWkXXPf69gMuTXAD8ctvCqjpsgja/A/xBkt1pDvUcBGyaII4kaQJdC//x02qwqs5PchrwZeAG4CLaQzqSpP51vZzzvCR3BO5aVZ9p99Z3nbTRqnoV8KpJt5ckTa7rsMzPAU4D/q5ddAfgzJ5ykiT1qOvJ3T8FHgJcD7/5UZa9+0pKktSfroX/l1W1ddtMkjU01/FLknYyXQv/eUleTnO37cHAqcDH+0tLktSXroX/OOBamgHV/gT4B5rf35Uk7WS6XtXza5qfXvz7ftORJPWt61g932LEMf2quvPUM5Ik9WqcsXq2uQVwBHD76acjSepbp2P8VfWjRY/vVdWbgEf2m5okqQ9dD/Xcf9HsLjTfAPboJSPNzVvfP9nwzkc/7aY/vLN0U9L1UM/iQc1vALYAT556NpKk3nW9qucRfSciSZqNrod6/myl56vqb6aTjiSpb+Nc1fNA4GPt/B8Cnwe+20dSkqT+jPNDLPevqp8AJDkeOLWqnt1XYpKkfnQdsmEtsHXR/FZg3dSzkST1ruse//uAC5KcQXMH7xOA9/aWlSSpN12v6nltkk8CD20XHVlVF/WXliSpL10P9QDsDlxfVW8Grkxyp55ykiT1qOtPL74KeCnwsnbRbsApfSUlSepP1z3+JwCHAT8DqKqrcMgGSdopdS38W6uqaIdmTnKr7Wk0yZ5JTkvy1SRXJHnw9sSTJHXXtfB/JMnfAXsmeQ7wGbbvR1neDHyqqu4O3Ae4YjtiSZLGsOpVPUkCfBi4O3A9sB54ZVWdM0mDSW4DPAz47wDtj7hvXWkbSdL0rFr4q6qSnFlVDwAmKvZL3Jnm93tPTnIf4ELghVX1s8UrJdkIbARYu3btFJqVJEH3Qz1fTPLAKbW5Brg/8Paquh/NCePjlq5UVSdW1Yaq2rCwsDClpiVJXQv/I2iK/78kuSTJV5JcMmGbVwJXVtX57fxpNP8RSJJmYMVDPUnWVtV3gEOn1WBVfT/Jd5Osr6qvAQcBl08rviRpZasd4z+TZlTObyc5vaqeOKV2jwben+RmwDeBI6cUV5K0itUKfxZN33lajVbVZpox/iVJM7baMf5aZlqStJNabY//Pkmup9nzv2U7TTtfVXWbXrOTJE3dioW/qnadVSKSpNkYZ1hmSdJNgIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYFYdllmSlvPaM66eaLtXPGG/KWeicbjHL0kDY+GXpIGx8EvSwFj4JWlgLPySNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGZW+FPsmuSi5KcNa8cJGmI5rnH/0Lgijm2L0mDNJfCn2R/4LHAO+fRviQN2bz2+N8EvAT49Zzal6TBmvmwzEkeB1xTVRcmOXCF9TYCGwHWrl07m+S03Y497ZCJt33jkz41xUy0szjz1B9OvO3jj9hriplMzw9O2DzRdvu86L5TzWM589jjfwhwWJItwIeARyY5ZelKVXViVW2oqg0LCwuzzlGSbrJmXvir6mVVtX9VrQOeAvzfqnr6rPOQpKHyOn5JGpi5/vRiVZ0LnDvPHCRpaNzjl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGpiZF/4kByT5XJIrklyW5IWzzkGShmzNHNq8AXhxVX05yR7AhUnOqarL55CLJA3OzPf4q+rqqvpyO/0T4ArgDrPOQ5KGah57/L+RZB1wP+D8Ec9tBDYCrF27FoBr337KRO0sPPfpk6a4rMv+9rCJtrvn8z52o/nPvvOxE8U56NmfmGg7SSu78o3fn2i7/Y/dd8qZ9GduJ3eT3Bo4HTimqq5f+nxVnVhVG6pqw8LCwuwTlKSbqLkU/iS70RT991fVR+eRgyQN1Tyu6gnwLuCKqvqbWbcvSUM3jz3+hwDPAB6ZZHP7eMwc8pCkQZr5yd2q+n9AZt2uJKnhnbuSNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGx8EvSwFj4JWlgLPySNDBzHZZ5Hr7/9ldPtN2+z33VlDORNE0XnHzNRNs96Mi9p5zJ9Fzz1nMm2m7vow9e8Xn3+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGpi5FP4khyT5WpJvJDluHjlI0lDNvPAn2RX438ChwD2Apya5x6zzkKShmsce/4OAb1TVN6tqK/Ah4PA55CFJg5Sqmm2DyZOAQ6rq2e38M4D/XFXPX7LeRmBjO7se+NoqofcCfrid6U0jxo4Wx1z6jbMj5TKtOObSb5xZ5nLHqlpYunAe4/FnxLLf+d+nqk4ETuwcNNlUVRu2K7EpxNjR4phLv3F2pFymFcdc+o2zI+Qyj0M9VwIHLJrfH7hqDnlI0iDNo/B/CbhrkjsluRnwFOBjc8hDkgZp5od6quqGJM8HPg3sCpxUVZdNIXTnw0I9x9jR4phLv3F2pFymFcdc+o0z91xmfnJXkjRf3rkrSQNj4ZekgdnpCn+SWyS5IMnFSS5L8up2+X2TfDHJ5iSbkjyoQ6xdk1yU5Kx2/vZJzkny9fbv7TrmtDTOXya5pM3l7CS/N0mcRcuPTVJJ9pogl+OTfK/NZXOSx0yaS5Kj26E2Lkvy+kniJPnwoly2JNk8QYyxP+tl4twnyReSfCXJx5PcpkOMLe36m5NsapeN1W+WiXFE+77+OkmnS/SWifOGJF9t+98ZSfacMM5YfXhUjEXPjdN/R+Uydh9eLp9x+vAyuUzSf0fFGasPJ9kzyWntZ3tFkgeP2+9upKp2qgfNfQC3bqd3A84H/gA4Gzi0Xf4Y4NwOsf4M+ABwVjv/euC4dvo44K865rQ0zm0WPfcC4B2TxGmXHUBzIvzbwF4T5HI8cOwE7/PSOI8APgPcvJ3fe9LXtOi5vwZeOUEuY3/Wy8T5EvDwdvoo4C87xNiy9HMYt98sE+P3aW5UPBfY0PH1jIrz34A17fRfdenDy8QZqw+PijFh/x2Vy9h9eJk4Y/Xh5V7TBP13VC5j9WHgPcCz2+mbAXuO2+8WP3a6Pf5q/LSd3a19VPvYtsd2W1a5NyDJ/sBjgXcuWnw4zRtM+/fxq+UzKk5VXb9olVsx4ga1jvkAnAC8ZDtjjGWZOM8FXldVvwSoqmu2J58kAZ4MfHCCGGN91ivEWQ98vp0+B3jianGWMXa/Waqqrqiq1e5O7xLn7Kq6oZ39Is19MpPEGbsPL6Nz/52Bsfvwcrr23xV07sPtN9GHAe8CqKqtVfVjtqPf7XSFH37zlX0zcA1wTlWdDxwDvCHJd4E3Ai9bJcybaDrkrxct26eqrgZo/+7dIZ1RcUjy2jaXpwGvnCROksOA71XVxR22XzYX4Pnt1/aTOn4dHBXnbsBDk5yf5LwkD9yOfAAeCvygqr4+QYxjGO+zXi7OpcBh7fQR3PjGwuUUcHaSC9MMKwLj95tRMSaxWpyjgE9OGmfMPvw7MSbov8vmwvh9eFSccfvwSu9v1/67XJxj6N6H7wxcC5yc5lDlO5PcisnqVZvRGF+fdrQHzdedzwH3At4CPLFd/mTgMyts9zjgb9vpA/ntV/8fL1nvX1dpf2ScJeu8DHj1uHGA3WkOY922un3tXO417UNzv8QuwGtp7psY+zXRFMm30BxqexDwLdrLgSd5b4C3Ay+eMJfOn/Uqce5O85X7QuBVwI869Lnfa//uDVxMsyc2br/5nRiLnjuX7od6VorzCuCMlT6jLnHG6MOj3pfO/XeVOGP14RXijNuHV3p/V+2/q+QyTr3aANxAM6YZwJuBvxy3391o3a4r7qiP9h/sscC/bfsQ2w/2+hW2+V80Q0dsAb4P/Bw4hWYguP3adfYDvrZK2yPjLFnnjsClE8Q5neYbzZb2cQPwHWDf7chl3YS5nAJ8Cjhw0Xr/AixM8t7Q3Dj4A2D/CXPp/FmP8d7cDbhgzL53fNv3xuo3o2Ismj+XjoV/uTjAM4EvALtvT5xx+vCIGH8xTv8dI5dV+/AKn9NYfXiF97dT/10ll3Hq1b7AlkXzDwU+sV39btzE5/0AFoA92+lbAv9Is0d3xbYPFTgIuLBjvAP57R7gG7jxyZLXj5HX4jh3XbT8aOC0SeIsWb6FDntMI3LZb9HyFwEfmvA1/Q/gNe303YDv0mFvctRrAg4Bzhvzc1+cy0Sf9Yg4e7d/dwHeCxy1yra3AvZYNP1P7Wvp3G+Wi7Ho+XPpUPhXyOUQ4HK6F7Tl4nTuw6u9pq79d4VcxurDK8Tp3IdXek3j9N8VchmrD9PUufXt9PFtn5u4Xs1jdM7ttR/wnjQ/6LIL8JGqOivJj4E3J1kD/Du/HdJ5HK8DPpLkWTR7J0dMmOPrkqynOZ78bZoONy+vT3JfmuOMW4A/mTDOScBJSS4FtgLPrLbHTeApTH5SDOA5bP9nDc2PAP1pO/1R4ORV1t8HOKM5r8ca4ANV9akkX6J7v1kuxhOAt9Ls2HwiyeaqevQEcb4B3Bw4p33ui1W1Uv9bLs7pY/ThkTFWWH/cXN43Zh9eLs7N6N6HV3pN4/Tf5XL5KeP14aOB97ev4ZvAkbT1b5J65ZANkjQwO+VVPZKkyVn4JWlgLPySNDAWfkkaGAu/JA3Mzng5p9RJkv8EfLad3Rf4Fc2t7wAPqqqtq2x/ILC1qv5pxHP70IydcgDNeFFbqqrTyKfSvFn4dZNVVT8C7gvN0L7AT6vqjWOEOBD4Kc1NN0u9hmacqDe38e+9Pbm2MdbUbwdYk3rjoR4NSpIHtAN0XZjk00n2a5e/IMnl7UBgH0qyjuampRe1Y6Y/dEmo/WiGgQCgqi5Z1MZL0oy/fnGS17XLto2/vm2c/Nu1y89N8j+TnAe8cLn8pGnyBi4NQrvH/zPgCcDhVXVtkj8GHl1VRyW5CrhTVf0yyZ5V9eOVviUkeTTwYeAimjHeT66qq5IcSjNOzaOq6udJbl9V1yW5BDi6qs5L8hqa8e6PSXIucHlVPS/JbsB5o/Lr+e3RwHioR0Nyc5qRXLcNZ7ArcHX73CU0t8SfCZy5WqCq+nSSO9OMu3IocFGSewGPovlP4OftetcluS3N+FLntZu/Bzh1UbgPt3/Xr5CfNDUWfg1JgMuq6sEjnnsszXC5hwF/keSeqwWrqutoftHrA2l+zvFhbRvjfo3+WYf8pKnxGL+G5JfAQpIHAyTZLck9k+wCHFBVn6P5sZY9gVsDPwH2GBUoySOT7N5O7wHchWagrLOBoxY9d/uq+jfgXxedJ3gGzSGdpb42Kr8pvG7pRtzj15D8GngS8Jb28Msaml/m+mfglHZZgBPaY/wfB05LcjjN8fl/XBTrAcDbktxAswP1zqr6EjQncoFNSbYC/wC8nGaM/He0/yFsG13xRqpqa5JR+V023bdBQ+fJXUkaGA/1SNLAWPglaWAs/JI0MBZ+SRoYC78kDYyFX5IGxsIvSQPz/wEORZQ7KhIhqwAAAABJRU5ErkJggg==\n", "text/plain": [ "<Figure size 432x288 with 1 Axes>" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "dataframe = test_score_distribution.DataFrame()\n", "\n", "%matplotlib inline\n", "# uncomment the following line if you get an module error saying seaborn not found\n", "# !pip install seaborn\n", "import seaborn\n", "\n", "plot = seaborn.barplot(x='Test Score',y='Frequency', data=dataframe)\n", "help(seaborn.barplot)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now you know how to work with Db2 from within JupyterLab notebooks using SQL \"magic\"!\n" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://zsl80828:***@dashdb-txn-sbox-yp-lon02-06.services.eu-gb.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/html": [ "<table>\n", " <tr>\n", " <th>country</th>\n", " <th>first_name</th>\n", " <th>last_name</th>\n", " <th>test_score</th>\n", " </tr>\n", " <tr>\n", " <td>United States</td>\n", " <td>Marshall</td>\n", " <td>Bernadot</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>Ghana</td>\n", " <td>Celinda</td>\n", " <td>Malkin</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Ukraine</td>\n", " <td>Guillermo</td>\n", " <td>Furze</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Greece</td>\n", " <td>Aharon</td>\n", " <td>Tunnow</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>Russia</td>\n", " <td>Bail</td>\n", " <td>Goodwin</td>\n", " <td>46</td>\n", " </tr>\n", " <tr>\n", " <td>Poland</td>\n", " <td>Cole</td>\n", " <td>Winteringham</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>Sweden</td>\n", " <td>Emlyn</td>\n", " <td>Erricker</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>Russia</td>\n", " <td>Cathee</td>\n", " <td>Sivewright</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Barny</td>\n", " <td>Ingerson</td>\n", " <td>57</td>\n", " </tr>\n", " <tr>\n", " <td>Uganda</td>\n", " <td>Sharla</td>\n", " <td>Papaccio</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Stella</td>\n", " <td>Youens</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Poland</td>\n", " <td>Julio</td>\n", " <td>Buesden</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>United States</td>\n", " <td>Tiffie</td>\n", " <td>Cosely</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>Poland</td>\n", " <td>Auroora</td>\n", " <td>Stiffell</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Clarita</td>\n", " <td>Huet</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Poland</td>\n", " <td>Shannon</td>\n", " <td>Goulden</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Emylee</td>\n", " <td>Privost</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Madelina</td>\n", " <td>Burk</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Saunderson</td>\n", " <td>Root</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Bo</td>\n", " <td>Waring</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Hollis</td>\n", " <td>Domotor</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>Russia</td>\n", " <td>Robbie</td>\n", " <td>Collip</td>\n", " <td>46</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Davon</td>\n", " <td>Donisi</td>\n", " <td>46</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Cristabel</td>\n", " <td>Radeliffe</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Wallis</td>\n", " <td>Bartleet</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>Moldova</td>\n", " <td>Arleen</td>\n", " <td>Stailey</td>\n", " <td>38</td>\n", " </tr>\n", " <tr>\n", " <td>Ireland</td>\n", " <td>Mendel</td>\n", " <td>Grumble</td>\n", " <td>58</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Sallyann</td>\n", " <td>Exley</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Mexico</td>\n", " <td>Kain</td>\n", " <td>Swaite</td>\n", " <td>46</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Alonso</td>\n", " <td>Bulteel</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>Armenia</td>\n", " <td>Anatol</td>\n", " <td>Tankus</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Coralyn</td>\n", " <td>Dawkins</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Deanne</td>\n", " <td>Edwinson</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Georgiana</td>\n", " <td>Epple</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Portugal</td>\n", " <td>Bartlet</td>\n", " <td>Breese</td>\n", " <td>56</td>\n", " </tr>\n", " <tr>\n", " <td>Azerbaijan</td>\n", " <td>Idalina</td>\n", " <td>Lukash</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Livvie</td>\n", " <td>Flory</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>Malaysia</td>\n", " <td>Nonie</td>\n", " <td>Borit</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Clio</td>\n", " <td>Mugg</td>\n", " <td>47</td>\n", " </tr>\n", " <tr>\n", " <td>Brazil</td>\n", " <td>Westley</td>\n", " <td>Measor</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Katrinka</td>\n", " <td>Sibbert</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Poland</td>\n", " <td>Valentia</td>\n", " <td>Mounch</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>Norway</td>\n", " <td>Sheilah</td>\n", " <td>Hedditch</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Papua New Guinea</td>\n", " <td>Itch</td>\n", " <td>Jubb</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>Latvia</td>\n", " <td>Stesha</td>\n", " <td>Garnson</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Canada</td>\n", " <td>Cristionna</td>\n", " <td>Wadmore</td>\n", " <td>46</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Lianna</td>\n", " <td>Gatward</td>\n", " <td>43</td>\n", " </tr>\n", " <tr>\n", " <td>Guatemala</td>\n", " <td>Tanney</td>\n", " <td>Vials</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Alma</td>\n", " <td>Zavittieri</td>\n", " <td>44</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Alvira</td>\n", " <td>Tamas</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>United States</td>\n", " <td>Shanon</td>\n", " <td>Peres</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>Sweden</td>\n", " <td>Maisey</td>\n", " <td>Lynas</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Kip</td>\n", " <td>Hothersall</td>\n", " <td>46</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Cash</td>\n", " <td>Landis</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>Panama</td>\n", " <td>Kennith</td>\n", " <td>Digance</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Ulberto</td>\n", " <td>Riggeard</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>Switzerland</td>\n", " <td>Judy</td>\n", " <td>Gilligan</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Tod</td>\n", " <td>Trevaskus</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Brazil</td>\n", " <td>Herold</td>\n", " <td>Heggs</td>\n", " <td>44</td>\n", " </tr>\n", " <tr>\n", " <td>Latvia</td>\n", " <td>Verney</td>\n", " <td>Note</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>Poland</td>\n", " <td>Temp</td>\n", " <td>Ribey</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Conroy</td>\n", " <td>Egdal</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>Japan</td>\n", " <td>Gabie</td>\n", " <td>Alessandone</td>\n", " <td>47</td>\n", " </tr>\n", " <tr>\n", " <td>Ukraine</td>\n", " <td>Devlen</td>\n", " <td>Chaperlin</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Babbette</td>\n", " <td>Turner</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>Czech Republic</td>\n", " <td>Virgil</td>\n", " <td>Scotney</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Tajikistan</td>\n", " <td>Zorina</td>\n", " <td>Bedow</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Aidan</td>\n", " <td>Rudeyeard</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>Ireland</td>\n", " <td>Saunder</td>\n", " <td>MacLice</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>France</td>\n", " <td>Waly</td>\n", " <td>Brunstan</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Gisele</td>\n", " <td>Enns</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Peru</td>\n", " <td>Mina</td>\n", " <td>Winchester</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>Japan</td>\n", " <td>Torie</td>\n", " <td>MacShirrie</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>Russia</td>\n", " <td>Benjamen</td>\n", " <td>Kenford</td>\n", " <td>51</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Etan</td>\n", " <td>Burn</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Russia</td>\n", " <td>Merralee</td>\n", " <td>Chaperlin</td>\n", " <td>38</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Lanny</td>\n", " <td>Malam</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>Canada</td>\n", " <td>Wilhelm</td>\n", " <td>Deeprose</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>Czech Republic</td>\n", " <td>Lari</td>\n", " <td>Hillhouse</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Ossie</td>\n", " <td>Woodley</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Macedonia</td>\n", " <td>April</td>\n", " <td>Tyer</td>\n", " <td>50</td>\n", " </tr>\n", " <tr>\n", " <td>Vietnam</td>\n", " <td>Madelon</td>\n", " <td>Dansey</td>\n", " <td>53</td>\n", " </tr>\n", " <tr>\n", " <td>Ukraine</td>\n", " <td>Korella</td>\n", " <td>McNamee</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Jamaica</td>\n", " <td>Linnea</td>\n", " <td>Cannam</td>\n", " <td>43</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Mart</td>\n", " <td>Coling</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Marna</td>\n", " <td>Causbey</td>\n", " <td>47</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Berni</td>\n", " <td>Daintier</td>\n", " <td>55</td>\n", " </tr>\n", " <tr>\n", " <td>Poland</td>\n", " <td>Cynthia</td>\n", " <td>Hassell</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>Canada</td>\n", " <td>Carma</td>\n", " <td>Schule</td>\n", " <td>49</td>\n", " </tr>\n", " <tr>\n", " <td>Indonesia</td>\n", " <td>Malia</td>\n", " <td>Blight</td>\n", " <td>48</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Paulo</td>\n", " <td>Seivertsen</td>\n", " <td>47</td>\n", " </tr>\n", " <tr>\n", " <td>Niger</td>\n", " <td>Kaylee</td>\n", " <td>Hearley</td>\n", " <td>54</td>\n", " </tr>\n", " <tr>\n", " <td>Japan</td>\n", " <td>Maure</td>\n", " <td>Jandak</td>\n", " <td>46</td>\n", " </tr>\n", " <tr>\n", " <td>Argentina</td>\n", " <td>Foss</td>\n", " <td>Feavers</td>\n", " <td>45</td>\n", " </tr>\n", " <tr>\n", " <td>Venezuela</td>\n", " <td>Ron</td>\n", " <td>Leggitt</td>\n", " <td>60</td>\n", " </tr>\n", " <tr>\n", " <td>Russia</td>\n", " <td>Flint</td>\n", " <td>Gokes</td>\n", " <td>40</td>\n", " </tr>\n", " <tr>\n", " <td>China</td>\n", " <td>Linet</td>\n", " <td>Conelly</td>\n", " <td>52</td>\n", " </tr>\n", " <tr>\n", " <td>Philippines</td>\n", " <td>Nikolas</td>\n", " <td>Birtwell</td>\n", " <td>57</td>\n", " </tr>\n", " <tr>\n", " <td>Australia</td>\n", " <td>Eduard</td>\n", " <td>Leipelt</td>\n", " <td>53</td>\n", " </tr>\n", "</table>" ], "text/plain": [ "[('United States', 'Marshall', 'Bernadot', 54),\n", " ('Ghana', 'Celinda', 'Malkin', 51),\n", " ('Ukraine', 'Guillermo', 'Furze', 53),\n", " ('Greece', 'Aharon', 'Tunnow', 48),\n", " ('Russia', 'Bail', 'Goodwin', 46),\n", " ('Poland', 'Cole', 'Winteringham', 49),\n", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Russia', 'Cathee', 'Sivewright', 49),\n", " ('China', 'Barny', 'Ingerson', 57),\n", " ('Uganda', 'Sharla', 'Papaccio', 55),\n", " ('China', 'Stella', 'Youens', 51),\n", " ('Poland', 'Julio', 'Buesden', 48),\n", " ('United States', 'Tiffie', 'Cosely', 58),\n", " ('Poland', 'Auroora', 'Stiffell', 45),\n", " ('China', 'Clarita', 'Huet', 52),\n", " ('Poland', 'Shannon', 'Goulden', 45),\n", " ('Philippines', 'Emylee', 'Privost', 50),\n", " ('France', 'Madelina', 'Burk', 49),\n", " ('China', 'Saunderson', 'Root', 58),\n", " ('Indonesia', 'Bo', 'Waring', 55),\n", " ('China', 'Hollis', 'Domotor', 45),\n", " ('Russia', 'Robbie', 'Collip', 46),\n", " ('Philippines', 'Davon', 'Donisi', 46),\n", " ('China', 'Cristabel', 'Radeliffe', 48),\n", " ('China', 'Wallis', 'Bartleet', 58),\n", " ('Moldova', 'Arleen', 'Stailey', 38),\n", " ('Ireland', 'Mendel', 'Grumble', 58),\n", " ('China', 'Sallyann', 'Exley', 51),\n", " ('Mexico', 'Kain', 'Swaite', 46),\n", " ('Indonesia', 'Alonso', 'Bulteel', 45),\n", " ('Armenia', 'Anatol', 'Tankus', 51),\n", " ('Indonesia', 'Coralyn', 'Dawkins', 48),\n", " ('China', 'Deanne', 'Edwinson', 45),\n", " ('China', 'Georgiana', 'Epple', 51),\n", " ('Portugal', 'Bartlet', 'Breese', 56),\n", " ('Azerbaijan', 'Idalina', 'Lukash', 50),\n", " ('France', 'Livvie', 'Flory', 54),\n", " ('Malaysia', 'Nonie', 'Borit', 48),\n", " ('Indonesia', 'Clio', 'Mugg', 47),\n", " ('Brazil', 'Westley', 'Measor', 48),\n", " ('Philippines', 'Katrinka', 'Sibbert', 51),\n", " ('Poland', 'Valentia', 'Mounch', 50),\n", " ('Norway', 'Sheilah', 'Hedditch', 53),\n", " ('Papua New Guinea', 'Itch', 'Jubb', 50),\n", " ('Latvia', 'Stesha', 'Garnson', 53),\n", " ('Canada', 'Cristionna', 'Wadmore', 46),\n", " ('China', 'Lianna', 'Gatward', 43),\n", " ('Guatemala', 'Tanney', 'Vials', 48),\n", " ('France', 'Alma', 'Zavittieri', 44),\n", " ('China', 'Alvira', 'Tamas', 50),\n", " ('United States', 'Shanon', 'Peres', 45),\n", " ('Sweden', 'Maisey', 'Lynas', 53),\n", " ('Indonesia', 'Kip', 'Hothersall', 46),\n", " ('China', 'Cash', 'Landis', 48),\n", " ('Panama', 'Kennith', 'Digance', 45),\n", " ('China', 'Ulberto', 'Riggeard', 48),\n", " ('Switzerland', 'Judy', 'Gilligan', 49),\n", " ('Philippines', 'Tod', 'Trevaskus', 52),\n", " ('Brazil', 'Herold', 'Heggs', 44),\n", " ('Latvia', 'Verney', 'Note', 50),\n", " ('Poland', 'Temp', 'Ribey', 50),\n", " ('China', 'Conroy', 'Egdal', 48),\n", " ('Japan', 'Gabie', 'Alessandone', 47),\n", " ('Ukraine', 'Devlen', 'Chaperlin', 54),\n", " ('France', 'Babbette', 'Turner', 51),\n", " ('Czech Republic', 'Virgil', 'Scotney', 52),\n", " ('Tajikistan', 'Zorina', 'Bedow', 49),\n", " ('China', 'Aidan', 'Rudeyeard', 50),\n", " ('Ireland', 'Saunder', 'MacLice', 48),\n", " ('France', 'Waly', 'Brunstan', 53),\n", " ('China', 'Gisele', 'Enns', 52),\n", " ('Peru', 'Mina', 'Winchester', 48),\n", " ('Japan', 'Torie', 'MacShirrie', 50),\n", " ('Russia', 'Benjamen', 'Kenford', 51),\n", " ('China', 'Etan', 'Burn', 53),\n", " ('Russia', 'Merralee', 'Chaperlin', 38),\n", " ('Indonesia', 'Lanny', 'Malam', 49),\n", " ('Canada', 'Wilhelm', 'Deeprose', 54),\n", " ('Czech Republic', 'Lari', 'Hillhouse', 48),\n", " ('China', 'Ossie', 'Woodley', 52),\n", " ('Macedonia', 'April', 'Tyer', 50),\n", " ('Vietnam', 'Madelon', 'Dansey', 53),\n", " ('Ukraine', 'Korella', 'McNamee', 52),\n", " ('Jamaica', 'Linnea', 'Cannam', 43),\n", " ('China', 'Mart', 'Coling', 52),\n", " ('Indonesia', 'Marna', 'Causbey', 47),\n", " ('China', 'Berni', 'Daintier', 55),\n", " ('Poland', 'Cynthia', 'Hassell', 49),\n", " ('Canada', 'Carma', 'Schule', 49),\n", " ('Indonesia', 'Malia', 'Blight', 48),\n", " ('China', 'Paulo', 'Seivertsen', 47),\n", " ('Niger', 'Kaylee', 'Hearley', 54),\n", " ('Japan', 'Maure', 'Jandak', 46),\n", " ('Argentina', 'Foss', 'Feavers', 45),\n", " ('Venezuela', 'Ron', 'Leggitt', 60),\n", " ('Russia', 'Flint', 'Gokes', 40),\n", " ('China', 'Linet', 'Conelly', 52),\n", " ('Philippines', 'Nikolas', 'Birtwell', 57),\n", " ('Australia', 'Eduard', 'Leipelt', 53)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- Feel free to experiment with the data set provided in this notebook for practice:\n", "SELECT country, first_name, last_name, test_score FROM INTERNATIONAL_STUDENT_TEST_SCORES; " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Author\n", "\n", "<a href=\"https://www.linkedin.com/in/ravahuja/\" target=\"_blank\">Rav Ahuja</a>\n", "\n", "## Change Log\n", "\n", "| Date (YYYY-MM-DD) | Version | Changed By | Change Description |\n", "| ----------------- | ------- | ---------- | ---------------------------------- |\n", "| 2020-07-17 | 2.0 | Lavanya | Moved lab to course repo in GitLab |\n", "\n", "<hr>\n", "\n", "## <h3 align=\"center\"> © IBM Corporation 2020. All rights reserved. <h3/>\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python", "language": "python", "name": "conda-env-python-py" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.11" } }, "nbformat": 4, "nbformat_minor": 4 }