How does this part of code work?
CASE WHEN subject IN ('Chemistry','Physics') THEN 1 ELSE 0 END,
First it checks if subjet is Chemistry or Physics. If it does the sorting score will be 1.
If the subject does not contain any of "Chemistry" or "Physics" the score will be 0.
After that it sorts the results by score so the results that contains Chemistry or Physics will come first after that those that dont contain.