SQL Language

Most databases today use Structured Query Language, commonly referred to as SQL (pronounced "see quell"). This common language enables a Visual J Plus Plus v6 program to access almost any database using the same source code. In fact, as you'll see, Visual J Plus Plus v6 programs can access different types of databases from the same .EXE file.

What is SQL, and how did it come to be?

Roots of SQL

There are many different types of databases. In earlier times, each database company defined its own command set. This made accessing databases more costly than it is now, for several reasons. Companies had to train programmers in a unique database language when they hired them, and train them again if the company moved from one type of database to another. In addition, unique internal data storage formats made moving data from one database type to another almost impossible. Database companies didn't seem to mind this state of affairs, because the use of unique languages and formats meant the cost of changing from one type of database to another was prohibitive. Companies tended to get stuck with one database type whether or not it proved to be the best one over time.

This situation might have continued, were it not for pressure from the user community to standardize. At the same time that different database suppliers were using their own unique languages, university database researchers invented an extremely powerful yet simple database command language called Structured Query Language (SQL). (The acronym SQL has become so common that the phrase "SQL language" is part of the coding vernacular, even though the word "language" is redundant.)

SQL is so simple and powerful that once the first database provider adopted it, the user community forced other database companies to follow. Most database companies that refused to adopt SQL disappeared. Microsoft supports SQL in its database tools, including Microsoft Access and Microsoft SQL Server.

Database Internal Formats

---

The use of the common SQL command set doesn't imply that different database providers have adopted a common internal format—far from it. I would wager that no two database companies use the same internal format to store data.

This lack of common internal structure is actually a benefit to the user community. No one database structure can be best at everything. Different internal formats means that database companies can optimize their offerings to serve different requirements. A database format might be optimized to minimize the amount of disk space required to store data, the effort required to maintain the database, or the time required to access the data, to name just a few possibilities. This is why Microsoft offers both Access and SQL Server.

Even with different internal formats, SQL enables companies to move both programmers and data from one database type to another with minimal effort. The programmer queries data from one database using SQL commands and stores it back into another database using the same type of SQL commands.

SQL Databases

There are many commands that make up the SQL command set. Entire databases can be created and destroyed using SQL commands. The average Visual J Plus Plus v6 user, however, will use a tool such as Microsoft Access or SQL Server to create the database. A Visual J Plus Plus program that is able to read, write, insert, and delete a record from the database is sufficient for most purposes. First, you'll need to reach a fundamental understanding of the way a SQL database is organized—at least, when viewed from the outside world. The internal details are unimportant.

Every database has a name. In most cases, the name of the database is the same as the name of the file that contains it. Thus, the Access file MyCompany.mdb contains the database MyCompany.

A database can include any number of tables, and each table carries a name. For example, the table named Customers might contain the names and phone numbers of different customers of the company MyCompany.

Each table is conceptually arranged like a grid. The columns—often referred to as fields—represent the various categories of information. The horizontal rows—often referred to as records—represent the entries in the table. For example, the following table shows a possible layout for the Access table named Customers.

You can access the entire table, but it's more common to address a single row at a time. This format also enables you to pull out and analyze individual columns within a row, or individual columns within the entire table.

SQL Commands

The simplest SQL command is the DELETE command. For example, using Access to delete the row in the Customers table containing the name Stephen Davis, you would enter the following SQL command:

DELETE FROM CUSTOMERS WHERE CONTACTFIRSTNAME = 'Stephen' AND CONTACTLASTNAME = 'Davis';

The command DELETE FROM CUSTOMERS says that we want to delete one or more rows from the Customers table. The WHERE clause specifies which row to delete. Notice that because SQL uses a space to delineate commands, Access converted the row named "Contact First Name" to ContactFirstName.

NOTE

---

SQL commands aren't case sensitive.

You can delete multiple rows with the following command:

DELETE FROM CUSTOMERS WHERE CONTACTLASTNAME = 'Davis';

In this case, every row in which the Contact Last Name value is Davis (in this example, the first two rows) is removed from the table.

What if you want to retrieve the rows of the database? For this, you need the SELECT command. The following command selects the entire Customers table:

SELECT * FROM CUSTOMERS;

What this command returns is essentially the entire Customers table, although Visual J Plus Plus v6 contains commands that can access one row returned from the SELECT command at a time.

Of course, you might not want to process every row in the table. Suppose, for example, that you wanted to single out the rows in which the last name value is Davis (undoubtedly for special recognition):

SELECT * FROM CUSTOMERS WHERE CONTACTLASTNAME = 'Davis';

Finally, suppose you don't want to select an entire row, but merely a few columns of the row. For example, suppose you wanted the customer id and the phone number for the Gibson twins and you want the results sorted in alphabetical order by the first names:

SELECT CUSTOMERID, PHONENUMBER FROM CUSTOMERS
 WHERE CONTACTLASTNAME = 'Gibson'
 ORDER BY CONTACTFIRSTNAME;

Although numerous other commands—including variations on the SELECT command—exist, this is all the SQL you'll need to know for the rest of this tutorial.

These SQL commands are great for accessing a SQL database from a command tool like Access, but what about accessing these tables from a Visual J Plus Plus program? Comments