SQL

From Wikipedia for FEVERv2
(Redirected from Structured Query Language)
Jump to navigation Jump to search

This article is about the database language. SQL_sentence_0

For the IATA code, see San Carlos Airport (California). SQL_sentence_1

"SEQUEL" redirects here. SQL_sentence_2

For the topic of the word, see sequel. SQL_sentence_3

For other uses, see sequel (disambiguation). SQL_sentence_4

SQL_table_infobox_0

SQL (Structured Query Language)SQL_table_caption_0
ParadigmSQL_header_cell_0_0_0 DeclarativeSQL_cell_0_0_1
FamilySQL_header_cell_0_1_0 Query languageSQL_cell_0_1_1
Designed bySQL_header_cell_0_2_0 Donald D. Chamberlin

Raymond F. BoyceSQL_cell_0_2_1

DeveloperSQL_header_cell_0_3_0 ISO/IECSQL_cell_0_3_1
First appearedSQL_header_cell_0_4_0 1974; 46 years ago (1974)SQL_cell_0_4_1
Stable releaseSQL_header_cell_0_5_0 SQL:2016
  /    December 2016; 4 years ago (2016-12)SQL_cell_0_5_1
Typing disciplineSQL_header_cell_0_6_0 Static, strongSQL_cell_0_6_1
OSSQL_header_cell_0_7_0 Cross-platformSQL_cell_0_7_1
WebsiteSQL_header_cell_0_8_0 SQL_cell_0_8_1
Major implementationsSQL_header_cell_0_9_0
DialectsSQL_header_cell_0_10_0
Influenced bySQL_header_cell_0_11_0
InfluencedSQL_header_cell_0_12_0

SQL_table_infobox_1

SQL (file format)SQL_table_caption_1
SQL_header_cell_1_0_0 .sqlSQL_cell_1_0_1
Internet media typeSQL_header_cell_1_1_0 application/sqlSQL_cell_1_1_1
Developed bySQL_header_cell_1_2_0 ISO/IECSQL_cell_1_2_1
Initial releaseSQL_header_cell_1_3_0 1986 (1986)SQL_cell_1_3_1
Type of formatSQL_header_cell_1_4_0 DatabaseSQL_cell_1_4_1
StandardSQL_header_cell_1_5_0 ISO/IEC 9075SQL_cell_1_5_1
Open format?SQL_header_cell_1_6_0 YesSQL_cell_1_6_1
WebsiteSQL_header_cell_1_7_0 SQL_cell_1_7_1

SQL (/ˌɛsˌkjuːˈɛl/ (listen) S-Q-L, /ˈsiːkwəl/ "sequel"; Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). SQL_sentence_5

It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables. SQL_sentence_6

SQL offers two main advantages over older read–write APIs such as ISAM or VSAM. SQL_sentence_7

Firstly, it introduced the concept of accessing many records with one single command. SQL_sentence_8

Secondly, it eliminates the need to specify how to reach a record, e.g. with or without an index. SQL_sentence_9

Originally based upon relational algebra and tuple relational calculus, SQL consists of many types of statements, which may be informally classed as sublanguages, commonly: a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a data manipulation language (DML). SQL_sentence_10

The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. SQL_sentence_11

Although SQL is essentially a declarative language (4GL), it also includes procedural elements. SQL_sentence_12

SQL was one of the first commercial languages to utilize Edgar F. Codd’s relational model. SQL_sentence_13

The model was described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks". SQL_sentence_14

Despite not entirely adhering to the relational model as described by Codd, it became the most widely used database language. SQL_sentence_15

SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. SQL_sentence_16

Since then the standard has been revised to include a larger set of features. SQL_sentence_17

Despite the existence of standards, most SQL code requires at least some changes before being ported to different database systems. SQL_sentence_18

History SQL_section_0

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce after learning about the relational model from Edgar F. Codd in the early 1970s. SQL_sentence_19

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. SQL_sentence_20

Chamberlin and Boyce's first attempt at a relational database language was Square, but it was difficult to use due to subscript notation. SQL_sentence_21

After moving to the San Jose Research Laboratory in 1973, they began work on SEQUEL. SQL_sentence_22

The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley Dynamics Engineering Limited company. SQL_sentence_23

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. SQL_sentence_24

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. SQL_sentence_25 Navy, Central Intelligence Agency, and other U.S. SQL_sentence_26 government agencies. SQL_sentence_27

In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. SQL_sentence_28

By 1986, ANSI and ISO standard groups officially adopted the standard "Database Language SQL" language definition. SQL_sentence_29

New versions of the standard were published in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011 and, most recently, 2016. SQL_sentence_30

Syntax SQL_section_1

Main article: SQL syntax SQL_sentence_31

The SQL language is subdivided into several language elements, including: SQL_sentence_32

SQL_unordered_list_0

  • Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)SQL_item_0_0
  • Expressions, which can produce either scalar values, or tables consisting of columns and rows of dataSQL_item_0_1
  • Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL) (true/false/unknown) or Boolean truth values and are used to limit the effects of statements and queries, or to change program flow.SQL_item_0_2
  • Queries, which retrieve the data based on specific criteria. This is an important element of SQL.SQL_item_0_3
  • Statements, which may have a persistent effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics.SQL_item_0_4
    • SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.SQL_item_0_5
  • Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.SQL_item_0_6

Procedural extensions SQL_section_2

SQL is designed for a specific purpose: to query data contained in a relational database. SQL_sentence_33

SQL is a set-based, declarative programming language, not an imperative programming language like C or BASIC. SQL_sentence_34

However, extensions to Standard SQL add procedural programming language functionality, such as control-of-flow constructs. SQL_sentence_35

These include: SQL_sentence_36

SQL_table_general_2

SourceSQL_header_cell_2_0_0 AbbreviationSQL_header_cell_2_0_1 Full nameSQL_header_cell_2_0_2
ANSI/ISO StandardSQL_cell_2_1_0 SQL/PSMSQL_cell_2_1_1 SQL/Persistent Stored ModulesSQL_cell_2_1_2
Interbase / FirebirdSQL_cell_2_2_0 PSQLSQL_cell_2_2_1 Procedural SQLSQL_cell_2_2_2
IBM DB2SQL_cell_2_3_0 SQL PLSQL_cell_2_3_1 SQL Procedural Language (implements SQL/PSM)SQL_cell_2_3_2
IBM InformixSQL_cell_2_4_0 SPLSQL_cell_2_4_1 Stored Procedural LanguageSQL_cell_2_4_2
IBM NetezzaSQL_cell_2_5_0 NZPLSQLSQL_cell_2_5_1 (based on Postgres PL/pgSQL)SQL_cell_2_5_2
InvantiveSQL_cell_2_6_0 PSQLSQL_cell_2_6_1 Invantive Procedural SQL (implements SQL/PSM and PL/SQL)SQL_cell_2_6_2
MariaDBSQL_cell_2_7_0 SQL/PSM, PL/SQLSQL_cell_2_7_1 SQL/Persistent Stored Module (implements SQL/PSM), Procedural Language/SQL (based on Ada)SQL_cell_2_7_2
Microsoft / SybaseSQL_cell_2_8_0 T-SQLSQL_cell_2_8_1 Transact-SQLSQL_cell_2_8_2
Mimer SQLSQL_cell_2_9_0 SQL/PSMSQL_cell_2_9_1 SQL/Persistent Stored Module (implements SQL/PSM)SQL_cell_2_9_2
MySQLSQL_cell_2_10_0 SQL/PSMSQL_cell_2_10_1 SQL/Persistent Stored Module (implements SQL/PSM)SQL_cell_2_10_2
MonetDBSQL_cell_2_11_0 SQL/PSMSQL_cell_2_11_1 SQL/Persistent Stored Module (implements SQL/PSM)SQL_cell_2_11_2
NuoDBSQL_cell_2_12_0 SSPSQL_cell_2_12_1 Starkey Stored ProceduresSQL_cell_2_12_2
OracleSQL_cell_2_13_0 PL/SQLSQL_cell_2_13_1 Procedural Language/SQL (based on Ada)SQL_cell_2_13_2
PostgreSQLSQL_cell_2_14_0 PL/pgSQLSQL_cell_2_14_1 Procedural Language/PostgreSQL Structured Query Language (based on reduced PL/SQL)SQL_cell_2_14_2
SAP R/3SQL_cell_2_15_0 ABAPSQL_cell_2_15_1 Advanced Business Application ProgrammingSQL_cell_2_15_2
SAP HANASQL_cell_2_16_0 SQLScriptSQL_cell_2_16_1 SQLScriptSQL_cell_2_16_2
SybaseSQL_cell_2_17_0 Watcom-SQLSQL_cell_2_17_1 SQL Anywhere Watcom-SQL DialectSQL_cell_2_17_2
TeradataSQL_cell_2_18_0 SPLSQL_cell_2_18_1 Stored Procedural LanguageSQL_cell_2_18_2

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_sentence_37

The SQL standard defines SQL/JRT extensions (SQL Routines and Types for the Java Programming Language) to support Java code in SQL databases. SQL_sentence_38

Microsoft 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 unmanaged extended stored procedures primarily written in C. PostgreSQL lets users write functions in a wide variety of languages—including Perl, Python, Tcl, JavaScript (PL/V8) and C. SQL_sentence_39

Interoperability and standardization SQL_section_3

Overview SQL_section_4

SQL implementations are incompatible between vendors and do not necessarily completely follow standards. SQL_sentence_40

In particular date and time syntax, string concatenation, NULLs, and comparison case sensitivity vary from vendor to vendor. SQL_sentence_41

Particular exceptions are PostgreSQL and Mimer SQL which strive for standards compliance, though PostgreSQL does not adhere to the standard in how folding of unquoted names is done. SQL_sentence_42

The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. SQL_sentence_43

Thus, Foo should be equivalent to FOO not foo according to the standard. SQL_sentence_44

Popular implementations of SQL commonly omit support for basic features of Standard SQL, such as the DATE or TIME data types. SQL_sentence_45

The most obvious such examples, and incidentally the most popular commercial and proprietary SQL DBMSs, are Oracle (whose DATE behaves as DATETIME, and lacks a TIME type) and MS SQL Server (before the 2008 version). SQL_sentence_46

As a result, SQL code can rarely be ported between database systems without modifications. SQL_sentence_47

Reasons for incompatibility SQL_section_5

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

SQL_unordered_list_1

  • The complexity and size of the SQL standard means that most implementors do not support the entire standard.SQL_item_1_7
  • The standard does not specify database behavior in several important areas (e.g. indexes, file storage...), leaving implementations to decide how to behave.SQL_item_1_8
  • 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 ambiguity.SQL_item_1_9
  • Many database vendors have large existing customer bases; where the newer version of the SQL standard conflicts with the prior behavior of the vendor's database, the vendor may be unwilling to break backward compatibility.SQL_item_1_10
  • There is little commercial incentive for vendors to make it easier for users to change database suppliers (see vendor lock-in).SQL_item_1_11
  • Users evaluating database software tend to place other factors such as performance higher in their priorities than standards conformance.SQL_item_1_12

Standardization history SQL_section_6

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. SQL_sentence_49

It is maintained by ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange. SQL_sentence_50

Until 1996, the National Institute of Standards and Technology (NIST) data management standards program certified SQL DBMS compliance with the SQL standard. SQL_sentence_51

Vendors now self-certify the compliance of their products. SQL_sentence_52

The original standard declared that the official pronunciation for "SQL" was an initialism: /ˌɛsˌkjuːˈɛl/ ("ess cue el"). SQL_sentence_53

Regardless, many English-speaking database professionals (including Donald Chamberlin himself) use the acronym-like pronunciation of /ˈsiːkwəl/ ("sequel"), mirroring the language's pre-release development name, "SEQUEL". SQL_sentence_54

The SQL standard has gone through a number of revisions: SQL_sentence_55

SQL_table_general_3

YearSQL_header_cell_3_0_0 NameSQL_header_cell_3_0_1 AliasSQL_header_cell_3_0_2 CommentsSQL_header_cell_3_0_3
1986SQL_cell_3_1_0 SQL-86SQL_cell_3_1_1 SQL-87SQL_cell_3_1_2 First formalized by ANSI.SQL_cell_3_1_3
1989SQL_cell_3_2_0 SQL-89SQL_cell_3_2_1 FIPS 127-1SQL_cell_3_2_2 Minor revision that added integrity constraints, adopted as FIPS 127-1.SQL_cell_3_2_3
1992SQL_cell_3_3_0 SQL-92SQL_cell_3_3_1 SQL2, FIPS 127-2SQL_cell_3_3_2 Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.SQL_cell_3_3_3
1999SQL_cell_3_4_0 SQL:1999SQL_cell_3_4_1 SQL3SQL_cell_3_4_2 Added regular expression matching, recursive queries (e.g. transitive closure), triggers, support for procedural and control-of-flow statements, non-scalar types (arrays), and some object-oriented features (e.g. structured types). Support for embedding SQL in Java (SQL/OLB) and vice versa (SQL/JRT).SQL_cell_3_4_3
2003SQL_cell_3_5_0 SQL:2003SQL_cell_3_5_1 SQL_cell_3_5_2 Introduced XML-related features (SQL/XML), window functions, standardized sequences, and columns with auto-generated values (including identity-columns).SQL_cell_3_5_3
2006SQL_cell_3_6_0 SQL:2006SQL_cell_3_6_1 SQL_cell_3_6_2 ISO/IEC 9075-14:2006 defines ways that SQL can be used 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 lets applications integrate queries into their SQL code with XQuery, the XML Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents.SQL_cell_3_6_3
2008SQL_cell_3_7_0 SQL:2008SQL_cell_3_7_1 SQL_cell_3_7_2 Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers, TRUNCATE statement, FETCH clause.SQL_cell_3_7_3
2011SQL_cell_3_8_0 SQL:2011SQL_cell_3_8_1 SQL_cell_3_8_2 Adds temporal data (PERIOD FOR) (more information at: Temporal database#History). Enhancements for window functions and FETCH clause.SQL_cell_3_8_3
2016SQL_cell_3_9_0 SQL:2016SQL_cell_3_9_1 SQL_cell_3_9_2 Adds row pattern matching, polymorphic table functions, JSON.SQL_cell_3_9_3
2019SQL_cell_3_10_0 SQL:2019SQL_cell_3_10_1 SQL_cell_3_10_2 Adds Part 15, multidimensional arrays (MDarray type and operators).SQL_cell_3_10_3

Current standard SQL_section_7

The standard is commonly denoted by the pattern: ISO/IEC 9075-n:yyyy Part n: title, or, as a shortcut, ISO/IEC 9075. SQL_sentence_56

ISO/IEC 9075 is complemented by ISO/IEC 13249: SQL Multimedia and Application Packages (SQL/MM), which defines SQL based interfaces and packages to widely spread applications like video, audio and spatial data. SQL_sentence_57

Interested parties may purchase SQL standards documents from ISO, IEC or ANSI. SQL_sentence_58

A draft of SQL:2008 is freely available as a archive. SQL_sentence_59

Anatomy of SQL Standard SQL_section_8

The SQL standard is divided into ten parts. SQL_sentence_60

There are gaps in the numbering due to the withdrawal of outdated parts. SQL_sentence_61

Extensions to the ISO/IEC Standard SQL_section_9

ISO/IEC 9075 is complemented by ISO/IEC 13249 SQL Multimedia and Application Packages. SQL_sentence_62

This closely related but separate standard is developed by the same committee. SQL_sentence_63

It defines interfaces and packages based on SQL. SQL_sentence_64

The aim is a unified access to typical database applications like text, pictures, data mining or spatial data. SQL_sentence_65

Technical Reports SQL_section_10

ISO/IEC 9075 is also accompanied by a series of Technical Reports, published as ISO/IEC TR 19075 in 8 parts. SQL_sentence_66

These Technical Reports explain the justification for and usage of some features of SQL, giving examples where appropriate. SQL_sentence_67

The Technical Reports are non-normative; if there is any discrepancy from 9075, the text in 9075 holds. SQL_sentence_68

Currently available 19075 Technical Reports are: SQL_sentence_69

Alternatives SQL_section_11

A distinction should be made between alternatives to SQL as a language, and alternatives to the relational model itself. SQL_sentence_70

Below are proposed relational alternatives to the SQL language. SQL_sentence_71

See navigational database and NoSQL for alternatives to the relational model. SQL_sentence_72

Distributed SQL processing SQL_section_12

Distributed Relational Database Architecture (DRDA) was designed by a work group within IBM in the period 1988 to 1994. SQL_sentence_73

DRDA enables network connected relational databases to cooperate to fulfill SQL requests. SQL_sentence_74

An interactive user or program can issue SQL statements to a local RDB and receive tables of data and status indicators in reply from remote RDBs. SQL_sentence_75

SQL statements can also be compiled and stored in remote RDBs as packages and then invoked by package name. SQL_sentence_76

This is important for the efficient operation of application programs that issue complex, high-frequency queries. SQL_sentence_77

It is especially important when the tables to be accessed are located in remote systems. SQL_sentence_78

The messages, protocols, and structural components of DRDA are defined by the Distributed Data Management Architecture. SQL_sentence_79

Criticisms SQL_section_13

Design SQL_section_14

SQL deviates in several ways from its theoretical foundation, the relational model and its tuple calculus. SQL_sentence_80

In that model, a table is a set of tuples, while in SQL, tables and query results are lists of rows: the same row may occur multiple times, and the order of rows can be employed in queries (e.g. in the LIMIT clause). SQL_sentence_81

Critics argue that SQL should be replaced with a language that returns strictly to the original foundation: for example, see The Third Manifesto. SQL_sentence_82

However, no known proof exists that such uniqueness cannot be added to SQL itself, or at least a variation of SQL. SQL_sentence_83

In other words, it's quite possible that SQL can be "fixed" or at least improved in this regard such that the industry may not have to switch to a completely different query language to obtain uniqueness. SQL_sentence_84

Debate on this remains open. SQL_sentence_85

Other criticisms SQL_section_15

Chamberlin discusses four historical criticisms of SQL in a 2012 paper: SQL_sentence_86

Orthogonality and completeness SQL_section_16

Early specifications did not support major features, such as primary keys. SQL_sentence_87

Result sets could not be named, and sub-queries had not been defined. SQL_sentence_88

These were added in 1992. SQL_sentence_89

Null SQL_section_17

The concept of Null is the subject of some debates. SQL_sentence_90

The Null marker indicates that there is no value, even no 0 for an integer column or a string of length 0 for a text column. SQL_sentence_91

The concept of Nulls enforces the 3-valued-logic in SQL, which is a concrete implementation of the general 3-valued logic. SQL_sentence_92

Duplicates SQL_section_18

Another popular criticism is that it allows duplicate rows, making integration with languages such as Python, whose data types might make it difficult to accurately represent the data, difficult in terms of parsing and by the absence of modularity. SQL_sentence_93

This can be avoided declaring a unique constraint with one or more fields that identifies uniquely a row in the table. SQL_sentence_94

That constraint could also become the primary key of the table. SQL_sentence_95

Impedance mismatch SQL_section_19

In a similar sense to Object-relational impedance mismatch, there is a mismatch between the declarative SQL language and the procedural languages that SQL is typically embedded in. SQL_sentence_96

Data Integrity Categories SQL_section_20

Main data integrity categories of each RDBMS. SQL_sentence_97

Entity integrity SQL_section_21

Establishes that within the table the primary key has a unique value for each row, checking the uniqueness of the value of the primary key avoiding that there are duplicated rows in a table. SQL_sentence_98

Domain integrity SQL_section_22

Restricts the type, format, and value range that applies to valid entries for a column within a table SQL_sentence_99

Referential integrity SQL_section_23

Makes rows in a table that are being used by other records impossible to delete SQL_sentence_100

User-defined integrity SQL_section_24

Other specific rules not included above apply SQL_sentence_101

SQL data types SQL_section_25

The SQL standard defines three kinds of data types: SQL_sentence_102

SQL_description_list_2

  • SQL_item_2_13
    • predefined data typesSQL_item_2_14
    • constructed typesSQL_item_2_15
    • user-defined types.SQL_item_2_16

Predefined data types SQL_section_26

Constructed types SQL_section_27

Constructed types are one of ARRAY, MULTISET, REF(erence), or ROW. SQL_sentence_103

User-defined types are comparable to classes in object-oriented language with their own constructors, observers, mutators, methods, inheritance, overloading, overwriting, interfaces, and so on. SQL_sentence_104

See also SQL_section_28

SQL_unordered_list_3


Credits to the contents of this page go to the authors of the corresponding Wikipedia page: en.wikipedia.org/wiki/SQL.