When you want the column name of the table to appear differently in your SQL query, just to make sure that you too can do some magic, you can use aliases. Though aliases can also be created for various database objects like tables, views, stored procedures and macros, it is the column aliases that are more frequently used. Aliases are also referred as correlation name. Except for having a better readability, column aliases can also be used anywhere in the SQL statement. It becomes handy especially if you are using a derived column. Let’s discuss what all you need to create an alias. First code example:

SELECT emp_no AS “Employee No”,
 dept_no AS “Department No”,
 salary “Employee Salary”,
 (salary* 12/100) “Salary Bonus”
FROM emp
WHERE “Department No”= 114
GROUP BY   1,2,3,4
ORDER BY “Salary Bonus”

Result:- 

In the above code, you can see that the columns emp_no, dept_no, salary and the derived column (salary* 12/100) are all aliased. Also note that the alias “Department No” and “Salary Bonus” as used within the SQL query and the query returns the shown result without any hiccups. So what are the secrets? Aliases should be from 1 to 30 characters long with the following guidelines: 

• Uppercase or lowercase letters (A to Z and a to z).
• Digits (0 through 9).
• The special characters DOLLAR SIGN ($), NUMBER SIGN (#), and UNDER SCORE ( _ ).
• Must NOT begin with a digit.
• Must NOT begin with an UNDER SCORE.
• NO keywords can be used as aliases.
• Alias Names can not be duplicated in the same SQL query.
• Usage of the word AS is optional.
• If there is a space used, make sure it is enclosed with only double quotes.

Popularity: 47% [?]

A derived column is a result of a calculation (most of the time mathematical) that happens during the SQL query processing and generates a new column which is not part of the original table definition. For example, in our table we have salary column present but when we want to extract a percentage of the salary or portion of the salary, we generally create a column on the fly with the mathematical calculation from the existing salary column.

Suppose in a well managed successful organization, the HR head decided to reduce the salary by 20% of all the employees just to show that he is something. To know the future salary of the employees working in the dept 114, we have to use a derived column as mentioned in the following query.

SELECT emp_no, dept_no, salary “Actual Salary”,
(Salary * 80/100) “Proposed Salary” <== This is a derived Column
FROM emp
WHERE dept_no = 114
GROUP BY 1,2,3,4
ORDER BY 1, 2

Result:-

Popularity: 48% [?]