SQL 
|
| SQL | | Overview |
The SQL module of SIR/XS implements the data retrieval, update and definition capabilities defined by "American National Standard X3.135-1986 Database Language SQL." In addition, SIR/XS has implemented many enhancements to simplify the interactive use of SQL and to take advantage of SIR/XS database structures.
The primary function of SQL is to select data from records and tables where particular conditions are true. The selected data always creates a new table. The data from a table can be then be displayed in a simple and straightforward manner. Tables can also be used by the other SIR/XS components such as VisualPQL to produce more complex analyses and outputs.
SQL creates and populates new tables and can also be used to create indexes and views to tables.
SQL can be used to update and modify databases and tables and can update whole sets of data which match particular conditions. While SQL also has some direct data entry functions, these are limited and data entry is better handled in other SIR/XS modules.
The SirSQL interface is a menu driven system, which, since SQL is a command based language, generates commands.
The main SQL command is SELECT, which selects data according to particular conditions, creating a new set of data on a new table. There are various formatting options for individual columns.
There are commands such as UPDATE which updates individual records or sets of records and CREATE which defines new tables.
The SQL settings such as the connected databases and tabfiles,
the limits on reading and writing records, any synonym definitions, path
definitions, etc. make up the workspace.
The SAVE and GET commands
save the workspace and get it again in subsequent sessions.
SQL uses a default workspace file,
called SirSQL.wsp which is loaded when you access SQL.
You can modify and save the default workspace, or create and use any
number of different workspace files.
A simple editor is used to enter commands into a command area. SQL commands can be created and saved for subsequent execution. Commands can be kept as members in the procedure file of a database or as operating system files. Commands can be run as a batch process.
Online help is available for explanation and syntax of all commands, options and clauses.
Queries are optimised to take advantage of database case structures and records
with common keys, simplifying the required query specifications and making the
retrieval more efficient. All quality controls, including security, defined in
the database schema are applied when using SQL for data entry or modification.
SQL can operate concurrently with other SIR/XS modules when reading data and through
Master for concurrent update.
Databases & Tabfiles
Databases
SQL can operate on multiple SIR/XS databases. Before working with a database, the
database must be connected. The last database connected is the default
database which is used whenever a particular database is not specified.Tables and Tabfiles
SQL can read, write and create tables. A table holds a single type of record
and is equivalent to a database record. Tables can be used by VisualPQL and
FORMS as well as by SQL. Tables can only be used for update by one user at
once. Tables are held on a Tabfile.
A tabfile contains tables, indexes to the tables plus System tables which hold information about other tables. A single tabfile can contain multiple tables. Tabfiles can contain security controls and authorised users may grant or revoke permissions for operations on a tabfile or on specific tables within a tabfile.
SQL can operate on multiple tabfiles. Before working with a tabfile, the tabfile must be connected.
A temporary system tabfile ($SYSTEM) is the default
used for creating temporary tables. This is created and deleted per session
and is not normally listed on displays of tabfiles.
The $ as the start
character in a name is used by the system to recognise system generated
names and so should not be used except for this purpose.
The system expects a file extension of .tbf to be used for
tabfiles.
A command normally ends at the end of a line. To specify that a command continues
on the next line, put a hyphen (continuation character) after all the text on the
line to be continued.
A non-standard name can contain blanks or use lowercase letters. Non-standard names might be:
Suppose a column is created with a
Syntax Rules
SQL is primarily a command based system so there are rules for the syntax
of the commands.
The syntax is intended to be English like and as natural as possible.
SQL translates all commands, keywords and names (unless a non-standard name) to
uppercase.SELECT NAME SALARY -
FROM EMPLOYEE -
WHERE GENDER EQ 1
You can submit multiple commands at one time. To do this, start each command on a new line.
The starting position of a command on a line is unimportant.
Names
Names are normal SIR/XS names. Standard names are 1 to 32 characters long with no spaces.
The first character must be alphabetic. Characters can be letters, digits or four characters
($, #, @,_). Names are translated to upper case so uppercase and lowercase letters are equal.
A name cannot be an SQL reserved word. The following are examples of
valid names:A PART_NUMBER NAME1
Non-standard names
If you wish to use a name which does not conform to these rules, enclose the name in curly braces
{} as per other SIR/XS references to non-standard names. (Note: for compatibility with previous
versions and with the SQL standard, the SIR/XS SQL module also supports the use of double quotes
"" as delimiters on input for non-standard names, but these will be translated to curly brackets
on output.){On hand} {SELECT} {part*}SELECT such as:SELECT ... max(salary)*1.1 .....
If this column is referred to in a subsequent SELECT, the column name is not a
valid SQL name and must be enclosed in curly brackets. This name was not specified as a non-standard
name when originally created and was thus mapped to upper case, so the reference would be:SELECT {MAX(SALARY)*1.1} .....
Note: It is possible to specify new column names with the FORMAT COLUMN NAME command and it is
possible to refer to columns by a column number. This minimises the need to have non-standard
column names, even where these have been derived from a calculation. For example:SELECT id salary*1.3 FORMAT COLUMN 2 NAME newsal
Single v Double Quotes
Strings inside single quotes are constants; strings inside double quotes are names. (This is
according to the SQL standard and is thus different from other SIR/XS modules standard behaviour.)
When creating a table, it is possible to specify constants as columns, so you must
use single or double quotes correctly. For example, suppose the following:SELECT 'MAX(SALARY)*1.1' .....
Because this command has single quotes, it creates a character constant with the
value MAX(SALARY)*1.1 rather than looking for a column with this name.Qualified Record and Table Names
Unless using the default database or tabfile, qualify record and table names
with the appropriate database or tabfile name. Separate the names by a period.
For example:SELECT NAME FROM COMPANY.EMPLOYEE ON MYTFILE.TABLE1
Qualifying variable names
Variables within a record or table always have unique names. However, variables
in different records may have the same name. If referring to two variables with
the same name from different records or tables, qualify the name by preceding it
with the record or table name. Separate the names by a period. For example:SELECT EMPLOYEE.ID DEPT.ID FROM EMPLOYEE DEPT
Alias
Sometimes you may need to qualify the record or table by a database or tabfile name. However,
qualification at two levels (A.B.C) is not a valid SQL format. In this case (or when joining a
record to itself) specify an alias. Specify a single name
after the qualified record or table name, optionally using the AS keyword and then
specify this to qualify
individual variable names used in the select, where, order by or other clauses. For example:SELECT A.ID B.ID FROM COMPANY.EMPLOYEE AS A, OLD.EMPLOYEE AS B -
WHERE A.ID = B.ID
Filenames
When specifying filenames in SQL, use the standard short or long
SIR/XS Filenames.
1 134 999 +333 -9322Specify real constants as a series of digits followed by a decimal point and another series of digits. They may be preceded by a + or - sign. Either the initial or terminal series of digits may be absent but not both. Specify a power of 10 exponent by suffixing the number by the character 'E' followed by the power of ten. There can be no embedded blanks or commas in the number. For example:
1. .44 +1.4 -.44433 123.456E3 333.432E-3
'aaa' 'THIS is A string' 'Bill''s job'
There are functions in SQL which convert numbers to strings and vice versa.
Dates, times and categorical variables can be either integers or strings and SQL decides which format to use from the expression that uses the variable. If a string is called for, the string is used; if a numeric value is called for, the integer value is used. For example, a time can be an integer (the number of seconds since midnight) or a character string such as '11.15AM'.
Numeric expressions consist of numeric variables, constants, all of the normal arithmetic operators (+, -, /, *, **), numeric functions, and parentheses. Expressions are evaluated according to normal precedence and parentheses can be used. Within equal precedence they are evaluated from left to right. Example numeric expressions are:
2 * 5 - 4 2 * ( 5 - 4 ) SALARY * 52 / 12String expressions consist of string variables, string constants in single quotes, string functions, the concatenation operator (+) and parentheses. Example string expressions are:
'ABC' + 'DEF' SBST('ABCDEF',4,3) TIMEC(NOW(0),'HH:MM:SS')
RND is a function which returns
a number rounded to the nearest whole integer;
SELECT NAME RND(SALARY*12/52) FROM EMPLOYEE
SELECT NAME ! get the full name - RND(SALARY*12/52) ! and weekly salary - FROM EMPLOYEE ! for each employee
| |