SQL support


Supported syntax in SQL

Some differences:
- Calculated fields in the result set not fully supported yet, althoug this is not a big problem due to that you can create calculated columns by defining them in the list of columns in SELECT clause of SQL statement.
- Dates are defined like this: #dd/mm/yyyy#
- Long names (also called extended identifiers) area given with this notation: [This Is a long Name] compared "This is a long name". This identifiers are used so for field names as for table names.

Aditionally, comments embedded in SQL statements are allowed, and must be between "/*" and "*/":
SELECT * FROM /* this is a comment */ customer WHERE custno > 2000

SELECT statement
SELECT clause
FROM clause
WHERE clause
ORDER BY clause
GROUP BY clause
Subqueries (or Subselects)

UPDATE statement
UPDATE

DELETE statement
DELETE

INSERT statement
INSERT

CREATE TABLE statement
CREATE TABLE

ALTER TABLE statement
ALTER TABLE

CREATE INDEX statement
CREATE INDEX

DROP TABLE statement
DROP TABLE

DROP INDEX statement
DROP INDEX

UNION statement
UNION

TRANSFORM...PIVOT statement
TRANSFORM...PIVOT


Available functions in SQL statements.

These are the list of functions that can be used in expression in the differents clauses in SQL statements (not counting the aggregates functions SUM(), MIN(), MAX(), AVG(), COUNT(*). Some of this function comes from the expression evaluator and some were added:

Arithmetic Functions

function RoundDec(X: Extended, Precision: Integer)
The RoundDec function round a float-type value to a given precision (decimals).

function TruncDec(X: Extended, Precision: Integer)
The TruncDec function truncates a float-type value to a given precision (decimals).

* function Trunc(X: Extended):Integer
The Trunc function truncates a float-type value to an integer-type value. X is a float-type expression. Trunc returns an Integer value that is the value of X rounded toward zero.

* function Round(X: Extended): Integer
The Round function rounds a float-type value to an integer-type value. X is a float-type expression. Round returns a Longint value that is the value of X rounded to the nearest whole number. If X is exactly halfway between two whole numbers, the result is the number with the greatest absolute magnitude.

* function Abs(X): Float
The Abs function returns the absolute value of the argument. X is an integer-type or float-type expression.

* function ArcTan(X: Float): Float
ArcTan calculates the arctangent of the given number. Calculate other trigonometric functions using Sin, Cos, and ArcTan in the following expressions
Tan(x) = Sin(x) / Cos(x)
ArcSin(x) = ArcTan (x/sqrt (1-sqr (x)))
ArcCos(x) = ArcTan (sqrt (1-sqr (x)) /x)

* function Cos(X: Float): Float
The Cos function returns the cosine of the angle X, in radians.

* function Exp(X: Float): Float
Exp returns the value of e raised to the power of X, where e is the base of the natural logarithms.

* function Frac(X: Float): Float
The Frac function returns the fractional part of the argument X. X is a float-type expression. The result is the fractional part of X; that is: Frac(X) = X - Int(X).

* function Int(X: Float): Float
X is a float-type expression. The result is the integer part of X; that is, X rounded toward zero.

* function Ln(X: Float): Float
The Ln function returns the natural logarithm (Ln(e) = 1) of the float-type expression X.

* function Pi: Float
Use Pi in mathematical calculations that require pi, the ratio of a circle's circumference to its diameter. Pi is approximated as 3.1415926535897932385.

* function Sin(X: Float): Float
The Sin function returns the sine of the argument. X is a float-type expression. Sin returns the sine of the angle X in radians.

* function Sqr(X: Float): Float
The Sqr function returns the square of the argument. X is a floating-point expression. The result, of the same type as X, is the square of X, or X*X.

* function Sqrt(X: Float): Float
X is a floating-point expression. The result is the square root of X.

* function Power(Base, Exponent: Float): Float
The Power function raises Base to any power. For fractional exponents or exponents greater than MaxInt, Base must be greater than 0.

function Now
The Now function returns the current system date in floating point format, so maybe you must used FormatdateTime() function to convert to readable date format. Now must be used without "()"

* function MINOF(arg1, arg2, ... argn)
The MINOF function returns the lowest value of the n figures. arg1, arg2, ..., argn are valid expressions. There must be at least 2 arguments for this function or an error will be raised.

SELECT MINOF(consumption1, consumption2, consumption3) FROM WaterUsers

* function MAXOF(arg1, arg2, ..., argn)
The MAXOF function returns the highest value of the n figures. arg1, arg2, ..., argn are valid expressions. There must be at least 2 arguments for this function or an error will be raised.

SELECT MAXOF(consumption1, consumption2, consumption3, consumption4) FROM WaterUsers

function YEAR(date)
Return the year for the given date

SELECT YEAR(LastInvoiceDate) FROM Customer

function MONTH(date)
Return the month for the given date

SELECT MONTH(LastInvoiceDate) FROM Customer

function DAY(date)
Return the day for the given date

SELECT DAY(LastInvoiceDate) FROM Customer

function HOUR(date)
Return the hour for the given time

SELECT HOUR(LastInvoiceDate) FROM Customer

function MIN(datetime)
Return the minutes for the given time

SELECT MIN(LastInvoiceDate) FROM Customer

function SEC(datetime)
Return the seconds for the given time

SELECT SEC(LastInvoiceDate) FROM Customer

function MSEC(datetime)
Return the milliseconds for the given time

SELECT MSEC(LastInvoiceDate) FROM Customer

String Functions

function Upper(S: string): string
The Upper function returns a string containing the same text as S, but with all 7-bit ASCII characters between 'a' and 'z' converted to uppercase.

function Lower(S: string): string
Lower returns a string with the same text as the string passed in S, but with all letters converted to lowercase. The conversion affects only 7-bit ASCII characters between 'A' and 'Z'.

* function Copy(S: string; Index, Count: Integer): string
The Copy function returns a substring of a string. S is a string-type expression. Index and Count are integer-type expressions. Copy returns a string containing Count characters starting at S[Index]. If Index is larger than the length of S, Copy returns an empty string. If Count specifies more characters than are available, the only the characters from S[Index] to the end of S are returned.

* function Pos(Substr: string; S: string): Integer
Pos searches for a substring, Substr, in a string, S. Substr and S are string-type expressions. Pos searches for Substr within S and returns an integer value that is the index of the first character of Substr within S. Pos ignores case-insensitive matches. If Substr is not found, Pos returns zero.

* function Length(S: string): Integer
The Length function returns the number of characters actually used in the string S.

* function Left(S: String; Count: Integer): string
The Lefts function returns the leftmost Count characters contained in string S. S is a string-type expression. Count is an integer expression. If Count is larger than S, then S is returned.
Note: Please check that the function name is Lefts, and you cannot use Left alone due to that it is a reserved word (used probably in a LEFT OUTER JOIN)

* function Right(S: String; Count: Integer): string
The Rights function returns the rightmost Count characters contained in string S. S is a string-type expression. Count is an integer expression. If Count is larger than S, then S is returned.
Note: Please check that the function name is Rights, and you cannot use Right alone due to that it is a reserved word (used probably in a RIGHT OUTER JOIN)

* function Format(Format: string; Arg1, Arg2, Arg3, ...): string;
This function formats the series of arguments in the list of arguments Arg1, Arg2, Arg3, .... Formatting is controlled by the Object Pascal format string Format; the results are returned in the function result as a string. Every argument in the list can be an expression. Work the same as in Object Pascal.

* function FormatDateTime(Format: String; DateTime: float): String
FormatDateTime formats the date-and-time value given by DateTime using the format given by Format. You can see the list of format specifiers in the Delphi Help system.

* function FormatFloat(Format: String; Value: Float): String
FormatFloat formats the floating-point value given by Value using the format string given by Format. You can see the list of format specifiers in the Delphi Help system.

TRIM function
The TRIM function have the SQL syntax TRIM(LEADING|TRAILING|BOTH trimmed_char FROM column_reference).
Use TRIM to delete the leading or trailing character, or both, from a table column. The TRIM function only deletes characters located in the specified position.
The first parameter indicates the position of the character to be deleted, and has one of the following values:

Value Description
LEADING Deletes the character at the left end of the string.
TRAILING Deletes the character at the right end of the string.
BOTH Deletes the character at both ends of the string.

The trimmed character parameter specifies the character to be deleted, if present. Case-sensitivity is applied for this parameter. To make TRIM case-insensitive, use the UPPER function.
FROM specifies the column from which to delete the character.

SUBSTRING function
The SUBSTRING function have the syntax:
SUBSTRING(column_reference FROM start_index [FOR length])

Use SUBSTRING to extract a substring from a table column or character literal, specified in the column reference.

FROM is the character position at which the extracted substring starts within the original string. The index for FROM is based on the first character in the source value being 1.

FOR is optional, and specifies the length of the extracted substring. If FOR is omitted, the substring goes from the position specified by FROM to the end of the string.

The example below, applied to the literal string "ABCDE" returns the value "BCD".

SELECT SUBSTRING("ABCDE" FROM 2 FOR 3) AS Sub FROM country

In the SELECT statement below only the second and subsequent characters of the NAME column are retrieved.

SELECT SUBSTRING(name FROM 2) FROM country

EXTRACT function
Return one field from a date value
EXTRACT (extract_field FROM column_reference)

Use EXTRACT to return the year, month, or day field from a DATE or TIMESTAMP column.

SELECT saledate,
EXTRACT(YEAR FROM saledate) AS YY,
EXTRACT(MONTH FROM saledate) AS MM,
EXTRACT(DAY FROM saledate) AS DD
FROM orders

The statement below uses a DOB column (containing birthdates) to filter to those rows where the date is in the month of May. The month field from the DOB column is retrieved using the EXTRACT function and compared to 5, May being the fifth month.

SELECT DOB, LastName, FirstName
FROM People
WHERE (EXTRACT(MONTH FROM DOB) = 5)

Conditional Expression

* function IF(Condition: Boolean, TrueResult, FalseResult): ResultType
Condition is a Boolean expression. When the function is evaluated, it returns TrueResult if Condition evaluates to true, else FalseResult. TrueResult and FalseResult need not be of the same type and result type of the IF expression may change depending on Condition.

Example:

SELECT category, suppcode, prodcode, barcode, proddesc, retprice,
costprice, IF(retprice <> 0, (1-costprice*1.125/retprice)*100, 0) as margin FROM hds1
WHERE IF(retprice <> 0, (1-costprice*1.125/retprice), 0) > 0.8) or (
IF(retprice <> 0, (1-costprice*1.125/retprice), 1) < 0.4);

* As of my knowledge, this functions is not part of standard SQL-syntax, althoug I think that MS Access already implements this.


SQL tutorial


Welcome

Welcome to a tutorial of the Structured Query Language (also known as SQL). SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see.
SQL also allows users to define the data in a database, and manipulate that data. This tutorial will describe how to use SQL, and give examples of some common functions. The SQL used in this document is standard SQL-92, and no SQL features of specific database management systems will be discussed.


Basics of the SELECT statement

In a relational database, data is stored in tables. An example table would relate Social Security Number, Name, and Address:
EmployeeAddressTable
SSN FirstName LastName Address City State
512687458 Joe Smith 83 First Street Howard Ohio
758420012 Mary Scott 842 Vine Ave. Losantiville Ohio
102254896 Sam Jones 33 Elm St. Paris New York
876512563 Sarah Ackerman 440 U.S. 110 Upton Michigan
Now, let's say you want to see the address of each employee. Use the SELECT statement, like so:

SELECT FirstName, LastName, Address, City, State FROM EmployeeAddressTable

The following is the results of your query of the database:
First Name Last Name Address City State
Joe Smith 83 First Street Howard Ohio
Mary Scott 842 Vine Ave. Losantiville Ohio
Sam Jones 33 Elm St. Paris New York
Sarah Ackerman 440 U.S. 110 Upton Michigan
To explain what you just did, you asked for the all of data in the EmployeeAddressTable, and specifically, you asked for the columns called FirstName, LastName, Address, City, and State. Note that column names and table names do not have spaces...they must be typed as one word; and that the statement ends with a semicolon (;). The general form for a SELECT statement, retrieving all of the rows in the table is:

SELECT ColumnName, ColumnName, ...
FROM TableName

To get all columns of a table without typing all column names, use:

SELECT * FROM TableName


Conditional selection

To further discuss the SELECT statement, let's look at a new example table (for hypothetical purposes only):
EmployeeStatisticsTable
EmployeeIDNo Salary Benefits Position
010 75000 15000 Manager
105 65000 15000 Manager
152 60000 15000 Manager
215 60000 12500 Manager
244 50000 12000 Staff
300 45000 10000 Staff
335 40000 10000 Staff
400 32000 7500 Entry-Level
441 28000 7500 Entry-Level

Logical Operators
There are six logical operators in SQL, and after introducing them, we'll see how they're used:
= Equal
<> Not Equal
< Less Than
> Greater Than
<= Less Than or Equal To
>= Greater Than or Equal To
The WHERE clause is used to specify that only certain rows of the table are displayed, based on the criteria described in that WHERE clause. It is most easily understood by looking at a couple of examples.
If you wanted to see the EMPLOYEEIDNO's of those making at or over $50,000, use the following:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000

Notice that the >= (greater than or equal to) sign is used, as we wanted to see those who made greater than $50,000, or equal to $50,000, listed together. This displays:
EMPLOYEEIDNO
------------
010
105
152
215
244
The WHERE description, SALARY >= 50000, is known as a condition. The same can be done for text columns:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = "Manager"

This displays the ID Numbers of all Managers. Generally, with text columns, stick to equal to or not equal to conditions, and make sure that any text that appears in the statement is surrounded by single quotes (').


More complex conditions

More complex conditions: Compound conditions

The AND operator joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed (i.e. all conditions hold true). For example, to display all staff making over $40,000, use:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE (SALARY > 40000) AND (POSITION = 'Staff')

The OR operator joins two or more conditions, but returns a row if ANY of the conditions listed hold true. To see all those who make less than $40,000 or have less than $10,000 in benefits, listed together, use the following query:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE (SALARY < 40000) OR (BENEFITS < 10000)

Note: Always use parenthesis to separate more than one condition in SQL.

AND & OR can be combined, for example:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE (POSITION = 'Manager') AND (SALARY > 60000) OR (BENEFITS > 12000)

First, SQL finds the rows where the salary is greater than $60,000 and the position column is equal to Manager, then taking this new list of rows, SQL then sees if any of these rows satisfies the previous AND condition or the condition that the Benefits column is greater then $12,000. Subsequently, SQL only displays this second new list of rows, keeping in mind that anyone with Benefits over $12,000 will be included as the OR operator includes a row if either resulting condition is True. Also note that the AND operation is done first.
To generalize this process, SQL performs the AND operation(s) to determine the rows where the AND operation(s) hold true (remember: all of the conditions is true), then these results are used to compare with the OR conditions, and only display those remaining rows where the conditions joined by the OR operator hold true.
To perform OR's before AND's, like if you wanted to see a list of employees making a large salary (>$50,000) or have a large benefit package (>$10,000), and that happen to be a manager, use parentheses:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE (POSITION = 'Manager') AND ( (SALARY > 50000) OR (BENEFIT > 10000) )


AND & BETWEEN

An easier method of using compound conditions uses IN or BETWEEN. For example, if you wanted to list all managers and staff:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff')

or to list those making greater than or equal to $30,000, but less than or equal to $50,000, use:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000

To list everyone not in this range, try:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY NOT BETWEEN 30000 AND 50000

Similarly, NOT IN lists all rows excluded from the IN list.


Using LIKE

Look at the EmployeeStatisticsTable, and say you wanted to see all people whose last names started with "L"; try:

SELECT EMPLOYEEIDNO
FROM EMPLOYEEADDRESSTABLE
WHERE LASTNAME LIKE 'L%'

The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the "L".
To find those people with LastName's ending in "L", use '%L', or if you wanted the "L" in the middle of the word, try '%L%'. The '%' can be used for any characters, in that relative position to the given characters.

Here is a more detailed explain about this command:

Use the LIKE comparison predicate to filter a table based on the similarity of a column value to a comparison value. Use of substitution characters allows the comparison to be based on the whole column value or just a portion.

SELECT *
FROM customer
WHERE (company LIKE "Adventure Undersea")

The wildcard substitution character ("%") may be used in the comparison to represent an unknown number of characters. LIKE returns a TRUE when the portion of the column value matches that portion of the comparison value not corresponding to the position of the wildcard character. The wildcard character can appear at the beginning, middle, or end of the comparison value (or multiple combinations of these positions). For example, the statement below retrieves rows where the column value begins with "A" and is followed by any number of any characters. Matching values could include "Action Club" and "Adventure Undersea", but not "Blue Sports".

SELECT *
FROM customer
WHERE (company LIKE "A%")

The single-character substitution character ("_") may be used in the comparison to represent a single character. LIKE returns a TRUE when the portion of the column value matches that portion of the comparison value not corresponding to the position of the single-character substitution character. The single-character substitution character can appear at the beginning, middle, or end of the comparison value (or multiple combinations of these positions). Use one single-character substitution character for each character to be wild in the filter pattern For example, the statement below retrieves rows where the column value begins with "b" ends with "n", with one character of any value between. Matching values could include "bin" and "ban", but not "barn".

SELECT words
FROM dictionary
WHERE (words LIKE "b_n")

Use NOT to return the converse of a LIKE comparison.

Use ESCAPE when the wildcard character "%" or "_" appear as data in the column. The ESCAPE keyword designates an escape character. In the comparison value for the LIKE predicate, the character that follows the escape character is treated as a data character and not a wildcard character. Other wildcard characters in the comparison value are unaffected.

In the example below, the "^" character is designated as the escape character. In the comparison value for the LIKE predicate ("%10^%%"), the "%" that immediately follows the escape character is treated as data in the PercentValue. This allows filtering based on the string "10%".

SELECT *
FROM Sales
WHERE (PercentValue LIKE "%10^%%" ESCAPE "^")

LIKE can be used only with CHAR or compatible data types.


JOINs

In this section, we will only discuss inner joins, and equijoins, as in general, they are the most useful.
Good database design suggests that each table lists data only about a single entity, and detailed information can be obtained in a relational database, by using additional tables, and by using a join.
First, take a look at these example tables:

AntiqueOwners

OwnerID | OwnerLastName | OwnerFirstName
01 | Jones | Bill
02 | Smith | Bob
15 | Lawson | Patricia
21 | Akins | Jane
50 | Fowler | Sam

Orders
OwnerID | ItemDesired
02 | Table
02 | Desk
21 | Chair
15 | Mirror

Antiques
SellerID | BuyerID | Item
01 | 50 | Bed
02 | 15 | Table
15 | 02 | Chair
21 | 50 | Mirror
50 | 01 | Desk
01 | 21 | Cabinet
02 | 21 | Coffee Table
15 | 50 | Chair
01 | 15 | Jewelry Box
02 | 21 | Pottery
21 | 02 | Bookcase
50 | 01 | Plant Stand

Keys
First, let's discuss the concept of keys. A primary key is a column or set of columns that uniquely identifies the rest of the data in any given row. For example, in the AntiqueOwners table, the OwnerID column uniquely identifies that row. This means two things: no two rows can have the same OwnerID, and, even if two owners have the same first and last names, the OwnerID column ensures that the two owners will not be confused with each other, because the unique OwnerID column will be used throughout the database to track the owners, rather than the names.
A foreign key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key. In DBMS-speak, this correspondence is known as referential integrity. For example, in the Antiques table, both the BuyerID and SellerID are foreign keys to the primary key of the AntiqueOwners table (OwnerID; for purposes of argument, one has to be an Antique Owner before one can buy or sell any items), as, in both tables, the ID rows are used to identify the owners or buyers and sellers, and that the OwnerID is the primary key of the AntiqueOwners table. In other words, all of this "ID" data is used to refer to the owners, buyers, or sellers of antiques, themselves, without having to use the actual names.

Performing a Join
The purpose of these keys is so that data can be related across tables, without having to repeat data in every table--this is the power of relational databases. For example, you can find the names of those who bought a chair without having to list the full name of the buyer in the Antiques table...you can get the name by relating those who bought a chair with the names in the AntiqueOwners table through the use of the OwnerID, which relates the data in the two tables. To find the names of those who bought a chair, use the following query:

SELECT OWNERLASTNAME, OWNERFIRSTNAME
FROM ANTIQUEOWNERS, ANTIQUES
WHERE (BUYERID = OWNERID) AND (ITEM = 'Chair')

Note the following about this query...notice that both tables involved in the relation are listed in the FROM clause of the statement. In the WHERE clause, first notice that the ITEM = 'Chair' part restricts the listing to those who have bought (and in this example, thereby owns) a chair. Secondly, notice how the ID columns are related from one table to the next by use of the (BUYERID = OWNERID) clause. Only where ID's match across tables and the item purchased is a chair (because of the AND), will the names from the AntiqueOwners table be listed. Because the joining condition used an equal sign, this join is called an equijoin. The result of this query is two names: Smith, Bob & Fowler, Sam.
Dot notation refers to prefixing the table names to column names, to avoid ambiguity, as such:

SELECT ANTIQUEOWNERS.OWNERLASTNAME, ANTIQUEOWNERS.OWNERFIRSTNAME
FROM ANTIQUEOWNERS, ANTIQUES
WHERE (ANTIQUES.BUYERID = ANTIQUEOWNERS.OWNERID) AND (ANTIQUES.ITEM = 'Chair')

As the column names are different in each table, however, this wasn't necessary.


DISTINCT and Eliminating Duplicates

Let's say that you want to list the ID and names of only those people who have sold an antique. Obviously, you want a list where each seller is only listed once--you don't want to know how many antiques a person sold, just the fact that this person sold one (for counts, see the Aggregate Function section below). This means that you will need to tell SQL to eliminate duplicate sales rows, and just list each person only once. To do this, use the DISTINCT keyword.
First, we will need an equijoin to the AntiqueOwners table to get the detail data of the person's LastName and FirstName. However, keep in mind that since the SellerID column in the Antiques table is a foreign key to the AntiqueOwners table, a seller will only be listed if there is a row in the AntiqueOwners table listing the ID and names. We also want to eliminate multiple occurences of the SellerID in our listing, so we use DISTINCT on the column where the repeats may occur.
To throw in one more twist, we will also want the list alphabetized by LastName, then by FirstName (on a LastName tie), then by OwnerID (on a LastName and FirstName tie). Thus, we will use the ORDER BY clause:

SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME
FROM ANTIQUES, ANTIQUEOWNERS
WHERE (SELLERID = OWNERID)
ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID

In this example, since everyone has sold an item, we will get a listing of all of the owners, in alphabetical order by last name. For future reference (and in case anyone asks), this type of join is considered to be in the category of inner joins.


Aliases & In/Subqueries

In this section, we will talk about Aliases, In and the use of subqueries, and how these can be used in a 3-table example. First, look at this query which prints the last name of those owners who have placed an order and what the order is, only listing those orders which can be filled (that is, there is a buyer who owns that ordered item):

SELECT OWN.OWNERLASTNAME As LastName, ORD.ITEMDESIRED As ItemOrdered
FROM ORDERS ORD, ANTIQUEOWNERS OWN
WHERE (ORD.OWNERID = OWN.OWNERID) AND ORD.ITEMDESIRED IN (SELECT ITEM FROM ANTIQUES)

This gives:
LastName | ItemOrdered
Smith | Table
Smith | Desk
Akins | Chair
Lawson | Mirror

There are several things to note about this query:
First, the "LastName" and "ItemOrdered" in the Select lines gives the headers on the report.
The OWN & ORD are aliases; these are new names for the two tables listed in the FROM clause that are used as prefixes for all dot notations of column names in the query (see above). This eliminates ambiguity, especially in the equijoin WHERE clause where both tables have the column named OwnerID, and the dot notation tells SQL that we are talking about two different OwnerID's from the two different tables.
Note that the Orders table is listed first in the FROM clause; this makes sure listing is done off of that table, and the AntiqueOwners table is only used for the detail information (Last Name).
Most importantly, the AND in the WHERE clause forces the In Subquery to be invoked. What this does is, the subquery is performed, returning all of the Items owned from the Antiques table, as there is no WHERE clause. Then, for a row from the Orders table to be listed, the ItemDesired must be in that returned list of Items owned from the Antiques table, thus listing an item only if the order can be filled from another owner. You can think of it this way: the subquery returns a set of Items from which each ItemDesired in the Orders table is compared; the In condition is true only if the ItemDesired is in that returned set from the Antiques table.
Also notice, that in this case, that there happened to be an antique available for each one desired...obviously, that won't always be the case. In addition, notice that when the IN, "= ANY" is used, that these keywords refer to any possible row matches, not column matches...that is, you cannot put multiple columns in the subquery Select clause, in an attempt to match the column in the outer Where clause to one of multiple possible column values in the subquery; only one column can be listed in the subquery, and the possible match comes from multiple row values in that one column, not vice-versa.
That's enough on the topic of complex SELECT queries for now. Now on to other SQL statements.


Miscellaneous SQL Statements

Aggregate Functions
I will discuss five important aggregate functions: SUM, AVG, MAX, MIN, and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.
- SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.
- AVG () gives the average of the given column.
- MAX () gives the largest figure in the given column.
- MIN () gives the smallest figure in the given column.
- COUNT(*) gives the number of rows satisfying the conditions.
Looking at the tables at the top of the document, let's look at three examples:

SELECT SUM(SALARY), AVG(SALARY)
FROM EMPLOYEESTATISTICSTABLE

This query shows the total of all salaries in the table, and the average salary of all of the entries in the table.

SELECT MIN(BENEFITS)
FROM EMPLOYEESTATISTICSTABLE
WHERE (POSITION = 'Manager')

This query gives the smallest figure of the Benefits column, of the employees who are Managers, which is 12500.

SELECT COUNT(*)
FROM EMPLOYEESTATISTICSTABLE
WHERE (POSITION = 'Staff')

This query tells you how many employees have Staff status (3).


GROUP BY & HAVING

One special use of GROUP BY is to associate an aggregate function (especially COUNT; counting the number of rows in each group) with groups of rows. First, assume that the Antiques table has the Price column, and each row has a value for that column. We want to see the price of the most expensive item bought by each owner. We have to tell SQL to group each owner's purchases, and tell us the maximum purchase price:

SELECT BUYERID, MAX(PRICE)
FROM ANTIQUES
GROUP BY BUYERID

Now, say we only want to see the maximum purchase price if the purchase is over $1000, so we use the HAVING clause:

SELECT BUYERID, MAX(PRICE)
FROM ANTIQUES
GROUP BY BUYERID
HAVING (MAX(PRICE) > 1000)


More Subqueries

Another common usage of subqueries involve the use of logical operators to allow a Where condition to include the Select output of a subquery. First, list the buyers who purchased an expensive item (the Price of the item is $100 greater than the average price of all items purchased):

SELECT OWNERID
FROM ANTIQUES
WHERE PRICE > (SELECT AVG(PRICE) FROM ANTIQUES)

The subquery calculates the average Price, and using that figure, an OwnerID is printed for every item costing over that figure. One could use DISTINCT OWNERID, to eliminate duplicates.
List the Last Names of those in the AntiqueOwners table, ONLY if they have bought an item:

SELECT OWNERLASTNAME
FROM ANTIQUEOWNERS
WHERE OWNERID = (SELECT DISTINCT BUYERID FROM ANTIQUES)

The subquery returns a list of buyers, and the Last Name is printed for an Antique Owner if and only if the Owner's ID appears in the subquery list (sometimes called a candidate list).
For an Update example, we know that the gentleman who bought the bookcase has the wrong First Name in the database...it should be John:

UPDATE ANTIQUEOWNERS
SET OWNERFIRSTNAME = 'John'
WHERE OWNERID = (SELECT BUYERID FROM ANTIQUES WHERE ITEM = 'Bookcase')

First, the subquery finds the BuyerID for the person(s) who bought the Bookcase, then the outer query updates his First Name.
Remember this rule about subqueries: when you have a subquery as part of a WHERE condition, the Select clause in the subquery must have columns that match in number and type to those in the Where clause of the outer query. In other words, if you have "WHERE ColumnName = (SELECT...);", the Select must have only one column in it, to match the ColumnName in the outer Where clause, and they must match in type (both being integers, both being character strings, etc.).


UNION & Outer Joins

There are occasions where you might want to see the results of multiple queries together, combining their output; use UNION. To merge the output of the following two queries, displaying the ID's of all Buyers, plus all those who have an Order placed:

SELECT BUYERID FROM ANTIQUEOWNERS
UNION SELECT OWNERID FROM ORDERS

Notice that SQL requires that the Select list (of columns) must match, column-by-column, in data type. In this case BuyerID and OwnerID are of the same data type (integer). Also notice that SQL does automatic duplicate elimination when using UNION (as if they were two "sets"); in single queries, you have to use DISTINCT.
The outer join is used when a join query is "united" with the rows not included in the join, and are especially useful if constant text "flags" are included. First, look at the query:

SELECT OWNERID
FROM ORDERS, ANTIQUES
WHERE (OWNERID = BUYERID)
UNION
SELECT BUYERID
FROM ANTIQUES
WHERE BUYERID NOT IN (SELECT OWNERID FROM ORDERS)

The first query does a join to list any owners who are in both tables, and putting a tag line after the ID repeating the quote. The UNION merges this list with the next list. The second list is generated by first listing those ID's not in the Orders table, thus generating a list of ID's excluded from the join query. Then, each row in the Antiques table is scanned, and if the BuyerID is not in this exclusion list, it is listed with its quoted tag. There might be an easier way to make this list, but it's difficult to generate the informational quoted strings of text.
This concept is useful in situations where a primary key is related to a foreign key, but the foreign key value for some primary keys is NULL. For example, in one table, the primary key is a salesperson, and in another table is customers, with their salesperson listed in the same row. However, if a salesperson has no customers, that person's name won't appear in the customer table. The outer join is used if the listing of all salespersons is to be printed, listed with their customers, whether the salesperson has a customer or not--that is, no customer is printed (a logical NULL value) if the salesperson has no customers, but is in the salespersons table. Otherwise, the salesperson will be listed with each customer.


Running Crosstabs (TRANSFORM...PIVOT)

Running Crosstabs queries (TRANSFORM...PIVOT)

The crosstab query is a special form of SELECT query that summarizes data in spreadsheet style, most often in time-series format. You are likely to find that 75 percent or more of the decision-support applications you create for your firm or clients include crosstab queries. In many cases, the crosstab query result set serves as the datasource for graphs and charts.

Using Crosstab Queries to Present Summary Data
Even before the early days of the personal computer and the VisiCalc spreadsheet application, managers were accustomed to viewing summary data in the row-column format of accountants' worksheets. Worksheets that are used for comparative performance analysis fall into two basic categories:
- Time-series format implies that column titles contain date intervals, such as months, quarters, or years, and that row titles designate the category of data being compared. The underlying detail data is grouped by both data category and time period. Time-series summary data often is used to prepare line graphs or bar charts with sales as the y-axis (vertical axis or abscissa) and time as the x-axis (horizontal axis or ordinate).
- Classification format uses column titles with the names of individuals, regions, divisions, or other organizational categories and data categories for the row titles. This format is restricted to a single, predetermined time period. (Multiple time periods can be represented by "stacking" worksheets with an identical format that can be consolidated by adding the values of corresponding cells.) The most common graphical representation of data from classification worksheets is the pie chart.

Today's spreadsheet applications, such as Microsoft Excel, replace the drudgery of preparing handwritten worksheets with automated computer-based procedures. However, the majority of the detail information that is needed to prepare summary data for management is available only in the fields of tables of relational accounting databases. Conventional SQL statements return data in relational format, not spreadsheet format. Thus, a substantial amount of data manipulation ordinarily is required to create a time-series or classification spreadsheet from relational data.
The following sections describe how summary data is returned by SQL and how you use SQL's TRANSFORM and PIVOT statements to automate the denormalization of relational data to spreadsheet format.

Summary Data Created by the GROUP BY Clause
In order to fully explain the functionality of the TRANSFORM...PIVOT statement, I will start by defining this simple SQL query statement:

SELECT CUSTNO, SALEDATE, AMOUNTPAID FROM ORDERS ORDER BY CUSTNO, SALEDATE

This query applied against the Orders table that comes with Delphi will give us a result like this:

As you can see, the result of the query is ordered first by CUSTNO, and next by SALEDATE, and also will show the amount paid by the Customer in that order.

What I want now, is this same statement, but this time, the AmountPaid field must be grouped by year in order to obtain the total sales for every customer and for all the years. We can do this with the following statement.

SELECT CUSTNO, EXTRACT(YEAR FROM SALEDATE) AS SALEYEAR, SUM(AMOUNTPAID) FROM ORDERS GROUP BY CUSTNO, 2 ORDER BY CUSTNO, 2

This SQL statement can be interpreted as this:

· In the SELECT section, the field CUSTNO is included in the result set.
· Also, the SQL function EXTRACT is used to extract only the year part of the field SALEDATE
· Next, the aggregate function SUM(AmountPaid) is used in order to sum this field for the grouping of the first two columns as is defined later in the GROUP BY clause.
· The section FROM defines the table to query, that in this case is ORDERS.
· The GROUP BY clause is used to indicate the fields that the grouping will be done. In this case, when an expression is used in the included fields in the SELECT clause, that column must be referenced by its ordinal position, starting from 1. Then in this case, the EXTRACT function is used in the second column. Also, the grouping could be defined this way: GROUP BY 1,2
· Finally, the result set is ordered by the first two columns also. That is the same order of the grouping in order to get the desired results.

The result of querying the table with this SQL statement will give the result shown in the following figure:

It's clear from the rows returned by the preceding query and illustrated in previous figure, that a substantial rearrangement of worksheet cells is required if you import this data into a worksheet that is intended to display data in time-series format.
In this example, it is clear that you may want to show the sum of sales for every year and for every customer but listed for years and one year for every column.

Using TRANSFORM and PIVOT to Create Crosstab Queries
SQL's crosstab query feature solves the data cell rearrangement problem. Two SQL keywords, TRANSFORM and PIVOT, handle the denormalization of the data. The elements of an SQL crosstab query are as follows:
· The object of the TRANSFORM predicate specifies the values for the data cells and must contain one of the SQL aggregate functions, such as SUM() or AVG().
· The SELECT statement designates the row titles. The field you specify with the SELECT statement must be the same as the GROUP BY field.
· The FROM statement specifies each table involved in the query. The tables may be identified by the INNER JOIN statements, without the conventional comma-separated table_names list if you want to do joinings.
· The GROUP BY clause aggregates row data. Only one field is permitted in the GROUP BY clause of a conventional crosstab query.
· The optional ORDER BY clause sorts the rows by the value of the single field specified in the SELECT and GROUP BY statements.
· The PIVOT statement determines the column grouping and supplies the column titles. Column titles consist of the value of the grouping criterion. The object of the PIVOT predicate takes the place of the second GROUP BY field of the SQL statements of the two examples in the preceding section.

The SQL syntax for performing the crosstab query is this:

TRANSFORM SUM(AMOUNTPAID) SELECT CUSTNO FROM ORDERS GROUP BY CUSTNO ORDER BY CUSTNO
PIVOT EXTRACT(YEAR FROM SALEDATE)

This SQL statement will give us this crosstab query as a result set:

As you can see, the first column show the customer ID, and the rest of the columns shows the sum of sales for that customer for every year involved.

With the previous syntax explained, will create columns for every distinct year found on the field SALEDATE for all the orders in the database. This is known as dynamic columns.

Creating Fixed Column Headers with the IN() Predicate
If we want to show the monthly sales, then we need to change the SQL statement to something like this:

TRANSFORM SUM(AMOUNTPAID) SELECT CUSTNO FROM ORDERS GROUP BY CUSTNO ORDER BY CUSTNO
PIVOT FormatDateTime("mmm yy", SaleDate)

And you will have the following result set:

In this crosstab query, the column titles appear in the strange sequence shown in the previous Figure. The pivot statement automatically orders the columns alphabetically.

You could solve the column sequence problem by changing the PIVOT statement to PIVOT FormatDateTime("mm/yy", SaleDate) to provide column titles 01/95 through 12/96, but many people prefer three-letter abbreviations for months. SQL lets you use the IN() predicate to create fixed column names that appear in any sequence you specify. The following example demonstrates a PIVOT...IN statement that creates monthly column titles for any year:

TRANSFORM SUM(AMOUNTPAID) SELECT CUSTNO FROM ORDERS GROUP BY CUSTNO ORDER BY CUSTNO
PIVOT FormatDateTime("mmm", SaleDate) IN ("Ene", "Feb", "Mar", "Abr", "May", "Jun", "Jul", "Ago", "Sep", "Oct", "Nov", "Dic")

The preceding PIVOT statement gives the more satisfactory result shown in the following Figure:

The only restriction on the values of the IN() predicate's arguments is that, except for case, each value must exactly match the values returned by the formatted PIVOT statement. If you misspell an argument value, you will find that the data rows of the column with the mistaken header are empty.


AND & BETWEEN

AND and BETWEEN usage

К началу

An easier method of using compound conditions uses IN or BETWEEN. For example, if you wanted to list all managers and staff:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff')

or to list those making greater than or equal to $30,000, but less than or equal to $50,000, use:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000

To list everyone not in this range, try:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY NOT BETWEEN 30000 AND 50000

Similarly, NOT IN lists all rows excluded from the IN list.


Aliases & In/Subqueries

In this section, we will talk about Aliases, In and the use of subqueries, and how these can be used in a 3-table example. First, look at this query which prints the last name of those owners who have placed an order and what the order is, only listing those orders which can be filled (that is, there is a buyer who owns that ordered item):

SELECT OWN.OWNERLASTNAME As LastName, ORD.ITEMDESIRED As ItemOrdered
FROM ORDERS ORD, ANTIQUEOWNERS OWN
WHERE (ORD.OWNERID = OWN.OWNERID) AND ORD.ITEMDESIRED IN (SELECT ITEM FROM ANTIQUES)

This gives:
LastName ItemOrdered
--------- ------------
Smith Table
Smith Desk
Akins Chair
Lawson Mirror

There are several things to note about this query:
First, the "LastName" and "ItemOrdered" in the Select lines gives the headers on the report.
The OWN & ORD are aliases; these are new names for the two tables listed in the FROM clause that are used as prefixes for all dot notations of column names in the query (see above). This eliminates ambiguity, especially in the equijoin WHERE clause where both tables have the column named OwnerID, and the dot notation tells SQL that we are talking about two different OwnerID's from the two different tables.
Note that the Orders table is listed first in the FROM clause; this makes sure listing is done off of that table, and the AntiqueOwners table is only used for the detail information (Last Name).
Most importantly, the AND in the WHERE clause forces the In Subquery to be invoked. What this does is, the subquery is performed, returning all of the Items owned from the Antiques table, as there is no WHERE clause. Then, for a row from the Orders table to be listed, the ItemDesired must be in that returned list of Items owned from the Antiques table, thus listing an item only if the order can be filled from another owner. You can think of it this way: the subquery returns a set of Items from which each ItemDesired in the Orders table is compared; the In condition is true only if the ItemDesired is in that returned set from the Antiques table.
Also notice, that in this case, that there happened to be an antique available for each one desired...obviously, that won't always be the case. In addition, notice that when the IN, "= ANY" is used, that these keywords refer to any possible row matches, not column matches...that is, you cannot put multiple columns in the subquery Select clause, in an attempt to match the column in the outer Where clause to one of multiple possible column values in the subquery; only one column can be listed in the subquery, and the possible match comes from multiple row values in that one column, not vice-versa.
That's enough on the topic of complex SELECT queries for now. Now on to other SQL statements.


ALTER TABLE

ALTER TABLE table_reference DROP [COLUMN] column_reference | ADD [COLUMN] column_reference [, DROP [COLUMN] column_reference | ADD [COLUMN] column_reference...]

Description

Use the ALTER TABLE statement to add a column to or delete a column from an existing table. It is possible to delete one column and add another in the same ALTER TABLE statement. Note: Actually, this SQL statement does not alter any table, it only fires event OnAlterTable where you can implement the specific action for your used database.

The DROP keyword requires only the name of the column to be deleted. The ADD keyword requires the same combination of column name, type, and possibly dimension definition as CREATE TABLE when defining new columns. See the section on defining column types for the specific syntax of all supported column types.

The statement below deletes the column FULLNAME and adds the column LASTNAME.

ALTER TABLE "Names.dbf"

DROP FullName, ADD LastName CHAR(25)

It is possible to delete and add a column of the same name in the same ALTER TABLE statement. This allows quick redefinition of columns while still in the database design stages.

ALTER TABLE "Names.dbf"

DROP LastName, ADD LastName CHAR(30)

The valid syntax data types supported in the current release are :

CHAR( n ), where n = number of characters for this field
INTEGER
SMALLINT
BOOLEAN
DATE
TIME
DATETIME
MONEY
FLOAT
FLOAT(n), where n = number of digits
FLOAT(n,p), where n = number of digits, p = precision
Also, for float types you can use the reserved word: NUMERIC:
NUMERIC
NUMERIC(n), where n = number of digits
NUMERIC(n,p), where n = number of digits, p = precision
AUTOINC, autoincrement fields
BLOB( n ), where n = 1 Memo
= 2 Formatted Memo
= 3 Binary
= 4 OLE
= 5 Graphic/Binary


CREATE INDEX statement

Fires event OnCreateIndex where you can create a new secondary index for any table.

CREATE [UNIQUE] [ASC | DESC] INDEX index_reference ON table_reference (column_reference [,column_reference...])

Description

Use the CREATE INDEX statement to create a secondary index for an existing table. Index names may not have embedded spaces.

Use UNIQUE to create an index that raises an error if rows with duplicate column values are inserted. By default, indexes are not unique.

Use ASC (or ASCENDING) to create an index that orders data in an ascending direction (smallest to largest). DESC (or DESCENDING) creates a descending ordering (largest to smallest). When a direction imperative is not specified, ASC is the implied default.

CREATE INDEX custdate ON "orders.dbf" (custno, saledate)

The following statement creates a unique secondary index.

CREATE UNIQUE INDEX namex ON "employee.dbf" (last_name)


CREATE TABLE statement

CREATE TABLE table_reference (column_definition [, column_definition,...] [ primary_key_constraint]) ;

Description
Use the CREATE TABLE statement to create any table, define its columns, and define a primary key constraint.

The table name reference for CREATE TABLE must comply with the rules described in the section on naming conventions. Table names with embedded spaces must be enclosed in brackets ( []).

Column definitions consist of a comma-separated list of combinations of column name, data type, and (if applicable) dimensions. The list of column definitions must be enclosed in parentheses. The number and type of dimensions that must be specified varies with column type. See the section on defining column types for specific syntax of all supported column types.

Use the PRIMARY KEY keyword to create a primary index for the new table.The following statement creates a (possible) Paradox table with a PRIMARY KEY constraint on the LAST_NAME and FIRST_NAME columns:

CREATE TABLE "employee.dbf"
(
last_name CHAR(20),
first_name CHAR(15),
salary NUMERIC(10,2),
dept_no SMALLINT
PRIMARY KEY (last_name, first_name)
)

You will receive in event OnCreateTable, the full name of the table as is given in the SQL syntax, example:

CREATE TABLE "c:\temp\users\employee.dbf"
(
last_name CHAR(20),
first_name CHAR(15),
salary FLOAT(10,2),
amount NUMERIC(10,2),
dept_no SMALLINT
PRIMARY KEY (last_name, first_name)
)

The valid syntax data types supported in the current release are :

CHAR( n ), where n = number of characters for this field
INTEGER
SMALLINT
BOOLEAN
DATE
TIME
DATETIME
MONEY
FLOAT
FLOAT(n), where n = number of digits
FLOAT(n,p), where n = number of digits, p = precision
Also, for float types you can use the reserved word: NUMERIC:
NUMERIC
NUMERIC(n), where n = number of digits
NUMERIC(n,p), where n = number of digits, p = precision
AUTOINC, autoincrement fields
BLOB( n ), where n = 1 Memo
= 2 Formatted Memo
= 3 Binary
= 4 OLE
= 5 Graphic/Binary


DELETE

The delete statement deletes one or more rows from a table.

DELETE FROM table_reference [WHERE predicates]

DELETE FROM survey WHERE name Like '%LOPEZ%'

The optional where clause restricts row deletions to a subset of rows in a table. If no WHERE clause is defined, all rows in the table will be deleted.

All expressions allowed in WHERE clause in SELECT statement are allowed here, including subqueries. When a subquery is defined, only one column must be returned and only one subquery is allowed.

DELETE FROM routes WHERE area >= (SELECT Avg(Area) FROM routes)


DROP INDEX statement

Fires event OnDropIndex where you can delete an index.

DROP INDEX table_reference.index_reference | PRIMARY

Description

Use the DROP INDEX statement to delete a primary or secondary index.

DROP INDEX "employee.dbf" namex


DROP TABLE statement

Fires event OnDropTable where you can drop or delete a table.

DROP TABLE table_reference

Description

Use the DROP TABLE statement to delete an existing table. The statement below drops a table:

DROP TABLE "employee.dbf"


FROM clause

The FROM clause identifies the table or tables from which the data is retrieved

- The table names referes to the Alias.

- Table names cannot contains spaces in between

- Table name followed by an alias is supported: SELECT * FROM Customer c, Orders o...

JOINS
Joins syntax in FROM clause is supported. RIGHT [OUTER] JOIN and FULL [OUTER] JOIN are not yet supported
Syntax:

- table1 [INNER] JOIN table2 ON (table1.common_field=table2.common_field) [ [INNER] JOIN table3 ON (table2.common_field=table3.common_field) ] ...

- table1 LEFT [OUTER] JOIN table2 ON (table1.common_field=table2.common_field) [ LEFT [OUTER] JOIN table3 ON (table2.common_field=table3.common_field) ] ...

Some examples

SELECT * FROM Customer, Orders...

SELECT * FROM Customer c, Orders o

SELECT * FROM Customer INNER JOIN Orders ON (Customer.CustNo = Orders.CustNo) INNER JOIN Items i ON (Orders.OrderNo = i.OrderNo)

SELECT * FROM Customer LEFT JOIN Orders ON (Customer.CustNo = Orders.CustNo) LEFT OUTER JOIN Items i ON (Orders.OrderNo = i.OrderNo)

/* This syntax is also supported: */
SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON (t1.CommonField1 = t2.CommonField1) AND (t1.CommonField2 = t2.CommonField2) INNER JOIN...


GROUP BY & HAVING

One special use of GROUP BY is to associate an aggregate function (especially COUNT; counting the number of rows in each group) with groups of rows. First, assume that the Antiques table has the Price column, and each row has a value for that column. We want to see the price of the most expensive item bought by each owner. We have to tell SQL to group each owner's purchases, and tell us the maximum purchase price:

SELECT BUYERID, MAX(PRICE)
FROM ANTIQUES
GROUP BY BUYERID

Now, say we only want to see the maximum purchase price if the purchase is over $1000, so we use the HAVING clause:

SELECT BUYERID, MAX(PRICE)
FROM ANTIQUES
GROUP BY BUYERID
HAVING (MAX(PRICE) > 1000)


GROUP BY clause

Combines rows with column values in common into single rows.

SELECT ... FROM ... [WHERE ...] GROUP BY list_fields

- GROUP BY clause is optional.
- HAVING clause is supported.
- Field(s) in GROUP BY clause must be present on first columns in SELECT clause.
- Full expressions including aggregate function in SELECT clause are accepted.

Examples:

SELECT customer_code, Count(*) AS num_requests, SUM(units) AS quantity FROM requests GROUP BY customer_code

/* Aggregate functions embedded in expressions are supported */
SELECT CAST(SUM(AmountPaid) / COUNT(*) AS MONEY) FROM ORDERS

SELECT CUSTNO, SUM(AMOUNTPAID) / SUM(ITEMSTOTAL) As Rate FROM ORDERS GROUP BY CUSTNO

SELECT date, count(*) AS num_requests FROM requests GROUP BY date

SELECT date, SUM(units) AS quantity FROM requests GROUP BY date;

SELECT date,customer_code, COUNT(*) AS num_requests, SUM(units) AS quantity
FROM requests GROUP BY date, customer_code
WHERE date < #1/6/1999#

SELECT date, COUNT(*) AS requests, SUM(units) AS subtotal, MIN(units) AS minimum,
MAX(units) AS maximum, AVG(units) AS average
FROM requests GROUP BY date

SELECT Customer.CustNo, Sum(Orders.AmountPaid), Min(Orders.AmountPaid), Max(Orders.AmountPaid), Avg(AmountPaid), Count(*)
FROM Customer
INNER JOIN Orders ON (Customer.CustNo = Orders.CustNo)
GROUP BY Customer.CustNo HAVING Sum(Orders.AmountPaid) > 50000


INSERT

Adds one or more new rows of data in a table

INSERT INTO table_reference (columns_list) VALUES (update_expressions)

Description

Use the INSERT statement to add new rows of data to a table.

Use a dataset alias (defined in property DataSets) in the INTO clause to specify the DataSet to receive the incoming data.

The columns list is a comma-separated list, enclosed in parentheses, of columns in the table and is not optional. The VALUES clause is a comma-separated list of constants or expressions, enclosed in parentheses. If you define (*) as the columns list, incoming update values (update expressions) are stored in fields as they are defined sequentially in the table structure. Update expressions are applied to columns in the order the update expressions are listed in the VALUES clause. There must also be as many update expressions as there are columns in the table.

INSERT INTO holdings (*) VALUES (4094095, "BORL", 5000, 10.500, #1/2/1998#)

If an explicit columns list is stated, incoming update expressions (in the order they appear in the VALUES clause) are stored in the listed columns (in the order they appear in the columns list).

INSERT INTO customer (custno, company) VALUES (9842, "Borland International, Inc.")

To add rows to one table from another, omit the VALUES keyword and use a subquery as the source for the new rows. The subquery must be enclosed between parentheses.

INSERT INTO customer (custno, company) (SELECT custno, company FROM oldcustomer)


ORDER BY clause

ORDER BY clause is used to designate how the records must be classified or sorted in the set of records.
(...) ORDER BY field1 [ASC/DESC][,field2 [ASC/DESC]...]

- ORDER BY clause is optional.
- Single fields listed in SELECT clause can be used in ORDER BY clause.
- Multi-columns sort is supported.
- If an expression is defined in SELECT clause for one column, then you must refer to that column by its ordinal position (starting from 1 for first column) or by its alias.
- ASC/DESC are fully supported

SELECT firstname, lastname, phone FROM customer ORDER BY firstname, lastname

SELECT * FROM requests ORDER BY date DESC

SELECT * FROM credits ORDER BY lastname, firstname, born_date DESC

SELECT Sales + Bonus As TotalSales FROM customers ORDER BY TotalSales DESC

SELECT Length / 2.54 As Inches, Area, Perimeter FROM Lots ORDER BY 1 DESC, Area ASC, 3


Reserved words

Below is the list of words reserved by SQL. Avoid using these reserved words for the names of tables or columns or columns alias.

SELECT
DISTINCT
TRUE
FALSE
AND
OR
NOT
FROM
WHERE
ORDER
BY
ASC
DESC
AS
INNER
OUTER
FULL
JOIN
ON
GROUP
ANY
ALL
SUM
AVG
COUNT
MIN
MAX
STDEV
BETWEEN
IN
LIKE
LEFT
RIGHT
HAVING
LEADING
TRAILING
BOTH
TRIM
EXTRACT
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
SUBSTRING
FOR
DELETE
UPDATE
INSERT
INTO
VALUES
SET
CAST
CHAR
INTEGER
BOOLEAN
DATE
DATETIME
TIME
FLOAT
NUMERIC
ESCAPE
CREATE
TABLE
SMALLINT
MONEY
AUTOINC
PRIMARY
KEY
BLOB
NOW
INDEX
UNIQUE
DROP
TRANSFORM
PIVOT
MERGE
WITH
IS
NULL
MOD
DIV
SHL
SHR
ALTER
COLUMN
ADD
APPEND
CASE
WHEN
THEN
ELSE
END
PACK
ZAP
REINDEX
STRING
FLOAT
INTEGER
BOOLEAN
FALSE
TRUE
TRUNC
ROUND
ABS
ARCTAN
COS
EXP
FRAC
INT
LN
PI
SIN
SQR
SQRT
POWER
UPPER
LOWER
COPY
POS
LENGTH


SELECT clause

The SELECT clause defines the list of items returned by the SELECT statement.

- Semicolon at the end of the SELECT statement is optional (;).

- Case insensitive.

- Full expressions in every column are supported.

- Operators in expressions : +, -, *, /, ^.

- Embedded functions in expressions : TRUNC, ROUND, ABS, ARCTAN, COS, EXP, FRAC, INT, LN, PI, SIN, SQR, SQRT, POWER, UPPER, LOWER, COPY, POS, LENGTH.

- Additional functions supported: LEFTS, RIGHTS, NOW; Pascal Functions: FORMAT, FORMATDATETIME, FORMATFLOAT.

- The TRIM SQL function: TRIM([LEADING|TRAILING|BOTH] trimmed_char FROM column_reference).

- The EXTRACT SQL function is supported.

- Also is supported the SUBSTRING SQL function.

- Aggregate functions fully supported: SUM, MIN, MAX, AVG, COUNT(*).

- DISTINCT predicate is supported.

- Standard characters for default identifiers are recognized: ['A'..'Z', 'a'..'z', '0'..'9', '_']. Other characters are not allowed: SPACEBAR, '(', ')', etc.

- Extended identifiers are recognized with the notation [Field Name]. This Identifiers can contain any character between '[' and ']', except '[]' and the identifier must begin with 'A'..'Z' or 'a'..'z'.
TIP.- This notation is the only way to include in the SQL syntax, fields that are also reserved words in the SQL language. Example:
SELECT * FROM LINES WHERE [COUNT] > 2;
In the previous example, if you don't use the notation [COUNT], then this will raise a syntax error exception, due to that COUNT is a reserved word in the SQL language.

- RequestLive property not implemented yet.

- CAST function can be used to cast a column to a specific type: the only types supported are: CHAR(n), INTEGER, BOOLEAN, DATE, TIME, DATETIME, FLOAT and must be used only in SELECT clause. Ex.: "SELECT CAST(LastInvoiceDate + 28 AS DATE)". This example is not syntactically valid because it has embedded CAST function in another function: "SELECT saledate, SUBSTRING(CAST(CAST(saledate AS DATE) AS CHAR(10)) FROM 1 FOR 1) FROM orders;" The CHAR casted type explicity must have specified the length of the field: "SELECT CAST(country AS CHAR(10)) FROM customer ;". This example is not syntactically valid : "SELECT CAST(lastinvoicedate AS CHAR) FROM customer ;". You don't need to use CAST function in expression in order to cast a field or expression to a specific type. This is done automatically and allways the rule is that casting is done to the type of higher precedence: STRING -> FLOAT -> INTEGER -> BOOLEAN. Example: a1 + a2; if a1 is of string type and a2 is of float, integer, or boolean type, then a2 will be converted to string and a string concatenation will be in effect and the expression will return a string. if a1 is a float and a2 is an integer, then a sum will take place and the expression will return a FLOAT.

Examples of syntax supported:

SELECT * FROM Customer

SELECT * FROM Biolife

/*
Example with distinct
*/
SELECT DISTINCT AuthorID FROM FilesAut ORDER BY AuthorID

/*
Example with distinct in an aggregate. In this example, will count only the pricelist field that have different values.
*/
SELECT COUNT(DISTINCT pricelist) FROM sales

SELECT [Species No],Category, Common_Name, [Species Name], [Length (cm)], Length_In FROM Biolife

SELECT (AmountPaid / 1000) As Thousands, (AmountPaid - ItemsTotal), ShipDate As SD, SaleDate FROM Orders

SELECT (TRIMDC(Sales) / 100) As SSales FROM Invoices

SELECT Customer.CustNo, Company, Addr1, LastInvoiceDate FROM Customer

SELECT CustNo As C, Company As Co, Addr1, LastInvoiceDate As LID FROM Customer

SELECT AmountPaid / 1000 As Thousands, (AmountPaid - ItemsTotal), ShipDate As SD, SaleDate FROM Orders

SELECT FormatDateTime("dd/mmm/yyyy", ShipDate), FormatDateTime('dd of mmm of yyyy', Saledate) FROM Orders

SELECT c.CustNo, Sum(AmountPaid), Avg(AmountPaid), Min(AmountPaid), Count(*) FROM Customer c, Orders c WHERE (c.CustNo = Orders.CustNo) GROUP BY c.CustNo;

SELECT Customer.*, Orders.AmountPaid From Customer c, Orders o WHERE (c.CustNo = o.CustNo)

SELECT TRIM(TRAILING 'K' FROM City) AS TrimmedCity FROM customer
SELECT TRIM(LEADING ' ' FROM City) AS TrimmedCity FROM customer
SELECT TRIM(BOTH 'A' FROM City) AS TrimmedCity FROM customer


Subqueries (or Subselects)

Subqueries are supported.
SELECT ... FROM ... WHERE expression operator [ANY/ALL] (Subquery)

- Subqueries in the result set are allowed
- Multiples subqueries in the WHERE clause allowed.
- The subqueries are executed first, then the main query.
- ANY/ALL are fully supported. If it is not specified then ANY is the default.
- Logical operators >, >=, <, <=, =, <>, IN, can be used in WHERE clause
- The subquery must be enclosed in parenthesis.
- EXISTS predicate not supported yet.
- SOME keyword not supported althoug it seems that it is equivalent to ANY.

SELECT (SELECT SUM(amountpaid) FROM customer WHERE custno BETWEEN 1000 AND 2000) /
(SELECT Count(*) FROM customer WHERE custno BETWEEN 1000 AND 2000) As PartialAverage
FROM customer

SELECT * FROM Customer WHERE (CustNo > (SELECT AVG(CustNo) FROM Customer)) AND (CustNo < (SELECT MAX(CustNo) FROM Customer))

SELECT * FROM Orders WHERE AmountPaid > (SELECT AVG(AmoungPaid) FROM Orders WHERE OrderNo > 1300)

SELECT * FROM Orders WHERE AmountPaid > ANY (SELECT AmountPaid FROM Orders WHERE OrderNo BETWEEN 1000 AND 3000)

SELECT * FROM Orders WHERE AmountPaid > ALL (SELECT AmountPaid FROM Orders WHERE OrderNo BETWEEN 1000 AND 3000)

SELECT * FROM Orders WHERE (OrderNo > 1000) And ( AmountPaid > ALL (SELECT AmountPaid FROM Orders WHERE OrderNo BETWEEN 1000 AND 3000) )

SELECT * FROM Customer WHERE custno >= ALL (SELECT CustNo FROM customer WHERE City IN ("Freeport", "Christiansted", "Kailua-Kona", "Giribaldi", "Kitchener", "Negril"))

SELECT * FROM Files WHERE fileID IN (SELECT fileID FROM FilesAut WHERE AuthorID = 2912)


TRANSFORM_PIVOT

Creates a browse of a crosstab query.

Sintax

TRANSFORM AggregateFunction
SelectStatement
PIVOT DynamicField [IN (value1[, value2[, ...]])]

The TRANSFORM instruction is build with the following parts:

Part Description
AggregateFunction An aggregate SQL function that acts on the selected information
SelectStatement A SELECT instruction.
DynamicField The field or expression that you want to use to create column headers in the result set.
value1, value2 Fixed values used to create the column headers.

Comments

When you resume data using a crosstab query, you select values of the fields or expressions by defining as column headers in such a way that yu can see the information in a more compact way as compared to a normal SELECT.

The TRANSFORM instruction is optional, but when it is included as the first instruction in a SQL string, it must precede a SELECT instruction that defines the fields used as column headers and a GROUP BY clause that defines the column grouping. Optionally, you can included other clauses, like the WHERE clause, that will define a selection criteria. Also you can use subqueries as predicates (especifically, that that belong to the WHERE clause) in a crosstab query.

The returned values in DynamicFields are used as column headers in the result set. By example, doing dynamic the sellings amounts in one month in a crosstab query, you can create 12 columns. You can restrict DynamicField to create headers with fixed values (value1, value2) enumerating them in an optional IN clause. Also you can include fixed values that does not have corresponding values in the database in order to create additional columns.


UNION & Outer Joins

There are occasions where you might want to see the results of multiple queries together, combining their output; use MERGE. To merge the output of the following two queries, displaying the ID's of all Buyers, plus all those who have an Order placed:

MERGE SELECT BUYERID FROM ANTIQUEOWNERS
WITH SELECT OWNERID FROM ORDERS;

Notice that SQL requires that the Select list (of columns) must match, column-by-column, in data type. In this case BuyerID and OwnerID are of the same data type (integer). Also notice that SQL does automatic duplicate elimination when using MERGE (as if they were two "sets"); in single queries, you have to use DISTINCT.
The outer join is used when a join query is "united" with the rows not included in the join, and are especially useful if constant text "flags" are included. First, look at the query:

MERGE
SELECT OWNERID
FROM ORDERS, ANTIQUES
WHERE (OWNERID = BUYERID)
WITH
SELECT BUYERID
FROM ANTIQUES
WHERE BUYERID NOT IN (SELECT OWNERID FROM ORDERS)

The first query does a join to list any owners who are in both tables, and putting a tag line after the ID repeating the quote. The MERGE merges this list with the next list. The second list is generated by first listing those ID's not in the Orders table, thus generating a list of ID's excluded from the join query. Then, each row in the Antiques table is scanned, and if the BuyerID is not in this exclusion list, it is listed with its quoted tag. There might be an easier way to make this list, but it's difficult to generate the informational quoted strings of text.
This concept is useful in situations where a primary key is related to a foreign key, but the foreign key value for some primary keys is NULL. For example, in one table, the primary key is a salesperson, and in another table is customers, with their salesperson listed in the same row. However, if a salesperson has no customers, that person's name won't appear in the customer table. The outer join is used if the listing of all salespersons is to be printed, listed with their customers, whether the salesperson has a customer or not--that is, no customer is printed (a logical NULL value) if the salesperson has no customers, but is in the salespersons table. Otherwise, the salesperson will be listed with each customer.


UNION statement

The UNION statement is a command that will append one result set, to another result set. The number of columns in the first result set to append, must correspond with the number of columns in the second result set. Also, the type of fields must correspond: strings with strings, float with floats, etc.

The UNION statement have this syntax:

sql_statement; UNION another_sql_statement

Examples :

SELECT * FROM Orders WHERE custno > 5000 ORDER BY SaleDate;
UNION SELECT * FROM Orders WHERE CustNo BETWEEN 1000 AND 3000

In general, you can mix in every sql statement, all the syntax supported, including: subqueries, JOINing, GROUPing, ORDERing, etc.
Of course, you can UNION with sources coming from different tables and not only from the same table as with the example shown.


UPDATE

Use the UPDATE statement to modify one or more column values in one or more existing rows in a table.
UPDATE table_reference SET column_reference = expression [, column_reference = expression...] [WHERE predicate]
Use a table reference in the UPDATE clause to specify the table to receive the data changes.
The SET clause is a comma_separated list of update expressions. Each expression is composed of the name of a column in 'table_reference', the assignment operator '=' and the update expression for that column.

- All expressions allowed in WHERE clause in SELECT statement are allowed here, including subqueries. When a subquery is defined, only one column must be returned and only one subquery is allowed.

UPDATE survey SET mark = 'X', total = qty * value WHERE area >= (SELECT Avg(Area) FROM survey)


WHERE clause

Specifies filtering condition for SELECT statement. Consists of a boolean expression that will evaluate to true or false. Rows that evaluate to true will be included in the result set, and rows that evaluate to false, not.

- WHERE clause is optional.

- Multiple predicates separated by logical operators OR, AND or NOT must be enclosed in parenthesis. They also can be negated with the NOT operator.

- Dates constants must be enclosed in pragmas (#)

- String constant must be enclosed in single or double quotations ('this is a string', "this is another valid string").

- IN, BETWEEN and LIKE are fully supported. LIKE with ESCAPE [escape_char syntax si supported]

- Where clause can be used for joining tables when used with '=' and expressions can be used for concatenating more than one column. Left side expression must have only references to first table, right side expression must have only references to right table and so on for the next '='. Example: SELECT * FROM ... WHERE (UPPER(c.name) + UPPER(c.lastname) = UPPER(o.FullName)) And (c.CustNo = o.CustNo);

- Logical operators allowed in logical expressions: <, >, <=, >=, <>

- '||' also can optionally be used for concatenating fields instead of '+' operator. This is only for supporting standard syntax.

Examples follow:

SELECT * FROM Customer WHERE name = 'ALFRED'

SELECT Sales + Bonus As TotalSales FROM customers WHERE TotalSales > 1000

SELECT * FROM customer WHERE firstname || lastname = "John Doe"

SELECT * FROM credits WHERE (county='MADRID') OR (county='VALENCIA') OR (county='BARCELONA')

/* Note: Allways use parenthesis to separate logical operators OR, AND or NOT. */

SELECT firstname, lastname FROM credits WHERE age >= 18

SELECT * FROM credits WHERE (age >= 18 ) AND (age <= 45)

SELECT * FROM credits WHERE age BETWEEN 18 AND 45

SELECT * FROM dairy WHERE date = #7/1/1999#
/* Note: Dates allways in the format mm/dd/yyyy (full year). */

SELECT * FROM dairy WHERE date <= #12/31/1999#

SELECT * FROM dairy WHERE date BETWEEN #7/1/1999# AND #7/31/1999#

SELECT * FROM customer WHERE firstname LIKE 'AL%'

Note: The operator LIKE is more flexible than operator '=', because LIKE can do more powerfull selection with wildcards. Two wildcards characters are % (percent) and '_' (underline). The percent symbol designates any number of characters and the underlined character represent only one character. They can be combined in an expression and repeated as many times as you wish.
ESCAPE syntax is supported.

SELECT * FROM Sales WHERE (PercentValue LIKE "%10$%%" ESCAPE "$")

/* Same result obtained with:*/ SELECT * FROM customer WHERE LEFTt(firstname, 2) = 'AL'

SELECT * FROM customer WHERE firstname LIKE '%AL'

/* Same result obtained with : */ SELECT * FROM Customer WHERE Right(nombre, 2) = 'AL'

SELECT * FROM customer WHERE firstname LIKE '%AL%'

/* Same result obtained with :*/ SELECT * FROM customer WHERE Pos('AL', firstname) > 0

SELECT * FROM customer WHERE firstname LIKE 'C%N'

/* Same result obtained with : */ SELECT * FROM customer WHERE (LEFT( firstname,1) = 'C') And (RIGHT(firstname,1) = 'N')

SELECT * FROM customer WHERE lastname LIKE '%EZ'

SELECT * FROM customer WHERE firstname LIKE '%ZAMO%'

SELECT * FROM customer WHERE firstname LIKE 'b_n'

SELECT * FROM customer WHERE firstname LIKE 'b_n%zuela%diaz'

SELECT * FROM customer WHERE county IN ('MADRID', 'BARCELONA', 'VALENCIA','TOLEDO', 'AVILA')

SELECT * FROM customer WHERE county NOT IN ('MADRID', 'BARCELONA')

SELECT * FROM customer where CustNo IN (1317, 1456, 2376, 1318)

SELECT * FROM Customer c, Orders o, Items i, Parts p WHERE (c.CustNo = o.CustNo) And (o.OrderNo = i.OrderNo) And (i.PartNo = p.PartNo) And (c.CustNo > 1300) And (c.CustNo < 2000)