Aliases, Literals and Concatenation

Working With Aliases:
Ø  An Alias is an alternate name given for any oracle database object.
Ø  Aliases in oracle are of two types.
Column Alias:
Ø  Column Alias renames a column heading in a query.
Ø  The column Alias is specified in the SELECT list by declaring the alias after the column name by using the space separator.
Ø  Alias heading appears in UPPER casing by default.
Ø  The alias should be declared in double quotes if it is against the specifications of naming conversions of oracle.
Ø  The AS keyword can be used between the column name and Alias
Ø  An Alias effectively renames the SELECT list item for the duration of that query only.
Ø  An alias cannot be used, anywhere in the select list for operational purpose.
Table Alias:
Ø  Table Alias renames the original name of the table in a SQL statement.
Ø  Table Aliases are very important when working with self joins.
Ø  The table Alias is applied for the current SQL statement only.
Ø  It is an important source when implementing the standards of merge statements, correlated queries and analytic functions.
Ex:
  • Select Empno Numbers, Ename name, Sal “Basic salary”, Job Designation From Emp;
  • Select Deptno as “Department ID”, Dname as “Department Name”, Loc as Place From Dept;
  • Select Hisal as “Maximum Range”, Losal as “Minimum Range”, Grade From Salgrade; 
 Literals:
Ø  A Literal and a Constant value are synonyms to one another and refer to a fixed data value.
Ø  The types of literals recognized by Oracle are…
o   Text literals
o   Integer literals
o   Number literals
o   Interval literals
Text literals:
Ø  It specifies a text or character literal.
Ø  It is used to specify values whenever ‘TEXT’ or CHAR appear in
o   Expressions
o   Conditions
o   SQL Functions
o   SQL Statements
Ø  Text literal should be enclosed in single quotes.
Ø  They have properties of both char and varchar2 data types.
Ø  A text literal can have a maximum length of 4000 bytes.
Using Literal Character Strings:
Ø  A literal that is declared in a select list can be a character, a number or a Date.
Ø  A literal is not a column name or a column Alias.
Ø  A literal is printed for each row that is retrieved by the select statement.
Ø  Literal strings of free-form text can be included in the query as per the requirement.
Ø  Date and character literals must be enclosed within the single quotation marks.
Ø  Literals increase the readability of the output.
Ø  A literal is printed for each row that is retrieved by the select statement.
Ø  Date and character literals must be enclosed within the single quotation marks.

Ex:
  • Select Ename||’ : ’|| ’month salary = ’|| Sal as salaries From Emp;
  • Select ’The Designation of ’|| Ename ||’ is ’||Job as designation From Emp;
  • Select ’The Annual salary of’ ||Ename|| ’ is ’ ||Sal * 12 as Annual_salray From Emp;
Concatenation:
Ø  The concatenation operator links columns to other columns, Arithmetic expressions, or constant values.
Ø  Columns on either side of the operator are combined to make a single output column.
Ø  The resultant column is treated as a character expression.
Ø  The concatenation operator is represented in oracle by double pipe symbol (||).
Ex:

  • Select Empno||’ ’||Ename||’ Designation is ’||job “employee information” From Emp;