loading..
Русский    English
06:17

Renaming the columns and calculations in the result set page 1

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

Console
Execute
  1. SELECT ram AS Mb, hd Gb
  2. FROM PC
  3. WHERE cd = '24x';
renames the ram column as Mb (megabytes) and the hd column as Gb (gigabytes). This query will return the base memory amount and the hard drive storage on computers with a 24-speed CD-ROM:

Mb Gb
64 8
32 10

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:

Console
Execute
  1. SELECT ram*1024 AS Kb, hd Gb
  2. FROM PC
  3. WHERE cd = '24x';

Now the following result will be obtained:

Kb Gb
65536 8
32768 10

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

Console
Execute
  1. SELECT ram, 'Mb' AS ram_units, hd, 'Gb' AS hd_units
  2. FROM PC
  3. WHERE cd = '24x';
gives the following result:

ram ram_units hd hd_units
64 Mb 8 Gb
32 Mb 10 Gb

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(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server has no title at all.

Suggested exercises: 32

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100