Universal Database (netDB2) Introductory Guide

Using some predefined functions

Functions are either built-in or user defined. The predefined functions include Column Functions:

  1. avg : returns the sum of values in that column divided by the total number of values in that particular column
  2. count : returns the number of rows or values in a set of rows or values.
  3. max : returns the highest value in the column
  4. min : returns the smallest value in the column. 

example:
       select name, student_id
       from student
       where grades > (select avg(grades) from student)

This example gives us the names and student_id's of all students whose grades are higher than the average grade of the class. 

example:
       select name, student_id
       from student
       where grades=max(grades)

This example gives us the names and student_id's of that student whose grade is the highest in the class.

Scalar Functions:
Scalar functions perform some kid of operation on a value to return another value. Or in other terms makes some modifications to return a modified value.

  1. abs : returns the absolute value .
  2. hex : returns a hexadecimal representation of a value.
  3. length : returns the number of bytes of the argument.
  4. Year : Extract the year portion of the date -time. 

Syntax: {Select statement} from {table name} where {condition}
   
condition can be: {function name} {field}