A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model.... computer language designed for the retrieval and management of data
DATA
Debt, AIDS, Trade in Africa is a multinational Non-governmental organization founded in January 2002 in London by U2's Bono along with Robert Sargent Shriver III and activists from the Jubilee 2000 Drop the Debt campaign.... 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 commercial and open source databases currently in use are based on the relational model.... s (RDBMS), database schema
Database schema
The schema of a database system is its structure described in a formal language supported by the database management system . In a relational database, the schema defines the Table_, the Field in each table, and the relationships between fields and tables.... creation and modification, and database object access control management.
SQL is a programming language for querying and modifying data and managing databases. SQL was standardized first by the ANSI
American National Standards Institute
The American National Standards Institute or ANSI 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.... and later by the ISO
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.... . Most database management systems implement a majority of one of these standards and add their proprietary extensions.
A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model.... computer language designed for the retrieval and management of data
DATA
Debt, AIDS, Trade in Africa is a multinational Non-governmental organization founded in January 2002 in London by U2's Bono along with Robert Sargent Shriver III and activists from the Jubilee 2000 Drop the Debt campaign.... 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 commercial and open source databases currently in use are based on the relational model.... s (RDBMS), database schema
Database schema
The schema of a database system is its structure described in a formal language supported by the database management system . In a relational database, the schema defines the Table_, the Field in each table, and the relationships between fields and tables.... creation and modification, and database object access control management.
SQL is a programming language for querying and modifying data and managing databases. SQL was standardized first by the ANSI
American National Standards Institute
The American National Standards Institute or ANSI 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.... and later by the ISO
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.... . Most database management systems implement a majority of one of these standards and add their proprietary extensions. SQL allows the retrieval, insertion, updating, and deletion of data. A database management system also includes management and administrative functions. Most – if not all – implementations also include a command-line interface (SQL/CLI) that allows for the entry and execution of the language commands, as opposed to only providing an application programming interface
Application programming interface
An application programming interface is a set of subroutine, data structures, class and/or Protocol provided by library and/or operating system Service s in order to support the building of applications.... (API) intended for access from a graphical user interface
Graphical user interface
A graphical user interface is a type of user interface which allows people to human-computer interaction such as computers; hand-held devices such as MP3 Players, Portable Media Players or Gaming devices; household appliances and office equipment.... (GUI).
International Business Machines Corporation, abbreviated IBM and nicknamed "Big Blue" , is a multinational corporation computer technology and consulting corporation headquartered in Armonk, New York, New York, United States.... by Donald C. Messerly and Raymond F. Boyce
Raymond F. Boyce
Raymond 'Ray' Boyce was a 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.... 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
System R
IBM System R is a database system built as a research project at IBM San Jose Research in the 1970s. System R was a seminal project: it was the first implementation of SQL , which has since become the standard relational data query language.... . IBM patent
Patent
A patent is a set of exclusive rights granted by a state to an inventor or his assignee for a term of patent in exchange for a disclosure of an invention.... ed their version of SQL in 1985, while the SQL language was not formally standard
Standardization
Standardization is the process of developing and agreeing upon Standard . A standard is a document that establishes uniform engineering or technical specifications, criteria, methods, processes, or practices.... ized until 1986 by the American National Standards Institute
American National Standards Institute
The American National Standards Institute or ANSI 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.... (ANSI) as SQL-86. Subsequent versions of the SQL standard have been released by ANSI and as 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.... (ISO) standards.
In computer science, declarative programming is a programming paradigm that expresses the logic of a computation without describing its control flow.... query and data manipulation language, variations of SQL have been created by SQL database management system (DBMS)
Database management system
A database management system is computer software that manages databases. DBMSes may use any of a variety of database models, such as the network model or relational model.... vendors that add procedural constructs, flow-of-control
Control flow
In computer science control flow refers to the order in which the individual statement , Instruction or function calls of an imperative programming or functional programming computer program are execution or evaluated.... 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.
Common criticisms of SQL include a perceived lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)
Null (SQL)
Null is a special marker used in SQL to indicate that a data value does not exist in the database. Introduced by the creator of the Relational model database model, Edgar F.... ), and unnecessarily complex and occasionally ambiguous language grammar and semantics.
History
During the 1970s, a group at IBM San Jose Research Laboratory developed the System R
System R
IBM System R is a database system built as a research project at IBM San Jose Research in the 1970s. System R was a seminal project: it was the first implementation of SQL , which has since become the standard relational data query language.... relational database management system, based on the model introduced by Edgar F. Codd
Edgar F. Codd
Edgar Frank "Ted" Codd was a United Kingdom computer science who, while working for International Business Machines, invented the relational model for database management, the theoretical basis for relational databases.... in his influential paper, "A Relational Model of Data for Large Shared Data Banks". 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. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark
TradeMark
TradeMark is a tall, primarily residential, skyscraper in Charlotte, North Carolina. It was completed in 2007 and has 28 floors. There are 200 hundred residential units.... of the UK-based
United Kingdom
The United Kingdom of Great Britain and Northern Ireland, commonly known as the United Kingdom , the UK or Britain,is a sovereign state located off the northwestern coast of continental Europe.... Hawker Siddeley aircraft company.
The first non-commercial non-SQL RDBMS, Ingres, was developed in 1974 at U.C. Berkeley
University of California, Berkeley
The University of California, Berkeley is a public university research university located in Berkeley, California, California, United States. The oldest of the ten major campuses affiliated with the University of California, Berkeley offers some 300 undergraduate and graduate degree programs in a wide range of disciplines.... . 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 Edgar F.... , which was later supplanted in the marketplace by SQL.
Oracle Corporation specializes in developing and marketing enterprise software products ? particularly database management systems. Through organic growth and a number of high-profile acquisitions, Oracle enlarged its share of the software market.... ) 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 navy of the United States Armed Forces. It is one of the seven uniformed services of the United States. The U.S. Navy currently has approximately 331,682 personnel on active duty as of 31 December 2008 and 124,000 in the United States Navy Reserve.... , Central Intelligence Agency
Central Intelligence Agency
The Central Intelligence Agency is a civilian intelligence agency of the Federal government of the United States. It is the successor of the Office of Strategic Services formed during World War II to coordinate espionage activities between the branches of the US military services.... , and other U.S. government
Federal government of the United States
The Federal Government of the United States is the central current reigning United States governmental body, established by the United States Constitution.... agencies. In the summer of 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle
Oracle database
The Oracle Database consists of a relational database management system produced and marketed by Oracle Corporation. , Oracle had become a major presence in database computing.... 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 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 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 computer computers of a DBMS built around the SQL language.SQL/DS is an software implementation of Dr.... , and DB2
IBM DB2
DB2 is one of IBM's families of relational database management system software products within IBM's broader IBM Information Management Software line.... , which were commercially available in 1979, 1981, and 1983, respectively.
Standardization
SQL was adopted as a standard by ANSI in 1986 and ISO in 1987. In the original SQL standard, ANSI declared that the official pronunciation for SQL is "es queue el". However, many English-speaking database professionals still use the nonstandard pronunciation (like the word "sequel"). SEQUEL was an earlier IBM database language
Query language
Query languages are computer languages used to make query into databases and information systems.Broadly, query languages can be classified according to whether they are database query languages or information retrieval query languages.... , a predecessor to the SQL language.
The National Institute of Standards and Technology , known between 1901 and 1988 as the National Bureau of Standards , is a measurement standards laboratory which is a non-regulatory agency of the United States Department of Commerce.... 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.
The SQL standard has gone through a number of revisions, as shown below:
Federal Information Processing Standards are publicly announced Standardizations developed by the United States Federal government for use by all non-military government agencies and by government contractors.... 127-1
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.
A database trigger is procedural code that is automatically executed in response to certain events on a particular Table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.... , support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features.
SQL:2008 is the sixth revision of the SQL database query language....
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 provides facilities that permit applications to integrate into their SQL code the use of XQuery
XQuery
XQuery is a query language that is designed to query collections of XML data. It is semantic similarity to SQL.XQuery 1.0 was developed by the XML Query working group of the W3C.... , the XML Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents.
2008
SQL:2008
Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers. Adds the TRUNCATE statement.
The SQL standard is not freely available, but it may be purchased from ISO or ANSI. A draft of SQL:2008 is freely available as a zip archive, however. The zip archive contains a number of PDF
Portable Document Format
Portable Document Format is a file format created by Adobe Systems in 1993 for document exchange. PDF is used for representing two-dimensional documents in a manner independent of the application software, hardware, and operating system.... files that define the parts of the SQL:2008 specification.
Scope and extensions
Procedural extensions
SQL is designed for a specific purpose: to query data
DATA
Debt, AIDS, Trade in Africa is a multinational Non-governmental organization founded in January 2002 in London by U2's Bono along with Robert Sargent Shriver III and activists from the Jubilee 2000 Drop the Debt campaign.... contained in a relational database
Relational database
A relational database is a database that groups data using common attributes found in the data set. The resulting "clumps" of organized data are much easier for people to understand.... . SQL is a set
Set (computer science)
In computer science, a set is a collection of certain values, without any particular Canonical order, and no repeated values. It corresponds with a finite set in mathematics.... -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.... query language, not an imperative language
Imperative programming
In computer science, imperative programming is a programming paradigm that describes computation in terms of statement s that change a program state .... such as C
C (programming language)
C is a general-purpose computer programming language originally developed in 1972 by Dennis Ritchie at the Bell Telephone Laboratories to implement 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 are:
SQL/PSM stands for SQL/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 programming in addition to the querying commands of SQL....
InterBase is a relational database management system currently developed and marketed by CodeGear. 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 relational database management system that runs on Linux, Microsoft Windows, and a variety of Unix platforms. The database fork from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten....
Microsoft Corporation is a multinational corporation computer technology corporation that develops, manufactures, licenses, and supports a wide range of computer software products for computing devices.... / Sybase
Sybase
Sybase Inc. is an enterprise software and services company exclusively focused on managing and mobilizing information....
MySQL is a relational database management system which has more than 11 million installations. The program runs as a server providing multi-user access to a number of databases....
SQL/PSM stands for SQL/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 programming in addition to the querying commands of SQL....
Oracle Corporation specializes in developing and marketing enterprise software products ? particularly database management systems. Through organic growth and a number of high-profile acquisitions, Oracle enlarged its share of the software market....
PL/SQL is Oracle Corporation's proprietary procedural extension to the SQL database programming language, used in the Oracle database. Some other SQL database management systems offer similar extensions to the SQL language....
Ada is a structured programming, statically typed, Imperative programming, and Object-oriented programming high-level language computer programming programming language, extended from Pascal and other languages.... )
PostgreSQL is an object-relational database management system . It is released under a BSD licenses and is thus free software. As with many other open-source programs, PostgreSQL is not controlled by any single company, but has a global community of developers and companies to develop it....
PL/pgSQL is a procedural language supported by the PostgreSQL RDBMS. It closely resembles Oracle_database PL/SQL language.PL/pgSQL, as a fully featured programming language, allows much more procedural control than SQL, including the ability to use loops and other control structures....
Procedural Language/PostgreSQL Structured Query Language (based on Oracle PL/SQL)
PostgreSQL is an object-relational database management system . It is released under a BSD licenses and is thus free software. As with many other open-source programs, PostgreSQL is not controlled by any single company, but has a global community of developers and companies to develop it....
In addition to the standard SQL/PSM extensions and proprietary SQL extensions, procedural and object-oriented
Object-oriented programming language
An object-oriented programming language is one that allows or encourages, to some degree, object-oriented programming techniques such as Information hiding, Inheritance , module , and Polymorphism .... 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.... 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 .... 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
Perl
In computer programming, Perl is a high-level programming language, List of programming languages by category, Interpreter , dynamic programming language.... , Python
Python (programming language)
Python is a general-purpose high-level programming language. Its design philosophy emphasizes code readability. Python's core syntax and semantics are Minimalism , while the 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 quickly gained wide acceptance on its own and is generally thought to be easy to learn, but powerful in competent hands.... , and C.
Standard structure
The SQL standard is divided into several parts, including:
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/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 Connectivity
Open Database Connectivity
In computing, Open Database Connectivity provides a standard software application programming interface method for using database management systems .... is a well-known superset of SQL/CLI. This part of the standard consists solely of mandatory features.
SQL/PSM stands for SQL/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 programming in addition to the querying commands of SQL.... , 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/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.... , 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/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 .... , or Object Language Bindings, part is defined by ISO/IEC 9075, Part 10. SQL/OLB defines the syntax and symantics of SQLJ
SQLJ
SQLJ is an International Organization for Standardization standard for embedding SQL statements in Java programs.Unlike JDBC, SQLJ is not an Application programming interface but 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/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 database 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/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.... , 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/XML, or XML-Related Specifications, extension to the SQL standard is defined by ISO/IEC 9075-14:2003 see... , 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.
Language elements
The SQL language is sub-divided into several language elements, including:
Clauses, which are in some cases optional, constituent components of statements and queries.
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 , object composition, etc.... values or tables
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical column and horizontal row .... consisting of columns
Column (database)
In the context of a relational database Table , a column is a set of data values of a particular simple datatype, one for each Row of the table.... and rows
Row (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 .... of data.
A ternary, three-valued or trivalent logic is any of several multi-valued logic systems in which there are three truth values indicating true, false and some third value.... Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow.
Queries which retrieve data based on specific criteria.
Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
A semicolon is a conventional punctuation mark with several uses, mainly for pauses in sentences. The Italy printer Aldus Manutius the Elder established the practice of using the semicolon mark to separate words of opposed meaning, and to indicate interdependent statements.... (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
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 databases is the query, which is performed with the declarative SELECT
Select (SQL)
The SQL SELECT statement returns a result set of records from one or more tables.It retrieves zero or more rows from one or more base tables, temporary tables, or views in a database.... keyword. SELECT retrieves data from a specified table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical column and horizontal row .... , multiple related tables in a database or the result of an expression. While often grouped with Data Manipulation Language (DML)
Data Manipulation Language
Data Manipulation Language is a family of computer languages used by computer programs database users to retrieve, insert, delete and update data in a database.... 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.
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)
Database management system
A database management system is computer software that manages databases. DBMSes may use any of a variety of database models, such as the network model or relational model.... to plan
Query plan
A query plan is a 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.... , optimize
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 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:
FROM is an SQL reserved word.A FROM clause in SQL specifies where a SQL Data Manipulation Language statement should pull rows from.... clause which indicates the source table or tables from which the data is to be retrieved. The FROM clause can include optional JOIN
Join (SQL)
An SQL JOIN clause combines records from two table s 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.... clauses to join related tables to one another based on user-specified criteria.
A WHERE clause in SQL specifies that a SQL Data Manipulation Language statement should only affect rows that meet a specified criteria. The criteria is expressed in form of predicates.... 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.
A GROUP BY statement in SQL specifies that a SQL Select statement returns a list that is grouped by one or more columns, usually in order to apply some sort of aggregate function to certain columns.... 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.
A HAVING statement in SQL specifies that a SQL Select statement should only return rows where aggregate values meet the specified conditions.... 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.
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.... 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 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 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 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 3
The Joy of SQL 1
How to use Wikipedia 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:
SELECTtitle,
count(*) AS Authors
FROM Book
NATURAL JOIN Book_author
GROUP BY title
However, many vendors either do not support this approach, or it requires certain column naming conventions. Thus, it is less common in practice.
In relational algebra, a projection is a unary operation written as where is a set of attribute names. The result of such projection is defined as the Set obtained when the components of the tuple are restricted to the set ? it discards the other attributes.... when the user is looking for calculated values and not just the verbatim data stored in primitive data types
Primitive type
In computer science, primitive type can refer to either of the following concepts:* a basic type is a data type provided by a programming language as a basic building block.... , 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.
SELECT isbn,
title,
price, price * 0.06 AS sales_tax
FROM Book
WHEREprice > 100.00
ORDER BY title
Some modern day SQL queries may include extra WHERE statements that are conditional to each other. They may look like this example:
SELECT isbn, title, price, date
FROMBook
WHERE price > 100.00
AND date = '2004-04-16'
OR
date = '2005-04-16'
ORDER BY title
In predicate logic, universal quantification formalizes the notion that something is true for everything, or every relevant thing.The resulting statement is a universally quantified statement, and we have universally quantified over the predicate.... 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. In laymen's terms, it simply refers to something.... .
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:
An SQL INSERT statement adds one or more records to any single table in a relational database.... is used to add rows (formally tuple
Tuple
In mathematics, a tuple is a sequence of a specific number of values, called the components of the tuple. These components can be any kind of mathematical objects, where each component of a tuple is a value of a specified type.... s) to an existing table, e.g.,:
INSERT INTO My_table (field1, field2, field3) VALUES ('test', 'N', NULL);
A SQL UPDATE statement that 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 .... is used to modify the values of a set of existing table rows, e.g.,:
UPDATE My_table SET field1 = 'updated value' WHERE field2 = 'N';
An SQL 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.... removes zero or more existing rows from a table, e.g.,:
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.... 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
Upsert
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:
START TRANSACTION (or BEGIN WORK, or BEGIN TRANSACTION, depending on SQL dialect) can be used to mark the start of a database transaction
Database transaction
A database 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.
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.
Once the COMMIT statement has been executed, the changes cannot be rolled back. In other words, its meaningless to have ROLLBACK executed after COMMIT statement and vice versa.
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 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;
A Data Definition Language is a computer language for defining data structure. The term was first introduced in relation to the Codasyl database model, where the schema of the database was written in a Data Definition Language describing the records, fields, and "sets" making up the user Data Model.... (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:
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.
In SQL, the TRUNCATE TABLE statement quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.... deletes all data from a table in a very fast way. It usually implies a subsequent COMMIT operation.
ALTER statement permits the user to modify an existing object in various ways -- for example, adding a column to an existing table.
Example:
CREATE TABLE My_table
(
my_field1 INT,
my_field2 VARCHAR(50),
my_field3 DATE NOT NULL,
PRIMARY KEY (my_field1, my_field2)
);
A Data Control Language is a computer language and a subset of SQL, used to control access to data in a database.Examples of DCL commands include:... (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:
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;
REVOKE SELECT, UPDATE ON My_table FROM some_user, another_user;
Other
The ISO-standard SQL specifies double dash, --, as a single line comment
Comment (computer programming)
In computer programming, a comment is a programming language construct used to embed programmer-readable annotations in the source code of a computer program.... identifier. Example:
SELECT * FROM Inventory WHERE category = 2 -- Category 2 is electronics
Additionally, the database system may allow for C
C (programming language)
C is a general-purpose computer programming language originally developed in 1972 by Dennis Ritchie at the Bell Telephone Laboratories to implement the Unix operating system.... -style /* ... */ multi-line comments, as well.
Some database systems allow user-defined functions and stored procedure
Stored procedure
A stored procedure is a subroutine available to applications accessing a relational database database management system. Stored procedures are actually stored in the database data dictionary.... s (collectively called user defined routines). Some allow control of the query's execution with hint
Hint (SQL)
In various SQL implementations, a hint is an addition to the SQL standard that instructs the database engine on how to execute the query. For example, a hint may tell the engine to use as little memory as possible , or to use or not use an index even if the query optimizer would decide otherwise.... s.
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 violated, the relational model
Relational model
The relational model for database management is a database model based on first-order logic, first formulated and proposed in 1969 by Edgar F. Codd.... for database management and its tuple calculus
Tuple relational calculus
The tuple calculus is a Calculus that was introduced by Edgar F. Codd as part of the relational model in order to give 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 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.
In mathematics, the Cartesian product is a direct product of sets. The Cartesian product is named after Ren? Descartes, whose formulation of analytic geometry gave rise to this concept.... 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. SQL 1992 introduced the CROSS JOIN keyword 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.
It is also possible to misconstruct a WHERE on an update or delete, thereby affecting more rows in a table than desired.
The grammar of SQL is perhaps unnecessarily complex, borrowing a COBOL
COBOL
COBOL is one of the oldest programming languages still in active use. 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. 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 mathematical or programming background. (Predecessors to SQL were more mathematical.)
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
Index (database)
A database index is a data structure that improves the speed of operations on a Table . Indexes can be created using one or more column , providing the basis for both rapid random look ups and efficient access of ordered records.... , file storage...), 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
Backward compatibility
In technology, for example in telecommunications and computing, a device or technology is said to be backwards compatible if it allows input generated by older devices.... .
Vendors of closed-source software often desire to create incompatibility with other products, as it provides a strong incentive for their existing customers to remain loyal (see 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 Product s and Service , unable to use another vendor without substantial switching barriers.... ).
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
Navigational 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 ['dot'kju'?l] 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 .... - object-oriented Datalog
4th Dimension is a relational database management system and integrated development environment developed by Laurent Ribardiere.The 4D product line has since expanded to an SQL front-end, integrated compiler and several productivity plug-ins and interfaces.... (4D QL)
Datalog is a query language 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 1978 when Herv? Gallaire and Jack Minker organized a workshop on logic and databases....
HQL is a computer language designed for the retrieval and management of data in Hibernate . It is fully object oriented.References ... (HQL) - A Java-based tool that uses modified SQL
ISBL is the relational algebra notation that was invented for PRTV, one of the earliest database management systems to implement Edgar F. Codd relational model of data....
Java Persistence Query Language (JPQL) - The query language used by the Java Persistence API in Java EE5
Query 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.... created by Moshè Zloof, IBM 1977
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 Edgar F.... introduced in 1974 by the U.C. Berkeley Ingres project.
D is a set of requirements proposed by Christopher J. Date and Hugh Darwen in their book The Third Manifesto for what they believe a relational database query language ought to be like; D is not a language itself....
XQuery is a query language that is designed to query collections of XML data. It is semantic similarity to SQL.XQuery 1.0 was developed by the XML Query working group of the W3C....
The aim of this list of programming languages is to include all notable programming languages in existence, both those in current use and historical ones, in alphabetical order....
Comparison of object-relational database management systems
The following Database Management Systems have at least some object-relational database management system They vary widely in their completeness and the approaches taken....
Comparison of relational database management systems
The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information....
D is a set of requirements proposed by Christopher J. Date and Hugh Darwen in their book The Third Manifesto for what they believe a relational database query language ought to be like; D is not a language itself....
A hierarchical data model is a data model in which the data is organized into a Tree data structure-like structure. The structure allows repeating information using parent/child relationships: each parent can have many children but each child only has one parent....
MUMPS , or alternatively M, is a programming language created in the late 1960s, originally for use in the Health care. It was designed for the production of multi-user database-driven applications....
External links
: transcript of a reunion meeting devoted to the personal history of relational databases and SQL
The 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.
The 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 College
Harvey Mudd College
Harvey Mudd College is a highly selective private college of science, engineering, and mathematics, located in Claremont, California. It is one of the institutions of the contiguous Claremont Colleges.... and Stanford University
Stanford University
Leland Stanford Junior University, commonly referred to as Stanford University or Stanford, is a private university research university located in Stanford, California, California, United States.... , and his work on relational database technology. Chamberlin was a member of the System R research team and, with Raymond F. Boyce
Raymond F. Boyce
Raymond 'Ray' Boyce was a 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.... , developed the SQL
SQL
SQL is a database 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.... database language. Chamberlin also briefly discusses his more recent research on XML query languages.
