What's the Big Deal about SQL?
10/20/2000|
Related: Uploading, Saving and Downloading Binary Data in a MySQL Database  
|
Tired of not knowing what SQL is? Any serious application developer is going to need to learn some SQL at some point in their career. And once you start learning it, you'll use it. A lot.
This column is all about SQL and to get things started, this time we'll be focusing on what SQL is and why you'd want to use it. Trust me, it'll be fun.
Once upon a time...
Back in the old days, the 1970s, computers were expensive but crucial ways to process data. Banks, large companies, and governments had the need to not only store but manipulate huge stores of information. Of course IBM was the prime source of all things computer at that time, and a research group in San Jose came up with a new database paradigm based on relational calculus. System R was a giant step forward because it focused on the relationships between data instead of efficient tape storage or pure sequential data dumps. Relational databases may seem passà in the era of object-oriented programming, but at the time it was a giant leap forward.
Part of the development of System R was a non-procedural, roughly English-like language for structuring database queries. This language was dubbed Structured English Query Language, or SEQUEL. Other vendors saw the power in relational databases as a tool, so Oracle and other companies released their own relational databases that included languages based on the same principles as SEQUEL. As more relational database products were developed, it became clear that a standard was necessary, so in 1986 the so-called SQL-86 standard was adopted by the American National Standards Institute (ANSI) and early the following year by the International Standards Organization (ISO). Since 1986, three new versions of the SQL standard have been developed. SQL-89, SQL-92, and last year's SQL-99 (also called SQL3) all add additional functionality to the specification and tend to be driven by developments in the database tool community.
The SQL standard is not a simple document - over 2000 pages of hardcore technical jargon, relational calculus, logic, and standards-speak does not make compelling reading! But SQL is probably one of the few 30-year-old standards you're likely to run into in your daily programming life. Even though object-oriented databases are currently far more sexy, SQL plays a fundamental role in virtually any significant application that reads, stores, or manipulates data. Long a client-server workhorse, SQL has been leveraged by a number of web tools, ranging from Perl and Java to PHP, ColdFusion, and ASP. If you haven't run into SQL yet, you're going to. Prepare now, gentle reader -- we'll have you speaking SQL in no time!
A standard is a standard is a... standard?
All right, let's be honest right off the bat. No tool fully implements the SQL3 standard. In fact, a lot of tools don't implement the full SQL-92 standard. Or the SQL-89 standard. You get the picture. Most implement large portions of the standards and for all practical purposes, everything you're likely to need. In my mind, SQL is much more of a guideline than a strict standard. Of course ANSI and ISO would argue with me, but for all intents and purposes, SQL serves as a common glue for fundamental relational database development. SQL also is key to making database applications portable not only across operating systems, but also across database platforms as well. Plus it provides relatively easy access to a great deal of data manipulation power for folks who aren't too technical. As we'll see in future columns that delve into the arcana of SQL, it also normally reads intelligibly, unlike Perl for example, where phrases like '/a(.*)b\1c/;' have meaning to the initiated.
The SQL standard defines three major classes of entities: objects, data types, and language elements. Table 1 has a quick overview of these classes and some examples. If you've done any work with databases, most of these terms should ring bells.
Table 1: SQL Language Entities | |
Objects |
table, column, procedure, function |
Data types |
float, real, int, but, char, boolean |
Language elements |
keywords, tokens, and special characters that make up SQL ( |
So the standard provides the basic definitions for the functionality of an arbitrary database. The next step is up to the development team for a specific database tool - they decide which features to include and ignore; which features to add that aren't in the specification; and which parts of the specification they'll implement in a non-standard method. The extent of any single product's conformance with the SQL standard is basically driven by the standard programming principles of costing - time and effort required for development compared to the essential nature of that feature plays a strong role. It's easy to see why Oracle costs thousands (tens or hundreds) with its rich feature set compared to something that's free like mSQL. But it's important to note that both use standard SQL objects, data types, and language elements so that most (or even all) of your application will port between the two databases.
Pages: 1, 2 |
