SQL Language: The Standard Language of Relational Databases
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 DLL2 where the user defines the data to be manipulated.
- The DML where the user manipulates the data that is stored.
SQL Components
- Data definition language (LDD): Provides commands for defining relationship schemas, deleting relationships and modifying relationship schemas.
- Interactive Language of Data Manipulation (LMD): Includes a query language that is based on both relational algebra and relational calculation of tuples. Contains commands to insert, delete and modify tuples.
- Integrity: The SQL LDD includes commands that allow you to specify the integrity restrictions that the data stored in the database must meet. Updates that violate integrity restrictions are rejected.
- Definition of views: The SQL LDD includes commands for the definition of views.
- Transaction control: SQL includes commands that are used to specify the beginning and end of transactions.
- Embedded SQL and Dynamic SQL: De fi ne how SQL statements can be incorporated into general-purpose programming languages such as C, C ++, Java, PL / I, Cobol, Pascal and Fortran.
- Authorization: The SQL LDD includes commands to specify access rights to relationships and views.
Definition of Data
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 scheme of each relationship.
- The domain of values associated with each attribute.
- The integrity restrictions.
- The set of indices that must be maintained for each relationship.
- The security information and authorization of each relationship.
- The physical storage structure of each relation on the disk.
Basic Domain Types
The SQL standard supports a wide variety of domain types, among them we have:
- char (n): A string of fixed length characters, with a length n that is defined by the user. We can also use the full word character.
- varchar (n): It is a string of variable length characters with a maximum length n that is specified by the user. The full word character varying can be used.
- int: An integer (a finite subset of integers dependent on the machine). The whole word integer can be used.
- smallint: A small integer (a subset dependent on the machine of the entire domain type).
- numeric (p, d): A fixed point number, whose precision is defined by the user. The number is formed by p digits (plus the sign), and of these p digits, d belong to the decimal part.
- real, double precision: Floating point numbers and double precision floating point numbers, with machine-dependent precision.
- float (n): A floating-point number whose precision is at least n digits.
Basic Definition of Schemas in SQL
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:
- Primary key (Aj1, Aj2, ..., Ajm). The primary key specification determines that the attributes Aj1, Aj2, ...,Ajm form the primary key of the relation. The attributes of the primary key must be non-zero and unique; that is, no tuple can have a null value for an attribute of the primary key and no pair of tuples in the relationship can be the same in all the attributes of the primary key1 . Although the primary key specification is optional, it is usually a good idea to specify a primary key for each relationship.
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.
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:
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
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
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
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.
Basic structure of SQL queries
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.
- The select clause corresponds to the projection operation of the relational algebra. It is used to obtain a list of the desired attributes in the result of a query.
- The from clause corresponds to the Cartesian product operation of the relational algebra. Generates a list of the relationships that must be analyzed in the evaluation of the expression.
- The where clause corresponds to the predicate selection of the relational algebra. It is a predicate that encompasses the attributes of the relationships that appear in the from clause.
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:
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:
As we can see we are showing all the data of the records while the id_autor is between 15 and 26.
- Book: Fundamentals of Database 5th Edition. ABRAHAM SILBERSCHATZ, HENRY F. KORTH, S. SUDARSHAN.
- Book: Introduction to SQL Theory and Practice. Olinto Rodríguez.
I hope you enjoyed the content, I will soon publish the second part. See you soon.
