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


Abhishek Srivastava is a seasoned IT professional with diverse experience in Banking, Insurance, Utility and Education domains. Managing large accounts, Program management & Developing business solutions has been his forte. An NIT / IIM Kozhikode graduate, He founded Techcanvass (https://techcanvass.com) in 2013. With Techcanvass, He is pursuing his dream of creating an organization imparting quality education to IT professionals. He believes that learning is a lifelong journey and one must never stop learning. He also loves writing and sharing his knowledge. Some of his notable books are ERP to E2RP, UML Modelling for Business Analysts, Business Analysts Practitioners Guide, Software Testing: A practical Approach. All these books are available on Amazon.

Leave a Reply

Your email address will not be published. Required fields are marked *