title: How to write multiple conditions in a check constraint?
body: <p>I have 3 ranks Executive, Senior and junior
  I want to write a check constraint that ensures that when data is input for salary, The executive cannot have a lower salary than the senior and junior. Same thing for the other possible combinations that is Senior salary > Junior salary but not greater than executive salary.</p>
  
  <p>This is what I tried but nothing is working</p>
  
  <pre><code>CONSTRAINT check_salary (CASE WHEN Job_Rank = 'Executive' THEN salary &gt; (SELECT salary WHERE Job_Rank = 'Senior','Junior' END)) 
  
  CONSTRAINT check_salary (Job_Rank = 'Executive' AND salary &gt; Job_Rank = 'Senior','Junior' ) 
  </code></pre>
  
  <p>I am sure this is not correct since MYSQL is rejecting it</p>