Interactive SQL

Interactive SQL consists of entering SQL statements into a database management system such as SQL Server, Oracle, or DB2. The DBMS then performs the commands specified by the statements. You could build a database from scratch this way, starting with a CREATE DATABASE statement, and building everything from there. You could fill it with data, and then type queries to selectively pull information out of it.

Although it’s possible to do everything you need to do to a database with interactive SQL, this approach has a couple of disadvantages:

• It can get awfully tedious to enter everything in the form of SQL statements from the keyboard.
• Only people fluent in the SQL language can operate on the database, and most people have never even heard of SQL, let alone are able to use it effectively.

SQL is the only language that most relational databases understand, so there is no getting around using it. However, the people who interact with databases the most — those folks that ask questions of the data — do not need to be exposed to naked SQL. They can be protected from that intimidating prospect by wrapping the SQL in a blanket of code written in another language. With that other language, a programmer can generate screens, forms, menus, and other familiar objects for the user to interact with. Ultimately, those things translate the user’s actions to SQL code that the DBMS understands. The desired information is retrieved, and the user sees the result.

Challenges to combining SQL with a host language

SQL has these fundamental differences from host languages that you might want to combine it with:

• SQL is nonprocedural. One basic feature of all common host languages is that they are procedural, meaning that programs written in those languages execute procedures in a step-by-step fashion. They deal with data the same way, one row at a time. Because SQL is nonprocedural, it does whatever it is going to do all at once and deals with data a set of rows at a time. Procedural programmers coming to SQL for the first time need to adjust their thinking in order to use SQL effectively as a data manipulation and retrieval tool.
• SQL recognizes different data types than does whatever host language you are using with it. Because there are a large number of languages out there that could serve as host languages for SQL, and the data types of any one of them do not necessarily agree with the data types of any other, the committee that created the ANSI/ISO standard defined the data types for SQL that they thought would be most useful, without referring to the data types recognized by any of the potential host languages. This data type incompatibility presents a problem if you want to perform calculations with your host language on data that was retrieved from a database with SQL. The problem is not serious; you just need to be aware of it. (It helps that SQL provides the CAST statement for translating one data type into another.)

Embedded SQL

Until recently, the most common form of SQL has been embedded SQL. This method uses a general-purpose computer language such as C, C++, or COBOL to write the bulk of an application. Such languages are great for creating an application’s user interface. They can create forms with buttons and menus, format reports, perform calculations, and basically do all the things that SQL cannot do. In a database application, however, sooner or later, the database must be accessed. That’s a job for SQL.

It makes sense to write the application in a host language and, when needed, drop in SQL statements to interact with the data. It is the best of both worlds. The host language does what it’s best at, and the embedded SQL does what it’s best at. The only downside to the cooperative arrangement is that the host language compiler will not recognize the SQL code when it encounters it and will issue an error message. To avoid this problem, a precompiler processes the SQL before the host language compiler takes over. When everything works, this is a great arrangement. Before everything works, however, debugging can be tough because a host language debugger doesn’t know how to handle any SQL that it encounters. Nevertheless, embedded SQL remains the most popular way to create database applications.

For example, look at a fragment of C code that contains embedded SQL statements. This particular fragment is written in Oracle’s Pro*C dialect of the C language and is code that might be found in an organization’s human resources department. This particular code block is designed to authenticate and log on a user, and then enable the user to change the salary and commission information for an employee.

EXEC SQL BEGIN DECLARE SECTION;

VARCHAR uid[20];

VARCHAR pwd[20];

VARCHAR ename[10];

FLOAT salary, comm;

SHORT salary_ind, comm_ind;

EXEC SQL END DECLARE SECTION;

main()

{

int sret; /* scanf return code */

/* Log in */

strcpy(uid.arr,"Mary"); /* copy the user name */

uid.len=strlen(uid.arr);

strcpy(pwd.arr,"Bennett"); /* copy the password */

pwd.len=strlen(pwd.arr);

EXEC SQL WHENEVER SQLERROR STOP;

EXEC SQL WHENEVER NOT FOUND STOP;

EXEC SQL CONNECT :uid;

printf("Connected to user: percents ",uid.arr);

printf("Enter employee name to update: ");

scanf("percents",ename.arr);

ename.len=strlen(ename.arr);

EXEC SQL SELECT SALARY,COMM INTO :salary,:comm

FROM EMPLOY

WHERE ENAME=:ename;

printf("Employee: percents salary: percent6.2f

comm: percent6.2f ", ename.arr, salary, comm);

printf("Enter new salary: ");

sret=scanf("percentf",&salary);

salary_ind = 0;

if (sret == EOF !! sret == 0) /* set indicator */

salary_ind =-1; /* Set indicator for NULL */

printf("Enter new commission: ");

sret=scanf("percentf",&comm);

comm_ind = 0; /* set indicator */

if (sret == EOF !! sret == 0)

comm_ind=-1; /* Set indicator for NULL */

EXEC SQL UPDATE EMPLOY

SET SALARY=:salary:salary_ind

SET COMM=:comm:comm_ind

WHERE ENAME=:ename;

printf("Employee percents updated. ",ename.arr);

EXEC SQL COMMIT WORK;

exit(0);

}

Here’s a closer look at what the code does:

• First comes an SQL declaration section, where variables are declared.
• Next, C code accepts a username and password.
• A couple of SQL error traps follow, and then a connection to the database is established. (If an SQL error code or Not Found code is returned from the database, the run is aborted before it begins.)
• C code prints out some messages and accepts the name of the employee whose record will be changed.
• SQL retrieves that employee’s salary and commission data.
• C displays the salary and commission data and solicits new salary and commission data.
• SQL updates the database with the new data.
• C displays a successful completion message.
• SQL commits the transaction.
• C terminates the program.

In this implementation, every SQL statement is introduced with an EXEC SQL directive. This is a clue to the compiler not to try to compile what follows, but instead to pass it directly to the DBMS’s database engine.

Some implementations have deprecated embedded SQL or discontinued it entirely. For example, embedded SQL was deprecated in SQL Server 2008, meaning it was still present, but may not be in a subsequent version. Software vendors recommend that deprecated features not be included in new development efforts. Embedded SQL is now absent from MySQL and SAP SQL Anywhere, although an independently developed preprocessor is available for MySQL.

Module language

Module language is similar to embedded SQL in that it combines the strengths of SQL with those of a host language. However, it does it in a slightly different way. All the SQL code is stored — as procedures — in a module separate from the host language program. Whenever the host language program needs to perform a database operation, it calls a procedure from the SQL module to do the job. With this arrangement, all your SQL is kept out of the main program, so the host language compiler has no problem, and neither does the debugger. All they see is host language code, including the procedure calls. The procedures themselves cause no difficulty because they are in a separate module, and the compiler and debugger just skip over them.

Another advantage of module language over embedded SQL is that the SQL code is separated from the host language code. Because high skill in both SQL and any given host language is rare, it is difficult to find good people to program embedded SQL applications. Because a module language implementation separates the languages, you can hire the best SQL programmer to write the SQL, and the best host language programmer to write the host language code. Neither one has to be an expert in the other language.

To see how this would work, check out the following module definition, which shows you the syntax you’d use to create a module that contains SQL procedures:

MODULE [module-name]

[NAMES ARE character-set-name]

LANGUAGE {ADA|C|COBOL|FORTRAN|MUMPS|PASCAL|PLI|SQL}

[SCHEMA schema-name]

[AUTHORIZATION authorization-id]

[temporary-table-declarations…]

[cursor-declarations…]

[dynamic-cursor-declarations…]

procedures…

The MODULE declaration is mandatory, but the module name is not. (It’s a good idea to name your modules anyway, just to reduce the confusion.) With the optional NAMES ARE clause, you can specify a character set — Hebrew, for example, or Cyrillic. The default character set will be used if you don’t include a NAMES ARE clause.

The next line lets you specify a host language — something you definitely have to do. Each language has different expectations about what the procedure will look like, so the LANGUAGE clause determines the format of the procedures in the module.

Although the SCHEMA clause and the AUTHORIZATION clause are both optional, you must specify at least one of them. The AUTHORIZATION clause is a security feature. If your authorization ID does not carry sufficient privileges, you won’t be allowed to use the procedures in the module.

If any of the procedures use temporary tables, cursors, or dynamic cursors, they must be declared before they are used. I talk about cursors in Book 3, Chapter 5.

Exact numerics

Because computers store numbers in registers of finite size, there is a limit to how large or small a number can be and still be represented exactly. There is a range of numbers centered on zero that can be represented exactly. The size of that range depends on the size of the registers that the numbers are stored in. Thus a machine with 64-bit registers can exactly represent a range of numbers that is wider than the range that can be exactly represented on a machine with 32-bit registers.

After doing all the complex math, you’re left with six standard exact numeric data types. They are

• INTEGER
• SMALLINT
• BIGINT
• NUMERIC
• DECIMAL
• DECFLOAT

The next few sections drill down deeper into each type.

Approximate numerics

The approximate numeric types (all three of them) exist so that you can represent numbers either too large or too small to be represented by an exact numeric type. If, for example, a system has 32-bit registers, then the largest number that can be represented with an exact numeric type is the largest number that can be represented with 32 binary digits — which happens to be 4,294,967,295 in decimal. If you have to deal with numbers larger than that, you must move to approximate numerics or buy a computer with 64-bit registers. Using approximate numerics may not be much of a hardship: For most applications, after you get above four billion, approximations are good enough.

Similarly, values very close to zero cannot be represented with exact numerics either. The smallest number that can be represented exactly on a 32-bit machine has a one in the least significant bit position and zeros everywhere else. This is a very small number, but there are a lot of numbers of interest, particularly in science, that are smaller. For such numbers, you must also rely on approximate numerics.

With that intro out of the way, it’s time to meet the three approximate numeric types: REAL, DOUBLE PRECISION, and FLOAT.

Character strings

After numbers, the next most common thing to be stored is strings of alphanumeric characters. SQL provides several character string types, each with somewhat different characteristics from the others. The three main types are CHARACTER, CHARACTER VARYING, and CHARACTER LARGE OBJECT. These three types are mirrored by NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT, which deal with character sets other than the default character set, which is usually the character set of the English language.

Binary strings

The various binary string data types were added to SQL:2008. Binary strings are like character strings except that the only characters allowed are 1 and 0. There are three different types of binary strings, BINARY, BINARY VARYING, and BINARY LARGE OBJECT.

Booleans

A column of the BOOLEAN data type, named after nineteenth-century English mathematician George Boole, will accept any one of three values: TRUE, FALSE, and UNKNOWN. The fact that SQL entertains the possibility of NULL values expands the traditional restriction of Boolean values from just TRUE and FALSE to TRUE, FALSE, and UNKNOWN. If a Boolean TRUE or FALSE value is compared to a NULL value, the result is UNKNOWN. Of course, comparing a Boolean UNKNOWN value to any value also gives an UNKNOWN result.

Datetimes

You often need to store either dates, times, or both, in addition to numeric and character data. ISO/IEC standard SQL defines five datetime types. Because considerable overlap exists among the five types, not all implementations of SQL include all five types. This could cause problems if you try to migrate a database from a platform that uses one subset of the five types to a platform that uses a different subset. There is not much you can do about this except deal with it when the issue arises.

Intervals

An interval is the difference between two dates, two times, or two datetimes. There are two different kinds of intervals, the year-month interval and the day-hour-minute-second interval. A day always has 24 hours. An hour always has 60 minutes. A minute always has 60 seconds. However, a month may have 28, 29, 30, or 31 days. Because of that variability, you cannot mix the two kinds of intervals. A field of the INTERVAL type can store the difference in time between two instants in the same month, but cannot store an interval such as 2 years, 7 months, 13 days, 5 hours, 6 minutes, and 45 seconds.

XML type

The SQL/XML:2003 update to the ISO/IEC SQL standard introduced the XML data type. Values in the XML type are XML values, meaning you can now manage and query XML data in an SQL database.

With SQL/XML:2006, folks moved to the XQuery Data Model, which means that any XML value is also an XQuery sequence. The details of the XQuery Data Model are beyond the scope of this book. Refer to Querying XML, by Jim Melton and Stephen Buxton (published by Morgan Kaufmann), for detailed coverage of this topic.

With the introduction of SQL/XML:2006, three specific subtypes of the XML type were defined. They are XML(SEQUENCE), XML(CONTENT), and XML(DOCUMENT). The three subtypes are related to each other hierarchically. An XML(SEQUENCE) is any sequence of XML nodes, XML values, or both. An XML(CONTENT) is an XML(SEQUENCE) that is an XML fragment wrapped in a document node. An XML(DOCUMENT) is an XML(CONTENT) that is a well-formed XML document.

Every XML value is at least an XML(SEQUENCE). An XML(SEQUENCE) that is a document node is an XML(CONTENT). An XML(CONTENT) that has legal document children is an XML(DOCUMENT).

XML types may be associated with an XML schema. There are three possibilities:

• UNTYPED: There is no associated XML schema.
• XMLSCHEMA: There is an associated XML schema.
• ANY: There may or may not be an associated XML schema.

So a document of type XML(DOCUMENT(ANY)) may or may not have an associated XML schema. If you specify a column as being of type XML with no modifiers, it must be either XML(SEQUENCE), XML(CONTENT(ANY), or XML(CONTENT(UNTYPED)). Which of those it is depends on the implementation.

ROW type

The ROW type, introduced in the 1999 version of the ISO/IEC SQL standard (SQL:1999), represents the first break of SQL away from the relational model, as defined by its creator, Dr. E.F. Codd. With the introduction of this type, SQL databases can no longer be considered pure relational databases. One of the defining characteristics of Codd’s First Normal Form (1NF) is the fact that no field in a table row may be multivalued. Multivalued fields are exactly what the ROW type introduces. The ROW type enables you to place a whole row’s worth of data into a single field, effectively nesting a row within a row. To see how this works, create a ROW type.

Note: The normal forms constrain the structure of database tables as a defense against anomalies, which are inconsistencies in table data or even outright wrong values. 1NF is the least restrictive of the normal forms, and thus the easiest to satisfy. Notwithstanding that, a table that includes a ROW type fails the test of First Normal Form. According to Dr. Codd, such a table is not a relation, and thus cannot be present in a relational database. I give extensive coverage to normalization and the normal forms in Book 2, Chapter 2.

CREATE ROW TYPE address_type (

Street VARCHAR (25),

City VARCHAR (20),

State CHAR (2),

PostalCode VARCHAR (9)

) ;

This code effectively compresses four attributes into a single type. After you have created a ROW type — such as address_type in the preceding example — you can then use it in a table definition.

CREATE TABLE VENDOR (

VendorID INTEGER PRIMARY KEY,

VendorName VARCHAR (25),

Address address_type,

Phone VARCHAR (15)

) ;

If you have tables for multiple groups, such as vendors, employees, customers, stockholders, or prospects, you have to declare only one attribute rather than four. That may not seem like much of a savings, but you’re not limited to putting just four attributes into a ROW type. What if you had to type in the same forty attributes into a hundred tables?

The ROW type, like many other aspects of SQL that have been added relatively recently, has not yet been included into many of the most popular implementations of SQL. Even Oracle, which is one of the closest implementations to the SQL:2016 standard, does not currently support the ROW type. Instead, it supports object types, which perform a similar function.

Collection types

The introduction of ROW types in SQL:1999 was not the only break from the ironclad rules of relational database theory. In that same version of the standard, the ARRAY type was introduced, and in SQL:2003, the MULTISET type was added. Both of these collection types violate the ol’ First Normal Form (1NF) and thus take SQL databases a couple of steps further away from relational purity.

REF types

REF types are different from distinct data types such as INTEGER or CHAR. They are used in obscure circumstances by highly skilled SQL wizards, and just about nobody else. Instead of holding values, an REF type references a user-defined structured type associated with a typed table. Typed tables are beyond the scope of this book, but I mention REF type here for the sake of completeness.

REF types are not a part of core SQL. This means that database vendors can claim compliance with the SQL standard without implementing REF types.

The REF type is an aspect of the object-oriented nature of SQL since the SQL:1999 standard. If object-oriented programming seems obscure to you, as it does to many programmers of a more traditional bent, you can probably survive quite well without ever needing the REF type.

User-defined types

User-defined types (UDTs) are another addition to SQL imported from the world of object-oriented programming. If the data types that I have enumerated here are not enough for you, you can define your own data types. To do so, use the principles of abstract data types (ADTs) that are major features of such object-oriented languages as C++.

SQL is not a complete programming language, and as such must be used with a host language that is complete, such as C. One of the problems with this arrangement is that the data types of the host language often do not match the data types of SQL. User-defined types come to the rescue here. You can define a type that matches the corresponding type in the host language.

The object-oriented nature of UDTs becomes evident when you see that a UDT has attributes and methods encapsulated within it. The attribute definitions and the results of the methods are visible to the outside world, but the ways the methods are actually implemented are hidden from view. In this object-oriented world, you can declare attributes and methods to be public, private, or protected. A public attribute or method is available to anyone who uses the UDT. A private attribute or method may be used only by the UDT itself. A protected attribute or method may be used only by the UDT itself and its subtypes. (If this sounds familiar to you, don’t be surprised — an SQL UDT is much like a class in object-oriented programming.)

There are two kinds of UDTs: distinct types and structured types. The next sections take a look at each one in turn.

Data type summary

Table 6-1 summarizes the SQL data types and gives an example of each.

Column constraints

You can constrain the contents of a table column. In some cases, that means constraining what the column must contain, and in other cases, what it may not contain. There are three kinds of column constraints: the NOT NULL, UNIQUE, and CHECK constraints.

Table constraints

Sometimes a constraint applies not just to a column, but to an entire table. The PRIMARY KEY constraint is the principal example of a table constraint; it applies to an entire table.

Although a primary key may consist of a single column, it could also be made up of a combination of two or more columns. Because a primary key must be guaranteed to be unique, multiple columns may be needed if one column is not enough to guarantee uniqueness.

To see what I mean, check out the following, which shows a table with a single-column primary key:

CREATE TABLE PROSPECT (

ProspectName CHAR (30) PRIMARY KEY,

Address1 CHAR (30),

Address2 CHAR (30),

City CHAR (25),

State CHAR (2),

PostalCode CHAR (10),

Phone CHAR (13),

Fax CHAR (13)

) ;

The primary key constraint in this case is listed with the ProspectName column, but it is nonetheless a table constraint because it guarantees that the table contains no duplicate rows. By applying the primary key constraint to ProspectName, you are guaranteeing that ProspectName cannot have a null value, and no entry in the ProspectName column may duplicate another entry in the ProspectName column. Because ProspectName is guaranteed to be unique, every row in the table must be distinguishable from every other row.

ProspectName may not be a particularly good choice for a proposed primary key. Some people have rather common names— Joe Wilson or Jane Adams. It is quite possible that two people with the same name might both be prospects of your business. You could overcome that problem by using more than one column for the primary key. Here’s one way to do that:

CREATE TABLE PROSPECT (

ProspectName CHAR (30) NOT NULL,

Address1 CHAR (30) NOT NULL,

Address2 CHAR (30),

City CHAR (25),

State CHAR (2),

PostalCode CHAR (10),

Phone CHAR (13),

CONSTRAINT prospect_pk PRIMARY KEY

(ProspectName, Address1)

) ;

A composite primary key is made up of both ProspectName and Address1.

You might ask, “What if a father and son have the same name and live at the same address?” The more such scenarios you think up, the more complex things tend to get. In many cases, it’s best to make up a unique ID number for every row in a table and let that be the primary key. If you use an autoincrementer to generate the keys, you can be sure they are unique. This keeps things relatively simple. You can also program your own unique ID numbers by storing a value in memory and incrementing it by one after each time you add a new record that uses the stored value as its primary key.

CREATE TABLE PROSPECT (

ProspectID INTEGER PRIMARY KEY,

ProspectName CHAR (30),

Address1 CHAR (30),

Address2 CHAR (30),

City CHAR (25),

State CHAR (2),

PostalCode CHAR (10),

Phone CHAR (13)

) ;

Many database management systems automatically create autoincrementing primary keys for you as you enter new rows into a table.

Foreign key constraints

Relational databases are categorized as they are because the data is stored in tables that are related to each other in some way. The relationship occurs because a row in one table may be directly related to one or more rows in another table.

For example, in a retail database, the record in the CUSTOMER table for customer Lisa Mazzone is directly related to the records in the INVOICE table for purchases that Ms. Mazzone has made. To establish this relationship, one or more columns in the CUSTOMER table must have corresponding columns in the INVOICE table.

The primary key of the CUSTOMER table uniquely identifies each customer. The primary key of the INVOICE table uniquely identifies each invoice. In addition, the primary key of the CUSTOMER table acts as a foreign key in INVOICE to link the two tables. In this setup, the foreign key in each row of the INVOICE table identifies the customer who made this particular purchase. Here’s an example:

CREATE TABLE CUSTOMER (

CustomerID INTEGER PRIMARY KEY,

CustomerName CHAR (30),

Address1 CHAR (30),

Address2 CHAR (30),

City CHAR (25),

State CHAR (2),

PostalCode CHAR (10),

Phone CHAR (13)

) ;

CREATE TABLE SALESPERSON (

SalespersonID INTEGER PRIMARY KEY,

SalespersonName CHAR (30),

Address1 CHAR (30),

Address2 CHAR (30),

City CHAR (25),

State CHAR (2),

PostalCode CHAR (10),

Phone CHAR (13)

) ;

CREATE TABLE INVOICE (

InvoiceNo INTEGER PRIMARY KEY,

CustomerID INTEGER,

SalespersonID INTEGER,

CONSTRAINT customer_fk FOREIGN KEY (CustomerID)

REFERENCES CUSTOMER (CustomerID),

CONSTRAINT salesperson_fk FOREIGN KEY (SalespersonID)

REFERENCES SALESPERSON (SalespersonID)

) ;

Each invoice is related to the customer who made the purchase and the salesperson who made the sale.

Using constraints in this way is what makes relational databases relational. This is the core of the whole thing right here! How do the tables in a relational databases relate to each other? They relate by the keys they hold in common. The relationship is established, but also constrained by the fact that a column in one table has to match a corresponding column in another table. The only relationships present in a relational database are those where there is a key-to-key link mediated by a foreign key constraint.

Assertions

Sometimes a constraint may apply not just to a column or a table, but to multiple tables or even an entire database. A constraint with such broad applicability is called an assertion.

Suppose a small bookstore wants to control its exposure to dead inventory by not allowing total inventory to grow beyond 20,000 items. Suppose further that stocks of books and DVDs are maintained in different tables — the BOOKS and DVD tables. An assertion can guarantee that the maximum is not exceeded.

CREATE TABLE BOOKS (

ISBN INTEGER,

Title CHAR (50),

Quantity INTEGER ) ;

CREATE TABLE DVD (

BarCode INTEGER,

Title CHAR (50),

Quantity INTEGER ) ;

CREATE ASSERTION

CHECK ((SELECT SUM (Quantity)

FROM BOOKS)

+ (SELECT SUM (Quantity)

FROM DVD)

< 20000) ;

This assertion adds up all the books in stock, then adds up all the DVDs in stock, and finally adds those two sums together. It then checks to see that the sum of them all is less than 20,000. Whenever an attempt is made to add a book or DVD to inventory, and that addition would push total inventory to 20,000 or more, the assertion is violated and the addition is not allowed.

Most popular implementations do not support assertions. For example, SQL Server 2016, DB2, Oracle Database 18c, SAP SQL Anywhere, MySQL, and PostgreSQL do not. Assertions may become available in the future, since they are a part of SQL:2003, but it would not be wise to hold your breath until this functionality appears. Although a feature that would be nice to have, assertions are far down on the list of features to add for most DBMS vendors.