Q

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:

EMP      SALARY
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:

EMP      SALARY
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.

This was first published in February 2013

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

7 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close