Due to increase in the number of data, need for its storage and processing there was a demand to create a standard database language, which could operate in many different types of computer systems. Indeed, it allows users to manipulate the data regardless of whether they operate on a personal computer, a network workstation or a mainframe. One of the languages that have resulted from the development of the relational data model is the SQL – structured query language. Nowadays, it is widely spread and, in fact, has become the standard language of relational databases. The following paper will discuss the notion of SQL, its functions and advantages for users.
Structured Query Language: General Notion
The standard for the SQL was produced by the American National Standards Institute (ANSI) in 1986. In 1987, the International Standards Organization (ISO) adopted it as international. “Structured query language is a database programming language used to create and maintain relational databases” (Allison, 2004, p. 4). Structured query language is focused on operations with the data presented in the form of logically interrelated sets of tables. “It is used to create tables, sort data, retrieve data, update data and to set security settings” (Allison, 2004, p. 4). The language is oriented more at the final processing result of data rather than a procedure of this processing. SQL determines where the data is located, which indexes and even the most effective sequence of operations should be used to obtain them. It is not necessary to specify these details in a database query. The implementation of the concept of operations oriented at the tabular presentation of data created a compact language with a small (less than 30) set of proposals. SQL can be used both as an interactive (for queries execution) and as built-in (for building of software applications).
The main language constructs are:
- Proposals of data definition (definition of databases, as well as identification and elimination of tables and indexes).
- Quests for data selection (proposition SELECT).
- Proposals of data modification (add, delete and update data).
- Proposals of data management (provisioning and cancellation of privileges to access data, transaction management, and others).
In addition, SQL provides the ability to perform in their proposals:
- Arithmetic calculations (including a variety of functional transformations), processing of text strings and implementation of comparison operations of values of arithmetic expressions and texts.
- Ordering of rows and columns, when a person displays the contents of tables for printing or on a display screen.
- Creation of presentation (virtual tables) allowing users to have a look at the data without increasing its volume in the database.
- Memorizing of outputted table contents, multiple tables or views in another table (relational assignment operation).
- Aggregation of data: grouping of data and application of such operations as the mean, the sum, maximum, minimum and the number of elements to these groups.
The Main Functions
In SQL, there are two classes of functions – single-line and group. Single line functions take a single line as input (query or arithmetic expression) and produce one result. This result, as in the case of the concept of function in any programming language, is associated with its name. Single line functions can be of different types: character, numeric, for working with dates or conversion functions. The argument of single line functions can be: a constant set by the user, the value of the variable, a name of the column in the table or an expression. Group function takes a group of lines as input and outputs the same value after processing of this group. The difference in interpretation of the input data determines the difference in the application of these functions. Therefore, single-line functions that can be used as the result of a database query mean receiving of only one row of data. To use the group function, row groups must be first formed from the multi query result, and then the group function is applied to each of them (Stephens & Plew, 2004).
Advantages of SQL
The SQL language is the basis of many databases. It is responsible for the physical structuring and writing data to disk, as well as reading data from the disk. It allows a user to take SQL-queries from other components of the database and user applications. Thus, SQL is a powerful tool that provides users, programs, and computer systems access to information contained in relational databases. The main advantages of the SQL language are as follows:
- Standarcy – using the SQL language in programs is standardized by international organizations.
- Independence of the concrete DBMS. All common DBMS use SQL, as the relational database can be transferred from one database to another with minimal modifications
- The possibility of transferring from one computer system to another. DBMS can be focused on different computer systems. However, applications created with SQL allow the use for both local databases and large multi-user systems.
- Relational basis of language. SQL is the language of relational databases, so it became popular, when relational model of data became widespread. The tabular structure of relational database is well understood and, therefore, the SQL language is easy to learn.
- The ability to create interactive queries. SQL provides users with immediate access to data, while in interactive mode, it is possible to get the query results in a very short time without having to write complex programs.
- The possibility of programmatic access to the database. The SQL language is easy to use in applications that need to access databases. The same SQL statements are used for both interactive and programmatic access. Therefore, parts of the program containing an appeal to the database can be first checked online, and then embedded in the program.
“Another benefit of SQL is that it is an open standard, and there are several open-source versions available” (Jacobson & Jacobson, 2002, p. 163). Any language of operation with databases should provide the user with the following features:
- Create databases and tables with a complete description of their structure.
- Perform basic data manipulation operations, in particular, insertion, modification, and deletion of data tables.
- Do simple and complex queries performing data conversion.
Moreover, the language of operation with databases should solve all the above problems with minimum efforts from the user, and the structure and syntax of its commands should be quite simple and easy to learn. Finally, it should be universal – to meet some recognized standard that allows using the same syntax and command structure in the transition from one database to another. The SQL satisfies almost all these requirements.
The SQL is an example of a language with transforming orientation or a language designed to work with the tables for converting the input data to the desired output form. It only includes commands of definition and data manipulation and does not contain any commands of controlling the course of calculations. These problems must be solved either by using programming languages or job control, or interactively, as a result of actions performed by the user.
By a reason of incompleteness in terms of organization of the computational process, the SQL can be used in two ways. The first one provides interactive work, which consists in entering of the separate SQL-operators by the user from the terminal. The second way consists in the introduction of SQL-operators in programs in procedural languages. The SQL is relatively easy to learn. Since SQL is not a procedural language, it is necessary to specify what information should be obtained. In other words, SQL does not require data access methods. Like most modern languages, it supports free-format recording of operators. It means that when entering, certain elements of the operators are not associated with the fixed position of the screen. The SQL can be used by a wide range of professionals including database administrators, application programmers and many other end-users.
Despite of all the advantages, there are some drawbacks of the SQL. “The biggest drawback of SQL, though, is that it is cumbersome for nonprogrammers to use, making it a technical rather than a business language” (Hillard, 2010, p. 14).
The SQL language is used in other standards and even has an impact on the development of other standards as a tool for determining (the standard Remote Data Access, RDA). Creation of the language has not only fostered the development of the necessary theoretical basis, but also the preparation of successfully implemented technical solutions. It is especially true for query optimization, techniques of data distribution and sale of defenders. Specialized language implementation began to appear. They are designed for emerging markets – the control system in transaction processing (OnLine Transaction Processing, OLTP) and online analytical processing system or a decision support system (OnLine Analytical Processing, OLAP). There are plans for further extensions of the standard, including support for distributed processing, object-oriented programming, as well as extensions of users and multimedia (Jensen & Anderson, 2007).
SQL is the first and only standard language for operation with databases, which is widely spread. Virtually, all major DBMS developers now create their products using the SQL language or with SQL-interface. Huge investments on the part of developers and its users are made in the language. It became a part of the application architecture. Thanks to its advantages, the SQL language is a strategic choice for many large and influential organizations.