Sql functions, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Sql functions

Wrapping and unwrapping in sql, Wrapping and unwrapping in SQL Operato...

Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Map and order methods, Map and Order Methods: The values of the scalar...

Map and Order Methods: The values of the scalar datatype like CHAR or REAL have a predefined order that allows them to be compared. While, the instances of an object type has

Example of coalesce operator - sql, Example of COALESCE operator Examp...

Example of COALESCE operator Example: Give the total of marks for each exam (simplified solution) SELECT CourseId, COALESCE ((SELECT SUM (Mark) FROM EXAM_MARK AS EM

Primary key - sql, Primary Key - SQL A PRIMARY KEY specification carri...

Primary Key - SQL A PRIMARY KEY specification carries an implicit NOT NULL constraint on each column of the specified key. When more than one key constraint is required, the k

Sql scripts, The SQL ‘CREATE TABLE' scripts for all the tables you have imp...

The SQL ‘CREATE TABLE' scripts for all the tables you have implemented. Note that your tables must correspond exactly to the ERD you have provided in 1. above, or you will lose ma

Disjunction - sql, Disjunction (OR, ∨) Again we have nine rows instead...

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Write a program to implement inverted file shown slider, Write a program to...

Write a program to implement the inverted file shown in the slides (Simple Index file, LabelID file and Data file).  Use the Avail_List to point at the deleted Label IDs so that th

Redeclaring predefined exceptions - user-defined exceptions, Redeclaring Pr...

Redeclaring Predefined Exceptions Keep in mind that, the PL/SQL declares predefined exceptions globally in the package STANDARD; Therefore you need not declare them yourself.

How exceptions propagate in pl/sql programming?, How Exceptions Propagate ?...

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

Laws / rules associated with evolution, LAWS / RULES - Dollo's Law    ...

LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd