SQL Class Exercise:  Aggregate Functions & Grouping

          
Objective:  To apply built-in functions and grouping to aggregate
    data accessed from Department and Employee database tables.
1. Expression:
   Project deptno, ename, salary, and salary + $900 from Employee.


2. Built-in Functions(avg [distinct] expression), count( [distinct] 
   expression), count(*), max([distinct] expression), min(), sum():
   a. Count the number of employee tuples (rows).


   b. Count the number of employees with the job value 'ANALYST'.


   c. Count the number of employees with a job (nonNull value).
        
        
   d. Count the number of different(distinct) jobs in Employee.


   e. Find the number of employees, the lowest and highest salary, the
      sum of salary, and the average salary rounded to two decimal places
      (use format(x,d)) for all employees.



   f. Recompute average employee salary with an expression involving sum()
      and count().



   g. Project eName and salary from Employee where salary is less than the
      average for all employees. Hint: use subQuery.



3.  Grouping:
    a. Categorize the employees by job so as to produce a list of the 
       different jobs without using the distinct keyword.



    b. Retrieve employee count by job category.



    c. Project job name and a count of employees with each job. The list
       should be sorted into descending sequence by the count.


    d. Display job name and average salary (as a whole number) for 
       employees holding that job in department 20.



    e. For each department having more than three employees, display 
       department number and the salary total (sum) for that department
       formatted to two decimal places with comma insertion. Assign the 
       name "salary total" to the salary expression column.