This SQL tutorial focuses on the SQL Column Aliases in Oracle, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts explaining how to write basic queries in Oracle. To read additional posts regarding this subject, please use the following links:
- Writing Basic SQL SELECT statements
- SQL Arithmetic Operators
- SQL Column Aliases
- SQL String Concatenation
- SQL DISTINCT keyword
Oracle Column Aliases
In Oracle by default the column’s heading in the results produced by your query, is the original column’s name as it was defined in the table.
SELECT product_name FROM products product_name ---------- Coffee Tea
In many cases, column’s heading is not descriptive enough. In addition different operations, such as: string concatenation or mathematical calculation, will change the column’s heading to be even less readable and more difficult to understand:
SELECT 'Hello ' + product_name FROM products 'Hello' + Product name ---------------------- Hello Tea Hello Coffee
Oracle Column Aliases are used to change column’s heading. Using the Oracle Column Aliases the headings will be more easy to understand and more meaningful. To create an Oracle Column Alias use the following syntax :
SELECT column_name AS "Column_Alias" FROM table_name
For Example :
SELECT product_name, unit_price, unit_price + 500 AS "NEW_UNIT_PRICE" FROM products
The result:
product_name unit_price NEW_unit_price --------- ---------- ------------------ Tea 30 530
You can also write the Oracle column’s alias without the AS Keyword, for example:
SELECT product_name, unit_price, unit_price + 500 "NEW_UNIT_PRICE" FROM products
The result:
product_name unit_price NEW_UNIT_PRICE --------- ----------- ------------------ Tea 30 530
It is also possible to write the Oracle column’s alias without the enclosing double quotes (” “):
SELECT product_name, unit_price, unit_price + 500 new_unit_price FROM products
The result:
product_name unit_price NEW_UNIT_PRICE ----------- --------- ------------------ Tea 30 530
- When an Oracle column alias is written without enclosing double quotes (” “), it is not possible to write the alias as two (or more) separate words; you will have to insert some character between these words, underline (_) is most commonly used in these cases.
-- This query will generate an error -- The "New Unit Price" column alias should be enclosed within double quotes SELECT product_name, unit_price, unit_price * 1.1 AS New Unit Price FROM products -- Insert an underline between these words : SELECT product_name, unit_price, unit_price * 1.1 AS New_Unit_Price FROM products -- Or use double quotes : SELECT product_name, unit_price, unit_price * 1.1 AS "New Unit Price" FROM products
- In order to make your Oracle query more readable, always use the AS keyword, always use double quotes (” “), and when your alias consists of two words or more, always ensure that you insert an underline between them.
- Another common approach to handle an alias consists of two words or more, is to concatenate the words, and capitalize the first letter of each word :
SELECT product_name, unit_price, unit_price + 500 AS 'NewUnitPrice' FROM products