2012-01-10

Structure Query Language




SQL

This article is about the database language. For the airport with IATA code SQL, see San Carlos Airport.
SQL
Paradigm(s) Multi-paradigm
Appeared in 1974
Designed by Donald D. Chamberlin
Raymond F. Boyce
Developer ISO/IEC
Stable release SQL:2008 (2008)
Typing discipline Static, strong
Major implementations Many
Dialects SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008
Influenced by Datalog
Influenced , CQL, LINQ, Windows PowerShell
OS Cross-platform
Usual filename extensions .sql
Website "ISO/IEC 9075-1:2008: Information technology – Database languages – SQL – Part 1: Framework (SQL/Framework)". http://www.iso.org/iso/catalogue_detail.htm?csnumber=45498
SQL

Structured Query Language
Filename extension .sql
Internet media type application/x-sql
Developed by ISO/IEC
Initial release 1986
Latest release SQL:2008 / 2008
Type of format Database
Standard(s) ISO/IEC 9075
Open format? Yes
Website

SQL (officially play /ˈɛs kjuː ˈɛl/, often play /ˌsiːkwəl/; often referred to as Structured Query Language) is a programming language designed for managing data in relational database management systems (RDBMS).

Originally based upon relational algebra and tuple relational calculus, its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

SQL was one of the first commercial languages for Edgar F. Codd's relational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks". Despite not adhering to the relational model as described by Codd, it became the most widely used database language. Though often described as, and to a great extent is a declarative language, SQL also includes procedural elements. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. Since then the standard has been enhanced several times with added features. However, issues of SQL code portability between major RDBMS products still exist due to lack of full compliance with, or different interpretations of the standard. Among the reasons mentioned are the large size, and incomplete specification of the standard, as well as vendor lock-in.

History

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.

The first Relational Database Management System (RDBMS) was RDMS, developed at MIT in the early 1970s, soon followed by Ingres, developed in 1974 at U.C. Berkeley. Ingres implemented a query language known as QUEL, which was later supplanted in the marketplace by SQL.

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. Oracle V2 beat IBM's August release of the System/38 RDBMS to market by a few weeks.

After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.


Queries

The most common operation in SQL is the query, which is performed with the declarative SELECT statement. SELECT retrieves data from one or more tables, or expressions. Standard SELECT statements have no persistent effects on the database. Some non-standard implementations of SELECT can have persistent effects, such as the SELECT INTO syntax that exists in some databases.

Queries allow the user to describe desired data, leaving the database management system (DBMS) responsible for planning, optimizing, and performing the physical operations necessary to produce that result as it chooses.

A query includes a list of columns to be included in the final result immediately following the SELECT keyword. An asterisk (" * ") can also be used to specify that the query should return all columns of the queried tables. SELECT is the most complex statement in SQL, with optional keywords and clauses that include:

 Title Authors ---------------------- ------- SQL Examples and Guide 4 The Joy of SQL 1 An Introduction to SQL 2 Pitfalls of SQL 1

Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Books table, the above query could be rewritten in the following form:

 SELECT title, COUNT(*) AS Authors FROM Book NATURAL JOIN Book_author GROUP BY title;

However, many vendors either do not support this approach, or require certain column naming conventions in order for natural joins to work effectively.

SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.

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

Null and three-valued logic (3VL)

The idea of Null was introduced into SQL to handle missing information in the relational model. The introduction of Null (or Unknown) along with True and False is the foundation of three-valued logic. Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. Therefore comparisons with Null can never result in either True or False but always in the third logical result.

SQL uses Null to handle missing information. It supports three-valued logic (3VL) and the rules governing SQL three-valued logic are shown below (p and q represent logical states). The word NULL is also a reserved keyword in SQL, used to identify the Null special marker.

Additionally, since SQL operators return Unknown when comparing anything with Null, SQL provides two Null-specific comparison predicates: IS NULL and IS NOT NULL test whether data is or is not Null.

Note that SQL returns only results for which the WHERE clause returns a value of True; i.e. it excludes results with values of False and also excludes those whose value is Unknown.

Data manipulation

The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data:

Transaction controls

Transactions, if available, wrap DML operations:

Data definition

The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE , ALTER , RENAME , DROP and TRUNCATE statements:

Data types

Each column in an SQL table declares the type(s) that column may contain. ANSI SQL includes the following data types.

Character strings

Bit strings

Numbers

Date and time

Data control

The Data Control Language (DCL) authorizes users and groups of users to access and manipulate data. Its two main statements are:

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 include:

Criticism

SQL is a declarative computer language intended for use with relational databases. Many of the original SQL features were inspired by, but violated the semantics of the relational model and its tuple calculus realization. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto. Therefore, it cannot be considered relational in any significant sense, but is still widely called relational due to differentiation to other, pre-relational database languages which never intended to implement the relational model; due to its historical origin; and due to the use of the "relational" term by product vendors.

Other criticisms of SQL include:

Cross-vendor portability

Popular implementations of SQL commonly omit support for basic features of Standard SQL, such as the DATE or TIME data types. The most obvious such examples, and incidentally the most popular commercial, proprietary SQL DBMSs, are Oracle (whose DATE behaves as DATETIME , and lacks a TIME type) and the MS SQL Server (before the 2008 version). As a result, SQL code can rarely be ported between database systems without modifications.

There are several reasons for this lack of portability between database systems:

Standardization

SQL was adopted as a standard by the American National Standards Institute (ANSI) in 1986 as SQL-86 and the International Organization for Standardization (ISO) in 1987. The original SQL standard declared that the official pronunciation for SQL is "es queue el". Many English-speaking database professionals still use the nonstandard pronunciation /ˈsiːkwəl/ (like the word "sequel").

Until 1996, the National Institute of Standards and Technology (NIST) data management standards program certified SQL DBMS compliance with the SQL standard. Vendors now self-certify the compliance of their products.

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

Standard structure

The SQL standard is divided into several parts, including:

Alternatives

A distinction should be made between alternatives to relational query languages and alternatives to SQL. Below are proposed relational alternatives to SQL. See navigational database for alternatives to relational:

See also

Notes

References

External links
Keywords
Related
Concepts Objects Components

Retrieved from : http://en.wikipedia.org/w/index.php?title=SQL&oldid=464868927
Posted by at