1. A little history
SQL is a language that was developed in the 60s, on the theoretical bases of Dr Codd, with the aim of dialoguing with large databases. The first usable versions were developed by IBM and Oracle (at the time Relational Software) in the 1970s. It was adopted as an ISO standard in 1987, and since several revisions have followed: SQL-89, SQL-92 , SQL-99, SQL: 2003 and SQL: 2008. The version we are currently living under is SQL: 2011, adopted in December 2011.
There is a notable difference between traditional languages (Fortran, Pascal, C, C ++, Java) and SQL. These languages make it possible to describe calculation and processing procedures. We therefore tell the processor what to do, and in what order. What is indicated to the processor is therefore how a calculation or a treatment should be carried out. These languages are called procedural languages, quite simply because they describe procedures.
SQL is a non-procedural language. An SQL command does not explain how the processing should be carried out, but what the result should be. The procedure to be followed to determine the good result is the responsibility of the server software, the SQL programmer does not have a priori to worry about it. In fact, it does care, in particular for reasons of optimization and computation time. Among other things, server software is free to use all the optimization procedures it wants.
Despite standardization efforts, the various players in the database market have developed their own SQL, and there remain notable differences between the servers available on the market. These differences can appear at three levels:
- The server does not support some features, and the corresponding SQL statements have not been implemented;
- The data types do not always correspond in syntax from one server to another, or some servers support different data types, or even have their own declaration syntax (this is particularly the case for dates);
- Some functions simply do not have the same syntax from server to server.
2. Organization of the presentation
We generally divide SQL commands into four parts:
- The commands which can act on the data structure: tables, columns, etc … This part of the language is called DDL (Data Definition Language)
- The commands which make it possible to query the database, to select data, to sort them, to classify them, to create some, to modify them, etc … These commands are grouped together in what is called the DML ( Data Manipulation Language).
- The commands that allow you to act on transactions, called TCL (Transaction Control Language). We will see in detail what a transaction is, and how we can act on it.
- Finally, the commands used to manage data security, DCL (Data Control Language): user creation, data rights assignments, etc.
All these different notions will be detailed in the following chapters.
Finally, note that despite the efforts to standardize the SQL language and the concepts it handles, each editor offers elements that are specific to it. We will therefore try to limit ourselves as much as possible to what is standardized, and to cite the elements specific to the different data servers.