Getting Started with SQL

Hopefully, the inaugural column convinced you that SQL is a skill you want to have under your belt. In case you missed it, here's a quick synopsis: SQL is a database manipulation language, pure and simple. It is a rich language -- it has a number of commands for managing the database structure itself, powerful functions, many data types, and other useful features -- but at its most basic level, SQL is simply about three things:

adding data,

finding data, and

changing data.

Nothing too mind-bending there! Of course there is plenty of power under the hood for gurus, but even a newbie can get an enormous amount of work accomplished with a few simple commands. So where to begin...? A good first step is choosing a SQL environment.

Database tools

Before we can talk about SQL commands, we need an environment that understands SQL commands -- basically, we need a relational database management system, or RDBMS. And you thought I was going to say we need a database! Well, we do need a database -- but databases (files or filesystems) are created by RDBMS tools like Oracle, MySQL, and all the other programs that you probably call a "database." An RDBMS provides an environment that can be used to create and manipulate databases as well as the tools to manipulate the data in the database. One ground rule for aboutSQL -- when I say database, I always mean a relational database. For the record, there are other models like network, hierarchical, and object, to name a few, but SQL is all about relational databases.

Almost all RDBMS tools implement some version of the SQL standard and, for our purposes, should be interchangeable. You already know the names of a lot of them (e.g. IBM DB2, Sybase). For our purposes I'll assume you're using some sort of personal database, as opposed to your production database server. In any case, the details shouldn't matter too much. But if you're looking for an RDBMS to use as a learning environment, here are the ones that I'd suggest, in order of preference:

Microsoft Access -- Turn off the flamethrower! Yes, it's Microsoft; and yes, it's a toy database. But Access is cheap, probably on your desktop if you're a Windows user, and easy to install and use. There's no shame in learning SQL with Access ... just don't use it as a production database!

MySQL -- This is one of the most popular open source (GPL) database tools there is. It is also a natural companion to PHP for web-enabled database sites. You've got no excuse for not giving this one a chance.

PostgreSQL -- Probably the most sophisticated open source SQL database there is. It may be a bit intimidating, but it promises to be one serious database contender in the real world production environment. (We'll be focusing specifically on PostgreSQL in a future article)

Oracle -- It looks pricey, but it is also one of the industry standards for the database community. Plus there's a cheaper Personal Edition as well as the developer license for Oracle development partners. Neither is free, but knowing Oracle will pay for itself many times over in the commercial world.

Once you have one of these installed, you're almost ready to try your hand at SQL. But first I need to make sure you understand what I'm talking about when I start throwing around unfamiliar words. We'll start with the database basics.

Database terms

Relational databases are pretty easy to understand if you think about each one as a set of one or more tables of data. In fact, a table is exactly the term used to describe a collection of data in a database. The table below is a representation of a simple database of musical recordings.

ID

Title

Artist

Year

1

Pet Sounds

The Beach Boys

1966

2

Security

Peter Gabriel

1990

3

The Way it Is

Bruce Hornsby

1986

4

Joshua Judges Ruth

Lyle Lovett

1992

The collection of data is a table that could be one of many related tables in a single database. The horizontal green row is typically called a record in the database. The columns of data, such as the years (in bold) are typically called fields. So '1996' is the value of the Year field of record number 1 in this database. There's plenty of other terminology to introduce, but that should do for now.

Next steps

In the next series of columns, we'll start with the most important SQL command of all -- SELECT. In the meantime, get a database installed and be ready to start experimenting next week as you learn aboutSQL.