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 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
| Defines a table and optionally creates the corresponding data file. |
| 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. |
| 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
| Defines a new 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
| Defines a new index (a named index) for an existing table. |
| 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
| Defines a trigger for an existing table. |
| 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
| Stores a new procedure in the data dictionary. |
| 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
| Provides conditional execution based on the truth value of a condition. |
| Continues execution by leaving a block or loop statement. |
| Repeats the execution of a block of statements. |
| 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
| Creates a scalar user-defined function in the database. |
| 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
| 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. |
| 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. |
| 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
| Adds rows to one or more tables or a view. |
| Changes data in a table or a view. |
| 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
| Defines a database view and stores the definition in the dictionary. |
| 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
| 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
| Recalls a system stored procedure and executes it. |
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
| 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 | | | |
Columns in a table | 1,536 | | |
Columns allowed in a trigger or stored procedure | 300 | | |
Column name2 | 20 bytes | | |
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) | | |
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 | | |
128 bytes | | |
Group name2 | 30 bytes | | |
128 bytes | | |
Index name2 | 20 bytes | | |
128 bytes | | |
Key name2 | 20 bytes | | |
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 | | |
2 billion | | |
Parameter name | 126 bytes | | |
Password2 | 8 bytes | | |
128 bytes | | |
Procedure name2 | 30 bytes | | |
128 bytes | | |
Referential integrity (RI) constraint name | 20 bytes | | |
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 | | |
128 bytes | | |
Table rows | 2 billion | | |
Joined tables per query | Limited by memory | | |
Trigger name2 | 20 bytes | | |
128 bytes | | |
User name2 | 30 bytes | | |
128 bytes | | |
VARCHAR column size | 8,000 bytes1 | | |
Variable name | Limited by memory | | |
View name2 | 20 bytes | | |
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.