rename operation with select statement

The rename operation with select statement

This is a mechanism used for renaming both relations and attributes, this meaning is highly useful when one has to compare a set of tuples of the same relation with other tuples of the relation. It has the following general format:
Old_name as new_name,
The as clauses can appear in both the select and from clauses. For example if we want the attribute name loan_no to be replaced with the name loan_id, we can write a query as
select distinct cust_name, borrowers.loan-no as loan_no
From  borrowers,loan
Where borrower.loan_no=loan.loan.no AND
Branch_name=”KR CIRCLE”

Tuple variables
To define tuple variables, we use AS clause, Tuple variables are defined in the from clause via the use of the AS clause. A tuple variable in SQL must be associated with a particular relation. To illustrate we write the, query “For all customers who have a loan from the bank, find their names and loan numbers” as
select distinct cust_name, B.loan_no
From  borrowers as B, loan as L
Where B.loan_no=L.loan_no
In order to define a tuple variable, we define the new tuple variable name in the FROM clause by placing it after the original name with the keyword AS in between (the keyword AS is optional). When we write expressions of the form relation attribute name, the relation name is, in effect, an implicitly defined tuple variable.
Tuple variables are most powerful for comparing two tuples in the same relation-Suppose that we want the query ”Find the name of all branches that have assets greater than at least one branch located in MYSORE”. We can write the SQL expression
Select distinct B1.branch_name
From branch as B1, branch as B2

Where B1.assets > B2.assets and B1.br_city  “MYSORE”