SQL is used to manipulate the data in the database and query the database. It is also used to define and change the structure of the data--in other words, to implement the data model. This you already know from the previous chapters.
In general, SQL consists of three parts:
- Data definition language (DDL)
- Data manipulation language (DML)
- Data control language (DCL)
The first part is used to create and manage the structure of the data, the second part is used to manage the data itself, and the third part—to control access to the data. Usually, the data structure is defined only once and then it is rarely changed. However, data is constantly inserted into the database, changed, or retrieved. For this reason, DML is used more often than DDL.
SQL is not an imperative programming language, which makes it different from many other languages. To be more specific, one cannot define a detailed algorithm of how the data should be processed. This might make an impression of lack of control of the data. When using imperative languages, the developer usually specifies it on a very detailed level: where to take the data from and how to do it, how to iterate through the array of records, and when and how to process them. If it is necessary to process the data from multiple sources, the developer should implement the relationship between them in the application layer rather than in the database.
SQL, in contrast, is a declarative language. In other words, to get the same result in other languages, the developer writes a whole story. In SQL, the developer writes only one major sentence and leaves details for the database. Developing SQL statements, one just defines the format in which it is needed to get the data from the database, specifies the tables where the data is stored, and states the rules to process the data. All the necessary operations and their exact order and actual algorithms to process the data are chosen by the database, and the developer is not supposed to care about it.
However, this black-box behavior should not be treated as something bad. First, the box is not completely black: there are ways to find out how the data is processed by the database engine, and there are ways to control it. Second, the logic in the SQL statement is very deterministic. Even if it is not clear how the database is processing the query on a low level, the logic of the process and the result of the query is entirely determined by the SQL statement.
This determines the size of a statement (smallest standalone element of execution). In Java, for example, every operation such as assigning a value to a variable is logically processed as a separate item of an algorithm. In contrast, the logic of SQL implies that the whole algorithm is executed all at once, as one statement. There is no way to get the state of the data at any intermediate step of the execution of the query. This does not limit the complexity of the query though. It is possible to implement any sophisticated algorithm in a single SQL statement. Usually, it takes less time to implement complex logic in SQL than in any lower-level language. Developers operate with logical relational data structures and do not need to define their own algorithms of data processing on a physical level. This is what makes SQL so powerful.
Another good thing about SQL is that there is a standard for the language and every modern relation database supports SQL. Although different databases could support different features and implement their own dialect of SQL, the basics of the language are the same. PostgreSQL also has its own SQL dialect, and we will point out some differences to the other RDBMS. By the way, at the beginning of its history, postgres did not support SQL. It was added in 1994 and, after a while, the database was renamed PostgreSQL to indicate this fact.