What is SQL
SQL stands for “ Structured Query Language "Or what is the same, structured query language . SQL is a declarative programming language that is used to make queries and change data in relational databases (later we will explain what the relational model is). Therefore, this language allows us to interact through code with large databases and extract from them the data that is really of interest to us.
Since the 70s it has been the most used programming language in databases used in large organizations such as banks.
SQL is a language with a short learning curve since the sentences to write to obtain data are very similar to the language spoken in English.
SQL is a standard language defined by ANSI standards. In this way, this language can be used by different managers or DBMS, such as MariaDB, PostgreSQL, Mysql or Oracle SQL.
Furthermore, multiple languages accept its use within the language itself. This allows you to interact directly with the database using Python, Java or PHP through SQL statements.
What is the purpose of learning to program SQL?
Knowing how to program SQL is equivalent to having complete control of relational databases. This has many advantages today since we are in the information age, and data is the most important thing we have. This data must be saved somewhere for later analysis. And guess where they are kept. In database!
Therefore, we have millions and millions of data saved for analysis. But to be able to observe and analyze them it is important to know how to handle them since a large amount of data without knowing how to use it is like having nothing.
This is where the difference is made between a person who masters the syntax of SQL and another who has no idea.
All large technology companies such as Facebook, Google, Microsoft or Netflix use this type of language (among others) to store all the user's information and use them when necessary to train their artificial intelligence and provide the consumer with what they need at all times.
Therefore, learning this relational database language will open doors for you both in your own projects and in finding work. Later in this publication we will give you the first steps to take to be able to learn SQL quickly and efficiently.
Relational databases
In 1970, the relational model emerged from the IBM laboratory. Este It consisted of placing the data in different tables that were joined together through a column present in both. For example, in a bank we could have a table with the information of the bank's clients where each user has a unique identifier and another table with the different transactions that each user has made. These two tables can be joined through operations called JOIN, and extract a single table.
In short, in the relational type of database we have different tables that have a specific structure and that are joined together with different types of relationships (later we will go into more depth on this type of data models).
This differentiates them from NoSQL or non-relational databases. Non-relational databases do not have a specific structure, data is simply stored in what are called collections, and each of the data is located through a unique key. Therefore, in NoSQL databases there are no relationships.
SQL Features
- Data integrity – This term refers to the completeness and correctness of the data in the database. When information is updated or added, there may be errors that cause a change in the data. This system allows you to avoid this type of problems.
- Simple query language : Relational databases use the SQL language, very similar to natural language, allowing easy interaction with the database. In addition, it is a common language for most relational database managers so we can go from one to the other without problems.
- Great community : SQL databases have been with us for a long time. This has allowed a large community to exist around this language. This greatly helps your learning and implementation since we can consult with any problem and find an effective solution.
Examples of management systems that use SQL
- PostgreSQL
- mysql
- Oracle SQL
- Microsoft SQL
Types of SQL commands
The different commands that exist in SQL to manipulate and extract the different tables of a relational database can be classified into five categories: DDL (Data Definition Language), DQL (Data Query Language), DML (Data Manipulation Language), DCL (Data Control Language) and TCL (Transaction Control Language).
- DDL : These types of commands are used to define and change the schema of database tables. Some examples of these commands are: CREATE, ALTER, DROP, TRUNCATE, COMMENT or RENAME.
- DQL : These commands are used to select and extract certain data of interest. The main example is the SELECT statement.
- DML : Data changes and updates are responsible for DML commands. The main examples are: INSERT, UPDATE and DELETE.
- DCL : Control of database permissions is carried out through DCL commands. Some examples of these are GRANT and REVOKE.
- TCL : Database transactions are done with commands such as ROLLBACK, COMMIT or SET TRANSACTION.
This type of transactional operations must ensure that four characteristics of the ACID acronym are met. (Atomicity, Consistency, Isolation and Durability.
The atomicity refers to the property that, if an operation consists of a sequence of steps, all or none of them must be carried out.
The consistency It ensures that any operation performed on the database will maintain its consistency and the final state will be valid, without errors.
H.E. isolation allows independence of operations. Therefore, if two transactions are working with the same data, they cannot affect each other.
The durability It allows the persistence of the transactions carried out, preventing their information from being lost due to errors in the database system.