SQL Series...11

Building PowerDesign with SQL’s DDL

All the database definition functions you can perform by using a RAD tool, such as Access, you can also accomplish by using SQL. Instead of clicking menu choices with the mouse, you enter commands from the keyboard.

People who prefer to manipulate visual objects find the RAD tools easy to understand and use. People who are more oriented toward stringing words together into logical statements find SQL commands easier. Becoming profi-cient at using both methods is worthwhile because some things are more easily represented by using the object paradigm and others are more easily handled by using SQL.

In the following sections, I use SQL to perform the same table creation, alter-ation, and deletion operations that I used the RAD tool to perform in the first part of this article.

Using SQL with Microsoft Access

Access is designed as a rapid application development (RAD) tool that

does not require programming. You can write and execute SQL statements

in Access, but you have to use a “back door” method to do it. To open a basic

editor that you can use to enter SQL code, follow these steps:

1. Open your database and select Queries from the Objects list.

2. In the task pane on the right, select Create Query in Design view. The Show Table dialog box appears.

3. Select any table. Click the Add button and then the Close button.

The cursor blinks in the Query window that you just created, but you can ignore it.

4. From the main Access menu, choose View➪SQL View.

An editor window appears with the beginnings of an SQL SELECT statement.

5. Delete the SELECT statement and then enter the SQL statement you want.

6. When you’re finished, click the Save icon.

Access asks you for a name for the query you have just created.

7. Enter a name and then click OK.

Your statement is saved and will be executed as a query later. Unfortunately, Access doesn’t execute the full range of SQL statements. For example, it won’t execute a CREATE TABLE statement. But after your table is created, you can perform just about any manipulation of your table’s data that you want.

Creating a table

If you’re working with a full-featured DBMS — such as Microsoft SQL Server, Oracle 9i, or IBM DB2 — to create a database table with SQL, you must enter the same information that you’d enter if you created the table with a RAD tool. The difference is that the RAD tool helps you by providing a table creation dialog box (or some similar data-entry skeleton) and by preventing you from entering invalid field names, types, or sizes. SQL doesn’t give you as much help. You must know what you’re doing at the onset instead of figuring things out along the way. You must enter the entire CREATE TABLE statement before SQL even looks at it, let alone gives you any indication as to whether you made any errors in the statement.

The statement that creates a proposal-tracking table identical to the one cre-ated earlier in the article  uses the following syntax:

CREATE TABLE PowerSQL (

ProposalNumber                  SMALLINT,

FirstName                            CHAR (15),

LastName                             CHAR (20),

Address                                CHAR (30),

City                                      CHAR (25),

StateProvince                       CHAR (2),

PostalCode                           CHAR (10),

Country                                CHAR (30),

Phone                                   CHAR (14),

HowKnown                         CHAR (30),

Proposal                               CHAR (50),

BusinOrCharity                    CHAR (1) );

The information in the SQL statement is essentially the same as what you enter into the RAD tool (discussed earlier in this article). Which method you use is largely a matter of personal preference. The nice thing about SQL is that the language is universal. The same standard syntax works regardless of what database management system you use.

Becoming proficient in SQL has long-term payoffs because it will be around for a long time. The effort you put into becoming an expert in a particular development tool is likely to yield a lower return on investment. No matter how wonderful the latest RAD tool may be, it will be superseded by newer technology within three to five years. If you can recover your investment in the tool in that time, great! Use it. If not, you may be wise to stick with the tried and true. Train your people in SQL, and your training investment will pay dividends over a much longer period.

Creating an index

Indexes are an important part of any relational database. They serve as point-ers into the tables that contain the data of interest. By using an index, you can go directly to a particular record without having to scan the table sequentially, one record at a time, to find that record. For really large tables, indexes are a necessity; without indexes, you may need to wait years rather than seconds for a result. (Well, I suppose you wouldn’t actually wait years. Some retrievals, however, may actually take that long if you let them keep running. Unless you have nothing better to do with your computer’s time, you’d probably just abort the retrieval and do without the result. Life goes on.)

Amazingly, the SQL:2003 specification doesn’t provide a means to create an index. The DBMS vendors provide their own implementations of the function. Because these implementations aren’t standardized, they may differ from one another. Most vendors provide the index creation function by adding a CREATE INDEX command to SQL. Even though two vendors may use the same words (CREATE INDEX), the way the command operates may not be the same. You’re likely to find quite a few implementation-dependent clauses. Carefully study your DBMS documentation to determine how to use that particular DBMS to create indexes.

Altering the table structure

To change the structure of an existing table, you can use SQL’s ALTER TABLE command. Interactive SQL at your client station is not as convenient as a RAD tool. The RAD tool displays your table’s structure, which you can then modify.

Using SQL, you must know in advance the table’s structure and how you want to modify it. At the screen prompt, you must enter the appropriate command to perform the alteration. If, however, you want to embed the table alteration instructions in an application program, using SQL is usually the easiest way to do so.

To add a second address field to the PowerSQL table, use the following DDL command:

ALTER TABLE PowerSQL

ADD COLUMN Address2 CHAR (30);

 

You don’t need to be an SQL guru to decipher this code. Even professed com-puter illiterates can probably figure this one out. The command alters a table with the name PowerSQL by adding a column to the table. The column is named Address2, is of the CHAR data type, and is 30 characters long. This example demonstrates how easily you can change the structure of database tables by using SQL DDL commands.

SQL:2003 provides this statement for adding a column to a table and allows you to drop an existing column in a similar manner, as in the following code:

ALTER TABLE PowerSQL

DROP COLUMN Address2;

Deleting a table

Deleting database tables that you no longer need is easy. Just use the DROP TABLE command, as follows:

DROP TABLE PowerSQL ;

What could be simpler? If you drop a table, you erase all its data and its meta-data. No vestige of the table remains.

Deleting an index

If you delete a table by issuing a DROP TABLE command, you also delete any indexes associated with that table. Sometimes, however, you may want to keep a table but remove an index from it. SQL:2003 doesn’t define a DROP INDEX command, but most implementations include that command anyway. Such a command comes in handy if your system slows to a crawl and you discover that your tables aren’t optimally indexed. Correcting an index problem can dramatically improve performance, which will delight users who’ve become accustomed to response times reminiscent of pouring molasses on a cold day in Vermont.

Portability Considerations

Any SQL implementation that you’re likely to use may have extensions that give it capabilities that the SQL:2003 specification doesn’t cover. Some of these features will likely appear in the next release of the SQL specification. Others are unique to a particular implementation and probably destined to stay that way.

Often, these extensions make creating an application that meets your needs easier, and you’ll find yourself tempted to use them. Using the extensions may be your best course, but if you do, be aware of the trade-offs. If you ever want to migrate your application to another SQL implementation, you may need to rewrite those sections in which you used extensions that your new environ-ment doesn’t support. Think about the probability of such a migration in the future and also about whether the extension you’re considering is unique to your implementation or fairly widespread. Forgoing use of an extension may be better in the long run, even if its use saves you some time. On the other hand, you may find no reason not to use the extension. Consider each case carefully. The more you know about existing implementations and develop-ment trends, the better the decisions you’ll make.