oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



CUBE, ROLLUP and CASE Expression in Oracle

ROLLUP

The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM, ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates.

For example, given three expressions (n=3) in the ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.

Rows grouped on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.

The following query uses rollup operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales
     group by rollup(prod,year);

CUBE

The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.

For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows.

The following query uses CUBE operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales
     group by CUBE(prod,year);

CASE EXPRESSION

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.

For example the following query uses CASE expression to display Department Names based on deptno.

select empno,ename,sal,CASE deptno when 10 then  
  ‘Accounts’ when 20 then ‘Sales’
              when 30 then ‘R&D’
               else “Unknown’ end
                   from emp;

The following statement finds the average salary of the employees in the employees table using $2000 as the lowest salary possible:

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal
   ELSE 2000 END) "Average Salary" from emp e;

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved