SQL
Encyclopedia
SQL is a programming language
designed for managing data in relational database management system
s (RDBMS).
Originally based upon relational algebra
and tuple relational calculus
, its scope includes data insert, query, update and delete
, schema
creation and modification, and data access control.
SQL was one of the first commercial languages for Edgar F. Codd
's relational model
, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks". Despite not adhering to the relational model as described by Codd
, it became the most widely used database language. Though often described as, and to a great extent is a declarative language
, SQL also includes procedural
elements. SQL became a standard of the American National Standards Institute
(ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. Since then the standard has been enhanced several times with added features. However, issues of SQL code portability between major RDBMS products still exist due to lack of full compliance with, or different interpretations of the standard. Among the reasons mentioned are the large size, and incomplete specification of the standard, as well as vendor lock-in
.
by Donald D. Chamberlin
and Raymond F. Boyce
in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark
of the UK-based
Hawker Siddeley aircraft company.
The first Relational Database Management System (RDBMS) was RDMS
, developed at MIT in the early 1970s, soon followed by Ingres, developed in 1974 at U.C. Berkeley
. Ingres implemented a query language known as QUEL
, which was later supplanted in the marketplace by SQL.
In the late 1970s, Relational Software, Inc. (now Oracle Corporation
) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy
, Central Intelligence Agency
, and other U.S. government
agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle
V2 (Version2) for VAX
computers. Oracle V2 beat IBM's August release of the System/38
RDBMS to market by a few weeks.
After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS
, and DB2
, which were commercially available in 1979, 1981, and 1983, respectively.
The SQL language is sub-divided into several language elements, including:
s, or expressions. Standard
Queries allow the user to describe desired data, leaving the database management system (DBMS)
responsible for planning
, optimizing
, and performing the physical operations necessary to produce that result as it chooses.
A query includes a list of columns to be included in the final result immediately following the
The following is an example of a
SELECT *
FROM Book
WHERE price > 100.00
ORDER BY title;
The example below demonstrates a query of multiple tables, grouping, and aggregation, by returning a list of books and the number of authors associated with each book.
SELECT Book.title,
count(*) AS Authors
FROM Book JOIN Book_author
ON Book.isbn = Book_author.isbn
GROUP BY Book.title;
Example output might resemble the following:
Title Authors
---------------------- -------
SQL Examples and Guide 4
The Joy of SQL 1
An Introduction to SQL 2
Pitfalls of SQL 1
Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Books table, the above query could be rewritten in the following form:
SELECT title,
count(*) AS Authors
FROM Book NATURAL JOIN Book_author
GROUP BY title;
However, many vendors either do not support this approach, or require certain column naming conventions in order for natural joins to work effectively.
SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.
SELECT isbn,
title,
price,
price * 0.06 AS sales_tax
FROM Book
WHERE price > 100.00
ORDER BY title;
was introduced into SQL to handle missing information in the relational model. The introduction of Null (or Unknown) along with True and False is the foundation of three-valued logic. Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. Therefore comparisons with Null can never result in either True or False but always in the third logical result.
SQL uses Null to handle missing information. It supports three-valued logic (3VL) and the rules governing SQL three-valued logic are shown below (p and q represent logical states). The word NULL is also a reserved keyword in SQL, used to identify the Null special marker.
Additionally, since SQL operators return Unknown when comparing anything with Null, SQL provides two Null-specific comparison predicates:
Note that SQL returns only results for which the WHERE clause returns a value of True; i.e. it excludes results with values of False and also excludes those whose value is Unknown.
Universal quantification
is not explicitly supported by SQL, and must be worked out as a negated existential quantification
.
There is also the " IS DISTINCT FROM " infixed comparison operator which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as "NOT ( IS DISTINCT FROM )".
(DML) is the subset of SQL used to add, update and delete data:
INSERT INTO My_table
(field1, field2, field3)
VALUES
('test', 'N', NULL);
UPDATE My_table
SET field1 = 'updated value'
WHERE field2 = 'N';
DELETE FROM My_table
WHERE field2 = 'N';
CREATE TABLE tbl_1(id int);
INSERT INTO tbl_1(id) VALUES(1);
INSERT INTO tbl_1(id) VALUES(2);
COMMIT;
UPDATE tbl_1 SET id=200 WHERE id=1;
SAVEPOINT id_1upd;
UPDATE tbl_1 SET id=1000 WHERE id=2;
ROLLBACK to id_1upd;
SELECT id from tbl_1;
Once the
Example: A classic bank transfer of funds transaction.
START TRANSACTION;
UPDATE Account SET amount=amount-200 WHERE account_number=1234;
UPDATE Account SET amount=amount+200 WHERE account_number=2345;
IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;
(DDL) manages table and index structure. The most basic items of DDL are the
CREATE TABLE My_table(
my_field1 INT,
my_field2 VARCHAR(50),
my_field3 DATE NOT NULL,
PRIMARY KEY (my_field1, my_field2)
);
ALTER TABLE My_table ADD my_field4 NUMBER(3) NOT NULL;
TRUNCATE TABLE My_table;
DROP TABLE My_table;
The precision is a positive integer that determines the number of significant digits in a particular radix (binary or decimal). The scale is a non-negative integer. A scale of 0 indicates that the number is an integer. For a scale of S, the exact numeric value is the integer value of the significant digits multiplied by 10-S.
SQL provides a function to round numerics or dates, called
SQL provides several functions for generating a date / time variable out of a date / time string (
(DCL) authorizes users and groups of users to access and manipulate data.
Its two main statements are:
Example:
GRANT SELECT, UPDATE
ON My_table
TO some_user, another_user;
REVOKE SELECT, UPDATE
ON My_table
FROM some_user, another_user;
contained in a relational database
. SQL is a set
-based, declarative
query language, not an imperative language
such as C
or BASIC. However, there are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs. These include:
In addition to the standard SQL/PSM extensions and proprietary SQL extensions, procedural and object-oriented
programmability is available on many SQL platforms via DBMS integration with other languages. The SQL standard defines SQL/JRT
extensions (SQL Routines and Types for the Java Programming Language) to support Java
code in SQL databases. SQL Server 2005 uses the SQLCLR (SQL Server Common Language Runtime) to host managed .NET assemblies in the database, while prior versions of SQL Server were restricted to using unmanaged extended stored procedures which were primarily written in C. PostgreSQL allows functions to be written in a wide variety of languages including Perl
, Python
, Tcl
, and C.
intended for use with relational databases. Many of the original SQL features were inspired by, but violated the semantics of the relational model
and its tuple calculus
realization. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto
. Therefore, it cannot be considered relational in any significant sense, but is still widely called relational due to differentiation to other, pre-relational database languages which never intended to implement the relational model; due to its historical origin; and due to the use of the "relational" term by product vendors.
Other criticisms of SQL include:
There are several reasons for this lack of portability between database systems:
(ANSI) in 1986 as SQL-86 and the International Organization for Standardization
(ISO) in 1987. The original SQL standard declared that the official pronunciation for SQL is "es queue el". Many English-speaking database professionals still use the nonstandard pronunciation /ˈsiːkwəl/ (like the word "sequel").
Until 1996, the National Institute of Standards and Technology
(NIST) data management standards program certified SQL DBMS compliance with the SQL standard. Vendors now self-certify the compliance of their products.
The SQL standard has gone through a number of revisions, as shown below:
Interested parties may purchase SQL standards documents from ISO or ANSI. A draft of SQL:2008 is freely available as a zip
archive.
for alternatives to relational:
Programming language
A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
designed for managing data in relational database management system
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
s (RDBMS).
Originally based upon relational algebra
Relational algebra
Relational algebra, an offshoot of first-order logic , deals with a set of finitary relations that is closed under certain operators. These operators operate on one or more relations to yield a relation...
and tuple relational calculus
Tuple relational calculus
Tuple calculus is a calculus that was introduced by Edgar F. Codd as part of the relational model, in order to provide a declarative database-query language for this data model...
, its scope includes data insert, query, update and delete
Data Manipulation Language
A data manipulation language is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database...
, schema
Database schema
A database schema of a database system is its structure described in a formal language supported by the database management system and refers to the organization of data to create a blueprint of how a database will be constructed...
creation and modification, and data access control.
SQL was one of the first commercial languages for Edgar F. Codd
Edgar F. Codd
Edgar Frank "Ted" Codd was an English computer scientist who, while working for IBM, invented the relational model for database management, the theoretical basis for relational databases...
's relational model
Relational model
The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...
, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks". Despite not adhering to the relational model as described by Codd
Codd's 12 rules
Codd's twelve rules are a set of thirteen rules proposed by Edgar F. Codd, a pioneer of the relational model for databases, designed to define what is required from a database management system in order for it to be considered relational, i.e., a relational database management system...
, it became the most widely used database language. Though often described as, and to a great extent is a declarative language
Declarative programming
In computer science, declarative programming is a programming paradigm that expresses the logic of a computation without describing its control flow. Many languages applying this style attempt to minimize or eliminate side effects by describing what the program should accomplish, rather than...
, SQL also includes procedural
Procedural programming
Procedural programming can sometimes be used as a synonym for imperative programming , but can also refer to a programming paradigm, derived from structured programming, based upon the concept of the procedure call...
elements. SQL became a standard of the American National Standards Institute
American National Standards Institute
The American National Standards Institute is a private non-profit organization that oversees the development of voluntary consensus standards for products, services, processes, systems, and personnel in the United States. The organization also coordinates U.S. standards with international...
(ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. Since then the standard has been enhanced several times with added features. However, issues of SQL code portability between major RDBMS products still exist due to lack of full compliance with, or different interpretations of the standard. Among the reasons mentioned are the large size, and incomplete specification of the standard, as well as vendor lock-in
Vendor lock-in
In economics, vendor lock-in, also known as proprietary lock-in or customer lock-in, makes a customer dependent on a vendor for products and services, unable to use another vendor without substantial switching costs...
.
History
SQL was initially developed at IBMIBM
International Business Machines Corporation or IBM is an American multinational technology and consulting corporation headquartered in Armonk, New York, United States. IBM manufactures and sells computer hardware and software, and it offers infrastructure, hosting and consulting services in areas...
by Donald D. Chamberlin
Donald D. Chamberlin
Donald D. Chamberlin is an American computer scientist who is best known as one of the principal designers of the original SQL language specification with Raymond Boyce. He also made significant contributions to the development of XQuery....
and Raymond F. Boyce
Raymond F. Boyce
Raymond 'Ray' Boyce was an American computer scientist who was known for his research in relational databases.Boyce grew up in New York, and went to college in Providence, Rhode Island. He earned his PhD in computer science at Purdue in 1971 . After leaving Purdue he worked on database projects...
in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark
Trademark
A trademark, trade mark, or trade-mark is a distinctive sign or indicator used by an individual, business organization, or other legal entity to identify that the products or services to consumers with which the trademark appears originate from a unique source, and to distinguish its products or...
of the UK-based
United Kingdom
The United Kingdom of Great Britain and Northern IrelandIn the United Kingdom and Dependencies, other languages have been officially recognised as legitimate autochthonous languages under the European Charter for Regional or Minority Languages...
Hawker Siddeley aircraft company.
The first Relational Database Management System (RDBMS) was RDMS
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
, developed at MIT in the early 1970s, soon followed by Ingres, developed in 1974 at U.C. Berkeley
University of California, Berkeley
The University of California, Berkeley , is a teaching and research university established in 1868 and located in Berkeley, California, USA...
. Ingres implemented a query language known as QUEL
QUEL query languages
QUEL is a relational database access language, similar in most ways to SQL. It was created as a part of the Ingres effort at University of California, Berkeley, based on Codd's earlier suggested but not implemented Data Sub-Language ALPHA. QUEL was used for a short time in most products based on...
, which was later supplanted in the marketplace by SQL.
In the late 1970s, Relational Software, Inc. (now Oracle Corporation
Oracle Corporation
Oracle Corporation is an American multinational computer technology corporation that specializes in developing and marketing hardware systems and enterprise software products – particularly database management systems...
) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy
United States Navy
The United States Navy is the naval warfare service branch of the United States Armed Forces and one of the seven uniformed services of the United States. The U.S. Navy is the largest in the world; its battle fleet tonnage is greater than that of the next 13 largest navies combined. The U.S...
, Central Intelligence Agency
Central Intelligence Agency
The Central Intelligence Agency is a civilian intelligence agency of the United States government. It is an executive agency and reports directly to the Director of National Intelligence, responsible for providing national security intelligence assessment to senior United States policymakers...
, and other U.S. government
Federal government of the United States
The federal government of the United States is the national government of the constitutional republic of fifty states that is the United States of America. The federal government comprises three distinct branches of government: a legislative, an executive and a judiciary. These branches and...
agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
V2 (Version2) for VAX
VAX
VAX was an instruction set architecture developed by Digital Equipment Corporation in the mid-1970s. A 32-bit complex instruction set computer ISA, it was designed to extend or replace DEC's various Programmed Data Processor ISAs...
computers. Oracle V2 beat IBM's August release of the System/38
System/38
The System/38 was a midrange computer server platform manufactured and sold by the IBM Corporation. The system offered a number of innovative features, and was the brainchild of IBM engineer Dr. Frank Soltis...
RDBMS to market by a few weeks.
After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS
SQL/DS
SQL/DS was IBM's first commercial implementation for its mainframe computers of a DBMS built around the SQL language....
, and DB2
IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
, which were commercially available in 1979, 1981, and 1983, respectively.
- Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)
- Expressions, which can produce either scalarScalar (computing)In computing, a scalar variable or field is one that can hold only one value at a time; as opposed to composite variables like array, list, hash, record, etc. In some contexts, a scalar value may be understood to be numeric. A scalar data type is the type of a scalar variable...
values or tablesTable (database)In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
consisting of columnsColumn (database)In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
and rowsRow (database)In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
of data. - Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL)Ternary logicIn logic, a three-valued logic is any of several many-valued logic systems in which there are three truth values indicating true, false and some indeterminate third value...
or BooleanBoolean logicBoolean algebra is a logical calculus of truth values, developed by George Boole in the 1840s. It resembles the algebra of real numbers, but with the numeric operations of multiplication xy, addition x + y, and negation −x replaced by the respective logical operations of...
(true/false/unknown) truth values and which are used to limit the effects of statements and queries, or to change program flow. - Queries, which retrieve the data based on specific criteria. This is the most important element of SQL.
- Statements, which may have a persistent effect on schemata and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
- SQL statements also include the semicolonSemicolonThe semicolon is a punctuation mark with several uses. The Italian printer Aldus Manutius the Elder established the practice of using the semicolon to separate words of opposed meaning and to indicate interdependent statements. "The first printed semicolon was the work of ... Aldus Manutius"...
(";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
- SQL statements also include the semicolon
- Insignificant whitespaceWhitespace (computer science)In computer science, whitespace is any single character or series of characters that represents horizontal or vertical space in typography. When rendered, a whitespace character does not correspond to a visual mark, but typically does occupy an area on a page...
is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.
Queries
The most common operation in SQL is the query, which is performed with the declarativeSELECTSelect (SQL)The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
statement. SELECT retrieves data from one or more tableTable (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
s, or expressions. Standard
SELECT statements have no persistent effects on the database. Some non-standard implementations of SELECT can have persistent effects, such as the SELECT INTO syntax that exists in some databases.Queries allow the user to describe desired data, leaving the database management system (DBMS)
Database management system
A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...
responsible for planning
Query plan
A query plan is an ordered set of steps used to access or modify information in a SQL relational database management system. This is a specific case of the relational model concept of access plans....
, optimizing
Query optimizer
The query optimizer is the component of a database management system that attempts to determine the most efficient way to execute a query. The optimizer considers the possible query plans for a given input query, and attempts to determine which of those plans will be the most efficient...
, and performing the physical operations necessary to produce that result as it chooses.
A query includes a list of columns to be included in the final result immediately following the
SELECT keyword. An asterisk ("*") can also be used to specify that the query should return all columns of the queried tables. SELECT is the most complex statement in SQL, with optional keywords and clauses that include:
- The
FROMclause which indicates the table(s) from which data is to be retrieved. TheFrom (SQL)The SQL From clause is the source of a rowset to be operated upon in a Data Manipulation Language statement. From clauses are very common, and will provide the rowset to be exposed through a Select statement, the source of values in an Update statement, and the target rows to be deleted in a...FROMclause can include optionalJOINsubclauses to specify the rules for joining tables.Join (SQL)An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT... - The
WHEREclause includes a comparison predicate, which restricts the rows returned by the query. TheWhere (SQL)A WHERE clause in SQL specifies that a SQL Data Manipulation Language statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates...WHEREclause eliminates all rows from the result set for which the comparison predicate does not evaluate to True. - The
GROUP BYclause is used to project rows having common values into a smaller set of rows.GROUP BYis often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. TheWHEREclause is applied before theGROUP BYclause. - The
HAVINGclause includes a predicate used to filter rows resulting from theHaving (SQL)A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added to the SQL language because the WHERE keyword could not be used with aggregate functions.- Examples :...GROUP BYclause. Because it acts on the results of theGROUP BYclause, aggregation functions can be used in theHAVINGclause predicate. - The
ORDER BYclause identifies which columns are used to sort the resulting data, and in which direction they should be sorted (options are ascending or descending). Without anOrder by (SQL)An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns. The sort criteria do not have to be included in the result set. The sort criteria can be expressions, including – but not limited to – column...ORDER BYclause, the order of rows returned by an SQL query is undefined.
The following is an example of a
SELECT query that returns a list of expensive books. The query retrieves all rows from the Book table in which the price column contains a value greater than 100.00. The result is sorted in ascending order by title. The asterisk (*) in the select list indicates that all columns of the Book table should be included in the result set.SELECT *
FROM Book
WHERE price > 100.00
ORDER BY title;
The example below demonstrates a query of multiple tables, grouping, and aggregation, by returning a list of books and the number of authors associated with each book.
SELECT Book.title,
count(*) AS Authors
FROM Book JOIN Book_author
ON Book.isbn = Book_author.isbn
GROUP BY Book.title;
Example output might resemble the following:
Title Authors
---------------------- -------
SQL Examples and Guide 4
The Joy of SQL 1
An Introduction to SQL 2
Pitfalls of SQL 1
Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Books table, the above query could be rewritten in the following form:
SELECT title,
count(*) AS Authors
FROM Book NATURAL JOIN Book_author
GROUP BY title;
However, many vendors either do not support this approach, or require certain column naming conventions in order for natural joins to work effectively.
SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.
SELECT isbn,
title,
price,
price * 0.06 AS sales_tax
FROM Book
WHERE price > 100.00
ORDER BY title;
Null and three-valued logic (3VL)
The idea of NullNull (SQL)
Null is a special marker used in Structured Query Language 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 fulfill the requirement that all true relational database management systems support...
was introduced into SQL to handle missing information in the relational model. The introduction of Null (or Unknown) along with True and False is the foundation of three-valued logic. Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. Therefore comparisons with Null can never result in either True or False but always in the third logical result.
SQL uses Null to handle missing information. It supports three-valued logic (3VL) and the rules governing SQL three-valued logic are shown below (p and q represent logical states). The word NULL is also a reserved keyword in SQL, used to identify the Null special marker.
Additionally, since SQL operators return Unknown when comparing anything with Null, SQL provides two Null-specific comparison predicates:
IS NULL and IS NOT NULL test whether data is or is not Null.Note that SQL returns only results for which the WHERE clause returns a value of True; i.e. it excludes results with values of False and also excludes those whose value is Unknown.
|
|
||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||
Universal quantification
Universal quantification
In predicate logic, universal quantification formalizes the notion that something is true for everything, or every relevant thing....
is not explicitly supported by SQL, and must be worked out as a negated existential quantification
Existential quantification
In predicate logic, an existential quantification is the predication of a property or relation to at least one member of the domain. It is denoted by the logical operator symbol ∃ , which is called the existential quantifier...
.
There is also the "
Data manipulation
The Data Manipulation LanguageData Manipulation Language
A data manipulation language is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database...
(DML) is the subset of SQL used to add, update and delete data:
-
INSERTadds rows (formally tupleInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...TupleIn mathematics and computer science, a tuple is an ordered list of elements. In set theory, an n-tuple is a sequence of n elements, where n is a positive integer. There is also one 0-tuple, an empty sequence. An n-tuple is defined inductively using the construction of an ordered pair...
s) to an existing table, e.g.,:
INSERT INTO My_table
(field1, field2, field3)
VALUES
('test', 'N', NULL);
-
UPDATEmodifies a set of existing table rows, e.g.,:Update (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
UPDATE My_table
SET field1 = 'updated value'
WHERE field2 = 'N';
-
DELETEremoves existing rows from a table, e.g.,:Delete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
DELETE FROM My_table
WHERE field2 = 'N';
-
MERGEis used to combine the data of multiple tables. It combines theMerge (SQL)A relational database management system uses SQL MERGE statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches...INSERTandUPDATEelements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called "upsertUpsertThe term "Upsert" refers to any database statement, or combination of statements, that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record...
".
Transaction controls
Transactions, if available, wrap DML operations:-
START TRANSACTION(orBEGIN WORK, orBEGIN TRANSACTION, depending on SQL dialect) mark the start of a database transactionDatabase transactionA transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
, which either completes entirely or not at all. -
SAVE TRANSACTION(orSAVEPOINT) save the state of the database at the current point in transaction
CREATE TABLE tbl_1(id int);
INSERT INTO tbl_1(id) VALUES(1);
INSERT INTO tbl_1(id) VALUES(2);
COMMIT;
UPDATE tbl_1 SET id=200 WHERE id=1;
SAVEPOINT id_1upd;
UPDATE tbl_1 SET id=1000 WHERE id=2;
ROLLBACK to id_1upd;
SELECT id from tbl_1;
-
COMMITcauses all data changes in a transaction to be made permanent. -
ROLLBACKcauses all data changes since the lastCOMMITorROLLBACKto be discarded, leaving the state of the data as it was prior to those changes.
Once the
COMMIT statement completes, the transaction's changes cannot be rolled back.COMMIT and ROLLBACK terminate the current transaction and release data locks. In the absence of a START TRANSACTION or similar statement, the semantics of SQL are implementation-dependent.Example: A classic bank transfer of funds transaction.
START TRANSACTION;
UPDATE Account SET amount=amount-200 WHERE account_number=1234;
UPDATE Account SET amount=amount+200 WHERE account_number=2345;
IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;
Data definition
The Data Definition LanguageData Definition Language
A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...
(DDL) manages table and index structure. The most basic items of DDL are the
CREATE, ALTER, RENAME, DROP and TRUNCATE statements:-
CREATEcreates an object (a table, for example) in the database, e.g.,:
CREATE TABLE My_table(
my_field1 INT,
my_field2 VARCHAR(50),
my_field3 DATE NOT NULL,
PRIMARY KEY (my_field1, my_field2)
);
-
ALTERmodifies the structure of an existing object in various ways, for example, adding a column to an existing table or a constraint, e.g.,:
ALTER TABLE My_table ADD my_field4 NUMBER(3) NOT NULL;
-
TRUNCATEdeletes all data from a table in a very fast way, deleting the data inside the table and not the table itself. It usually implies a subsequent COMMIT operation, i.e., it cannot be rolled back.Truncate (SQL)In SQL, the TRUNCATE TABLE statement is a Data Definition Language operation that marks the extents of a table for deallocation . The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms...
TRUNCATE TABLE My_table;
-
DROPdeletes an object in the database, usually irretrievably, i.e., it cannot be rolled back, e.g.,:
DROP TABLE My_table;
Data types
Each column in an SQL table declares the type(s) that column may contain. ANSI SQL includes the following data types.Character strings
-
CHARACTER(n)orCHAR(n)— fixed-width n-character string, padded with spaces as needed -
CHARACTER VARYING(n)orVARCHAR(n)— variable-width string with a maximum size of n characters -
NATIONAL CHARACTER(n)orNCHAR(n)— fixed width string supporting an international character set -
NATIONAL CHARACTER VARYING(n)orNVARCHAR(n)— variable-widthNCHARstring
Bit strings
-
BIT(n)— an array of n bits -
BIT VARYING(n)— an array of up to n bits
Numbers
-
INTEGERandSMALLINT -
FLOAT,REALandDOUBLE PRECISION -
NUMERIC(precision, scale)orDECIMAL(precision, scale)
The precision is a positive integer that determines the number of significant digits in a particular radix (binary or decimal). The scale is a non-negative integer. A scale of 0 indicates that the number is an integer. For a scale of S, the exact numeric value is the integer value of the significant digits multiplied by 10-S.
SQL provides a function to round numerics or dates, called
TRUNC (in Informix, DB2, PostgreSQL, Oracle and MySQL) or ROUND (in Informix, Sybase, Oracle, PostgreSQL and Microsoft SQL Server)Date and time
-
DATE— for date values (e.g.,2011-05-03) -
TIME— for time values (e.g.,15:51:36). The granularity of the time value is usually a tick (100 nanoseconds). -
TIME WITH TIME ZONEorTIMETZ— the same asTIME, but including details about the time zone in question. -
TIMESTAMP— This is aDATEand aTIMEput together in one variable (e.g.,2011-05-03 15:51:36). -
TIMESTAMP WITH TIME ZONEorTIMESTAMPTZ— the same asTIMESTAMP, but including details about the time zone in question.
SQL provides several functions for generating a date / time variable out of a date / time string (
TO_DATE, TO_TIME, TO_TIMESTAMP), as well as for extracting the respective members (seconds, for instance) of such variables. The current system date / time of the database server can be called by using functions like NOW.Data control
The Data Control LanguageData Control Language
A data control language is a syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language .Examples of DCL commands include:...
(DCL) authorizes users and groups of users to access and manipulate data.
Its two main statements are:
-
GRANTauthorizes one or more users to perform an operation or a set of operations on an object. -
REVOKEeliminates a grant, which may be the default grant.
Example:
GRANT SELECT, UPDATE
ON My_table
TO some_user, another_user;
REVOKE SELECT, UPDATE
ON My_table
FROM some_user, another_user;
Procedural extensions
SQL is designed for a specific purpose: to query dataData
The term data refers to qualitative or quantitative attributes of a variable or set of variables. Data are typically the results of measurements and can be the basis of graphs, images, or observations of a set of variables. Data are often viewed as the lowest level of abstraction from which...
contained in a relational database
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
. SQL is a set
Set (computer science)
In computer science, a set is an abstract data structure that can store certain values, without any particular order, and no repeated values. It is a computer implementation of the mathematical concept of a finite set...
-based, declarative
Declarative programming
In computer science, declarative programming is a programming paradigm that expresses the logic of a computation without describing its control flow. Many languages applying this style attempt to minimize or eliminate side effects by describing what the program should accomplish, rather than...
query language, not an imperative language
Imperative programming
In computer science, imperative programming is a programming paradigm that describes computation in terms of statements that change a program state...
such as C
C (programming language)
C is a general-purpose computer programming language developed between 1969 and 1973 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system....
or BASIC. However, there are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs. These include:
| Source | Common Name |
Full Name |
|---|---|---|
| ANSI/ISO Standard | SQL/PSM SQL/PSM SQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute as an extension to SQL. It was first adopted in 1996, and it provides procedural programmability in addition to the querying commands of SQL.The SQL/PSM extension... |
SQL/Persistent Stored Modules |
| Interbase InterBase InterBase is a relational database management system currently developed and marketed by Embarcadero Technologies. InterBase is distinguished from other DBMSs by its small footprint, close to zero administration requirements, and multi-generational architecture... / Firebird Firebird (database server) Firebird is an open source SQL relational database management system that runs on Linux, Windows, and a variety of Unix. The database forked from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten .... |
PSQL | Procedural SQL |
| IBM | SQL PL SQL PL SQL PL stands for Structured Query Language Procedural Language and was developed by IBM as a set of commands that extend the use of SQL in the IBM DB2 database system. It provides procedural programmability in addition to the querying commands of SQL. It is a subset of the SQL Persistent Stored... |
SQL Procedural Language (implements SQL/PSM) |
| Microsoft Microsoft Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions... / Sybase Sybase Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms.... |
T-SQL Transact-SQL Transact-SQL is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements... |
Transact-SQL |
| Mimer SQL Mimer SQL Mimer SQL is an SQL-based relational database management system from the Swedish company Mimer Information Technology AB , which has been developed and produced since the 1970s. The Mimer SQL database engine is available for Microsoft Windows, Mac OS X, Linux, Symbian OS, Unix, VxWorks and OpenVMS... |
SQL/PSM SQL/PSM SQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute as an extension to SQL. It was first adopted in 1996, and it provides procedural programmability in addition to the querying commands of SQL.The SQL/PSM extension... |
SQL/Persistent Stored Module (implements SQL/PSM) |
| MySQL MySQL MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My... |
SQL/PSM SQL/PSM SQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute as an extension to SQL. It was first adopted in 1996, and it provides procedural programmability in addition to the querying commands of SQL.The SQL/PSM extension... |
SQL/Persistent Stored Module (implements SQL/PSM) |
| Oracle Oracle Corporation Oracle Corporation is an American multinational computer technology corporation that specializes in developing and marketing hardware systems and enterprise software products – particularly database management systems... |
PL/SQL PL/SQL PL/SQL is Oracle Corporation's procedural extension language for SQL and the Oracle relational database... |
Procedural Language/SQL (based on Ada Ada (programming language) Ada is a structured, statically typed, imperative, wide-spectrum, and object-oriented high-level computer programming language, extended from Pascal and other languages... ) |
| PostgreSQL PostgreSQL PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software... |
PL/pgSQL PL/pgSQL PL/pgSQL is a procedural language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language.... |
Procedural Language/PostgreSQL Structured Query Language (based on Oracle PL/SQL) |
| PostgreSQL PostgreSQL PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software... |
PL/PSM | Procedural Language/Persistent Stored Modules (implements SQL/PSM) |
In addition to the standard SQL/PSM extensions and proprietary SQL extensions, procedural and object-oriented
Object-oriented programming language
This is a list of object-oriented programming programming languages.-Languages with object-oriented features:*ABAP*Ada 95*AmigaE*BETA*Blue*Boo*C++*C#*COBOL*Cobra*ColdFusion*Common Lisp*COOL*CorbaScript*Clarion*CLU*Curl*D*Dylan*E*Eiffel...
programmability is available on many SQL platforms via DBMS integration with other languages. The SQL standard defines SQL/JRT
SQL/JRT
The SQL/JRT, or SQL Routines and Types for the Java Programming Language, extension to the SQL standard is defined by ISO/IEC 9075-13:2003. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL...
extensions (SQL Routines and Types for the Java Programming Language) to support Java
Java (programming language)
Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...
code in SQL databases. SQL Server 2005 uses the SQLCLR (SQL Server Common Language Runtime) to host managed .NET assemblies in the database, while prior versions of SQL Server were restricted to using unmanaged extended stored procedures which were primarily written in C. PostgreSQL allows functions to be written in a wide variety of languages including Perl
Perl
Perl is a high-level, general-purpose, interpreted, dynamic programming language. Perl was originally developed by Larry Wall in 1987 as a general-purpose Unix scripting language to make report processing easier. Since then, it has undergone many changes and revisions and become widely popular...
, Python
Python (programming language)
Python is a general-purpose, high-level programming language whose design philosophy emphasizes code readability. Python claims to "[combine] remarkable power with very clear syntax", and its standard library is large and comprehensive...
, Tcl
Tcl
Tcl is a scripting language created by John Ousterhout. Originally "born out of frustration", according to the author, with programmers devising their own languages intended to be embedded into applications, Tcl gained acceptance on its own...
, and C.
Criticism
SQL is a declarative computer languageDeclarative programming
In computer science, declarative programming is a programming paradigm that expresses the logic of a computation without describing its control flow. Many languages applying this style attempt to minimize or eliminate side effects by describing what the program should accomplish, rather than...
intended for use with relational databases. Many of the original SQL features were inspired by, but violated the semantics of the relational model
Relational model
The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...
and its tuple calculus
Tuple relational calculus
Tuple calculus is a calculus that was introduced by Edgar F. Codd as part of the relational model, in order to provide a declarative database-query language for this data model...
realization. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto
The Third Manifesto
The Third Manifesto is Christopher J. Date's and Hugh Darwen's proposal for future database management systems, a response to two earlier Manifestos with the same purpose. The theme of the manifestos is how to avoid the 'object-relational impedance mismatch' between object-oriented programming...
. Therefore, it cannot be considered relational in any significant sense, but is still widely called relational due to differentiation to other, pre-relational database languages which never intended to implement the relational model; due to its historical origin; and due to the use of the "relational" term by product vendors.
Other criticisms of SQL include:
- Implementations are inconsistent with the standard and, usually, incompatible between vendors. In particular date and time syntax, string concatenation,
NULLs, and comparison case sensitivityCase sensitivityText sometimes exhibits case sensitivity; that is, words can differ in meaning based on differing use of uppercase and lowercase letters. Words with capital letters do not always have the same meaning when written with lowercase letters....
vary from vendor to vendor. A particular exception is PostgreSQLPostgreSQLPostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
, which strives for compliance, and SQLiteSQLiteSQLite is an ACID-compliant embedded relational database management system contained in a relatively small C programming library. The source code for SQLite is in the public domain and implements most of the SQL standard...
, which strives to follow PostgreSQL. - The language makes it too easy to do a CartesianCartesian productIn mathematics, a Cartesian product is a construction to build a new set out of a number of given sets. Each member of the Cartesian product corresponds to the selection of one element each in every one of those sets...
join (joining all possible combinations), which results in "run-away" result sets whenWHEREclauses are mistyped. Cartesian joins are so rarely used in practice that requiring an explicitCARTESIANkeyword may be warranted. (SQL 1992 introduced theCROSS JOINkeyword that allows the user to make clear that a Cartesian join is intended, but the shorthand "comma-join" with no predicate is still acceptable syntax, which still invites the same mistake.) - It is also possible to misconstruct a
WHEREon an update or delete, thereby affecting more rows in a table than desired. (A work-around is to use transactions or habitually type in the WHERE clause first, then fill in the rest later.) - The grammar of SQL is perhaps unnecessarily complex, borrowing a COBOLCOBOLCOBOL is one of the oldest programming languages. Its name is an acronym for COmmon Business-Oriented Language, defining its primary domain in business, finance, and administrative systems for companies and governments....
-like keyword approach, when a function-influenced syntax could result in more re-use of fewer grammar and syntax rules. - The non-compliance of SQL to the relational model, and specifically to the 0th rule of Codd’s twelve rulesCodd's 12 rulesCodd's twelve rules are a set of thirteen rules proposed by Edgar F. Codd, a pioneer of the relational model for databases, designed to define what is required from a database management system in order for it to be considered relational, i.e., a relational database management system...
, is another source of complexity and incompatibility.
Cross-vendor portability
Popular implementations of SQL commonly omit support for basic features of Standard SQL, such as theDATE or TIME data types. The most obvious such examples, and incidentally the most popular commercial, proprietary SQL DBMSs, are Oracle (whose DATE behaves as DATETIME, and lacks a TIME type) and the MS SQL Server (before the 2008 version). As a result, SQL code can rarely be ported between database systems without modifications.There are several reasons for this lack of portability between database systems:
- The complexity and size of the SQL standard means that most implementors do not support the entire standard.
- The standard does not specify database behavior in several important areas (e.g., indexesIndex (database)A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
, file storage…), leaving implementations to decide how to behave. - The SQL standard precisely specifies the syntax that a conforming database system must implement. However, the standard's specification of the semantics of language constructs is less well-defined, leading to ambiguity.
- Many database vendors have large existing customer bases; where the SQL standard conflicts with the prior behavior of the vendor's database, the vendor may be unwilling to break backward compatibilityBackward compatibilityIn the context of telecommunications and computing, a device or technology is said to be backward or downward compatible if it can work with input generated by an older device...
. - Software vendors often desire to create incompatibilities with other products, as it provides a strong incentive for their existing users to remain loyal (see vendor lock-inVendor lock-inIn economics, vendor lock-in, also known as proprietary lock-in or customer lock-in, makes a customer dependent on a vendor for products and services, unable to use another vendor without substantial switching costs...
).
Standardization
SQL was adopted as a standard by the American National Standards InstituteAmerican National Standards Institute
The American National Standards Institute is a private non-profit organization that oversees the development of voluntary consensus standards for products, services, processes, systems, and personnel in the United States. The organization also coordinates U.S. standards with international...
(ANSI) in 1986 as SQL-86 and the International Organization for Standardization
International Organization for Standardization
The International Organization for Standardization , widely known as ISO, is an international standard-setting body composed of representatives from various national standards organizations. Founded on February 23, 1947, the organization promulgates worldwide proprietary, industrial and commercial...
(ISO) in 1987. The original SQL standard declared that the official pronunciation for SQL is "es queue el". Many English-speaking database professionals still use the nonstandard pronunciation /ˈsiːkwəl/ (like the word "sequel").
Until 1996, the National Institute of Standards and Technology
National Institute of Standards and Technology
The National Institute of Standards and Technology , known between 1901 and 1988 as the National Bureau of Standards , is a measurement standards laboratory, otherwise known as a National Metrological Institute , which is a non-regulatory agency of the United States Department of Commerce...
(NIST) data management standards program certified SQL DBMS compliance with the SQL standard. Vendors now self-certify the compliance of their products.
The SQL standard has gone through a number of revisions, as shown below:
| Year | Name | Alias | Comments |
|---|---|---|---|
| 1986 | SQL-86 | SQL-87 | First formalized by ANSI. |
| 1989 | SQL-89 | FIPS Federal Information Processing Standard A Federal Information Processing Standard is a publicly announced standardization developed by the United States federal government for use in computer systems by all non-military government agencies and by government contractors, when properly invoked and tailored on a contract... 127-1 |
Minor revision, adopted as FIPS 127-1. |
| 1992 | SQL-92 SQL-92 SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. For all but a few minor incompatibilities, the SQL-89 standard is forwards-compatible with SQL-92.... |
SQL2, FIPS 127-2 | Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2. |
| 1999 | SQL:1999 SQL:1999 SQL:1999 was the fourth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:1999 standard, also known as SQL3, was published in 1999. Unlike previous editions, the standard's name used a colon instead of a hyphen for consistency with the... |
SQL3 | Added regular expression matching, recursive queries, triggers Database trigger A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database... , support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features. |
| 2003 | SQL:2003 SQL:2003 SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the... |
SQL 2003 | Introduced XML XML Extensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards.... -related features, window functions, standardized sequences, and columns with auto-generated values (including identity-columns). |
| 2006 | SQL:2006 | SQL 2006 | ISO/IEC 9075-14:2006 defines ways in which SQL can be used in conjunction with XML. It defines ways of importing and storing XML data in an SQL database, manipulating it within the database and publishing both XML and conventional SQL-data in XML form. In addition, it enables applications to integrate into their SQL code the use of XQuery XQuery - Features :XQuery provides the means to extract and manipulate data from XML documents or any data source that can be viewed as XML, such as relational databases or office documents.... , the XML Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents. |
| 2008 | SQL:2008 | SQL 2008 | Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers. Adds the TRUNCATE statement. |
Interested parties may purchase SQL standards documents from ISO or ANSI. A draft of SQL:2008 is freely available as a zip
ZIP (file format)
Zip is a file format used for data compression and archiving. A zip file contains one or more files that have been compressed, to reduce file size, or stored as is...
archive.
Standard structure
The SQL standard is divided into several parts, including:- SQL Framework, provides logical concept
- SQL/Foundation, defined in ISO/IEC 9075, Part 2. This part of the standard contains the most central elements of the language. It consists of both mandatory and optional features.
- The SQL/Bindings, specifies how SQL is to be bound to variable host languages, excluding Java.
- The SQL/CLISQL/CLIThe SQL/CLI, or Call-Level Interface, extension to the SQL standard is defined in ISO/IEC 9075-3:2003. This extension defines common interfacing components that can be used to execute SQL statements from applications written in other programming languages...
, or Call-Level Interface, part is defined in ISO/IEC 9075, Part 3. SQL/CLI defines common interfacing components (structures and procedures) that can be used to execute SQL statements from applications written in other programming languages. SQL/CLI is defined in such a way that SQL statements and SQL/CLI procedure calls are treated as separate from the calling application's source code. Open Database ConnectivityOpen Database ConnectivityIn computing, ODBC is a standard C interface for accessing database management systems . The designers of ODBC aimed to make it independent of database systems and operating systems...
is a well-known superset of SQL/CLI. This part of the standard consists solely of mandatory features. - The SQL/PSMSQL/PSMSQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute as an extension to SQL. It was first adopted in 1996, and it provides procedural programmability in addition to the querying commands of SQL.The SQL/PSM extension...
, or Persistent Stored Modules, part is defined by ISO/IEC 9075, Part 4. SQL/PSM standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors and local variables, and assignment of expressions to variables and parameters. In addition, SQL/PSM formalizes declaration and maintenance of persistent database language routines (e.g., "stored procedures"). This part of the standard consists solely of optional features. - The SQL/MEDSQL/MEDThe SQL/MED, or Management of External Data, extension to the SQL standard is defined by ISO/IEC 9075-9:2003. SQL/MED provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed...
, or Management of External Data, part is defined by ISO/IEC 9075, Part 9. SQL/MED provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed by, an SQL-based DBMS. This part of the standard consists solely of optional features. - The SQL/OLBSQL/OLBThe SQL/OLB, or Object Language Bindings, extension to the SQL standard is defined by ISO/IEC 9075-10:2003. SQL/OLB defines the syntax and symantics of SQLJ, which is SQL embedded in Java. The standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies...
, or Object Language Bindings, part is defined by ISO/IEC 9075, Part 10. SQL/OLB defines the syntax and symantics of SQLJSQLJSQLJ is an ISO standard for embedding SQL statements in Java programs.Whereas JDBC provides an API, SQLJ consists of a language extension...
, which is SQL embedded in Java. The standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies various Java packages and their contained classes. This part of the standard consists solely of optional features. - The SQL/MM (Multimedia), This extends SQL to deal intelligently with large, complex and sometimes streaming items of data, such as video, audio and spatial dataGeoreferenceTo georeference something means to define its existence in physical space. That is, establishing its location in terms of map projections or coordinate systems. The term is used both when establishing the relation between raster or vector images and coordinates, and when determining the spatial...
. - The SQL/SchemataSQL/SchemataThe SQL/Schemata, or Information and Definition Schemas, part to the SQL standard is defined by ISO/IEC 9075-11:2008. SQL/Schemata defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing...
, or Information and Definition Schemas, part is defined by ISO/IEC 9075, Part 11. SQL/Schemata defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing. These tools include the SQL object identifier, structure and integrity constraints, security and authorization specifications, features and packages of ISO/IEC 9075, support of features provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and sizing items, and the values supported by the DBMS implementations. This part of the standard contains both mandatory and optional features. - The SQL/JRTSQL/JRTThe SQL/JRT, or SQL Routines and Types for the Java Programming Language, extension to the SQL standard is defined by ISO/IEC 9075-13:2003. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL...
, or SQL Routines and Types for the Java Programming Language, part is defined by ISO/IEC 9075, Part 13. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL structured user-defined types. This part of the standard consists solely of optional features. - The SQL/XMLSQL/XMLSQL/XML or XML-Related Specifications is an extension to the Structured Query Language specification, which defines the use of XML in conjunction with SQL. The XML data type is introduced, as well as several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage...
, or XML-Related Specifications, part is defined by ISO/IEC 9075, Part 14. SQL/XML specifies SQL-based extensions for using XML in conjunction with SQL. The XML data type is introduced, as well as several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in an SQL database. This part of the standard consists solely of optional features.
Alternatives
A distinction should be made between alternatives to relational query languages and alternatives to SQL. Below are proposed relational alternatives to SQL. See navigational databaseNavigational database
A navigational database is a type of database characterized by the fact that objects in it are found primarily by following references from other objects...
for alternatives to relational:
- .QL.QL.QL is an object-oriented query language used to retrieve data from relational database management systems. It is reminiscent of the standard query language SQL and the object-oriented programming language Java. .QL is an object-oriented variant of a logic programming language known in the...
- object-oriented Datalog - 4D Query Language4th Dimension (Software)4th Dimension is a relational database management system and IDE developed by Laurent Ribardière. 4D was created in 1984....
(4D QL) - DatalogDatalogDatalog is a query and rule language for deductive databases that syntactically is a subset of Prolog. Its origins date back to the beginning of logic programming, but it became prominent as a separate area around 1977 when Hervé Gallaire and Jack Minker organized a workshop on logic and databases...
- HTSQLHtsqlHyper Text Structured Query Language is a schema-driven URI-to-SQL query language that takes a request over HTTP, converts it to a SQL query, executes the query against a database, and returns the results in a format best suited for the user agent The HTSQL language is implemented on "HTSQL...
- URL based query method - IBM Business System 12 (IBM BS12) - one of the first fully relational database management systems, introduced in 1982
- ISBLISBLISBL is the relational algebra notation that was invented for PRTV, one of the earliest database management systems to implement E.F. Codd's relational model of data.-See also:...
- Java Persistence Query LanguageJava Persistence Query LanguageThe Java Persistence Query Language is a platform-independent object-oriented query language defined as part of the Java Persistence API specification.JPQL is used to make queries against entities stored in a relational database...
(JPQL) - The query language used by the Java Persistence API and HibernateHibernate (Java)Hibernate is an object-relational mapping library for the Java language, providing a framework for mapping an object-oriented domain model to a traditional relational database...
persistence library - LINQLanguage Integrated QueryLanguage Integrated Query is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages, although ports exist for Java, PHP and JavaScript....
- Object Query LanguageObject Query LanguageObject Query Language is a query language standard for object-oriented databases modeled after SQL. OQL was developed by the Object Data Management Group . Because of its overall complexity no vendor has ever fully implemented the complete OQL...
- QBE (Query By Example)Query by ExampleQuery by Example is a database query language for relational databases. It was devised by Moshé M. Zloof at IBM Research during the mid 1970s, in parallel to the development of SQL. It is the first graphical query language, using visual tables where the user would enter commands, example elements...
created by Moshè Zloof, IBM 1977 - QuelQUEL query languagesQUEL is a relational database access language, similar in most ways to SQL. It was created as a part of the Ingres effort at University of California, Berkeley, based on Codd's earlier suggested but not implemented Data Sub-Language ALPHA. QUEL was used for a short time in most products based on...
introduced in 1974 by the U.C. Berkeley Ingres project. - Tutorial D
- SBQL - the Stack Based Query Language (SBQL)
- UnQLUnQLUnQL is a specification for a query language for NoSQL databases, developed by the creators of SQLite and CouchDB database management systems, based on SQL used in relational databases with the elements of JSON. It is built to query collections of documents with loosely defined fields...
- the Unstructured Query Language, a functional superset of SQL, developed by the authors of SQLiteSQLiteSQLite is an ACID-compliant embedded relational database management system contained in a relatively small C programming library. The source code for SQLite is in the public domain and implements most of the SQL standard...
and CouchDBCouchDBApache CouchDB, commonly referred to as CouchDB, is an open source document-oriented database written mostly in the Erlang programming language. It is part of the NoSQL group of data stores and is designed for local replication and to scale horizontally across a wide range of devices... - XQueryXQuery- Features :XQuery provides the means to extract and manipulate data from XML documents or any data source that can be viewed as XML, such as relational databases or office documents....
See also
- Comparison of object-relational database management systemsComparison of object-relational database management systemsThe following Database Management Systems have at least some object-relational features. They vary widely in their completeness and the approaches taken....
- Comparison of relational database management systemsComparison of relational database management systemsThe following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. This article is not all-inclusive or necessarily up to date...
- D (data language specification)D (data language specification)D is a set of requirements for what Christopher J. Date and Hugh Darwen believe a relational database query language ought to be like. It is proposed in their book The Third Manifesto.-Overview:...
- D4 (programming language)D4 (programming language)D4 is a computer language used in Dataphor, a relational database management system.-Syntax:Alphora, the creators of D4, have given it a Pascal like syntax...
(an implementation of D) - Hierarchical modelHierarchical modelA hierarchical database model is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has only one parent...
- List of relational database management systems
- MUMPSMUMPSMUMPS , or alternatively M, is a programming language created in the late 1960s, originally for use in the healthcare industry. It was designed for the production of multi-user database-driven applications...
- NoSQLNosqlIn computing, NoSQL is a broad class of database management systems that differ from the classic model of the relational database management system in some significant ways. These data stores may not require fixed table schemas, usually avoid join operations, and typically scale horizontally...
External links
- 1995 SQL Reunion: People, Projects, and Politics, by Paul McJones (ed.): transcript of a reunion meeting devoted to the personal history of relational databases and SQL.
- American National Standards Institute. X3H2 Records, 1978–1995 Charles Babbage InstituteCharles Babbage InstituteThe Charles Babbage Institute is a research center at the University of Minnesota specializing in the history of information technology, particularly the history since 1935 of digital computing, programming/software, and computer networking....
Collection documents the H2 committee’s development of the NDL and SQL standards. - Oral history interview with Donald D. Chamberlin Charles Babbage InstituteCharles Babbage InstituteThe Charles Babbage Institute is a research center at the University of Minnesota specializing in the history of information technology, particularly the history since 1935 of digital computing, programming/software, and computer networking....
In this oral history Chamberlin recounts his early life, his education at Harvey Mudd CollegeHarvey Mudd CollegeHarvey Mudd College is a private residential liberal arts college of science, engineering, and mathematics, located in Claremont, California. It is one of the institutions of the contiguous Claremont Colleges, which share adjoining campus grounds....
and Stanford UniversityStanford UniversityThe Leland Stanford Junior University, commonly referred to as Stanford University or Stanford, is a private research university on an campus located near Palo Alto, California. It is situated in the northwestern Santa Clara Valley on the San Francisco Peninsula, approximately northwest of San...
, and his work on relational database technology. Chamberlin was a member of the System R research team and, with Raymond F. BoyceRaymond F. BoyceRaymond 'Ray' Boyce was an American computer scientist who was known for his research in relational databases.Boyce grew up in New York, and went to college in Providence, Rhode Island. He earned his PhD in computer science at Purdue in 1971 . After leaving Purdue he worked on database projects...
, developed the SQL database language. Chamberlin also briefly discusses his more recent research on XML query languages. - Comparison of Different SQL Implementations This comparison of various SQL implementations is intended to serve as a guide to those interested in porting SQL code between various RDBMS products, and includes comparisons between SQL:2008, PostgreSQL, DB2, MS SQL Server, MySQL, Oracle, and Informix.