Oracle Group by clause along with ROLLUP or CUBE operators

Use the ROLLUP OPERATOR to produce subtotal values, ROLLUP IS AN EXTENSION OF GROUP BY CLAUSE, Use the CUBE OPERATOR to produce cross-tabulation values.  Use the GROUPING function to identify the row values created by ROLLUP or CUBE.  These operators and GROUPING function can best be used ALONG WITH GROUP FUNCTIONS, as group functions operate on a set of rows to give one result per group.

• Examples of Rollup:

          1. SELECT e.department_id, SUM(e.salary)

                       FROM employees e

                                   WHERE e.department_id < 30 

                                                 GROUP BY ROLLUP(e.department_id);

             Results:

          2. SELECT e.department_id, e.job_id, SUM(e.salary) 

                         FROM employees e

                                     WHERE e.department_id < 30 

                                                    GROUP BY ROLLUP(e.department_id, e.job_id);
              Results: 

•    Example of CUBE:

The cube operator is used to produce results sets that are typically used for cross-tabular reports. This means Rollup produces only one possible subtotaling where as Cube produces subtotal for all possible conditions of grouping specified in the group by clause and a grand total.

       1. SELECT e.department_id, SUM(e.salary) 

                          FROM employees e

                                      WHERE e.department_id < 30 GROUP BY CUBE(e.department_id);
           Results:

      2. The following query produces subtotaling results based on job, based on deptno and based on the                 individual jobs(clerk or analyst or manager etc in dept 10 and 20)  

          SELECT e.department_id, e.job_id, SUM(e.salary)
                         FROM employees e
                                     WHERE e.department_id < 30 GROUP BY CUBE(e.department_id, e.job_id);

          Results:

What is an OLAP and Explain OLAP's advantages with example?

OLAP (or Online Analytical Processing) has been growing in popularity due to the increase in data volumes and the recognition of the business value of analytics. It uses database tables (fact and dimension tables) to enable multidimensional viewing, analysis and querying of large amounts of data. E.g. OLAP technology could provide management with fast answers to complex queries on their operational data or enable them to analyze their company's historical data for trends and patterns.

OLAP allows business users to slice and dice data at will. Normally data in an organization is distributed in multiple data sources and are incompatible with each other. A retail example: Point-of-sales data and sales made via call-center or the Web are stored in different location and formats. It would a time consuming process for an executive to obtain OLAP reports such as - What are the most popular products purchased by customers between the ages 15 to 30?

Part of the OLAP implementation process involves extracting data from the various data repositories and making them compatible. Making data compatible involves ensuring that the meaning of the data in one repository matches all other repositories. An example of incompatible data: Customer ages can be stored as birth date for purchases made over the web and stored as age categories (i.e. between 15 and 30) for in store sales.The major OLAP vendors are Hyperion, Cognos, Business Objects, MicroStrategy.
OLAPs are designed to give an overview analysis of what happened. Hence the data storage (i.e. data modeling) has to be set up differently. The most common method is called the star design.

The central table in an OLAP start data model is called the fact table. The surrounding tables are called the dimensions. Using the above data model, it is possible to build reports that answer questions such as:

• The supervisor that gave the most discounts.
• The quantity shipped on a particular date, month, year or quarter.
• In which pincode did product A sell the most.

To obtain answers, such as the ones above, from a data model OLAP cubes are created. OLAP cubes are not strictly cuboids - it is the name given to the process of linking data from the different dimensions. The cubes can be developed along business units such as sales or marketing.Or a giant cube can be formed with all the dimensions.

OLAP can be a valuable and rewarding business tool. Aside from producing reports, OLAP analysis can aid an organization evaluate balanced scorecard targets.

Business intelligence refers to computer-based methods for identifying and extracting useful information from business data. OLAP (online analytical processing) as the name suggest is a compilation of ways to query multi-dimensional databases.

OLAP is a class of systems, which provide answers to multi-dimensional queries.Typically OLAP is used for marketing, budgeting, forecasting and similar applications. It goes without saying that the databases used for OLAP are configured for complex and ad-hoc queries with a quick performance in mind. Typically a matrix is used to display the output of an OLAP. The rows and columns are formed by the dimensions of the query. They often use methods of aggregation on multiple tables to obtain summaries.For example,it can be used to find out about the sales of this year in Wal-Mart compared to last year? What is the prediction on the sales in the next quarter? What can be said about the trend by looking at the percentage change?
OLAP tools provides multidimensional data analysis and they provide summaries of the data but contrastingly, data mining focuses on ratios, patterns and influences in the set of data.

Joins in Oracle with examples

1. The purpose of a join is to combine the data across tables.

2. A join is actually performed by the where clause which combines the specified rows of tables.

3. If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

Types:

1. Equi join
2. Non-equi join
3. Self join
4. Natural join
5. Cross join
6. Outer join

• Left Outer join
• Right Outer join
• Full Outer join

      7. Inner join

      8. On clause

      9. Using clause

Assume that, we have following tables in Oracle.

SQL> select * from dept;

SQL> select * from emp;

1. Equi Join: 

A join which contains an equal to ‘=’ operator in the joins condition.

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

2. Non-Equi Join:

A join which contains an operator other than equal to ‘=’ in the joins condition.

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno > d.deptno;

3. SELF JOIN

Joining the table itself is called self join.

SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr;

4. NATURAL JOIN

Natural join compares all the common columns.

SQL> select empno,ename,job,dname,loc from emp natural join dept;

5. CROSS JOIN

This gives the cross products.

SQL> select empno,ename,job,dname,loc from emp cross join dept;

6. OUTER JOIN

Outer join gives the non-matching records along with matching records.

• LEFT OUTER JOIN

           This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

SQL> select empno,ename,job,dname,loc from emp e left outer join dept d on(e.deptno=d.deptno);               OR

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);

• FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

7. INNER JOIN

This will display all the records that have matched.

SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

8. Using Clause

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

9. On Clause:

SQL> select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);