Article catalogue

        SQL Birth of
        SQL Standardization
        SQL:2019
        SQL:2016
        SQL:2011
        SQL:2008
        SQL:2006
        SQL:2003
        SQL:1999
        SQL-92
        Language characteristics
        Statement classification
        SQL realization
        NewSQL

The birth of SQL

It was a summer in 1970.

Edgar Frank Codd, a senior researcher from IBM's St. Joseph research laboratory, created a relational data model by publishing an article entitled "A Relational Model of Data for Large Shared Data Banks" on Communications of ACM. Up to now, the relational database based on this model is still the mainstream in the field of database; Database ranking website DB engines gives the ranking of various databases.

Codd also won the Turing Award, the highest award in the computer field, in 1981, and was known as the father of relational database.

In 1974, Donald D. Chamberlin and Raymond F. Boyce, also from IBM, developed the initial version of SQL: sequence (Structured English Query Language) based on the relational model. Sequence is designed for IBM's original quasi relational database management system SystemR. IBM then developed commercial products based on its SystemR prototype, and launched system/38, SQL/DS and DB2 database management system in 1979, 1981 and 1983 respectively.

However, as early as June 1979, Relational Software, Inc. (now Oracle) discovered the potential of relational model and developed the first commercial SQL implementation: Oracle V2 (version 2). With the acquisition of open source database MySQL, Oracle has firmly occupied the leading position in the database market, which also led to the emergence of MariaDB branch.

SQL is the first commercial implementation of relational pattern and the most successful one. SQL is the most widely used database query language.
SQL standardization

In 1986, the American National Standards Institute (ANSI) first released the SQL standard; Subsequently, the ISO standards organization created the "database language SQL" standard in 1987. After many revisions in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011, 2016 and 2019, today's SQL standard contains a large number of functions and thousands of pages.

At present, ISO organization regular customization Part 16, attribute graph query (SQL/PGQ). Earlier, Neo4j announced on September 17, 2019 that graphical query language (GQL) has become another new ISO standard database query language after SQL.

The new part 16 (SQL/PGQ) is mainly to provide some GQL functions directly in SQL.
SQL:2019

At present, the latest SQL standard is SQL:2019, and part 15: ISO/IEC 9075-15:2019 multidimensional array (SQL/MDA) is added. SQL/MDA allows storage, access and processing of multidimensional arrays of scale, such as N-channel satellite images. This means that SQL can now decode images and directly access and process image areas through pixel coordinates.

The following are components of the current SQL standard, some of which are deprecated:

ISO/IEC 9075-1 information technology – Database language – SQL – Part 1: Framework( SQL/Frame)
ISO/IEC 9075-2 information technology – Database language – SQL – Part 2: basic principles( SQL/Basic principles)
ISO/IEC 9075-3 information technology – Database language – SQL – Part 3: call level interface( SQL/CLI)
ISO/IEC 9075-4 information technology – Database language – SQL – Part 4: persistent storage modules( SQL/PSM)
ISO/IEC 9075-9 information technology – Database language – SQL – Part 9: external data management( SQL/MED)
ISO/IEC 9075-10 information technology – Database language – SQL – Part 10: object language binding( SQL/OLB)
ISO/IEC 9075-11 information technology – Database language – SQL – Part 11: summary of information and definitions( SQL/Schemata)
ISO/IEC 9075-13 information technology – Database language – SQL – Part 13: use Java Programming language SQL Procedure and type( SQL/JRT)
ISO/IEC 9075-14 information technology – Database language – SQL – Part 14: XML Relevant specifications( SQL/XML)
ISO/IEC 9075-15 information technology – Database language – SQL – Part 15: multidimensional arrays( SQL/MDA)

The ISO organization has provided some relevant technical reports.
SQL:2016

SQL: 44 optional new features have been added in 2016, of which 22 are related to JSON functions and more than 10 are related to polymorphic table functions.

JSON: establish JSON Documents, access JSON Document nodes and JSON Function for format validation.
Line pattern recognition: through MATCH_RECOGNIZE Clause specifies a pattern of cross row matching (regular expression), and these matched row groups can be filtered, grouped and aggregated at the same time. Line pattern recognition can be used to analyze data streams or time series data, such as stock quotes or event logs.
Formatting and parsing of date and time.
LISTAGG Function: you can convert multiple rows of data into a string with a specified separator.
Polymorphic table functions: table functions that do not require predefined return types allow developers to take advantage of dynamic SQL Create powerful and complex custom functions.
New data type DECFLOAT. 

This article details the new features of SQL:2016.
SQL:2011

One of the most important new features of SQL:2011 is the enhanced support for temporal databases, including:

Period definition: two standard fields are used as the start and end of a period, including the start time point and excluding the end time point. This approach is consistent with existing data models, applications and tools.
use PERIOD FOR Schedule definition (also known as the application validity period table).
Use automatic time period splitting to update and delete the time data rows of the application.
adopt WITHOUT OVERLAPS Clause adds optional non overlapping constraints to the application time period to form a temporal primary key.
Supports time referential integrity constraints for application schedules.
Query the application schedule using regular query syntax or new temporal predicates defined for time period data, including CONTAINS,OVERLAPS,EQUALS,PRECEDES,SUCCEEDS,IMMEDIATELY PRECEDES,IMMEDIATELY SUCCEEDS. 
use PERIOD FOR SYSTEM_TIME and WITH SYSTEM VERSIONING Option defines the system version table (also known as the transaction schedule). The system time period is automatically maintained by the database. The constraints of the system version table should not be temporal constraints, and should only be enforced on the current data row.
use AS OF SYSTEM TIME and VERSIONS BETWEEN SYSTEM TIME ... AND Clause to query the time slice and sequence data in the system schedule.
Application time and system version can be used together to form a dual tense table.

This article introduces the new features of SQL:2011. This website can download some SQL standard draft files. Here is a paper on temporal database.
SQL:2008

The new functions in SQL:2008 mainly include:

Enhanced MERGE and DIAGNOSTIC sentence.
support TRUNCATE TABLE sentence
CASE Expressions support comma separated WHEN Clause.
INSTEAD OF Trigger.
JOIN Partition table.
FETCH Clause.
Allow outside of cursor definition ORDER BY. 
Support various XQuery regular expression /Pattern matching.
Derived field name enhancement.

SQL:2006

SQL:2006 defines how SQL operates XML. It defines the methods of importing and Storing XML data in SQL database, operating XML data in database, and publishing XML and regular SQL data in XML form. In addition, it allows applications to integrate queries with XQuery into SQL code to access both SQL data and XML documents.
SQL:2003

The new functions introduced by SQL:2003 standard mainly include:

XML Related functions( SQL/XML). 
Window function (analysis function).
Sequence generator for defining sequences( sequence). 
There are two types of automatically generated field values:( generated always as)And identity columns( identity). 
Merge statement( MERGE). 
CREATE TABLE Extension, support“ CREATE TABLE AS"And“ CREATE TABLE LIKE". 
Deleted BIT and BIT VARYING Data type.
OLAP Function expansion, support window function.

Here are some documents about the SQL:2003 standard.
SQL:1999

SQL:1999 adds a lot of new functions, and divides the SQL standard into several parts.

Data type: Boolean data type, user-defined unique type and user-defined structure type are supported.
Common table expressions and recursive queries.
OLAP Function: GROUP BY support ROLLUP,CUBE as well as GROUPING SETS Options.
Role based access control, CREATE ROLE sentence.
UNNEST keyword.
Regular expression matching.
Trigger.
Support procedure or control flow statements.
Java Used in SQL(SQL/OLB)as well as SQL Used in Java(SQL/JRT). 

This website provides a complete SQL:1999 tutorial.
SQL-92

The main new functions of SQL-92 include:

New data type: DATE,TIME,TIMESTAMP,INTERVAL,BIT String VARCHAR as well as NATIONAL CHARACTER . 
Support in addition to expression SQL Statement.
New scalar operations, such as string concatenation and getting substrings, date and time operations, and conditional statements.
New set operations, such as UNION JOIN,NATURAL JOIN,Difference set and intersection of sets.
CASE Conditional expression.
support ALTER and DROP Modify the schema definition.
C,Ada as well as MUMPS Language binding.
User permissions.
New integrity checks, e.g CHECK Constraints.
new information schema，Defines a read-only view of metadata, such as SELECT * FROM INFORMATION_SCHEMA.TABLES You can view tables in the database.
Dynamic execution of query statements (non precompiled statements).
Better support remote database access.
Temporary table, CREATE TEMP TABLE Wait.
Transaction isolation level.
adopt CAST (expr AS type) Dynamically modify data types.
Movable cursor.
Compatibility identification, which can be defined with other SQL Standard forward and backward compatibility.

Subsequently, the SQL standard released two important extensions:

1995 Year SQL/CLI(Call level interface);
1996 Year SQL/PSM(Stored procedure).

Here is the original text of SQL-92 standard.

For more information, see: http://www.mark-to-win.com/tutorial/51671.html