Some Important Query in Oracle(Arithmetic operation,Null value,Column Aliases,Concatenation Operator):: Part-2

0

In previous lesson we see use of select statement, how we select all row and column from a table.

Example: select * from emp;

We also see the way in how we selecting specific columns

Example: select ename,empno,deptno,sal from emp;

Arithmetic operation

And today we see the use of arithmetic operation (+,-,*,/) in Oracle.

If there is any condition arise in where u want to add 500 tk for everyone salary.Then u use this kind of Query.

Exam: select ename,empno,deptno,sal,sal+500 from emp;

If there is any condition arise in where u want to add 500 tk in BLAKE salary. Then u use this kind of Query.

Exam: select ename,empno,deptno,sal,sal+500 from emp where ename=’BLAKE’;

We can also use more then one Arithmetic operation in the query.

Exam: select ename,empno,deptno,sal,12*sal+100 from emp;

We can also use PARENTHESIS () in the query.

Exam: select ename.empno,deptno,sal,(12*sal)+100 from emp;

NULL VALUE

Null Value is an important factor in Oracle.

(01) A null is a value that is unavailable, unassigned,unknown or inapplicable.
(02) A null is not the same as zero or a blank space.

Exam: select ename,job,sal,comm From emp;

When we use this query then we see the person who don’t have any comm. Those space is blank.
In the comm. Column in the emp table,you notice that only a SALESMAN can earn commission.Other employees are not entitled to earn commission.

NULL VALUE
In arithmetic Expressions

Exam: select ename,12*sal+comm from emp where ename=’KING’;

If any column value in an arithmetic expression is null,the result is null.

Column Aliases

By using this Function we can change the Table attribute name.
Such as In the emp table there are some attribute such as ename,empno,sal and also many other.If we want to show ename as NAME,empno as EMPLOYEE_DETAILS,sal as SALARY then we use this aliases

[ as,” “,space]

Exam: select ename as NAME,empno as EMPLOYEE_DETAILS,sal as SALARY from emp;

Or,

select ename “NAME”,empno “EMPLOYEE_DETAILS”,sal “SALARY” from emp;

OR,

Select ename(space)NAME,empno EMPLOYEE_DETAILS,sal SALARY from emp;

Concatenation Operator

By using this operator we can add strings.

01.Concatenates columns or character strings to other columns.
02.Is represented by two vertical bars (||)
03.Creates a resultant column that is a character expression.

Exam: select ename||job as Employee from emp;

In the example ENAME and JOB are concatenated, and they are given the aliases Employee.Notice that the employee number and job are combined to make a single output column.

Share.

About Author

Avatar

Recently complete my graduation from university of information technology and sciences[UITS] in IT

Leave A Reply

CAPTCHA ImageChange Image