Getting Started with SQL?

Many Real Studio projects require the use of a database.  In 10 years of Real Studio consulting and many, many projects both internally and for clients, I can tell you the projects that did NOT use a database simply because they are the oddities in my career.  If truth be told, even some that didn’t use a database could have been, and perhaps should have been using a database.

A lot of Real Studio developers avoid them because the term ‘database’ is scary and mysterious and brings up images of having to wrestle with huge problematic installations of MS SQL Server, PostgreSQL and MySQL.  While you might have to use them eventually, we do a lot of development on the lowly and surprisingly powerful SQLite.  It is a lightweight database that offers most of the features of the big database servers.  But for many, SQL, the language behind databases, is mysterious and arcane.  Not so!

At last weeks Real Studio Database Days training in Frankfurt, Simon Larkin of QiSQL gave a very interesting talk about SQL.  He also has a section on his website devoted to learning SQL.  His SQL School takes you through the basics of the terms and then starts introducing you to more and more complex situations.

Equally important is his Database Design section that walks you through the best ways of designing your database.  Learn about primary keys, entities, relationships and database normalization.  All in all the tutorials are very well done and if you’re just starting to use databases this is a good place to start.

Simon likes to use raw SQL, which is fine, but I’m lazy and SQL-challenged at times so I tend to use a number of SQLite utility applications.  Since each seems to have some compelling feature over the others I use the one that best fits the project.  I use Base, SQLiteManager, NaviCat, and the FireFox plugin SQLite Manager.  Of these, SQLiteManager and NaviCat can open encrypted databases and only Navicat being able to remember the encryption key.  Navicat also has a visual Query Builder that newbies to SQL might find attractive.

What SQLite tool are you using and why?

9 thoughts on “Getting Started with SQL?

  1. For Database designing, I’m mainly using SQL Editor. It handles several format, so when I design a database for a proof of concept, I can design the database graphically, generate the CREATE statements for SQLite. Make my tests with RB locally, and then, if I need to go to MySQL, I just change the database engine type in SQL Editor, make the unavoidable tweakings needed and then export the CREATE statements. And thanks to the reverse engineering function in MySQLWorkbench, I can retrieve it in this free and very useful software. For database management I’m using a self made tool in Real Studio which is basically composed of TextField, a Push button and a ListBox like the Real Studio built-in database tool.

  2. Can’t get the URL to appear, so I try this way: malcolmhardie dot com
    I hope it’ll work this time.

  3. I use Navicat. I looked at a ton of them when I first moved from VB6 to RB 2 years ago and Navicat had the best UI for me. I felt right at home almost immediately. I tried others and even purchased one other, but gave up on them, they just never got me into that “comfort zone”. And when you are monkeying around with the actual data, you have to be comfortable with the tool, or you are always wondering if you changed something you didn’t mean to when working directly on the database structure or data.

  4. Ahem, Microsoft Access. It has this nice visual query builder, which I haven’t seen yet for Mac. I do a lot of reporting with Access and this has been a life saver.

  5. Working 100% with postgresql I luck out and use the native pgadmin tool. Pgadmin has all the tools I need to edit do manual backups and restores and test sql query’s before I use them in code. If you have not looked at postgres then I suggest you do its not to hard to setup once you do it once you can do new deploments in no time.

  6. I create all my SQL manually and paste it into Base or SQLite Manager to test it if it fails to work :-). I’ve gradually built up a library of parameter driven methods that cover most eventualities.

    IMO SQLite is quite superb as a single user DB And as it supports a comprehensive subset of SQL, it is easy to write code that converts on the fly to MySQL or Oracle. I’ll check out QiSQL as I have not come across them before.

  7. I second PostgreSQL as being easy to setup in Windows & Linux and the pgAdmin tool does all one needs. When using SQLite, I just use SQLiteBrowser.

    As for MS Access, the SQL dialect it uses is so far away from PostgreSQL, SQLite & MySQL that I find it more trouble than it’s worth (disregarding some bad experiences with file corruption)

Comments are closed.