ALIAS NAMES: It is a mechanism in SQL Server which allows
users to provide alternative or another name for the Entities (Tables) and
their Attributes (columns).These Alias names are mainly used to hide the
original name of the table or column. These are always associated with SELECT
statement
Note: Alias names are only for
temporary displaying purpose. Those names not stored permanently into the
database.
E.g.: SELECT EMPNO AS ENO, ENAME
AS EMPLOYNAME, SAL AS BASICSAL, DEPTNO AS DNO FROM EMP
Note: While providing alias names for the columns
we have use a keyword called ‘AS’ in between original column name and alias
column name
The above statement simply
displays employ details by providing temporary column names.
Alias names are also used to
provide alternative names for the newly derived values .
E.g.: Write a Query to select SAL
as BASIC_SAL, DA, HRA, PF, and NET, GROSS by taking the following
specification.
30% of salary as DA
20% of salary as HRA
10% of salary as PF
SAL+DA+HRA as GROSS
GROSS-PF as NET
SELECT SAL AS BASIC_SAL,
SAL*30/100 AS DA,
SAL*20/100 AS HRA,
SAL*10/100 AS PF,
(SAL+SAL*30/100+SAL*20/100) AS GROSS,
(SAL+SAL*30/100+SAL*20/100)-(SAL*10/100) AS
NET
FROM EMP
In the above query the DA, HRA,
PF, GROSS, NET are alias names provided for the newly derived values.
Computed Columns:
These are the columns which completely depends
on physical columns (EMPNO, ENAME, SAL, …………).Computed columns can
automatically arrange their values according to the user specified formula or
definition. For this computed columns user need to supply any values or any
data types. These are always associated with CREATE statement.
E.g.: CREATE TABLE Student
(SNO INT, SNAME
VARCHAR (20), M1 INT, M2 INT, M3 INT,
TOT_MAR AS (M1+M2+M3), AVG_MAR AS
(M1+M2+M3)/3)
The above create statement
creates a table student with 5 Physical columns and 2 Computed Columns. For the
2 computed columns user need to provide the values and need not to provide data
types.
No comments:
Post a Comment