Additional Basic Operations-The Rename Operation

Consider this query :

select name, course_id
from instructor, teaches
where instructor.ID= teaches.ID;

The result of this query is a relation with the following attributes:

name, course_id

The names of the attributes in the result are derived from the names of the
attributes in the relations in the from clause.

We cannot, however, always derive names in this way, for several reasons:
First, two relations in the from clause may have attributes with the same name,
in which case an attribute name is duplicated in the result. Second, if we used an
arithmetic expression in the select clause, the resultant attribute does not have
a name. Third, even if an attribute name can be derived from the base relations
as in the preceding example, we may want to change the attribute name in the
result. Hence, SQL provides a way of renaming the attributes of a result relation.
It uses the as clause, taking the form:

old-name as new-name

The as clause can appear in both the select and from clauses.

For example, if we want the attribute name name to be replaced with the name
instructor_name, we can rewrite the preceding query as:

select name as instructor_name, course_id
from instructor, teaches
where instructor.ID= teaches.ID;

The as clause is particularly useful in renaming relations. One reason to
rename a relation is to replace a long relation name with a shortened version that
is more convenient to use elsewhere in the query. To illustrate, we rewrite the
query “For all instructors in the university who have taught some course, find
their names and the course ID of all courses they taught.”

select T.name, S.course_id
from instructor as T, teaches as S
where T.ID= S.ID;

Another reason to rename a relation is a case where we wish to compare
tuples in the same relation. We then need to take the Cartesian product of a
relation with itself and, without renaming, it becomes impossible to distinguish
one tuple from the other. Suppose that we want to write the query “Find the
names of all instructors whose salary is greater than at least one instructor in the
Biology department.” We can write the SQL expression:

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ’Biology’;

Observe that we could not use the notation instructor.salary, since it would not be
clear which reference to instructor is intended.

In the above query, T and S can be thought of as copies of the relation instructor,
but more precisely, they are declared as aliases, that is as alternative names, for the
relation instructor. An identifier, such as T and S, that is used to rename a relation
is referred to as a correlation name in the SQL standard, but is also commonly
referred to as a table alias, or a correlation variable, or a tuple variable.

Note that a better way to phrase the previous query in English would be “Find
the names of all instructors who earn more than the lowest paid instructor in the
Biology department.” Our original wording fits more closely with the SQL that
we wrote, but the latter wording is more intuitive, and can in fact be expressed
directly in SQL.

Frequently Asked Questions