According to Abbreviationfinder, SQL or Structured Query Language is a language that emerged from an IBM research project for accessing relational databases. Today it has become a database language standard, and is supported by most database systems, from personal computer systems to large computers.
Origins and evolution
The origins of SQL are tied to those of relational databases. In 1970 Codd proposed the relational model and associated with it; a sublanguage for data access based on the computation of predicates. Based on these ideas, the IBM laboratories define the SEQUEL (Structured English Query Language) language that would later be widely implemented by the experimental DBMS System R, also developed in 1977 by IBM. However, it was Oracle who first introduced it in 1979. in a commercial show. SEQUEL would end up being the predecessor of SQL, this being an evolved version of the first. SQL became the language par excellence of the various relational DBMS that emerged in the following years and was finally standardized in 1986 by ANSI, giving rise to the first standard version of this language, SQL-86 or SQL1.
The following year this standard is also adopted by ISO. However, this first standard does not cover all the needs of developers and includes storage definition functionalities that were considered to be suppressed. So in 1992 a new expanded and revised SQL standard called SQL-92 or SQL2 is released. Currently, SQL is the de facto standard for the vast majority of commercial DBMS. And, although the diversity of particular add-ons included in the different commercial implementations of the language is wide, support for the SQL-92 standard is general and very broad.
Versions
The ANSI SQL underwent several revisions and additions over time: 1986 SQL-86 SQL-87, first publication by ANSI. Confirmed by ISO in 1987. 1989 SQL-89, minor revision. 1992 SQL-92 SQL2 major revision. 1999 SQL: 1999 SQL3; added regular expressions, recursive queries (for hierarchical relationships), triggers and some object-oriented features. 2003 SQL: 2003 Introduces some XML features, changes in the functions, standardization of the sequence object and the autonumeric columns. SQL: 2006 ISO / IEC 9075-14: 2006 Defines the ways in which SQL can be used in conjunction with XML. It defines ways to import and save XML data to an SQL database, manipulating it within the database, and publishing the XML and conventional SQL data in XML form. In addition, it provides facilities that allow applications to integrate within their SQL code the use of XQuery, an XML query language published by the W3C (World Wide Web Consortium) for concurrent access to ordinary SQL data and XML documents. SQL: 2008 Allows the use of ORDER BY clause outside of cursor definitions. Includes INSTEAD OF type triggers. Add the TRUNCATE statement.
General characteristics
SQL is a database access language that exploits the flexibility and power of relational systems, allowing a great variety of operations on them. It is a high-level or non-procedural declarative language that, thanks to its strong theoretical base and its orientation towards managing sets of records, and not individual records, allows high productivity in coding. In this way, a single statement can be equivalent to one or more programs that use a low-level, registry-oriented language.
Types of SQL statements and their syntactic components
As its name suggests, SQL allows us to query the database. But the name falls short since SQL also performs functions of definition, control and management of the database. SQL statements are classified according to their purpose, giving rise to three ‘languages’ or sublanguages:
The DDL (Data Definition Language)
The DDL (Data Definition Language), data definition language, includes commands to define, modify or delete the tables in which the data is stored and the relationships between them. It is the one that varies the most from one system to another.
The DCL (Data Control Language)
The DCL (Data Control Language), data control language, contains useful elements to work in a multi-user environment, in which data protection, table security and the establishment of access restrictions are important, as well as elements to coordinate data sharing by concurrent users, ensuring that they do not interfere with each other.
The DML (Data Manipulation Language)
The DML (Data Manipulation Language), data manipulation language, allows us to recover the data stored in the database and also includes commands to allow the user to update the database by adding new data, deleting old data or modifying previously stored data..
PLSQL (Programmatic SQL)
PLSQL (Programmatic SQL: Defines a cursor for a query. DECLARE: Opens a cursor to retrieve query results. OPEN Retrieves a row of query results, FETCH, CLOSE: Closes a cursor.)
Syntactic components
Most SQL statements have the same structure. They all begin with a verb (select, insert, update, create), followed by one or more clauses that tell us the data with which we are going to operate (from, where), some of these are optional and others are mandatory, such as the from case.
Functionality
SQL provides rich functionality beyond simply querying (or retrieving) data. It takes on the role of Data Definition Language (LDD), View Definition Language (LDV), and Data Manipulation Language (LMD). It also allows the granting and denial of permissions, the implementation of integrity restrictions and transaction controls, and the alteration of schemas. The earliest versions of SQL included Storage Definition Language (LDA) functions but were dropped in more recent standards in order to keep the language only at a conceptual and external level.
How to use
The SQL basically allows two modes of use:
An interactive use
An interactive use, primarily intended for casual or advanced end users, where the various SQL statements are written and executed on the command line, or similar environment.
An integrated use
An integrated use, intended for use by programmers within programs written in any host programming language. In this case, SQL takes on the role of a data sublanguage.
Alternative programming techniques
In the case of making an embedded use of the language, we can use two alternative programming techniques.
- In one of them, in which the language is called static SQL, the sentences used do not change during the execution of the program.
- In the other, where the language is called dynamic SQL, there is a total or partial modification of the sentences during the execution of the program.
The use of dynamic SQL allows greater flexibility and greater complexity in the sentences, but as a counterpoint, we obtain lower efficiency and the use of more complex programming techniques in memory and variable handling.
Optimization
As already said above, and as is usually common in database access languageshigh-level, SQL is a declarative language. In other words, it specifies what is wanted and not how to achieve it, so a statement does not explicitly establish an order of execution. The internal order of execution of a sentence can seriously affect the efficiency of the DBMS, which is why it is necessary for it to carry out an optimization before the execution of the sentence. Many times, the use of indexes speeds up a query statement, but slows down the updating of the data, depending on the use of the application, indexed access or a quick update of the information will be prioritized. Optimization differs significantly for each database engine and depends on many factors. There is a SQL extension known as FSQL (Fuzzy SQL, Fuzzy SQL) that allows access to fuzzy databases, using fuzzy logic. This language has been implemented on an experimental level and is evolving rapidly.
Language of definition of data
The data definition language, in English Data Definition Language (DDL), is responsible for modifying the structure of database objects. There are four basic operations: CREATE, ALTER, DROP, and TRUNCATE.
Create
This command creates an object within the database. It can be a table, view, index, trigger, function, procedure or any other object that the database engine supports. Example 1 (creating a table): CREATE TABLE TABLE_NAME (my_field1 INT UNSIGNED, my_field2 VARCHAR (50), my_field3 DATE NOT NULL, PRIMARY KEY (my_field1, my_field2))
Alter
This command allows you to modify the structure of an object. You can add / remove fields to a table, modify the type of a field, add / remove indexes to a table, modify a trigger, etc. Example 1 (add column to a table): ALTER TABLE TABLE_NAME (ADD NEW_FIELD INT UNSIGNED)
Drop
This command removes an object from the database, it can be a table, view, index, trigger, function, procedure or any other object that the database engine supports. It can be combined with the ALTER statement. Example 1: DROP TABLE TABLE_NAME Example 2: ALTER TABLE TABLE_NAME (DROP COLUMN FIELD_NAME1)
Truncate
This command truncates all the contents of a table. The advantage over the DELETE command is that if you want to delete all the contents of the table, it is much faster, especially if the table is very large, the disadvantage is that TRUNCATE only works when you want to delete absolutely all the records, since that the WHERE clause is not allowed. Although, at first, this statement would appear to be DML (Data Manipulation Language), it is actually a DDL, since internally, the truncate command drops the table and recreates it and does not execute any transactions. Example 1: TRUNCATE TABLE TABLE_NAME