Normalize data and insert into table

Question

I have three table and their data. I have also desire output but i don't no how to normalize it. Desire output:

    name   | title |    role    
----------+-------+------------
 Dilan    | si106 | Instructor
 Boshra   | si106 | Learner
 Kearney  | si106 | Learner
 Norea    | si106 | Learner
 Schuyler | si106 | Learner
 Sylvia   | si110 | Instructor
 Arzoo    | si110 | Learner
 Callin   | si110 | Learner
 Raja     | si110 | Learner
 Silas    | si110 | Learner
 Isher    | si206 | Instructor
 Arryn    | si206 | Learner
 Jemmia   | si206 | Learner
 Luciano  | si206 | Learner
 Pasquale | si206 | Learner
(15 rows)

Query:

SELECT student.name, course.title, roster.role
    FROM student 
    JOIN roster ON student.id = roster.student_id
    JOIN course ON roster.course_id = course.id
    ORDER BY course.title, roster.role DESC, student.name;

But i got this output:

Problem is here: Due to DESC the role are not printing like desire output.

name   | title |    role    
----------+-------+------------
 Boshra   | si106 | Learner
 Kearney  | si106 | Learner
 Norea    | si106 | Learner
 Schuyler | si106 | Learner
 Dilan    | si106 | Instructor
 Arzoo    | si110 | Learner
 Callin   | si110 | Learner
 Raja     | si110 | Learner
 Silas    | si110 | Learner
 Sylvia   | si110 | Instructor
 Arryn    | si206 | Learner
 Jemmia   | si206 | Learner
 Luciano  | si206 | Learner
 Pasquale | si206 | Learner
 Isher    | si206 | Instructor
(15 rows)

How can i get above output by change insertion into tables and not changing query? my three tables are given below:

=> SELECT * FROM STUDENT;
 id |   name   
----+----------
  1 | Dilan
  2 | Boshra
  3 | Kearney
  4 | Norea
  5 | Schuyler
  6 | Sylvia
  7 | Arzoo
  8 | Callin
  9 | Raja
 10 | Silas
 11 | Isher
 12 | Arryn
 13 | Jemmia
 14 | Luciano
 15 | Pasquale
(15 rows)

select * from course;
 id | title 
----+-------
  1 | si106
  2 | si106
  3 | si106
  4 | si106
  5 | si106
  6 | si110
  7 | si110
  8 | si110
  9 | si110
 10 | si110
 11 | si206
 12 | si206
 13 | si206
 14 | si206
 15 | si206
(15 rows)

select * from roster;                                                                                                             
 id | student_id | course_id |    role    
----+------------+-----------+------------
 16 |          1 |         1 | Instructor
 17 |          2 |         2 | Learner
 18 |          3 |         3 | Learner
 19 |          4 |         4 | Learner
 20 |          5 |         5 | Learner
 21 |          6 |         6 | Instructor
 22 |          7 |         7 | Learner
 23 |          8 |         8 | Learner
 24 |          9 |         9 | Learner
 25 |         10 |        10 | Learner
 26 |         11 |        11 | Instructor
 27 |         12 |        12 | Learner
 28 |         13 |        13 | Learner
 29 |         14 |        14 | Learner
 30 |         15 |        15 | Learner
(15 rows)

So any one help me to normalize there tables so that i get desire output.

Answer 1

How can i get above output by change insertion into tables and not changing query?

You cannot. The query is explicitly stating the ordering of the result set. That puts "Learners" before "Instructors" because of the DESC ordering.

The query you want would use the ORDER BY without DESC:

 ORDER BY course.title, roster.role, student.name;

I am not aware of a collation (which controls the ordering) where 'I' > 'L'.

There is no way to change the data (in a sensible) way to make the query return the results you want. I mean, it might be possible to prepend each roster.role with a an "invisible" character and use that the control the sorting. That would be a total hack and not at all recommended.