Re: Renaming operation in SQL

The AS operator is part of the SQL-92 Standard. It can be used to give
a name to scalar expressions or to tabular expressions, which is
important because SQL can references schema objects only by name and
not by location. Let's look at both uses:

SELECT emp_name, (salary + commssion) AS total_pay
FROM Personnel
ORDER BY total_pay DESC;

The ORDER BY clause in part of a CURSOR, which gets the result set of
the SELECT ...FROM statement. Without the AS operator there is no way
to refer to the (salary + commssion) column for sorting.

SELECT AVG(tot_salary)
FROM (SELECT dept, SUM(salary)

FROM Personnel
GROUP BY dept) AS DeptTotal (dept, tot_salary);

The innermost query result and its columns are given names so that the
containing query can process them. Otherwise, you would have to use a
VIEW:
BEGIN
CREATE VIEW DeptTotal (dept, tot_salary)
AS SELECT dept, SUM(salary)

FROM Personnel
GROUP BY dept;

SELECT AVG(tot_salary)
FROM DeptTotal;
DROP VIEW DeptTotal;
END;
Yes, some SQL products -- not all -- can use position numbers such as
"ORDER BY 1" to do GROUP BY or ORDER BY on a column. This is a
deprecated feature in SQL-92 and was dropped from SQL-99.