×Oracle MIN and MAX Analytic Functions

MIN and MAX Analytic Functions and MIN and MAX as Aggregate Functions

The MIN and MAX aggregate functions are used to calculate the minimum and maximum values of a set of data respectively. As aggregate functions they reduce the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values.

SELECT MIN(sal) AS min_sal,

       MAX(sal) AS max_sal

FROM   emp;

   MIN_SAL    MAX_SAL

---------- ----------

       800       5000

SQL>

We can get more granularity of information by including a GROUP BY clause. In the following example we see the minimum and maximum values on a per-department basis.

SELECT deptno,

       MIN(sal) AS min_sal,

       MAX(sal) AS max_sal

FROM   emp

GROUP BY deptno

ORDER BY deptno;

    DEPTNO    MIN_SAL    MAX_SAL

---------- ---------- ----------

        10       1300       5000

        20        800       3000

        30        950       2850

SQL>

In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.

MIN Analytic Function

The basic description for the MIN analytic function is shown below. The analytic clause is described in more detail here.

MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]

Using an empty OVER clause turns the MIN function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the minimum salary for all employees, as well as all the original data.

SELECT empno,

       ename,

       deptno,

       sal,

       MIN(sal) OVER () AS min_sal

FROM   emp

ORDER BY deptno;

     EMPNO ENAME          DEPTNO        SAL    MIN_SAL

---------- ---------- ---------- ---------- ----------

      7782 CLARK              10       2450        800

      7839 KING               10       5000        800

      7934 MILLER             10       1300        800

      7566 JONES              20       2975        800

      7902 FORD               20       3000        800

      7876 ADAMS              20       1100        800

      7369 SMITH              20        800        800

      7788 SCOTT              20       3000        800

      7521 WARD               30       1250        800

      7844 TURNER             30       1500        800

      7499 ALLEN              30       1600        800

      7900 JAMES              30        950        800

      7698 BLAKE              30       2850        800

      7654 MARTIN             30       1250        800

SQL>

Adding the partitioning clause allows us to display the minimum salary per department, along with the employee data for each department.

SELECT empno,

       ename,

       deptno,

       sal,

       MIN(sal) OVER (PARTITION BY deptno) AS min_sal_by_dept

FROM   emp;

     EMPNO ENAME          DEPTNO        SAL MIN_SAL_BY_DEPT

---------- ---------- ---------- ---------- ---------------

      7782 CLARK              10       2450            1300

      7839 KING               10       5000            1300

      7934 MILLER             10       1300            1300

      7566 JONES              20       2975             800

      7902 FORD               20       3000             800

      7876 ADAMS              20       1100             800

      7369 SMITH              20        800             800

      7788 SCOTT              20       3000             800

      7521 WARD               30       1250             950

      7844 TURNER             30       1500             950

      7499 ALLEN              30       1600             950

      7900 JAMES              30        950             950

      7698 BLAKE              30       2850             950

      7654 MARTIN             30       1250             950

SQL>

MAX Analytic Function

The basic description for the MAX analytic function is shown below. The analytic clause is described in more detail here.

MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]

Using an empty OVER clause turns the MAX function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the maximum salary for all employees, as well as all the original data.

SELECT empno,

       ename,

       deptno,

       sal,

       MAX(sal) OVER () AS max_sal

FROM   emp;

     EMPNO ENAME          DEPTNO        SAL    MAX_SAL

---------- ---------- ---------- ---------- ----------

      7369 SMITH              20        800       5000

      7499 ALLEN              30       1600       5000

      7521 WARD               30       1250       5000

      7566 JONES              20       2975       5000

      7654 MARTIN             30       1250       5000

      7698 BLAKE              30       2850       5000

      7782 CLARK              10       2450       5000

      7788 SCOTT              20       3000       5000

      7839 KING               10       5000       5000

      7844 TURNER             30       1500       5000

      7876 ADAMS              20       1100       5000

      7900 JAMES              30        950       5000

      7902 FORD               20       3000       5000

      7934 MILLER             10       1300       5000

SQL>

Adding the partitioning clause allows us to display the maximum salary per department, along with the employee data for each department.

SELECT empno,

       ename,

       deptno,

       sal,

       MAX(sal) OVER (PARTITION BY deptno) AS max_sal_by_dept

FROM   emp;

     EMPNO ENAME          DEPTNO        SAL MAX_SAL_BY_DEPT

---------- ---------- ---------- ---------- ---------------

      7782 CLARK              10       2450            5000

      7839 KING               10       5000            5000

      7934 MILLER             10       1300            5000

      7566 JONES              20       2975            3000

      7902 FORD               20       3000            3000

      7876 ADAMS              20       1100            3000

      7369 SMITH              20        800            3000

      7788 SCOTT              20       3000            3000

      7521 WARD               30       1250            2850

      7844 TURNER             30       1500            2850

      7499 ALLEN              30       1600            2850

      7900 JAMES              30        950            2850

      7698 BLAKE              30       2850            2850

      7654 MARTIN             30       1250            2850

SQL>

Oracle Forms Triggers

Block Processing Triggers:

Block processing triggers fire in response to events related to record management in a block.

·        When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.

·        When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.

·        When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.

Interface Event Triggers:

Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.

·        When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.

·        When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.

·        When-Image-Activated Initiate an action whenever the operator double-clicks an image item.

·        When-Image-Pressed Initiate an action whenever an operator clicks on an image item.

·        When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.

·        When-Window-Activated Initiate an action whenever an operator or the application activates a window.

·        When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.

·        When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.

Master/Detail Triggers:

Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.

·        On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.

·        On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.

·        On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.

Message-Handling Triggers:

Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.

·        On-Error Replace a default error message with a custom error message, or to trap and recover from an error.

·        On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.

Validation Triggers:

Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.

·        When-Validate-Item

·        When-Validate-Record

Navigational Triggers:

Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.

·        Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.

·        Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.

·        Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.

·        Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.

·        Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.

·        Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.

·        Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.

·        Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.

·        When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).

·        When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.

·        When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.

·        When-New-Item-Instance Perform an action immediately after the input focus moves to a different item.

Transactional Triggers:

Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.

·        On-Delete

·        On-Insert

·        On-Update

·        On-Logon

·        On-Logout

·        Post-Database-Commit

·        Post-Delete

·        Post-Insert

·        Post-Update

·        Pre-Commit

·        Pre-Delete

·        Pre-Insert

·        Pre-Update

Query-Time Triggers:

Query-time triggers fire just before and just after the operator or the application executes a query in a block.

·        Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.

·        Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.

Advertisements

REPORT THIS AD

REPORT THIS AD

Top of Form

Bottom of Form

Oracle Courses for all levels

Oracle Official Academy Courses

sql

pl/sql

forms

reports

1-Oracle Database 10g: Introduction to SQL
Course Topics:
- Introduction
- Retrieving Data Using the SQL SELECT Statement
- Restricting and Sorting Data
- Using Single Row Functions to Customize Reports
- Reporting Aggregated Data Using the Group Functions
- Displaying Data From Multiple Tables
- Using Sub queries to Solve Queries
- Using the SET Operators
- Manipulating Data
- Using DDL Statements to Create and Manage Tables
- Creating Other Schema Objects
- Managing Objects with Data Dictionary Views
- Controlling User Access
- Manage Schema Objects
- Manipulating Large Data Sets
- Generating Reports by Grouping Related Data
- Searching Data Using Advanced Sub queries
- Hierarchical Data Retrieval

2-Oracle Database 10g: Program with PL/SQL
Course Topics:
- Introduction to PL/SQL
- Declaring PL/SQL Identifiers
- Writing Executable Statements
- Interacting with the Oracle Server
- Writing Control Structures
- Working with Composite Data Types
- Using Explicit Cursors
- Handling Exceptions
- Creating Stored Procedures
- Creating Stored Functions
- Creating Packages
- Using More Package Concepts
- Utilizing Oracle Supplied Packages in Application Development
- Dynamic SQL and Metadata
- Design Considerations for PL/SQL Code
- Managing Dependencies
- Creating Triggers
- Applications for Triggers
- Understanding and Influencing the PL/SQL Compiler

3-Oracle Forms Developer 10g: Build Internet Applications
Course Topics:
- Introducing Oracle Forms Developer and Forms Services
- Creating Forms Modules
- Working with Data Blocks and Frames
- Working with Input Items
- Working with Non Input Items
- Working with Windows and Canvases
- Producing Triggers
- Debugging Triggers
- Adding Functionality to Items
- Run-Time Messages and Alerts
- Query Triggers
- Validation
- Navigation
- Transaction Processing
- Writing Flexible Code
- Sharing Objects and Code


4-Oracle Reports Developer 10g: Build Reports
Course Topics:
- Introduction to Oracle Reports Developer
- Designing and Running Reports
- Working in Oracle Reports Developer
- Creating a Paper Report
- Enhancing a Basic Paper Report
- Managing Report Templates
- Creating a Web Report
- Enhancing Reports Using the Data Model: Queries and Groups
- Enhancing Reports Using the Data Model: Data Sources
- Enhancing Reports Using the Data Model: Creating Columns
- Enhancing Reports Using the Paper Layout
- Controlling the Paper Layout: Common Properties
- Controlling the Paper Layout: Specific Properties
- Web Reporting
- Creating and Using Report Parameters
- Enhancing Matrix Reports
- Coding PL/SQL Triggers
- Extending Functionality Using the SRW Package

10$ per hour

            yasser.hassan@yandex.com

Oracle Index types

Types of Indexes:

Oracle Database provides several indexing schemes, which provide complementary performance functionality. The indexes can be categorized as follows:

    B-tree indexes

    These indexes are the standard index type. They are excellent for primary key and highly-selective indexes. Used as concatenated indexes, B-tree indexes can retrieve data sorted by the indexed columns. B-tree indexes have the following subtypes:

        Index-organized tables

        An index-organized table differs from a heap-organized because the data is itself the index. See “Overview of Index-Organized Tables”.

        Reverse key indexes

        In this type of index, the bytes of the index key are reversed, for example, 103 is stored as 301. The reversal of bytes spreads out inserts into the index over many blocks. See “Reverse Key Indexes”.

        Descending indexes

        This type of index stores data on a particular column or columns in descending order. See “Ascending and Descending Indexes”.

        B-tree cluster indexes

        This type of index is used to index a table cluster key. Instead of pointing to a row, the key points to the block that contains rows related to the cluster key. See “Overview of Indexed Clusters”.

    Bitmap and bitmap join indexes

    In a bitmap index, an index entry uses a bitmap to point to multiple rows. In contrast, a B-tree index entry points to a single row. A bitmap join index is a bitmap index for the join of two or more tables. See “Bitmap Indexes”.

    Function-based indexes

    This type of index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression. B-tree or bitmap indexes can be function-based. See “Function-Based Indexes”.

    Application domain indexes

    This type of index is created by a user for data in an application-specific domain. The physical index need not use a traditional index structure and can be stored either in the Oracle database as tables or externally as a file. See “Application Domain Indexes”.”

Oracle salary of emp which > salary of his manager

select e.empno,e.ename,e.sal from emp e
where e.sal > (select m.sal from emp m where m.empno =(select mgr from emp mg where e.empno = mg.empno) and m.empno = e.mgr)

SELECT
*
FROM
EMP EM
WHERE
SAL  > (SELECT SAL FROM EMP WHERE EMPNO  = EM.MGR)

--------------
SELECT E.SAL,E.EMPNO,E.ENAME,M.SAL MSAL
  FROM EMP E,EMP M
  WHERE M.EMPNO = E.MGR
  AND E.SAL > M.SAL

Oracle SUM Analytic Function

SUM Analytic Function
This article gives an overview of the SUM analytic function. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Setup
SUM as an Aggregate Function
SUM Analytic Function
Quick Links
Related articles.

Analytic Functions : All Articles
Setup
The examples in this article require the following table.

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
SUM as an Aggregate Function
The SUM aggregate function returns the sum of the specified values in a set. As an aggregate function it reduces the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated value. In the following example we see the total value of the salaries for all employees.

SELECT SUM(sal) AS sum_total
FROM   emp;

 SUM_TOTAL
----------
     29025

SQL>
We can get more granularity of information by including a GROUP BY clause. In the following example we see the sum of the salaires on a per-department basis.

SELECT deptno,
       SUM(sal) AS sum_total_by_dept
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO SUM_TOTAL_BY_DEPT
---------- -----------------
        10              8750
        20             10875
        30              9400

SQL>
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.

SUM Analytic Function
The basic description for the SUM analytic function is shown below. The analytic clause is described in more detail here.

SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause)
Omitting a partitioning clause from the OVER clause means the whole result set is treated as a single partition. In the following example we display the total salaries of all employees, as well as all the original data.

SELECT empno,
       ename,
       deptno,
       sal,
       SUM(sal) OVER () AS total_sal
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL  TOTAL_SAL
---------- ---------- ---------- ---------- ----------
      7369 SMITH              20        800      29025
      7499 ALLEN              30       1600      29025
      7521 WARD               30       1250      29025
      7566 JONES              20       2975      29025
      7654 MARTIN             30       1250      29025
      7698 BLAKE              30       2850      29025
      7782 CLARK              10       2450      29025
      7788 SCOTT              20       3000      29025
      7839 KING               10       5000      29025
      7844 TURNER             30       1500      29025
      7876 ADAMS              20       1100      29025
      7900 JAMES              30        950      29025
      7902 FORD               20       3000      29025
      7934 MILLER             10       1300      29025

SQL>
Adding the partitioning clause allows us to display total salary within a partition.

SELECT empno,
       ename,
       deptno,
       sal,
       SUM(sal) OVER (PARTITION BY deptno) AS total_sal_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL TOTAL_SAL_BY_DEPT
---------- ---------- ---------- ---------- -----------------
      7782 CLARK              10       2450              8750
      7839 KING               10       5000              8750
      7934 MILLER             10       1300              8750
      7566 JONES              20       2975             10875
      7902 FORD               20       3000             10875
      7876 ADAMS              20       1100             10875
      7369 SMITH              20        800             10875
      7788 SCOTT              20       3000             10875
      7521 WARD               30       1250              9400
      7844 TURNER             30       1500              9400
      7499 ALLEN              30       1600              9400
      7900 JAMES              30        950              9400
      7698 BLAKE              30       2850              9400
      7654 MARTIN             30       1250              9400

SQL>
Adding the ORDER BY clause allows us to display a running total salary within a partition. In the example below, the default windowing clause is used, as well as being specified explicitly.

SELECT empno,
       ename,
       deptno,
       sal,
       SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS running_tot_sal_by_dept_1,
       SUM(sal) OVER (PARTITION BY deptno ORDER BY sal
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_tot_sal_by_dept_2
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL RUNNING_TOT_SAL_BY_DEPT_1 RUNNING_TOT_SAL_BY_DEPT_2
---------- ---------- ---------- ---------- ------------------------- -------------------------
      7934 MILLER             10       1300                      1300                      1300
      7782 CLARK              10       2450                      3750                      3750
      7839 KING               10       5000                      8750                      8750
      7369 SMITH              20        800                       800                       800
      7876 ADAMS              20       1100                      1900                      1900
      7566 JONES              20       2975                      4875                      4875
      7788 SCOTT              20       3000                     10875                     10875
      7902 FORD               20       3000                     10875                     10875
      7900 JAMES              30        950                       950                       950
      7654 MARTIN             30       1250                      3450                      3450
      7521 WARD               30       1250                      3450                      3450
      7844 TURNER             30       1500                      4950                      4950
      7499 ALLEN              30       1600                      6550                      6550
      7698 BLAKE              30       2850                      9400                      9400

SQL>