Alias name can be used in such cases like more than a table is used in the select statement, naming a derived column or table, etc. However the usage seems to be different among the RDBMSs. Consider the following examples which are valid in SQL Server

select t.* from table1 as t

select sum(t.amt) as amt from
(
select amt from table1
union all
select amt from table2
) as t

In the above examples the alias name is preceded by the keyword as. But when you use ORACLE alias names cannot be preceded by as for table names (although it is valid for column names). So in ORACLE the above code should be as follows

select t.* from table1 t

select sum(t.amt) as amt from
(
select amt from table1
union all
select amt from table2
) t

But note that SQL Server supports with/without the keyword as. It is helpful to know the syntax difference of different RDBMSs