Learn SQL

It’s easier than ever to learn SQL.

More precisely, it’s easier than ever to start to learn SQL. SQL is in its sixth or seventh revision as an official standard, and understanding why there are two different counts, let alone comprehending all the variations of the query language through the years, is a task few undertake. The latest of these standards is almost 4,000 pages long.

There are plenty of rewards to nibbling at SQL without having to swallow all of it, though. As we explained last month, important information is in the databases in the world, and SQL is the primary way to get at that information. If you’re not already comfortable with SQL, it’s time to become so.

Bad Old Days

SQL is now celebrating its thirtieth anniversary as a “productized” implementation. Until the mid-’90s, SQL was available only commercially, and the only effective opportunities to practice it were through on-the-job experience, along with a relative handful of college courses and commercial classes.

In 2009, in contrast, a wealth of resources that make SQL approachable is available. Here are highlights you’ll want to consider for your own education:

• Linux packages
• Vendor installers
• On-line and printed tutorials

Packages for Linux

Most Linux distributions now support through their package mechanisms such open-source implementations as

• PostgreSQL
• SQLite
• MySQL
• Gadfly

While these packages are largely of high quality, working with them is generally less straighforward than installation of special-purpose applications or even most computing languages. Several subtleties complicate start-up with these packages:

First, SQL installations admit a lot of customization. Postgres, for example, requires a few steps beyond the apt-get install postgresql-8.2 or equivalent you might expect. There’s also significant skew between different revisions of a database system, so that a tutorial which correctly installs baseline configuration for release N is likely to lead only to frustration if applied to a package for release N+1.

Also, SQL is reminiscent of the Web itself, in that a minimal demonstration usually involves:

• A server application
• A client application
• Content

If you’re just starting, and run into a difficulty, even localizing the problem to one of these three elements can be daunting. There are several ways to stack the odds in you favor, though.

Vendor Release

All the leading vendors now make basic SQL systems freely available to developers under one license or another:

• IBM’s DB2 is available for no charge in its Express-C releases.
• IBM’s Informix is available at no charge for versions 7-10, and even the latest 11.5 is available for a free trial.
• Oracle makes its flagship RDBMS available for free in the 10g Express Edition, as well as earlier release.
• SQL Anywhere is available at no charge in its SQL Anywhere 10 Developer Edition.
• Sybase’s central RDBMS is Adaptive Server Enterprise (ASE). ASE is licensed at no charge for several uses.

Most of the free versions of the RDBMSs have full functionality, but limit the number of users, size of a working database, or otherwise constrain the scale of their own operations. It’s perfectly legal and feasible to develop even quite complex applications at no charge, though, and certainly to practice your RDBMS skills without ever paying for a license. The vendors–including others not listed above–encourage their developer communities, and provide a wealth of material to promote success with their products.

At the same time, the vendors have to contend with the complexity already mentioned above. Even the simplest default installations demand decisions about 32- vs. 64-bit, character encoding, security, and other abstrusities that are neither germane nor helpful for the beginner. A naive newcomer is often left wondering what the difference is between the name of a database, and the filename of the database, for example.

It can be simpler, though! MySQL, in particular, boasts a large userbase of relatively unsophisticated computer consumers whom it cultivates with simple tutorials and powerful point-and-click managers. SQLite goes in a slightly different direction, as a technically “lightweight” implementation. A standard installation of SQLite, for example, doesn’t separate client and server applications, and its relation to the native filesystem is easier to understand than that of any other SQL implementation. If you install SQLite or MySQL, you’ll likely find that you can get basic example databases to do what you want.

Written Material

Write-ups from which you can learn SQL are far richer and simply better than they were decades ago. While we still sometimes come across books or other explanations that are wrong and misleading, many good ones are now available. Learning SQL, SQL Hacks, and SQL Pocket Guide all just came out last month, and, for more ambitious students, we also like SQL and Relational Theory, published in January 2009.

On-line tutorials of SQL are more numerous than we’ve made the time to index. Among them all, though, one stands out: SQLzoo (at left) is not only accurate and detailed, with specific information about eight leading relational database management systems, but its examples are live! You don’t have to install anything to work through SQLzoo’s lessons; it hosts working databases, so that you can practice your SQL in your Web browser, and immediately receive results.

Summary

We’ll put it bluntly: there’s no excuse for a computing professional not to learn at least a little SQL. As flawed and limited as SQL might be, it is the vehicle for getting to most of the world’s structured data. Technical tour-de-forces like SQLite and SQLzoo make your own practice with SQL nearly instantaneous.