What's the Big Deal about SQL?

What's the Big Deal about SQL?
Pages: 1, 2

SQL in your daily life

If you've ever taken a good computer science course, you've talked about all the fundamental programming concepts -- algorithms, control constructs, and data structures, for example. But once you've created those data structures for your program, you need to store them somewhere. In some instances, especially for simple data, a straightforward sequential text file is fine. In other cases, you may implement a random-access binary file for your data records. But when your data starts to increase in size and complexity, and your application needs to be robust and fast, you'll probably wander towards the world of database tools. The advantage of choosing a database tool to manage your application data is that you get to be lazy. I know that I can't even write a good bubble sort off the top of my head, let alone some seriously efficient searching algorithm for more complex data. But if I abstract the data functionality into a database, I can simply deal with the interface to that database and rely on thousands of person-years that have been spent optimizing relational database functionality. As far as the programmer is concerned, that interface to the world of high-powered data crunching is SQL.

Imagine writing an algorithm to search through a text file of tab-delimited (columnular) data to find a particular last name (Ashenfelter) in the 3rd column of the file of authors. You'd have to deal with all sorts of hard stuff like arrays, comparison operators, recursion, and other things you'd hope to leave behind in your computer science courses or the unread computer books stacked on the bookshelf next to your computer. I mean, all you want to do is select the text "Ashenfelter" from the column that holds the last name in the data file! Well, I can rewrite that request in SQL while you write the algorithm in the language of your choice (I'd vote for Perl). Go ahead, I'll wait…

SELECT Lastname FROM Authors WHERE Lastname='Ashenfelter';

I bet I beat you, and that my code is shorter. Note the similarities between the SQL code in the example above and the English request in the previous paragraph. Basically, you've got a verb (SELECT), a subject the verb acts on, a few modifiers, and a final semi-colon. I don't have to know details of the algorithm, physical data storage methods, or even the brand of the database I'm querying - SQL hides all of that behind a simple, standard interface. That, in a nutshell, is the beauty of a standard language.

Note: All SQL statements must end with a semicolon (;), but capitalization is not required.

Most languages and application platforms provide some underlying technology to connect to a relational database, as shown in Table 2. These technologies provide the tools to open a database connection between the programming environment and the database engine, and basically allow the developer to pass SQL code into the database on that connection. Many higher-end databases also provide native call-level interfaces that can be called from programming languages like C and C++.

Table 2: Database--application technology interfaces
DBI, DBM	Perl
JDBC	Java
ODBC	Visual Basic, ColdFusion, ASP, and many others
OLE-DB	Microsoft tools (ASP, VB)

Open DataBase Connectivity (ODBC) is a standard interface for vendor-independent communication between an application and a database. ODBC provides an interface where database-specific drivers are used to translate an ODBC request into a native database call. The other tools in Table 2 basically provide the same vendor-independent layer for communicating with a database -- as long as there's a driver for a given SQL database, you can swap the databases on the back end of the application by copying the data to the new platform and installing the new driver.

In many real-world scenarios, you may be using ODBC to connect an application to a database and then using SQL to frame the query. This is especially true of web application built using tools like ColdFusion and ASP. This make SQL a great tool for all the lazy programmers, which included most good code hackers. You learn SQL once and you're golden -- the server folks install the proper ODBC drivers, the DBA manages the database and creates the structure, and you simply stick the data in there with SQL. You can develop on an NT box using ColdFusion and Access and then deploy on a multiprocessor Solaris box running Oracle simply by swapping in the appropriate ODBC driver. Ditto for Perl, or JDBC, or just about any other tool. The only caveat is that you need to make sure that your SQL is fairly cross-platform. That means no proprietary extensions, no esoteric structures or unusually complex nesting. In most cases, that won't limit you a bit.

More aboutSQL

I hope you're now convinced that SQL is the best thing since chocolate-covered expresso beans or H₂Joe. This column is dedicated to getting you into SQL and saving you development time and effort. I'll be coming at you weekly with information on SQL keywords, functions, tricks, and hacks. We'll talk about the quirks of commands in different database, and we'll occasionally explore the wider world of database applications, particularly those delivered over the web. Until then, feel free to send questions, comments, criticism, and flames to john@ashenfelter.com.

Next: In the second half of his inaugural column, John Paul Ashenfelter introduces us to several good SQL learning tools and begins to define database terms. Continue