Using Literal Character Strings in SQL

A concatenation operator :

• Links columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression

Literal Character Strings :

• A literal is a character, a number, or a date that is included in the SELECT statement
• Date and character literal values must be enclosed by single quotation marks
• Each character string is output once for each row returned

Example :

• postgres=# select ename||' earns '||sal as Report from emp;

report
----------------------
SMITH earns 800.00
ALLEN earns 1600.00
WARD earns 1250.00
JONES earns 2975.00
MARTIN earns 1250.00
BLAKE earns 2850.00
CLARK earns 2450.00
SCOTT earns 3000.00
(8 rows)

postgres=#

here as you can see we have added a string "earns" to the two different fields of the table and the displayed result in one single column.

Concatination is done using ||

Defining a Column Alias

A column alias :

• Renames a column heading
• Is useful with calculations
• AS keyword is mandatory between the column name and alias
• Requires double quotation marks if it contains spaces or special characters or if it is case-sensitive

Example :

• postgres=# select ename, sal, comm as "commission" from emp;

ename | sal | commission
--------+---------+------------
SMITH | 800.00 |
ALLEN | 1600.00 | 300.00
WARD | 1250.00 | 500.00
JONES | 2975.00 |
MARTIN | 1250.00 | 1400.00
BLAKE | 2850.00 |
CLARK | 2450.00 |
SCOTT | 3000.00 |

In the previous post we have seen when we performed a mathematical operation the resultant column was labeled with ?Column? automatically... but here the same column has labeled as commission.

This is how we can name/rename the column name in SQL as per our requirement.