IBM developed the original version of SQL, which was originally named Sequel as part of the System R project in the early 1970s. The Sequel language evolved ever since and its name became SQL (Structured Query Language) . Today many products are compatible with the SQL language and has become the standard language of relational databases.
SQL consists of a wide range of commands and is designed to handle data stored in the tables of a database. According to its configuration, it is referred to as a non-procedural language since the user only specifies the data that must be extracted but not the way in which they must be manipulated. This language has two qualifications:
The set of relationships for each database must be specified in the system in terms of a data definition language (LDD). The SQL LDD not only allows you to specify a set of relationships, but also information about those relationships, including:
The SQL standard supports a wide variety of domain types, among them we have:
The relationships are defined by means of the create table command::
Where r is the name of the relation, each Ai is the name of an attribute of the scheme of the relation r and Di is the type of domain of the values of the domain of the attribute Ai. There are several valid integrity restrictions.
In this section only the primary key will be studied, which takes the form:
The primary key is the one that the database administrator selects to uniquely identify the records. A database can have many primary keys but there can only be one primary key per table. In some cases a table can have a set of attributes that unambiguously identify the records in a table and are called candidate keys. From these attributes, the database administrator can choose which of these attributes would be the primary key, and by selecting it the rest of the attributes that unambiguously identify the records in the table are called secondary keys.
Figure 1. Definition of the Author table for part of the database of a library.Own source
Here we can notice that this table contains 3 attributes of which the author_id is the attribute that unambiguously identifies the records of a table, that is, it represents it univocally therefore it is selected as the primary key.
If a newly inserted or newly modified tuple of a relation contains null values for any of the attributes that are part of the primary key, or if it has the same value in them as another tuple of the relation, SQL noti fi es the error and prevents the update.
The newly created relationships are initially empty. The insert command is used to add data to the relationship. For example, if we want to add the data of an author in its corresponding table we must write the following:
Figure 2. Insertion of data in the author table.Own source
The values are specified in the order in which the corresponding attributes are related in the scheme of the relationship. Data that is char type is written inside single quotes ('').
You can use the delete command to delete tuples from a relationship. The command
delete from autor
This would erase all tuples of the account relationship. Other forms of the delete command allow you to delete only specific tuples. To eliminate a relationship from a SQL database, use the drop table command. This command removes all the information from the database from the database. The drop table r statement is a more drastic action than delete from r.
The last one keeps the relation r, but erases all its tuples. The first not only clears all tuples of the relation r, but also eliminates its schema. Once r is removed, no tuple can be inserted in that relation, unless it is recreated with the create table statement.
The alter table command is used to add attributes to an existing relationship. All tuples in the relationship are assigned a null value as the value of the new attribute. The form of the alter table command is
alter table r add A D
where r is the name of an existing relationship, A is the name of the attribute that you want to add, and D is the domain of the added attribute. You can remove attributes from a relationship by using the command
alter table r drop A
where r is the name of an existing relationship and A is the name of an attribute of the relationship. Many database systems do not allow the deletion of attributes, although they do allow the deletion of complete tables.
The relational databases are formed by a set of relationships (tables), to which a unique name is assigned. Each relation allows the use of null values to indicate that the value is unknown or does not exist. It also allows the user to specify attributes that cannot contain null values. The basic structure of an SQL expression consists of three clauses: select, from and where.
Next, these 3 clauses will be explained in a better way:
The Select clause:
The select clause is to retrieve a set of tuples from a relation that meets a given condition. The result of SQL queries is a relation. Consider the simple query based on the example of the library "Get the name of all authors of the author relationship":
In this query we are looking for the records of a specific attribute, if we wanted to search the records of all the attributes of the table we would put an asterisk (*) after the selection.
Below I will present an example of this query with a database that I made for my final project of this subject, it is a database of a library:
Figure 3. Records of the attribute name of the author table.Own source
The clause from:
The from clause defines by itself a Cartesian product of the relationships that appear in the clause. Since the natural meeting is defined in terms of a Cartesian product, a selection and a projection, it is relatively easy to write an SQL expression for the natural meeting.
The Where clause:
Consider the query "Get all the names of the authors whose name is 'Alejandro'". This query can be written in SQL as:
SQL uses the logical connectives and, or and not (instead of the mathematical symbols ^, V, and ~ :) in the where clause. The operands of the logical connectives can be expressions that contain the comparison operators <, ,> =, = and . SQL allows you to use comparison operators to compare strings and arithmetic expressions, as well as special types, such as date types. SQL also includes a comparison operator between to simplify the where clauses, which specifies that a value is less than or equal to one value and greater than or equal to another value.
Below is an example of these conditions:
Figure 4. Use of between.Own source
As we can see we are showing all the data of the records while the id_autor is between 15 and 26.
Bibliographic References
