SQL Series...8

Data Manipulation Language

The DDL is the part of SQL that creates, modifies, or destroys database struc-tures; it doesn’t deal with the data. The Data Manipulation Language (DML) is the part of SQL that operates on the data. Some DML statements read like ordinary English-language sentences and are easy to understand. Because SQL gives you very fine control of data, other DML statements can be fiendishly complex. If a DML statement includes multiple expressions, clauses, predi-cates, or subqueries, understanding what that statement is trying to do can

be a challenge. After you deal with some of these statements, you may even consider switching to an easier line of work, such as brain surgery or quantum electrodynamics. Fortunately, such drastic action isn’t necessary. You can understand complex SQL statements by breaking them down into their basic components and analyzing them one chunk at a time.

The DML statements you can use are INSERT, UPDATE, DELETE, and SELECT. These statements can consist of a variety of parts, including multiple clauses. Each clause may incorporate value expressions, logical connectives, predi-cates, aggregate functions, and subqueries. You can make fine discrimina-tions among database records and extract more information from your data by including these clauses in your statements. 

Value expressions

You can use value expressions to combine two or more values. Nine different kinds of value expressions exist, corresponding to the different data types:

Numeric String Datetime Interval Boolean

User-defined Row

Collection

The Boolean, user-defined, row, and collection types were introduced with SQL:1999. Some implementations may not support them yet. If you want to use one of these data types, make sure your implementation includes it.

Numeric value expressions

To combine numeric values, use the addition (+), subtraction (-), multiplica-tion (*), and division (/) operators. The following lines are examples of numeric value expressions:

12 – 7

15/3 - 4

6 * (8 + 2)

The values in these examples are numeric literals. These values may also be column names, parameters, host variables, or subqueries — provided that those column names, parameters, host variables, or subqueries evaluate to a numeric value. The following are some examples:

SUBTOTAL + TAX + SHIPPING

6 * MILES/HOURS

:months/12

The colon in the last example signals that the following term (months) is either a parameter or a host variable.

String value expressions

String value expressions may include the concatenation operator (||). Use concatenation to join two text strings, as shown in Table 

Expression		Result
‘military ‘ || ‘intelligence’		‘military intelligence’
‘oxy’ || ‘moron’		‘oxymoron’
CITY|| ‘ ‘ ||STATE|| ‘ ‘||ZIP		A single string with city, state, and
		zip code, each separated by a single
		space

Some SQL implementations use + as the concatenation operator rather than ||.

Some implementations may include string operators other than concatena-tion, but SQL:2003 doesn’t support such operators.

Datetime and interval value expressions

Datetime value expressions deal with (surprise!) dates and times. Data of DATE, TIME, TIMESTAMP, and INTERVAL types may appear in datetime value expressions. The result of a datetime value expression is always another datetime. You can add or subtract an interval from a datetime and specify time zone information.

One example of a datetime value expression appears as follows:

DueDate + INTERVAL ‘7’ DAY

A library may use such an expression to determine when to send a late notice.

Another example, specifying a time rather than a date, appears as follows:

TIME ‘18:55:48’ AT LOCAL

The AT LOCAL keywords indicate that the time refers to the local time zone.

Interval value expressions deal with the difference (how much time passes) between one datetime and another. You have two kinds of intervals: year-month and day-time. You can’t mix the two in an expression.

As an example of an interval, say that someone returns a library book after the due date. By using an interval value expression such as that of the following example, you can calculate how many days late the book is and assess a fine accordingly:

(DateReturned - DateDue) DAY

Because an interval may be of either the year-month or the day-time variety, you need to specify which kind to use. In the preceding example, I specify DAY.

Boolean value expressions

A Boolean value expression tests the truth value of a predicate. The following is an example of a Boolean value expression:

(Class = SENIOR) IS TRUE

If this were a condition on the retrieval of rows from a student table, only rows containing the records of seniors would be retrieved. To retrieve the records of all non-seniors, you could use the following:

NOT (Class = SENIOR) IS TRUE

Alternatively, you could use:

(Class = SENIOR) IS FALSE

To retrieve all rows that have a null value in the CLASS column, use:

(Class = SENIOR) IS UNKNOWN

User-defined type value expressions

With this facility, you can define your own data types instead of having to settle for those provided by “stock” SQL. Expressions that incorporate data elements of such a user-defined type must evaluate to an element of the same type.

Row value expressions

A row value expression, not surprisingly, specifies a row value. The row value may consist of one value expression, or two or more comma-delimited value expressions. For example:

(‘Joseph Tykociner’, ‘Professor Emeritus’, 1918)

This is a row in a faculty table, showing a faculty member’s name, rank, and year of hire.

Collection value expressions

A collection value expression evaluates to an array.

Reference value expressions

A reference value expression evaluates to a value that references some other database component, such as a table column.

Predicates

Predicates are SQL equivalents of logical propositions. The following state-ment is an example of a proposition:

“The student is a senior.”

In a table containing information about students, the domain of the CLASS column may be SENIOR, JUNIOR, SOPHOMORE, FRESHMAN, or NULL. You can use the predicate CLASS = SENIOR to filter out rows for which the predicate is false, retaining only those for which the predicate is true. Sometimes, the value of a predicate in a row is unknown (NULL). In those cases, you may choose either to discard the row or to retain it. (After all, the student could be a senior.) The correct course depends on the situation.

Class = SENIOR is an example of a comparison predicate. SQL has six com-parison operators. A simple comparison predicate uses one of these opera-tors. Table 3-3 shows the comparison predicates and examples of their use.

Table	Comparison Operators and Comparison Predicates
Operator	Comparison	Expression
=	Equal to	Class = SENIOR
<>	Not equal to	Class <> SENIOR
<	Less than	Class < SENIOR

Operator	Comparison		Expression
>	Greater than		Class > SENIOR
<=	Less than or equal to		Class <= SENIOR
>=	Greater than or equal to		Class >= SENIOR

In the preceding example, only the first two entries in Table 3-3 (Class = SENIOR and Class < > SENIOR) make sense. SOPHOMORE is considered greater than SENIOR because SO comes after SE in the default collation sequence, which sorts in ascending alphabetical order. This interpretation, however, is probably not the one you want.

Logical connectives

Logical connectives enable you to build complex predicates out of simple ones. Say, for example, that you want to identify child prodigies in a database of high school students. Two propositions that could identify these students may read as follows:

“The student is a senior.”

“The student’s age is less than 14 years.”

You can use the logical connective AND to create a compound predicate that isolates the student records that you want, as in the following example:

Class = SENIOR AND Age < 14

If you use the AND connective, both component predicates must be true for the compound predicate to be true. Use the OR connective when you want the compound predicate to evaluate to true if either component predicate is true. NOT is the third logical connective. Strictly speaking, NOT doesn’t con-nect two predicates, but instead reverses the truth value of the single predi-cate to which you apply it. Take, for example, the following expression:

NOT (Class = SENIOR)

This expression is true only if Class is not equal to SENIOR.

Set functions

Sometimes, the information that you want to extract from a table doesn’t relate to individual rows but rather to sets of rows. SQL:2003 provides five set (or

aggregate) functions to deal with such situations. These functions are COUNT, MAX, MIN, SUM, and AVG. Each function performs an action that draws data from a set of rows rather than from a single row.

COUNT

The COUNT function returns the number of rows in the specified table. To count the number of precocious seniors in my example high school database, use the following statement:

SELECT COUNT (*)

FROM STUDENT

WHERE Grade = 12 AND Age < 14 ;

MAX

Use the MAX function to return the maximum value that occurs in the speci-fied column. Say that you want to find the oldest student enrolled in your school. The following statement returns the appropriate row:

SELECT FirstName, LastName, Age

FROM STUDENT

WHERE Age = (SELECT MAX(Age) FROM STUDENT);

This statement returns all students whose ages are equal to the maximum age. That is, if the age of the oldest student is 23, this statement returns the first and last names and the age of all students who are 23 years old.

This query uses a subquery. The subquery SELECT MAX(Age) FROM STUDENT is embedded within the main query. 

MIN

The MIN function works just like MAX except that MIN looks for the minimum value in the specified column rather than the maximum. To find the youngest student enrolled, you can use the following query:

SELECT FirstName, LastName, Age

FROM STUDENT

WHERE Age = (SELECT MIN(Age) FROM STUDENT);

This query returns all students whose age is equal to the age of the youngest student.

SUM

The SUM function adds up the values in a specified column. The column must be one of the numeric data types, and the value of the sum must be within the range of that type. Thus, if the column is of type SMALLINT, the sum must be

no larger than the upper limit of the SMALLINT data type. In the retail database from earlier in this article, the INVOICE table contains a record of all sales. To find the total dollar value of all sales recorded in the database, use the SUM function as follows:

SELECT SUM(TotalSale) FROM INVOICE;

AVG

The AVG function returns the average of all the values in the specified column. As does the SUM function, AVG applies only to columns with a numeric data type. To find the value of the average sale, considering all trans-actions in the database, use the AVG function like this:

SELECT AVG(TotalSale) FROM INVOICE

Nulls have no value, so if any of the rows in the TotalSale column contain null values, those rows are ignored in the computation of the value of the average sale.

Subqueries

Subqueries, as you can see in the “Set functions” section earlier in this article, are queries within a query. Anywhere you can use an expression in an SQL statement, you can also use a subquery. Subqueries are a powerful tool for relating information in one table to information in another table because you can embed a query into one table, within a query to another table. By nesting one subquery within another, you enable the access of information from two or more tables to generate a final result. When you use subqueries correctly, you can retrieve just about any information you want from a database.