 Relational DBMS (rDBMS) | Structured Query Language SQL: A Language for Working with rDBMS | SQL: The Standard ISO IEC 9075 and various Implementations |
As outlined above, rDBMS acts on the data with operations ofrelational algebra like projections, selections, joins, set operations (union, except and intersect) and more. The operations of relational algebra are denoted in a mathematical language that is highly formal and hard to understand for end-users and - possibly also - for many software engineers. Therefore, rDBMS offers a layer above relational algebra that is easy to understand but can be mapped to the underlying relational operations. Since the 1970s, we have seen some languages doing this job; one of them was SQL - another example wasQUEL. In the early 1980s (after a rename from its original nameSEQUEL due to trademark problems), SQL achieved market dominance. And in 1986, SQL was standardized for the first time. The current version isSQL 2023.
The tokens and syntax of SQL are modeled onEnglish common speech to keep the access barrier as small as possible. An SQL command likeUPDATE employee SET salary = 2000 WHERE id = 511; is not far away from the sentence "Change employee's salary to 2000 for the employee with id 511."
The keywords of SQL can be expressed in any combination of upper and lower case characters, i.e. the keywords arecase insensitive. It makes no difference whetherUPDATE, update, Update, UpDate, or any other combination of upper and lower case characters is written in SQL code.
Next, SQL is adescriptive language, not a procedural one. It does not proscribe all aspects of the relational operations (which operation, their order, ...), which are generated from the given SQL statement. The rDBMS has the freedom to generate more than one execution plan from a statement. It may compare several generated execution plans with each other and run the one it thinks is best in a given situation. Additionally, the programmer is freed from considering all the details of data access, e.g.: Which one of a set of WHERE criteria should be evaluated first if they are combined with AND?
Despite the above simplifications, SQL is very powerful. It allows the manipulation of aset of data records with a single statement.UPDATE employee SET salary = salary * 1.1 WHERE salary < 2000; will affect all employee records with an actual salary smaller than 2000. Potentially, there may be thousands of those records, only a few or even zero. The operation may also depend on data already present in the database; the statementSET salary = salary * 1.1 leads to an increase of the salaries by 10%, which may be 120 for one employee and 500 for another one.
The designer of SQL tried to define the language elementsorthogonally to each other. Among other things, this refers to the fact that any language element may be used in all positions of a statement where the result of that element may be used directly. E.g.: If you have a function power(), which takes two numbers and returns another number, you can use this function in all positions where numbers are allowed. The following statements are syntactically correct (if you have defined the function power() ) - and lead to the same resulting rows.
SELECTsalaryFROMemployeeWHEREsalary<2048;SELECTsalaryFROMemployeeWHEREsalary<power(2,11);SELECTpower(salary,1)FROMemployeeWHEREsalary<2048;
Another example of orthogonality is the use of subqueries within UPDATE, INSERT, DELETE, or inside another SELECT statement.
However, SQL is not free ofredundancy. Often there are several possible formulations to express the same situation.
SELECTsalaryFROMemployeeWHEREsalary<2048;SELECTsalaryFROMemployeeWHERENOTsalary>=2048;SELECTsalaryFROMemployeeWHEREsalarybetween0AND2048;-- 'BETWEEN' includes edges
This is a very simple example. In complex statements, there may be the choice between joins, subqueries, and theexists predicate.
Core SQL consists of statements. Statements consist of keywords, operators, values, names of system- and user-objects or functions. Statements are concluded by a semicolon. In the statementSELECT salary FROM employee WHERE id < 100; the tokens SELECT, FROM and WHERE are keywords. salary, employee, and id are object names, the "<" sign is an operator, and "100" is a value.
The SQL standard arranges statements into nine groups:
This detailed grouping is unusual in everyday speech. A typical alternative is to organize SQL statements into the following groups:
Core SQL, as described above, is not Turing complete. It misses conditional branches, variables, subroutines. But the standard, as well as most implementations, offers an extension to fulfill the demand forTuring completeness. In 'Part 4: Persistent Stored Modules (SQL/PSM)' of the standard, there are definitions for IF-, CASE-, LOOP-, assignment- and other statements. The existing implementations of this part have different names, different syntax, and also a different scope of operation: PL/SQL in Oracle, SQL/PL in DB2, Transact-SQL, or T-SQL in SQL Server and Sybase, PL/pgSQL in Postgres and simply 'stored procedures' in MySQL.
| Relational DBMS (rDBMS) | Structured Query Language SQL: A Language for Working with rDBMS | SQL: The Standard ISO IEC 9075 and various Implementations |