SQL (IPA: /ˌɛsˌkjuːˈɛl/ or /ˈsiːkwəl/) is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management.^[1][2] SQL stands for Structured Query Language. A programming paradigm is a paradigmatic style of programming (compare with a methodology, which is a paradigmatic style of doing software engineering). ... This does not cite any references or sources. ... Donald D. Chamberlin is best known as one of the principal designers of the original SQL language specification. ... Raymond ‘Ray’ Boyce grew up in New York, he went to college in Providence, Rhode Island and got his PhD in Purdue in 1971 [1] . After he left Purdue he worked on database projects for IBM in Yorktown Heights, New York. ... For other uses, see Software developer (disambiguation). ... For other uses, see IBM (disambiguation) and Big Blue. ... A software release refers to the creation and availability of a new version of a computer software product. ... In computer science, a type system defines how a programming language classifies values and expressions into types, how it can manipulate those types and how they interact. ... On computer science, a datatype (often simply type) is a name or label for a set of values and some operations which can be performed on that set of values. ... In computing, strongly-typed, when applied to a programming language, is used to describe how the language handles datatypes. ... Look up Implementation in Wiktionary, the free dictionary. ... See DBMS for a shorter list of “typical”, representative database management systems. ... The term computer language is a more expansive and alternate term for the more commonly-used term programming language. ... For other uses, see Data (disambiguation). ... A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. ...

SQL is a standard interactive and programming language for getting information from and updating a database. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth. There is also a programming interface.

The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM's original relational database product, System R. The SQL language was later formally standardized by the American National Standards Institute (ANSI) in 1986. Subsequent versions of the SQL standard have been released as International Organization for Standardization (ISO) standards. For other uses, see IBM (disambiguation) and Big Blue. ... Donald D. Chamberlin is best known as one of the principal designers of the original SQL language specification. ... Raymond ‘Ray’ Boyce grew up in New York, he went to college in Providence, Rhode Island and got his PhD in Purdue in 1971 [1] . After he left Purdue he worked on database projects for IBM in Yorktown Heights, New York. ... System R is a database system built as a research project at IBM San Jose Research (now IBM Almaden Research Center) in the 1970s. ... “Standard” redirects here. ... The American National Standards Institute or ANSI (pronounced an-see) is a nonprofit organization that oversees the development of standards for products, services, processes and systems in the United States. ... “ISO” redirects here. ...

Originally designed as a declarative query and data manipulation language, variations of SQL have been created by SQL database management system (DBMS) vendors that add procedural constructs, control-of-flow statements, user-defined data types, and various other language extensions. With the release of the SQL:1999 standard, many such extensions were formally adopted as part of the SQL language via the SQL Persistent Stored Modules (SQL/PSM) portion of the standard. Declarative programming is a term with two distinct meanings, both of which are in current use. ... A database management system (DBMS) is computer software designed for the purpose of managing databases. ...

Common criticisms of SQL include a perceived lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)), and its complex and occasionally ambiguous language grammar and semantics. The Greek lowercase omega (ω) character is historically used by academics to represent Null in relational databases. ...

History

During the 1970s, a group at IBM's San Jose research center developed the System R relational database management system, based on the model introduced by Dr. E.F. Codd in his influential paper, A Relational Model of Data for Large Shared Data Banks.^[3] Donald D. Chamberlin and Raymond F. Boyce of IBM subsequently created the Structured English Query Language (SEQUEL) to manipulate and manage data stored in System R.^[4] The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.^[5] System R is a database system built as a research project at IBM San Jose Research (now IBM Almaden Research Center) in the 1970s. ... Academic publishing describes the subfield of publishing which distributes academic research and scholarship. ... “(TM)” redirects here. ... “UK” redirects here. ... Hawker-Ciggerley was a group of UK aircraft manufacturing companies formed as a result of the merger of Hawker Aircraft with Armstrong Siddeley. ...

The first non-commercial non-SQL RDBMS, Ingres, was developed in 1974 at the U.C. Berkeley. Ingres implemented a query language known as QUEL, which was later supplanted in the marketplace by SQL.^[5] Ingres (pronounced ingress) is a commercially supported, open-source relational database management system. ... Sather tower (the Campanile) looking out over the San Francisco Bay and Mount Tamalpais. ... This article or section does not adequately cite its references or sources. ...

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, CIA, and other government agencies. In the summer of 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. Oracle V2 beat IBM's release of the System/38 RDBMS to market by a few weeks.^{[citation needed]} Oracle Corporation (NASDAQ: ORCL) is one of the major companies developing database management systems (DBMS), tools for database development, middle-tier software, enterprise resource planning software (ERP), customer relationship management software (CRM) and supply chain management (SCM) software. ... A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by Edgar F. Codd. ... USN redirects here. ... CIA redirects here. ... Also: 1979 by Smashing Pumpkins. ... The term Oracle database may refer either to the database management system (DBMS) software released by Oracle Corporation as Oracle RDBMS, or to any of the individual databases managed by such software. ... VAX is a 32-bit computing architecture that supports an orthogonal instruction set (machine language) and virtual addressing (i. ... The IBM System/38 was a computer. ...

After methodically testing SQL at customer test sites to demonstrate both 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.^[6] SQL/DS (Structured Query Language/Data System) was IBMs first commercial implementation for its mainframe computers of a DBMS (Database Management System) built around the SQL language. ... DB2 is one of IBMs lines of relational database management system (or, as IBM now calls it, data server) software products within IBMs broader Information Management Software line. ... Year 1981 (MCMLXXXI) was a common year starting on Thursday (link displays the 1981 Gregorian calendar). ... Year 1983 (MCMLXXXIII) was a common year starting on Saturday (link displays the 1983 Gregorian calendar). ...

Standardization

SQL was adopted as a standard by the ANSI in 1986 and ISO in 1987. In their SQL standard, the ANSI declared that the official pronunciation for SQL is "es queue el".^[7] However, many English-speaking database professionals still pronounce the name of the language like the word "sequel". Until 1996, the National Institute of Standards and Technology (NIST) data management standards program was tasked with certifying SQL DBMS compliance with the SQL standard. In 1996, however, the NIST data management standards program was dissolved, and vendors are now relied upon to self-certify their products for compliance.^[8] Year 1996 (MCMXCVI) was a leap year starting on Monday (link will display full 1996 Gregorian calendar). ... NIST logo The National Institute of Standards and Technology (NIST, formerly known as The National Bureau of Standards) is a non-regulatory agency of the United States Department of Commerce’s Technology Administration. ...

The SQL standard has gone through a number of revisions, as shown below:

The SQL standard is not freely available. SQL:2003 and SQL:2006 may be purchased from ISO or ANSI. A late draft of SQL:2003 is freely available as a zip archive, however, from Whitemarsh Information Systems Corporation. The zip archive contains a number of PDF files that define the parts of the SQL:2003 specification. Year 1989 (MCMLXXXIX) was a common year starting on Sunday (link displays 1989 Gregorian calendar). ... Federal Information Processing Standards (FIPS) are publicly announced standards developed by the U.S. Federal government for use by all (non-military) government agencies and by government contractors. ... Year 1992 (MCMXCII) was a leap year starting on Wednesday (link will display full 1992 Gregorian calendar). ... Structured Query Language (SQL) is the most popular computer language used to create, modify and retrieve data from relational database management systems. ... This article is about the year. ... Year 2003 (MMIII) was a common year starting on Wednesday of the Gregorian calendar. ... The Extensible Markup Language (XML) is a general-purpose markup language. ... Year 2006 (MMVI) was a common year starting on Sunday of the Gregorian calendar. ... XQuery is a query language (with some programming language features) that is designed to query collections of XML data. ... The World Wide Web Consortium (W3C) is a consortium that produces standards—recommendations, as they call them—for the World Wide Web. ... The ZIP file format is the most widely-used compressed file format in the IBM PC world. ... PDF is an abbreviation with several meanings: Portable Document Format Post-doctoral fellowship Probability density function There also is an electronic design automation company named PDF Solutions. ...

Scope and extensions

Procedural extensions

SQL is designed for a specific purpose: to query data 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 are: A relational database is a database that conforms to the relational model, and refers to a databases data and schema (the databases structure of how that data is arranged). ... In mathematics, a set can be thought of as any collection of distinct objects considered as a whole. ... Declarative programming is a term with two distinct meanings, both of which are in current use. ... In computer science, imperative programming, as opposed to declarative programming, is a programming paradigm that describes computation in terms of a program state and statements that change the program state. ... C is a general-purpose, block structured, procedural, imperative computer programming language developed in 1972 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system. ... BASIC (Beginners All-purpose Symbolic Instruction Code) is a family of high-level programming languages. ... ...

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. Other database platforms, like MySQL and Postgres, allow functions to be written in a wide variety of languages including Perl, Python, Tcl, and C. SQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute (ANSI) as an extension to SQL.[1] It was first adopted in 1996,[2] and it provides procedural programmability in addition to the querying commands of SQL. The SQL/PSM extension... 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 DB2 UDB database system. ... Microsoft Corporation, (NASDAQ: MSFT, HKSE: 4338) is a multinational computer technology corporation with global annual revenue of US$44. ... Sybase Inc. ... Transact-SQL (TSQL) is an enhanced version of the SQL relational database language. ... MySQL (pronounced (IPA) , my S-Q-L[1]) is a multithreaded, multi-user SQL database management system (DBMS)[2] which has, according to MySQL AB, more than 10 million installations. ... Oracle Corporation (NASDAQ: ORCL) is one of the major companies developing database management systems (DBMS), tools for database development, middle-tier software, enterprise resource planning software (ERP), customer relationship management software (CRM) and supply chain management (SCM) software. ... PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporations proprietary server-based procedural extension to the SQL database language. ... PostgreSQL is a free software object-relational database management system (ORDBMS), released under a BSD-style license. ... PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) is a procedural language built into the PostgreSQL RDBMS. It closely resembles Oracles PL/SQL language. ... An object-oriented programming language (also called an OO language) is one that allows or encourages, to some degree, object-oriented programming techniques such as encapsulation, inheritance, interfaces, and polymorphism. ... 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. ... Java language redirects here. ... Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. ... SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft . ... Microsoft . ... Wikibooks has a book on the topic of Perl Programming Perl is a dynamic programming language created by Larry Wall and first released in 1987. ... Python is a high-level programming language first released by Guido van Rossum in 1991. ... Tcl (originally from Tool Command Language, but nonetheless conventionally rendered as Tcl rather than TCL; and pronounced tickle) is a scripting language created by John Ousterhout. ...

Additional extensions

SQL:2003 also defines several additional extensions to the standard to increase SQL functionality overall. These extensions include:

The SQL/CLI, or Call-Level Interface, extension is defined in ISO/IEC 9075-3:2003. This extension defines common interfacing components (structures and procedures) that can be used to execute SQL statements from applications written in other programming languages. The SQL/CLI extension 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. The SQL/CLI, or Call-Level Interface, extension to the SQL standard is defined in ISO/IEC 9075-3:2003. ...

The SQL/MED, or Management of External Data, extension 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 by, an SQL-based DBMS. The SQL/MED, or Management of External Data, extension to the SQL standard is defined by ISO/IEC 9075-9:2003. ...

The SQL/OLB, or 'Object Language Bindings, extension 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 various Java packages and their contained classes. The SQL/OLB, or Object Language Bindings, extension to the SQL standard is defined by ISO/IEC 9075-10:2003. ... SQLJ is an ISO standard (ISO/IEC 9075-10) for embedding SQL statements in Java programs. ...

The SQL/Schemata, or Information and Definition Schemas, extension is defined by ISO/IEC 9075-11:2003. 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.^[9] The SQL/Schemata, or Information and Definition Schemas, extension to the SQL standard is defined by ISO/IEC 9075-11:2003. ...

The SQL/JRT, or SQL Routines and Types for the Java Programming Language, extension 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 structured user-defined types.

The SQL/XML, or XML-Related Specifications, extension is defined by ISO/IEC 9075-14:2003. SQL/XML specifies SQL-based extensions for using 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. The SQL/XML, or XML-Related Specifications, extension to the SQL standard is defined by ISO/IEC 9075-14:2003. ...

The SQL/PSM, or Persistent Stored Modules, extension is defined by ISO/IEC 9075-4:2003. 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").

Language elements

This chart shows several of the SQL language elements that compose a single statement.

The SQL language is sub-divided into several language elements, including: Image File history File links Size of this preview: 800 × 243 pixelsFull resolution (831 × 252 pixel, file size: 28 KB, MIME type: image/png) This chart represents several constituent components of the SQL language in a single SQL Update (SQL) statement. ... Image File history File links Size of this preview: 800 × 243 pixelsFull resolution (831 × 252 pixel, file size: 28 KB, MIME type: image/png) This chart represents several constituent components of the SQL language in a single SQL Update (SQL) statement. ...

• Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
• Queries which retrieve data based on specific criteria.
• Expressions which can produce either scalar values or tables consisting of columns and rows of data.
• Predicates which specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow.
• Clauses which are (in some cases optional) constituent components of statements and queries.^[10]
• Whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.
• SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.

In computing, a scalar is a variable or field that can hold only one value at a time; as opposed to composite variables like array, list, record, etc. ... In relational databases, SQL databases, and flat file databases, a table is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. ... In the context of a relational database, a column of a table is a set of data values of a particular simple type, one for each row of the table. ... 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. ... A ternary, three-valued or trivalent logic is a term to describe any of several multi-valued logic systems in which there are three truth values indicating true, false and some third value. ... For other uses, see white space. ... A semicolon (  ;  ) is a punctuation mark. ...

Queries

The most common operation in SQL databases is the query, which is performed with the declarative SELECT keyword. SELECT retrieves data from a specified table, or multiple related tables, in a database. While often grouped with Data Manipulation Language (DML) statements, the standard SELECT query is considered separate from SQL DML, as it has no persistent effects on the data stored in a database. Note that there are some platform-specific variations of SELECT that can persist their effects in a database, such as the SELECT INTO syntax that exists in some databases.^[11] An SQL SELECT statement returns a result set of records from one or more tables. ... In relational databases, SQL databases, and flat file databases, a table is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. ... Data Manipulation Language (DML) is a family of computer languages used by computer programs or database users to retrieve, insert, delete and update data in a database. ...

SQL queries allow the user to specify a description of the desired result set, but it is left to the devices of the database management system (DBMS) to plan, optimize, and perform the physical operations necessary to produce that result set in as efficient a manner as possible. An SQL query includes a list of columns to be included in the final result immediately following the SELECT keyword. An asterisk ("*") can also be used as a "wildcard" indicator to specify that all available columns of a table (or multiple tables) are to be returned. SELECT is the most complex statement in SQL, with several optional keywords and clauses, including: A database management system (DBMS) is computer software designed for the purpose of managing databases. ... A query plan (or query execution plan) is an set of steps used to access information in a SQL relational database management system. ... The query optimizer is a component of database management system that is used to analyzes queries submitted to database server for execution, and then determines the optimal way to execute the query. ...

• The FROM clause which indicates the source table or tables from which the data is to be retrieved. The FROM clause can include optional JOIN clauses to join related tables to one another based on user-specified criteria.
• The WHERE clause includes a comparison predicate, which is used to restrict the number of rows returned by the query. The WHERE clause is applied before the GROUP BY clause. The WHERE clause eliminates all rows from the result set where the comparison predicate does not evaluate to True.
• The GROUP BY clause is used to combine, or group, rows with related values into elements of a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregate functions or to eliminate duplicate rows from a result set.
• The HAVING clause includes a comparison predicate used to eliminate rows after the GROUP BY clause is applied to the result set. Because it acts on the results of the GROUP BY clause, aggregate functions can be used in the HAVING clause predicate.
• The ORDER BY clause is used to identify which columns are used to sort the resulting data, and in which order they should be sorted (options are ascending or descending). The order of rows returned by an SQL query is never guaranteed unless an ORDER BY clause is specified.

The following is an example of a SELECT query that returns a list of expensive books. The query retrieves all rows from the books 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 books table should be included in the result set. FROM is an SQL reserved word. ... A JOIN clause in SQL combines records from two tables in a relational database and results in a new (temporary) table, also called a joined table. Structured Query Language (SQL:2003) specifies two types of joins: inner and outer. ... A WHERE statement in SQL specifies that an SQL command should only be ran on rows that meet a specified condition. ... It has been suggested that this article or section be merged into Data Manipulation Language. ... A HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions. ... 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. ...

 SELECT * FROM books WHERE price > 100.00 ORDER BY title; 

The example below demonstrates the use of multiple tables in a join, grouping, and aggregation in an SQL query, by returning a list of books and the number of authors associated with each book.

 SELECT books.title, count(*) AS Authors FROM books JOIN book_authors ON books.isbn = book_authors.isbn GROUP BY books.title; 

Example output might resemble the following:

 Title Authors ---------------------- ------- SQL Examples and Guide 3 The Joy of SQL 1 How to use Wikipedia 2 Pitfalls of SQL 1 How SQL Saved my Dog 1 

(The underscore character "_" is often used as part of table and column names to separate descriptive words because other punctuation tends to conflict with SQL syntax. For example, a dash "-" would be interpreted as a minus sign.)

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: The International Standard Book Number, or ISBN (sometimes pronounced is-ben), is a unique[1] identifier for books, intended to be used commercially. ...

 SELECT title, count(*) AS Authors FROM books NATURAL JOIN book_authors GROUP BY title; 

However, many vendors either don't support this approach, or it requires certain column naming conventions. Thus, it is less common in practice.

Data retrieval is very often combined with data projection when the user is looking for calculated values and not just the verbatim data stored in primitive data types, or when the data needs to be expressed in a form that is different from how it's stored. 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. In computer science, primitive types — as distinct from composite types — are data types provided by a programming language as basic building blocks. ...

 SELECT isbn, title, price, price * 0.06 AS sales_tax FROM books WHERE price > 100.00 ORDER BY title; 

Data manipulation

First, there are the standard Data Manipulation Language (DML) elements. DML is the subset of the language used to add, update and delete data:

• INSERT is used to add rows (formally tuples) to an existing table, eg:

 INSERT INTO my_table (field1, field2, field3) VALUES ('test', 'N', NULL); 

• UPDATE is used to modify the values of a set of existing table rows, eg:

 UPDATE my_table SET field1 = 'updated value' WHERE field2 = 'N'; 

• DELETE removes zero or more existing rows from a table, eg:

 DELETE FROM my_table WHERE field2 = 'N'; 

• MERGE is used to combine the data of multiple tables. It is something of a combination of the INSERT and UPDATE elements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called an "upsert".

A SQL INSERT statement adds one or more records to a table in a relational database. ... In mathematics, a tuple is a finite sequence (also known as an ordered list) of objects, each of a specified type. ... An UPDATE statement in SQL changes data in one or more records in a relational database management system. ... An SQL DELETE statement removes one or more records from a table. ... Wikipedia does not have an article with this exact name. ... The SQL like UPSERT statement inserts a record to a table in a database if the record does not exist; if the record already exists, an update operation is performed. ...

Transaction controls

Transactions, if available, can be used to wrap around the DML operations:

• BEGIN WORK (or START TRANSACTION, depending on SQL dialect) can be used to mark the start of a database transaction, which either completes completely or not at all.
• COMMIT causes all data changes in a transaction to be made permanent.
• ROLLBACK causes all data changes since the last COMMIT or ROLLBACK to be discarded, so that the state of the data is "rolled back" to the way it was prior to those changes being requested.

COMMIT and ROLLBACK interact with areas such as transaction control and locking. Strictly, both terminate any open transaction and release any locks held on data. In the absence of a BEGIN WORK or similar statement, the semantics of SQL are implementation-dependent. Example: It has been suggested that this article or section be merged into Database transaction. ... A database transaction is a unit of interaction with a database management system or similar system that is treated in a coherent and reliable way independent of other transactions. ... A COMMIT statement in SQL ends a transaction within a relational database management system (RDBMS) and makes all changes visible to other users. ... In database technologies, a rollback is an operation which returns the database to some previous state. ...

 BEGIN WORK; UPDATE inventory SET quantity = quantity - 3 WHERE item = 'pants'; COMMIT; 

Data definition

The second group of keywords is the Data Definition Language (DDL). DDL allows the user to define new tables and associated elements. Most commercial SQL databases have proprietary extensions in their DDL, which allow control over nonstandard features of the database system. The most basic items of DDL are the CREATE, ALTER, RENAME, TRUNCATE and DROP statements: A Data Definition Language (DDL) is a computer language for defining data. ...

• CREATE causes an object (a table, for example) to be created within the database.
• DROP causes an existing object within the database to be deleted, usually irretrievably.
• TRUNCATE deletes all data from a table (non-standard, but common SQL statement).
• ALTER statement permits the user to modify an existing object in various ways -- for example, adding a column to an existing table.

Example: A CREATE statement in SQL creates an object inside of a relational database management system (RDBMS). ... A DROP statement in SQL removes an object from a relational database management system (RDBMS). ... The Truncate statement removes all the data from a table. ... An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). ...

 CREATE TABLE my_table ( my_field1 INT, my_field2 VARCHAR (50), my_field3 DATE NOT NULL, PRIMARY KEY (my_field1, my_field2) ); 

Data control

The third group of SQL keywords is the Data Control Language (DCL). DCL handles the authorization aspects of data and permits the user to control who has access to see or manipulate data within the database. Its two main keywords are: A Data Control Language is a computer language for controlling access to data in a database. ...

• GRANT authorizes one or more users to perform an operation or a set of operations on an object.
• REVOKE removes or restricts the capability of a user to perform an operation or a set of operations.

Example:

 GRANT SELECT, UPDATE ON my_table TO some_user, another_user. 

Other

• ANSI-standard SQL supports double dash, --, as a single line comment identifier (some extensions also support curly brackets or C style /* comments */ for multi-line comments).

Example: An illustration of Java source code with prologue comments indicated in red and inline comments in green. ...

 SELECT * FROM inventory -- Retrieve everything from inventory table 

• Some SQL servers allow User Defined Functions

A User Defined Function, or UDF, is an extension to an SQL database server (like MySQL) that can be compiled for use in an SQL query. ...

Criticisms of SQL

Technically, SQL is a declarative computer language for use with "SQL databases". Theorists and some practitioners note that many of the original SQL features were inspired by, but in violation of, the relational model for database management and its tuple calculus realization. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto. The relational model for database management is a database model based on predicate logic and set theory. ... This article may be too technical for most readers to understand. ... The Third Manifesto (1995) is Christopher J. Dates and Hugh Darwens proposal for future relational database management systems that would avoid Object-Relational Impedance Mismatch between object-oriented programming languages and RDBMSs by fully supporting all the capabilities of the relational model. ...

In addition, there are also some criticisms about the practical use of SQL:

• Implementations are inconsistent and, usually, incompatible between vendors. In particular date and time syntax, string concatenation, nulls, and comparison case sensitivity often vary from vendor to vendor.
• The language makes it too easy to do a Cartesian join (joining all possible combinations), which results in "run-away" result sets when WHERE clauses are mistyped. Cartesian joins are so rarely used in practice that requiring an explicit CARTESIAN keyword may be warranted.
• It is also possible to misconstruct a WHERE on an update or delete, thereby affecting more rows in a table than desired. (Which is easy to prevent by using the LIMIT clause.)
• SQL—and the relational model as it is—offers no standard way for handling tree-structures, i.e. rows recursively referring other rows of the same table. Oracle offers a CONNECT BY clause, Microsoft and IBM DB2 offer recursive joins via Common Table Expressions, other solutions are database functions which use recursion and return a row set, as possible in PostgreSQL with PL/PgSQL. Similar problems exist with Graph (data structure) traversal. A general graph solution could perhaps be used for trees also, since trees are a subset of graphs.
• The grammar of SQL is perhaps unnecessarily complex, borrowing a COBOL-like keyword approach, when a function-influenced syntax could result in more re-use of fewer grammar and syntax rules. This is perhaps due to IBM's early goal of making the language more English-like so that it is more approachable to those without a math or programming background. (Predecessors to SQL were more math-like.)

In mathematics, the Cartesian product is a direct product of sets. ... A hierarchical database is a kind of database management system that links records together in a tree data structure such that each record type has only one owner, e. ... A common method of simplification is to divide a problem into subproblems of the same type. ... DB2 is one of IBMs lines of relational database management system (or, as IBM now calls it, data server) software products within IBMs broader Information Management Software line. ... PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) is a procedural language built into the PostgreSQL RDBMS. It closely resembles Oracles PL/SQL language. ... In computer science, a graph is a kind of data structure, specifically an abstract data type (ADT), that consists of a set of nodes and a set of edges that establish relationships (connections) between the nodes. ... COBOL (pronounced //) is a Third-generation programming language, and one of the oldest programming languages still in active use. ...

Reasons for lack of portability

Popular implementations of SQL commonly omit support for basic features of Standard SQL, such as the DATE or TIME data types, preferring variations of their own. 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 databases do not implement the entire standard.
• The standard does not specify database behavior in several important areas (e.g. indexes), leaving it up to implementations of the database 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 areas of 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 compatibility.

A database index is a data structure that improves the speed of operations in a table. ... In technology, especially computing (irrespective of platform), a product is said to be backward compatible (or upward compatible) when it is able to take the place of an older product, by interoperating with other products that were designed for the older product. ...

Alternatives to SQL

A distinction should be made between alternatives to relational query languages and alternatives to SQL. The list below are proposed alternatives to SQL, but are still (nominally) relational. See navigational database for alternatives to relational: Navigational databases incorporate both the network model and hierarchical model of database interfaces. ...

• IBM Business System 12 (IBM BS12)
• Tutorial D
• Hibernate Query Language (HQL) - A Java-based tool that uses modified SQL
• EJB-QL (Enterprise Java Bean Query Language/Java Persistence Query Language) - An object-based query language, which allows objects to be retrieved using a syntax similar to SQL. It is used within the Java Persistence framework, and formerly within the J2EE/JEE Enterprise Java Bean framework with Entity Beans.^[12]
• Quel introduced in 1974 by the U.C. Berkeley Ingres project.
• Object Query Language
• Datalog
• .QL - object-oriented Datalog
• LINQ
• InfinityDB (infinitydb.com) Entity-Attribute-Value embeddable java dbms
• Languages used by fuzzy databases, such as SQLf and FSQL.
• 4D Query Language (4D QL)
• Internet Business Logic, a kind of Wiki for writing and running knowledge. It combines three kinds of semantics: English, Logic, and Data. It provides automatic generation and execution of complex networked SQL, with English explanations of the results. Can also be used as an SOA endpoint on the Internet. Shared use is free.[1]

(Note to Wikipedia editors: Although this represents over a decade of serious R&D, and is made available free on the net, we find that some editors delete the entry. If you are about to do that, please contact adriandwalker@gmail.com to discuss first. Thank you.) Business System 12, or simply BS12, was one of was the first fully relational database management systems, designed and implemented by IBMs UK Bureau Service subsidiary. ... Tutorial D is an example of a truly relational database query language, developed by Christopher J. Date and Hugh Darwen and described in The Third Manifesto. ... Hibernate is an object-relational mapping (ORM) solution for the Java language: it provides an easy to use framework for mapping an object-oriented domain model to a traditional relational database. ... JavaBeans are software components written in the Java programming language. ... Simple EJB Architecture Enterprise Java Beans (EJB) is a managed, server-sided component for modular construction of enterprise applications. ... This article or section does not adequately cite its references or sources. ... Object Query Language (OQL) is a query language standard for object-oriented databases modelled after SQL. OQL was developed by the Object Data Management Group (ODMG). ... Datalog is a query and rule language for deductive databases that syntactically is a subset of Prolog. ... .QL [ˈdotˈkjuˈɛl] is an object-oriented query language used to retrieve data from relational database management systems. ... Language intergrated query (LINQ) is a Microsoft project that aims to add a native querying syntax to C# and VB.Net. ... 4th Dimension (or 4D) is a relational database management system and integrated IDE developed by Laurent Ribardière in 1984. ...

See also

• D (data language specification)
• D4 (programming language) (an implementation of D)
• Comparison of relational database management systems
• Comparison of object-relational database management systems
• List of relational database management systems
• List of object-relational database management systems
• List of hierarchical database management systems
• MUMPS

D is a set of requirements proposed by Christopher J. Date and Hugh Darwen in The Third Manifesto for what they believe a relational language ought to be like; D is not a language itself. ... D4 is a computer language used in Dataphor, a truly Relational Database Management System. ... The following tables compare general and technical information for a number of relational database management systems. ... This article or section is not written in the formal tone expected of an encyclopedia article. ... See DBMS for a shorter list of “typical”, representative database management systems. ... The following is a list of object-relational database management systems. ... In a hierarchical data model, data is organized into a tree-like structure in such a way that it cannot have too many relationships. ...

References

Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... is the 161st day of the year (162nd in leap years) in the Gregorian calendar. ... Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... is the 161st day of the year (162nd in leap years) in the Gregorian calendar. ... Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... June 9 is the 160th day of the year in the Gregorian calendar (161st in leap years), with 205 days remaining. ... Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... June 9 is the 160th day of the year in the Gregorian calendar (161st in leap years), with 205 days remaining. ... This article is about the city in California. ... Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... June 9 is the 160th day of the year in the Gregorian calendar (161st in leap years), with 205 days remaining. ... Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... is the 242nd day of the year (243rd in leap years) in the Gregorian calendar. ... Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... June 9 is the 160th day of the year in the Gregorian calendar (161st in leap years), with 205 days remaining. ... Year 2007 (MMVII) is the current year, a common year starting on Monday of the Gregorian calendar and the AD/CE era in the 21st century. ... is the 168th day of the year (169th in leap years) in the Gregorian calendar. ... Ann Arbor redirects here. ...

External links

Wikibooks has a book on the topic of

SQL

Wikibooks has a book on the topic of

SQL dialects reference

• The 1995 SQL Reunion: People, Projects, and Politics (early history of SQL)
• SQL lessons from W3Schools