1. CASE Statement

The CASE statement is flexible and allows for complex conditional logic based on multiple conditions.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

SQL Example

SELECT employee_id,
       first_name,
       salary,
       CASE
           WHEN salary > 5000 THEN 'High'
           WHEN salary BETWEEN 3000 AND 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

Explanation:

• This SQL query selects the employee_id, first_name, and salary from the employees table.
• It categorizes the salary into three categories: "High" for salaries greater than 5000, "Medium" for salaries between 3000 and 5000, and "Low" for salaries below 3000.

2. DECODE Function

The DECODE function is an Oracle-specific function used for equality checks in conditional logic.

Syntax

DECODE(expression, search1, result1, search2, result2, ..., default_result)

SQL Example

SELECT employee_id,
       first_name,
       salary,
       DECODE(salary,
              5000, 'High',
              3000, 'Medium',
              'Low') AS salary_category
FROM employees;

Explanation:

• In this SQL query, the DECODE function checks the salary value.
• If the salary equals 5000, it returns 'High'; if it equals 3000, it returns 'Medium'; otherwise, it returns 'Low'.
• This is a more straightforward way to handle conditions when you are comparing specific values.

3. IF-THEN-ELSE (PL/SQL Block)

The IF-THEN-ELSE statement is used in PL/SQL blocks for implementing conditional logic.

Syntax

IF condition THEN
    -- statements
ELSIF condition THEN
    -- statements
ELSE
    -- statements
END IF;

PL/SQL Example

DECLARE
   v_salary NUMBER := 4000;  -- Initializing a salary variable
   v_category VARCHAR2(10);   -- Variable to hold the salary category
BEGIN
   IF v_salary > 5000 THEN
      v_category := 'High';   -- Set category to 'High'
   ELSIF v_salary BETWEEN 3000 AND 5000 THEN
      v_category := 'Medium'; -- Set category to 'Medium'
   ELSE
      v_category := 'Low';    -- Set category to 'Low'
   END IF;

   DBMS_OUTPUT.PUT_LINE('Salary Category: ' || v_category); 
-- Output the result
END;

Explanation:

• In this PL/SQL block, the variable v_salary is initialized with a value of 4000.
• The IF-THEN-ELSE statement checks the value of v_salary and assigns the appropriate category to v_category.
• Finally, it outputs the category using DBMS_OUTPUT.PUT_LINE.