{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e05f6623",
   "metadata": {},
   "source": [
    "# In-Class Exercises, Session 2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7cfb4ad",
   "metadata": {},
   "source": [
    "COMMAND LINE AND ZIP FILE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "347af407",
   "metadata": {},
   "source": [
    "<B>2.1:  Open a Command Prompt (Windows) or a Terminal window (Mac).  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cc667a6",
   "metadata": {},
   "source": [
    "<UL>\n",
    "  <LI> (Unix only) Use <B>pwd</B> to see the \"present working directory\"</LI>\n",
    "  <LI> Use <B>ls</B> (Mac) or <B>dir</B> (Windows) to see a listing of the files and directories in this directory</LI>\n",
    "  <LI> Use <B>cd</B> to move into a directory you see listed in the present working directory (for example <B>cd Downloads</B>)</LI>\n",
    "  <LI> Use <B>cd ..</B> to move into the \"parent\" directory of the one you are in (this will take you back to the parent of <B>Downlaods</B></LI>\n",
    "  <LI> Use <B>cd /Users/[yourhomedir]/Desktop</B> to move into the <B>Desktop</B> directory</LI>\n",
    "  <LI> Use <B>cd ../Downloads</B> to move from the <B>Desktop</B> directory to the <B>Downloads</B> directory</LI>\n",
    "  <LI> Use <B>cd</B> (Mac) or <B>cd %HOMEPATH%</B> (Windows) to move back into the home directory</LI>\n",
    "</UL>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21dcde2a",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d94681d",
   "metadata": {},
   "source": [
    "SQLITE COMMANDS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "654a18d8",
   "metadata": {},
   "source": [
    "<B>2.2:  In the Command Prompt (Windows) or a Terminal window (Mac), travel to the project directory (look at the prompt (Windows) or use <B>pwd</B> (Mac) to see where you are (the present working directory); <B>ls</B> (Mac) or <B>dir</B> (Windows) to see what is in the present directory, and <B>cd [dirname]</B> to travel to a directory, or <B>cd ..</b> to travel into the parent directory.  Launch sqlite with the command <B>sqlite3</B>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3e7ab24",
   "metadata": {},
   "source": [
    "<BR>\n",
    "If you unzipped your project directory in the <B>Downloads</B> folder, the commands are:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2a0c1ac",
   "metadata": {},
   "source": [
    "Mac:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8da26d40",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">cd\n",
    "cd Downloads\n",
    "cd python_data\n",
    "cd session_02_files</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ca8e692",
   "metadata": {},
   "source": [
    "Windows:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f92537a1",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">cd %HOMEPATH%\n",
    "cd Downloads\n",
    "cd python_data\n",
    "cd session_02_files</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2234caf3",
   "metadata": {},
   "source": [
    "If any command returns an error, check the spelling of your command.  If the 3rd command returns an error, check that the <B>session\\_02\\_files</B> folder was unzipped to your Downloads directory (and that you've spelled it correctly).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33014f14",
   "metadata": {},
   "source": [
    "start sqlite:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d6b300d",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite3</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a23bbe80",
   "metadata": {},
   "source": [
    "You should see the sqlite prompt:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a77539f7",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8be752d",
   "metadata": {},
   "source": [
    "You can quit out of sqlite with <B>.quit</B>:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b76705eb",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite>  <B>.quit</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "027cbfce",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1266d531",
   "metadata": {},
   "source": [
    "<B>2.3:  <U>Open a database file</U>, then immediately check to see that there are tables.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31557e76",
   "metadata": {},
   "source": [
    "From the sqlite prompt:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f868f8a8",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>.open session_2.db</B>\n",
    "sqlite> <B>.tables</B>\n",
    "ad_buys              revenue              students\n",
    "ad_companies         student_status       user\n",
    "companyrev           student_status_orig  user_classes</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdfbb223",
   "metadata": {},
   "source": [
    "If you don't see any tables listed, then you have either misspelled the name of the <B>.db</B> file, or you're in the wrong directory.  First, delete the empty file that you created so you don't get confused."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30177e8d",
   "metadata": {},
   "source": [
    "See the present working directory on Windows:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3121d489",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>.shell cd</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56f92793",
   "metadata": {},
   "source": [
    "See the present working directory on Mac:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5724300",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>.shell pwd</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5d2e4bb",
   "metadata": {},
   "source": [
    "You can use <B>.shell</B> commands to move into the directory where the <B>.db</B> file can be found.\n",
    "<BR>\n",
    "\n",
    "<U>Please note</U> that SQL queries end in a semicolon (<B>;</B>) and SQLite3 commands such as <B>.tables</B> and <B>.schema</B> do not:\n",
    "\n",
    "adding a semicolon to a \"dot\" command (e.g. <B>.schema;</B>) causes SQLite to ignore the command;\n",
    "\n",
    "omitting a semicolon from a query causes SQLite to allow \"multi-line\" queries, which means it will wait until it sees the semicolon."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6faefa44",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0816b070",
   "metadata": {},
   "source": [
    "<B>2.4:  <U>Set display columns</U>.  This configures SQLite to show SELECT output in columns with headers.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01655f82",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <b>.header on</b>\n",
    "sqlite> <b>.mode column</b></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eda71144",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65bb8d8d",
   "metadata": {},
   "source": [
    "<B>2.5:  <U>Show the schema for a table</U>.  Use the <B>.schema</B> command to see the <B>CREATE TABLE</B> for the <B>revenue</B> table, which shows its structure.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d916df61",
   "metadata": {},
   "source": [
    "show schema for a table:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cd1a6bd",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>.schema revenue</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "207bfa6a",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "597a3071",
   "metadata": {},
   "source": [
    "SQL COMMANDS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5397f1a0",
   "metadata": {},
   "source": [
    "<B>2.6:  <U>Select all rows from the <B>revenue</B> table</U>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d679fe6",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>SELECT * FROM revenue;</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d140b1b1",
   "metadata": {},
   "source": [
    "<U>Please note</U> that SQL queries end in a semicolon (<B>;</B>) and SQLite3 commands such as <B>.tables</B> and <B>.schema</B> do not:\n",
    "\n",
    "adding a semicolon to a \"dot\" command (e.g. <B>.schema;</B> causes SQLite to ignore the command;\n",
    "\n",
    "omitting a semicolon from a query causes SQLite to allow \"multi-line\" queries, which means it will wait until it sees the semicolon.\n",
    "\n",
    "If you see a continuation line indicator (<B>   ...> </B>) this means that you have started an SQL client and the client is waiting for more SQL (queries can be expressed in more than one line, so the client needs the semicolon to understand the end of the query).  If you're done, simply type the semicolon and hit [Enter]."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22a3bcdb",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "852766f9",
   "metadata": {},
   "source": [
    "<B>2.7:  <U>Create a new table</U> called 'test', with columns 'name' (a TEXT column), 'years' (INT) and 'revenue' (a REAL (or floating-point) column).  Use the <B>.schema</B> to verify the table's existence and have the query echoed back to you.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1002dec2",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>CREATE TABLE test (name TEXT, years INT, revenue REAL);</B>\n",
    "sqlite> <B>.schema test</B>\n",
    "CREATE TABLE test (name TEXT, years INT, revenue REAL);</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "997a97c8",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eecc697",
   "metadata": {},
   "source": [
    "<B>2.8:  <U>Drop the table you just created</U>.</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "383d9e8c",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>DROP TABLE test;</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "248aa088",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b37d651e",
   "metadata": {},
   "source": [
    "<B>2.9:  <U>Recreate the table</U> <B>test</B> by using the up arrow to retrieve the original <B>CREATE TABLE</B> statement.  (Make sure not to retype the CREATE TABLE statement - you don't need to!)</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d1be361",
   "metadata": {},
   "source": [
    " Use the up arrow (twice) on the SQLite3 client, no need to retype:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1487789",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>CREATE TABLE test (name TEXT, years INT, revenue REAL);</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ead1f316",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d9b062f",
   "metadata": {},
   "source": [
    "<B>2.10:  <U>Insert a row into the <B>test</B> table</U>.  Run the query a few times by pressing the up arrow and hitting [Enter]</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ba3a9f5",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>INSERT INTO test VALUES ('Joe', 23, 23.95);</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33d42412",
   "metadata": {},
   "source": [
    "Note that TEXT column values must have quotes around them and INT and REAL (floating-point) column values must not."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73d40df3",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b056402",
   "metadata": {},
   "source": [
    "<B>2.11:  <U>Run a select query</U> to display the rows you inserted.</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f0f6e6d",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>SELECT * FROM test;</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4598ab84",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b80bf75b",
   "metadata": {},
   "source": [
    "<B>2.12:  <U>Delete all rows from the table</U>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edee950b",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>DELETE FROM test;</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa4368f0",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c11bc79",
   "metadata": {},
   "source": [
    "<B>2.13:  <U>Drop table entirely</U>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c62bbd21",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>DROP TABLE test;</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17aeda11",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae270463",
   "metadata": {},
   "source": [
    "<B>2.14:  <U>Note that you can't drop a nonexistent table</U>.  Attempt to drop the 'test' table again, and note that this fails.  Add an IF EXISTS so that if the table exists, it will be dropped; if it does not exist, it will return an error.  This is especially useful for the \"wipe and reload\" best practice.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "445389df",
   "metadata": {},
   "source": [
    "Next, use the up arrow to recover and re-execute the CREATE TABLE and follow it up imediately with the DROP TABLE IF EXISTS -- to see how easy it is to create and drop tables.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "995f379b",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">sqlite> <B>DROP TABLE IF EXISTS test;</B>\n",
    "sqlite> <B>CREATE TABLE test (name TEXT, years INT, revenue REAL);</B>\n",
    "sqlite> <B>DROP TABLE IF EXISTS test;</B>\n",
    "sqlite> <B>CREATE TABLE test (name TEXT, years INT, revenue REAL);</B></DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d20cd03",
   "metadata": {},
   "source": [
    "Remember, you don't have to retype an old command!  Just use up arrow and down arrow to recall previously executed commands."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "751a96ff",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b00d2eb",
   "metadata": {},
   "source": [
    "Please KEEP YOUR SQLite SESSION OPEN FOR THE FOLLOWING Python exercises."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b27baa84",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a0f975b",
   "metadata": {},
   "source": [
    "LAB 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70b81f48",
   "metadata": {},
   "source": [
    "<U>Note</U>:  if this is a new session, please follow the directions at the start of these exercises and the first few exercises to travel to the directory where <B>session\\_2.db</B> can be found, to verify that it is in your current location, and to use <B>.open</B> to open the file.  (Don't forget that if you are in the wrong location or misspell the filename you will create a  new, empty database file.)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db40ac20",
   "metadata": {},
   "source": [
    "Also in a new session, always remember to issue the setup commands:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2550f129",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">.mode column\n",
    ".headers on</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0eed80f",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a33ba6e",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.15:  <U>Issue a <B>.tables</B> command to verify that you have opened the existing <B>session\\_2.db</B> file:</B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "380f6a73",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">.tables</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f285fd2d",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b37f6a28",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">ad_buys         companyrev      student_status  user\n",
    "ad_companies    revenue         students        user_classes</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "704b07f9",
   "metadata": {},
   "source": [
    "(please note that your table listing may be different than shown above, but should at least show several tables rather than no tables)\n",
    "\n",
    "If there are no tables listed, use <B>.quit</B> to leave <B>sqlite3</B>, use <B>ls</B> or <B>dir</B> to see that you have created a new, empty <B>.db</B> file (you can check the size in your Windows Explorer or Mac Finder view of the file), and then delete this file.  Then return to the start of these exercises and follow directions to find the existing <B>session\\_2.db</B> file."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddb00a5b",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44f9ff35",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.16:  <U>CREATE TABLE from database prompt</U>.   </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "519fe36e",
   "metadata": {},
   "source": [
    "Create a table called <B>planets</B> with the following columns:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca431708",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">planet (a TEXT field)\n",
    "mass (a REAL (or floating-point) field)\n",
    "distance (an INT field)</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2af48cfd",
   "metadata": {},
   "source": [
    "When done, issue a <B>.schema</B> query to see the query and structure of the table reflected back to you."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10f24701",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8791954",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.17:  <U>INSERT query from database prompt</U>.   </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c58eb36",
   "metadata": {},
   "source": [
    "Insert each of the following rows of values into the <B>planets</B> table by executing 3 separate INSERT query statements:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "daf20e55",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">Mercury   .33    58\n",
    "Venus    4.87   108\n",
    "Earth    5.98   150</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b7b2c4d",
   "metadata": {},
   "source": [
    "The <B>sqlite</B> prompt should return without error after each INSERT query."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd63522f",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d9949d2",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.18:  <U>SELECT query from database prompt</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "701a48f0",
   "metadata": {},
   "source": [
    "Select all rows and columns from the <B>planets</B> table.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88e8b8e4",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f197d889",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">planet      mass        distance\n",
    "----------  ----------  ----------\n",
    "Mercury     0.33        58\n",
    "Venus       4.87        108\n",
    "Earth       5.98        150</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8df8a1b",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fbe256f",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.19:  <U>SELECT query for specific columns with WHERE clause from database prompt</U>.   </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "754121f6",
   "metadata": {},
   "source": [
    "Select the planet and and distance columns for all planets further than 100 (km) from the sun.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1974a127",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7176ae2",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">planet      distance\n",
    "----------  ----------\n",
    "Venus       108\n",
    "Earth       150</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "157d8aec",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a7e9e8d",
   "metadata": {},
   "source": [
    "<U>Please keep this <B>sqlite</B> session open, or make note</U> of where your <B>session\\_2.db</B> file is located so we can access the data you created in the next lab."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57e8559d",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e908968",
   "metadata": {},
   "source": [
    "DATABASE ACCESS FROM PYTHON"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "638ca8a5",
   "metadata": {},
   "source": [
    "NOTE THAT when opening data files from the exercise files or notebooks, any filename referenced from those exercises should be preceded with <B>../</B>.  (If you create a script in the same directory as the data file, you would not include this.)\n",
    "\n",
    "NOTE ALSO THAT if you are using Jupyter Notebook, if you wish to call <B>exit()</B> from a notebook, please use <B>sys.exit()</B> (requires <B>import sys</B>); if a strange error occurs, it may be because Jupyter retains variables from all executed cells.  To reset the notebook, click 'Restart Kernel' (the circular arrow) -- this will not undo any changes made."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52877c6c",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d6ed9da",
   "metadata": {},
   "source": [
    "<B>2.20:  <U>Database connect and <B>cursor()</B></U>.  In a Python program, connect to <B>../session\\_2.db</B> (returning a <B>connection</B> object) and call <B>.cursor()</B> on it to get a <B>cursor</B> object we will use for querying.  (Note the <B>../</B> assumes that we are in a directory above the <B>session\\_2.db</B> file.)</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac7144b5",
   "metadata": {},
   "source": [
    "Close the database connection with <B>conn.close()</B> when finished.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d98e1b5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db_filename = '../session_2.db'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62db8789",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4c543d3",
   "metadata": {},
   "source": [
    "<B>2.21:  <U>Result set:  <B>for</B> looping</U>.  Use the <B>cursor</B> object to <B>.execute()</B> a <B>SELECT</B> query for all columns in the <B>revenue</B> table, and loop through the <B>result set</B> object with a <b>for</B> loop, printing each row.  Close the database connection when done.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd991965",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db_filename = '../session_2.db'\n",
    "\n",
    "conn = sqlite3.connect(db_filename)\n",
    "\n",
    "c = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd5c8b4d",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae4ddaf8",
   "metadata": {},
   "source": [
    "<B>2.22:  <U>Result set:  <B>.fetchone()</B></U>.  Use the <B>cursor</B> object to <B>.execute()</B> a <B>SELECT</B> query for all columns in the <B>revenue</B> table <B>WHERE company = 'Westfield'</B>, and use <B>.fetchone()</B> to retrieve the single result row (tuple) of values.  Close the database connection when done.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29f10a73",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db_filename = '../session_2.db'\n",
    "\n",
    "conn = sqlite3.connect(db_filename)\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "c.execute(\"SELECT * FROM revenue WHERE company = 'Westfield'\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f403e162",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1630f92b",
   "metadata": {},
   "source": [
    "<B>2.23:  <U>Result set:  <B>.fetchmany()</b></U>.  Use the <B>cursor</B> object to <B>.execute()</B> a <B>SELECT</B> query for all columns in the <B>revenue</B> table.  Use <B>.fetchmany(3)</B> to retrieve just 3 rows, then use <B>.fetchmany(4)</B> again to retrieve the remaining 4 rows.  Close the database connection when done.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f510d4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db_filename = '../session_2.db'\n",
    "\n",
    "conn = sqlite3.connect(db_filename)\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "c.execute('SELECT * FROM revenue')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "878069d7",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea176109",
   "metadata": {},
   "source": [
    "<B>2.24:  <U>Result set:  <B>.fetchall()</B></U>.  Use the <B>cursor</B> object to <B>.execute()</B> a <B>SELECT</B> query for all columns in the <B>revenue</B> table.  Use <B>.fetchall()</B> to retrieve all rows in the result set as a list of tuples.  Close the database connection when done.  </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24239db6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db_filename = '../session_2.db'\n",
    "\n",
    "conn = sqlite3.connect(db_filename)\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "c.execute(\"SELECT * FROM revenue\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78e02956",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa31e295",
   "metadata": {},
   "source": [
    "<B>2.25:  <U>Show table columns</U>.  After doing a <B>select *</B> query, use the cursor <B>.description</B> attribute to see the names of columns in a table, which are nested in a list of tuples.  Then pull out just the first item in each tuple to get a list of columns.  Close the database connection when done.    </B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22bee7b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db_filename = '../session_2.db'\n",
    "\n",
    "conn = sqlite3.connect(db_filename)\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "c.execute('SELECT * FROM revenue LIMIT 1')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e947987",
   "metadata": {},
   "source": [
    "We're using a LIMIT 1 to pull just one row from the table, which will have all columns."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ed715f2",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "189dad00",
   "metadata": {},
   "source": [
    "<B>2.26:  <U>INSERT query</U>.  Use the <B>cursor</B> object to <B>.execute()</B> an <B>INSERT</B> query, inserting a name, state and cost value into the <B>revenue</B> table.  Use parameterized arguments (?) to perform the insertions without having to worry about enquoting or escaping values in the query.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bb9ae15",
   "metadata": {},
   "source": [
    "Make sure to call <B>conn.commit()</B> (where <B>conn</B> is the connection object) to see results.  Also remember to close the database connection when done.\n",
    "\n",
    "After running, return to your SQLite3 session and perform a <B>SELECT *</B> query so you can see the new row.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f1570fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db_filename = '../session_2.db'\n",
    "\n",
    "conn = sqlite3.connect(db_filename)\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "query = 'INSERT INTO revenue VALUES (?, ?, ?)'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eca77029",
   "metadata": {},
   "source": [
    "If you don't see the change, make sure you executed <B>conn.commit()</B> after executing the insert."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f3120bb",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bad73905",
   "metadata": {},
   "source": [
    "CSV:  review"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1dfee18",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6062b51f",
   "metadata": {},
   "source": [
    "<B>2.27:  <U>Write a row to a csv</U>.  Open a new file <B>new.csv</B> for writing (remember that this will truncate (blank out) the file).  Using <B>write.writerow()</b>, write the header row first, then the data in <B>row</B> and <B>row2</B> to the file.  Make sure to close the file before finishing (especially in Jupyter, where the program does not stop running and so the file will not be closed until you close the notebook).  To confirm, open the file directly in a text editor or Excel to view it.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "776c24f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "header = ['name', 'state', 'country']\n",
    "row = ['Joe', 'CA', 'USA']\n",
    "row2 = ['Mary', 'NY', 'USA']\n",
    "\n",
    "wfh = open('../new.csv', 'w', newline='')\n",
    "writer = csv.writer(wfh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5d734e7",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "831921a2",
   "metadata": {},
   "source": [
    "<B>2.28:  <U>Write several rows to csv</U>.  Open the file <B>new2.csv</B> for writing and use <B>writer.writerow()</B> to write the header, followed by using <B>writer.writerows()</B> to write the entire list of tuples, to the file.  Make sure to close the file after writing.</B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1493570f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "header = ['name', 'state', 'country']\n",
    "rows = [ ('Joe', 'CA', 'USA'),\n",
    "         ('Mary' ,'NY', 'USA') ]\n",
    "\n",
    "wfh = open('../new2.csv', 'w', newline='')\n",
    "writer = csv.writer(wfh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "329075f7",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28e7757c",
   "metadata": {},
   "source": [
    "LAB 2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "415342c8",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.29:  <U>Connect to a database and generate a <B>cursor</B> object</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09ab3046",
   "metadata": {},
   "source": [
    "Remember that from exercise files, any data file will be located in the parent directory.  So the correct location for this week's database file is <B>session\\_2.db</B>.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60998484",
   "metadata": {},
   "source": [
    "Confirm that you have connected to the existing database with this query:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ed83faa",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">SELECT name FROM sqlite_master WHERE type= \"table\"</DIV></pre>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88d45c76",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d6c889e6",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "885ebeef",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">('ad_companies',)\n",
    "('ad_buys',)\n",
    "('students',)\n",
    "('revenue',)\n",
    "('student_status',)\n",
    "('user',)\n",
    "('user_classes',)\n",
    "('companyrev',)\n",
    "('planets',)</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51619a86",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b96dc9ce",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.30:  <U>SELECT query from <B>planets</B> table</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "809325cf",
   "metadata": {},
   "source": [
    "Select all columns and rows from the 'planets' table (created in the previous lab), then use 'for' to loop over and print each row of results.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5a6892d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('../session_2.db')\n",
    "cursor = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69270a1a",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bd2bff7",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">('Mercury', '0.33', '58')\n",
    "('Venus', '4.87', '108')\n",
    "('Earth', '5.98', '150')</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fe53812",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31a1ecaf",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.31:  <U>Use <B>.fetchone()</B> to retrieve one row</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea0b93d9",
   "metadata": {},
   "source": [
    "Select all columns from the <B>user</B> table where <B>user\\_id</B> is equal to 2.  Use <B>.fetchone()</B> to retreieve the row.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49092983",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('../session_2.db')\n",
    "cursor = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "614c4596",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd5459d0",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">(2, 'joe', 'pass', 'Joe', 'Wilson')</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f1b2b4e",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a9fc35e",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.32:  <U>Use <B>.fetchall()</B> to retrieve all rows</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a04524a3",
   "metadata": {},
   "source": [
    "Select all columns from the <B>students</B> table where <B>state</B> is equal to <B>NJ</B>.  Use <B>.fetchall()</B> to retreieve the rows.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4270c23",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('../session_2.db')\n",
    "cursor = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "347baecc",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca08f5bb",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">('jb23', '115 Karas Dr.', 'Jersey City', 'NJ', '07127')\n",
    "('jb29', '119 Xylon Dr.', 'Jersey Cit', 'NJ', '07127')</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0be4d709",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b343594",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.33:  <U>Read a table description</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e7bf0ce",
   "metadata": {},
   "source": [
    "After doing a <B>select *</B> query on the <B>students</B> table, use the cursor <B>.description</B> attribute to retrive a tuple of tuples with tables names:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0cf1b4c",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">( ('id', None, None, None, None, None, None),\n",
    "  ('address', None, None, None, None, None, None),\n",
    "  ('city', None, None, None, None, None, None),\n",
    "  ('state', None, None, None, None, None, None),\n",
    "  ('zip', None, None, None, None, None, None)     )</DIV></pre>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf4177f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('../session_2.db')\n",
    "cursor = conn.cursor()\n",
    "\n",
    "query = 'SELECT * FROM students LIMIT 1'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa53a6bf",
   "metadata": {},
   "source": [
    "Next, use a list comprehension to retrieve a list of names:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18434d92",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba2138b9",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">['id', 'address', 'city', 'state', 'zip']</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddfca271",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a102953",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.34:  <U>Insert a row</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97387725",
   "metadata": {},
   "source": [
    "Insert a new row into the <B>planets</B> table with the following data:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "692d0208",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">Mars    64.20    228</DIV></pre>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9bbb76c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('../session_2.db')\n",
    "cursor = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c868ff28",
   "metadata": {},
   "source": [
    "After committing the insert with <B>conn.commit()</B>, issue a SELECT query to confirm that the row has been inserted.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26b13fdb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2ef0c5f3",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68ac0d44",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.35:  <U>Loop through database results and write rows to CSV</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c99bb16",
   "metadata": {},
   "source": [
    "Select all rows from the <B>planets</B> table and insert them into a new file <B>../planets.csv</B> file that you open for writing and write row-by row using a <B>csv.writer</B> object.  (Make sure to include the <B>newline=''</B> argument when opening the file, and to close the write file at the end.)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d21e2b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('../session_2.db')\n",
    "cursor = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97c607ca",
   "metadata": {},
   "source": [
    "The resulting file should look like this:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "775c6df2",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">Mercury,0.33,58\n",
    "Venus,4.87,108\n",
    "Earth,5.98,150\n",
    "Mars,64.2,228</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6a94d47",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4941d488",
   "metadata": {},
   "source": [
    "JSON / MULTIDIMENSIONAL STRUCTURES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35ef515c",
   "metadata": {},
   "source": [
    "<B>2.36:  <U>View and loop through list of dicts</U>.  Open <B>weather\\_newyork\\_lod.json</B> in a text editor or PyCharm.  This is a <I>list of dicts</I> -- a list where each item in the list is a dict.</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99b94830",
   "metadata": {},
   "source": [
    "Now open this file in Python and load the JSON into a Python object with <B>json.load()</B>.\n",
    "\n",
    "Loop through the list of dicts and print each dict.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a34bdd66",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "fh = open('../weather_newyork_lod.json')\n",
    "lod = json.load(fh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0c1e6d0",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1418b9bc",
   "metadata": {},
   "source": [
    "<B>2.37:  <U>Print value from list of dicts</U>.  Again open the file <B>weather\\_newyork\\_lod.json</B> in Python and load the JSON into a Python object with <B>json.load()</B>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9eee035",
   "metadata": {},
   "source": [
    "Loop through each dict in the list of dicts and print just the 'date' column.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b00635d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "fh = open('../weather_newyork_lod.json')\n",
    "lod = json.load(fh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f4d9489",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c5cf38c",
   "metadata": {},
   "source": [
    "<B>2.38:  <U>View and loop through a dict of dicts</U>.  Open <B>weather\\_newyork\\_dod.json</B> in a text editor or PyCharm.  This is a <I>dict of dicts</I> -- a dict where each key in the dict is a string, and each value in the dict is a dict.</B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34c189ed",
   "metadata": {},
   "source": [
    "Now open this file in Python and load the JSON into a Python object with <B>json.load()</B>.\n",
    "\n",
    "Loop through the dict of dicts and print each key and associated value in the dict.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9249a557",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "fh = open('../weather_newyork_dod.json')\n",
    "dod = json.load(fh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8319ff77",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e71c0ed",
   "metadata": {},
   "source": [
    "<B>2.39:  <U>Loop through a dict of dicts and print a value from each</U>.  Again open the file <B>weather\\_newyork\\_dod.json</B> in Python and load the JSON into a Python object with <B>json.load()</B>.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d1ff83d",
   "metadata": {},
   "source": [
    "Loop through each key and value in the dict of dicts and from each dict value print the 'mean\\_temp' value.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caa4fb46",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "fh = open('../weather_newyork_dod.json')\n",
    "dod = json.load(fh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a154e6a0",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d415731c",
   "metadata": {},
   "source": [
    "<B>2.40:  <U>Travel to an inner structure</U>.  Open the file <B>ip\\_routes.json</B> in a text editor and examine the structure.  Scroll down until you see a repeating dict of dicts nested within the structure.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3de99e52",
   "metadata": {},
   "source": [
    "Now open the file in your Python code and load it into an object with <B>json.load()</b>.  See if you can reach the repeating dict of dicts using a <I>single statement</I> of chained dict and/or list subscripts.\n",
    "Print this 'inner' structure (dict of dicts) using json.dumps() with the <B>indent=4</B> parameter argument.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10afe1c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "fh = open('../ip_routes.json')\n",
    "struct = json.load(fh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1652afeb",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2eea5896",
   "metadata": {},
   "source": [
    "<B>2.41:  <U>Build a dict from a CSV file with CSV header keys</U>.  Use csv to open <B>revenue.csv</B> in a Python file and select out:  the header row and the first data row (hint:  use <B>next()</B> on the <B>reader</B> object to get one line at a time from the file).  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1424c261",
   "metadata": {},
   "source": [
    "Now build a dict from the header row and first data row with keys that match the header line.\n",
    "\n",
    "<U>Tip</u>:  use <B>dict(zip(headers, data\\_row))</B> to build the dict:  <B>zip()</B> takes two lists and \"zips\" them together like a zipper to pair up the first items in each list, the second items in each list, etc.  <B>zip(headers, data\\_row)</b> will pair up the column heads with data from the row, and passing this to <B>dict()</B> will transform to a dictionary.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9180844",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "fh = open('../revenue.csv')\n",
    "reader = csv.reader(fh)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13f861db",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dac2770",
   "metadata": {},
   "source": [
    "<B>2.42:  <U>Build a list of dicts</U>.  Incorporating what you did in the previous assignment, now loop through the file and build a dict of header keys and row values <I>for each data row</I> in the <B>revenue.csv</B> file.  Print each of the dicts as you build them.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6670f60",
   "metadata": {},
   "source": [
    "Now initialize a new empty list before the loop begins.  As you build each dict, append the dict to the list.\n",
    "\n",
    "Print the resulting structure once completed.\n",
    "\n",
    "Finally, use <B>print(json.dumps(lod, indent=4))<B> to print the structure in a friendly format.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f45471d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import json\n",
    "\n",
    "fh = open('../revenue.csv')\n",
    "reader = csv.reader(fh)\n",
    "\n",
    "lod = []\n",
    "\n",
    "header = next(reader)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b760413b",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "786e4ef5",
   "metadata": {},
   "source": [
    "<B>2.43:  <U>Build a dict of dicts</U>.  Adapting what you did in the prior solution, now loop through the file and build a dict of header keys and row values <I>for each data row</I> in the <B>revenue.csv</B> file.  Print each of the dicts as you build them.  </B>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63a4347c",
   "metadata": {},
   "source": [
    "Now initialize a new empty dict before the loop begins.  As you build each dict, add the \"inner\" dict to the \"outer\" dict:  make the company name the key in the \"outer\" dict, and the value the \"inner\" dict.\n",
    "\n",
    "Print the resulting structure once completed.\n",
    "\n",
    "Finally, use <B>print(json.dumps(dod, indent=4))</B> to print the structure in a friendly (\"pretty\") format.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c03ecea8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import json\n",
    "\n",
    "fh = open('../revenue.csv')\n",
    "reader = csv.reader(fh)\n",
    "\n",
    "dod = {}\n",
    "\n",
    "header = next(reader)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68e2b7cb",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d14caf6d",
   "metadata": {},
   "source": [
    "LAB 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ac9248e",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.44:  <U>Access item in a list of lists</U>. </B></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "622b734b",
   "metadata": {},
   "outputs": [],
   "source": [
    "lol = [\n",
    "    [ 1816,   6.7,  'Indiana' ],\n",
    "    [ 1788,  10.6,  'Georgia' ],\n",
    "    [ 1819,   4.9,  'Alabama' ]\n",
    "]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1075e6aa",
   "metadata": {},
   "source": [
    "Access the value <B>10.6</B> and print it.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f01ad11",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26c2aee8",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">10.6</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e38f9e22",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2eacbfb7",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.45:  <U>Loop through and print 'column' values in a list of lists</U>.</B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5153398b",
   "metadata": {},
   "source": [
    "Using a loop, print the state names one after the other.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3071857",
   "metadata": {},
   "outputs": [],
   "source": [
    "lol = [\n",
    "    [ 1816,   6.7,  'Indiana' ],\n",
    "    [ 1788,  10.6,  'Georgia' ],\n",
    "    [ 1819,  4.9,   'Alabama' ]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a94c39ac",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df21b3a6",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">Indiana\n",
    "Georgia\n",
    "Alabama</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebd0cafe",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bf6d13c",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.46:  <U>Loop through and print 'column' values in a list of dicts</U>. </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14f60062",
   "metadata": {},
   "source": [
    "Using a loop, print the years one after the other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa977bef",
   "metadata": {},
   "outputs": [],
   "source": [
    "lol = [\n",
    "    { 'name': 'Indiana',\n",
    "      'year': '1816',\n",
    "      'pop':  6.7\n",
    "    },\n",
    "    { 'name': 'Georgia',\n",
    "      'year': '1788',\n",
    "      'pop':  10.6\n",
    "    },\n",
    "    { 'name': 'Alabama',\n",
    "      'year': '1819',\n",
    "      'pop':  4.9\n",
    "    }\n",
    "]\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80061e3e",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04195ee5",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">1816\n",
    "1788\n",
    "1819</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed55c4a3",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a67f994",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.47:  <U>Access item in a dict of dicts</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b2e9b1a",
   "metadata": {},
   "source": [
    "In a single statement, print <B>1819</B>."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a36f5584",
   "metadata": {},
   "outputs": [],
   "source": [
    "dod = {\n",
    "    'Indiana':  {\n",
    "                   'year': '1816',\n",
    "                   'pop':  6.7\n",
    "                },\n",
    "    'Georgia':  {\n",
    "                   'year': '1788',\n",
    "                   'pop':  10.6\n",
    "                },\n",
    "    'Alabama':  {\n",
    "                   'year': '1819',\n",
    "                   'pop':  4.9\n",
    "                }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bef8a91",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "929d46c3",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">1819</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9076c406",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e6d3a7f",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.48:  <U>Loop through and print 'column' values in a dict of dicts</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d59fbc86",
   "metadata": {},
   "source": [
    "Using a loop, print the populations, one after the other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c61b772",
   "metadata": {},
   "outputs": [],
   "source": [
    "dod = {\n",
    "    'Indiana':  {\n",
    "                   'year': '1816',\n",
    "                   'pop':  6.7\n",
    "                },\n",
    "    'Georgia':  {\n",
    "                   'year': '1788',\n",
    "                   'pop':  10.6\n",
    "                },\n",
    "    'Alabama':  {\n",
    "                   'year': '1819',\n",
    "                   'pop':  4.9\n",
    "                }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db92efdd",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0efb0d8",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">6.7\n",
    "10.6\n",
    "4.9</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0ce3fdf",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb752be1",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.49:  <U>Access an item in a complex structure</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce4b73ea",
   "metadata": {},
   "source": [
    "Print the value <B>9.7</B> in the below structure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc970d51",
   "metadata": {},
   "outputs": [],
   "source": [
    "struct = { 'version':  1.1,\n",
    "           'data': {\n",
    "                'states': {\n",
    "                    'Indiana':  {\n",
    "                                   'year': '1816',\n",
    "                                   'pop':  [ 6.7, 6.5, 6.1 ]\n",
    "                                },\n",
    "                    'Georgia':  {\n",
    "                                   'year': '1788',\n",
    "                                   'pop':  [ 10.6, 9.7, 8.2 ]\n",
    "                                },\n",
    "                    'Alabama':  {\n",
    "                                   'year': '1819',\n",
    "                                   'pop':  [ 4.9, 4.8, 4.4 ]\n",
    "                                }\n",
    "                },\n",
    "                'pop_years':  [ '2019', '2010', '2000']\n",
    "            }\n",
    "        }"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4dc56fa",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0364e6f7",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">9.7</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5330a270",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14f113a8",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.50:  <U>Loop through and print 'column' values in a complex structure</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1181d8a",
   "metadata": {},
   "source": [
    "Using a loop, print the values <B>1816</B>, <B>1788</B> and <B>1819</B> from the below structure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bbde946f",
   "metadata": {},
   "outputs": [],
   "source": [
    "struct = { 'version':  1.1,\n",
    "           'data': {\n",
    "                'states': {\n",
    "                    'Indiana':  {\n",
    "                                   'year': '1816',\n",
    "                                   'pop':  [ 6.7, 6.5, 6.1 ]\n",
    "                                },\n",
    "                    'Georgia':  {\n",
    "                                   'year': '1788',\n",
    "                                   'pop':  [ 10.6, 9.7, 8.2 ]\n",
    "                                },\n",
    "                    'Alabama':  {\n",
    "                                   'year': '1819',\n",
    "                                   'pop':  [ 4.9, 4.8, 4.4 ]\n",
    "                                }\n",
    "                },\n",
    "                'pop_years':  [ '2019', '2010', '2000']\n",
    "            }\n",
    "        }"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c8b885c",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70740864",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">1816\n",
    "1788\n",
    "1819</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "797db70d",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0bb4a0a",
   "metadata": {},
   "source": [
    "<div style=\"background-color: #ccffcc; padding: 10px; border: 2px solid green\"><B>2.51:  <U>Perform a loop within a loop within a complex structure</U>.  </B></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cddafe5",
   "metadata": {},
   "source": [
    "Using a loop nested within another 'for' loop, print each state, print and all 3 population values.  For clarity, try indenting each population value, and printing an extra blank line after each state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ea60dc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "struct = { 'version':  1.1,\n",
    "           'data': {\n",
    "                'states': {\n",
    "                    'Indiana':  {\n",
    "                                   'year': '1816',\n",
    "                                   'pop':  [ 6.7, 6.5, 6.1 ]\n",
    "                                },\n",
    "                    'Georgia':  {\n",
    "                                   'year': '1788',\n",
    "                                   'pop':  [ 10.6, 9.7, 8.2 ]\n",
    "                                },\n",
    "                    'Alabama':  {\n",
    "                                   'year': '1819',\n",
    "                                   'pop':  [ 4.9, 4.8, 4.4 ]\n",
    "                                }\n",
    "                },\n",
    "                'pop_years':  [ '2019', '2010', '2000']\n",
    "            }\n",
    "        }\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dea4a879",
   "metadata": {},
   "source": [
    "<U>Expected Output</U>:  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfd765f9",
   "metadata": {},
   "source": [
    "<pre style=\"margin: 0; padding=0\"><DIV style=\"font-family: Courier; border: 1px solid #dbdbdb; padding: 7px\">Indiana\n",
    "  6.7\n",
    "  6.5\n",
    "  6.1\n",
    "\n",
    "Georgia\n",
    "  10.6\n",
    "  9.7\n",
    "  8.2\n",
    "\n",
    "Alabama\n",
    "  4.9\n",
    "  4.8\n",
    "  4.4</DIV></pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a242518",
   "metadata": {},
   "source": [
    " "
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}
