SQL (Structured Query Language) (pronounced /ɛskjuːˈɛl / officially, although the unofficial pronunciation /ˈsiːkwəl/ is often used (see below)), is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management. A programming paradigm is a fundamental style of Computer programming. A multi-paradigm programming language is a Programming language that supports more than one Programming paradigm. Donald D Chamberlin is an american computer scientist who is best known as one of the principal designers of the original SQL language specification Raymond 'Ray' Boyce is a Computer scientist who was known for his research in Relational databases Boyce grew up in New York, and went to college A software developer is a person or organization concerned with facets of the software development process wider than design and coding a somewhat broader scope of International Business Machines Corporation abbreviated IBM and nicknamed "Big Blue", is a multinational Computer Technology A software release is the distribution whether public or private of an initial or new and upgraded 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 In Computer science, a type system defines how a Programming language classifies values and expressions into '''types''', how it can In Computer science and Computer programming, the term strong typing is used to describe those situations where Programming languages specify one or more Implementation is the realization of an application or execution of a Plan, idea Model, Design, Specification, standard, Algorithm See DBMS for a shorter list of “typical” representative database management systems SQL-92 was the third revision of the SQL Database Query language. SQL1999 is the fourth revision of the SQL Database Query language. SQL2003 is the fifth revision of the SQL Database Query language. SQL2006 is the sixth revision of the SQL Database Query language. Datalog is a query and rule language for Deductive databases that syntactically is a subset of Prolog. Windows PowerShell is an extensible command-line shell and associated Scripting language from Microsoft An operating system (commonly abbreviated OS and O/S) is the software component of a Computer system that is responsible for the management and coordination In computing cross-platform (also known as multi-platform) is a term used to refer to Computer software or computing methods and concepts that are implemented Debt AIDS Trade in Africa (or DATA) is a Multinational non-government organization founded in January 2002 in London by U2 's A Relational database management system (RDBMS is a Database management system (DBMS that is based on the Relational model as introduced by E ^[1][2]

SQL is a standard interactive and programming language for querying and modifying data and managing databases. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. The core of SQL is formed by a command language that allows the retrieval, insertion, updating, and deletion of data, and performing management and administrative functions. SQL also includes a Call Level Interface (SQL/CLI) for accessing and managing data and databases remotely. The Call Level Interface (CLI is a software standard defined in ISO / IEC 9075-32003

The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. International Business Machines Corporation abbreviated IBM and nicknamed "Big Blue", is a multinational Computer Technology Donald D Chamberlin is an american computer scientist who is best known as one of the principal designers of the original SQL language specification Raymond 'Ray' Boyce is a Computer scientist who was known for his research in Relational databases Boyce grew up in New York, and went to college This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM's original relational database product, System R. IBM System R is a Database system built as a research project at IBM San Jose Research (now IBM Almaden Research Center) in the 1970s The SQL language was later formally standardized by the American National Standards Institute (ANSI) in 1986. Standardization (or standardisation) is the process of developing and agreeing upon technical standards. Subsequent versions of the SQL standard have been released as International Organization for Standardization (ISO) standards.

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. In Computer science, Declarative programming is a Programming paradigm that attempts to minimize or eliminate side effects by describing what A database management system ( DBMS) is Computer software designed for the purpose of managing Databases DBMSes may use any of a variety of Data models 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)), and unnecessarily complex and occasionally ambiguous language grammar and semantics. Null is a special marker used in Structured Query Language (SQL to indicate that a data value does not exist in the database

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 Edgar F. Codd in his influential paper, A Relational Model of Data for Large Shared Data Banks. IBM System R is a Database system built as a research project at IBM San Jose Research (now IBM Almaden Research Center) in the 1970s Edgar Frank "Ted" Codd ( August 23, 1923 – April 18, 2003) was a British computer scientist who while working Academic publishing describes the subfield of Publishing which distributes academic Research and Scholarship. ^[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. A trademark or trade mark, represented by the symbols ™ and ®, or mark is a distinctive sign or indicator used by an individual The United Kingdom of Great Britain and Northern Ireland, commonly known as the United Kingdom, the UK or Britain,is a Sovereign state located Hawker Siddeley was a group of British manufacturing companies renowned for their aircraft production. ^[5]

The first non-commercial non-SQL RDBMS, Ingres, was developed in 1974 at the U.C. Berkeley. The University of California Berkeley (also referred to as Cal, Berkeley and UC Berkeley) is a major research university located in Berkeley Ingres implemented a query language known as QUEL, which was later supplanted in the marketplace by SQL. QUEL is a Relational database access language similar in most ways to SQL. ^[5]

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. Oracle Corporation ( specializes in developing and marketing Enterprise software products — particularly Database management systems In 2007 Oracle ranked A Relational database management system (RDBMS is a Database management system (DBMS that is based on the Relational model as introduced by E near as long as it used to be several months ago It has been actively summarized and split into sub-articles and there is a dynamic talk page discussion of all The federal government of the United States is the central United States Governmental body established by the United States Constitution. In the summer of 1979, Relational Software, Inc. Year 1979 ( MCMLXXIX) was a Common year starting on Monday (link displays the 1979 Gregorian calendar) introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. Oracle Database (commonly referred to as Oracle RDBMS or simply Oracle) is a Relational database management system (RDBMS produced and marketed by Name "VAX" was originally an Acronym for V irtual A ddress e' X' tension, both because the VAX was seen as a 32-bit Oracle V2 beat IBM's release of the System/38 RDBMS to market by a few weeks. The System/38 was a Midrange computer Server Platform manufactured and sold by the IBM Corporation

After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively. The System/38 was a Midrange computer Server Platform manufactured and sold by the IBM Corporation SQL/DS (Structured Query Language/Data System was IBM 's first commercial implementation for its mainframe Computers of a DBMS (Database Management DB2 is one of IBM 's families of Relational database management system (RDBMS (or as IBM now calls it data server software products within IBM's broader Information Year 1979 ( MCMLXXIX) was a Common year starting on Monday (link displays the 1979 Gregorian calendar) Year 1981 ( MCMLXXXI) was a Common year starting on Thursday (link displays the 1981 Year 1983 ( MCMLXXXIII) was a Common year starting on Saturday (link displays the 1983 Gregorian calendar) ^[6]

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". ^[7] However, many English-speaking database professionals still use the nonstandard^[8] pronunciation /ˈsiːkwəl/ (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. Year 1996 ( MCMXCVI) was a Leap year starting on Monday (link will display full 1996 Gregorian calendar) In 1996, however, the NIST data management standards program was dissolved, and vendors are now relied upon to self-certify their products for compliance. ^[9]

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 File format is a Data compression and archival format. The zip archive contains a number of PDF files that define the parts of the SQL:2003 specification.

Scope and extensions

Procedural extensions

SQL is designed for a specific purpose: to query data contained in a relational database. A relational database is a Database that groups data using common attributes found in the data set SQL is a set-based, declarative query language, not an imperative language such as C or BASIC. In Computer science, Declarative programming is a Programming paradigm that attempts to minimize or eliminate side effects by describing what In Computer science, imperative programming is a Programming paradigm that describes computation in terms of statements that change a program state tags please moot on the talk page first! --> In Computing, C is a general-purpose cross-platform block structured In Computer programming, BASIC (an Acronym for Beginner's All-purpose Symbolic Instruction Code) is a family of High-level programming languages However, there are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs. Procedural programming can sometimes be used as a synonym for Imperative programming (specifying the steps the program must take to reach the desired state but can also These are:

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. 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 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 Microsoft Corporation is an American multinational Computer technology Corporation, which rose to dominate the Home computer Sybase Inc ( is an enterprise software and services company exclusively focused on managing and mobilizing information Transact-SQL ( T-SQL) is Microsoft 's and Sybase 's proprietary extension to the SQL. MySQL is a Relational database management system (RDBMS which has more than 11 million installations Oracle Corporation ( specializes in developing and marketing Enterprise software products — particularly Database management systems In 2007 Oracle ranked PL/SQL (Procedural Language/Structured Query Language is Oracle Corporation 's proprietary procedural extension to the SQL database language, used PostgreSQL is an Object-relational database management system (ORDBMS PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language is a procedural language supported by the PostgreSQL RDBMS. PostgreSQL is an Object-relational database management system (ORDBMS An object-oriented Programming language (also called an OO language) is one that allows or encourages to some degree Object-oriented programming The SQL standard defines SQL/JRT extensions (SQL Routines and Types for the Java Programming Language) to support Java code in SQL databases. The SQL/JRT, or SQL Routines and Types for the Java Programming Language, extension to the SQL standard is defined by ISO /IEC 9075-132003 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. 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. Other database platforms, like MySQL and Postgres, allow functions to be written in a wide variety of languages including Perl, Python, Tcl, and C. NOTES FOR EDITORS "Perl" is not an acronym (read the "Name" section below Python is a general-purpose High-level programming language. Its design philosophy emphasizes programmer productivity and code readability Tcl (originally from "Tool Command Language" but nonetheless conventionally rendered as "Tcl" rather than "TCL" pronounced as " tickle "

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. The SQL/CLI, or Call-Level Interface, extension to the SQL standard is defined in ISO /IEC 9075-32003 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/MED, or Management of External Data, extension is defined by ISO/IEC 9075-9:2003. The SQL/MED, or Management of External Data, extension to the SQL standard is defined by ISO /IEC 9075-92003 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/OLB, or 'Object Language Bindings, extension is defined by ISO/IEC 9075-10:2003. The SQL/OLB, or Object Language Bindings, extension to the SQL standard is defined by ISO /IEC 9075-102003 SQL/OLB defines the syntax and symantics of SQLJ, which is SQL embedded in Java. SQLJ is an ISO standard (ISO/IEC 9075-10 for embedding SQL statements in Java programs The standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies various Java packages and their contained classes.

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

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. The SQL/XML, or XML-Related Specifications, extension to the SQL standard is defined by ISO /IEC 9075-142003 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/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:

• 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. 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 In Relational databases and Flat file databases a table is a set of data elements (values that is organized using a model of vertical columns (which are In the context of a Relational database table, a column is a set of Data values of a particular simple type, one for each row of the In the context of a Relational database, a row —also called a record or tuple —represents a single implicitly structured Data item in a
• 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. A ternary, three-valued or trivalent logic (sometimes abbreviated 3VL) is a term to describe any of several Multi-valued logic systems in which
• Clauses which are (in some cases optional) constituent components of statements and queries. ^[11]
• Whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability. In Computer science, whitespace is any single character or series of characters that represents horizontal or vertical space in Typography.
• SQL statements also include the semicolon (";") statement terminator. A semicolon (   ) is a conventional Punctuation mark with several usages Though not required on every platform, it is defined as a standard part of the SQL grammar.

Queries

The most common operation in SQL databases is the query, which is performed with the declarative SELECT keyword. A SQL SELECT statement returns a Result set of records from one or more tables SELECT retrieves data from a specified table, or multiple related tables, in a database. In Relational databases and Flat file databases a table is a set of data elements (values that is organized using a model of vertical columns (which are 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. 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 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. ^[12]

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. A database management system ( DBMS) is Computer software designed for the purpose of managing Databases DBMSes may use any of a variety of Data models A query plan (or query execution plan) is a set of steps used to access or modify information in a SQL Relational database management system. The query optimizer is the component of a Database management system that attempts to determine the most efficient way to execute a Query. 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:

• The FROM clause which indicates the source table or tables from which the data is to be retrieved. FROM is an SQL reserved word The FROM clause is used in conjunction with SQL statements and takes the following general form SQL-DML-Statement The FROM clause can include optional JOIN clauses to join related tables to one another based on user-specified criteria. A SQL JOIN clause combines records from two tables in a Relational database, resulting in a new temporary table sometimes called a "joined table"
• The WHERE clause includes a comparison predicate, which is used to restrict the number of rows returned by the query. A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML statement should only affect rows that meet a specified criteria 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. 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 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. A HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions 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). An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a Result set with the rows being sorted by the values 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
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:

SELECT title, 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.

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. Introduction In Relational algebra, a projection is a Unary operation written as \pi_{a_1. In Computer science, primitive types — as distinct from Composite types ' — are Data types provided by a Programming language as basic 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;

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 
FROM Book
WHERE price > 100. 00
AND (date = '16042004' OR date = '16042005')
ORDER BY title;

Universal quantification is not explicitly supported by sql, and must be worked out as a negated existential quantification. In Predicate logic, universal quantification is an attempt to formalize the notion that something (a Logical predicate) is true for everything, or every In Predicate logic, an existential quantification is the predication of a property or relation to at least one member of the domain ^[13][14][15]

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, for example:

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. An SQL INSERT statement adds one or more records to any single table in a Relational database. In Mathematics, a tuple is a Sequence (also known as an "ordered list" of values called the components of the tuple A SQL UPDATE statement that changes the data of one or more records in a table An SQL DELETE statement removes one or more records from a table A Relational database management system uses SQL MERGE (aka Upsert) statements to INSERT new records or 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". 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, which either completes completely or not at all. A database transaction is a unit of work performed against a Database management system or similar system that is treated in a coherent and reliable way independent of other A database transaction is a unit of work performed against a Database management system or similar system that is treated in a coherent and reliable way independent of other
• COMMIT causes all data changes in a transaction to be made permanent. In the context of Computer science and Data management, commit refers to the idea of making a set of tentative changes 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. In Database technologies a rollback is an operation which returns the database to some previous state

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;

Data definition

The second group of keywords is the Data Definition Language (DDL). A Data Definition Language ( DDL) is a Computer language for defining data structures. 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. A Data Definition Language ( DDL) is a Computer language for defining data structures.
• DROP causes an existing object within the database to be deleted, usually irretrievably. A Data Definition Language ( DDL) is a Computer language for defining data structures.
• TRUNCATE deletes all data from a table (non-standard, but common SQL statement). In SQL, the TRUNCATE statement removes all the data from a table
• ALTER statement permits the user to modify an existing object in various ways -- for example, adding a column to an existing table. A Data Definition Language ( DDL) is a Computer language for defining data structures.

Example:

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). A Data Control Language ( DCL) is a Computer language and a subset of SQL, used to control access to data in a database 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;

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). In Computer programming, a comment is a Programming language construct used to embed Information in the Source code of a computer program

Example:

SELECT * FROM Inventory -- Retrieve everything from inventory table

• Some SQL servers allow user-defined functions

Criticisms of SQL

Technically, SQL is a declarative computer language for use with "SQL databases". A User-Defined Function, or UDF, is a function provided by the user of a program or environment in a context where the usual assumption is that functions are built 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. The relational model for Database management is a Database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar The Tuple Calculus is a calculus that was introduced by Edgar F Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto. The Third Manifesto ( 1995) is Christopher J Date 's and Hugh Darwen 's proposal for future Relational database management systems that would avoid

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 square redirects here For Cartesian squares in Category theory, see Cartesian square (category theory. 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-like keyword approach, when a function-influenced syntax could result in more re-use of fewer grammar and syntax rules. COBOL (ˈkoʊbɒl is one of the oldest programming languages still in active use 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, file storage. A database index is a Data structure that improves the speed of operations on a database table. . . ), 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. In Technology, especially Computing (irrespective of platform a product is said to be backward compatible when it is able to take the place of an 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:

• IBM Business System 12 (IBM BS12)
• Tutorial D
• Hibernate Query Language (HQL) - A Java-based tool that uses modified SQL
• Quel introduced in 1974 by the U. Navigational databases are characterized by the fact that objects in the database are found primarily by following references from other objects Business System 12, or simply BS12, was one of the first fully Relational database management systems designed 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 HQL ( Hibernate Query Language) is a Computer language designed for the retrieval and management of Data in Hibernate (Java. QUEL is a Relational database access language similar in most ways to SQL. C. Berkeley Ingres project.
• Object Query Language
• Datalog
• .QL - object-oriented Datalog
• LINQ
• 4D Query Language (4D QL)
• QBE (Query By Example) created by Moshe Zloof, IBM 1977
• Aldat Relational Algebra and Domain algebra

See also

• Alphabetical list of programming languages
• 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
• Hierarchical model
• MUMPS

References

External Links

• Oral history interview with Donald D. Chamberlin Charles Babbage Institute In this oral history Chamberlin recounts his early life, his education at Harvey Mudd College and Stanford University, and his work on relational database technology. The Charles Babbage Institute (also titled the Center for the History of Information Technology) is a research center at the University of Minnesota specializing Chamberlin was a member of the System R research team and, with Raymond F. Boyce, developed the SQL database language. Raymond 'Ray' Boyce is a Computer scientist who was known for his research in Relational databases Boyce grew up in New York, and went to college Chamberlin also briefly discusses his more recent research on XML query languages.
• American National Standards Institute. X3H2 Records, 1978-1995 Charles Babbage Institute Collection documents the H2 committee’s development of the NDL and SQL standards. The Charles Babbage Institute (also titled the Center for the History of Information Technology) is a research center at the University of Minnesota specializing