Renaming the columns and calculations in the result set

The column names listed in the SELECT clause can be changed. This can be done to increase readability because the column names are frequently abbreviated to simplify typing. The AS keyword being used for renaming can be neglected in accordance with the standard, since it is implicitly implied.

For example, the following query

SELECT ram AS Mb, hd Gb
FROM PC
WHERE cd = '24x';

Renaming is especially desired when using value-calculation expressions in SELECT clause. These expressions give the data which may not be directly derived from database tables. An expression including the column names of the table contained in the FROM clause is calculated for each row of the result set. For example, RAM amount in kilobytes can be obtained when writing:

SELECT ram*1024 AS Kb, hd Gb
FROM PC
WHERE cd = '24x';

Now the following result will be obtained:

Sometimes, an explanation needs to be placed along with the corresponding value. To make this, add string expression as complementary column. For example, the query

SELECT ram, 'Mb' AS ram_units, hd, 'Gb' AS hd_units
FROM PC
WHERE cd = '24x';

Should the expression have no specified name, a default naming way will be used, dependable on DBMS used. For example, the names like expression1 and so on will be used in MS Access, while the column returned in MS SQL Server has no title at all.

Suggested exercises: 32

SQL Standard allows to use delimited identifier, whereas delimiter is the double quote - ("). If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. For example, the query

SELECT
'SELECT' "SELECT"

SELECT 'SELECT' SELECT

Apart from standard delimiter, nearly each DBMS adopts the own  one.  For example, the above query in SQL Server can be written as

SELECT 'SELECT' [SELECT]

The both delimiters - standard and own - can be used simultaneously, but the standard one not always is supported by default. The proper setting parameter in MSSQL can be changed with the statement:

SET QUOTED_IDENTIFIER { ON | OFF }

The standard behaviour (ON) is assumed by default here.

MySQL

The query

SELECT 'SELECT' "SELECT"

will give us upon execution

I can explain this behaviour by default settings which adopt usage of double quote symbol (") in the same manner as single quote (’) - for delimiting the character constant. So two string unites into one string. However if you type the query as

SELECT 'SELECT' as "SELECT";

or as

SELECT 'SELECT' `SELECT`;

you’ll obtain desired result.

To forbid usage of double quote as single quote, you can replace settings by standard ones. The following statement changes the setting mentioned above:

SET GLOBAL sql_mode='ANSI_QUOTES';

but this one makes all the settings as standard ones:

SET GLOBAL sql_mode='ANSI';

After that the query

SELECT 'SELECT' "SELECT";

will give us

Oracle & PostgreSQL

These DBMS behave in standard manner. Note only that Oracle (in view of FROM clause is mandatory)  demands to rewrite our query as

SELECT 'SELECT' "SELECT" FROM dual;