Sql functions, PL-SQL Programming

SQL Functions

The PL/SQL uses all the SQL functions involving the following aggregate functions that summarize the whole columns of the Oracle data: GROUPING, AVG, COUNT, STDDEV, MAX, MIN, SUM, & VARIANCE. Except for the COUNT (*), all the aggregate functions ignore nulls.

You can use the aggregate functions in the SQL statements, but not in the procedural statements. The Aggregate functions operate on whole columns unless you use the SELECT GROUP BY statement to sort the returned rows into subgroups. If you omit the GROUP BY clause, the aggregate functions treat all returned rows as a single group.

You call an aggregate the function using the syntax as shown below:

function_name([ALL | DISTINCT] expression)

Where the expression refers to one or more database columns. When you specify ALL (the default), the aggregate function consider all column values including the duplicates. When you specify DISTINCT, the aggregate function considers only the distinct values. For illustration, the statement below returns the number of various job titles in the database table emp:


The function COUNT specify the asterisk (*) choice, that returns the number of rows in a table. For illustration, the following statement returns the number of rows in a table emp:

SELECT COUNT (*) INTO emp_count FROM emp;

Posted Date: 10/4/2012 3:39:30 AM | Location : United States

Related Discussions:- Sql functions, Assignment Help, Ask Question on Sql functions, Get Answer, Expert's Help, Sql functions Discussions

Write discussion on Sql functions
Your posts are moderated
Related Questions
Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

How Exceptions Propagate ? Whenever an exception is raised, and if the PL/SQL cannot find a handler for it in the present subprogram or block, the exception propagates. That is

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

Updating Tables in SQL The topic of updating by describing the assignment operator, ":=" in Tutorial D. SQL uses a different syntax for assignment, using the key word SET and

THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or

Literals A literal is an explicit numeric, string, character, or Boolean value not represented by an identifier. Numeric literal 147 and the Boolean literal FALSE are some of

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

Comparison Operators Usually, you use the comparison operators in the WHERE clause of a data manipulation statement to form the predicates, that compare one expression to anot

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n