Select Statement

The form of the Select statement supported by the flat-file drivers is:

SELECT [DISTINCT] {* | column_expression, ...}

FROM table_names [table_alias] ...

[ WHERE expr1 rel_operator expr2 ]

[ GROUP BY {column_expression, ...} ]

[ HAVING expr1 rel_operator expr2 ]

[ UNION [ALL] (SELECT...) ]

[ ORDER BY {sort_expression [DESC | ASC]}, ... ]

[ FOR UPDATE [OF {column_expression, ...}] ]

Select Clause

Follow Select with a list of column expressions you want to retrieve or an asterisk (*) to retrieve all fields.

SELECT [DISTINCT] {* | column_expression, [[AS] column_alias]. . .}

column_expression can be simply a field name (for example, LAST_NAME). More complex expressions may include mathematical operations or string manipulation (for example, SALARY * 1.05). See “SQL Expressions” for details.

column_alias can be used to give the column a descriptive name. For example, to assign the alias DEPARTMENT to the column DEP:

SELECT dep AS department FROM emp

Separate multiple column expressions with commas (for example, LAST_NAME, FIRST_NAME, HIRE_DATE).

Field names can be prefixed with the table name or alias. For example, EMP.LAST_NAME or E.LAST_NAME, where E is the alias for the table EMP.

The Distinct operator can precede the first column expression. This operator eliminates duplicate rows from the result of a query. For example:

SELECT DISTINCT dep FROM emp

Aggregate Functions

Aggregate functions can also be a part of a Select clause. Aggregate functions return a single value from a set of rows. An aggregate can be used with a field name (for example, AVG(SALARY)) or in combination with a more complex column expression (for example, AVG(SALARY * 1.07)). The column expression can be preceded by the Distinct operator. The Distinct operator eliminates duplicate values from an aggregate expression. For example:

COUNT (DISTINCT last_name)

In this example, only distinct last name values are counted.

Table 11-1 lists valid aggregate functions.

Table 11-1. Aggregate Functions

Aggregate	Returns
SUM	The total of the values in a numeric field expression. For example, SUM(SALARY) returns the sum of all salary field values.
AVG	The average of the values in a numeric field expression. For example, AVG(SALARY) returns the average of all salary field values.
COUNT	The number of values in any field expression. For example, COUNT(NAME) returns the number of name values. When using COUNT with a field name, COUNT returns the number of non-NULL field values. A special example is COUNT(*), which returns the number of rows in the set, including rows with NULL values.
MAX	The maximum value in any field expression. For example, MAX(SALARY) returns the maximum salary field value.
MIN	The minimum value in any field expression. For example, MIN(SALARY) returns the minimum salary field value.

From Clause

The From clause indicates the tables to be used in the Select statement. The format of the From clause is:

FROM table_names [table_alias]

table_names can be one or more simple table names in the current working directory or complete path names.

table_alias is a name used to refer to a table in the rest of the Select statement. Database field names may be prefixed by the table alias. Given the table specification:

FROM emp E

you may refer to the LAST_NAME field as E.LAST_NAME. Table aliases must be used if the Select statement joins a table to itself. For example:

SELECT * FROM emp E, emp F WHERE E.mgr_id = F.emp_id

The equal sign (=) includes only matching rows in the results.

If you are joining more than one table, you can use LEFT OUTER JOIN, which includes non-matching rows in the first table you name. For example:

SELECT * FROM T1 LEFT OUTER JOIN T2 on T1.key = T2.key

Where Clause

The Where clause specifies the conditions that rows must meet to be retrieved. The Where clause contains conditions in the form:

WHERE expr1 rel_operator expr2

expr1 and expr2 can be field names, constant values, or expressions.

rel_operator is the relational operator that links the two expressions. See “SQL Expressions” for details.

For example, the following Select statement retrieves the names of employees that make at least $20,000.

SELECT last_name,first_name FROM emp WHERE salary >= 20000

Group By Clause

The Group By clause specifies the names of one or more fields by which the returned values should be grouped. This clause is used to return a set of aggregate values. It has the following form:

GROUP BY column_expressions

column_expressions must match the column expression used in the Select clause. A column expression can be one or more field names of the database table, separated by a comma (,) or one or more expressions, separated by a comma (,). See “SQL Expressions” for details.

The following example sums the salaries in each department:

SELECT dept_id, sum(salary) FROM emp GROUP BY dept_id

This statement returns one row for each distinct department ID. Each row contains the department ID and the sum of the salaries of the employees in the department.

Having Clause

The Having clause enables you to specify conditions for groups of rows (for example, display only the departments that have salaries totaling more than $200,000). This clause is valid only if you have already defined a Group By clause. It has the following form:

HAVING expr1 rel_operator expr2

expr1 and expr2 can be field names, constant values, or expressions. These expressions do not have to match a column expression in the Select clause.

rel_operator is the relational operator that links the two expressions. See “SQL Expressions” for details.

The following example returns only the departments whose sums of salaries are greater than $200,000:

SELECT dept_id, sum(salary) FROM emp

GROUP BY dept_id HAVING sum(salary) > 200000

Union Operator

The Union operator combines the results of two Select statements into a single result. The single result is all the returned rows from both Select statements. By default, duplicate rows are not returned. To return duplicate rows, use the All keyword (UNION ALL). The form is:

SELECT statement

UNION ALL

SELECT statement

When using the Union operator, the Select lists for each Select statement must have the same number of column expressions with the same data types, and must be specified in the same order. For example:

SELECT last_name, salary, hire_date FROM emp

UNION

SELECT name, pay, birth_date FROM person

This example has the same number of column expressions, and each column expression, in order, has the same data type.

The following example is not valid because the data types of the column expressions are different (salary from emp has a different data type than last_name from raises). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.

SELECT last_name, salary FROM emp

UNION

SELECT salary, last_name FROM raises

Order By Clause

The Order By clause indicates how the rows are to be sorted. The form is:

ORDER BY {sort_expression [DESC | ASC]}, ...

sort_expression can be field names, expressions, or the positioned number of the column expression to use.

The default is to perform an ascending (ASC) sort.

For example, to sort by last_name and then by first_name, you could use either of the following Select statements:

SELECT emp_id, last_name, first_name FROM emp

ORDER BY last_name, first_name

or

SELECT emp_id, last_name, first_name FROM emp

ORDER BY 2,3

In the second example, last_name is the second column expression following Select, so Order By 2 sorts by last_name.

For Update Clause

The For Update clause locks the rows of the database table selected by the Select statement. The form is:

FOR UPDATE OF column_expressions

column_expressions is a list of field names in the database table that you intend to update, separated by a comma (,).

The following example returns all rows in the employee database that have a salary field value of more than $20,000. When each record is fetched, it is locked. If the record is updated or deleted, the lock is held until you commit the change. Otherwise, the lock is released when you fetch the next record.

SELECT * FROM emp WHERE salary > 20000

  FOR UPDATE OF last_name, first_name, salary

SQL Expressions

Expressions are used in the Where clauses, Having clauses, and Order By clauses of SQL Select statements.

Expressions enable you to use mathematical operations as well as character string and date manipulation operators to form complex database queries.

The most common expression is a simple field name. You can combine a field name with other expression elements.

Valid expression elements are as follows:

■	Field Names

■

Constants

■	Exponential notation

■	Numeric operators

■	Character operators

■	Date operators

■	Relational operators

■	Logical operators

■

Functions

Constants

Constants are values that do not change. For example, in the expression PRICE * 1.05, the value 1.05 is a constant.

You must enclose character constants in pairs of single (') or double (") quotation marks. To include a single quotation mark in a character constant enclosed by single quotation marks, use two single quotation marks together (for example, 'Don''t'). Similarly, if the constant is enclosed by double quotation marks, use two double quotation marks to include one.

You must enclose date and time constants in braces ({}), for example, {01/30/89} and {12:35:10}. The form for date constants is MM/DD/YY or MM/DD/YYYY. The form for time constants is HH:MM:SS.

The logical constants are .T. and 1 for True and .F. and 0 for False. For portability, use 1 and 0.

Exponential Notation

You can include exponential notation in expression elements. For example:

SELECT col1, 3.4E+7 FROM table1 WHERE calc < 3.4E-6 * col2

Numeric Operators

You can include the following operators in numeric expressions:

Operator	Meaning
+	Addition
–	Subtraction
*	Multiplication
/	Division
**	Exponentiation
^	Exponentiation

The following table shows examples of numeric expressions. For these examples, assume salary is 20000.

Example	Resulting value
salary + 10000	30000
salary * 1.1	22000
2 ** 3	8

You can precede numeric expressions with a unary plus (+) or minus (–). For example, –(salary * 1.1) is -22000.

Character Operators

Character expressions can include the following operators:

Operator	Meaning
+	Concatenation, keeping trailing blanks.
–	Concatenation, moving trailing blanks to the end.

The following table shows examples of character expressions. In the examples, last_name is 'JONES  ' and first_name is 'ROBERT  '.

Example	Resulting Value
first_name + last_name	'ROBERT    JONES  '
first_name – last_name	'ROBERTJONES      '

NOTE: Some flat-file drivers return character data with trailing blanks as shown in the table; however, you cannot rely on the driver to return blanks. If you want an expression that works regardless of whether the drivers return trailing blanks, use the TRIM function before concatenating strings to make the expression portable. For example:

TRIM(first_name) + '' + TRIM(last_name)

Date Operators

You can include the following operators in date expressions:

Operator	Meaning
+	Add a number of days to a date to produce a new date.
–	The number of days between two dates, or subtract a number of days from a date to produce a new date.

The following table shows examples of date expressions. In these examples, hire_date is {01/30/1990}.

Example	Resulting Value
hire_date + 5	{02/04/1990}
hire_date – {01/01/1990}	29
hire_date – 10	{01/20/1990}

Relational Operators

Relational operators separating any two expressions can be any one of those listed in Table 11-2.

Table 11-2. Relational Operators 

Operator	Meaning
=	Equal.
<>	Not Equal.
>	Greater Than.
>=	Greater Than or Equal.
<	Less Than.
<=	Less Than or Equal.
Like	Matching a pattern.
Not Like	Not matching a pattern.
Is NULL	Equal to NULL.
Is Not NULL	Not Equal to NULL.
Between	Range of values between a lower and upper bound.
In	A member of a set of specified values or a member of a subquery.
Exists	True if a subquery returned at least one record.
Any	Compares a value to each value returned by a subquery. Any must be prefaced by =, <>, >, >=, <, or <=. =Any is equivalent to In.
All	Compares a value to each value returned by a subquery. All must be prefaced by =, <>, >, >=, <, or <=.

The following list shows some examples of relational operators:

salary <= 40000

dept = 'D101'

hire_date > {01/30/1989}

salary + commission >= 50000

last_name LIKE 'Jo%'

salary IS NULL

salary BETWEEN 10000 AND 20000

WHERE salary = ANY (SELECT salary FROM emp WHERE dept = 'D101')

WHERE salary > ALL (SELECT salary FROM emp WHERE dept = 'D101')

Logical Operators

Two or more conditions may be combined to form more complex criteria. When two or more conditions are present, they must be related by AND or OR. For example:

salary = 40000 AND exempt = 1

The logical NOT operator is used to reverse the meaning. For example:

NOT (salary = 40000 AND exempt = 1)

Operator Precedence

As expressions become more complex, the order in which the expressions are evaluated becomes important. Table 11-3 shows the order in which the operators are evaluated. The operators in the first line are evaluated first, then those in the second line, and so on. Operators in the same line are evaluated left to right in the expression.

Table 11-3. Operator Precedence 

Precedence	Operator
1	Unary -, Unary +
2	**
3	*, /
4	+, -
5	=, <>, <, <=, >, >=, LIKE, NOT LIKE, IS NULL, IS NOT NULL, BETWEEN, IN, EXISTS, ANY, ALL
6	NOT
7	AND
8	OR

The following example shows the importance of precedence:

WHERE salary > 40000 OR

hire_date > {01/30/1989} AND

dept = 'D101'

Because AND is evaluated first, this query retrieves employees in department D101 hired after January 30, 1989, as well as every employee making more than $40,000, no matter what department or hire date.

To force the clause to be evaluated in a different order, use parentheses to enclose the conditions to be evaluated first. For example:

WHERE (salary > 40000 OR hire_date > {01/30/1989})

AND dept = 'D101'

retrieves employees in department D101 that either make more than $40,000 or were hired after January 30, 1989.

Functions

The flat-file drivers support a number of functions that you may use in expressions. In Table 11-4 through Table 11-6, the functions are grouped according to the type of result they return.

Table 11-4. Functions that Return Character Strings 

Function	Description
CHR	Converts an ASCII code into a one-character string. CHR(67) returns C.
RTRIM	Removes trailing blanks from a string. RTRIM('ABC  ') returns ABC.
TRIM	Removes trailing blanks from a string. TRIM('ABC  ') returns ABC.
LTRIM	Removes leading blanks from a string. LTRIM('  ABC') returns ABC.
UPPER	Changes each letter of a string to uppercase. UPPER('Allen') returns ALLEN.
LOWER	Changes each letter of a string to lowercase. LOWER('Allen') returns allen.
LEFT	Returns leftmost characters of a string. LEFT('Mattson',3) returns Mat.
RIGHT	Returns rightmost characters of a string. RIGHT('Mattson',4) returns tson.
SUBSTR	Returns a substring of a string. Parameters are the string, the first character to extract, and the number of characters to extract (optional). SUBSTR('Conrad',2,3) returns onr. SUBSTR('Conrad',2) returns onrad.
SPACE	Generates a string of blanks. SPACE(5) returns '     '.
DTOC	Converts a date to a character string. An optional second parameter determines the format of the result: 0 (the default) returns MM/DD/YY. 1 returns DD/MM/YY. 2 returns YY/MM/DD. 10 returns MM/DD/YYYY. 11 returns DD/MM/YYYY. 12 returns YYYY/MM/DD. An optional third parameter specifies the date separator character. If not specified, a slash (/) is used. DTOC({01/30/1997}) returns 01/30/97. DTOC({01/30/1997}, 0) returns 01/30/97. DTOC({01/30/1997}, 1) returns 30/01/97. DTOC({01/30/1997}, 2,'-') returns 97-01-30.
DTOS	Converts a date to a character string using the format YYYYMMDD. DTOS({01/23/1990}) returns 19900123.
IIF	Returns one of two values, true or false. Parameters are a logical expression, the true value, and the false value. If the logical expression evaluates to true, the function returns the true value. Otherwise, it returns the false value. IIF(salary>20000,'BIG','SMALL') returns BIG if salary is greater than 20000. If not, it returns SMALL.
STR	Converts a number to a character string. Parameters are the number, the total number of output characters (including the decimal point), and optionally the number of digits to the right of the decimal point. STR(12.34567,4) returns 12. STR(12.34567,4,1) returns 12.3. STR(12.34567,6,3) returns 12.346.
STRVAL	Converts a value of any type to a character string. STRVAL('Woltman') returns Woltman. STRVAL({12/25/1953}) returns 12/25/1953. STRVAL (5 * 3) returns 15. STRVAL (4 = 5) returns 'False'.
TIME	Returns the time of day as a character string. At 9:49 PM, TIME() returns 21:49:00.
TTOC	NOTE: This function applies only to flat-file drivers that support SQL_TIMESTAMP: the Btrieve driver and the dBASE (access to FoxPro 3.0) driver. Converts a timestamp to a character string. An optional second parameter determines the format of the result: When set to 0 or none (the default), MM/DD/YY HH:MM:SS AM is returned. When set to 1, YYYYMMDDHHMMSS is returned, which is a suitable format for indexing. TTOC({1992-04-02 03:27:41}) returns 04/02/92 03:27:41 AM. TTOC({1992-04-02 03:27:41, 1}) returns 19920402032741
USERNAME	For Btrieve, the logon ID specified at connect time is returned. For all other flat-file drivers, an empty string is returned.

Table 11-5. Functions that Return Numbers 

Function	Description
MOD	Divides two numbers and returns the remainder of the division. MOD(10,3) returns 1.
LEN	Returns the length of a string. LEN('ABC') returns 3.
MONTH	Returns the month part of a date. MONTH({01/30/1989}) returns 1.
DAY	Returns the day part of a date. DAY({01/30/1989}) returns 30.
YEAR	Returns the year part of a date. YEAR({01/30/1989}) returns 1989.
MAX	Returns the larger of two numbers. MAX(66,89) returns 89.
DAYOFWEEK	Returns the day of week (1-7) of a date expression. DAYOFWEEK({05/01/1995}) returns 5.
MIN	Returns the smaller of two numbers. MIN(66,89) returns 66.
POW	Raises a number to a power. POW(7,2) returns 49.
INT	Returns the integer part of a number. INT(6.4321) returns 6.
ROUND	Rounds a number. ROUND(123.456, 0) returns 123. ROUND(123.456, 2) returns 123.46. ROUND(123.456, –2) returns 100.
NUMVAL	Converts a character string to a number. If the character string is not a valid number, a zero (0) is returned. NUMVAL('123') returns the number 123.
VAL	Converts a character string to a number. If the character string is not a valid number, a zero (0) is returned. VAL('123') returns the number 123.

Table 11-6. Functions that Return Dates 

Function	Description
DATE	Returns today’s date. If today is 12/25/1999, DATE() returns {12/25/1999}.
TODAY	Returns today's date. If today is 12/25/1999, TODAY() returns {12/25/1999}.
DATEVAL	Converts a character string to a date. DATEVAL('01/30/1989') returns {01/30/1989}.
CTOD	Converts a character string to a date. An optional second parameter specifies the format of the character string: 0 (the default) returns MM/DD/YY, 1 returns DD/MM/YY, and 2 returns YY/MM/DD. CTOD('01/30/1989') returns {01/30/1989}. CTOD('01/30/1989',1) returns {30/01/1989}.

The following examples use some of the number and date functions.

Retrieve all employees that have been with the company at least 90 days:

SELECT first_name, last_name FROM emp

  WHERE DATE() – hire_date >= 90

Retrieve all employees hired in January of this year or last year:

SELECT first_name, last_name FROM emp

  WHERE MONTH(hire_date) = 1

  AND (YEAR(hire_date) = YEAR(DATE())

  OR YEAR(hire_date) = YEAR(DATE()) – 1)