title: Oracle homework - I'm stumped
body: <p>This is the last question for my Oracle homework for the year and I cannot figure out even where to start..must be a bad brain day.</p>
  
  <p>Can someone help me with this?</p>
  
  <p>Using a WITH clause write a SELECT statement to list the job_title of those jobs whose maximum salary is more than half the maximum salary of the entire company. Name your subquery MAX_CALC_SAL. Name the columns in the result JOB_TITLE and JOB_TOTAL, and sort the result on JOB_TOTAL in descending order. 
  Hint: Examine the jobs table. You will need to join JOBS and EMPLOYEES to display the job_title. </p>
  
  <p>Thanks!</p>
  
  <p>This is what I have so far...</p>
  
  <pre><code>WITH MAX_CALC_SAL  AS (
      SELECT e.salary, SUM(e.salary) AS tot_salary
      FROM   employees e JOIN jobs j
      ON     e.job_id = j.job_title
      GROUP BY j.job_title
  ),
  avg_cost AS (
      SELECT SUM(e.salary)/COUNT(*) AS avg_salary
      FROM   e.employee
  )
  SELECT * 
  FROM   e.employee 
  WHERE  avg_salary &gt; (SELECT max_salary / 2 
                       FROM j.jobs)
  ORDER BY job_total
  </code></pre>