Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎

T01 Concept of database and relation

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.

Database: Ejemplo

PROFESORES ( dni : varchar(10), nombre : varchar(40), categoria : char(4), ingreso : date )
Primary key: dni


ASIGNATURAS ( codigo : char(5), descripcion : varchar(35), creditos : number(3,1),
creditosp : number(3,1) )
Primary key: codigo


IMPARTE ( dni : varchar2(10), asignatura : char(5) )
Primary key: (dni, asignatura)
Foreign key: dni → PROFESORES
Foreign key: asignatura → ASIGNATURAS

Extensions of  Ejemplo:

profesores

dni nombre categoria ingreso
21111222 EVA GOMEZ TEU 1993-10-01
21222333 MANUEL PALOMAR TEU 1989-06-16
21333444 RAFAEL ROMERO ASO6 1992-06-16

asignaturas

codigo descripcion creditos creditosp
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
HI HISTORIA DE LA INFORMATICA 4.5  
PC PROGRAMACION CONCURRENTE 6.0 1.5

imparte

dni asignatura
21111222 DGBD
21111222 FBD
21333444 PC








Selection of the working database

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.

Data types

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:

VARCHAR(x)

Character string of variable length with a maximum of x (1<=x<=4000)

CHAR(x)

Character string with a fixed length of n (1<=x<=2000)

INT,INTEGER

Integer numbers

DECIMAL(p,s)

Numbers with precision p and scale s (1<=p<=38) (-84<=s<=127)

DATE

Date types, with format yyyy-mm-dd (year, month and day). Date values must be used in simple quotes.


Select-from

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

dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


When using asterisk, the DBMS returns all the columns of the table.

select categoria from profesores

categoria
TEU
TEU
ASO6


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

nombrecategoria
EVA GOMEZTEU
MANUEL PALOMARTEU
RAFAEL ROMEROASO6


If we want to eliminate duplicates the modifier distinct can be used.

select distinct categoria from profesores

categoria
TEU
ASO6


Where

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.

select nombre
from profesores 
where categoria = 'TEU'

nombre
EVA GOMEZ
MANUEL PALOMAR


These conditions can use logical conectors such as ANDORand NOTas 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)."

select nombre 
from profesores 
where categoria = 'TEU' or categoria = 'ASO6'

nombre
EVA GOMEZ
MANUEL PALOMAR
RAFAEL ROMERO

Order by

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
from asignaturas
order by creditos

creditosdescripcion
4.5HISTORIA DE LA INFORMATICA
6.0DISEÑO Y GESTION DE BASES DE DATOS
6.0FUNDAMENTOS DE LAS BASES DE DATOS
6.0PROGRAMACION CONCURRENTE
9.0FUNDAMENTOS DE LA PROGRAMACION


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, descripcion
from asignaturas
where creditos > 4.5
order by creditos DESC

creditosdescripcion
9.0FUNDAMENTOS DE LA PROGRAMACION
6.0DISEÑO Y GESTION DE BASES DE DATOS
6.0FUNDAMENTOS DE LAS BASES DE DATOS
6.0PROGRAMACION CONCURRENTE


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 
from asignaturas 
order by creditos, descripcion

creditosdescripcion
4.5HISTORIA DE LA INFORMATICA
6.0DISEÑO Y GESTION DE BASES DE DATOS
6.0FUNDAMENTOS DE LAS BASES DE DATOS
6.0PROGRAMACION CONCURRENTE
9.0FUNDAMENTOS DE LA PROGRAMACION

 NULL

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.

select *
from asignaturas
where creditosp = ''
--simple-quote + simple-quoute = empty string

select *
from asignaturas
where creditosp = ' '
--simple-quote + blank + simple-quote = blank_space

select *
from asignaturas
where creditosp = 0

select *
from asignaturas
where creditosp = NULL

The good one is shown next. What we are really looking for is

select *
from asignaturas
where creditosp is NULL

codigodescripcioncreditoscreditosp
HIHISTORIA DE LA INFORMATICA4.5 

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.

Constants

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' 
from asignaturas 
order by creditos

La asignaturadescripciontienecreditoscréditos
La asignaturaHISTORIA DE LA INFORMATICAtiene4.5créditos
La asignaturaDISEÑO Y GESTION DE BASES DE DATOStiene6.0créditos
La asignaturaFUNDAMENTOS DE LAS BASES DE DATOStiene6.0créditos
La asignaturaPROGRAMACION CONCURRENTEtiene6.0créditos
La asignaturaFUNDAMENTOS DE LA PROGRAMACIONtiene9.0créditos