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:

SELECT COUNT(DISTINCT job) INTO job_count FROM 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
Write a query to find academics that are authors and that have only ever coauthored papers with authors from institutes in the same state as their own. List their academic number,

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

THEO R Y OF PANSPERMIA - Arrhenius (1908) postulated the cosmic panspermia theory that claims that organisms existed throughout the universe, and their spores, etc., could

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Restriction in SQL Syntax: SELECT DISTINCT StudentId FROM IS_CALLED WHERE Name = 'Boris' The WHERE clause operates on the result of the FROM clause in analogo

Loop Labels Like the PL/SQL blocks, loops can also be labeled. The label, an undeclared identifier enclosed by double angle brackets, should appear at the beginning of the LOOP

Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw