A database is a set of interrelated information that represents a particular information system, and it is composed of relationships, commonly known as tables, that store the data referring to an object or an interrelation between objects.
Therefore, if we want to manage academic information using a Database Management System (DBMS) , we have to create(in this particular case) a database that comprises three tables: PROFESORES, ASIGNATURAS and IMPARTE. Each table have its columns, that represents the corresponding attributes of the entity or the foreign keys that allows relationships between tables. The DB that manages this information will be called Ejemplo, and its tables are shown in the next frame.
PROFESORES ( dni : varchar(10), nombre : varchar(40), categoria : char(4), ingreso : date )
IMPARTE ( dni : varchar2(10), asignatura : char(5) )
Primary key: (dni, asignatura)
Foreign key: dni → PROFESORES
Foreign key: asignatura → ASIGNATURAS
Extensions of Ejemplo:
MySQL uses the concept of databases as independent contents, therefore in order to access to a particular table two ways are possible:Adding the name of the database to the name of the table: select * from ejemplo.profesores
Selecting the database first: use ejemplo
Second option allows to perform queries without specifying the database that contains these tables.
As a rule, using more than one table implies the need of a relationship between these tables by a column that they share in common. In this case, dni from profesor, for the relationship between imparte and profesor, and código from asignatura, for the relationship between asignatura and imparte. However, notice that in the table imparte the code of the asignatura is called asignatura and in the table asignaturas is called código. Actually, these attributes are common because they have the same domain and therefore, they can be compared. Domains are defined by the possible types that the DBMS provides.
Data types in the scheme of the DB for every column in each table determines the possible values that these columns can have. They are very important when two tables are related in a Select sentence, since only identical data types columns can be compared, and also when manipulating data, due to the fact that each data type presents specific requirements for its manipulation
Some of the data types that can be found in MySQL are:
In order to query a database, the select order of SQL will be used. With the syntax explained in the previous section, we can be able to formulate all possible requirements (queries) of the tables that compound a specific DB. In this moment, we will see the minimum "select" expression, formed by two clauses, select and from, compulsory elements in every query we made.
The following SQL statement will select all the records in "profesores" table:
select * from profesores
When using asterisk, the DBMS returns all the columns of the table.
select categoria from profesores
Now, we only obtained the "nombre" and "categoria" columns, avoiding the other columns in the output. Therefore, it is possible to require as many columns as we want if they are defined in the table.
select nombre, categoria from profesores
If we want to eliminate duplicates the modifier distinct can be used.
select distinct categoria from profesores
With the statement select-from we obtain the information of the columns of the table required. If we only want to select the rows that fullfil an specific condition, the clause where should be used.
These conditions can use logical conectors such as AND, OR, and NOT, as well as brackets to modify the left-to-right evaluation. Besides, the comparing operators can be also used: >, <, >=, <=, <>, !=. When in doubt, consult the table of precedence of operators, which in the case of MySQL can be found here: is not the same "A and B or C" than "A and (B or C)."
The ORDER BY keyword is used to sort the result-set by a specified column. By default, it sorts the records in ascending order, but it can be done in descending order also.
select creditos, descripcion
The previous result is in ascending order. If we specified the DESC keyword, the result is in descending order (also ASC keyword can be used to ascending ordering)
select creditos, descripcionfrom asignaturaswhere creditos > 4.5order by creditos DESC
More complex ordering criteria can be applied. The next example is performing an ordering by two attributes: when two values are the same for the first attribute, the ordering must be determined by the second one. Notice that is possible to combine the ordering criteria with different data types.
select creditos, descripcion
The relational DBs work with an special value called NULL, that represents missing "unknown" data. . NULL is not the same as "empty string" or "blank"; these ones are concrete values that belong to the character strings, and it is not the same as NULL.
If we have the query "subjects without practical credits" all these possible solutions can return void or erroneous results.
The good one is shown next. What we are really looking for is
There is a subject with no practical credits indeed. This is due to the fact that, in the DB loading, the NULL value was introduced in the creditosp column of the subject HI.
Notice that even the common comparison operator ("equal" symbol) is not possible, you have to use the IS NULL or IS NOT NULL operator.
It is possible to state constants in the select statement, having this constant value in all the rows.
select 'La asignatura ', descripcion, ' tiene ', creditos, ' créditos'