SQL:1999

Last updated

SQL:1999 (also called SQL 3) was the fourth revision of the SQL database query language. It introduced many new features, many of which required clarifications in the subsequent SQL:2003. In the meanwhile[ clarification needed ] SQL:1999 is deprecated.

Contents

Summary

The ISO standard documents were published between 1999 and 2002 in several installments, the first one consisting of multiple parts. Unlike previous editions, the standard's name used a colon instead of a hyphen for consistency with the names of other ISO standards. The first installment of SQL:1999 had five parts:

Three more parts, also considered part of SQL:1999 were published subsequently:

New features

Data types

Boolean data types

The SQL:1999 standard calls for a Boolean type, [1] but many commercial SQL servers (Oracle Database, IBM Db2) do not support it as a column type, variable type or allow it in the results set. Microsoft SQL Server is one of the few database systems that properly supports BOOLEAN values using its "BIT" data type[ citation needed ]. Every 1–8 bit fields occupies one full byte of space on disk. MySQL interprets "BOOLEAN" as a synonym for TINYINT (8-bit signed integer). [2] PostgreSQL provides a standard conforming Boolean type. [3]

Distinct user-defined types of power

Sometimes called just distinct types, these were introduced as an optional feature (S011) to allow existing atomic types to be extended with a distinctive meaning to create a new type and thereby enabling the type checking mechanism to detect some logical errors, e.g. accidentally adding an age to a salary. For example:

createtypeageasintegerFINAL;createtypesalaryasintegerFINAL;

creates two different and incompatible types. The SQL distinct types use name equivalence not structural equivalence like typedefs in C. It's still possible to perform compatible operations on (columns or data) of distinct types by using an explicit type CAST.

Few SQL systems support these. IBM Db2 is one those supporting them. [4] Oracle database did not support them as of 2012, recommending instead to emulate them by a one-place structured type. [5]

Structured user-defined types

These are the backbone of the object–relational database extension in SQL:1999. They are analogous to classes in objected-oriented programming languages. SQL:1999 allows only single inheritance.

Common table expressions and recursive queries

SQL:1999 added a WITH [RECURSIVE] construct allowing recursive queries, like transitive closure, to be specified in the query language itself; see common table expressions.

Some OLAP capabilities

GROUP BY was extended with ROLLUP, CUBE, and GROUPING SETS.

Role-based access control

Full support for RBAC via CREATE ROLE.

Keywords

SQL:1999 introduced the UNNEST keyword. [6]

Related Research Articles

<span class="mw-page-title-main">PostgreSQL</span> Free and open-source relational database management system

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.

A relational database is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems are equipped with the option of using SQL for querying and updating the database.

Structured Query Language (SQL), is a 1C copy-cat used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

<span class="mw-page-title-main">Object–relational database</span> Database management system

An object–relational database (ORD), or object–relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.

The Call Level Interface (CLI) is an application programming interface (API) and software standard to embed Structured Query Language (SQL) code in a host program as defined in a joint standard by the International Organization for Standardization (ISO) and International Electrotechnical Commission (IEC): ISO/IEC 9075-3:2003. The Call Level Interface defines how a program should send SQL queries to the database management system (DBMS) and how the returned recordsets should be handled by the application in a consistent way. Developed in the early 1990s, the API was defined only for the programming languages C and COBOL.

In computer science, the Boolean is a data type that has one of two possible values which is intended to represent the two truth values of logic and Boolean algebra. It is named after George Boole, who first defined an algebraic system of logic in the mid 19th century. The Boolean data type is primarily associated with conditional statements, which allow different actions by changing control flow depending on whether a programmer-specified Boolean condition evaluates to true or false. It is a special case of a more general logical data type—logic does not always need to be Boolean.

A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time. Temporal databases can be uni-temporal, bi-temporal or tri-temporal.

An XML database is a data persistence software system that allows data to be specified, and sometimes stored, in XML format. This data can be queried, transformed, exported and returned to a calling system. XML databases are a flavor of document-oriented databases which are in turn a category of NoSQL database.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

Remote database access (RDA) is a protocol standard for database access produced in 1993 by the International Organization for Standardization (ISO). Despite early efforts to develop proof of concept implementations of RDA for major commercial remote database management systems (RDBMSs), this standard has not found commercial support from database vendors. The standard has since been withdrawn, and replaced by ISO/IEC 9579:1999 - Information technology -- Remote Database Access for SQL, which has also been withdrawn, and replaced by ISO/IEC 9579:2000 Information technology -- Remote database access for SQL with security enhancement.

<span class="mw-page-title-main">Null (SQL)</span> Marker used in SQL databases to indicate a value does not exist

In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent null in database theory. In SQL, NULL is a reserved word used to identify this marker.

A spatial database is a general-purpose database that has been enhanced to include spatial data that represents objects defined in a geometric space, along with tools for querying and analyzing such data. Most spatial databases allow the representation of simple geometric objects such as points, lines and polygons. Some spatial databases handle more complex structures such as 3D objects, topological coverages, linear networks, and triangulated irregular networks (TINs). While typical databases have developed to manage various numeric and character types of data, such databases require additional functionality to process spatial data types efficiently, and developers have often added geometry or feature data types. The Open Geospatial Consortium (OGC) developed the Simple Features specification and sets standards for adding spatial functionality to database systems. The SQL/MM Spatial ISO/IEC standard is a part of the SQL/MM multimedia standard and extends the Simple Features standard with data types that support circular interpolations. Almost all current relational and object-relational database management systems now have spatial extensions, and some GIS software vendors have developed their own spatial extensions to database management systems.

SQLJ is a working title for efforts to combine Java and SQL. It was a common effort started around 1997 by engineers from IBM, Oracle, Compaq, Informix, Sybase, Cloudscape and Sun Microsystems.

SQL/PSM is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92, SQL/PSM was later incorporated into the multi-part SQL:1999 standard, and has been part 4 of that standard since then, most recently in SQL:2023. The SQL:1999 part 4 covered less than the original PSM-96 because the SQL statements for defining, managing, and invoking routines were actually incorporated into part 2 SQL/Foundation, leaving only the procedural language itself as SQL/PSM. The SQL/PSM facilities are still optional as far as the SQL standard is concerned; most of them are grouped in Features P001-P008.

SQL/JRT, or SQL Routines and Types for the Java Programming Language, is an extension to the SQL standard first published as ISO/IEC 9075-13:2002. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications, commonly referred to as "Java stored procedures". SQL/JRT also calls for the ability to use Java classes as SQL structured user-defined types. The two parts of the extension originate from the earlier ANSI SQLJ part 1 and 2 standards

SQL/XML or XML-Related Specifications is part 14 of the Structured Query Language (SQL) specification. In addition to the traditional predefined SQL data types like NUMERIC, CHAR, TIMESTAMP, ... it introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database.

A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.

SQL:2011 or ISO/IEC 9075:2011 is the seventh revision of the ISO (1987) and ANSI (1986) standard for the SQL database query language. It was formally adopted in December 2011. The standard consists of 9 parts which are described in detail in SQL. The next version is SQL:2016.

The syntax of the SQL programming language is defined and maintained by ISO/IEC SC 32 as part of ISO/IEC 9075. This standard is not freely available. Despite the existence of the standard, SQL code is not completely portable among different database systems without adjustments.

References

  1. ↑ ISO/IEC 9075-2:1999 section 4.6 Boolean types
  2. ↑ "MySQL :: MySQL 5.0 Reference Manual :: 11.4 Using Data Types from Other Database Engines". Dev.mysql.com. 2010-01-09. Retrieved 2014-01-30.
  3. ↑ PostgreSQL documentation about Boolean Type
  4. ↑ "IBM Information Management Software for z/OS Solutions Information Center". Publib.boulder.ibm.com. Retrieved 2014-01-30.
  5. ↑ "Oracle Compliance To Core SQL:2003". Docs.oracle.com. Retrieved 2014-01-30.
  6. ↑ Jones, Arie; Stephens, Ryan K.; Plew, Ronald R.; Garrett, Robert F.; Kriegel, Alex (2005). "Appendix B ANSI and Vendor Keywords". SQL Functions Programmer's Reference. John Wiley & Sons. p. 680. ISBN   9780764598074 . Retrieved 2016-05-16.

Further reading

This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.