Category Software Related Stories Handling Data with SQL -- 11/17/98 Next: FIGURE 1: CREATE TABLE Syntax

Continued from Static and Dynamic SQL

Now we're ready to begin writing some SQL code. If you have access to a SQL database and would like to try the examples as we go along, make sure to log on as a user with full privileges and to use an interactive SQL facility. (If your SQL database is on a network, you'll have to talk to your database administrator about getting privileges.) If you don't have access to a SQL database, don't worry: the examples are easy to follow. You don't need to try them out to understand what's going on.

The only way to make anything happen in SQL is to execute a SQL statement. There are several types of SQL statements, but they generally fall into three categories: data definition language (DDL) statements, data manipulation language (DML) statements, and data control statements. In a way, SQL is like three languages rolled into one.

Data Definition Language statements

Let's start with one of the fundamental DDL statements in SQL: the CREATE TABLE statement. There are several types of tables in SQL, but the two basic types are base tables and viewed tables (or views). Base tables are actual tables, and views are "virtual" tables; they're derived from base tables but appear as actual tables to the user. The CREATE TABLE statement is used to create base tables.

The CREATE TABLE statement requires a table name and a list of columns along with their corresponding data types. There are several optional elements that you can also include in the CREATE statement, but for the moment, let's just deal with the required ones. The basic syntax is as follows:

CREATE TABLE table ( column  datatype ) ;

CREATE and TABLE are SQL keywords; table, column, and datatype are placeholders for values that you supply. Notice that column and datatype are enclosed in parentheses. Generally in SQL, parentheses are used to group items together. Here, they are required to enclose the list of column definitions. The semicolon at the end is a statement separator. Use it at the end of each SQL statement.

Let's try an example. Suppose you want to keep track of all of your appointments in an appointment table. You could create such a table in SQL by entering the following statement:

CREATE TABLE APPOINTMENTS ( APPOINTMENT_DATE  DATE ) ;

Executing this statement creates a table named APPOINTMENTS consisting of a single column named APPOINTMENT_DATE with a data type of DATE. At this point, there are no rows, since the table is empty. (The CREATE TABLE statement only defines tables; the INSERT statement, discussed later, populates them.)

APPOINTMENTS and APPOINTMENT_DATE are called identifiers in SQL, because they identify specific database objects, in this case a particular table and column, respectively. There are two types of identifiers in SQL: regular and delimited. Delimited identifiers are enclosed in double quotation marks and are case sensitive; regular identifiers are neither delimited nor case sensitive. In this article, we'll use only regular identifiers.

Identifiers must conform to certain rules. Regular identifiers can contain letters of the alphabet (any alphabet, not just Latin), numeric digits, and underscores. (If you're familiar with Japanese Hiragana syllabary or Chinese, you can also include syllables and ideographs.) Identifiers can't contain any punctuation, spaces, or special characters (such as #, @, %, or !), nor can they begin with a digit or an underscore. Although it's possible to use some SQL keywords as identifiers, common sense should tell you not to try this. Also, because an identifier identifies something, it has to be unique within its namespace: You can't create a table in a database if a table of that name already exists, and you can't have two columns of the same name within a table. Remember that to SQL, APPOINTMENTS and Appointments are the same. Varying the case won't make a regular identifier unique.

Technically, although you need only one column to create a table, to be at all useful most tables have more than one column. Let's change our syntax diagram slightly to reflect the optional columns:

CREATE TABLE table (
     column  datatype
     [ { , column datatype } ] ) ;

We used square brackets to denote optional elements and curly braces to denote an element that can be repeated any number of times. (You wouldn't include these special characters in the actual SQL statement.) The syntax diagram now shows that we can include any number of columns. Notice the comma before the second column placeholder. Whenever you specify a list of items in SQL, you have to separate the items with commas.

Since our single-column APPOINTMENTS table isn't very realistic, let's create a more useful one. (We'll have to give it a different name, though, because we already have an APPOINTMENTS table in the database.)

CREATE TABLE APPOINTMENTS2 (
     APPOINTMENT_DATE  DATE ,
     APPOINTMENT_TIME  TIME,
     DESCRIPTION  VARCHAR ( 256 ) ) ;

Here we've defined a table called APPOINTMENTS2. As in our first example, it has a column called APPOINTMENT_DATE, to record the date of the appointment, and APPOINTMENT_TIME, to record the time. DESCRIPTION is a variable-length string of text of up to 256 characters. We used the VARCHAR (short for CHARACTER VARYING) data type, because we weren't sure how much space we'd need, but we knew we didn't want more than 256 characters. Character strings as well as some other data types take length assignments. These assignments have to be enclosed in parentheses to the right of the data type.

You may have noticed that we changed the spacing in our two examples. The first time, the entire statement was written in one continuous string, but the second time, we broke the line after the opening parenthesis and moved each column definition to a new line. There's no rule in SQL that says we had to do that. Listing columns in a stack just improves readability. SQL is a free-form language to some extent. You can break up the lines or add indentation and spaces however you see fit. Use whatever spacing is easiest to read and understand.

Now that you have the basic form under your belt, let's create a more complex multicolumn table. On the previous page there is a table called EMPLOYEES, which has columns for last name, first name, date-of-hire, branch office, salary grade level, and annual salary. To define this table, enter the following SQL statement:

CREATE TABLE EMPLOYEES (
     LAST_NAME  CHARACTER ( 13 )  NOT NULL,
     FIRST_NAME  CHARACTER ( 10 ) NOT NULL,
     HIRE_DATE  DATE ,
     BRANCH_OFFICE  CHARACTER ( 15 ) ,
     GRADE_LEVEL  SMALLINT ,
     SALARY  DECIMAL ( 9 , 2 )  ) ;

As you can see, we've introduced several new elements.

First off, the NOT NULL clauses at the end of the column definitions for LAST_NAME and FIRST_NAME are examples of a constraint. A constraint sets requirements that have to be met. In this case, we're telling the system that LAST_NAME and FIRST_NAME must have values when data is entered into the table; these columns can't be left blank. (Which makes sense, if you think about it. How could you identify an employee without a name?)

Also, our example shows three new data types: CHARACTER, SMALLINT, and DECIMAL. We haven't said much about data types up until now. Although SQL unfortunately doesn't support relational domains, it does support a set of basic data types. It uses them to allocate storage, constrain comparisons, and restrict data entry to some extent, but it doesn't have true type-checking the way other languages do.

SQL data types fall into six categories: character strings, exact numerics, approximate numerics, bit strings, datetimes, and intervals. We've listed all the categories here for completeness, even though we won't be discussing them all in this article. (Bit strings won't mean much to nonprogrammers.)

By the way, if you're wondering whether datetime is a typo, it isn't. It's the term the SQL standard uses to categorize most temporal data types. (Intervals are also temporal, but they've been given their own category.) You've already been introduced to two of the datetime data types, DATE and TIME, in our previous example.

Another data type you've already encountered, CHARACTER VARYING (or VARCHAR for short), is in the character string category. One of our new data types, CHARACTER or CHAR, is also a character string. But unlike VARCHAR, which is of varying length, CHAR is a fixed-length data type. LAST_NAME will always be 13 characters long, whether the employee's name is Poe or Pennworth-Chickering. (In Poe's case, the remaining 10 characters would be padded by blanks.)

From the user's perspective, VARCHAR and CHAR look the same, so why have two types? The reasons mainly have to do with storage requirements and performance. Generally, fixed-length character strings provide slightly faster access time, but unusually long ones waste space. In our APPOINTMENT2 example, it wouldn't make much sense to set aside 256 characters for every appointment's description; most entries wouldn't require that much space. A name, on the other hand, also varies in length, but is usually 13 characters or fewer, so any lost space would be negligible. A good rule of thumb is, if you know that the length of a text field is likely to be uniform or relatively short, use CHAR; otherwise, use VARCHAR.

Our other new data types, SMALLINT and DECIMAL, are examples of exact numeric data types. SMALLINT is short for small integer. SQL also provides an INTEGER data type. Again, the reason for having two types has to do with storage. In our example, we knew that the GRADE_LEVEL column would never be larger than two digits, so SMALLINT was appropriate, but sometimes you don't know how large the values will be. When in doubt, use INTEGER. The actual precision and storage of SMALLINT and INTEGER will vary depending on the system you're using.

The DECIMAL data type, usually used for monetary values, lets you specify amounts with a fixed number of decimal places. Because it is an exact numeric data type, you get back exact results when using it in mathematical operations involving decimals. (The approximate numeric data types such as FLOAT [floating point number] have rounding errors in their decimal values, so they're not appropriate for financial applications.) To define a DECIMAL data type, use the form shown in our example:

DECIMAL(p,d)

The p stands for precision and the d stands for decimal places. Replace p with the total number of digits you'll need and d with the number of digits you want placed to the right of the decimal point.

Now look at Figure 1 to see our updated CREATE TABLE syntax diagram, which reflects these new elements and shows the format for all the data types we've talked about so far. (There are other data types, but these are enough to get you going.)

SQL syntax diagrams can get complicated fast, but since you've looked at some examples, this one shouldn't be too hard to understand. We've added one more symbol to our notation--a vertical bar used to separate alternatives. In effect, we're saying that for each column definition you have a choice of data types. You have to choose one data type for each column. (As before, anything enclosed in square brackets is optional and anything enclosed in curly braces can be repeated any number of times. Except for parentheses, you would not include these special characters in actual SQL statements.) To save space, we've given the full data type names in the first set of alternatives and abbreviated names in the second set, but you can use them interchangeably.

That's all we have room for in this issue. In Part 2, we'll cover the Data Manipulation Language (DML) statements INSERT, SELECT, UPDATE, and DELETE. Along the way, we'll introduce you to selection predicates, comparison and logical operators, nulls, and three-valued logic.

Next: FIGURE 1: CREATE TABLE Syntax

Published as PC Tech Feature in the 11/3/98 issue of PC Magazine.

Elsewhere on ZDNet
• Learn SQL Online -- ZD University
• Business Software: Databases -- ZD Products