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.

PROFESORES (

dni : varchar(10),

nombre : varchar(40),

categoria : char(4),

ingreso : date )

Clave primaria: dni

ASIGNATURAS (

codigo : char(5),

descripcion : varchar(35),

creditos : number(3,1),

creditosp : number(3,1) )

Clave primaria: codigo

IMPARTE (

dni : varchar(10),

asignatura :

char(5) )

Clave primaria: (dni, asignatura)

Clave ajena: dni → PROFESORES

Clave ajena: asignatura → ASIGNATURAS

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

select nombre, descripcion

from asignaturas, profesores

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

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)

select * from asignaturas, profesores, imparte

where profesores.dni = imparte.dni

and asignatura = codigo

select nombre, descripcion

from asignaturas, profesores, imparte

where profesores.dni=imparte.dni

and asignatura=codigo

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 real, one of the advantages of using DBMS is that the queries are efficiently processed and it is totally transparent for the user. It is just a way to understand the basic actions represented by each parameter of the select command.

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

This is a correct, unambiguous command.

But if we modify and do not qualify the dni of the projection:

select dni, nombre

from profesores, imparte

where profesores.dni = imparte.dni

This generates an error, the qualification has to be done in the whole order because SQL does not know which table should show the 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

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

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

Page updated

Google Sites

Report abuse