SQL queries

SQL(Structured Query Language) is a standard relational query language(ANSI and ISO standard).

Its creator is Chamberlin, and it was created at IBM’s IBM Research Laboratory in San Jose, California in 1974, in the same place where Code defined the basic concepts of the relational data model in 1970.

The language was initially called SEQUEL(Structured English Query Language) and was a programming interface (API) for System R, a prototype database management system (SU8P) that was being developed as part of a research project of the same name.

In the late 1970s and early 1980s, the first commercial versions of relational systems appeared, with SQL as the query language. Among them, the most significant are Oracle and two IBM products: SQL / DS and DB2.

The emergence of commercial relational systems has increased the importance and accelerated the process of standardization of relational query language.

The first stage of this process ended in 1986 with the adoption of SQL as a standard relational query language.

This first version of the SQL standard is known as SQL-86. It standardizes the basic characteristics of SQL as a declarative relational query language.

However, many important characteristics of the language remained non-standardized. This led to a revision of the standard adopted in 1989, which standardized the characteristics related to preserving the integrity of the database and linking to classical programming languages.

This version of the SQL standard is known as SQL-89. In 1992, the next major revision of the standard was adopted, known as SQL-92 or SQL2, which rounded out SQL as a programming language, and increased the scope of the standard six times compared to the initial version.

The last revision of the SQL standard was adopted in 1999. This version of the SQL standard is known as SQL: 1999. It includes concepts of object technology, trigger mechanism, recursion, and procedural extensions.

SQL is constantly evolving. In the beginning, it was quite simple, close to the user, and largely declarative (non-procedural).

Today, SQL can be said to be a complex, procedural/declarative language. SQL works with tables. The table is created with a single executable command.

Immediately after creating the table, it is available for use. All data is stored in tables and the result of any operation is logically displayed in tabular form.

The non-procedural nature of SQL is reflected in the fact that it defines WHAT is desired, not HOW it is obtained: which data is desired, which tables are referenced, and which conditions should be met, without specifying the procedure for obtaining the desired data.

In order to increase the functionality of the language, in the SQL: 1999 standard, a procedural upgrade of SQL was introduced, which mainly consists of management structures similar to the management structures of classical programming languages.

In conclusion, according to the SQL-92 standard, SQL statements are classified into three categories:

  • Data Definition Statements,
  • Data Manipulation Statements and
  • commands of the control(management) function(Data Control Statements).

SQL Commands

To reiterate, concluding with the SQL-92 standard, SQL statements are classified into one of the following three categories: Data Definition Statements, Data Manipulation Statements, and Control Functions(Data Control Statements).

Data definition commands allow you to define database objects. Examples of commands in this category are:

  • CREATE TABLE (creating a database table)
  • CREATE VIEW (creating a virtual table – “views”)
  • CREATE INDEX (creating an index over a combination of table columns)
  • ALTER TABLE (change table definition)
  • DROP TABLE (throwing a table out of a database)

Data manipulation commands allow updating and displaying database data:

  • SELECT (display the contents of a relational database)
  • UPDATE (change table column values)
  • DELETE (eject table rows)
  • INSERT (giving rows to an existing table)

Commands for control(management) functions enable recovery, competitiveness, security and integrity of a relational database:

GRANT (granting the right to use your own table to other users)
REVOKE (deprivation of the right to use your own table from other users)
COMMIT (transfer of transaction effects to the database)
ROLLBACK (undo transaction effects)

Categories of SQL statements

SQL: The 1999 standard classifies SQL statements into 7 categories. The main reason for the different classification of commands is the introduction of new concepts in SQL in accordance with the development of information technology and the need to group existing commands more precisely.

The following categories of SQL statements are defined:

  • database schema commands(SQL-Schema Statements), which are used to create, modify and drop schemas and schema objects (CREATE, ALTER, DROP),
  • data commands(SQL-Data Statements) used to display and update database data(SELECT, INSERT, UPDATE, DELETE),
  • transaction commands(SQL-Transaction Statements) used to start, end and set parameters for transactions(COMMIT, ROLLBACK),
  • SQL-Control Statements used to control the execution of a sequence of SQL statements(CALL, RETURN),
  • SQL-Connection Statements used to establish and terminate a SQL connection(CONNECT, DISCONNECT),
  • Session Statements used to set Default values and other SQL session parameters(SET),
  • diagnostic commands(SQL-Diagnostic Statements) that use diagnostic data and signal exceptions in SQL routines(GET DIAGNOSTICS).

SQL: The 1999 standard defines multiple ways to use SQL. The two main ways are direct(interactive) use of SQL and linking SQL to classical programming languages(“embedded” SQL).

The base table, which is the object of the database schema, is called the created base table and can be persistent or temporary. The base table, which is the object of the module, can only be temporary and is called the declared temporary table.

A persistent base table is a named table defined by the CREATE TABLE command without the TEMPORARY keyword.

A global temporary table is a named table defined by the CREATE TABLE command that includes the GLOBAL TEMPORARY determinant.

The created local temporary table is a named table defined by the CREATE TABLE command that includes the LOCAL TEMPORARY determinant.

A declared local temporary table is a named table that is declared as a module component.

Global temporary tables and created local temporary tables are similar to persistent base tables in the sense that their definitions are in the schema and remain in it until they are explicitly removed.

However, unlike persistent base tables, their physical existence only materializes effectively when referenced in an SQL session.

An SQL session is a context in which a single user(person or application) executes a sequence of SQL statements during a single SQL connection. An SQL connection is an association(connection) between an SQL client and an SQL server.

An SQL client is a processor that establishes a connection with an SQL server and is primarily user- or application-oriented. It represents the interface through which requests are forwarded to the SQL server and the results of request processing are accepted.

An SQL server is a processor that manages an SQL session. It accepts requests from the SQL client. executes them and forwards the results to the client.

Each module or “embedded” SQL program, which references the created local temporary table, causes the materialization of different occurrences(instances) of that table in each SQL session.

This means that the contents of the created local temporary table, as well as the global temporary table, unlike persistent base tables, cannot be shared between SQL sessions.

Therefore, the temporary table is empty at the beginning of the SQL session and empty at its end or at the end of the SQL transaction. The contents of the created local temporary table cannot be shared between modules or “embedded” SQL programs of one SQL session.

To conclude, the contents of a global temporary table are unique in an SQL session, the contents of a created local temporary table are unique within a module or “embedded” SQL program of an SQL session, and the contents of a declared local temporary table are unique within a single module or “embedded” SQL program procedure one SQL session.

Leave a comment

Your email address will not be published.