<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by gauravinthevalley on Medium]]></title>
        <description><![CDATA[Stories by gauravinthevalley on Medium]]></description>
        <link>https://medium.com/@gauravinthevalley?source=rss-698a7492dbf3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*Oo664bj2LyzZn2LC.jpg</url>
            <title>Stories by gauravinthevalley on Medium</title>
            <link>https://medium.com/@gauravinthevalley?source=rss-698a7492dbf3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 17:52:39 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@gauravinthevalley/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Run the Parch & Posey DB Locally in SQLite]]></title>
            <link>https://medium.com/udacity-technology-scholarship/run-the-parch-posey-db-locally-in-sqlite-91b5cb7a110e?source=rss-698a7492dbf3------2</link>
            <guid isPermaLink="false">https://medium.com/p/91b5cb7a110e</guid>
            <category><![CDATA[udacity-nanodegree]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[bertelsmann]]></category>
            <category><![CDATA[sqlite3]]></category>
            <dc:creator><![CDATA[gauravinthevalley]]></dc:creator>
            <pubDate>Sun, 16 Feb 2020 07:43:37 GMT</pubDate>
            <atom:updated>2020-02-16T09:13:19.255Z</atom:updated>
            <content:encoded><![CDATA[<p>One of the goals I’ve set up for myself in the Bertelsmann Tech Scholarship Challenge Course — Data Track Nanodegree Program in <a href="http://udacity.com/">Udacity</a>, is that by the end of the course, I would like to build a basic dashboard using Flask and some basic Javascript, hosted on <a href="https://www.heroku.com/">Heroku</a>. My plan for the dashboard is to display analyzed-data from the Parch &amp; Posey DB, which is the database used in the course to teach SQL. Since I’m new to building backends with <a href="https://www.palletsprojects.com/p/flask/">Flask</a> (I’m more comfortable with Node.js), I’ve been following <a href="https://www.youtube.com/watch?v=MwZwr5Tvyxo&amp;list=PL-osiE80TeTs4UjLw5MM6OjgkjFeUxCYH">this series from Corey Schafer in YouTube</a> to teach myself Flask — he is pretty good. The front-end Javascript part is something I can manage myself. I plan to use <a href="https://reactjs.org/">React</a> to build it.</p><p>Corey Schafer starts you off with SQLite with the intent to switch to Postgres later in the course. Because of this reason, I had to run the Parch &amp; Posey DB in SQLite. Though I’m sure there are better and more efficient ways to run the Parch &amp; Posey DB in SQLite, here’s what I did.</p><p><strong>Install SQLite3</strong></p><p><a href="https://www.sqlite.org/download.html">This page</a> is your friend. If you’re on a Mac, I highly recommend you use <a href="https://brew.sh/">Homebrew</a> to install SQLite3. If you’re on Linux, depending upon your flavor of it, you can use the package manager that comes bundled with your flavor to install SQLite3.</p><p>Once installed, I recommend you <a href="https://sqlite.org/cli.html">play with the CLI </a>for a bit to get comfortable with SQLite3. The CLI is pretty easy to get a handle on.</p><p><strong>Create the Parch &amp; Posey DB</strong></p><p>As you already saw <a href="https://sqlite.org/cli.html">here</a>, to create a DB in SQLite3, all you have to do is run the following command on your terminal:</p><pre>sqlite3 parchandposey.db</pre><p>This command creates a literal parchandposey.db file inside the directory from where you ran the command above; and you are also redirected to the SQLite3 CLI.</p><p>This bit may be tricky for beginners. For instance, it took me about 5 minutes of figuring out why the .databases command inside the CLI was not displaying any databases for me: it was because, I was in a different directory than where the parchandposey.db resided. So ensure that you are in the directory you intend to be in while creating your database.</p><p><strong>Inside the SQLite3 CLI</strong></p><p>Like I mentioned above, running .databases will display the literal parchandposey.db file created, like so:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JY0J5NRjG94POakjVXvhtw.png" /></figure><p>Alright, <a href="https://medium.com/@gauravinthevalley/run-the-parch-posey-db-locally-in-postgres-8a0c2fde0c2e">like we did for Postgres</a>, the first step is to create your tables. Let’s get on with creating the schema and importing the CSVs one by one.</p><p><strong>Create and Import <em>region</em> Table</strong></p><p>I’m assuming you already have all the CSVs required. If you don’t, you can <a href="https://github.com/jdbarillas/parchposey/tree/master/data-raw">get them from here</a>. Downloading the CSVs to your current directory will make things easy (you don’t have to type the entire CSV path when importing, for instance). Please ignore the .R files in that Github repo.</p><p>There’s actually another way to procuring the CSVs: you can download the data from the course itself. From your workspace, if you SELECT * from Region, you can download the CSV for the <em>Region </em>table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W6zpgIcQj0ZfEpH3ZqTDDQ.png" /><figcaption>Download CSV from Workspace</figcaption></figure><p>No matter which method you go with to procure the CSVs, be sure to download and store them to your current directory.</p><blockquote>When importing CSVs, there are generally 2 cases to consider: whether you want to import the headers, or you do not want to import the headers. In our case, because we will be creating schemas for all of our tables beforehand, we do not want to import the headers. SQLite is pretty specific here. <a href="https://www.sqlite.org/cli.html#csv_import">They clearly mention in their documentation</a> that if during import, table does not exist, it will treat the first row as headers and the rest as data. But if a table does exist (which will be our case), it will treat the first row also as data.</blockquote><p>The point above also means that we will have to manually delete the 1st line of the CSV - the one with headers. Why? Again, because we will create our tables’ schema first and then import the CSVs.</p><p>Open your <em>region.csv </em>(bear in mind that you could have named this file anything; for clarity, I named mine <em>region</em>). Here’s how mine looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/934/1*bB6B2wl-gRvMvwLQ5_oHtw.png" /><figcaption>Region with Header — region.csv</figcaption></figure><p>Check out that first row! “id” and “name” will be the names of our columns in soon-to-be-created <em>region </em>table. If we wouldn’t be creating the <em>region </em>table manually and directly importing this file into SQLite3, we wouldn’t have to delete this row. But in our case, we do so let’s remove the headers:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j6Ai4ZGuyzlQCd_eCcP3NA.png" /><figcaption>Region with No Header — region-no-header.csv</figcaption></figure><p>Once the first row (header) is deleted, I saved the the file as <em>region-no-header.csv. </em>This is the file we will be importing; not before creating the <em>region </em>table first though.</p><p>Run the following CREATE TABLE statement to create the table:</p><pre>CREATE TABLE region</pre><pre>(</pre><pre>id integer NOT NULL,</pre><pre>name text NOT NULL</pre><pre>);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/502/1*bCZQdtag8oZZszs1Q9M-pA.png" /><figcaption>SQLite3 — Create region table</figcaption></figure><p>As you can see in the screenshot above, you can verify that the <em>region </em>table was actually created using the friendly .tables command.</p><p>If you run the .schema region command, you can also check the schema of your table, like so:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/506/1*VjbwaOmC5cONbk8hAYRgPQ.png" /><figcaption>SQLite3 — View Schema of Region table</figcaption></figure><p>Let’s actually get to importing the <em>region</em> CSV now, shall we?</p><p>First off, you will need to change the mode of SQLite. Run the .mode command you check your current mode.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/386/1*HQyfVV4jmMr5IqIQjYfCRA.png" /><figcaption>SQLite3 — View your Mode</figcaption></figure><p>Change the mode from ‘list’ to ‘CSV’ with .mode CSV:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/354/1*hexEqGpoMUUrvMoLUfo6Ew.png" /><figcaption>SQLite3 — CSV Mode</figcaption></figure><p>Very noice! Since you already have the CSV downloaded, header removed, and saved in the current directory, all you now have to do is run the following command to import:</p><p>.import region-no-header.csv region</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/1*VsMNNYeiMm5zbxxnMzapog.png" /><figcaption>SQLite3 — Import region CSV (no header)</figcaption></figure><p>Apart from importing <em>region </em>CSV above, I have also verified that the data was actually imported with a simple SELECT statement. The data in <em>region </em>table is not that many, so I decided to SELECT * FROM region; above. For other tables, we will use the COUNT(*) function to verify imports.</p><p><strong>Create and Import <em>sales_reps</em> Table</strong></p><p>Ensure that the mode is still csv. This applies to the rest of this guide.</p><p>Also ensure that your .CSV for <em>sales_reps </em>is downloaded and stored in your current directory. I have named mine <em>sales_reps.csv</em>. Like we did for <em>region.csv</em>, we will also remove the headers manually for <em>sales_reps</em>. This also applies to the rest of the .CSVs on this guide.</p><p>Now, let’s create the <em>sales_reps </em>table and verify that it was actually created. Run the following CREATE TABLE statement to create the table:</p><pre>CREATE TABLE sales_reps</pre><pre>(</pre><pre>id integer NOT NULL,</pre><pre>name text NOT NULL,</pre><pre>region_id integer NOT NULL,</pre><pre>FOREIGN KEY(region_id) REFERENCES region(id)</pre><pre>);</pre><p>Verify that the table was actually created:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*sNhqYlltgUa-V8nWoG1BHA.png" /><figcaption>SQLite3 — Create and Verify sales_reps Table</figcaption></figure><p>Okay, you know the drill! Let’s import the .CSV without headers for <em>sales_reps</em>. Mine is named <em>sales_reps-no-header.csv </em>so I will run the following command to import it into my newly creates <em>sales_reps </em>table in SQLite3.</p><p>.import sales_reps-no-header.csv sales_reps</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/832/1*BOorr0YrDAFC41JOHljTUw.png" /><figcaption>SQLite3 — Import sales_reps CSV (no header)</figcaption></figure><p>There you have it! Imported and verified.</p><p><strong>Create and Import <em>accounts</em> Table</strong></p><p>From here on out, I will just post the CREATE TABLE script. Here we go:</p><pre>CREATE TABLE accounts</pre><pre>(</pre><pre>id integer NOT NULL,</pre><pre>name text NOT NULL,</pre><pre>website text NOT NULL,</pre><pre>lat text,</pre><pre>&quot;long&quot; text,</pre><pre>primary_poc text,</pre><pre>sales_rep_id integer NOT NULL,</pre><pre>FOREIGN KEY(sales_rep_id) REFERENCES sales_reps(id)</pre><pre>);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QHKH2dxtQgYsJYtfbz0DQg.png" /><figcaption>SQLite3 — Import accounts (no header)</figcaption></figure><p>Imported and verified! Let’s move on.</p><p><strong>Create and Import <em>orders</em> Table</strong></p><p>Run the following CREATE TABLE script to create the <em>orders </em>table first:</p><pre>CREATE TABLE orders</pre><pre>(</pre><pre>id integer NOT NULL,</pre><pre>account_id integer NOT NULL,</pre><pre>occurred_at text,</pre><pre>standard_qty integer,</pre><pre>gloss_qty integer,</pre><pre>poster_qty integer,</pre><pre>total integer,</pre><pre>standard_amt_usd real,</pre><pre>gloss_amt_usd real,</pre><pre>poster_amt_usd real,</pre><pre>total_amt_usd real,</pre><pre>FOREIGN KEY (account_id) REFERENCES accounts(id)</pre><pre>);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u2eWJSIyqs5qW9bWuoK59g.png" /><figcaption>SQLite3 — Import orders (no header)</figcaption></figure><p><strong>Create and Import <em>web_events</em> Table</strong></p><p>Let’s create the <em>web_events </em>table with:</p><pre>CREATE TABLE web_events</pre><pre>(</pre><pre>id integer NOT NULL,</pre><pre>account_id integer NOT NULL,</pre><pre>occurred_at text,</pre><pre>channel text,</pre><pre>FOREIGN KEY (account_id) REFERENCES accounts(id)</pre><pre>);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/906/1*I-MNVQY-uMjrbxvaXFZ4lQ.png" /><figcaption>SQLite3 — Import web_events (no header)</figcaption></figure><p>All done! We’ve created and imported all tables in the Parch &amp; Posey DB. I haven’t explained the CREATE TABLE queries in this post. I’m no expert but I’m assuming you are already familiar with schema design, etc. If you aren’t, you probably aren’t reading this. If you aren’t but did make it this point, there are plenty of resources online to help you out — and they are probably much better than what I will ever come up with. Good luck!</p><p>Before we part, let’s run a query from the course:</p><pre><strong>SELECT</strong> r.<strong>name</strong> region, a.<strong>name</strong> <strong>account</strong>, o.total_amt_usd/(o.total + 0.01) unit_price<br><strong>FROM</strong> region r<br><strong>JOIN</strong> sales_reps s<br><strong>ON</strong> s.region_id = r.<strong>id</strong><br><strong>JOIN</strong> accounts a<br><strong>ON</strong> a.sales_rep_id = s.<strong>id</strong><br><strong>JOIN</strong> orders o<br><strong>ON</strong> o.account_id = a.<strong>id</strong><br><strong>WHERE</strong> o.standard_qty &gt; 100 <strong>AND</strong> o.poster_qty &gt; 50<br><strong>ORDER</strong> <strong>BY</strong> unit_price;</pre><p>This one is from Lesson 27, Section 20, Number 5. Go ahead and run it.</p><p>Isn’t that something? 😄</p><p>Happy querying!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=91b5cb7a110e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/udacity-technology-scholarship/run-the-parch-posey-db-locally-in-sqlite-91b5cb7a110e">Run the Parch &amp; Posey DB Locally in SQLite</a> was originally published in <a href="https://medium.com/udacity-technology-scholarship">Udacity Technology Scholarship</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Run the Parch & Posey DB Locally in Postgres]]></title>
            <link>https://medium.com/udacity-technology-scholarship/run-the-parch-posey-db-locally-in-postgres-8a0c2fde0c2e?source=rss-698a7492dbf3------2</link>
            <guid isPermaLink="false">https://medium.com/p/8a0c2fde0c2e</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[udacity-nanodegree]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[gauravinthevalley]]></dc:creator>
            <pubDate>Fri, 31 Jan 2020 02:21:32 GMT</pubDate>
            <atom:updated>2020-02-01T02:26:23.768Z</atom:updated>
            <content:encoded><![CDATA[<p>Currently, I’m enrolled in the Bertelsmann Tech Scholarship Challenge Course — Data Track Nanodegree Program in <a href="http://udacity.com/">Udacity</a>. I’ve detailed my thoughts thus far about this course <a href="https://medium.com/@gauravinthevalley/on-the-bertelsmann-technology-scholarship-ace4e782f432">here</a>. One of the topics we’re taught in this program is SQL — they start you out with basic SQL and get you writing complex queries in no time. If you already know some SQL (but haven’t found yourself writing any of late), this is a good refresher of a course; if you don’t already know some SQL, this is a great course to get started learning SQL, among other things like Statistics and Python</p><p>This course primarily uses the Database (DB) of fictional company called Parch &amp; Posey to teach SQL; which also means that you will be using this DB to work out most of the quizzes. All the quizzes are interactive — they provide you with a workspace within which you can run your queries and view all the tables and their columns in the DB. However, if like me, you also wish to run the Parch &amp; Posey DB locally in your machine, I’ve outlined the process I went through to do so below.</p><p><strong>Download and Install Postgres</strong></p><p>First off, g<a href="https://www.postgresql.org/download/">o here to download</a> Postgres. Make sure you are downloading the pre-built binaries for your operating system. For Mac users, I recommend using <a href="http://brew.sh/">Homebrew</a> to do download and install Postgres. For Linux users, depending on the flavor of Linux you are using, I’m sure the package manager that comes bundled with your flavor can install Postgres without much fuss. For Ubuntu 18.04 for instance, <a href="https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04">this is a pretty good guide</a>.</p><p><strong>Play with Postgres</strong></p><p>Again, I’d like to point you to the <a href="https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04">excellent Digital Ocean tutorial</a> from above. Login to your local Postgres installation via the terminal, create a user (this is an important step as you will need this later), a DB, etc. all without leaving the comfort of your command line 😃Play with Postgres for a bit before downloading and installation a GUI tool to access Postgres.</p><p><strong>Postgres GUI</strong></p><p>This part is purely optional, of course. If you like your command line better than anything, there’s obviously no need to to follow these steps. Else, I’d recommend the fantastic <a href="http://pgadmin.org/">pgAdmin</a>. They bill themselves thus:</p><blockquote>“pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.”</blockquote><p><a href="https://www.pgadmin.org/download/">Go here to download pgAdmin</a> for your operating system.</p><p><strong>Run pgAdmin</strong></p><p>Once you install pgAdmin and run it, this is what you should get:</p><figure><img alt="Starting pgAdmin4 server on a Mac" src="https://cdn-images-1.medium.com/max/930/1*Twu9vvgn593XS5_RlpG3CA.png" /><figcaption>pgAdmin — Starting Up</figcaption></figure><p>After a few seconds, pgAdmin ought to load up in your default browser like so:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CX6TPQ_0lJgzuj7wG08njg.png" /><figcaption>pgAdmin — Enter Password</figcaption></figure><p>Here, you can simply reset the master password for pgAdmin and remember it (save it in the browser, etc.) for later.</p><p><strong>pgAdmin Interface</strong></p><p>Okay, so once you enter the pgAdmin interface after having logged into it, you should see a screen like so:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Vq0Kj4cMwRPndm6KUajYqw.png" /><figcaption>pgAdmin — Interface</figcaption></figure><p>Now is when you connect to your locally installed Postgres DB server.</p><p><strong>Connect to Localhost</strong></p><p>To connect to your installed Postgres DB server in your <a href="https://en.wikipedia.org/wiki/Localhost">Localhost</a>, you need to create a server, like so:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fL-p8epG8EhG6bcyttK1fg.png" /><figcaption>pgAdmin — Create Server</figcaption></figure><p>After that, you get a pop-up/modal/dialog like the one below:</p><figure><img alt="pgAdmin Create Server pop-up" src="https://cdn-images-1.medium.com/max/1024/1*p6mLfWEBidC7OYg4NYVEMg.png" /><figcaption>pgAdmin — Create Server General</figcaption></figure><p>In the “name” field, you can enter any name you desire. I put “localhost”. Ignore that error displayed toward the bottom of the pop-up for now. Go to the “Connection” tab, and enter the “Host” , “Username”, and “Password” fields like I have done (obviously, your “Username” will surely be different):</p><figure><img alt="pgAdmin Create Server Connection" src="https://cdn-images-1.medium.com/max/1024/1*qcJxT32OazHG7BS5tSkW9A.png" /><figcaption>pgAdmin — Create Server Connection</figcaption></figure><p>Host has to be “localhost” or the loopback address of “127.0.0.1”. Once you enter one of these values, the error from above will disappear. You should enter the password you created for the superuser username if you followed <a href="https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04">the Digital Ocean tutorial</a> I mentioned above.</p><p>If you created another user (who is not a superuser), that should be fine as well. Once you hit “Save”, you will be presented with every DB functionality to which the user you entered above has access to on your Localhost.</p><p>Time to go grab a cup of coffee 🍮 What say?</p><p><strong>Create the Parch &amp; Posey DB</strong></p><p>Still with me? Great! Let’s move on along.</p><p>If you’re connected to your localhost, proceed to create your database by right-clicking on Databases:</p><figure><img alt="pgAdmin Create Database" src="https://cdn-images-1.medium.com/max/1024/1*SCFPobQxl3qG06kxpYhgHQ.png" /><figcaption>pgAdmin — Right-Click to Create Database</figcaption></figure><p>Okay, you’re now presented with the following pop-up:</p><figure><img alt="pgAdmin — Create Database" src="https://cdn-images-1.medium.com/max/1024/1*9_kYp47GBypcC9u4gWg81w.png" /><figcaption>pgAdmin — Create Database</figcaption></figure><p>Hit the “Save” button, and you now have the mighty <em>parchandposey</em> DB created in your localhost. Albeit, a bit empty. Let’s create the 5 Tables and import all the data, shall we?</p><p><strong>Create <em>Region</em> Table</strong></p><p>There may be various ways of doing the <em>Region</em> Table. I decided to create it the old-school way — using the SQL CREATE TABLE statement. First though, let’s go to the Query Tool:</p><figure><img alt="pgadmin Query Tool" src="https://cdn-images-1.medium.com/max/924/1*HhMrP8842CCJ6ZTffU6OoA.png" /><figcaption>pgAdmin — Right-Click on parchandposey</figcaption></figure><p>As you see on the image above, right-click on <em>parchandpsey </em>and click on “Query Tool” to bring it up and enter the SQL to create <em>Regions </em>Table like so:</p><figure><img alt="pgAdmin create Region table" src="https://cdn-images-1.medium.com/max/1024/1*qUS2xN3gQE285vgDyHmlUw.png" /><figcaption>pgAdmin — Create Region Table</figcaption></figure><p>Press Play! Or hit the “F5” button on your keyboard to execute the query you just wrote:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CofJo1arrWsuXiVVehjpow.png" /><figcaption>pgAdmin — “Play”/Execute Button Toward the Right</figcaption></figure><p>See your <em>Region </em>Table in pgAdmin like so:</p><figure><img alt="pgAdmin View Tables 1" src="https://cdn-images-1.medium.com/max/424/1*rt_l70n-XQOoy4nnQXzG7A.png" /><figcaption>pgAdmin — Schemas -&gt; Tables -&gt; Region</figcaption></figure><p>Currently, the region table is empty. Let’s fill it up with data.</p><p>You can download the data from the course itself. From your workspace, if you SELECT * from Region, you can download the CSV.</p><figure><img alt="Download CSV from workspace" src="https://cdn-images-1.medium.com/max/1024/1*kFr-OYPvKGxYx5sTZ2md2g.png" /><figcaption>Download CSV from Workspace</figcaption></figure><p>Or you can <a href="https://github.com/jdbarillas/parchposey/tree/master/data-raw">go to this Github Repo</a>, and download all CSVs. Your call.</p><p>Okay, so once you download the CSVs, go to your directory where you downloaded the CSVs on the terminal . I’m sure this can be done via the pgAdmin interface as well, but I couldn’t get it done — got an error during importing table data (my mac says the CSV file does not exist for some reason). If you know a way, please let me know in the comments below.</p><p>Once in the terminal and inside the psql CLI, connect to your DB with \c parchandposey Verify that you have the <em>Region </em>table in there with \d</p><p>Okay, now you can copy the data from the CSV into your <em>Region </em>table with \copy region from ‘region.csv’ DELIMITER ‘,’ CSV HEADER;</p><p>I’m assuming that you named your CSV file as “region.csv” here.</p><p>Check that you have the data with SELECT * from Region;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_EbFipvf1naXNaAmnWuKLg.png" /><figcaption>psql — Steps to Import Region Data</figcaption></figure><p>Aaand, Voila! There you have it. 4 records is all that the <em>Region </em>Table has.</p><p>Above, I named my DB <em>parchandposey1 </em>because I already have a <em>parchandposey </em>in my local machine.</p><p>Go back to pgAdmin to check the <em>Region </em>data:</p><figure><img alt="pgAdmin Run a query" src="https://cdn-images-1.medium.com/max/1024/1*ogQUszrdSkwfz2XROLaq5Q.png" /><figcaption>pgAdmin — Check Region Data</figcaption></figure><p>To the run query above, you know the deal. Right-Click on your DB name, go to “Query Tool”, enter your query, and press play. You see the same 4 records.</p><p><strong>Create <em>SalesReps </em>Table</strong></p><p>Similar to the steps above, you can now create your <em>SalesReps </em>Table. Here’s the CREATE TABLE statement for it:</p><p>CREATE TABLE public.sales_reps<br>(<br> id integer NOT NULL,<br> name character varying(255) COLLATE pg_catalog.”default” NOT NULL,<br> region_id integer NOT NULL,<br> CONSTRAINT sales_reps_pkey PRIMARY KEY (id),<br> CONSTRAINT region_id FOREIGN KEY (region_id)<br> REFERENCES public.region (id) MATCH SIMPLE<br> ON UPDATE NO ACTION<br> ON DELETE NO ACTION<br>)</p><p>You can import the data in the exact same way that you imported <em>Region </em>Table Data above. After import, you can verify that the <em>SalesReps </em>Table has 50 records.</p><p><strong>Create <em>Accounts </em>Table</strong></p><p>Here’s the CREATE TABLE statement for <em>Accounts </em>Table:</p><p>CREATE TABLE public.accounts<br>(<br> id integer NOT NULL,<br> name character varying(255) COLLATE pg_catalog.”default” NOT NULL,<br> website character varying(255) COLLATE pg_catalog.”default” NOT NULL,<br> lat character varying(100) COLLATE pg_catalog.”default”,<br> “long” character varying(100) COLLATE pg_catalog.”default”,<br> primary_poc character varying(255) COLLATE pg_catalog.”default”,<br> sales_rep_id integer NOT NULL,<br> CONSTRAINT accounts_pkey PRIMARY KEY (id),<br> CONSTRAINT sales_rep_id FOREIGN KEY (sales_rep_id)<br> REFERENCES public.sales_reps (id) MATCH SIMPLE<br> ON UPDATE NO ACTION<br> ON DELETE NO ACTION<br>)</p><p>You can import the data in the exact same way that you imported <em>Region </em>Table Data above. After import, you can verify that the <em>Accounts </em>Table has 351 records.</p><p><strong>Create <em>Orders</em> Table</strong></p><p>Here’s the CREATE TABLE statement for <em>Orders </em>Table:</p><p>CREATE TABLE public.orders<br>(<br> id integer NOT NULL,<br> account_id integer NOT NULL,<br> occurred_at timestamp with time zone,<br> standard_qty integer,<br> gloss_qty integer,<br> poster_qty integer,<br> total integer,<br> standard_amt_usd numeric,<br> gloss_amt_usd numeric,<br> poster_amt_usd numeric,<br> total_amt_usd numeric,<br> CONSTRAINT orders_pkey PRIMARY KEY (id),<br> CONSTRAINT account_id FOREIGN KEY (account_id)<br> REFERENCES public.accounts (id) MATCH SIMPLE<br> ON UPDATE NO ACTION<br> ON DELETE NO ACTION<br>)</p><p>You can import the data in the exact same way that you imported <em>Region </em>Table Data above. After import, you can verify that the <em>Orders </em>Table has 6912 records.</p><p><strong>Create <em>WebEvents</em> Table</strong></p><p>Here’s the CREATE TABLE statement for <em>Orders </em>Table:</p><p>CREATE TABLE public.web_events<br>(<br> id integer NOT NULL,<br> account_id integer NOT NULL,<br> occurred_at timestamp without time zone,<br> channel character varying(255) COLLATE pg_catalog.”default”,<br> CONSTRAINT web_events_pkey PRIMARY KEY (id),<br> CONSTRAINT account_id FOREIGN KEY (account_id)<br> REFERENCES public.accounts (id) MATCH SIMPLE<br> ON UPDATE NO ACTION<br> ON DELETE NO ACTION<br>)</p><p>You can import the data in the exact same way that you imported <em>Region </em>Table Data above. After import, you can verify that the <em>WebEvents </em>Table has 9073 records.</p><p>And that’s pretty much it.</p><p>Happy querying!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8a0c2fde0c2e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/udacity-technology-scholarship/run-the-parch-posey-db-locally-in-postgres-8a0c2fde0c2e">Run the Parch &amp; Posey DB Locally in Postgres</a> was originally published in <a href="https://medium.com/udacity-technology-scholarship">Udacity Technology Scholarship</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[On the Bertelsmann Technology Scholarship]]></title>
            <link>https://medium.com/udacity-technology-scholarship/on-the-bertelsmann-technology-scholarship-ace4e782f432?source=rss-698a7492dbf3------2</link>
            <guid isPermaLink="false">https://medium.com/p/ace4e782f432</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[udacitybertelsmannscholar]]></category>
            <category><![CDATA[learning]]></category>
            <category><![CDATA[udacity-nanodegree]]></category>
            <category><![CDATA[udacity]]></category>
            <dc:creator><![CDATA[gauravinthevalley]]></dc:creator>
            <pubDate>Tue, 28 Jan 2020 08:55:50 GMT</pubDate>
            <atom:updated>2020-01-29T09:13:00.251Z</atom:updated>
            <content:encoded><![CDATA[<p>This past November, I got accepted into the Bertelsmann Technology Scholarship offered by<a href="http://udacity.com/"> Udacity</a>. You can learn more about the program and how it works<a href="https://www.udacity.com/bertelsmann-tech-scholarships"> here.</a> I had applied to the Data Track program because of my interest in making sense of the unimaginable amounts of data that is being collected in a multitude of industries across the world today. How does one go about learning how to make sense of the data, in the right way?</p><p>I’d taken a couple of MOOCs in <a href="http://edx.com/">edX</a> in the past — the quality of the courses there were top-notch, and I came out having learnt some <a href="https://www.r-project.org/about.html">R</a>. I’d browsed through a bunch of tutorials in the Internet — some were pretty good and I’d be lying if I said I didn’t learn something there. By this point, I had some knowledge, yes, but also a desperate longing for a course that would teach me the solid fundamentals of Data Analysis.</p><p>I had viewed a few videos on YouTube to see if I could get a learning plan going. Heck, I’d even ordered a book to see if I could get something going. That ‘something’, it turns out, is momentum. Tightly tethered to momentum — I was about to discover — is a strong feeling of you belonging with like-minded people heading down the same road as you are, cheering each other on. There’s a sense of working together, helping each other out, motivating one another, and celebrating learning — every little nugget of knowledge that you can soak in — whatever it takes, to see each other beyond the finish line. And that, is the essence of what I’ve experienced after I was accepted to the Bertelsmann Technology Scholarship, signed up, and started with the program.</p><p>The lessons are excellent for a beginner in Data Analysis like me. You learn a concept and take interactive quizzes to solidify that concept even more. The difficulty builds up incrementally as you go along. If you are stuck and require help, there’s an entire community waiting to help you out and this aspect of the course is what makes taking it so exciting and fulfilling. The course starts you out with basic statistics, moves on to teaching you the fundamentals of the Python Programming language, and ends with guiding you on writing SQL queries.</p><p>The community is managed via<a href="http://slack.com/"> Slack</a>. There are separate channels by course lessons, topics, study-groups, Reddit-like AMAs, coding-practice (<a href="http://rs.com/collections/bertelsmann">this</a> is a popular one and has a Slack channel of its own), and so on. One channel that’s helped me build the momentum I talk about above is #60daysofudacity. You are encouraged to study (anything related to the subject matter) for at least 30 minutes every day and you post your progress to this channel. I have to say: I was skeptical at first. I mean, isn’t one of the core concepts of a MOOC something along the lines of you being able to ‘learn at your own pace’? With the kind of demanding schedule I must negotiate with every day, this would surely be impossible, right?</p><p>Not so right. I made a commitment and stuck to it. Turns out, studying for at least 30 minutes every day is no different from working out for 30 minutes everyday. It’s a habit you take the time and effort to build — for your own benefit; and in this case, for the benefit of others as well; see, you get to help out people who are perhaps stuck on the same problem as you did in the past but you have since figured it out and moved on along. After a few days, you <em>want </em>to study for 30 minutes at least once every day. And when you don’t — I took a break after Day 36 and wondered hiking up the <a href="https://en.wikipedia.org/wiki/Shivapuri_Nagarjun_National_Park">Shivapuri</a> hill in the Kathmandu valley which lesson I’d be on studying had I not taken a break that day — you find yourself thinking about it.</p><p>Whether or not I get into the 2nd phase (yes, there’s a 2nd phase of this course where you’re awarded with the coveted Nanodegree provided you complete the 2nd phase in its entirety), this habit of studying everyday that I now have built up — thanks to Udacity — will have been the biggest takeaway for me. Not to mention, some Data Analysis chops I think I’m in the process of acquiring.</p><p>This post has gotten longer than I intended it to be. Now, if you’ll excuse me, I have to go and import a bunch of CSVs into Postgres so that I can practice some ‘manic SQL queries’ (I’ve taken the liberty of borrowing this phrase from the SQL part of the course) locally on my machine. That’s my plan for today — Day 39, and it will take a lot longer than just 30 minutes.</p><p>Happy learning!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ace4e782f432" width="1" height="1" alt=""><hr><p><a href="https://medium.com/udacity-technology-scholarship/on-the-bertelsmann-technology-scholarship-ace4e782f432">On the Bertelsmann Technology Scholarship</a> was originally published in <a href="https://medium.com/udacity-technology-scholarship">Udacity Technology Scholarship</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>