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;