SQL Language

TIBSQL

The TIBSQL support a subset of the standadt SQL.This document  There are the end of this document the  keywords     .

In all of the syntax diagrams that follow, literal text is shown in bold blue. Non-terminal symbols are shown in italic red. Operators that are part of the syntactic markup itself are shown in black roman.

The Database names, table names, columns names must be  1..32 caracters. Only use the english ABC letters, numbers and '_' character.

TIBSQL implements the follow syntax:

Details on the implementation of each command are provided in the sequel.


ALTER TABLE

sql-statement ::= ALTER TABLE table-name ADD colmn-name type

The  ALTER TABLE statement add a new column the table


BEGIN TRANSACTION

sql-statement ::= BEGIN TRANSACTION

Start the new transaction. If there is a activ transaction then commit the activ transaction and start a new transaction. 


COMMIT

sql-statement ::= COMMIT

Store the changed data into disc.


CREATE DATABASE

sql-statement ::= CREATE DATABASE database-name

The CREATE DATABASE statement create a new database.


comment

comment ::= /* multiple-lines */

Comments.


CREATE TABLE

sql-command ::= CREATE  TABLE table-name (
    
column-def [, column-def]*
    
)
column-def ::= name type
type ::=

CHAR(méret) |
INTEGER |
FLOAT|
DATE|
LOGICAL

The CREATE TABLE statement create a new table

Data type are::

CHAR ANSI characters, size must define in the CREATE TABLE statement ( 1..255)
INTEGER 32 bits integer (-2,147,483,638 ... 2,147,483,647)
FLOAT floatingpoint number (5.0E-324 ... 1.7E308)
DATE date
LOGICAL 'T' or 'F'

Tábea erase: DROP TABLE statement.tabe structure modify is  ALTER TABLE statement.


DELETE

sql-statement ::= DELETE FROM  table-name [WHERE expr]

The DELETE command is used to remove records from a table. The command consists of the "DELETE FROM" keywords followed by the name of the table from which records are to be removed.

Without a WHERE clause, all rows of the table are removed. If a WHERE clause is supplied, then only those rows that match the expression are removed.


DROP DATABASE

sql-command ::= DROP DATABASE database-name 

The DROP DATABASE statement erase a database.


DROP TABLE

sql-command ::= DROP TABLE  table-name

The DROP TABLE statement erase a table.


END TRANSACTION

sql-command ::= END TRANSACTION

Equal the FLUSH statement


FLUSH

sql-command ::= FLUSH

Write the changed data into disc, and start a new transaction.


expression

expr ::= expr1 | agregat-function(expr1) | COUNT(*)
expr1 ::= expr1 operand expr1 |
like-op |
NOT expr1|

( expr1 ) |
column-spec |
constant |
normal-function ( expr1 ) |
expr1 IS NULL |
expr1  IS NOT NULL |
expr1 [NOT] BETWEEN expr1 AND expr1 |
expr1 [NOT] IN ( value[,value]* )
column-spec := [table-alias .] column-name
operand := + | - | * | / | || | < | > | <= | >= | = | <> | OR | AND 
constant := 'caracters' | number | 'yyyy.mm.dd'
normal-function := SUBSTR | ABS | UPPER 
agregat-function := MIN | MAX | SUM | AVG
like-op ::=

column-name [NOT]  LIKE "%karakterek" |
column-name [NOT]  LIKE "karakterek%"

TIBSQL  understands the following binary operators, in order from highest to lowest precedence:


1. precedence level:


 

   ||   *    /  <    <=  >    >=  =      <>   IN   AND   OR normál-függvények< BR >

2. precedence level:
 


   + -


        
     3. priecdence level

         
      

   agregát-függvények
         In
a precedence level the processing left to right.
         
       

         
   TIBSQL automatic type conversions: 

NULL value handle

The NULL value in CHAR column is '' (emty string), in INTEGER or FLOAT column is 0 (zero),in DATE column is '1900.01.01', in LOGICAL column is False.


INSERT

sql-statement ::= INSERT  INTO  table-name [(column-list)] VALUES(value-list) |
INSERT 
INTO  table-name [(column-list)] select-statement
column-list := column-name [ , column-name ]*
value-list:= konstans [ , konstans ]*

The INSERT statement add new record or records into the table.


ROLLBACK

sql-statement ::= ROLLBACK

The ROLLBACK statement drop the data chang.


SELECT

sql-statement ::=

egyszerü-select
[union_op sql-statement]*
[ORDER BY expr-list]

egyszerü-select:=

SELECT [ DISTINCT] result
[FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]

result ::= result-column [, result-column]*
result-column ::= * |
[table-alias . ]col-name  [ alias ]  |
expr   [ alias ]
table-list ::= table [join-op table join-args]*
table ::= table-name [ alias] 
join-op ::= , | LEFT OUTER JOIN  |   INNER  JOIN
join-args ::= [ON expr] 
expr-list ::= expr  [, expr]*
union-op ::= UNION | UNION ALL 

The 'expr' in the  'result-column' must be STRING,INTEGER,FLOAT,DATE type.

The 'expr' in the WHERE, 'join-arg' and HAVING must by LOGICAL type.

Precept for the ORDER BY::

Remarks: 

Order the processing:

  1. Select the input tables, joins.Result a virtual table, There are the full input columns in the  result table.
  2. Filter by WHERE expression
  3. If there is not  UNION and  there is not GROUP BY and there are not column's  number in the  ORDER BY then short by  'ORDER BY'
  4. Make the result table by SELECT and  GROUP BY and DISCTINCT
  5. Filter by HAVING expression
  6. If the are more UNIONs then repeat the 1..5 steps, and append into result table.
  7. If there are more UNION or there is  GROUP BY  then sort by 'ORDER BY'

 


UPDATE

sql-statement ::= UPDATE    table-name
SET
assignment [, assignment]*
[WHERE expr]
assignment ::= column-name = expr

The UPDATE statement change the data in the table.


TIBSQL keywords

DATABASE    DESC    FOR     INNER    LEFT     OF    OUTER       ALL    AND     BETWEEN    BY     CREATE    DELETE    DISTINCT    DROP   FROM    GROUP    HAVING    IN    INDEX    INSERT    INTO    IS    JOIN    LIKE    NOT   NULL    ON    OR    ORDER     SELECT    SET    TABLE   UNION   UPDATE    VALUES    MIN MAX AVG SUM COUNT SUBSTR UPPER ABS  WHERE   BEGIN END COMMIT ROLLBACK FLUSH