Salary Classification using Conditional Statements - Exercise

Contact Us or call 1-877-932-8228
Salary Classification using Conditional Statements - Exercise

Salary Classification using Conditional Statements

Duration: 10 to 15 minutes.
  1. Start with the program displayed below that prints out a person's name, salary and the current date (using simple variable assignment).
  2. Add a conditional logic to display "low" if the salary is less than 3000.
  3. Add additional code so that "medium" is displayed if the salary is greater than or equal to 3000 or less than 10000.
  4. Make additional modifications so that "high" is displayed if the salary is greater than or equal to 10000. (At this point, either low, medium, or high will be displayed regardless of what valid numeric value is assigned to salary).
  5. Using different conditional logic, display "low","medium" or "high" depending on the salary value
  6. Using a third form of conditional logic, once again display "low","medium" or "high" depending on the salary value.

Code Sample:

Within-the-Block/Exercises/salary_classification_exercise.sql
DECLARE
  v_name VARCHAR2(45);
  v_current_date DATE;
  v_salary NUMBER;

BEGIN

  dbms_output.put_line('Date:   ' || v_current_date);
  dbms_output.put_line('Name:   ' || v_name);
  dbms_output.put_line('Salary: ' ||
                       trim(to_char(v_salary,'$999,999,999.00')));

END;

Code Explanation

Solution:

Within-the-Block/Solutions/salary_classification_solution.sql
DECLARE
  v_name VARCHAR2(45) := 'Larry Ellison';
  v_current_date DATE := SYSDATE;
  v_salary NUMBER     := 1.00;
  v_message VARCHAR2(10);
BEGIN

  dbms_output.put_line('Date:   ' || v_current_date);
  dbms_output.put_line('Name:   ' || v_name);
  dbms_output.put_line('Salary: ' ||
                       trim(to_char(v_salary,'$999,999,999.00')));
  
  -- Variation 1:  Using IF/ELSIF  
    dbms_output.put_line('Variation 1');
    -- display "low" if salary is less than 3000
    IF v_salary < 3000 THEN
      dbms_output.put_line('low');  
    -- if salary is greater than or equal to 3000 OR
    -- less than 10000 "medium" is displayed.   
    ELSIF v_salary >= 3000 AND v_salary < 10000 THEN 
      dbms_output.put_line('medium');	
    -- "high" is displayed if salary is
    -- greater than or equal to 10000.   
    ELSIF v_salary >= 10000 THEN 
      dbms_output.put_line('high');
    END IF;
  
  -- Variation 2:  Using searched case STATEMENT with else
    dbms_output.put_line('Variation 2');
    CASE
      WHEN v_salary < 3000
        THEN dbms_output.put_line('low');  
      WHEN v_salary >= 3000 AND v_salary < 10000
        THEN dbms_output.put_line('medium');	
      ELSE
        dbms_output.put_line('high');
    END CASE;
  
  -- Variation 3: Using searched case EXPRESSION with else:
  --              Assigning result of case to a variable
    dbms_output.put_line('Variation 3');
    v_message :=  CASE
                    WHEN v_salary < 3000 THEN 'low'  
                    WHEN v_salary >= 3000 AND v_salary < 10000 
                      THEN 'medium'
                    ELSE 'high'
                  END;
    dbms_output.put_line(v_message);
  
END;

Code Explanation

Next