PrimeBase SQL Reference Manual

Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.

The information contained in this document applies to:

Last major update to this document on March 2nd, 2001.


Table of Contents

1. Introduction

Examples

2. Basic Elements

Data Types

BOOLEAN, CHAR, CURSOR, DATE, DECIMAL, FLOAT, GENERIC, INTEGER, LONGBIN, LONGCHAR, MONEY, OBJNAME, REAL10, REAL12, SMFLOAT, SMINT, TIME, TIMESTAMP, TINYINT, UNICODE, VARBIN, VARCHAR

Literal Values

Integer, Decimal, Money, Floating-point, Boolean, Character String, Binary, Unicode, Date, Time, Datetime

Expressions

Boolean Operators
Comparison Operators
Arithmetic Operators
String Concatenation Operator
Date and Time Calculations
Bit-wise Operators
Unary Operators
Data Type Conversions
Cursor-based Reference

Search Conditions

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

Query Specification

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

A. Golfers Database


1. Introduction

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).

 

Examples

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>

 


2. Basic Elements

 

Data Types

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.

 

Number Data Type Constant Size Scale Description
1
BOOLEAN $boolean
-
-
3-valued BOOLEAN value: $true, $false or MAYBE. Size is 1 byte.
2
SMINT $smint
-
-
2-byte signed INTEGER value.
3
INTEGER $integer
-
-
4-byte signed INTEGER value.
4
SMFLOAT $smfloat
-
-
4-byte floating point value. (1)
5
FLOAT $float
-
-
8-byte floating point value.
6
DATE $date
-
-
4-byte DATE value (2-byte year, 1-byte month, 1-byte day).
7
TIME $time
-
-
4-byte TIME value (1-byte hours, 1-byte minutes, 1-byte seconds, 1-byte 1/100 sec).
8
TIMESTAMP $timestamp
-
-
8-byte DATE/TIME value (4-byte TIME followed by 4-byte DATE). (2)
9
CHAR $char
yes (3)
-
Fixed length character string. Space padded if necessary.
10
DECIMAL $decimal
yes (4)
yes (5)
Fixed point binary code decimal (BCD) value.
11
MONEY $money
yes (4)
yes (5)
Fixed point binary code decimal (BCD) monetary value.
12
VARCHAR $varchar
yes (5)
-
Variable length character string.
13
VARBIN $varbin
yes (6)
-
Variable length binary value.
14
LONGCHAR $longchar
-
-
Very large character string value.
15
LONGBIN $longbin
-
-
Very large binary value.
20
TINYINT
-
-
-
1-byte unsigned value. (7)
23
REAL10
-
-
-
10-byte floating point value. (8)
24
REAL12
-
-
-
12-byte floating point value. (8)
27
UNICODE
$unicode
yes (6)
-
Fixed length UNICODE character string using a 16 bit character set.

 

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.

 

Number Data Type Description
16
OBJNAME A reference to a database object (table, column, etc). An OBJNAME value can contain complex references, e.g. 'Master!System.SysConnections'.
17
GENERIC A value of unspecified data type. PrimeBaseTalk variables of this type can contain values of any type except CURSOR. The type of the value can be inspected using the $typeof() built-in function.
18
CURSOR A reference to a rowset selected from a database, or returned by a DESCRIBE statement. Columns of the current row of the CURSOR may be referenced using the '->' operator.

 

Literal Values

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:

Literal Type Description Examples
INTEGER A signed INTEGER value. 0, -1, 1000, +2300789
DECIMAL A decimal (BCD) value, containing a decimal point ('.'). 0.1, -123.0, 12000045.000003, +1.99999999999
MONEY A decimal value beginning with a dollar ('$') sign. Quoted string for other formats. $0.1, $-123.001, $1000000.0, 'EUR 7,631,033.56'
FLOAT A value in exponential form, with a decimal point and a signed exponent of an 'e' or 'E'. 1e0, 0.123e12, 3.141E0, 100.00001e-14, -5.6789E+8
BOOLEAN A true of false value. $true, $false, $true, $False
VARCHAR A string of characters in double or single quotes (1). "Hello!", '1,234,567', "Jack said: ""Hi!"""
VARBIN A binary value in hexadecimal format (2). 0xAE1FB955, 0Xa4cfa18D3476cdffAA, X"1EBB"
UNICODE quoted string with optional \uXXXX sequences (3) "Primebase\u21224.0, \u00A9 2008, PrimeBase Systems GmbH"
DATE quoted string formated according to the $datefmt. "03/10/2001", '18.09.1977', date($now())
TIME quoted string formated according to the $timefmt. "15:31:45.54", '11:30 PM'
DATETIME quoted string formated according to the $tsfmt. "03/10/2001 15:33:46.98", "10. JAN 2001 15:33", $now()

 

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.

 

Expressions

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

Boolean Operators

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.

 

Comparison Operators

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.
 

 

Warning

Note that trailing spaces are ignored when comparing strings, meaning that 'a ' == 'a    ' evaluates to boolean true.

 

Arithmetic Operators

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
 

 

String Concatenation Operator

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().

 

Example

  PRINT "Hello" + " " + "world" + "!";


  Screen Output:

  Hello world!
 

  

Date and Time Calculations

<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.

Bit-wise Operators

The bit-wise operations are those familiar to most programmers: bit-wise AND ( & ), bit-wise OR ( | ), bit-wise exclusive-OR ( ^ ).

 

Example


  PRINT "255 & 127 =", VARCHAR( 255 & 127 );


  Screen Output:

  255 & 128 = 127
 

 

Unary Operators

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>

  

Data Type Conversions

<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.

 

Example

  PRINT TINYINT ( INTEGER '10' );


  Screen Output:

  10
 

 

Example

  INTEGER personalID = 10;

  VARCHAR personalIDString = VARCHAR personalID;

  PRINT "The ID of this person is " + personalIDString;


  Screen Output:

  The ID of this person is 10
 

 

Example

  INTEGER 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
 

 

 

Cursor-based Reference

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>


Search Conditions

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.

 

Boolean Operators

<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

 

 

 

Comparison Operators

<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
 

 

 

Outer Join Operators

<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").

Null extended indicator

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
 

 

 

BETWEEN Operator


<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
 

 

IN Operator

<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

 

 

LIKE Comparison

<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 '\';
 

 

IS NULL Test

<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
 

 

Subqueries

<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
 

 

Exists Test

<srch_exists> ::= (  [ NOT ] EXISTS |
                    FOR ALL ) <subquery>

 

Full Text Search

<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>.

 

Arithmetic Operators

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>

 

Data Type 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:

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
 

Unary Negation

<srch_factor> ::= [ '+' | '-' | '~' ] <srch_primary>

 

Basic Elements

<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 References

<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.

 

Warning

  VARCHAR 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

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
 

 

Generic Functions

When arguments have different types, sizes or scales, the return type will have the larger types, sizes and scales.

 

GENERIC IF(cond, GENERIC a, GENERIC b)

When <cond> evaluates to TRUE, <a> is returned, else <b> is returned.

 

GENERIC GREATEST(GENERIC a, GENERIC b)

The function compares the arguments and returns the greatest value.

 

GENERIC LEAST(GENERIC a, GENERIC b)

The function compares the arguments and returns the least value.

 

GENERIC NVL(GENERIC a, GENERIC b)

When <a> is not NULL <a> is returned, else <b> is returned.

 

VARCHAR USER()

Returns the user name for the current connection to the database server.

 

DATETIME NOW()

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.

 

Character Functions

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.

 

SMALLINT ASCII(char c)

Returns <c> as the binary equivalent INTEGER number.

 

VARCHAR CHR(INTEGER n)

Returns the lower 8 bits of <n> as a binary equivalent character of the host character set.

 

VARCHAR CONCAT(VARCHAR a, VARCHAR b)

UNICODE CONCAT(UNICODE a, UNICODE b)

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.

 

VARCHAR INITCAP(VARCHAR string)

UNICODE INITCAP(UNICODE string)

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.

 

VARCHAR TOLOWER(VARCHAR string)

UNICODE TOLOWER(UNICODE string)

Returns <string> with all alphabet characters in lower case.

 

VARCHAR TOUPPER(VARCHAR string)

UNICODE TOUPPER(UNICODE string)

Returns <string> with all alphabet characters in upper case.

 

VARCHAR WORDS( [ CHAR | VARCHAR | LONGCHAR ] database_column)

UNICODE WORDS(UNICODE database_column)

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';

 

INTEGER LENGTH(GENERIC string)

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.

 

VARCHAR LEFT(VARCHAR string, VARCHAR pattern [, INTEGER count])

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>.

 

VARCHAR RIGHT(VARCHAR string, VARCHAR pattern [, INTEGER count])

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>.

 

VARCHAR SUBSTR(VARCHAR string, INTEGER position [, INTEGER len])

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>.

 

INTEGER LOCATE(string, pattern [, INTEGER count])

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;

 

INTEGER LOCATEWORDS(VARCHAR string, VARCHAR pattern [, INTEGER max_dist])

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 '&Uuml;'.

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>.

 

VARCHAR REPLACE(VARCHAR string, VARCHAR search [, VARCHAR replacement])

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.

 

VARCHAR TRANSLATE(VARCHAR string, VARCHAR from [, VARCHAR to])

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.

 

VARCHAR XMLPATH(VARCHAR text, VARCHAR search_path, VARCHAR escape)

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.

 

VARCHAR TO_CHAR(GENERIC num , VARCHAR format)

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"

VARCHAR LTRIM(VARCHAR string [, VARCHAR set])

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.

 

VARCHAR RTRIM(VARCHAR string [, VARCHAR set])

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.

 

VARCHAR LPAD(VARCHAR string, int length, VARCHAR padding)

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.

 

VARCHAR RPAD(VARCHAR string, int length, VARCHAR padding)

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.

 

UNICODE NORMALIZE(UNICODE string, INTEGER form)

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 Composition

For more details see http://www.UNICODE.org/UNICODE/reports/tr15/.

 

Mathematical Functions

 

numeric ABS(numeric x)

Returns the absolute value of the numeric argument <x>. The returned data type is the same as the argument type.

 

FLOAT CEIL(FLOAT x)

Returns the smallest INTEGER not less than <x>.

 

FLOAT COS(FLOAT x)

Returns the cosine of argument <x>. (An angle expressed in radians.)

 

FLOAT COSH(FLOAT x)

Returns the hyperbolic cosine of argument <x> as FLOAT.

 

FLOAT EXP(FLOAT x)

Returns e to the <x>'th power. e is the base of natural logarithms.

 

FLOAT FLOOR(FLOAT x)

Returns the largest INTEGER value not larger than <x> as FLOAT.

 

FLOAT LN(FLOAT x)

Returns the natural logarithm of argument <x>.

 

FLOAT LOG10(FLOAT x)

Returns the base-10 logarithm of argument <x>.

 

FLOAT POWER(FLOAT x, FLOAT y)

Returns <x> raised to the power of <y>.

 

DECIMAL ROUND(DECIMAL x [, INTEGER m])

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.

 

GENERIC SIGN(GENERIC x)

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.

 

FLOAT SIN(FLOAT x)

Returns the sine of argument <x>.

 

FLOAT SINH(FLOAT x)

Returns the hyperbolic sine of argument <x>.

 

FLOAT SQRT(FLOAT x)

Returns the square root of <x>.

 

FLOAT TAN(FLOAT x)

Returns the tangent of <x> in radians.

 

FLOAT TANH(FLOAT x)

Returns the hyperbolic tangent of <x>.

 

DECIMAL TRUNC(DECIMAL x, INTEGER m)

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.

 

FLOAT ACOS(FLOAT x)

Returns the principal value of the arc cosine of argument <x>.

<x> must be in a range -1 to +1.

 

FLOAT ASIN(FLOAT x)

Returns the principal value of the arc sine of argument <x>.

 

FLOAT ATAN(FLOAT 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

 

FLOAT ATAN2(FLOAT x, FLOAT y)

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.

 

Query Specification

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 Operators (UNION, MINUS, INTERSECT)

<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>.

 

SELECT Clause

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
 

 

FROM Clause

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
 

 

WHERE Clause

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
 

 

GROUP BY Clause

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
 

 

HAVING Clause

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
 

 

ORDER BY Clause

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
 

 


4. Data Manipulation Statements (SQL)

 

SELECT

<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

<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.

 

Example


  CURSOR 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

<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

<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

<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

<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

<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)
 

 


 

4. Appendices

 

A. Golfers Database

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.

 

Database Description

The golfers database consists of six tables storing information on golfers, golf clubs, golf courses, competitions, results of competitions and scores.

 

Table - Golfers

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.

 

Table - Clubs

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.

 

Table - Courses

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.

 

Table - Competitions

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)

 

Table - Results

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].

 

Table - Scores

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.

 

Create Script

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

www: http://www.primebase.net

Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.