title: avg sql function
body: <p>I have a table employees</p>
  
  <pre><code>  EMPLOYEE_ID     FIRST_NAME    LAST_NAME      HIRE_DATE    JOB_ID     SALARY  DEPARTMENT_ID
    -------------------------------------------------------------------------------------------
      100             Steven        King         17-JUN-03    AD_PRES     24000     90
      101             Neena         Kochhar      21-SEP-05    AD_VP       17000     90
      102             Lex           De Haan      13-JAN-01    AD_VP       17000     90
      103             Alexander     Hunold       03-JAN-06    IT_PROG     9000      60
      104             Bruce         Ernst        21-MAY-07    IT_PROG     6000      60
      105             David         Austin       25-JUN-05    IT_PROG     4800      60
      106             Valli         Pataballa    05-FEB-06    IT_PROG     4800      60
      107             Diana         Lorentz      07-FEB-07    IT_PROG     4200      60
      109             Daniel        Faviet       16-AUG-02    FI_ACCOUNT  9000      100
      110             John          Chen         28-SEP-05    FI_ACCOUNT  8200      100
      111             Ismael        Sciarra      30-SEP-05    FI_ACCOUNT  7700      100
      112             Jose Manuel   Urman        07-MAR-06    FI_ACCOUNT  7800      100
      113             Luis          Popp         07-DEC-07    FI_ACCOUNT  6900      100
  </code></pre>
  
  <p>and table departments</p>
  
  <pre><code>DEPARTMENT_ID   DEPARTMENT_NAME
  -------------------------------
      30          Purchasing
      50          Shipping
      60          IT
      90          Executive
     100          Finance
  </code></pre>
  
  <p>I need to write 3 queries:</p>
  
  <ol>
  <li><p>Get department name and number of employees in each department sorting ascending</p>
  
  <p>I tried this</p>
  
  <pre><code>select count(e.employee_id), d.department_name 
  from t_employees e, t_departments d
  where d.department_id = e.department_id
  group by e.department_name
  order by count(e.employee_id);
  </code></pre>
  
  <p>Not sure if it will work since I can not really try it.</p></li>
  <li><p>Get department name and average salary where average salary by department is more or equal 5000</p>
  
  <p>Result table</p>
  
  <pre><code> DEPARTMENT_NAME    AVERAGE_SALARY
   ---------------------------------
   Executive          19333,333
   IT                 5760
   Finance            7920`
  
  select d.department_name, AVG(e.salary) as AVARAGE_SALARY
  from t_departments d, t_employees e
  where d.department_id = e.department_id
    and avg(e.salary) &gt;= '5000'
  group by d.department_name;
  </code></pre></li>
  <li><p>Get first name and last name of employees who have salary bigger than the average salary from the department they work in</p>
  
  <p>Result table</p>
  
  <pre><code>        FIRST_NAME          LAST_NAME
          ---------------------------------
          Steven              King
          Alexander           Hunold
          Bruce               Ernst
          Daniel              Faviet
          John                Chen
  </code></pre>
  
  <p>I have no code for this one.</p></li>
  </ol>