SQL — Grammar

ALTER PROCEDURE proc_name [(parameters)]
['/path/to/source.prc' '/path/to/config.bbx'] [return_type] [code_block] description

Alter Procedure

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

For usage example, see CREATE/ALTER DROP PROCEDURE.

ALTER TABLE foo ADD COLUMN my_col CHAR(10)

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 TRIGGER ON table_name|'file_name' trigger_type
['source_file_name'] ENABLED|DISABLED [code_block]

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/DROP TRIGGER.

CREATE INDEX indexname ON tablename(indexcolumns)

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 PROCEDURE proc_name[(parameters)] ['/path/to/source.prc' '/path/to/config.bbx'] [return_type] [code_block] [description]

Create Procedure

Creates a new stored procedure.

For usage example, see CREATE/ALTER DROP PROCEDURE.

CREATE ROLE new_role

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 tablename (createcols)

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 TRIGGER ON table_name |'file_name' trigger_type
   ['source_file_name'] [code_block]

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/DROP TRIGGER.

CREATE VIEW viewname

[(identifier[, identifier]…)]

AS

SELECT [ALL | DISTINCT] select-list

FROM table-reference-list

[WHERE search-condition]

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.

DELETE FROM tablename where

Delete

Deletes one or more records in a table.

DROP INDEX indexname

Drop Index

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

DROP PROCEDURE my_sproc

Drop Procedure

Drops the specified stored procedure.

DROP ROLE role

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

DROP TABLE tablename

Drop Table

Removes a table from a database.

For usage example, see CREATE/ALTER/DROP TRIGGER.

Drop Trigger

Drops the specified trigger from a table.

DROP VIEW viewname

Drop View

Removes a view from a database.

EXPLAIN returns a standard ResultSet similar to a SELECT statement and is used to analyze and provide information about the execution plan of a query.

For usage example, see SQL EXPLAIN

GET TABLE INFO FOR tablename

Get Table Info

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

GRANT privilege_list ON entity_name TO {user_name | role_name} [WITH GRANT OPTION]

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 INTO tablenameinsertvals

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.

REVOKE privilege_list ON entity_name FROM {user_name | role_name} [CASCADE | RESTRICT]

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 [TOP integer] selectcols [INTO temp_table]] FROM tablelist where groupby having

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.

UPDATE tablename SET setlistwhere

UPDATE tablename SET setlist FROM tablelist where

Update

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