Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.
The information contained in this document applies to:
- PrimeBase SQL Database Server 4.0, build #4009 and higher
- PrimeBase Virtual Machine 4.0, build #4009 and higher
Last major update to this document on March 2nd, 2001.
1. Introduction
2. Basic Elements
BOOLEAN, CHAR, CURSOR, DATE, DECIMAL, FLOAT, GENERIC, INTEGER, LONGBIN, LONGCHAR, MONEY, OBJNAME, REAL10, REAL12, SMFLOAT, SMINT, TIME, TIMESTAMP, TINYINT, UNICODE, VARBIN, VARCHAR
Integer, Decimal, Money, Floating-point, Boolean, Character String, Binary, Unicode, Date, Time, Datetime
Boolean Operators
Comparison Operators
Arithmetic Operators
String Concatenation Operator
Date and Time Calculations
Bit-wise Operators
Unary Operators
Data Type Conversions
Cursor-based Reference
Boolean Operators
Comparison Operators
Outer Join Operators
BETWEEN Operator
IN Operator
LIKE Comparison
IS NULL Test
Subqueries
Exists Test
Full Text Search
Arithmetic Operators
Data Type Conversion
Unary Negation
Basic Elements
Column References
Aggregate Functions
Generic Functions
Character Functions
Mathematical Functions
Set Operators (UNION, MINUS, INTERSECT)
SELECT Clause
FROM Clause
WHERE Clause
GROUP BY Clause
HAVING Clause
ORDER BY Clause
3. Data Manipulation Statements (SQL)
SELECT
FETCH
INSERT
UPDATE
DELETE
BEGIN
COMMIT
ROLLBACK
4. Appendices
This documentation describes the standard SQL commands of PrimeBase and additionally functions that can directly be used in SQL statements, such as aggregate functions, but also proprietary extensions such as mathematical functions, string functions, etc.
Further extensions such as CURSORs, functions related to them as well as all other aspects of the fundamental programming language of the PrimeBase system (PrimeBaseTalk, or short PBT) are described in the "PrimeBase Talk Programmer's Guide" (pbpbtpg.html).
In this document we refer to the PrimeBase SQL dialect as PrimeBaseTalk (PBT), also because the SQL specific/compliant parts of PrimeBase SQL are fully embedded into and available in PrimeBaseTalk.
For information on the Data Definition Language commands in PrimeBase see the "PrimeBase SQL Database Server Reference Manual" (pbdsrefman.html).
Many of the examples in reference manual make use of a sample database, called the Golfers database. The tables and columns of the Golfers database are explained in detail in Appendix A.
So if you want to try any of these examples yourself you may want to copy and paste the Create Script into a file execute them in the PrimeBase Automation Client (PBAC) or the PrimeBase SQL Database Server console using the EXECUTE FILE command:
EXECUTE FILE "golfersdb.sql" LOCATION "c:\"; <press ENTER> go <press ENTER>Also make sure you open the Golfers database in PBAC or the server console prior to trying any of the examples contained in this reference manual. You do this by issuing the following:
OPEN DATABASE Golfers; <press ENTER> go <press ENTER>
In PrimeBaseTalk a data type specification can optionally include a size and scale specification where appropriate. The first <int_expr> is the size and the second <int_expr> is the scale.
Each column or literal value has an associated datatype. The data types of the operands will determine the meaning of arithmetic operators and function calls. When operands of different types are combined in an arithmetic operation, one operand is converted automatically to the type with higher precedence. The precedence order from highest to lowest is: (DECIMAL, MONEY, REAL, INTEGER, WORD, BOOLEAN, DATE, TIME, DATETIME, VARCHAR, CHAR, OBJNAME, VARBIN, BIN, UNICODE)
Data types are used to define the base types of domains or table columns. They are also used to explicitly convert a value to a new type.
<data_type> ::= ( BOOLEAN | SMINT | ... ) [ '[' [ <int_expr> ] [ ',' [ <int_expr> ] ] ]
The following table lists the data types supported by PrimeBase databases, and PrimeBaseTalk programming language.
NOTES:
(1) REAL is an alternative, equivalent and PrimeBase-specific name for the SMFLOAT datatype. A system constant named $real does not exist though, but can be created if needed the same way as any other PrimeBaseTalk variable is created. Declaring variables (and functions) starting with a '$' character is however a PrimeBase-specific functionality as well.
(2) DATETIME is an alternative name for the TIMESTAMP datatype.
(3) When a size is specified for CHAR values, values are always exactly this size. CHAR values are space padded in order to fill the required length. Trailing spaces are never significant in comparisons. If no size is specified a CHAR value is the same as a VARCHAR value of unspecified size.
(4) Size, in the case of DECIMAL and MONEY values, is the number of digits including one for the sign. For example, a DECIMAL[10,2] value has a maximum of 9 digits. If not specified, the maximum of 300 is used.
(5) Scale is the maximum number of digits after the decimal point. Excessive digits are truncated, e.g. DECIMAL[8,3] 123.4567 -> 123.456, and DECIMAL[8,3] -123.4567 -> -123.458 . If no scale is specified, the maximum of 60 is used.
(6) VARBIN, VARCHAR and UNICODE may have any size from 0 to 62 Kbytes. If size is not specified, the size is 62 Kbytes.
(7) TINYINT is a PrimeBase-specific extension of the Data Access Language (DAL) standard.
(8) The datatypes REAL10 and REAL12 are supported by PrimeBase on MC68K MacOS only. REAL10 and REAL12 are PrimeBase-specific extensions of the Data Access Language (DAL) standard.
The following data types are supported by PrimeBaseTalk only and can't be used for database columns.
Literals represent a constant value of a certain type. The characters that make up a single literal may not be separated by space, tab or end of line characters. Literal values may be placed directly in expressions and queries in PrimeBaseTalk programs.
The following are literal values and the types recognized by PrimeBaseTalk:
NOTES:
(1) A single quote can be placed in a string delimited by single quotes by placing two single quotes directly next to each other, for example: 'Jack said ''Hi!'''. The same applies to double quotes placed in a double quote delimited string.
(2) Binary strings should contain an even number of Hex digits.
(3) Use \uXXXX sequences for UNICODE characters that cannot be represented in the bit 8 character set. XXXX is the hexadecimal unicode character number. \u2122 for example is the copyright character. See the file setup/unicode/UnicodeData.txt for a complete listing.
An expression is evaluated by PrimeBaseTalk at runtime to produce a single value of a particular data type. The terms <int_expr>, <char_expr> and <bool_expr> are all expressions (in general, <expression>, below).
When expression with multiple operators are evaluated, the precedence determines the order of evaluation. Use left and right parenthesis to change the predefined evaluation order. The following table lists all operators with descending precedence.
Operators Result Datatype Comments {} () [] -> . : GENERIC column group, parenthesis, datatype size, column indicator, variable indicator ~ + - INT, SMINT, TINYINT unary operators: bit-wise negate, affirmation, numeric negation data type GENERIC data type conversion * / % INT, FLOAT, DECIMAL multiplication, division, remainder + - & ^ | GENERIC addition and concatenation, numeric subtraction, bit-wise AND, bit-wise exclusive-OR, bit-wise OR IS NULL BOOLEAN null test < <= > >= = == != <> BOOLEAN less, less or equal, greater, greater or equal, equal, not equal LIKE BETWEEN IN BOOLEAN like pattern matching, range test, in list test NOT BOOLEAN boolean negate AND BOOLEAN boolean AND OR BOOLEAN boolean OR , GENERIC comma
The operators AND, OR and NOT operate on BOOLEAN values, and return a BOOLEAN value.
Note that, unlike for example in the C programming language, both sides (operands) of the operators AND and OR are evaluated regardless of the outcome of the evaluation of the first operand.
<expression> ::= <boolean_term> | <expression> OR <boolean_term> <boolean_term> ::= <boolean_factor> | <boolean_term> AND <boolean_factor> <boolean_factor> ::= [ NOT ] <expr_predicate>
 |
Example
BOOLEAN initialized = $true;
BOOLEAN printed = $false;
IF ( initialized AND NOT printed )
{
PRINT "Now it is being printed.";
}
ELSE
{
PRINT "ERROR: Can't print, it probably has not " +
"been initialized yet.";
}
Screen output:
Now it is being printed.
|
The comparison operators and the "null-test" operator (IS NULL), all produce BOOLEAN values. Arguments may be values of any data type (as of PrimeBase 3.5 also CURSOR).
In comparisons, if one (or both) of the operands are $null, the result is the BOOLEAN value $maybe.
<expr_predicate> ::= <value_expr> | <value_expr> <comp_op> <value_expr> | <value_expr> IS [ NOT ] NULL <comp_op> ::= '=' | '<>' | '<' | '>' | '<=' | '>=' | '==' | '!='
|
Example
BOOLEAN itIsRaining = $true;
IF ( itIsRaining == $true )
{
PRINT "Too bad, it is raining.";
}
ELSE
{
PRINT "Hey, it is not raining.";
}
/* Or equivalent: */
IF ( itIsRaining )
{
PRINT "Too bad, it is raining.";
}
ELSE
{
PRINT "Hey, it is not raining.";
}
/* And a different example: */
INTEGER anIntValue = 10;
IF ( anIntValue != 10 )
{
PRINT "The value has not been properly assigned " +
"to the variable!";
}
ELSE
{
PRINT "The value has been properly assigned to the " +
"variable.";
}
Screen output:
Too bad, it is raining.
Too bad, it is raining.
The value has been properly assigned to the variable.
|
 |
WarningNote that trailing spaces are ignored when comparing strings, meaning that 'a ' == 'a ' evaluates to boolean true. |
The following arithmetic operations are supported by PrimeBaseTalk expressions: addition (+), subtraction (-), multiplication (*), division (/) and remainder (%).
<value_expr> ::= <expr_term> | <value_expr> ( '+' | '-' '&' | '^' | '|') <expr_term> <expr_term> ::= <expr_conversion> | <expr_term> ( '*' | '/' | '%' ) <expr_conversion>
|
Example
PRINT "The remainder of 10 / 3 is",
VARCHAR( 10 % 3 );
Screen Output:
The remainder of 10 / 3 is 1
|
The addition (+) operator may also be applied to string values. For operands of type CHAR, VARCHAR, LONGCHAR, UNICODE. BIN, LONGBIN the character string values are concatenated and the result has the same data type as the operands.
Also see built-in server function CONCAT().
|
ExamplePRINT "Hello" + " " + "world" + "!"; Screen Output: Hello world! |
<datetime_sum> ::= [ DATETIME ] <datetime_exp> '+' [ INT ] <int_expr>
<date_sum> ::= [ DATE ] <date_expr> '+' [ INT ] <int_expr>
<time_sum> ::= [ TIME ] <time_expr> '+' [ INT ] <int_expr>
<datetime_diff> ::= [ DATETIME ] <datetime_expr> '-' [ DATETIME ] <datetime_expr>
<date_diff> ::= [ DATE ] <date_expr> '-' [ DATE ] <date_expr>
<time_diff> ::= [ TIME ] <time_expr> '-' [ TIME ] <time_expr>
The addition (+) operator can be used for date and time calculations.
When the first operand has a DATETIME data type and the second has a INT data type, then the second operand is interpreted as a timescale value in seconds and the result is a DATETIME value increased by <int_expr>. <int_expr> can be a negative value to calculate a datetime in the past.
When the first operand has a DATE data type and the second has a INT data type, then the second operand is interpreted as a timescale value in days and the result is a DATE value increased by <int_expr>.
When the first operand has a TIME data type and the second has a INT data type, then the second operand is interpreted as a timescale value in seconds and the result is a TIME value increased by <int_expr>.
Example:
DATETIME time_in_one_hour = $now() + (60 * 60);
The subtraction (-) operator can be used to calculate date and time difference.
When both operands of a substraction are of type DATETIME or TIME, then the result is the time difference in seconds.
When both operands of a substraction are of type DATE, then the result is the time difference in days.
The bit-wise operations are those familiar to most programmers: bit-wise AND ( & ), bit-wise OR ( | ), bit-wise exclusive-OR ( ^ ).
|
ExamplePRINT "255 & 127 =", VARCHAR( 255 & 127 ); Screen Output: 255 & 128 = 127 |
The unary operators supported by PrimeBaseTalk are unary-affirmation (+), unary-negation (-), and unary bitwise NOT (~).
Permissible operands are all types of INTEGER values (INT and SMINT).
<expr_factor> ::= [ '+' | '-' | '~' ] <expr_primary>
<expr_conversion> ::= [ <data_type> ] <expr_factor><data_type> may specify size and scale as described for the various data types.
PrimeBaseTalk supports extensive data type conversion. When conversion to and from character string that are applied to literals and program variables, the format is controlled by the various Format Control Variables mentioned in Appendix A.1 Format Control Variables.
You can set the initial value of the format control variable with the 'Environment Editor' application.
String Conversion Control Variables Data types Client program Variable Environment Variable DATE, DATETIME $month 380 DATE, DATETIME $day 381 DATE, DATETIME $datefmt 382 TIME, DATETIME $ampm 383 TIME $timefmt 384 DATETIME $tsfmt 385 DECIMAL $decfmt 386 MONEY $moneyfmt 387 CHAR, VARCHAR $charset 388 UNICODE $unicodemap 389
If two data type conversions are to take place directly one after the other, the second data type conversion has to be put in brackets, otherwise an error results.
|
ExamplePRINT TINYINT ( INTEGER '10' ); Screen Output: 10 |
|
ExampleINTEGER personalID = 10; VARCHAR personalIDString = VARCHAR personalID; PRINT "The ID of this person is " + personalIDString; Screen Output: The ID of this person is 10 |
|
ExampleINTEGER a = 123; INTEGER b = 456; VARCHAR c = VARCHAR( b / a ); PRINT "b / a = " + c; PRINT "b / a + c = " + VARCHAR( INTEGER c + INTEGER c ); Screen output: b / a = 3 b / a + c = 6 |
A CURSOR based reference refers to values of the current row of a CURSOR rowset. The column may be specified using the column alias, an INTEGER literal representing the column ordinal number or a variable containing one of these two.
If the variable, <var_name>, is of type INTEGER it is the column ordinal number, and if it is of type OBJNAME or VARCHAR it is the column alias.
According to the DAL standard unqualified identifiers (like <column_alias> in the first option of <cursor_based_column_ref>) are first interpreted as DAL variables in the current scope, and then as a CURSOR based reference.
Note: The error message may be misleading here because the last thing the interpreter does is try to interpret the identifier as a column of $cursor.
<cursor_based_column_ref> ::= [ [ <cursor> ] '->' ] <column_alias> | [ <cursor> ] '->' <int_literal> | [ <cursor> ] '->' ':' <var_name>
A search condition is used to specify which rows should be selected from a set of tables. A search condition is placed in the WHERE Clause in a query specification, and may also appear with sub-query in the SELECT Clause.
<search_condition> ::= <srch_bool_term> | <search_condition> OR <srch_bool_term> <srch_bool_term> ::= <srch_bool_factor> | <srch_bool_term> AND <srch_bool_factor> <srch_bool_factor> ::= [ NOT ] <srch_predicate> <srch_predicate> ::= <srch_comparison> | <srch_outer_join> | <srch_between> | <srch_in> | <srch_like> | <srch_is_null> | <srch_quantified> | <srch_full_text> | <srch_exists> | <srch_expr>
|
Example/* ** Give us all "Pro"fessionals with earnings of more than ** $4999 */ SELECT FirstNames, Surname FROM Golfers WHERE ( Status == "Pro" ) AND ( Earnings > MONEY 4999 ); PRINTALL; Screen output: Buzz Bee Humsdee Dumsdee |
<srch_comparison> ::= <srch_expr> <comp_op> ([ * ] <srch_expr> | <subquery> ) <comp_op> ::= '=' | '<>' | '<' | '>' | '<=' | '>=' | '==' | '!='When a <srch_comparison> is performed on columns that are based on domains with collation sequences, it uses the collation sequence key weights in the comparision operation.
|
Example/* ** Give us all golfers who didn't earn exactly $10,999.99 */ SELECT FirstNames, Surname FROM Golfers WHERE Earnings != MONEY 10999.99; PRINTALL; Screen output: Buzz Bee Tumble Dry Fish Head |
<srch_outer_join> ::= <srch_expr> <outer_join_op> <srch_expr> <outer_join_op> ::= '/=' | '/=\' | '=\' | '*=' | '*=*' | '=*'The outer join operators supported by PrimeBaseTalk are left-outer (/= or *=), right-outer (=\ or =*), and symmetric-outer join (/=\ or *=*).
In a LEFT OUTER JOIN, NULL values are inserted in place of columns from the RIGHT table where no matching value from the LEFT table was found.
In a RIGHT OUTER JOIN, NULL values are inserted in place of columns from the LEFT table where no matching value from the RIGHT table was found.
In a SYMMETRIC OUTER JOIN, NULL values are inserted in place of columns from both tables where no matching value from any table was found.
Alternatively an outer join can be defined in the <from_clause> ("LEFT OUTER JOIN", "RIGHT OUTER JOIN").
The asterisk character '*' is used to indicate that the boolan value TRUE is returned by the operation when one of the operand expressions is null.
When you have columns with NULL values and you apply a search condition to that column, rows that have a NULL values in the searched column are removed from the result. When you want to keep these rows in the result you can can set the '*' indicator in after the operator. This indicator is especially useful for search condition on results of outer join operations. The outer join operation can insert NULL values in columns even when NULL values are not permitted in the base tables. Normal search conditions would remove these rows from the result set.
|
Example/* ** Give us all golfers who didn't earn exactly $10,999.99 */ SELECT FirstNames, Surname FROM Golfers WHERE Earnings != MONEY 10999.99; PRINTALL; Screen output: Buzz Bee Tumble Dry Fish Head |
<srch_between> ::= <srch_expr> [ NOT ] BETWEEN [ * ] <srch_expr> AND <srch_expr>
<srch_between> returns all rows which are greater or equal to the second expression, and less than or equal to the third expression.
|
Example/* ** Give us all user who earned between $3700 and $10999 as of yet */ SELECT FirstNames, Surname FROM Golfers WHERE Earnings BETWEEN "$3700" AND "$10999"; PRINTALL; Screen output: Buzz Bee Tumble Dry |
<srch_in> ::= <srch_expr> [ NOT ] IN ( '(' <value_list> ')' | <subquery> ) <value_list> ::= <value_expr> { ',' <value_expr> }
|
Example/* ** Give us all (well, not really) european golfers */ SELECT FirstNames, Surname FROM Golfers WHERE Nationality IN ( "french", "german", "dutch", "british" ); PRINTALL; Screen output: Buzz Bee Tumble Dry Humsdee Dumsdee |
<srch_like> ::= <srch_expr> [ NOT ] LIKE [ * ] <pattern> [ ESCAPE <escape> ]
<pattern> is a string consisting of characters that have to match the characters of <srch_expr>.
The characters '%' or '*' match any characters, including empty strings.
The character '?' matches any single character.
ESCAPE is used to enable searching of the characters that are used as wildcards ('%','_','*','?'). When a character is preceded by the <escape> character, it will be used literally.
The LIKE test is implemented for columns of type CHAR, VARCHAR, LONGCHAR and UNICODE. When a LIKE test is performed on columns that are based on domains with collation sequences, it uses the collation sequence keys to match the pattern.
For common search operations in larger tables, an index should exist on the search column and at least the first 2 or 3 characters should be provided as a search pattern.
When the LIKE search is performed on the words of a full text indexed column the matching is within the words boundaries.
|
Example/* ** Give us all golfers whose surnames end with "ee" */ SELECT FirstNames, Surname FROM Golfers WHERE Surname LIKE "%ee"; PRINTALL; Screen output: Buzz Bee Humsdee Dumsdee |
|
Example/* Find products where the name starts with 'A10*8' */ SELECT * FROM products WHERE .name LIKE 'A10\*8%' ESCAPE '\'; |
<srch_is_null> ::= <srch_expr> IS [ NOT ] NULL
|
Example/* ** Give us all golfers whose SurName column has been set ** to some value (as opposed to be $null) */ SELECT FirstNames, Surname FROM Golfers WHERE FirstNames IS NOT NULL; PRINTALL; Screen output: Buzz Bee Tumble Dry Humsdee Dumsdee Fish Head |
<srch_quantified> ::= <srch_expr> <comp_op> [ ALL | SOME | ANY ] <subquery>
<subquery> ::= '(' <query_spec> ')'
When a <subquery> is used in the <where_clause> the <select_list> of the query has a single column, that is compared with an other expression. The <where_clause> of the <subquery> can have one or more <join_condition> that references columns from the outer query. In this case the subquery is evaluated for every distinct value of the outer reference.
|
Example
/*
** Give us the oldest golfer
*/
SELECT FirstNames, Surname
FROM Golfers
WHERE DateOfBirth ==
(
SELECT MIN( DateOfBirth )
FROM Golfers
);
PRINTALL;
Screen output:
Fish Head
|
<srch_exists> ::= ( [ NOT ] EXISTS | FOR ALL ) <subquery>
<srch_full_text> ::= <text_column_ref> '=' [ ALL | ANY | SOME ] <char_literal> <char_literal> is the text sequence of words that are seperated by non-word characters. <text_column_ref> is a <column_ref> of a columns that has a full text index.
The ALL keyword indicates that all words of the <char_literal> have to be found in <column_ref>.
The ANY or SOME keyword indicates that at least one word of the <char_literal> has to be found in <column_ref>. Use the LOCATEWORDS() function to find the text offset in the text of <column_ref>.
The following arithmetic operations are supported by search expressions: addition (+), subtraction (-), multiplication (*), division (/) and remainder (%).
Addition (+) and subtraction (-) are also used for Date and Time Calculations .The bit-wise operations are: bit-wise AND ( & ), bit-wise OR ( | ), bit-wise exclusive-OR ( ^ ).
<srch_expr> ::= <srch_term> | <srch_expr> ( '+' | '-' | '&' | '^' | '|' ) <srch_term> <srch_term> ::= <srch_conversion> | <srch_term> ( '*' | '/' | '%' ) <srch_conversion>
<srch_conversion> ::= [ <data_type> ] <srch_factor>
Convert <srch_factor> to the specified data type. Data type conversion is not done automatically in a <search_condition> as it is in an <expression> (literal values are converted as required, however). The following groups may be combined without error in a search condition:
- Integers: TINYINT, SMALLINT and INTEGER.
- Decimals: DECIMAL and MONEY
- Floating point numbers: SMALLFLOAT, REAL, DOUBLE, etc.
- Character values: CHAR and VARCHAR.
Within these groups no conversion is explicitly required, however if values of different groups or values of type not mentioned above are to be combined (by operators) in a <search_condition> they must be explicitly converted.
When a string is converted in an SQL statement that is send to the server, database system variables are used to control the formatting. The TO_CHAR() function can be used in SQL statements to convert to variable formats. You can set the initial value of the format control variable with the Environment Editor
String Conversion Control Variables Data types Master Database Variable Environment Variable DATE, DATETIME MonthFormat 380 DATE, DATETIME DayFormat 381 DATE, DATETIME DateFormat 382 TIME, DATETIME AMPMFormat 383 TIME TimeFormat 384 DATETIME TimeStampFormat 385 DECIMAL DecimalFormat 386 MONEY MoneyFormat 387 CHAR, VARCHAR CharSet 388 UNICODE UnicodeSequence 389
<srch_factor> ::= [ '+' | '-' | '~' ] <srch_primary>
<srch_primary> ::= '(' <search_condition> ')' | <set_function_spec> | <column_ref> | <cursor_based_column_ref> | <literal> | <variable> | <function_call> | <composite_column>Note: <set_function_spec> is not allowed in a <where_clause> of a <query_spec> (see below).
<variable> is the name of a previously declared program variable or function argument.
<column_ref> ::= [ <table_alias> '.' | '.' ] <column_name> <composite_column> ::= '{' <search_condition> { ',' <search_condition> } '}'<column_name> is the name of a column from the <table_spec> listed in the <from_clause>. Valid names are simple and composite column names of database tables and <column_alias> names used in the <select_items> of <table_spec>. When <column_name> is not qualified by <table_alias>. or '.' PrimeBaseTalk attempts to insert the value contained in a variable with the same name first. Only when a variable with that name does not exist, the given name is assumed to be the column name itself. Because of this behaviour it is highly recommended to always use at least the '.' qualifier when specifying columns in database statements.
|
WarningVARCHAR Earnings = "1"; SELECT SurName, Earnings FROM Golfers; PRINT "First Results:"; PRINTALL; PRINT ""; SELECT SurName, .Earnings FROM Golfers; PRINT "Second Results:"; PRINTALL; Screen output: First Results: Bee 1 Dry 1 Dumsdee 1 Head 1 Second Results: Bee $ 5,000.0 Dry $ 3,999.0 Dumsdee $ 10,999.99 Head $ 3,520.0 Note: Consider the difference of 'First Results' and 'Second Results', even though the only difference between the two SELECT statements is a single '.'. |
Aggregate functions can be used in the <select_list> to return aggregated calculations for the whole rowset or for groups of a rowset. When aggregate functions are listed beside column references <column_ref> in the <select_list>, all this <column_ref> have to be listed in the <group_by_clause> too.
Aggregate functions can be used in <having_clause> to apply conditions to groups of rows.
<set_function_spec> ::= COUNT '(' '*' ')' | <distinct_set_function> | <all_set_function> <distinct_set_function> ::= ( AVG | MAX | MIN | SUM | COUNT ) '(' DISTINCT <column_ref> ')' <all_set_function> ::= ( AVG | MAX | MIN | SUM ) '(' [ ALL ] <srch_expr> ')'
When multiple <select_item> with <distinct_set_function> are used, they all have to reference the same <column_ref>.
When you apply an <all_set_function> to the rowset that is a result of a join of two tables A and B, the result may not be what you expect, since the join operation can build duplicates of the column values that you are computing.
When you need totals from two or more tables, you have to build the totals separately, before the tables are joined. Build separate views or table expressions <subquery> that summarizes the data and groups it by the referenced columns. Then join the views or table expressions to combine the results in the <select_list>.
When you apply an <all_set_function> like SUM(ALL column) or AVG(ALL column) to the result set of a UNION, INTERSECT or MINUS <set_operator> the totals are build from the first operand. In case of the UNION ALL, the totals are build from both operands.
|
Example/* ** Give us the oldest golfer */ SELECT FirstNames, Surname FROM Golfers WHERE DateOfBirth == ( SELECT MIN( DateOfBirth ) FROM Golfers ); PRINTALL; Screen output: Fish Head |
|
Example/* ** Give us the average earnings of all golfers */ SELECT AVG( Earnings ) FROM Golfers; PRINTALL; Screen output: $ 5,879.74 |
When arguments have different types, sizes or scales, the return type will have the larger types, sizes and scales.
When <cond> evaluates to TRUE, <a> is returned, else <b> is returned.
The function compares the arguments and returns the greatest value.
The function compares the arguments and returns the least value.
When <a> is not NULL <a> is returned, else <b> is returned.
Returns the user name for the current connection to the database server.
Returns the current date and time of the host clock where the database engine is executed. When a database column has a NOW default value, the current time is assigned from the host clock at insert time. When you update that column to set the current time, the NOW() function should be used to be sure that the same clock is used.
Some of the string functions return result types that are as large as their arguments. For VARCHAR results, the maximum length is 61400 characters. Return values that exceed the limit are truncated without warning.
When these functions are used in a select column list, beware that the total row size is also limited to 61400 bytes. To avoid the overflow in the row size and to be not surprized about the result type, you should explicitly type cast the result.
Example:
SELECT id, VARCHAR[20000] CONCAT(text_a, text_b) FROM table_a;In the next releases of PrimeBase functions that currently can only return VARCHAR results will return the data type of the first argument.
Function names are case insensitive. Function arguments are automatically converted to the required data type.
Returns <c> as the binary equivalent INTEGER number.
Returns the lower 8 bits of <n> as a binary equivalent character of the host character set.
Concatenates two strings <a> and <b>. When an argument is NULL, it is replaced with an empty string. It is a replacement for the '+' operator, when a domain rule prevents the '+' operator or NULL values should be ignored.
Returns <string> with the first character of each word in upper case and all other alphabet characters in lower case. Words are delimited by characters that are not alphanumeric.
Returns <string> with all alphabet characters in lower case.
Returns <string> with all alphabet characters in upper case.
Returns the words of a column.
The function is used to access the full text index word list. Use it to build full text search conditions and for listing words from the words index directory in a SELECT column list. Can currently only be used for columns that have a full text index defined for them. When the database_column is of type UNICODE, the return type is UNICODE[243], otherwise VARCHAR[243].
You can use all relational operators (==, !=, <=,<, >, >=, [NOT] BETWEEN, IS [NOT] NULL, [NOT] LIKE) to search the full text index word list.
Example:
List words that are greater than 'A' and less than 'C' and that end with 'er':
SELECT distinct WORDS(.content) AS Words FROM fulltext WHERE WORDS(.content) > 'A' AND WORDS(.content) LIKE '%er' AND WORDS(.content) < 'C';
Return the length of <string> in character units. UNICODE characters require 2 bytes per character. For other types the size is returned in byte units.
Returns the string to the left of the given pattern.
<string> - the string to be searched.
<pattern> - a pattern to be found in <string>.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the left side of the string. A negative <count> value causes the search to begin at the end and proceed to the start of <string>.
Returns the string to the right of the pattern.
<string> - the string to be searched.
<pattern> - a pattern to be found in <string>.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the left side of the string. A negative <count> value causes the search to begin at the end and proceed to the start of <string>.
Returns a substring extracted from a given string value.
<string> - a VARCHAR, CHAR or LONGCHAR value.
<position> - an INTEGER specifying the starting position within <string>. The first character of <string> has a position of 1. If <position< is negative, then it specifies a position relative to the end of <string>. The last character of <string> is designated by the value -1.
The first character of <string> has a position of 1. If <position> is negative, then it specifies a position relative to the end of <string>. The last character of <string> has a position of -1.
<length> - this argument is optional, and specifies the number of characters to extract. If length is negative, then position specifies the last character of the extracted string, and extraction proceeds to the left instead of to the right. If omitted, the function extracts the entire remainder of <string>.
Returns the offset of the pattern in the string. If the pattern could not be found, LOCATE() returns 0.
<string> - a VARCHAR, CHAR or LONGCHAR argument to be searched. When the string argument is a column that has a collation sequence, the pattern will be searched with keys of the collation, to do case insensitive and/or ignores accent matching.
<pattern> - a VARCHAR pattern to be found in <string>.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the right side of the string. A negative <count> value causes the search to begin at the end of <string>, and proceed from right to left.
Example:
SELECT .pathname + .filename, LOCATE(.content, 'good') FROM fulltext WHERE WORDS(.content) == 'good'; PRINTALL;
Returns the offset of the pattern in the string. If the pattern could not be found, LOCATEWORDS() returns 0.
Due in the next release!
<string> - a VARCHAR, CHAR or LONGCHAR argument to be searched. When the string argument is a column that has a collation sequence, the pattern will be searched with keys of the collation, to do case insensitive and/or ignores accent matching.
<pattern> - a VARCHAR pattern to be found in <string>.
<max_dist> - an optional argument and specifies the maximum distance between words in count of characters.
When the words of <pattern> are located in <string>, the same order of words has to be found. Non-alphanumeric characters define the word boundaries and are ignored in the comparison. Accent characters like 'Ü' will match HTML entities of the form 'Ü'.
The LOCATEWORDS function can be used to filter the set of matching rows that was returned by a full text search with conditions like <column_ref> = ALL <char_literal>. This search returns rows where all the words of the <char_literal> are contained somewhere in the text of <column_ref>.
Returns <string> with every occurrence of <search> replaced by <replacement>. The default value for <replacement> is an empty string. When it is omitted or NULL, all occurrences of <search> are removed. If <search> is NULL, <string> is returned.
Returns <string> with all occurrences of each character in <from> replaced by character in <to> that is in the same position as in <from>. When <from> is longer than <to>, then char of <from> that have not corresponding character in <to> are removed.
Returns a concatenation of sections of <text> where the nesting of <XX> </XX> tags matches the <search_path> argument.
<text> contains html or xml text that can be parsed by the function.
<search_path> is a list of tag names separated by '/'. The matching is performed using the wildcard syntax of the LIKE operator.
<escape> is used to enable searching of the characters that a used of wildcards ('%','_','*','?'). When a character is preceded with the <escape> character, it will be used literally.
This function may be changed in future releases to cover a subset of the XQL language. See http://www.w3.org/TandS/QL/QL98/pp/xql.html.
Returns a formated string that is converted from <num> with the specified <format>. The content of <format> depends on the data type of <num>. The format string has the notation of Format Control Variables:
Datatype Notation Example DATETIME $tsfmt "DDD, DD. MMM HH:MM" DATE $datefmt "MM/DD/YYYY" TIME $timefmt "HH:MM:SS:hu" DECIMAL $decfmt "[9999].9" MONEY $moneyfmt "$[9999].9" SMALLINT $format "%3.3d km/h" INTEGER $format "%05d" FLOAT $format "%10.2f"
Returns <string> with all characters of <set> removed from the left.
Starting with first character the function skips all characters that are in <set> until a character is not found in <set>.
<set> is optional and defaults to a single blank.
Returns <string> with all characters of <set> removed from the right.
Starting with last character the function skips all characters from the end, that are in <set> until a character is not found in <set>.
<set> is optional and defaults to a single blank.
Returns a left padded <string> with a specified <length>.
When <string> is shorter than <length> characters of <padding> are repeatedly appended until the string is <length> characters long.
When <string> is longer than <length>, <string> is truncated.
Returns a right padded <string> with a specified <length>.
When <string> is shorter than <length> characters of <padding> are repeatedly appended until the string is <length> characters long.
When <string> is longer than <length>, <string> is truncated.
Returns a UNICODE <string> normalized to the requested <form>.
Form: Description: 0 (F) Canonical Decomposition 1 (KD) Compatibility Decomposition 2 (C) Canonical Decomposition, followed by Canonical Composition 3 (KC) Compatibility Decomposition, followed by Canonical CompositionFor more details see http://www.UNICODE.org/UNICODE/reports/tr15/.
Returns the absolute value of the numeric argument <x>. The returned data type is the same as the argument type.
Returns the smallest INTEGER not less than <x>.
Returns the cosine of argument <x>. (An angle expressed in radians.)
Returns the hyperbolic cosine of argument <x> as FLOAT.
Returns e to the <x>'th power. e is the base of natural logarithms.
Returns the largest INTEGER value not larger than <x> as FLOAT.
Returns the natural logarithm of argument <x>.
Returns the base-10 logarithm of argument <x>.
Returns <x> raised to the power of <y>.
For positive <m> it returns <x> rounded to <m> places right of the DECIMAL point.
For negative <m> it returns <x> round off digits left of the DECIMAL point.
<m> is optional and a default of 0 is used.
Returns 1 when <x> is greater than 0.
Returns -1 when <x> is less than 0.
Returns 0 when <x> is equal 0.
The data type of the result is the same as the argument type.
Returns the sine of argument <x>.
Returns the hyperbolic sine of argument <x>.
Returns the square root of <x>.
Returns the tangent of <x> in radians.
Returns the hyperbolic tangent of <x>.
For positive <m> it returns <x> truncated to <m> places right of the DECIMAL point.
For negative <m> it returns <x> truncate <m> digits left of the DECIMAL point.
<m> is optional and a default of 0 is used.
Returns the principal value of the arc cosine of argument <x>.
<x> must be in a range -1 to +1.
Returns the principal value of the arc sine of argument <x>.
Returns the principal value of the arc tangent of argument <x>.
When <x> is between -1 and +1 it returns the arc sin of <x> in the range -pi/2 to +pi/2
Returns the principal value of the arc tangent of argument <x> / <y>.
It uses the signs of both arguments to determine the quadrant of the return value.
Use a query specification to select a set of data from the database.
<query_spec> ::= <table_expr> | <query_spec> <set_operator> <table_expr> <table_expr> ::= <select_clause> <from_clause> [ <where_clause> ] [ <group_by_clause> ] [ <having_clause> ] <set_operator> ::= [UNION [ ALL ] | INTERSECT | MINUS]
A <table_expr> consists of a FROM clause and an optional WHERE, GROUP BY and HAVING clauses.
<set_operator> is used to merge and intersect multiple <table_expr>. The <table_expr> must have the same cardinality (number of columns) and the data types of the columns must be the same for the columns in the same position of the <select_list>. Use <datatype> conversions in the <select_item> to build matching rowsets. All <set_operator> have the same precedence.
<set_operator> ::= [UNION [ ALL ] | INTERSECT | MINUS]
UNION is used to merge the rows of multiple <table_expr> into one rowset. A rowset that contains rows that are included in the first <table_expr> OR the second <table_expr>.
INTERSECT is used to build the intersection of multiple <table_expr> in one rowset. A rowset that contains rows that are included in the first <table_expr> AND the second <table_expr>.
MINUS is used to build a relational difference. A rowset that contains rows form the first <table_expr> and that are not included in the second <table_expr>.
Use the SELECT Clause to select data from tables.
<select_clause> ::= SELECT [ ALL | DISTINCT ] <select_list> <select_list> ::= <select_item> { ',' <select_item> } <select_item> ::= '*' | ( <table_alias> '.' '*' ) | <search_condition> [ [ AS ] <column_alias> ]
The <select_list> specifies which columns, constant values or expressions are to be selected from the database tables. The tables are specified in the FROM clause described below.
Columns in the SELECT list may be given an alias name, specified using the AS clause. The alias name can be used later to reference the column in the rowset. The '*' means all columns in all tables or all columns of a table if used in the form: <table_alias>.* .
The DISTINCT indicator specifies that duplicate rows will be removed from the result set to form a relational table. The PrimeBase SQL Database Server removes duplicate rows by default. In most cases queries can be processed faster when duplicate rows are removed before join operations are performed, rows are sorted and transported over the communication links.
The ALL indicator specifies that duplicate rows will remain in the result set. When duplicate rows are needed in result sets the ALL indicator has to be used.
<table_alias> refers to a table specified in the FROM clause below.
<column_alias> is the new name for a simple or calculated column. When no <column_alias> is specified and the <search_condition> is a <column_ref>, the columns are named as the columns of the base table. <column_alias> should be used to uniquely name columns of <select_clause>. When multiple <table_spec> are listed in the <from_clause> duplicate names have to be renamed. When the <search_condition> is a <composite_column>, the columns are named as the component columns of the composite colum. When the <search_condition> is a calculated expression the columns are named "expr" + n, where n is the column position in the <select_list>.
|
Example/* ** Give us golfer Name and Earnings */ SELECT A.Name, A.Earnings FROM Golfers A; PRINTALL; Screen output: Bee Buzz $ 5,000.0 Dry Tumble $ 3,999.0 Dumsdee Humsdee $ 10,999.99 Head Fish $ 3,520.0 |
Use the FROM Clause to specify which database tables data is to be selected from. An alias may be specified for each table.
When multiple <table_spec> are listed in the <from_clause>, the cartesian product of the rows from all tables is build.
<from_clause> ::= FROM <table_spec> { ',' <table_spec> } <table_spec> ::= <table_ref> [ [ AS ] <table_alias> ] | <view_ref> [ AS ] <table_alias> | <subquery> [ AS ] <table_alias> | <outer_join> [ AS ] <table_alias> | <outer_join> ::= <table_ref> [ LEFT | RIGHT ] OUTER JOIN <table_ref> ON <join_condition> [ AND <search_condition> ] <join_condition> ::= <column_ref> = <column_ref> <table_ref> ::= [ <database_alias> '!' ] [ <creator_name> '.' ] <table_name> | ':' <object_name> <view_ref> ::= [ '(' ] [ <database_alias> '!' ] [ <creator_name> '.' ] <view_name> | ':' <object_name> [ ')' ]
The alias name applies to this query specification only. In no alias is specified, the table name will be used. A <table_ref> (table reference) ,may include the explicit specification of the database and the table creator name (also known as the table owner name).
<database_alias> defines the name of the database. When multiple databases have been opened in the current program instance (session), the <database_alias> specifies which database to use. If no database is specified the currently in-use database is assumed. The "USE DATABASE <database_alias>;" command can be used to set the database that should be used when no <database_alias> is set in a <table_ref> or <view_ref>
<creator_name> defines the object owner. If the creator name is not specified, then the server first checks for a table with the user's creator name, and then for tables with creator names 'Common' and 'System'.
<object_name> is a variable of type OBJNAME containing a valid object reference may be used in place of the explicit object reference. In addition, each "name" type value may be specified using an OBJNAME variable (preceded by a colon, ':').
When a <view_ref> (view reference) is enclosed in parenthesis, the view is optimized in isolation. Without parenthesis, the view is optimized by pushing <search_condition> that are applied to simple columns of the view into the <where_clause> of the view. In cases where optimisation is not improving the access speed, parentheses will disable the optimisation process.
In a LEFT OUTER JOIN, NULL values are inserted in place of columns from the RIGHT table where no matching value from the LEFT table was found.
In an RIGHT OUTER JOIN, NULL values are inserted in place of columns from the LEFT table where no matching value from the RIGHT table was found.
|
Example/* ** Give us each golfer's SurName, FirstNames, ** Nationality, Status and Handicap */ SELECT SurName, FirstNames, Nationality, Status, Handicap FROM Golfers; PRINTALL; Screen output: Bee Buzz dutch Pro 20 Dry Tumble british Pro -3 Dumsdee Humsdee french Pro 20 Head Fish canadian Pro -2 |
The WHERE clause contains the search condition that specifies which rows will be selected from the tables.
<where_clause> ::= WHERE <search_condition>
|
Example/* ** Give us golfer Name, Earnings and Name of the club ** that golfers is a member of, and order the result ** Earnings */ SELECT A.Name, A.Earnings, B.Name FROM Golfers A, Clubs B WHERE A.MemberOfClub == B.ID ORDER BY A.Earnings; PRINTALL; Screen output: Head Fish $ 3,520.0 Astronauts Golf Club Dry Tumble $ 3,999.0 Sterling Club Hamburg Bee Buzz $ 5,000.0 Sterling Club Hamburg Dumsdee Humsdee $ 10,999.99 Astronauts Golf Club |
The GROUP BY clause is used to group sets of rows together according to the values in the columns specified. A single row is returned for each row group. Columns selected which are not in the GROUP BY list must be aggregated using one of the supported aggregate functions, such as COUNT, SUM, AVG, MIN, MAX.
<group_by_clause> ::= GROUP BY <column_ref> { ',' <column_ref>}
|
Example/* ** Give us the club ID and sum up the earnings of all ** respective members of this club */ SELECT MemberOfClub, SUM( Earnings ) FROM Golfers GROUP BY MemberOfClub; PRINTALL; Screen output: 1 $ 8,999.0 2 $ 14,519.99 |
The HAVING clause allows you to place a further restriction on grouped rows returned.
<having_clause> ::= HAVING <search_condition>
|
Example/* ** Give us the club ID and sum up the earnings of all ** respective members of this club if these exceed ** $10,000 */ SELECT MemberOfClub, SUM( Earnings ) FROM Golfers GROUP BY MemberOfClub HAVING SUM( Earnings ) > MONEY( 10000 ); PRINTALL; Screen output: 2 $ 14,519.99 |
The ORDER BY clause allows you to order the returned rows by one or more columns. Also columns that are not specified in the SELECT clause can be used to order the returned rows.
<order_by_clause> ::= ORDER BY <column_ref> [ ASC | DESC ] [ { ',' <column_ref> [ ASC | DESC ] } ]
|
Example/* ** Give us golfer's club Name, golfer's Name, Earnings; ** order by club Name first, then golfer's Handicap */ SELECT B.Name, A.Name, A.Earnings FROM Golfers A, Clubs B WHERE A.MemberOfClub == B.ID ORDER BY B.Name, A.Handicap; PRINTALL; Screen output: Astronauts Golf Club Dumsdee Humsdee $ 10,999.99 Astronauts Golf Club Head Fish $ 3,520.0 Sterling Club Hamburg Bee Buzz $ 5,000.0 Sterling Club Hamburg Dry Tumble $ 3,999.0 |
<select_stat> ::= <query_spec> [ ORDER BY <sort_spec> { ',' <sort_spec> } ] [ WHERE ( $true | $false ) ] [ INTO <cursor> ] [ FOR ( READONLY | <scroll_mode> | <update_mode> | EXTRACT ) ] ';' <sort_spec> ::= ( <column_ref> | <int_expr> ) [ ASC | DESC ] <scroll_mode> ::= SCROLLING [ <update_mode> ] <update_mode> ::= [ EXTRACT ] UPDATE [ <col_group> | OF <column_name> { ',' <column_name> } ] <col_group> ::= '(' <column_name> { ',' <column_name> } ')'
The SELECT statement returns a rowset of data from tables of a particular DBMS. If the FROM clause is omitted, the default CURSOR, $cursor, is used. <query_spec> is a query specification which is described in section 2.6.
If <int_expr> is used in <sort_spec> then it refers to the position number of the column as it appears in the SELECT list in <sort_spec>. If ASC (ascending) and DESC (descending) is omitted ASC is the default.
The OF clause in <update_mode> is alternative syntax for the normal <col_group> syntax provided for compatibility with the Data Access Language (DAL) standard.
The various select modes are described by the table below:
Mode Description Pros Cons READONLY Returns data row-by-row. Very large sets of data can be processed. Maximum of one page of data is stored on the client at any one time. The first row is available as soon as possible. No CURSOR motion other than FETCH NEXT is supported. All rows remain locked, on the server, until the last page of rows is fetched by the client. SCROLLING Returns data row-by-row. All fetched data is buffered on the client. All types of CURSOR motions are supported. The first row is available as soon as possible. Sufficient memory on the client machine to contain the entire result set is required. Rows remain locked until the last page is fetched from the server. EXTRACT The SELECT blocks until all data is retrieved from the server. All types of CURSOR motion are supported. Locks on the rows on the server are released as soon as possible. Sufficient memory on the client to contain the entire result is required. The first row is only available once the entire SELECT has been executed.
Whenever client memory permits, we recommend selecting data "FOR EXTRACT". In addition to the modes specified above, the UPDATE mode is used to select data to be used with the positioned update (UPDATE, DELETE WHERE CURRENT OF) statements later.
|
Example/* ** Give us golfer Name and Earnings */ CURSOR myCursor; SELECT A.Name, A.Earnings FROM Golfers A INTO myCursor FOR EXTRACT; PRINTALL myCursor; Screen output: Bee Buzz $ 5,000.0 Dry Tumble $ 3,999.0 Dumsdee Humsdee $ 10,999.99 Head Fish $ 3,520.0 |
<fetch_stat> ::= FETCH [ <motion> ] [ OF <cursor>] ';' <motion> ::= FIRST | LAST | ABSOLUTE <int_expr> | NEXT | PREVIOUS | RELATIVE <int_expr>
All CURSOR rowsets have a current row position. The FETCH statement is used to set the current row position of a rowset. After a SELECT statement, the current row position is "before the rowset". This means that a FETCH NEXT (or FETCH FIRST) is required to position the current row on the first row of the rowset.
Only then is it valid to use the CURSOR reference operator ('->'). After doing a FETCH NEXT on a rowset whose current position is set to the last row, the current row position will be " after the rowset". When this occurs, the global variable $sqlcode will be set to the value $sqlnotfound.
<int_expr> is the row number in the rowset. When using FETCH ABSOLUTE, the first row is 1. If the row number specified is greater than the number of rows in the rowset, then current row position will be set to after the rowset.
Note that on rowsets selected FOR EXTRACT or FOR SCROLLING permit the use of CURSOR motions other than FETCH NEXT.
The rowset has no particular order, unless ORDER BY was specified in the SELECT statement that created the rowset.
When a rowset is first generated, the current row is the one just before the actual first row, so that a FETCH NEXT statement then moves it to the first row.
There is no current row of the rowset between the execution of an SELECT and the first FETCH statement; when FETCH moves on past the last row of the rowset; and if the current row is deleted.
If you attempt to fetch past the end of the rowset with either FETCH FIRST, LAST, NEXT or PREVIOUS, a $sqlcode value of $sqlnotfound is caused; for FETCH ABSOLUTE or RELATIVE, an error code is returned.
|
ExampleCURSOR myCursor; SELECT A.Name, A.Earnings FROM Golfers A INTO myCursor FOR EXTRACT; FETCH ABSOLUTE 2 OF myCursor; PRINTROW myCursor; Screen output: Dry Tumble $ 3,999.0 |
<insert_stat> ::= INSERT [ INTO ] <table_ref> [ <col_group> ] [ <values_spec> | <query_spec> ] ';' <values_spec> ::= VALUES '(' [ <expression> | NULL ] { ',' [ <expression> | NULL ] } ')' [ <return_row> ] <return_row> ::= RETURNING [ '(' <col_group> ')' ] [ INTO <cursor> ]
Insert a row or set of rows into a specific table. Columns not listed in <col_group> in the INSERT are given default values by the server. If <col_group> is not specified, then all columns of the table are assumed in order defined. Values for each column is specified in <values_spec>. As an alternative to a list of values, <query_spec> may be used to select a set of rows from other tables in the database to insert into the table.
The RETURNING clause can be used to retrieve any values inserted into any columns of the row. This can be used to retrieve the default values inserted by the server (e.g. an automatic counter). The result is a rowset containing one row.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
|
Example/* ** Let's insert some test data, so we don't have to play ** with the Golfers' Database "real" data */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1900, 2, 2, 4, 503, 3, "$600" ); /* ** Check if the data really got entered fine into the ** Results table */ SELECT Year FROM RESULTS WHERE Year == 1900; PRINTALL; /* ** Now let's remove this row again... */ DELETE FROM Results WHERE Year == 1900; /* ** Check if the data really got deleted from the ** Results table */ SELECT Year FROM RESULTS WHERE Year == 1900; PRINTALL; Screen output: 1900 |
<update_stat> ::= UPDATE <table_ref> SET <set_clause> { ',' <set_clause> } [ WHERE <search_cond> | WHERE CURRENT OF <cursor> ] ';' <set_clause> ::= <column_name> '=' ( <search_cond> | NULL )
Update a row, or set of rows of the specified table. The <set_clause> describes how a column is to be updated. No sub-queries or aggregate functions are allowed in the <search_cond> of the <set_clause>. The <search_cond> may contain references to columns of the updated row.
There are two forms of update statement: searched and positioned. When using the positioned form, the CURSOR in the WHERE CURRENT OF clause must contain a rowset which was selected FOR UPDATE.
The table reference must specify an updatable table or view. In the case of updating a view, this is only possible if the view is derived from a single table.
According to the definition of the column, the values that can be updated may be restricted. Attempts to insert a row in violation of these restrictions will result in an error.
If an expression in the <set_clause> includes a reference to a column being updated, the value used in computing the expression is the value of the column before any updates are performed on the row.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
|
Example/* ** Change contents of all FirstNames containing "Buzz" ** to "Buzzz" */ UPDATE Golfers SET FirstNames = "Buzzz" WHERE FirstNames == "Buzz"; /* ** Find the rows in which the FirstNames column content ** starts with "Buzz", print all Firstnames and Surnames ** of these rows */ SELECT FirstNames, Surname FROM Golfers WHERE FirstNames LIKE "Buzz%"; PRINTALL; /* ** Undo the changes, repeat the find and print part to ** ensure the undo did really work */ UPDATE Golfers SET FirstNames = "Buzz" WHERE FirstNames == "Buzzz"; SELECT FirstNames, Surname FROM Golfers WHERE FirstNames LIKE "Buzz%"; PRINTALL; Screen output: Buzzz Bee Buzz Bee |
<delete_stat> ::= DELETE [ FROM ] <table_reference> [ WHERE <search_cond> | WHERE CURRENT OF <cursor> ] ';'
Delete a row or set of rows from a particular table. Two forms of deletion are supported, positioned and searched. Searched is the most commonly used form. The <cursor> in the WHERE CURRENT OF clause must contain a rowset selected FOR UPDATE.
The following applies to the WHERE CURRENT OF <cursor> variant:
The cursor must specify an updatable table or view and must specify a rowset created by a SELECT statement including the <update_mode>.
The database may include referential-integrity constraints that prevent certain rows from being dropped. Attempts to drop these rows will result in an error.
Rows from a view may only be dropped, if the view is derived from a single table.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
The following applies to the WHERE <search_cond> variant:
The database may contain referential integrity constraints that prevent certain rows from being dropped. Attempts to drop these rows will result in an error.
Rows from a view may only be dropped if the view has been derived from a single table.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
|
Example
/*
** Let's first insert some test data, so we don't have
** to play with the Golfers' Database "real" data
*/
INSERT INTO Results
(
Year,
Competition,
Placing,
Golfer,
TotalScore,
Points,
Winnings
)
VALUES
(
1900,
2,
2,
4,
503,
3,
"$600"
);
/*
** Check if the data really got entered fine into the
** Results table
*/
SELECT Year FROM RESULTS WHERE Year == 1900;
PRINTALL;
/*
** Now let's remove this row again...
*/
DELETE FROM Results WHERE Year == 1900;
/*
** Check if the data really got deleted from the Results
** table
*/
SELECT Year FROM RESULTS WHERE Year == 1900;
PRINTALL;
Screen output:
1900
|
BEGIN
<begin_stat> ::= BEGIN [ WORK | TRANSACTION | TRANS ] [ [ FOR ] <dbms_alias> ] ';'
In PrimeBaseTalk a transaction must be explicitly begun using this statement. A transaction is a unit of work (updates to a specific DBMS). If the FOR clause is omitted, the currently in-use database is assumed.
Transactions are started and committed for each DBMS (connection) independently. There is currently no mechanism to synchronize transactions across DBMS's. If an error occurs during the transaction, it will be automatically aborted ("rolled back").
When no transaction has been started with BEGIN transaction, each SQL statement will be executed in a server controlled transaction and is commited automatically.
Data definition statements (CREATE, DROP, ALTER ) are not allowed after starting a transaction with BEGIN.
<commit_stat> ::= COMMIT [ WORK | TRANS | TRANSACTION ] [ [ FOR ] <dbms_alias> ] ';'
Commit all updates done during the current transaction. If this statement completes without error, the DBMS guarantees that the data has been written. If the FOR clause is omitted, the currently in-use database is assumed. If no transaction is currently in progress, this statement is ignored.
|
Example/* ** Begin the transaction */ BEGIN; /* ** Some test data */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1899, 2, 2, 4, 503, 3, "$600" ); /* ** Check if the data got entered into the Results table ** already */ SELECT Year FROM RESULTS WHERE Year == 1899; PRINTALL; /* ** Now let's commit, thus write the data into the Results ** table */ COMMIT; /* ** Now let's remove this row again... transaction- ** oriented again, of course */ BEGIN; DELETE FROM Results WHERE Year == 1899; COMMIT; /* ** Check if the data really got deleted from the Results ** table */ SELECT Year FROM RESULTS WHERE Year == 1899; PRINTALL; Screen output: 1899 |
<rollback_stat> := ROLLBACK [ WORK ] [ [ FOR ] <dbms_alias> ]';'
Cancel all updates done during the current transaction to databases on the specified DBMS and begin a new transaction. If the FOR clause is omitted, the currently in-use DBMS is assumed. If no transaction is currently in progress, this statement is ignored.
|
Example/* ** Let's try something really awesome now */ BEGIN; /* ** Some test data */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1899, 2, 2, 4, 503, 3, "$600" ); /* ** Ahm, maybe that wasn't so good an idea, let's forget ** about it */ ROLLBACK; /* ** Check that the data really didn't make it into the ** Results table */ SELECT Year FROM RESULTS WHERE Year == 1899; PRINTALL; Screen output: (none) |
In some of the examples used in this reference manual, the golfers database is used to demonstrate the various commands of PrimeBaseTalk.
A brief description of the tables in this database is given here, followed by the creation script of the database, which also insert sample data which the examples work with.
You can copy and paste this script into a texteditor and store it in a file so it can be executed using the EXECUTE FILE command from within the PrimeBase Automation Client (PBAC) or the PrimeBase SQL Database Server console for example.
The golfers database consists of six tables storing information on golfers, golf clubs, golf courses, competitions, results of competitions and scores.
The golfers table contains one entry for each golfer.
Column
Description
ID An identity number for each golfer.
This number is taken from the domain GolferID, which has a serial default defined on it.
The primary key for this table is defined on this column, as is an index.SurName The last name of the golfer.This value is taken from the domain NameType, which is ordered using a case insensitive system collating sequence. FirstNames The first names of each golfer. This value is taken from the domain NameType, whose values are defined to be case insensitive. This is done by specifying that the domain is to be ordered using the case insensitive system collating sequence. Name This is a composite column, which takes its values from the columns, SurName and FirstName. This column is a candidate key for the table, which means the composite values of the column are table-wide unique. Title This column stores the title of each golfer: for example, whether the golfer is a Mr., Mrs., Miss, etc., etc. Gender The sex of each golfer. A rule has been defined on this column, allowing only the values 'M' and 'F'. Nationality The nationality of each golfer. All values in this column are taken from the domain NameType. DateOfBirth The date of birth of each golfer. Status The status of each golfer; whether he/she is an amateur, or professional. All values in this column are taken from the domain StatusType, which has a rule define on it, allowing only the following values: Amateur, Pro and Pro/Am. The value Pro/Am, can be applied to competitions only, and means that the competition includes both amateur and professional golfers. Note that the values are casesensitive. Handicap The handicap for each golfer is noted here. All values in this column are taken from the domain HandicapType. A rule has been defined on this domain, allowing only those values in the range between 36 and -5 (inclusive). MemberOfClub This is an identity number for the club in which each golfer plays. A foreign key is defined on this column. All values are taken from the domain ClubID. The value may be $null if the player is not a member of any club. Earnings This columns contains the amount of money this golfer has earned so far.
The Clubs table contains one entry for each club to which the golfers belong, or at which competitions take place.
Column
Description
ID An identity number for each club. All values are taken from the domain ClubID. The primary key for the table is defined on this column, as is an index. Name The name of the club. All values are taken from the domain NameType. A candidate key is defined on this column. Address The address of the club. The data type of this column is VARCHAR, with a maximum size of 120 characters. City The city in which the club is situated. Country The country in which the club is situated. Founded The date when the club was founded. Professional The ID of the professional golfer who works at the club. A foreign key is defined on this column. The foreign key automatically references the Golfers table over the domain GolferID. NoOfCourses The number of golf courses at the club. NoOfMembers The total number of members that belong to the club.
Each club has a number of courses. The details of each hole of each course, are stored in the Courses table.
Column
Description
Club The ID of the club, to which this course belongs. The foreign key for the table is defined on this column. Course The number of the course. All values are taken from the domain CourseNO, which has a rule defined on it, ensuring that all values are greater than zero. Courses are numbered 1,2,... up to the total number of courses for that particular club. Hole The number of the hole on the course. All values are taken from the domain HoleNO. A rule is defined on this domain, allowing only number in the range 1 to 18. Key A composite column, that takes its values from the simple columns, Club, Course, and Hole, in that order. The primary key for the table is defined on this composite column, as is an index. Distance The total distance of the hole in meters. Par The average number of strokes it takes a professional player to complete the hole. A rule is defined on this column, allowing only values 3, 4, and 5. A default has also been defined on this column, that automatically sets the par for a hole at 4. Stroke This is the difficulty rating for each hole. This rating is a value between 1 and 18, (inclusive). All values are taken from the domain HoleNo, which has a rule enforcing this restriction. Description A description of the hole. The data type of this column is VARCHAR, and the description may not be more than 120 characters long.
The Competitions table contains information on all competitions played by the golfers.
Column
Description
ID An identity number for each competition. All values are taken from the domain CompetitionID. The primary key for the table is defined on this column, as is the index. Name The name of the competition. All values are taken from the domain NameType. A candidate key for the table is defined on this column. Status The status of the competition; whether it is amateur, professional, or whether amateurs and professionals play together. All values are taken from the domain StatusType, which has a rule defined on it, allowing only the above values. Month The month in which the competition takes place. The data type of this column is SMINT. It is not DATE, as this is a record of an annual competition, therefore there is no need to state a year. Day The day on which the competition takes place. The data type of this column is SMINT. Club The ID of the club where the competition takes place. All values are taken from the domain ClubID. A foreign key is defined on this column, that automatically references the Clubs table of the domain ClubID. Course The number of the course where the competition takes place.All values are taken from the domain CourseNO. NoOfRounds The number of rounds in the competition. This column has a default defined on it, setting the value automatically to 4. All values are taken from the domain RoundNO. Scoring This column records the method of scoring at each competition. A rule is defined on this column that allows the scoring values, "Medal" (an absolute score count), "Stableford" (a point system), "Skins" (a game in which golfers play for money on each hole), and "PlusMinus" (another type of point scoring system)
Each competition has a number of results. The details are stored in the Results table.
Column
Description
Year The year the competition took place. The data type of this column is SMINT. Competition The ID of the competition in question. A foreign key is defined on this column referring to the Competitions table. Placing The placing (1st, 2nd, 3rd...) obtained by a golfer who played in the competition. Key This is a composite column, which takes its values from the simple column, Year, Competition and Place. The primary key for the table is defined on this column, as is the index. Golfer The ID of the golfer who played in the competition. A foreign key is defined on this column. TotalScore The sum of the golfer's score for each round of the competition. Points The value here depends on the type of competition. For example, in a Medal competition the value is the number of strokes above (a positive value) or below (a negative value) par at the end of the competition. In a Stableford competition, this field contains the total number of Stableford points obtained. Winnings How much the golfer won in the competition. The data type of this column is MONEY[12,2].
The Scores table contains the score for each hole obtained by each player in each competition.
Column
Description
Year The year in which the player took part in the competition. Competition The ID for the competition. All values are taken from the domain CompetitionID. A foreign key is defined on this column, that references the Competitions table. Golfer The ID for the golfer who took part in the competition. All values are taken from the domain GolfersID. A foreign key for the table is defined on this column. Round The number of the round, in which this hole was played. All values are taken from the domain RoundNO. Hole The number of the hole played. Key This is a composite column, which takes its values from the simple columns, Year, Competition, Golfer, Round, and Hole. The primary key for the table is defined on this column, as is the index. Score The number of strokes it took the golfer to play the hole. There is a rule defined on this column, that states that all values must be greater than zero. HoledOut This is a column of BOOLEAN type. If FALSE, this means that the player did not complete the hole. In a medal competition this would disqualify the player, but in other types of Competitions, this would only mean the player obtains the worst possible score for the hole. Points The number of points achieved at the hole. The value here depends on the coring system used in the competition.
This script creates the golfers database and fills it with sample data:
/* ** GOLFERS: */ CREATE DATABASE "Golfers"; OPEN DATABASE "Golfers"; CREATE COUNTER INTEGER GolferCnt = 1; CREATE COUNTER INTEGER ClubCnt = 1; CREATE COUNTER INTEGER CompetitionCnt = 1; CREATE DOMAIN GolferID INTEGER NOT NULL; CREATE DEFAULT GolferDef ON DOMAIN GolferID AS SERIAL GolferCnt; CREATE DOMAIN CompetitionID INTEGER NOT NULL; CREATE DEFAULT CompetitionDef ON DOMAIN CompetitionID AS SERIAL CompetitionCnt; CREATE DOMAIN ClubID INTEGER; CREATE DEFAULT ClubDef ON DOMAIN ClubID AS SERIAL ClubCnt; CREATE DOMAIN NameType VARCHAR[55] ORDER AS CASE INSENSITIVE; CREATE DOMAIN StatusType CHAR[8]; CREATE RULE StatusRule ON StatusType AS StatusType IN ( 'Amateur', 'Pro', 'Pro/Am' ); CREATE DOMAIN CourseNO SMINT; CREATE RULE CourseRule ON CourseNO AS CourseNO > 0; /* ** Handicaps are stored as value that is subtracted from ** the final score: */ CREATE DOMAIN HandicapType SMINT; CREATE RULE HandicapRule ON HandicapType AS HandicapType BETWEEN 36 AND -5; CREATE DOMAIN RoundNO SMINT; CREATE RULE RoundRule ON RoundNO AS RoundNO BETWEEN 1 AND 4; CREATE DOMAIN HoleNO SMINT; CREATE RULE HoleRule ON HoleNO AS HoleNO BETWEEN 1 AND 18; /* ** The golfers table contains one entry for each golfer. */ CREATE TABLE Golfers ( ID GolferID NOT NULL, SurName NameType NOT NULL, FirstNames NameType NOT NULL, Name (SurName, FirstNames), Title CHAR[10], Gender CHAR[1] NOT NULL, Nationality NameType, DateOfBirth DATE, Status StatusType, Handicap HandicapType, MemberOfClub ClubID, Earnings MONEY[12,2] ); CREATE PRIMARY KEY GolfersPk ON Golfers.ID; CREATE CANDIDATE KEY GolferNameCk ON Golfers.Name; CREATE FOREIGN KEY GolferClubFk ON Golfers.MemberOfClub; CREATE INDEX GolfersIndex ON Golfers (ID); CREATE RULE GenderRule ON Golfers AS Gender IN ( 'M', 'F' ); /* ** The Clubs table contains one entry for each club to ** which golfers belong, or at which competitions take ** place. */ CREATE TABLE Clubs ( ID ClubID NOT NULL, Name NameType NOT NULL, Address VARCHAR[120], City NameType, Country NameType, Founded DATE, Professional GolferID, NoOfCourses SMINT NOT NULL, NoOfMembers INTEGER NOT NULL ); CREATE PRIMARY KEY ClubsPk ON Clubs.ID; CREATE CANDIDATE KEY ClubNameCk ON Clubs.Name; CREATE FOREIGN KEY ClubProFk ON Clubs.Professional ON UPDATE CASCADE ON DELETE CASCADE; CREATE INDEX ClubsIndex ON Clubs (ID); /* ** Each club has a number of courses. The details of each ** hole of each course, are stored in the Courses table */ CREATE TABLE Courses ( Club ClubID NOT NULL, Course CourseNO NOT NULL, Hole HoleNO NOT NULL, Key ( Club, Course, Hole ), Distance SMINT, Par SMINT, Stroke HoleNO, Description VARCHAR[120] ); CREATE PRIMARY KEY CoursesPk ON Courses.Key; CREATE FOREIGN KEY CourseClubFk ON Courses.Club; CREATE INDEX CoursesIndex ON Courses (Club, Course, Hole); CREATE RULE ParRule ON Courses AS Par IN ( 3, 4, 5 ); CREATE DEFAULT ParDef ON Courses.Par AS 4; /* ** The Competitions table contains information of all ** competitions played by the golfers. */ CREATE TABLE Competitions ( ID CompetitionID NOT NULL, Name NameType NOT NULL, Status StatusType NOT NULL, Month SMINT, Day SMINT, Club ClubID, Course CourseNO, NoOfRounds RoundNO, Scoring CHAR[20] ); CREATE PRIMARY KEY CompetitionsPk ON Competitions.ID; CREATE CANDIDATE KEY CompetitionNameCk ON Competitions.Name; CREATE FOREIGN KEY CompetitionClubFk ON Competitions.Club; CREATE INDEX CompetitionsIndex ON Competitions (ID); CREATE DEFAULT NoOfRoundsDef ON Competitions.NoOfRounds AS 4; CREATE RULE ScoringRule ON Competitions AS Scoring IN ( 'Medal', 'Stableford', 'Skins', 'PlusMinus' ); /* ** Each competition has a number of results. The details ** are stored in the Results table. */ CREATE TABLE Results ( Year SMINT NOT NULL, Competition CompetitionID NOT NULL, Placing SMINT NOT NULL, Key ( Year, Competition, Placing ), Golfer GolferID, TotalScore SMINT, Points SMINT, Winnings MONEY[10,2] ); CREATE PRIMARY KEY ResultsPk ON Results.Key; CREATE FOREIGN KEY ResultGolferFk ON Results.Golfer; CREATE INDEX ResultsIndex ON Results (Year, Competition, Placing); /* ** The Scores table contains the score for each hole ** obtained by each player in each competition. */ CREATE TABLE Scores ( Year SMINT NOT NULL, Competition CompetitionID NOT NULL, Golfer GolferID NOT NULL, Round RoundNO NOT NULL, Hole HoleNO NOT NULL, Key ( Year, Competition, Golfer, Round, Hole ), Score SMINT, HoledOut BOOLEAN, Points SMINT ); CREATE PRIMARY KEY ScoresPk ON Scores.Key; CREATE FOREIGN KEY ScoreGolferFk ON Scores.Golfer; CREATE INDEX ScoresIndex ON Scores (Year, Competition, Golfer, Round, Hole); CREATE RULE ScoreRule ON Scores AS Score > 0; /* ** The View, GolfersAmateurs, includes all those golfers ** who have the Status, "Amateur" */ CREATE VIEW GolfersAmateurs AS SELECT ID, Name, Title, Handicap, Status FROM Golfers WHERE Status = "Amateur" WITH CHECK OPTION; /**************************** ** Create 1st club */ /*************** ** Create Club */ INSERT INTO Clubs ( ID, Name, Address, City, Country, Founded, Professional, NoOfCourses, NoOfMembers ) VALUES ( 1, "Sterling Club Hamburg", "20000 Hamburg, 12 Street of Joy", "Hamburg", "Germany", "01/01/1998", 1, 2, 2 ); /*************** ** Create 2 Courses */ INSERT INTO Courses ( Club, Course, Hole, Distance, Par, Stroke, Description ) VALUES ( 1, 1, 1, 200, 5, 10, "Not exactly easy, but kept well in shape, this " + "course offers an interesting arrangement of " + "different elements." ); INSERT INTO Courses ( Club, Course, Hole, Distance, Par, Stroke, Description ) VALUES ( 1, 2, 2, 200, 5, 10, "Not exactly difficult this course offers an " + "interesting arrangement of different elements." ); /*************** ** Create 2 Golfers */ INSERT INTO Golfers ( ID, SurName, FirstNames, Title, Gender, Nationality, DateOfBirth, Status, Handicap, MemberOfClub, Earnings ) VALUES ( 1, "Bee", "Buzz", "Mr.", "M", "dutch", "10/01/1960", "Pro", 20, 1, "$5000" ); INSERT INTO Golfers ( ID, SurName, FirstNames, Title, Gender, Nationality, DateOfBirth, Status, Handicap, MemberOfClub, Earnings ) VALUES ( 2, "Dry", "Tumble", "Mrs.", "F", "british", "12/10/1959", "Pro", -3, 1, "$3,999" ); /*************** ** Create Competition */ INSERT INTO Competitions ( ID, Name, Status, Month, Day, Club, Course, NoOfRounds, Scoring ) VALUES ( 1, "Sterling Club Competition", "Pro/Am", 6, 7, 1, 1, 4, "Medal" ); /*************** ** Create Results */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1990, 1, 1, 1, 1000, 2, "$1111.11" ); INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1990, 1, 2, 2, 500, 3, "$333.33" ); /**************************** ** Create 2nd club */ /*************** ** Create Club */ INSERT INTO Clubs ( ID, Name, Address, City, Country, Founded, Professional, NoOfCourses, NoOfMembers ) VALUES ( 2, "Astronauts Golf Club", "2001 Oddyssey, 12 Cape Canaveral Street", "Space-City", "Universe", "01/01/2001", 1, 2, 2 ); /*************** ** Create 2 Courses */ INSERT INTO Courses ( Club, Course, Hole, Distance, Par, Stroke, Description ) VALUES ( 2, 1, 1, 200, 5, 10, "Not exactly easy, but kept well in shape, this " + "course offers an interesting arrangement of " + "different elements." ); INSERT INTO Courses ( Club, Course, Hole, Distance, Par, Stroke, Description ) VALUES ( 2, 2, 2, 200, 5, 10, "Not exactly difficult this course offers an " + "interesting arrangement of different elements." ); /*************** ** Create 2 Golfers */ INSERT INTO Golfers ( ID, SurName, FirstNames, Title, Gender, Nationality, DateOfBirth, Status, Handicap, MemberOfClub, Earnings ) VALUES ( 3, "Dumsdee", "Humsdee", "Mrs.", "F", "french", "01/21/1972", "Pro", 20, 2, "$10,999.99" ); INSERT INTO Golfers ( ID, SurName, FirstNames, Title, Gender, Nationality, DateOfBirth, Status, Handicap, MemberOfClub, Earnings ) VALUES ( 4, "Head", "Fish", "Mr.", "M", "canadian", "02/07/1954", "Pro", -2, 2, "$3,520" ); /*************** ** Create Competition */ INSERT INTO Competitions ( ID, Name, Status, Month, Day, Club, Course, NoOfRounds, Scoring ) VALUES ( 2, "Space Competition", "Pro", 7, 6, 2, 2, 2, "Medal" ); /*************** ** Create Results */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1990, 2, 1, 2, 823, 1, "$2000" ); INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1990, 2, 2, 4, 503, 3, "$600" );
e-mail: info@primebase.net
Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.
