SQL Tutorial for Business Analysts

SQL tutorial | DECODE in ORACLE

SQL Basics tutorial on DECODE

In this SQL tutorial on DECODE, we are going to learn about Decode command in SQL (only applicable for Oracle). Every database system has some special and useful SQL commands. Oracle has an extremely powerful command known as DECODE. Please note that, using DECODE command is only possible in Oracle.

Implementing DECODE

How do you implement IF-THEN-ELSE in an SQL query in ORACLE? Let’s take an example to understand the problem first?
You have a table named “Employees”. The “Employees” table has the following fields:

  • Emp ID
  • Emp Name
  • Designation
  • Address
  • Contact No 1
  • Contact No 2
  • Basicsal

The company decided to pay the bonus as a multiple of base salary. This will be calculated as follows:

If Designation = “Project Manager” then

Bonus = 2.3 X Basic Salary

Else if Designation = “Program Manager” then

Bonus = 3.6 X Basic Salary


Bonus = 1.2 X Basic Salary

End If

If you are going to use a programming language, you can use If-Else-Then statements to code but SQL queries don’t support these so How do you fetch the data using a single SQL query but still using the above conditions?
The solution is to use DECODE, here is the query?

SELECT EmpID, EmpName,

DECODE (Designation, ‘Project Manager’, 2.3 X Basic Salary, ‘Program Manager’, 3.6 X Basic Salary, 1.2 X Basic Salary)

FROM Employees;

Alternative to DECODE

Is there any other way to achieve the same result in ORACLE? Of course, yes, using CASE.
Using CASE:

SELECT EmpID, EmpName,

CASE Designation,

WHEN ‘Project Manager’ THEN 2.3 X Basic Salary

WHEN  ‘Program Manager’ THEN 3.6 X Basic Salary

ELSE 1.2 X Basic Salary

FROM Employees;

This article was in series of articles, we have been publishing under “SQL tutorial for beginners”. Here are links to a 3-part SQL tutorial series:

SQL tutorial for Beginners Part 1

SQL tutorial for Beginners Part 2

SQL tutorial for Beginners Part 3

SQL Training for business Analysts


Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed