I have a small question about SQL arithmetic to ask. I have an employee table that includes two columns: employee number and salary. Example:
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:
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:
- Salaries are always stored in the table as multiples of 100.
- 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
Since this is a concatenation operation, not an arithmetic operation, the answer meets your limiting criteria and provides the answer you want.
This was first published in February 2013