SQL - Grammar


BASIS SQL grammar generally conforms to popular SQL standards.

SQL-92

BBj supports most of the SQL-92 core grammar specification, with the exception of GRANT and REVOKE. The BBj SQL engine variations from the SQL-92 are due to backward compatibility issues with prior BASIS SQL products. BBj implements the SQL language specifications contained in the ANSI SQL-92 Database Language-SQL standard.

SQL-99 and SQL-2003

For detailed list of BBj's support of these specifications, refer to BBj Support for SQL-99 and SQL-2003 Features.

SQL Reserved Words

The following words are reserved for use by SQL. They do not have to be capitalized, but are displayed in the SQL grammar examples to make them more readily identifiable.

ALL

AND

AS

ASC

AVG

BETWEEN

BYTE

CHAR

CHARACTER

CREATE

COUNT

DATE

DECIMAL

DELETE

DESC

DISTINCT

DOUBLE

DROP

EXISTS (2.0 and higher)

FALSE

FLOAT

FOREIGN

FROM

GROUPBY

HAVING

IN

INDEX

INSERT

INTEGER

INTO

IS

KEY

LIKE

MAX

MIN

NOT

NULL

NUMERIC

ON

OR

ORDERBY

NULL

NUMERIC

TABLE

TEMPORARY

TRUE

UPDATE

UNION

UNIQUE

UNKNOWN

UNSIGNED

VALUES

VARCHAR

VARYING

VIEW

WHERE

 

 

 

 

SQL Grammar for Supported Statements

The following defines the SQL grammar for SQL statements supported by the BASIS DBMS. SQL reserved words are written in upper case to make them more readily identifiable.

Type

Description

Syntax




Alter Procedure


Alters the specified stored procedure to change the parameters, return type or source code.

For usage example, see CREATE/ALTER PROCEDURE.

Alter Table

Alters the structure of a table including adding, modifying and removing columns.  This operation modifies the data dictionary as well as the underlying table's data file.

ALTER TABLE foo ADD COLUMN my_col CHAR(10)

Alter Trigger

Modifies the specified trigger to enable/disable it or to modify the source code associated with the trigger.

For usage example, see CREATE/ALTER PROCEDURE.

Create Index

Creates an index of a column or group of columns within a table. An index essentially functions as an ordered list (alphabet or numeric) that provides a fast and efficient way to look up records. If a UNIQUE index is specified, this indicates an index that does not allow duplicate values.

CREATE INDEX indexname ON tablename(indexcolumns)

Create Procedure

Creates a new stored procedure.

For usage example, see CREATE/ALTER PROCEDURE.

Create Role

Creates a new role on a database. A role is a security group that groups together one or more users making it easier to assign privileges to multiple users. This feature requires that object level permissions be enabled on the database.

For usage example, see DB Security That You Have Always Dreamed About.

Create Table

Creates a new table in a database by physically creating a data file. Specify the columns (and their data types) that are to be defined a record in the table.

CREATE TABLE tablename (createcols)

Create Trigger

Creates a trigger of the specified type on a table.  Triggers on tables will be fired when the operation occurs through SQL or directly when the file is accessed from a BBx program using OPEN/READ RECORD/WRITE RECORD calls.

For usage example, see CREATE/ALTER PROCEDURE.

Create View

Creates a view of one or more tables. Views are typically used to restrict or allow a users access to a database, and only to necessary information.

CREATE VIEW viewname

[(identifier[, identifier]…)]

AS

SELECT [ALL | DISTINCT] select-list

FROM table-reference-list

[WHERE search-condition]

Delete

Deletes one or more records in a table.

DELETE FROM tablename where

Drop Index

Removes an index from a table in a database. This does not alter the data in any way.

DROP INDEX indexname

Drop Procedure

Drops the specified stored procedure.

DROP PROCEDURE my_sproc

Drop Role

Drops a role from a database. This feature requires that object level permissions be enabled on the database.

For usage example, see CREATE/ALTER PROCEDURE.

Drop Table

Removes a table from a database.

DROP TABLE tablename

Drop Trigger

Drops the specified trigger from a table.

For usage example, see CREATE/ALTER PROCEDURE.

Drop View

Removes a view from a database.

DROP VIEW viewname

Get Table Info

In BBj 5.0 and higher, returns a string template that matches the information defined in the data dictionary.

GET TABLE INFO FOR tablename

Grant

Grant privileges to a user or role for a given database entity. This feature requires that object level permissions be enabled on the database.

For usage example, see DB Security That You Have Always Dreamed About.

Insert

Inserts a new record into a table. By default, the user provides values for all columns in the record in the order they are defined. It is possible, however, to optionally set values for a subset of columns, provided that none of the columns are omitted which were defined as being NOT NULL, since they must be defined when inserting a new record.

INSERT INTO tablename insertvals

Revoke

Revokes privileges for a user or role for a given database entity. This feature requires that object level permission be enabled on the database.

For usage example, see DB Security That You Have Always Dreamed About.

Select

Selects information from one or more tables. This is the most fundamental building block in formulating queries on databases. It does not allow data to be changed, but combined with all of its options, it becomes a powerful query builder.

SELECT selectcols [INTO temp_table]] FROM tablelist where groupby having

Update

Updates the values of one or more columns in one or more records in a table.

UPDATE tablename SET setlist where

UPDATE tablename SET setlist FROM tablelist where

Click here for a sample of how the BBj SQL engine uses Java CUP grammar, similar to the YACC grammar format



______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.