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

T02 Queries

Using more than one table

In order to resolve most of the requirements it is necessary to work with information extracted from various tables. The way to specify what tables are necessary is using a list of the table's names in the from clause.

If we select BD Ejemplo, we can look for

Name of the teachers and the description of the subjects they teach

Where is the requested information stored? Looking up the scheme of the database Ejemplo:

  • name (of the teacher) can be found in table PROFESORES and
  • description (of the subject) in ASIGNATURAS.

If we don´t think too much about it, we can trust the DBMS:

select nombre, descripcion 
from asignaturas, profesores

nombre descripcion
EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS
MANUEL PALOMAR DISEÑO Y GESTION DE BASES DE DATOS
RAFAEL ROMERO DISEÑO Y GESTION DE BASES DE DATOS
EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS
MANUEL PALOMAR FUNDAMENTOS DE LAS BASES DE DATOS
RAFAEL ROMERO FUNDAMENTOS DE LAS BASES DE DATOS
EVA GOMEZ FUNDAMENTOS DE LA PROGRAMACION
MANUEL PALOMAR FUNDAMENTOS DE LA PROGRAMACION
RAFAEL ROMERO FUNDAMENTOS DE LA PROGRAMACION
EVA GOMEZ HISTORIA DE LA INFORMATICA
MANUEL PALOMAR HISTORIA DE LA INFORMATICA
RAFAEL ROMERO HISTORIA DE LA INFORMATICA
EVA GOMEZ PROGRAMACION CONCURRENTE
MANUEL PALOMAR PROGRAMACION CONCURRENTE
RAFAEL ROMERO PROGRAMACION CONCURRENTE



Obviously, the previous result does not correspond with the demanded information, we have done a cartesian product between the two tables, combining all the teacher's names with all the subject's descriptions.

In this case, the relationship between teachers and subjects is in  imparte, which has got 2 foreign keys, one associated with the PROFESORES primary key and the other withASIGNATURAS primary key. Think about imparte as a "bridge" that allows linking the information of the first table with the second one:  from profesores to imparte though id of the teacher, and from imparte to asignaturas through the code of the subject.
PROFESORES  dni=dni  IMPARTE  asignatura=codigo  ASIGNATURAS


Therefore, we need to include the table  imparte in the from clause, and specify in the where  clause the conditions to link together the desired rows.

Name of teachers and the description of the subjects they teach
(what we must return): select nombre, descripcion 
(where is the information stored): from asignaturas, profesores, imparte 
(matching foreign keys and primary keys): where imparte.dni = profesores.dni and asignatura = codigo

nombre descripcion
EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS
EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS
RAFAEL ROMERO PROGRAMACION CONCURRENTE



In order to understand better how the results are obtained from a query we could think that the order of execution is

  1. from asignaturas, profesores, imparte (cartesian product)
  2. where profesores.dni = imparte.dni and asignatura = codigo (select)
  3. select nombre, descripcion (projection)
1. 
select * from asignaturas, profesores, imparte

codigo descripcion creditos creditosp dni nombre categoria ingreso dni asignatura
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222 EVA GOMEZ TEU 1993-10-01 21111222 DGBD
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 DGBD
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 DGBD
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222 EVA GOMEZ TEU 1993-10-01 21111222 FBD
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 FBD
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 FBD
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222 EVA GOMEZ TEU 1993-10-01 21333444 PC
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21222333 MANUEL PALOMAR TEU 1989-06-16 21333444 PC
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21333444 RAFAEL ROMERO ASO6 1992-06-16 21333444 PC
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01 21111222 DGBD
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 DGBD
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 DGBD
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01 21111222 FBD
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 FBD
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 FBD
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01 21333444 PC
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21333444 PC
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21333444 PC
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21111222 EVA GOMEZ TEU 1993-10-01 21111222 DGBD
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 DGBD
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 DGBD
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21111222 EVA GOMEZ TEU 1993-10-01 21111222 FBD
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 FBD
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 FBD
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21111222 EVA GOMEZ TEU 1993-10-01 21333444 PC
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21333444 PC
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21333444 PC
HI HISTORIA DE LA INFORMATICA 4.5
21111222 EVA GOMEZ TEU 1993-10-01 21111222 DGBD
HI HISTORIA DE LA INFORMATICA 4.5
21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 DGBD
HI HISTORIA DE LA INFORMATICA 4.5
21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 DGBD
HI HISTORIA DE LA INFORMATICA 4.5
21111222 EVA GOMEZ TEU 1993-10-01 21111222 FBD
HI HISTORIA DE LA INFORMATICA 4.5
21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 FBD
HI HISTORIA DE LA INFORMATICA 4.5
21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 FBD
HI HISTORIA DE LA INFORMATICA 4.5
21111222 EVA GOMEZ TEU 1993-10-01 21333444 PC
HI HISTORIA DE LA INFORMATICA 4.5
21222333 MANUEL PALOMAR TEU 1989-06-16 21333444 PC
HI HISTORIA DE LA INFORMATICA 4.5
21333444 RAFAEL ROMERO ASO6 1992-06-16 21333444 PC
PC PROGRAMACION CONCURRENTE 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01 21111222 DGBD
PC PROGRAMACION CONCURRENTE 6.0 1.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 DGBD
PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 DGBD
PC PROGRAMACION CONCURRENTE 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01 21111222 FBD
PC PROGRAMACION CONCURRENTE 6.0 1.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21111222 FBD
PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21111222 FBD
PC PROGRAMACION CONCURRENTE 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01 21333444 PC
PC PROGRAMACION CONCURRENTE 6.0 1.5 21222333 MANUEL PALOMAR TEU 1989-06-16 21333444 PC
PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21333444 PC


2. 
select * from asignaturas, profesores, imparte 
where profesores.dni = imparte.dni and asignatura = codigo

codigo descripcion creditos creditosp dni nombre categoria ingreso dni asignatura
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222 EVA GOMEZ TEU 1993-10-01 21111222 DGBD
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01 21111222 FBD
PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16 21333444 PC


3. 
select nombre, descripcion 
from asignaturas, profesores, imparte where profesores.dni=imparte.dni and asignatura=codigo

nombre descripcion
EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS
EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS
RAFAEL ROMERO PROGRAMACION CONCURRENTE

To sum up, we can affirm that

  1. from establishes the data source,
  2. where all the objective information, and
  3. select the extraction of the desired information.
IMPORTANT:This is not necessary realone of the advantages of using DBMS is that the queries are efficiently processed and it is totally transparent for the user. 


Attribute qualified names

An attribute qualified name is defined as the one that specifies the name of the table that the column belongs to:

profesores.dni
asignaturas.descripción

It is compulsory using attribute qualified names in case of ambiguity, since various tables in the select have columns with the same name:

profesores.dni
imparte.dni

In any other case it is not necessary

ID and name of the teachers that teach any subject

select profesores.dni, nombre 
from profesores, imparte 
where profesores.dni = imparte.dni

dni nombre
21111222 EVA GOMEZ
21111222 EVA GOMEZ
21333444 RAFAEL ROMERO

Same as before but without the name of the table for dni

select dni, nombre
from profesores, imparte 
where profesores.dni = imparte.dni

Error at Command Line:3 Column:0
Error report:
SQL Error: Column 'dni' in field list is ambiguous

It is also useful when, using more than one table, we want all the columns from one table and only some of the others

select profesores.*, descripcion
from profesores, asignaturas, imparte
where profesores.dni = imparte.dni
and codigo = asignatura 

dni nombre categoria ingreso descripcion
21111222 EVA GOMEZ TEU 1993-10-01 DISEÑO Y GESTION DE BASES DE DATOS
21111222 EVA GOMEZ TEU 1993-10-01 FUNDAMENTOS DE LAS BASES DE DATOS
21333444 RAFAEL ROMERO ASO6 1992-06-16 PROGRAMACION CONCURRENTE

Table alias

select * from tabla alias

A character string following the name of a table in the from clause is consider a temporal table alias.
  • It is recommendable in order to simplify the writing of the select statement and making it more readable.
  • It is compulsory in case of cartesian product of a table with itself.
select p1.nombre, p2.nombre
from profesores p1, profesores p2
where p1.nombre <> p2.nombre

nombre nombre
MANUEL PALOMAR EVA GOMEZ
RAFAEL ROMERO EVA GOMEZ
EVA GOMEZ MANUEL PALOMAR
RAFAEL ROMERO MANUEL PALOMAR
EVA GOMEZ RAFAEL ROMERO
MANUEL PALOMAR RAFAEL ROMERO



When an alias is defined in the from clause, it replaces completely the name of the table (it is not possible to use both simultaneous in the select or the where)
Nevertheless, it is not necessary to define alias for all the tables in a from clause.

ID and name of the teachers that teach any subject (using table alias)

select p.dni, nombre
from profesores p, imparte i
where p.dni = i.dni 

dni nombre
21111222 EVA GOMEZ
21111222 EVA GOMEZ
21333444 RAFAEL ROMERO



Ranges

Expressions such as 10 <= x <= 100 can be built using the range operator BETWEEN. The syntax of the subexpression of the where clause is:

expression [NOT] BETWEEN expression AND expression

Credits and description of the subjects whose number of credits is between 5 and 8.

select creditos, descripcion
from asignaturas
where creditos between 5 and 8

creditos descripcion
6.0 DISEÑO Y GESTION DE BASES DE DATOS
6.0 FUNDAMENTOS DE LAS BASES DE DATOS
6.0 PROGRAMACION CONCURRENTE


Lists

Using the IN operator is possible to look for a certain value in a list built using constants.

expression [NOT] IN (ValueList)

Description of the subjects FBD and DGBD.

select descripcion
from asignaturas
where codigo in ('FBD', 'DGBD')

descripcion
DISEÑO Y GESTION DE BASES DE DATOS
FUNDAMENTOS DE LAS BASES DE DATOS
Name of the teachers that do not teach HI, FBD or DGBD.

select nombre
from profesores p, imparte i
where p.dni = i.dni
and asignatura not in ('HI', 'FBD', 'DGBD')

nombre
RAFAEL ROMERO

Note that MANUEL PALOMAR, who is not teaching any of the subjects objective in the search, is neither appearing in the result-table because his id is not in the IMPARTE table.

Substrings

It is possible to ask for substrings within character columns. For doing so, the  LIKE (or MATCHES) can be used, with the following syntax:

expression [NOT] LIKE 'string'

The character string admits the % and _ (underlined):
  • % indicates a string of any length (Ali% = Alicante, Aligerar, Ali, ...)
  • _ any character (Ali_ = Alic, Alig, Ali, ...)

Teachers that respond to the name 'RAFA'.

select * from profesores where nombre like 'RAFA%'

dni nombre categoria ingreso
21333444 RAFAEL ROMERO ASO6 1992-06-16


 Code of the 'Database' subjects

select codigo from asignaturas where descripcion like '%BASES DE DATOS%'

codigo
DGBD
FBD


Code of the subjects, with a two characters code

select codigo from asignaturas where codigo like '__'

codigo
FP
HI
PC


Description of the subjects whose last word contains 'INFORMATIC' and an extra character

select descripcion from asignaturas where descripcion like '%INFORMATIC_'

descripcion
HISTORIA DE LA INFORMATICA

Nested queries

Sometines, the filtering conditions for rows or the aggregation calculus are more complex than a simple comparison with a constant or a value stored in a table. This intermediate data, not the final result, has to be obtained with an auxiliar query, known as subquery. 

In the filtering condition of the select statement (in where or having clause) we can also:

    * compare an expression with the result of another select statement
    * determine if the value of an expression is included in the results of another select statement
    * ask if a select statement has results


Description and credtis of the subjects with less credits.

select descripcion, creditos
from asignaturas
where creditos = ( select min(creditos) from asignaturas )
descripcion creditos
HISTORIA DE LA INFORMATICA 4.5

In a first place, the nested select is calculated (between brackets)  and the minimum value for column creditos in asignaturas table is obtained. This value is compared with every row and the subject (or subjects) with a credit value like the minimum is (or are) returned. 

List of values: inclusion

expr [NOT] IN (orden select)

It is also possible to ask if a value is in the list of values returned by the subquery.

Obtain all the data of the teachers that teach any subject.

select * from profesores
where dni IN (select dni from imparte)

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

Or in other words: "data of all the teachers whose id appears in imparte table".

In this case (not always happens), it would be the same processing the statement

select p.*
from profesores p, imparte i
where p.dni = i.dni


The utility of this operator would be clearer if we ask just the opposite.

Obtain all the data of the teachers that do not teach any subject.

select * from profesores
where dni NOT IN (select dni from imparte)

dni nombre categoria ingreso
21222333 MANUEL PALOMAR TEU 1989-06-16



Date and time


The management and administration of temporal values is the part of the DBMS less standarized and with more differences between them. Besides, and with the character codification and region features (e. g. date formats), it is part of a set of global parameters of the system that the administrator must understand and take into account depending on the environment where the results will be shown. If it would be part of a set of dinamic web pages, not only the server must be properly configurated but also the http server, the hosted language (e.g. php) or even the clients must be taking into account. It is very probably that the same date, in SQL Developer, shown in a result frame or in an output script will have a different format (the first one is controled by a client program and the second shows the data the same way are sended by the server.

Therefore, here the date issues will be treated in a shallow level and always from the SQL query point of view.

MySQL offers various types related with time:

  • DATETIME     '0000-00-00 00:00:00'
  • DATE     '0000-00-00'
  • TIMESTAMP     00000000000000
  • TIME     '00:00:00'
  • YEAR     0000

What is shown in the previous list gives us an idea of what type of data and result manages every type. Actually, the previous list is a relation of "zero values" that can every type can stored, being their default values. Leaving the type TIMESTAMP aside, that has its own properties and applications, all the types are related and differ from the others in storing limitations and automatic type conversions.

Strings, numbers, dates and times

Teachers that began before 1990.

select *
from profesores
where ingreso < '1990-01-01';

dni nombre categoria ingreso
21222333 MANUEL PALOMAR TEU 1989-06-16

Note that a date type is being compared with a character string. MySQL analyzes the string and determines if it has a appropriate format for the data type and it proccess it if so. In fact, although the generic output of a date is always yyyy-mm-dd, the character string we use for the comparison has some format freedom:

ingreso < '1990@01@01'
ingreso < '1990/01/01'
ingreso < '1990.01.01'
ingreso < '1990:01:01'

In all the cases the results in identical to the preceding. What is not possible is 

select *
from profesores
where ingreso < '01-01-1990'

0 rows selected

And, besides, no error message is generated, just no row is selected. The performance is not what we expect neither

select *
from profesores
where ingreso < 1990-01-01;

0 rows selected

But it is ok with 

select *
from profesores
where ingreso < 19900101;

dni nombre categoria ingreso
21222333 MANUEL PALOMAR TEU 1989-06-16

Another aspect with some freedom is the format of the year:

select *
from profesores
where ingreso < '90-01-01';


dni nombre categoria ingreso
21222333 MANUEL PALOMAR TEU 1989-06-16

However, it is neccessary to take into account that MySQL makes an interpretation of this part of the date, transforming the years in the range 00-69 to 2000-2069, and the 70-99 to 1970-1999. If we need to manage previous or later years, 4 characters must be used.

Date and time functions

In the reference manual of  SQL 5.0 is possible to look for the complete functionality related to time management. Here we show some of them:

  • now(), curdate(), curtime()
  • date_format(), str_to_date()
  • day(), dayofweek(), dayname(), month(), year(), hour(), minute(), second()

Now

now() returns the date and time of the server in datetime format. curdate() y curtime() do the same but with date and time respectively.

select now(), curdate(), curtime();
now() curdate() curtime()
2010-12-03 19:40:17 2010-12-03 19:40:17

Format

Main format functions are date_format() and str_to_date(). One is the opposite to the other: dateformat() transform the date to a certain textual format and str_to_date() a character string to a certain date format. Both use 2 parameters, an expression and a format string.

The format string indicates to the function the aspect that has or we want it has (depending on the function) the data given as the first parameter:

Teachers, with beginning date format of "dd/mm/aaaa".

select dni, nombre, date_format(ingreso, '%d/%m/%Y') ingreso from profesores;

dni nombre ingreso
21111222 EVA GOMEZ 01/10/1993
21222333 MANUEL PALOMAR 16/06/1989
21333444 RAFAEL ROMERO 16/06/1992


Teachers that began before 1/1/1990.

select *
from profesores
where ingreso < str_to_date('1/1/90','%d/%m/%y');.

dni nombre categoria ingreso
21222333 MANUEL PALOMAR TEU 1989-06-16


Therefore, the format string for the first query represents "how we need the output" and the second one represents "how the date I want to compare is written". All the possible codifications can be looked it up in the description of the function date_format() en http://dev.mysql.com/doc/refman/5.0/es/date-and-time-functions.html

Extraction

The other interesting functions are the ones that extract part of a temporal expression.

select day(ingreso) día, month(ingreso) mes, year(ingreso) año
from profesores
where nombre='EVA GOMEZ';

día mes año
1 10 1993


select dayname(ingreso) día, dayofweek(ingreso) ndía, monthname(ingreso) mes
from profesores
where nombre='EVA GOMEZ';

d nd m
Friday 6 October

IMPORTANT: SQL Developer, in its current configuration, it is not interpreting properly the results of  dayname() and monthname(), giving an unappropriate output.

select date_format(now(),'%Y%m%d -- %H:%i:%s');

ahora
20101203 -- 20:12:49


Subpáginas (1): T02B Nested queries
Comments