1. First version

At first glance, the following query would produce the order above:

      SELECT
        dept_cd, crse_num, crse_suffix, crse_prefix, crse_suffix2
      FROM
        sisgco.catlg_crse_info_tb
      WHERE
       dept_cd = 'MATH'
      ORDER BY
        crse_num, crse_suffix, crse_prefix, crse_suffix2
     

Because the prefix and suffix columns are nullable, and null values sort last, the result is not quite what we want:

2. Correct version

The solution lies in the SQL built-in function DECODE, which allows case-like expressions to be included in a query. Here we are simply replacing null values with ascii space so that lexical sort is correct. Because the course number column is a zero-padded VARCHAR, we use the TO_NUMBER function to strip leading zeros and obtain a numeric rather than lexical sort, and use the concatenation operator to present the course name:

 
      SELECT 
        dept_cd || ' ' || crse_prefix || TO_NUMBER(crse_num) ||
          crse_suffix || crse_suffix2 as course,
        TO_NUMBER(crse_num) as cat_num,
        DECODE(crse_suffix, null, ' ', crse_suffix) as s0,
        DECODE(crse_prefix, null, ' ', crse_prefix) as p,
        DECODE(crse_suffix2, null, ' ', crse_suffix2) as s1
      FROM
        sisgco.catlg_crse_info_tb@SISGCO_SS1P
      WHERE
        dept_cd = 'MATH'
      ORDER BY cat_num, s0, p, s1
      

Our new result is presented in a user-friendly format, and almost correctly ordered:

Why "almost?" The order of the Math 53 sequence is incorrect, because the prefixed course appears before the suffixed course. Since courses with a suffix but no prefix should appear before those with a prefix, we need to sort by prefix first. So we change the ORDER BY clause from:

      ORDER BY cat_num, s0, p, s1
     

to

      ORDER BY cat_num, p, s0, s1
     

Our new result is in the desired order: