SQL

from Wikipedia, the free encyclopedia

SQL (official pronunciation [ ɛskjuːˈɛl ], but often also [ ˈsiːkwəl ] after its predecessor SEQUEL; in German also often the German pronunciation of the letters) is a database language for defining data structures in relational databases and for editing (inserting, changing, deleting) and Queries of databases based on it.

The language is based on relational algebra , its syntax is relatively simple and semantically based on colloquial English. A joint committee of ISO and IEC standardizes the language with the participation of national standardization bodies such as ANSI or DIN . By using SQL, the aim is to make the applications independent of the database management system used .

The term SQL is generally understood as an abbreviation for “ Structured Query Language ” (in German: “Structured Query Language”), although according to the standard it is an independent name. The name is derived from its predecessor SEQUEL ([ ˈsiːkwəl ], Structured English Query Language), which was designed with the participation of Edgar F. Codd (IBM) in the 1970s by Donald D. Chamberlin and Raymond F. Boyce . SEQUEL was later renamed SQL because SEQUEL is a registered trademark of the Hawker Siddeley Aircraft Company.

Language elements and examples

Components of SQL

SQL commands can be divided into four categories (assignment according to the theory of database languages in brackets):

The term SQL refers to the English word " query " (German: " query "). With queries, the data stored in a database are called up, i.e. made available to the user or application software.

The result of a query looks like a table and can often be displayed, edited and used like a table.

The basic commands and terms are explained using the following example:

ER diagram : SQL example
Relations:
college student
MatrNr Surname
26120 Spruce
25403 Jonas
27103 Lazy
hears
MatrNr Template no
25403 5001
26120 5001
26120 5045
lecture
Template no title PersNo
5001 ET 15th
5022 IT 12
5045 DB 12
professor
PersNo Surname
12 Wirth
15th Tesla
20th Vacationers

Simple query

SELECT *
FROM Student;

lists all columns and all rows of the Student table .

Result:

MatrNr Surname
26120 Spruce
25403 Jonas
27103 Lazy

Query with column selection

SELECT VorlNr, Titel
FROM Vorlesung;

lists the columns VorlNr and Title of all rows in the Lecture table .

Result:

Template no title
5001 ET
5022 IT
5045 DB

Query with unique values

SELECT DISTINCT MatrNr
FROM hört;

lists only different entries of the column Legi from the table listening to. This shows the matriculation numbers of all students who have attended at least one lecture, whereby matriculation numbers that occur more than once are given out only once.

Result:

MatrNr
25403
26120

Query with renaming

SELECT MatrNr AS Matrikelnummer, Name
FROM Student;

lists the columns MatrNr and Name of all rows of the Student table . MatrNr is listed as the matriculation number in the display result.

Result:

Matriculation number Surname
26120 Spruce
25403 Jonas
27103 Lazy

Query with filter

SELECT VorlNr, Titel
FROM Vorlesung
WHERE Titel = 'ET';

lists VorlNr and title of all those lines of the table lecture whose title is 'ET'.

The frequently used instruction structured in this way is also referred to as "SFW block" after the initial letters.

Result:

Template no title
5001 ET

Query with filter for content

SELECT Name
FROM Student
WHERE Name LIKE 'F%';

lists the names of all students whose names begin with F (in the example: Fichte and Fauler).

LIKE can be used with various wildcards : _ stands for any single character, % stands for any character string. Some database systems offer other such wildcard characters, e.g. for character sets.

Result:

Surname
Spruce
Lazy

Query with filter and sorting

SELECT Vorname, Name, StrasseNr, Plz, Ort
FROM Student
WHERE Plz = '20095'
ORDER BY Name;

lists first name , name , road No , Zip and place all students from the specified zip code area by name on.

Query with linked tables 

SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
FROM Professor, Vorlesung
WHERE Professor.PersNr = Vorlesung.PersNr;

The list after FROM defines the data sources: at this point several tables can be linked with the help of so-called JOINs , so that data from different tables can be merged and displayed.

In this example, one is internal natural composite ( natural inner join used): All records from the tables professor and lectures that have the same value in the PersNr have. Professors without a lecture and lectures without a professor are not displayed.

This is equivalent to: 

SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
FROM Professor
    INNER JOIN Vorlesung ON Professor.PersNr = Vorlesung.PersNr;

Caution: Not all implementations understand the keywords "INNER", "OUTER" and "JOIN".

Tables can not only be linked using key fields, but also using any fields, as the following, technically nonsensical example shows: 

SELECT Vorlesung.Titel, Professor.Name
FROM Professor, Vorlesung
WHERE Professor.Name <> Vorlesung.Titel

The result contains the combinations of all professors and all lectures where the name of the professor differs from the title of the lecture - these are simply all (no lecture is called like a professor):

title Surname
ET Tesla
ET Wirth
ET Vacationers
IT Tesla
IT Wirth
IT Vacationers
DB Tesla
DB Wirth
DB Vacationers

Left outer composite 

SELECT Professor.PersNr, Professor.Name, Vorlesung.VorlNr, Vorlesung.Titel
FROM Professor
    LEFT OUTER JOIN Vorlesung ON Professor.PersNr = Vorlesung.PersNr;

results in all data records in the Professor table combined with the data records in the Lecture table , which each have the same value in the PersNr field . Professors without a lecture are included, the lecture columns in the result then have the value NULL. Lectures without a professor are not included.

The following query only returns those data records for which there is no suitable data record in the outer group on the left (all professors who do not give any lectures): 

SELECT Professor.PersNr, Professor.Name
FROM Professor
    LEFT OUTER JOIN Vorlesung ON Professor.PersNr = Vorlesung.PersNr
WHERE Vorlesung.PersNr IS NULL;

The same can be achieved using a subquery: 

SELECT Professor.PersNr, Professor.Name
FROM Professor
WHERE NOT EXISTS (SELECT * FROM Vorlesung WHERE PersNr = Professor.PersNr);

Grouping with aggregate functions

SELECT Professor.PersNr, Professor.Name, COUNT(Vorlesung.PersNr) AS Anzahl
FROM Professor
    LEFT OUTER JOIN Vorlesung ON Professor.PersNr = Vorlesung.PersNr
GROUP BY Professor.Name, Professor.PersNr;

counts the number of lectures per professor using the aggregate function COUNT .

Comment : COUNT (Professor.PersNr) or COUNT (*) would be wrong ( zero values should not be counted).

Summary of a SELECT

In summary, the most important elements of an SQL SELECT query can be described as follows: 

SELECT [DISTINCT] Auswahlliste [AS Spaltenalias]
FROM Quelle [ [AS] Tabellenalias], evtl. mit JOIN-Verknüpfungen
[WHERE Where-Klausel]
[GROUP BY ein oder mehrere Group-by-Attribute]
[HAVING Having-Klausel]
[ORDER BY ein oder mehrere Sortierungsattribute mit [ASC|DESC]];

Explanation:

  • DISTINCT specifies that the same result tuples should be removed from the result relation . So each data record is only output once, even if it occurs several times in the table. Otherwise SQL returns a multiset .
  • Selection list determines which columns of the source are to be output (* for all) and whether aggregate functions are to be used. As with all other lists, the individual elements are separated from one another with a comma.
  • Source indicates where the data came from. It may relations and views are given and each other as a Cartesian product or as a composite ( JOIN , SQL-92 ab) are linked. With the additional specification of a name, relations for the query can be renamed (see examples ).
  • Where clause specifies conditions, also known as filters, under which the data should be output. In SQL it is also possible to specify subqueries here, so that SQL is strictly relationally complete .
  • Group-by attribute specifies whether different values ​​should be output as individual lines ( GROUP BY = grouping) or the field values ​​of the lines through aggregations such as addition ( SUM ), average ( AVG ), minimum ( MIN ), maximum ( MAX ) can be combined into a result value that relates to the grouping.
  • Having clause is like the where clause, except that the specified condition relates to the result of an aggregation function, for example HAVING SUM (amount)> 0.
  • Sorting attribute: after ORDER BY attributes are specified according to which sorting is to take place. The default setting is ASC, which means ascending order, DESC is descending order.

Set operators can be applied to multiple SELECT queries that have the same number of attributes and for which the data types of the attributes match:

  • UNION combines the result sets. In some implementations, multiple result tuples are removed, as with DISTINCT, without "UNION DISTINCT" having to or may be written.
  • UNION ALL combines the result sets. Result tuples that occur more than once are retained. However, some implementations interpret “UNION” as “UNION ALL” and may not understand “ALL” and issue an error message.
  • EXCEPT returns the tuples that are contained in a first, but not in a second result set. Result tuples that occur more than once are removed.
  • MINUS is an operator analogous to EXCEPT , which is used alternatively by some SQL dialects.
  • INTERSECT returns the intersection of two result sets . Result tuples that occur more than once are removed.

Insertion of records 

INSERT INTO Vorlesung (VorlNr, Titel, PersNr) VALUES (1000, 'Softwareentwicklung 1', 12);
INSERT INTO Vorlesung (VorlNr, Titel, PersNr) VALUES (1600, 'Algorithmen', 12);
INSERT INTO Vorlesung (VorlNr, Titel, PersNr) VALUES (1200, 'Netzwerke 1', 20);
INSERT INTO Vorlesung (VorlNr, Titel, PersNr) VALUES (1001, 'Datenbanken', 15);

adds four records to the Lecture table . The values ​​must match the data types of the fields VorlNr, Titel, PersNr . 

SELECT *
FROM Vorlesung;

then delivers z. B. the result (the order can also be different):

Template no title PersNo
1001 Databases 15th
1000 Software development 1 12
1200 Networks 1 20th
5001 ET 12
5022 IT 12
1600 Algorithms 12
5045 DB 15th

Changing records 

UPDATE Vorlesung
SET VorlNr = VorlNr + 1000, PersNr = 20
WHERE PersNr = 15;

changes all data records for which PersNr has the value 15. The value of VorlNr is increased by 1000 and the value of PersNr is set to 20.

The result of a subsequent SELECT * is, possibly with a different order:

Template no title PersNo
1000 Software development 1 12
1200 Networks 1 20th
1600 Algorithms 12
2001 Databases 20th
5001 ET 12
5022 IT 12
6045 DB 20th

Deletion of records 

DELETE FROM Vorlesung
WHERE PersNr = 12;

deletes all data records for which PersNr has the value 12.

Result of a subsequent SELECT *, possibly in a different order:

Template no title PersNo
1200 Networks 1 20th
2001 Databases 20th
6045 DB 20th

Summary of INSERT, UPDATE, DELETE

In general terms, the change instructions look like this:

INSERT statement: 

INSERT INTO Quelle [(Auswahlliste)]
VALUES (Werteliste) | SELECT <Auswahlkriterien>;

UPDATE statement: 

UPDATE Quelle SET Zuweisungsliste
[FROM From-Klausel]
[WHERE Auswahlbedingung];

DELETE statement: 

DELETE FROM Quelle
[WHERE Auswahlbedingung];

Data definition

Database table

The database table lecture can be created with the following statement :

CREATE TABLE Vorlesung (VorlNr INT NOT NULL PRIMARY KEY, Titel VARCHAR NOT NULL, PersNr NOT NULL), FOREIGN KEY (PersNr) REFERENCES Professor (PersNr);

The value is not allowed in any of the fields VorlNr , Titel , PersNrNULL . The foreign key PersNr references the primary key PersNr of the Professor table . This ensures that only data records can be inserted into the Lecture table for which the value of PersNr occurs as the primary key in the Professor table (see referential integrity ).

Database index

With the instruction

CREATE INDEX VorlesungIndex
ON Vorlesung (PersNr, Titel);

a database index can be defined for the Lecture table , which the database system can use to speed up the execution of queries. The database system independently decides whether this makes sense through complex evaluations and analyzes for each query.

view

A view is essentially an alias for a database query . It can be used like a database table . The instruction

CREATE VIEW Vorlesungssicht AS
SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
FROM Professor
    INNER JOIN Vorlesung ON Professor.PersNr = Vorlesung.PersNr;

saves the defined query as a view. The query

SELECT Titel, Name
FROM Vorlesungssicht
WHERE VorlNr < 5000;

uses this view and could, for example, provide the following result:

title PersNo
Software development 1 Wirth
Networks 1 Vacationers
Algorithms Wirth
Databases Vacationers

Summary

In summary, the most important elements of the definition of a database table , a database index or a view must be specified as follows: 

CREATE TABLE Tabellenname (Attributdefinition [PRIMARY KEY]) [, FOREIGN KEY (Attributliste) REFERENCES Tabellenname (Attributliste)]);
DROP TABLE Tabellenname;
ALTER TABLE Tabellenname (Attributdefinition [PRIMARY KEY]) [, FOREIGN KEY (Attributliste) REFERENCES Tabellenname (Attributliste)]);

CREATE INDEX Indexname ON Tabellenname (Attributliste);
DROP INDEX Indexname;

CREATE VIEW Sichtname [(Attributliste)] AS SELECT <Auswahlkriterien>;
DROP VIEW Sichtname;

redundancy

A principle of database design is that there should be no redundancies in a database . This means that every piece of information, e.g. B. an address, is only saved once.

Example : in the list of participants in a lecture, the addresses are not entered again, but only indirectly via the matriculation number. In order to still create a list of participants with addresses, a SELECT query is made, in which the table of participants is linked to the table of students (see above: JOIN).

In some cases the performance of a database is better if it is not (fully) normalized. In this case, redundancies are often consciously accepted in practice in order to shorten time-consuming and complex joins and thus increase the speed of the queries. One also speaks of a denormalization of a database. When (and whether at all) denormalization makes sense is controversial and depends on the circumstances.

key

While the information must be spread across many tables to avoid redundancy, keys are the means to link this scattered information together.

As a rule, each data record has a unique number or some other unique field to identify it. These identifications are called keys.

If this data record is required in other contexts, only its key is given. When lecture participants are recorded, their names and addresses are not recorded, only their respective matriculation numbers, from which all other personal details are derived.

It is possible that some data records only consist of keys (mostly numbers) that can only be understood in connection with links. The data record's own key is called the primary key. Other keys in the record that reference the primary keys of other tables are known as foreign keys.

Keys can also consist of a combination of several items of information. For example, the participants of a lecture can be identified by the unique combination of lecture number and student number, so that the double registration of a student for a lecture is excluded.

Referential Integrity

Referential integrity means that records that are used by other records are completely present in the database.

In the example above, this means that the student table only contains matriculation numbers that actually exist in the student table.

This important functionality can (and should) already be monitored by the database, so that e.g. B.

  • only existing matriculation numbers can be entered in the participant table,
  • the attempt to delete the data record of a student who has already attended a lecture is either prevented (error message) or the data record is immediately removed from the participant table (deletion transfer) and
  • to attempt the matriculation of a student who has already occupied a lecture, change , either (error message) or the entry is prevented is also changed the same on the user table (Cascade Update).

Data inconsistency is commonly referred to as data inconsistency . This exists when data does not meet the integrity conditions (e.g. constraints or foreign key relationships ).

Data inconsistencies can be caused by errors in the analysis of the data model, a lack of normalization of the ERM or errors in programming.

The latter includes the lost update phenomena and the processing of interim results that have become out of date. This occurs primarily during online processing, as values ​​displayed to the user cannot be encapsulated in a transaction. 

Beispiel:
Transaktion A liest Wert x
Transaktion B verringert Wert x um 10
Transaktion A erhöht den gespeicherten Wert von x um eins und schreibt zurück
Ergebnis x' = x+1
Die Änderung von B ist verloren gegangen

SQL data types

In the commands create tableand presented above alter table, when defining each column, it is specified which data type the values ​​in this column can assume. For this purpose, SQL provides a whole range of standardized data types. However, the individual DBMS manufacturers have added a myriad of additional data types to this list. The most important standard data types are:

integer
Whole number (positive or negative), whereby designations such as smallint , tinyint or bigint are used depending on the number of bits used. The respective limits and the terminology used are defined by the database system.
numeric (n, m) or decimal (n, m)
Fixed point number (positive or negative) with a maximum of ndigits, including mdecimal places . Because it is stored here as a decimal number, there is an accuracy that is particularly necessary for amounts of money.
float (m)
Floating point number (positive or negative) with a maximum of mdecimal places.
real
Floating point number (positive or negative). The precision for this data type is defined by the database system.
double or double precision
Floating point number (positive or negative). The precision for this data type is defined by the database system.
float and double
are suitable for technical-scientific values ​​and also include the exponential representation. Because they are stored in binary format, they are not suitable for amounts of money because, for example, the value 0.10 € (corresponds to 10 cents) cannot be exactly represented.
character (n) or char (n)
String of text with ncharacters.
varchar (n) or character varying (n)
Character string (i.e. text) of variable length, but a maximum of nprintable and / or non-printable characters. The variant varchar2is specific to Oracle without actually differing.
text
String of characters (at least theoretically) of any length. In some systems synonymous with clob.
date
Date (without time)
time
Time specification (possibly including time zone)
timestamp
Time stamp (includes date and time; possibly including time zone), mostly with millisecond resolution, sometimes with microsecond accuracy
boolean
Boolean variable (can have the values true(true) or false(false) or NULL(unknown)). According to SQL: 2003, this data type is optional and not all DBMS provide this data type.
blob (n) or binary large object (n)
Binary data with a maximum length of n bytes .
clob (n) or character large object (n)
Strings with a maximum nlength of characters.

If the table definition allows, attributes can also NULLassume the value if no value is known or no value should be saved for other reasons. The NULLvalue is different from all other possible values ​​of the data type.

Transaction, commit and rollback

A transaction describes a set of database changes that (must) be carried out together. For example, the booking (as a transaction) of a monetary amount is characterized by two atomic database operations “debiting the monetary amount from account A” and “booking the monetary amount to account B”. If the complete processing of the elementary database operations of the transaction cannot be carried out (e.g. due to an error), all changes made to the database must be reset to the initial state.

The process of rolling back all changes to a transaction is called rollback. The term commit describes the execution of a transaction. Transactions are one way of ensuring the consistency of the database. In the example of double account management, preventing invalid partial postings ensures a balanced account balance.

Databases allow certain commands to be executed outside of a transaction. This includes in particular loading data into tables or exporting data using utilities. Some DBMS allow the transaction logic to be switched off temporarily and some controls to increase processing speed. However, this usually has to be enforced by an explicit command in order to avoid accidentally changing data outside of a transaction. Such changes can lead to serious problems or even data loss if a database restore is necessary. A transaction is ended with the SQL statement Commit. All changes to the transaction are made persistent , and the DBMS uses suitable (internal) means (e.g. logging) to ensure that these changes are not lost.

The command Rollbackalso terminates a transaction, but it undoes all changes since the transaction began. That is, the state of the system (in terms of changes to the transaction) is the same as it was before the transaction.

Programming with SQL

Programming interface

The original SQL was not a Turing-complete programming language , so it did not allow the implementation of any computer programs . It can now be combined with other programming languages to enable programming in the narrower sense. There are different techniques for doing this.

  • With Embedded SQL , SQL statements can be written in the source code of a program, typically in C , C ++ , COBOL , Ada , Pascal or similar. written, embedded. During program preparation, a precompiler translates the SQL commands into function calls. Embedded SQL is part of the ANSI SQL standard. Examples of implementations: SQLJ for Java , Pro * C for C , C ++, ADO and ADO.NET .
  • Conventional programming interfaces allow the direct transfer of SQL commands to database systems via function calls. Examples: ODBC , JDBC , ADO .
  • Persistence - frameworks such as Hibernate or iBATIS abstract from the database access and allow object-oriented processing of a relational database in an object oriented programming language (eg. Java or C # )
  • With Part 4 SQL / PSM of the standard, constructs such as IF blocks and loops are provided. It is implemented in the database systems in various forms and with manufacturer-specific extensions, e.g. B. PL / SQL in Oracle or Transact-SQL in MS SQL Server .

Static and dynamic SQL

Regardless of the programming technique used, a distinction is made between static and dynamic SQL.

  • With static SQL , the SQL statement is known and defined by the database system at the time the program is compiled (e.g. if the query for an account is pre-formulated and only the account number is used at runtime).
  • With dynamic SQL , the database system does not know the SQL statement until the program is executed (e.g. because the user enters the complete query). So are z. B. all SQL statements that are executed using SQL / CLI or JDBC are always dynamic. Dynamic SQL statements are generally executed with execute immediate ( SQL string ).

With dynamic SQL , the database system must interpret the SQL statement at the runtime of the program and optimize the access path. Since this so-called parse process takes time, many database systems buffer the SQL statements that have already been parsed in order to save the time for a new parsing if they repeat themselves. For static SQL can already translate the programs or in binding the SQL statements to a database (using Bind the SQL commands) the optimal access path to be determined. This enables the shortest possible runtimes of the application programs, but the access path of all affected programs must be redefined if conditions (e.g. statistics) change ( rebind ). The bind phase is mainly known today in the mainframe environment, but most database systems optimize during runtime.

chronology

  • 1975: SEQUEL = Structured English Query Language , the forerunner of SQL , is developed for the System R project by IBM .
  • 1979: SQL comes onto the market for the first time with Oracle V2 by Relational Software Inc.
  • 1986: SQL1 is adopted as the standard by ANSI .
  • 1987: SQL1 is adopted as a standard by the International Organization for Standardization (ISO) and revised again in 1989.
  • 1992: The SQL2 or SQL-92 standard is adopted by the ISO.
  • 1999: SQL3 or SQL: 1999 is adopted. As part of this revision, other important features (such as triggers or recursive queries) will be added.
  • 2003: SQL: 2003 . SQL / XML , Window functions, Sequences are added as new features .
  • 2006: SQL / XML: 2006 . Extensions for SQL / XML .
  • 2008: SQL: 2008 or ISO / IEC 9075: 2008. INSTEAD OF triggers, TRUNCATE statements and FETCH clauses are added as new features.
  • 2011: SQL: 2011 or ISO / IEC 9075: 2011. "Time-related data" (PERIOD FOR) are added as new features. There are extensions for window functions and the FETCH clause.
  • 2016: SQL: 2016 or ISO / IEC 9075: 2016. JSON and "row pattern matching" are added as new features.
  • 2019: SQL / MDA: 2019 . Extensions for a data type "multidimensional field".

Language standard

The aim of standardization is to be able to create application programs in such a way that they are independent of the database system used. Today's database systems implement more or less large parts of the language standard. In addition, they often provide manufacturer-specific extensions that do not correspond to the standard range of languages. In the pre-SQL period, the aim was to make applications portable via the compatible interface .

The standard consists of ten individual publications:

  • ISO / IEC 9075-1: 2016 Part 1: Framework (SQL / Framework)
  • ISO / IEC 9075-2: 2016 Part 2: Foundation (SQL / Foundation)
  • ISO / IEC 9075-3: 2016 Part 3: Call-Level Interface (SQL / CLI)
  • ISO / IEC 9075-4: 2016 Part 4: Persistent stored modules (SQL / PSM)
  • ISO / IEC 9075-9: 2016 Part 9: Management of External Data (SQL / MED)
  • ISO / IEC 9075-10: 2016 Part 10: Object language bindings ( SQL / OLB )
  • ISO / IEC 9075-11: 2016 Part 11: Information and definition schemas (SQL / Schemata)
  • ISO / IEC 9075-13: 2016 Part 13: SQL Routines and types using the Java TM programming language ( SQL / JRT )
  • ISO / IEC 9075-14: 2016 Part 14: XML-Related Specifications ( SQL / XML )
  • ISO / IEC 9075-15: 2019 Part 15: Multi-dimensional arrays (SQL / MDA)

Another part is currently (2019) under development:

  • ISO / IEC 9075-16: 20xx Part 16: Property Graph Queries (SQL / PGQ)

There are also a number of technical reports for these standards that provide an introduction to the individual topics. These are available free of charge from ISO.

  • ISO / IEC TR 19075-1: 2011 Part 1: XQuery Regular Expression Support in SQL Download
  • ISO / IEC TR 19075-2: 2015 Part 2: SQL Support for Time-Related Information Download
  • ISO / IEC TR 19075-3: 2015 Part 3: SQL Embedded in Programs using the JavaTM programming language Download
  • ISO / IEC TR 19075-4: 2015 Part 4: SQL with Routines and types using the JavaTM programming language Download
  • ISO / IEC TR 19075-5: 2016 Part 5: Row Pattern Recognition in SQL Download
  • ISO / IEC TR 19075-6: 2017 Part 6: SQL support for JavaScript Object Notation (JSON) Download
  • ISO / IEC TR 19075-7: 2017 Part 7: Polymorphic table functions in SQL Download
  • ISO / IEC TR 19075-8: 2019 Part 8: Multi-dimensional arrays (SQL / MDA)

Another part is currently (2019) under development:

  • ISO / IEC TR 19075-9: 20xx Part 9: Online Analytic Processing (OLAP) capabilities

and is supplemented by 6 standardized SQL multimedia and application packages :

  • ISO / IEC 13249-1: 2016 Part 1: Framework
  • ISO / IEC 13249-2: 2003 Part 2: Full-Text
  • ISO / IEC 13249-3: 2016 Part 3: Spatial
  • ISO / IEC 13249-5: 2003 Part 5: Still image
  • ISO / IEC 13249-6: 2006 Part 6: Data mining
  • ISO / IEC 13249-7: 2013 Part 7: History

The official standard is not freely available, but there is a zip archive with a working version from 2008.

Extensions

The first two parts of the SQL standard SQL / Framework and SQL / Foundation define the core functionalities. In the other parts, specific aspects of the language are defined.

  • Part 4: SQL / PSM is an extension of procedural constructs. Among other things, they enable the programming of loops (FOR, WHILE, REPEAT UNTIL, LOOP), cursors, exception handling, triggers and your own functions. Oracle implements this functionality under the name PL / SQL , DB2 uses the term SQL / PL , PostgreSQL calls it PL / pgSQL .
  • Part 14: SQL / XML makes it possible to save XML documents in SQL databases, to query them with XPath in SQL / XML: 2003 and XQuery from SQL / XML: 2006 and to export relational database contents as XML. In order to accelerate the original work on this part of the standard, an informal working group was formed in 2000 (IBM, Oracle, ...) which defined the core functionalities under the name The SQLX Group and under the name SQLX . Their work has been incorporated into the current standard.

As a supplement to the SQL standard, ISO / IEC 13249: SQL multimedia and application packages is a standard that defines specialized interfaces in SQL syntax for the use cases text , geographic data , images , data mining and metadata .

literature

  • Donald D. Chamberlin, Raymond F. Boyce: SEQUEL: A Structured English Query Language . In: SIGMOD Workshop. Vol. 1 1974, pp. 249-264.
  • Donald D. Chamberlin, Morton M. Astrahan, Kapali P. Eswaran, Patricia P. Griffiths, Raymond A. Lorie, James W. Mehl, Phyllis Reisner, Bradford W. Wade: SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control . In: IBM Journal of Research and Development. 20 (6) 1976, pp. 560-575.
  • Günter Matthiessen, Michael Unterstein: Relational Databases and SQL in Theory and Practice Springer Vieweg, ISBN 978-3-642-28985-9 .
  • Edwin Schicker: Databases and SQL - A practice-oriented introduction. Teubner, ISBN 3-519-02991-X .
  • Oliver Bartosch, Markus Throll: Introduction to SQL. Galileo Press, ISBN 3-89842-497-9 .
  • Daniel Warner, Günter Leitenbauer: SQL. Franzis, ISBN 3-7723-7527-8 .
  • H. Faeskorn-Woyke, B. Bertelsmeier, P. Riemer, E. Bauer: Database systems, theory and practice with SQL2003, Oracle and MySQL. Pearson Studies, ISBN 978-3-8273-7266-6 .
  • Jörg Fritze, Jürgen Marsch: Successful database application with SQL3. Practice-oriented instructions - efficient use - including SQL tuning. Vieweg Verlag, ISBN 3-528-55210-7 .
  • Can Türker: SQL 1999 & SQL 2003. Dpunkt Verlag, ISBN 3-89864-219-4 .
  • Gregor Kuhlmann, Friedrich Müllmerstadt: SQL. Rowohlt, ISBN 3-499-61245-3 .
  • Michael J. Hernandez, John L. Viescas: Go To SQL. Addison-Wesley, ISBN 3-8273-1772-X .
  • A. Kemper, A. Eickler: Database systems - an introduction. Oldenbourg, ISBN 3-486-25053-1 .
  • Marcus Throll, Oliver Bartosch: Introduction to SQL 2008. 2nd edition. Galileo Computing, ISBN 978-3-8362-1039-3 including SQL-Teacher exercise software
  • Marco Skulschus: SQL and relational databases Comelio Medien, ISBN 978-3-939701-11-8 .
  • Michael Wagner: SQL / XML: 2006 - Evaluation of the standard conformity of selected database systems 1st edition. Diplomica Verlag, ISBN 3-8366-9609-6 .
  • Christian FG Schendera: SQL with SAS. Volume 1: PROC SQL for beginners . Oldenbourg Wissenschaftsverlag, Munich 2011, ISBN 978-3-486-59840-7 .
  • Christian FG Schendera: SQL with SAS. Volume 2: Advanced PROC SQL . Oldenbourg Wissenschaftsverlag, Munich 2012, ISBN 978-3-486-59836-0 .
  • CJ Date with Hugh Darwen : A Guide to the SQL standard: a users guide to the standard database language SQL, 4th ed. , Addison-Wesley, USA 1997, ISBN 978-0-201-96426-4
  • Jim Melton: Advanced SQL: 1999: Understanding Object-Relational and Other Advanced Features, 1st ed. , Morgan Kaufmann, USA, 2002, ISBN 978-1558606777 .

Web links

Wikibooks: SQL  - learning and teaching materials

See also

Individual evidence

  1. ↑ Discussion about System R and changing the name from SEQUEL to SQL
  2. ↑ Michael Wagner: SQL / XML: 2006 - Evaluation of the standard conformity of selected database systems . Diplomica Verlag, 2010, ISBN 3-8366-9609-6 , p. 100 .
  3. ↑ ISO / IEC 9075 and 13249. International Organization for Standardization, accessed on September 20, 2018 (English).
  4. ↑ Working version of the standard from 2008 ( ZIP ; 12.7 MB)