Doing SQL arithmetic with SELECT instead of arithmetic operations

Learn how to do simple SQL arithmetic operations without using the arithmetic operators.

I have a small question about SQL arithmetic to ask. I have an employee table that includes two columns: employee...

number and salary. Example:

1            10
2            20
3            30

I have to craft a query to print all rows of the employee table after multiplying the numbers in the salary column by 100. But I can only use a SELECT statement, not any SQL arithmetic operations. So the output should be:

1            1000
2            2000
3            3000

More on SQL arithmetic and other SQL questions

Read more expert answers from Karen Morton

Ask your own expert question

First, I just have to say this question makes me wonder about its purpose. I can't imagine why SQL arithmetic operations couldn't be used in a real-life situation. Anyway, I can easily answer the question, given a couple of assumptions:

  1. Salaries are always stored in the table as multiples of 100.
  2. Salaries are always numeric or null; or if they are non-numeric, the final display answer still makes sense ending in 00.

The answer is:

SELECT emp, to_char(salary) || '00' as salary

FROM employee;

Since this is a concatenation operation, not an arithmetic operation, the answer meets your limiting criteria and provides the answer you want.

Dig Deeper on Oracle and SQL