Chapter6.Introducing SQL

Chapter 6. Introducing SQL

IN THIS CHAPTER

Understanding Data Sources 2

Creating A Data Source 3

Preparing to Write SQL Queries 9

Creating Queries 10

Sorting Query Results 13

Filtering Data 15

SQLpronounced "sequel" or "S-Q-L"is an acronym for Structured Query Language, a language you use to access and manipulate data in a relational database. It was designed to be easy to learn and extremely powerful, and its mass acceptance by many database vendors proves that it has succeeded in both.

In 1970, Dr. E. F. Codd, the man called the father of the relational database, described a universal language for data access. In 1974, engineers at IBM's San Jose Research Center created the Structured English Query Language, or SEQUEL, built on Codd's ideas. This language was incorporated into System R, IBM's pioneering relational database system.

Toward the end of the 1980s, two of the most important standards bodies, the American National Standards Institute (ANSI) and the International Standards Organization (ISO), published SQL standards, opening the door to mass acceptance. With these standards in place, SQL was poised to become the de facto standard used by every major database vendor.

Although SQL has evolved a great deal since its early SEQUEL days, the basic language concepts and its founding premises remain the same. The beauty of SQL is its simplicity. But don't let that simplicity deceive you. SQL is a powerful language, and it encourages you to be creative in your problem solving. You can almost always find more than one way to perform a complex query or to extract desired data. Each solution has pros and cons, and no solution is explicitly right or wrong.

Lest you panic at the thought of learning a new language, let me reassure you: SQL is easy to learn. In fact, you need to learn only four statements to be able to perform almost all the data manipulation you will need on a regular basis. Table 6.1 lists these statements.

Table 6.1. SQL-Based Data Manipulation Statements

STATEMENT	DESCRIPTION
SELECT	Queries a table for specific data.
INSERT	Adds new data to a table.
UPDATE	Updates existing data in a table.
DELETE	Removes data from a table.

Each of these statements takes one or more keywords as parameters. By combining various statements and keywords, you can manipulate your data in as many ways as you can imagine.

ColdFusion provides you with all the tools you need to add Web-based interaction to your databases. ColdFusion itself has no built-in database, however. Instead, it communicates with whatever database you select, passing updates and requests and returning query results.

TIP

This chapter (and the next) is by no means a complete SQL tutorial, so a good book on SQL is a must for ColdFusion developers. If you want a crash course on all the major SQL language elements, you might want to pick a copy of my Sams Teach Yourself SQL in 10 Minutes (ISBN: 0-672-32567-5).