Local SQL

There are a number of items that are specific to Local SQL that should be mentioned prior to discussing the statement syntax. The sections that follow highlight the syntax rules that are specific to the formulation of the SQL statements using the Local SQL implementation. When developing in a multi-tier environment, refer to the other product documentation to determine its specific requirements.

Table Names

Local SQL is enhanced to handle multiple-word table names. Table names can include the full path and file name and can include the table's file extension. When the file extension is included, it must be surrounded by single or double quotes. You may also use BDE aliases as a part of the table name.

When no file extension is provided as a part of the table name, the BDE configuration is referenced to determine the driver type of the table.

Column Names

Local SQL is enhanced to support multiple word column names. Multi-word column names with embedded spaces must be surrounded by quotes, as should column names that include the table name or alias preface.

Dates

Dates used as a part of a SQL statement must conform to U.S. date format, MM/DD/YY or MM/DD/YYYY. All dates must be enclosed in quotes to prevent confusion with arithmetic expressions. If the year is

Time formats

Chapter 3—Structured Query Language provided in two digits, the FOURDIGITYEAR setting in the BDE config' uration is referenced to determine its action.

If the FOURDIGITYEAR setting is False, years 49 or less are considered to be in the 2000s. Years of 50 or greater are considered a part of the 1900s.

Chapter 3—Structured Query Language

Time formats

Time data supplied as a part of a SQL statement must conform to the HH:MM:SS format and be enclosed in quotes. You may append AM or PM to indicate morning or evening. The AM/PM indicator is optional. If you use times from a 12-hour clock, you should specify the period of the day. You can substitute 24-hour times, and all hours past 12 will be considered to be in the afternoon.

Boolean

Boolean data must be spelled out, True or False, and can be included with or without quotes.

Table Correlation

Table correlation is used when explicitly linking a column name with a table. This is necessary when the same column name exists in one or more of the relations included in the SQL statement. It also allows you to provide a shorter alias for use throughout the statement.

If the table name is surrounded by quotes in the FROM clause, it must also be quoted when used in a correlation context. You can also assign an alias by adding a token directly after the relation name as in (VENDORS V). V will now be recognized as the alias for VENDORS throughout the SQL statement.

Column Corr elation

You use the AS keyword to assign an alias to a column name for use in the rest of the query The alias cannot contain embedded spaces and cannot be in quotes.

Embedded Comments

Comments are often helpful to document a lengthy SQL statement. Comments can be embedded in statements by surrounding the comment with /* and V-

Chapter 3-Structured Query Language

The asterisk wildcard character represents all columns to the SELECT statement. When used, every column from the original table is found irk the result set. To specify a set that is composed of specific columns, a comma-separated list of column names replaces the asterisk:

SELECT VendorlD, VendorName FROM Vendors

The technical term for the resulting data set is a projection.

Joins

Another common SELECT statement task is to select rows from multiple tables; this process is called a join. The SELECT statement allows you to use a comma-separated list of table names in the FROM clause to specify the table or tables that the statement should act against:

SELECT * FROM Vendors, Items

The resulting union contains every column and every row from both of the tables. The statement given as an example points out the importance of carefully considering your goals for the query. Such a blanket query as this may generate results that are far removed from what you were expecting. You would be much better off limiting the scope of your result set by adding additional clauses to the statement.

Join types are closely related to their mathematical counterparts and are named accordingly The joins supported through Local SQL are:

  • Equi-join
  • Inner
  • Outer Cartesian
  • Union Heterogeneous

Equi-join

The purpose of an equi-join is to denormalize tables that were originally normalized into separate relations. Through the normalization process, new relations were formed from a single original and both contain one or more columns in common. It is through these common values that the equi-join rejoins the tables into a single result set.

The WHERE clause of the SELECT statement determines on which columns the relations will be joined, the rows being selected when the values in columns of the WHERE clause are equal. The values used in the comparison expression can be single columns or concatenated

Chapter 3-Structured Query Language

Part I -The Relational Database columns. Multiple column values are concatenated through the use of the concatenation function, I I.

VENDORS

Inner Join

An inner join is the same as an equi-join; it just uses a different syntax to achieve the same result. The inner join allows you to define the join condition in the FROM clause by adding the INNER JOIN and ON keywords. The syntax for an inner join is:

SELECT <co lumn list> FROM <table>

INNER JOIN <table to be joined> ON join expression>

The result set from this join will contain only rows that meet the equality conditions of the join expression.

Outer Join outer join generates a new relation from two relations that have one or more columns in common. The resulting data set does not exclude rows from the source table-the table in the FROM clause-when they do not match a row in the joining relation. This concept may be best illustrated with an example. Suppose that we have VENDORS and ITEMS tables and we perform the following outer join on the tables:

SELECT * FROM Vendors OUTER JOIN Items ON Vendors:Vend ID

Items:Vend_ID

The base relations and the resulting data set are shown in Figure 3.1:

ITEMS

VENDORS

VEND ID

VEND NAME

loo

Blue Moon Foods

200

Sea Foods Inc.

300

CrabwalkSuppliers

DESC VEND ID

DESC VEND ID

RESULT SET

VEND ID

VEND NAME

DESC

VEND ID

100

Blue Moon Foods

Crab

100

200

Sea Foods Inc.

Oyster

200

300

Crabwalk Suppliers

Figure 3. I An outer join and result set

Figure 3. I An outer join and result set

The resulting relation contains the columns from both of the tables. In the third row, where there was no match between VENDORS and ITEMS, an outer join will fill the columns with null values.

Chapter 3—Structured Query Lung^uage

This is representative of a left outer join where all of the rows from the relation on the left of the expression are included in the result set. The modifier BIGHT will create exactly the opposite result; all rows from the table on the right of the expression will be included. A full outer join will contain all rows from both of the tables with the expression null filled in as appropriate.

Cartesian Join

Approach the Cartesian join cautiously, as it can quickly get out of hand. A Cartesian join is the result of all possible combinations of FOWS from all of the tables included in the join. The join will match each row in table A with each row in table B without comparison. The result set is built from simple association. If table A has 10 rows and table B has 10 rows, the resulting data set will contain 100 rows.

Union Join

The union join is used when you want to add the rows of one relation to the end of another relation of similar structure. The two relations must be very close in structure, including data type, for the union join to be successful. The BDE will attempt to do some data conversions during the process, but up-front work will pay off if this type of join is your goal.

Heterogenous join

A heterogenous join is a join in which the tables involved are from two different databases. A Local SQL requirement is the databases involved in the join must be accessible through the BDE.

The first delimiting word that we can add to a SELECT statement is DISTINCT The DISTINCT keyword limits a resulting data set so that it contains only singular rows. The uniqueness of the rows is determined by the column list that is provided in the statement. The SELECT statement:

SELECT DISTINCT Vendor-Name, VendorCity from Vendors will result in a new relation composed of these two columns. The SQL processor will evaluate each combination of columns for uniqueness, and redundant rows will be removed from the final result set. You are probably questioning the need for this clause, given the relational rules that we have discussed in the previous two chapters. In general, the database should take care of this for us. If the column list that we select includes the primary key, then we can be assured that a unique data set will result. However, many times we will want to retrieve a subset of

Part / -The Relational Database

the table that does not necessarily include the primary key, as shown in the above SELECT statement. The DISTINCT keyword acts upon the non-repeating values rule and ferrets out the duplicate rows.

Limiting the Result Set

Most of the time, you will not want to retrieve an entire data set into another, new data set. More often, the SELECT query that you formulate will be based upon a set of criteria. The SQL WHERE clause is the keyword that filters the resulting data set based on a Boolean evaluation of each record. The optional clause results in a subset of the base table being selected.

select <column list> from <table list> where < predicates >

The WHERE Clause

The WHERE clause uses a defined set of predicates, or logical expressions, to define the filtering conditions. The following predicates are supported:

Comparison-Compares two values BETWEEN-Compares a value to a range of values EXISTS-Compares a value to a lookup list IN-Determines if a value exists in a list of values or a table LIKE-Compares one value with another IS NULL-Compares a value with Null SOME/ANY/ALL-Performs a quantified comparison

Comparison

Comparison predicates simply compare two like values based on the Boolean operator that separates them. The following SELECT statement:

select VendorName from Vendors where VendorZipCode = '80437'

retrieves a result set that is composed of rows containing the vendor names for those vendors that have a zip code of 80437. You can use all of the Boolean operators available to Delphi ( <, < =, =,>=,> ) in a comparison predicate.

SELECT * FROM Salesman where Annual Sales >= 10000

Chapter 3-Structured Query

You can add the logical operators AND or OR to your WHERE clause to create more complex comparison statements. The SELECT statement:

SELECT * FROM Vendors

WHERE ((VendorCity = 'Coos Bay') OR (VendorCity

Portland))

will evaluate each row from Vendors to determine if either of the VendorCity conditions is met. The logical NOT operator can also be used to immediately negate the predicate:

SELECT * FROM Vendors

WHERE NOT VendorCity = 'Coos Bay'

This statement will retrieve a result set containing all vendors that are not located in Coos Bay.

Tip The CAST function can be used in SQL operations that require like data types such as the comparison predicates. The CAST function converts a specified value into a different data type. Using this function can allow you to create a comparison between two operators such as a number and a string. For example,

SELECT * FROM Bio life WHERE CAST("Species No" as Char(6)) >= '90200'

casts the field Species No as a character string rather than a numeric field. This might be just the ticket for performing some of your parameter-driven queries.

BETWEEN

The BETWEEN comparison operator compares the column value in your WHERE predicate to a range of values. If the column value is greater than or equal to the low end of the range and less than or equal to the high end of the range, a TRUE result will be returned. You may also modify this operator with the NOT logical operator to achieve the opposite result and identify those rows that fall on the outside of eithe side of the range. The values that are being compared must be of the same data type or of a compatible data type. The SELECT statement:

SELECT Description, Freshness FROM Items

WHERE (PurchaseDate BETWEEN "10/31/98" AND "11/03/98")

will return a result set that includes any inventory purchased on or between the range of dates.

Part I -The Relational Database

EXISTS

The EXISTS comparison predicate lets you take an entirely new approach to developing your query. The addition of EXISTS to a SQL statement indicates that you are performing a subquery, or nested query, within your outer query. The comparison action is then performed against the result set from the inner query

The tables shown in Figure 3.2 are related by the vendor ID contained in each, once as the primary key and again in the ITEMS table as a foreign key We want to develop a query that will tell us who our suppliers are for a specific product, crab. Performing this requires two different questions: Which vendors supply crab and what is the vital information that we need about them? The EXISTS predicate performs this operation for you. It filters a table to retrieve those values that exist in a subquery table.

ITEMS

VENDORS

IT ITEM # IT ITEM DESC IT VENDOR ID

ITEMS

VENDORS

IT ITEM # IT ITEM DESC IT VENDOR ID

Figure 3.2 The VENDORS and ITEMS relations are related through the Vendor-/D field, an EXISTS predicate easy to implement.

Figure 3.2 The VENDORS and ITEMS relations are related through the Vendor-/D field, an EXISTS predicate easy to implement.

The subquery uses a WHERE predicate that includes one or more fields from the outer query and, presumably, additional filtering conditions. The SQL statement:

SELECT * FROM Vendors V WHERE EXISTS

(select it-vendor-id FROM itEmS I MERE IT ITEM DESC = 'Crab ' AND V.VS VEND0RID~= I.ITJENDORJD)

performs the subquery (shown in italics) once for each row in the table of the outer query. If the conditions in the subquery are met and a TRUE is returned, the current row in the outer query is returned in the result set.

Chapter 3--Structured Query Language

The subquery requires table correlation in order to work correctly. There are a couple of different ways that this can be handled. The first is demonstrated in the example query. Notice that in each FROM clause, an alias is appended to the entry in the table list. This is known to the query as the table's alias. You can then use this shorthand to refer to the table as the owner of a field by prepending this alias to the column name, as in WSJVENDORID and I.IT_VENDOR_ID. If you don't want to use the alias approach, you can use the full table identifier to identify the table, VENDORS .VS_VENDORID and ITEMS .IT_VENDOR__ID.

The IN predicate allows you to select the result set based on the comparison of a column value with a specified set of values. The IN predicate works on the same principle as the Pascal IN Boolean operator: If value X is a part of set S, a TRUE value is returned. The IN predicate works the same way. If a column value matches an item in a set that is defined in the body of the query, then a TRUE is returned and the row containing the column value is retrieved as a part of the result set. The following SQL statement will retrieve only those rows in ITEMS that have an item description that is included in the set ('CrabyShad'):

SELECT * FROM Items

The items being compared, as in all SQL statements, must be of the same data type or cast to be of the same data type.

The IN predicate can also use the results of a subquery to determine the members of a set. For example, to determine all of the items that can be purchased quickly from a supplier in Portland, the query:

SELECT * from Items I where ( I.IT vendor _ID in

( SELECT V.VS_VENDORID FROM Vendors V WHERE V.VS_VENDORCITY = 'Portland'))

will create a set based on the criteria VS_VENDORCITY = 'Portland'. The set will contain the vendor ID numbers and will be a dynamic comparison set for the IN predicate. Each row in ITEMS will be retrieved based upon its Vendor ID column being a member of the set.

Part I -The Relational Database

LIKE

Selections for the result set using the LIKE predicate are based on the similarity between a column value and a comparison value. The fuzzy comparison is implemented through a set of substitution characters and allows you to match anything from the first letter of a column all the way out to the entire length of the value.

The wildcard substitution character "%" represents any number of characters in a comparison. A row is retrieved in a query in which a WHERE comparison using the LIKE predicate matches any portion of the comparison value not corresponding to the wildcard character.

SELECT * FROM Vendors WHERE VS VENDORNAME LIKE 'C%'

Running this SQL statement against the VENDORS table will retrieve rows that contain "Crabwalk Suppliers" and "Conglomerated Foods" but not "Blue Moon Foods."

If the substitution is limited to a single character, you can use the substitution character "J7 at any position within the comparison value.

SELECT * FROM Items

WHERE IT-ITEMNUMBER LIKE '10 1

This SQL statement will retrieve any Item Number that falls between "100" and "109," performing the substitution on the last character in the string. As with the wildcard substitution character, the single-character substitution token can appear in the beginning, middle, or end of the comparison value. For example, the comparison value in the SQL statement could be modified to be This would return a result set that included the first ten values in each hundreds set (101..109, 201..209) but would not include any above those values such as 110 or 233.

The keyword ESCAPE is a modifier to the substitution characters used in a LIKE predicate. You will use ESCAPE when the characters "%" or appear as a part of the data in the column values. An escape character designates a symbol which indicates to the LIKE predicate that the "%" or character immediately following is to be taken as a literal value. For example, '(§)' is the escape character in the comparison value '%[email protected]%%\ The retrieval selections will be filtered for values that are like '100%'. The SQL syntax for this statement is:

SELECT * FROM Inventory

Part I -The Relational Database

  • g»H»4gilt jì »♦H.DB Language I
  • g»H»4gilt jì »♦H.DB Language I

The LIKE predicate only works with alphanumeric data types or types that can be cast as character data. The comparison performed is case sensitive, following standard Pascal conventions.

IS NULL

The IS NULL predicate simply retrieves rows based upon a TRUE response to an IS NULL comparison.

SELECT * FROM Vendors

WHERE VS FIRSTORDERDATE IS NULL

This SQL query, performed against the VENDORS relation, returns a result set for all Vendors from which we have never ordered. The comparison value must be a true Null, not blanks or zeroes.

SOME/ANY/ALL

The quantified comparison predicates SOME, ANY, and ALL retrieve rows based on the quantified comparison with the results from a subquery. This predicate combines with a simple comparison predicate to select the rows to be retrieved. The SOME and ANY predicates are functionally the same and the rows are selected if the accompanying simple comparison predicate evaluates to TRUE for any row in the subquery. For instance, the SQL statement below will return any row from the ITEMS relation in which the Item Cost is less than ANY of the Wholesale Costs returned by the subquery.

SELECT * FROM Items I WHERE {I.ItemCost < ANY

(SELECT WholesaleCost FROM Wholesale))

The subquery may retrieve multiple rows but it can only contain one column. The column selected must be of the same data type as that to which it is being compared in the outer query You may also cast the values if necessary to make them comparable. The ALL predicate requires that every simple comparison between the column value and the column values in the subquery evaluate to TRUE.

SELECT * FROM Items I WHERE (I.Item_Cost > ALL

(SELECT WholesaleCost FROM Wholesale))

This SQL statement modifies the original query to retrieve rows from ITEMS in which the Item Cost is greater than every row in the WHOLESALE relation.

60 ■ Part I - The Relational Database

Predicate Summary

The predicates supported for the WHERE keyword determine the filtering of the query and any subqueries. If you review these in terms of the relational database discussion in earlier chapters, you can see that the SQL statements all work on the surety of the design. There can be only a true or false response to any query of the database; all gray areas have been removed through careful design. This confidence makes all of your up-front work worthwhile.

The ORDER BY Clause

The ORDER BY clause is used in a SQL SELECT statement to order the retrieved rows in the result set. The order is determined by the values in a comma-separated list of one or more columns.

SELECT * FROM Vendors WHERE VS-VENDORCITY = ORDER BY VS-VENDORNAME

'Seattle'

This statement will retrieve all vendors located in Seattle and then will order the output by the value contained in the column VS_Vendoraame. By default, this will be in ascending order. You may control the listing direction by adding either the ASC or DESC modifier to the ORDER BY column list.

SELECT * FROM Vendors

WHERE VS-VENDORCITY = 'Seattle'

ORDER BY VS-VENDORNAME DESC

This statement will present the retrieved rows sorted in descending order. A comma-separated list of column names can each be set to a different sort direction as shown in the following example.

SELECT * FROM Items

ORDER BY IT-VENDOR-ID ASC, IT-DESCRIPTION DESC

You can use some handy shorthand to build your ORDER BY (and GROUP BY) column list. Remember column correlation? Local SQL allows you to refer to the column names in three different ways. You can use the full column names but this makes for some very long statements. You can also refer to the columns as they are enumerated, for example, in the column list Name, Street, City, State, Zip, Name is referred to as position I, Street is position 2, etc. Also, much as we have used single letters to

60 ■ Part I - The Relational Database

Chapter 3-Structured Query Language

create table aliases (i.e., VENDORS V) you can create column aliases by listing the alias directly after the column name. The column list would now be Name N, Street S, City C, State ST, Zip Z, and your ORDER BY clause can now use the aliases to shorten up the overall statement.

The GROUP BY Clause

The GROUP BY clause is used in conjunction with the aggregate functions to combine rows with the same column value into a single row. The criteria for combining rows is based on the column list specified in the GROUP BY clause. The following SQL statement groups the average totals by the Item Name.

SELECT Item-Description, AVG( Item-Cost ) As Average

FROM Sales GROUP BY Item-Description

Figure 3.3 shows the original relation and the rows that are retrieved from this query

SALES

ITEM NUMBER ITEM DESCRIPTION

ITEM COST

SALES

ITEM NUMBER ITEM DESCRIPTION

ITEM COST

1 563 1

Smelt

\ 3.07

563

Smelt

9.88

Öttö

Devil Ray

1 .99

563

Smelt

1 3.56

666 1

Devil Ray

1.05

921

I Oyster

I 8.77

Result of SQL statement using the aggregate function Average.

RESULT SET ¡TEM_DESCRIPTION Average

Devil Ray j

1.02

I^^ter

8.77

Smelt

5.77

Figure 3.3 SQL results using an aggregate function and the GROUP BY clause.

Figure 3.3 SQL results using an aggregate function and the GROUP BY clause.

Part I -The Relational Database

Aggregate Functions

Local SQL supports a number of aggregate functions for use in SQL statements. The functions supported are:

  • AVG-Averages all non-null numeric values in a column
  • COUNT-Counts the number of rows in a result set
  • MAX—Determines the maximum value in a column MIN-Determines the minimum value in a column
  • SUM-Totals all numeric values in a column

As shown in the GROUP BY example above, the functions are used as a part of the SELECT column list. Each of them results in a new column being created that will either be named by you using the AS Name modifier or will default to a description of the function as in COUNT OF ITEM COST.

In its simplest form, the AVG (average) function will compute the average of all values in a column.

SELECT AVG( ITEM-COST ) FROM Sales will result in a single row result set. When the GROUP BY clause is used, an average is computed for each group determined by the column list.

COUNT

Use the COUNT function to return the number of rows retrieved by a statement. The statement:

SELECT COUNT( ITEM-DESCRIPTION ) FROM Items will return a count equal to the total number of rows in the Items table. You can modify this function by adding the DISTINCT modifier to the statement:

SELECT COUNT( DISTINCT ITEM-COST ) FROM Items

This will result in the function ignoring duplicate column values in its total.

The MAX function returns the maximum value encountered in all of the rows of the identified relation.

SELECT MAX( ITEM-COST ) From Items

Chapter 3-Structured Query Language ■ 63

When this SQL statement is processed against the Sales table in Figure 3.3, the row returned will contain 9.88. By modifying the SELECT statement with a GROUP BY clause, the maximum cost of each of the item types can also be returned.

The MIN function returns the minimum value of a column in all of the rows selected. It works identically to the MAX function.

One item not mentioned to this point is that you can combine these functions in a single statement to produce a set of useful statistics from a single query The following statement will produce a result set that shows the average cost for each item and the highest and lowest costs as well.

SELECT ITEM-DESCRIPTION, AVG( ITEM-COST ), MAX( ITEM-COST ), MIN( ITEM-COST ) FROM Items

GROUP BY ITEM-DESCRIPTION SUM

The aggregate function SUM totals all numeric values in columns across the entire set of rows in a dataset. As in all of the functions, using the function alone will result in a single row being returned that contains the sum of the columns selected. The GROUP BY clause will result in a sum being computed for each group.

The HAVING Clause

The HAVING clause is used in a SQL statement to limit the rows retrieved to those in which the aggregated columns meet the HAVING clause criteria. To utilize the HAVING clause, both a GROUP BY clause and one or more aggregated columns must be present in the statement. For example, the following SQL statement will retrieve only those groups that have an average cost of greater than $5.00.

SELECT ITEM-DESCRIPTION, AVG( ITEM-COST ) FROM Sales

GROUP BY ITEM-DESCRIPTION

Multiple predicates may be used in the HAVING clause to further limit the rows that are retrieved by using the logical operators AND or OR. You may also utilize subqueries in a HAVING clause, causing it to act like a search condition. Though they appear to be similar in function, the WHERE and the HAVING clauses perform two separate functions.

Part I -The Relational Database

The WHERE clause limits the data to be aggregated, using columns that are not a part of the aggregate functions as the criteria for selection. The HAVING clause filters the rows after aggregation, using the columns that are part of the aggregation functions to limit the result set.

Was this article helpful?

0 0

Post a comment