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.
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.
SELECT NAME SALARY - FROM EMPLOYEE - WHERE GENDER EQ 1You 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.
A PART_NUMBER NAME1
A non-standard name can contain blanks or use lowercase letters. Non-standard names might be:
{On hand} {SELECT} {part*}
Suppose a column is created with a 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
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.
SELECT NAME FROM COMPANY.EMPLOYEE ON MYTFILE.TABLE1
SELECT EMPLOYEE.ID DEPT.ID FROM EMPLOYEE DEPT
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
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
| |