What is ORACLE_HOME & ORACLE_BASE..??

What is ORACLE_BASE used for?

• The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
• Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.

What is ORACLE_HOME used for?

• The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
• The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
•  This directory can be used by any user who wants to use the particular database.
• If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
• Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
  ORACLE_HOME=$ORACLE_BASE\product\11.2.0

If no profile file is set with environment variables, then physically also be set as follows:

C Shell:
% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/11.2.0

On Windows Systems:
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)

Another way to physically set the variables as follow at the DOS prompt:
C:\> set ORACLE_HOME=C:\oracle\app\product\11.2.0
C:\> echo %ORACLE_HOME%

After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.

Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.

Or in Windows box, if you are having C, D and E drive then by default installation will be done on Last drive available, here is E drive.

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:

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);