SQL Environments

There are two general ways in which you can issue a SQL command to a database:

• Interactive SQL, in which a user types a single command and sends it immediately to the database. The result of an interactive query is a table in main memory (a virtual table). In mainframe environments, each user has one result table at a time, which is replaced each time a new query is executed; PC environments sometimes allow several. Result tables may not be legal relations—because of nulls they may have no primary key—but that is not a problem because they are not part of the database but exist only in main memory.
• Embedded SQL, in which SQL statements are placed in an application program. The interface presented to the user may be form-based or command-line based. Embedded SQL may be static, in which case the entire command is specified at the time the program is written. Alternatively, it may be dynamic, in which case the program builds the statement using user input and then submits it to the database.

The basic syntaxes of interactive SQL and the static embedded SQL are very similar. We will therefore spend the first portion of this book looking at interactive syntax and then turn to adapting and extending that syntax for embedding it in a program. Once you understand static embedded SQL syntax, you will be ready to look at preparing dynamic SQL statements for execution.

In addition to the two methods for writing SQL syntax, there are also a number of graphic query builders. These provide a way for a user who may not know the SQL language to “draw” the elements of a query. Many of these programs are report writers (for example, Crystal Reports3) and are not intended for data modification or for maintaining the structure of a database.