SQL Overview

An Overview of the Structured Query Language (SQL) and PSQL Support for SQL

The following topics present an overview of SQL and provide details on PSQL support for SQL.

You can also go to SQL Syntax Reference to look up specific SQL grammar supported by PSQL.

Working with SQL in PSQL

Structured Query Language (SQL) is a database language that uses Englishlike statements to perform database operations. Both the American National Standards Institute (ANSI) and IBM have defined standards for SQL. The IBM standard is the Systems Application Architecture (SAA). PSQL implements most of the features of both ANSI SQL and IBM SAA SQL and provides additional extensions that neither standard specifies.

PSQL allows you to create different types of SQL statements. The following table lists the types of SQL statements you can create and the tasks you can accomplish using each type of statement.

Table 1 SQL Statement Types and Related Tasks
SQL Statement Type	Tasks
Data Definition	Create, modify, and delete tables. Create and delete views. Create and delete indexes. Create and delete stored SQL procedures. Create and delete triggers. Create and delete user-defined functions.
Data Manipulation	Retrieve, insert, update, and delete data in tables. Define transactions. Define and delete views. Execute stored SQL procedures. Execute triggers.
Data Control	Enable and disable security for a dictionary. Create and delete users. Add and drop users from groups. Change user passwords. Grant and revoke table access rights.

The rest of this topic briefly describes the SQL statements used in each statement category. For detailed information about each statement, refer to SQL Syntax Reference.

The following are the statement category overview sections found in this section:

• Data Definition Statements

• Data Manipulation Statements

• Data Control Statements

Data Definition Statements

Data definition statements let you specify the characteristics of your database. When you execute data definition statements, PSQL stores the description of your database in a data dictionary. You must define your database in the dictionary before you can store or retrieve information.

PSQL allows you to construct data definition statements to do the following:

•Create, modify, and delete tables.

•Create and delete views.

•Create and delete indexes.

•Create and delete triggers.

•Create and delete stored procedures.

•Create and delete user-defined functions.

The following sections briefly describe the SQL statements associated with each of these tasks. For general information about defining the characteristics of your database, refer to the PSQL Programmer's Guide in the Developer Reference.

Creating, Modifying, and Deleting Tables

You can create, modify, and delete tables from a database by constructing SQL using the following statements:

Table 2 Data Definition Statements – Tables

CREATE TABLE	Defines a table and optionally creates the corresponding data file.
ALTER TABLE	Changes a table definition. With an ALTER TABLE statement, you can perform such actions as add a column to the table definition, remove a column from the table definition, change column data type or length (or other characteristics), add or remove a primary key or a foreign key, and associate the table definition with an different data file.
DROP TABLE	Deletes a table from the data dictionary and optionally deletes the associated data file.

Creating and Deleting Views

You can create and delete views from a database by constructing SQL using the following statements:

Table 3 Data Definition Statements – Views

CREATE VIEW	Defines a new view.
DROP VIEW	Deletes a view.

Creating and Deleting Indexes

You can create and delete indexes from a database by constructing SQL using the following statements:

Table 4 Data Definition Statements – Indexes

CREATE INDEX	Defines a new index (a named index) for an existing table.
DROP INDEX	Deletes a named index.

Creating and Deleting Triggers

You can create and delete triggers from a database by constructing SQL using the following statements:

Table 5 Data Definition Statements – Triggers

CREATE TRIGGER	Defines a trigger for an existing table.
DROP TRIGGER	Deletes a trigger.

PSQL provides additional SQL control statements, which you can only use in the body of a trigger. You can use the following statements in triggers:

Table 6 Data Definition Statements – Trigger Control

BEFORE	Defines the trigger execution before the INSERT, UPDATE, or DELETE operation.
AFTER	Defines the trigger execution after the INSERT, UPDATE, or DELETE operation.

Creating and Deleting Stored Procedures

A stored procedure consists of statements you can precompile and save in the dictionary. To create and delete stored procedures, construct statements using the following:

Table 7 Data Definition Statements – Stored Procedure

CREATE PROCEDURE	Stores a new procedure in the data dictionary.
DROP PROCEDURE	Deletes a stored procedure from the data dictionary.

PSQL provides additional SQL control statements, which you can only use in the body of a stored procedure. You can use the following statements in stored procedures:

Table 8 Data Definition Statements – Stored Procedure Control

IF...THEN...ELSE	Provides conditional execution based on the truth value of a condition.
LEAVE	Continues execution by leaving a block or loop statement.
LOOP	Repeats the execution of a block of statements.
WHILE	Repeats the execution of a block of statements while a specified condition is true.

Creating and Deleting User-Defined Functions (UDF)

In addition to the built-in functions, PSQL allows you to create your own user-defined functions (UDF) and use them in SQLstatements.

A user-defined function is a database object that encapsulates one or more SQL statements that can be reused. A user-defined function takes zero or more input arguments and evaluates a return value, which is a scalar value.

User-defined functions are always defined within the context of a database. Successful execution of this statement results in the storing of the UDF definition in the specific database. Once stored, the UDF can be modified, invoked, and deleted.

PSQL supports scalar user-defined functions.

A scalar user-defined function returns a single value of the data type specified in the RETURNS clause of the SQL statement. A scalar UDF can contain multiple SQL statements. You can specify any data type value for the returned data except text, ntext, image, cursor, or timestamp.

To create and delete user-defined functions, construct statements using the following:

Table 9 Data Definition Statements – User-Defined Function

CREATE FUNCTION	Creates a scalar user-defined function in the database.
DROP FUNCTION	Deletes a scalar user-defined function from the database.

Data Manipulation Statements

Data manipulation statements let you access and modify the contents of your database. PSQL allows you to construct data manipulation statements to do the following:

•Retrieve data from tables.

•Modify data in tables.

•Define transactions.

•Create and delete views.

•Execute stored procedures.

•Execute triggers.

The following sections briefly describe the SQL statements associated with each of these tasks.

Retrieving Data

All statements you use to retrieve information from a database are based on the SELECT statement.

Table 10 Data Manipulation Statements – Retrieving Data

SELECT

Retrieves data from one or more tables in the database.

When you create a SELECT statement, you can use various clauses to specify different options. The types of clauses you use in a SELECT statement are as follows:

Table 11 Data Manipulation Statements – Retrieving Data Options

FROM	Specifies the tables or views from which to retrieve data.
WHERE	Defines search criteria that qualify the data a SELECT statement retrieves.
GROUP BY	Combines sets of rows according to the criteria you specify and allows you to determine aggregate values for one or more columns in a group.
HAVING	Allows you to limit a view by specifying criteria that the aggregate values of a group must meet.
ORDER BY	Determines the order in which PSQL returns selected rows.

In addition, you can use the UNION keyword to obtain a single result table from multiple SELECT queries.

Modifying Data

You can add, change, or delete data from tables and views by issuing statements such as the following:

Table 12 Data Manipulation Statements – Modifying Data

INSERT	Adds rows to one or more tables or a view.
UPDATE	Changes data in a table or a view.
DELETE	Deletes rows from a table or a view.

When you create a DELETE or UPDATE statement, you can use a WHERE clause to define search criteria that restrict the data upon which the statement acts.

Creating and Deleting Views

You can create and delete views by constructing SQL using the following statements:

Table 13 Data Manipulation Statements – Views

CREATE VIEW	Defines a database view and stores the definition in the dictionary.
DROP VIEW	Deletes a view from the data dictionary.

Executing Stored Procedures

A stored procedure consists of statements that you can precompile and save in the dictionary. To execute stored procedures, construct statements using the following:

Table 14 Data Manipulation Statements- Stored Procedures

CALL or EXEC[UTE]

Recalls a previously compiled procedure and executes it.

Executing System Stored Procedures

A system stored procedure helps you accomplish those administrative and informative tasks that are not covered by the Data Definition Language. The system stored procedures have a psp_ prefix. To execute stored procedures, construct statements using the following:

Table 15 Data Manipulation Statements – System Stored Procedures

CALL or EXEC[UTE]

Recalls a system stored procedure and executes it.

For more details, see System Stored Procedures.

Executing Triggers

A trigger consists of statements you can precompile and save in the dictionary. Triggers are executed automatically by the engine when the specified conditions occur.

Data Control Statements

Data control statements let you define security for your database. When you create a dictionary, no security is defined for it until you explicitly enable security for that dictionary. PSQL allows you to construct data control statements to do the following:

•Enable and disable security.

•Create and delete users and groups.

•Add and drop users from groups and change user passwords.

•Grant and revoke rights.

Note If you have a Btrieve owner name set on a file that is a table in a secure database, the Master user of the database must include the owner name in any GRANT statement to give permissions on the table to any user, including the Master user.

The following sections briefly describe the SQL statements associated with each of these tasks.

Enabling and Disabling Security

You can enable or disable security for a database by issuing statements using the following statement:

Table 16 Data Control Statements – Security

SET SECURITY

Enables or disables security for the database and sets the Master password.

Creating and Deleting Users and Groups

You can create or delete users and user groups for the database by constructing SQL using the following statements:

Table 17 Data Control Statements – Groups and Users

ALTER USER	Rename a user or change a password.
CREATE USER	Creates a new user with or without a password or membership in a group.
DROP USER	Delete a user.
ALTER GROUP	Adds users to a group. Drops users from a group.
CREATE GROUP	Creates a new group of users.
DROP GROUP	Deletes a group of users.
GRANT LOGIN TO	Creates users and passwords, or adds users to groups.
REVOKE LOGIN FROM	Removes a user from the dictionary.

Granting and Revoking Rights

You can assign or remove rights from users or groups by issuing statements using the following:

Table 18 Data Control Statements – Rights

GRANT (access rights)	Grants a specific type of rights to a user or a group. The rights you can grant with a GRANT (access rights) statement are All, Insert, Delete, Alter, Select, Update, and References.
GRANT CREATETAB TO	Grants the right to create tables to a user or a group.
REVOKE (access rights)	Revokes access rights from a user or a group.
REVOKE CREATETAB FROM	Revokes the right to create tables from a user or a group.

PSQL Metadata

The Relational Interface in PSQL supports two versions of metadata, referred to as version 1 (V1) and version 2 (V2).

Metadata version is a property of the database that you specify when you create a database. V1 metadata is the default. When you create a database, you must specify V2 metadata if you want that version.

Metadata version applies to all data dictionary files (DDFs) within that database. A single database cannot use some DDFs with V1 metadata and others with V2 metadata. DDFs from the two versions cannot interact.

The database engine can, however, concurrently access multiple databases and each database can use either V1 metadata or V2 metadata.

All databases created with PSQL versions before PSQL v10 use V1 metadata. Databases created with PSQL v10 or later may use either metadata version depending on database settings.

Comparison of Metadata Versions

Version 2 metadata allows for many identifier names to be up to 128 bytes long. See Limits/Conditions of PSQL Features for additional information. In addition, V2 metadata allows for permissions on views and stored procedures. See Permissions on Views and Stored Procedures.

The data dictionary files (DDFs) for V2 metadata are named differently than for V1. In many cases, the V2 DDFs contain additional fields and changes to existing fields. See System Tables.

Relational Engine Limits

The following table shows the limits or conditions that apply to features of the Relational Engine. A PSQL database may contain four billion objects in any valid combination. The objects are persisted in the data dictionary files.

See also Naming Conventions in PSQL Programmer's Guide.

Table 19 Limits/Conditions of PSQL Features
PSQL Feature	Limit or Condition	Metadata
		V1	V2
Arguments in a parameter list for a stored procedure	300
CHAR column size	8,000 bytes1
Character string literal	See String Values.
Columns in a table	1,536
Columns allowed in a trigger or stored procedure	300
Column name2	20 bytes
Column name2	128 bytes
Column size	2 GB
Correlation name	Limited by memory
Cursor name	18 bytes
Database name2	20 bytes
Database sessions	Limited by memory
Data file path name	64 bytes (the maximum length of the data file path name is a combination of Xf$Loc path and the data file path)
Data file path name	250 bytes (the maximum length of the data file path name is a combination of Xf$Loc path and the data file path)
Function (user-defined) name2	30 bytes
Function (user-defined) name2	128 bytes
Group name2	30 bytes
Group name2	128 bytes
Index name2	20 bytes
Index name2	128 bytes
Key name2	20 bytes
Key name2	128 bytes
Label name	limited by memory
NCHAR column size	4,000 UCS-2 units (8,000 bytes1)
NVARCHAR column size	4,000 UCS-2 units (8,000 bytes1)
Number of ANDed and ORed predicates	3000
Number of database objects	65,536
Number of database objects	2 billion
Parameter name	126 bytes
Password2	8 bytes
Password2	128 bytes
Procedure name2	30 bytes
Procedure name2	128 bytes
Referential integrity (RI) constraint name	20 bytes
Referential integrity (RI) constraint name	128 bytes
Representation of single quote	Two consecutive single quotes ('')
Result name	Limited by memory
Savepoint name	Limited by memory
SELECT list columns in a query	1,600
Size of a single term (quoted literal string) in a SQL statement	14,997, excluding null terminator and quotations (15,000 total)
SQL statement length	512 KB
SQL statements per session	Limited by memory
Stored procedure size	64 KB
Table name2	20 bytes
Table name2	128 bytes
Table rows	2 billion
Joined tables per query	Limited by memory
Trigger name2	20 bytes
Trigger name2	128 bytes
User name2	30 bytes
User name2	128 bytes
VARCHAR column size	8,000 bytes1
Variable name	Limited by memory
View name2	20 bytes
View name2	128 bytes
1The maximum size of a CHAR, NCHAR, VARCHAR or NVARCHAR column that may be fully indexed is 255 bytes 2See also Identifier Restrictions in Advanced Operations Guide.

Fully Qualified Object Names

A fully qualified object name uses dot notation to combine database and object names. For example, if the database mydbase has a view myview, then its fully qualified object name is mydbase.myview.

Fully qualified object names must be unique within a database. For example, if database mydbase has table acctpay and user-defined function acctpay, then PSQL cannot resolve the name mydbase.acctpay.

Delimited Identifiers in SQL Statements

Table, column, and index names must be delimited if they contain spaces or nonstandard characters or if the identifier is a keyword. The delimiter character is the double quotation mark.

Examples

SELECT "last-name" FROM "non-standard-tbl"

The hyphen is a nonstandard character.

SELECT "password" FROM my_pword_tbl

“Password” is a keyword in the SET PASSWORD statement.