SQL — Grammar

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

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

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

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

Drop View

Removes a view from a database.

DROP VIEW viewname

Get Table Info

In BBj 5.00 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