Oracle SQL - Select Column Expressions

  1. HOME
  2. Oracle SQL
  3. Select
  4. Column Expressions

Introduction

Instead of column names, you can specify column expressions in the SELECT clause.

For example, the following code shows how you can derive the range of the salary grades in the SALGRADES table, by selecting the difference between upper limits and lower limits. 

select grade, upperlimit - lowerlimit 
from   salgrades;

In the following code, we concatenate the employee names with their initials into a single column, and also calculate each employee's yearly salary by multiplying their monthly salary value by 12. 

select init||' '||ename name 
,      12 * msal        yearsal 
from   emp 
where  deptno = 10;

Demo

SQL>
SQL> drop table emp;

Table dropped.--  ww  w .  ja v  a  2 s  .  c  o m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> drop table salgrades;

Table dropped.

SQL> create table salgrades(
  2  grade      NUMBER(2)   primary key,
  3  lowerlimit NUMBER(6,2) not null check (lowerlimit >= 0),
  4  upperlimit NUMBER(6,2) not null,
  5  bonus      NUMBER(6,2) not null);
SQL>
SQL> insert into salgrades values (1,  700,1200,   0);
SQL> insert into salgrades values (2, 1201,1400,  50);
SQL> insert into salgrades values (3, 1401,2000, 100);
SQL> insert into salgrades values (4, 2001,3000, 200);
SQL> insert into salgrades values (5, 3001,9999, 500);
SQL>
SQL>
SQL> select grade, upperlimit - lowerlimit
  2  from   salgrades;

    GRADE | UPPERLIMIT-LOWERLIMIT
--------- | ---------------------
 00001.00 |              00500.00
 00002.00 |              00199.00
 00003.00 |              00599.00
 00004.00 |              00999.00
 00005.00 |              06998.00

SQL>
SQL>
SQL> select init||' '||ename name
  2  ,      12 * msal        yearsal
  3  from   emp
  4  where  deptno = 10;

NAME           |   YEARSAL
-------------- | ---------
TF WARD        |  15000.00
R BLAKE        |  34200.00
AB CLARK       |  29400.00
CC KING        |  60000.00
ABC MARY       |  15600.00

SQL>

Related Topics

Quiz