SQL

SQL is the most popular computer language used to create, modify and retrieve Data from relational database management systems. The language has evolved beyond its original purpose to support object-relational database management systems. It is an American National Standards Institute/International Organization for Standardization standard.

=History=

A seminal ).

During the 1970s, a group at IBM s San Jose research center developed a database system System R based upon Codd s model. Structured English Query Language ( SEQUEL ) was designed to manipulate and retrieve data stored in System R. The acronym SEQUEL was later condensed to SQL due to a trademark dispute (the word SEQUEL was held as a trade mark by the Hawker-Siddeley aircraft company of the UK). It should be noted that although SQL was influenced by Dr. Codd s work, it was not designed by Dr. Codd himself; the SEQUEL language design was due to Donald D. Chamberlin and Raymond F. Boyce at IBM., and their concepts were published to increase interest in SQL.

The first non-commercial non-SQL relational database was developed in 1974.(Ingres from U.C. Berkeley.)

In 1978, methodical testing commenced at customer test sites. Demonstrating both the usefulness and practicality of the system, this testing proved to be a success for IBM. As a result, IBM began to develop commercial products that implemented SQL based on their System R prototype, including the System/38 (announced in 1978 and commercially available in August 1979), SQL/DS (introduced in 1981), and DB2 (in 1983).[http://www-03.ibm.com/ibm/history/history/year_1978.html]

At the same time Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Chamberlin and Boyce and developed their own version of a RDBMS for the Navy, CIA and others; and in the summer of 1979, Relational Software, Inc. introduced Oracle V2 (Version2) for VAX computers, as the first commercially available implementation of SQL. Oracle is often incorrectly cited as beating IBM to market by two years, but in a great public relations coup, beat IBM s release of the System/38 by only a few weeks. Considerable public interest then developed; soon many other vendors developed versions, and Oracle s future was ensured.

It is often suggested that IBM was slow to develop SQL and relational products, possibly because it wasn t available initially on the mainframe and Unix environments, and that they were afraid it would cut into lucrative sales of their IMS database product, which used navigational database models instead of relational. But at the same time as Oracle was being developed, IBM was developing the System/38, which was intended to be the first relational database system, and was thought by some at the time, because of its advanced design and capabilities, that it might have become a possible replacement for the mainframe and Unix systems.

SQL was adopted as a standard by the ANSI (American National Standards Institute) in 1986 and International Organization for Standardization (International Organization for Standardization) in 1987. ANSI has declared that the official pronunciation for SQL is , although many English-speaking database professionals still pronounce it as sequel . Another widespread misconception is that SQL is an initialism that stands for Structured Query Language — this is not the case.

The SQL standard has gone through a number of revisions:

=Scope=

The SQL standard is not freely available. SQL:2003 may be purchased from [http://www.iso.org/ ISO] or [http://webstore.ansi.org/ ANSI]. A late draft is available as a [http://www.wiscorp.com/sql/sql_2003_standard.zip zip archive] from [http://www.wiscorp.com/ Whitemarsh Information Systems Corporation]. The zip archive contains a number of Portable Document Format files that define the parts of the SQL:2003 specification.

Although SQL is defined by both ANSI and ISO, there are many extensions to and variations on the version of the language defined by these standards bodies. Many of these extensions are of a proprietary nature, such as Oracle Corporation s PL SQL or Sybase, IBM s SQL PL(SQL Procedural Language) and Microsoft s Transact-SQL. It is also not uncommon for commercial implementations to omit support for basic features of the standard, such as the DATE or TIME data types, preferring some variant of their own. As a result, in contrast to ANSI C programming language or ANSI Fortran, which can usually be ported from platform to platform without major structural changes, SQL code can rarely be ported between database systems without major 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 standard to decide how to behave. *the SQL standard precisely specifies the syntax that a conformant 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. *some believe the lack of compatibility between database systems is intentional in order to ensure vendor lock-in.

SQL is designed for a specific, limited purpose — querying data contained in a relational database. As such, it is a Set, Declarative programming language computer language rather than an Imperative programming such as C or BASIC programming language which, being programming languages, are designed to solve a much broader set of problems. Language extensions such as PL SQL are designed to address this by turning SQL into a full-fledged programming language while maintaining the advantages of SQL. Another approach is to allow programming language code to be embedded in and interact with the database. For example, Oracle and others include Java programming language in the database, while PostgreSQL allows functions to be written in a wide variety of languages, including Perl, Tcl, and C programming language.

One joke about SQL is that SQL is neither structured, nor is it limited to queries, nor is it a language. This is founded on the notion that pure SQL is not a classic programming language since it is not Turing complete . On the other hand, however, it is a programming language because it has a grammar, syntax, and programmatic purpose and intent. The joke recalls Voltaire s remark that the Holy Roman Empire was neither holy, nor Roman, nor an empire.

SQL contrasts with the more powerful Fourth-generation programming language such as Focus software or SAS_System, however, in its relative functional simplicity and simpler command set. This greatly reduces the degree of difficulty involved in maintaining SQL source code, but it also makes programming such questions as Who had the top ten scores more difficult, leading to the development of procedural extensions, discussed #Origins. However, it also makes it possible for SQL source code to be produced (and optimized) by software, leading to the development of a number of natural language database query languages, as well as drag and drop database programming packages with object oriented interfaces. Often these allow the resultant SQL source code to be examined, for educational purposes, further enhancement, or to be used in a different environment.

=SQL keywords=

SQL keywords fall into several groups.

==Connection==

The first commands used are to connect to a database. A default database can also be selected.

CONNECT is used to select which database to connect to.

USE is used to select a default database.

Example: USE my_db CONNECT DATABASE

==Data retrieval==

The most frequently used operation in transactional databases is the data retrieval operation.

Select (SQL) is used to retrieve zero or more rows from one or more tables in a database. In most applications, SELECT is the most commonly used Data Manipulation Language command. In specifying a SELECT query, the user specifies a description of the desired result set, but they do not specify what physical operations must be executed to produce that result set. Translating the query into an efficient query plan is left to the database system, more specifically to the query optimizer.

Commonly available keywords related to SELECT include:

FROM is used to indicate which tables the data is to be taken from, as well as how the tables Join (SQL) to each other.

WHERE is used to identify which rows to be retrieved, or applied to GROUP BY.

GROUP BY is used to combine rows with related values into elements of a smaller set of rows.

HAVING is used to identify which rows, following a GROUP BY, are to be retrieved.

ORDER BY is used to identify which columns are used to sort the resulting data.

Example: SELECT * FROM my_table WHERE id > 10;

==Data manipulation==

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

*Insert (SQL) is used to add zero or more rows (formally tuples) to an existing table. *Update (SQL) is used to modify the values of a set of existing table rows.

.

*Delete (SQL) deletes all data from a table (non-standard, but common SQL command). *Truncate (SQL) removes zero or more existing rows from a table.

Example: INSERT INTO my_table (field1, field2, field3) VALUES ( test , N , NULL); UPDATE my_table SET field1 = updated value WHERE field2 = N ; DELETE FROM my_table WHERE field2 = N ;

==Data transaction==

Transaction, if available, can be used to wrap around the DML operations.

Begin work (SQL) (or Start transaction (SQL), depending on SQL dialect) can be used to mark the start of a database transaction, which either completes completely or not at all.

Commit (SQL) causes all data changes in a transaction to be made permanent.

Rollback (SQL) 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: UPDATE inventory SET quantity = quantity - 3 WHERE item = pants ;

==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 and DROP commands.

Create (SQL) causes an object (a table, for example) to be created within the database.

Drop (SQL) causes an existing object within the database to be deleted, usually irretrievably.

Some database systems also have an Alter (SQL) command, which 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 UNSIGNED, 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). Data Control Language handles the authorisation 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 - authorises a user to perform an operation or a set of operations e.g. grant all privileges to user identified by passwd

REVOKE - removes or restricts the capability of a user to perform an operation or a set of operations.

Example: GRANT ALL ON my_db TO someone@localhost IDENTIFIED BY somepass

=Database systems using SQL=

*List of SQL database management systems *List of object-relational database management systems

=Criticisms of SQL=

Technically, SQL is a declarative computer language for use with relational databases . Theorists note that many of the original SQL features were inspired by, but in violation of, tuple calculus. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto.

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

*The language syntax is rather complex (sometimes called COBOL-like ). *It does not provide a standard way to split large commands into multiple smaller ones that reference each other by name (however some implementations allow for set-based functions to grant this functionality). This tends to result in run-on SQL sentences . *Implementations are inconsistent and, at times, incompatible between vendors. *It is at times too difficult a syntax for Database administrators (DataBase Administrators) to extend. *Over-reliance on Null (SQL)s , which some consider a flawed or over-used concept. *For larger statements, it is often difficult to factor repeated patterns and expressions into one or fewer places to avoid repetition and avoid having to make the same change to different places in a given statement.

Unexplained difference between value-to-column assignments in UPDATE and INSERT syntax.

=Alternatives to SQL=

A distinction should be made between alternatives to relational and alternatives to SQL. The list below are proposed alternatives to SQL, but are still (allegedly) relational. See navigational database for alternatives to relational.

*IBM BS12 *Tutorial D *[http://www.c2.com/cgi/wikiTqlRoadmap TQL Proposal (Not to be confused with Luca Cardelli s TQL)] *[http://www.hibernate.org/hib_docs/v3/reference/en/html/queryhql.html Hibernate Query Language (HQL) - A Java-based tool that uses modified SQL]

QUEL introduced in 1974 by the U.C. Berkeley Ingres project.

=External links=

*[http://www.mcjones.org/System_R/SQL_Reunion_95/ The 1995 SQL Reunion: People, Projects, and Politics (early history of SQL)]

==Tutorials==

*[http://www.bitesizeinc.net/sql.html BiteSize Inc. s Learn SQL tutorial] *[http://sqlzoo.net/ A Gentle Introduction to SQL at SQLzoo] *[http://www.sql-tutorial.net SQL Tutorial] *[http://aam.ugpl.de/sql_help/select SQL Help and Tutorial]

=References=

# Donald D. Chamberlin and Raymond F. Boyce, 1974. [http://portal.acm.org/citation.cfmid=811515 SEQUEL: A structured English query language] , International Conference on Management of Data, Proceedings of the 1974 ACM SIGFIDET (now SIGMOD) workshop on Data description, access and control, Ann Arbor, Michigan, pp. 249-264