Accessing Data With SQL Part 2

In my soon to be Part 1 of this series I walk through the basics of SQL and even venture a bit into intermediate territory.  However this discussion is geared specifically at ACCESSING data AKA the R of our CRUD actions.  This is fine for those who are primarily focused on retrieving data from a database to be used for some purpose (e.g Economics, Stats, Math, Marketing and Finance denizens).  While this subset of SQL skills is well suited for this target audience it makes it difficult to "play on your own" – writing SQL queries and reviewing the results.  This experimentation is super important for gaining experience and comfort with SQL so I'm taking some time here to show this group a way to get started writing SQL queries.

More after the jump.


After considerable searching I have been unable to find a playground suitable for this task.  Some of you might recommend SQLFiddle.com however I didn't find this it all that helpful for a read only audience.  SQLFiddle requires a greater if not full understanding of the CRUD actions just to get started.  This is why I don't find it to be a practical solution in this case.

BUT!… I think I have the next best thing.  I have set up a postgresql server in Heroku's cloud service so anyone can connect and and write queries against it.  I have pre-populated the database with several tables of data so all you need to do is connect with your DBMS of choice and have a blast.

So lets talk database management software
A database manager is an application that lets you graphically navigate the database and sends your queries to the server and displays the results.  Unfortunately there aren't many options for postgreSQL so I'm going cover Navicat as they are pretty much the only game in town.  They don't offer it anymore but I believe they used to have a free lite version.  If you google around you might find a link to an older free version.

Currently they offer PostgreSQL essentials for $10 or premium essentials (connects to all their SQL servers) for $20 so its not to bad.  For starter though they offer a free 30 day trial.  Follow the link below and download either premium or PostgreSQL for your operating system then watch the video below and we'll get started.

Click here to download your version of Navicat.

Connection Settings:
  Host:   ec2-54-243-242-213.compute-1.amazonaws.com
  Database:   d9cs8el0k6r7pi
  User:   kwuvnkbetwyjcq
  Port:   5432
  Password:  p-2LoSuEvawpz-Bl1vc2OE1bxK


8:22 PM , , , , , , , , , , , , 2

2 Responses so far.

  1. Jake Feasel says:
    November 1, 2012 at 11:41 AM

    Hi Doug,
    I am wondering what you mean by "SQLFiddle requires a greater if not full understanding of the CRUD actions just to get started." I assume you mean that to create a schema populated with data, you must have some knowledge of DDL and DML (create table, insert, etc....) before you can start querying against it. While it is true that there needs to be a schema created prior to writing meaningful queries, I'd like to mention two things about that:

    1) You could easily create a starting point for your users with the schema already built. You would only have to send them the link to your custom-built schema, and they could just start writing their SELECT queries against it right away.

    2) Using my handy little "Text to DDL" tool, you don't even need to know DDL and DML to quickly mock up a schema; just paste in regularly-formatted text into the dialog and it will generate the necessary scripts for you.

    If there was some other issue you found with sqlfiddle.com, I'd be interesting in knowing about it. It seems to me that it could be a very viable candidate for your users.

    Thanks!
    Jake Feasel
    SQLFiddle.com

  2. Doug says:
    November 14, 2012 at 10:34 PM

    Thanks. If I get some time i'll make a fiddle and post the link.

Leave a Reply