Database Reference
In-Depth Information
Example 29:
How many programs offer course M100?
Example 30:
Referring to the supplier-items database, how many boxes of item
'I100' is supplied? What is the maximum quantity and the minimum quantity?
Aggregate functions are also often used in situations where data is to be grouped, in
order to set up control breaks. This is illustrated in the following examples.
Example 31:
Referring to the supplier-items database, produce a list showing item
number and quantity of items shipped:
The aggregation functions are often used when grouping data (hence the alternate
name, group functions) in order to set up control (summary) lines in reports. The next
two examples will illustrate how this is done.
For the next two examples (as well as some of the upcoming ones), consider the
section of the sample Oracle database (which is shipped with the Oracle product)
consisting of the following tables (table names and column names may vary with different
implementations):
Dept
{Deptno, Dname, Loc} with primary key [Deptno]
Emp
{Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno} with primary key [Empno]
Assume further that
Emp.Deptno
is a foreign key, which references
Dept.Deptno
.
Example 32:
Develop a list from the employee table, showing for each department, the
total salary, average salary, minimum salary, maximum salary and standard deviation:
