Inline view ?

If we write a select statement in from clause that is nothing but inline view.

What’s the difference between View and Materialized View?

View:

Why Use Views?

1. To restrict data access

2. To make complex queries easy

3. To provide data independence

  A simple view is one that:

– Derives data from only one table

– Contains no functions or groups of data

– Can perform DML operations through the view.

 A complex view is one that:

– Derives data from many tables

– Contains functions or groups of data

– Does not always allow DML operations through the view.

A view has a logical existence but a materialized view has a physical existence. Moreover a materialized view can be Indexed, analysed and so on....that is all the things that  we can do with a table can also be done with a materialized view.

We can keep aggregated data into materialized view. we can schedule the MV to refresh but table can’t.MV can be created based on multiple tables.

Materialized View:

In DWH materialized views are very essential because in reporting side if we do aggregate calculations as per the business requirement report performance would be de graded. So to improve report performance rather than doing report calculations and joins at reporting side if we put same logic in the MV then we can directly select the data from MV without any joins and aggregations. We can also schedule MV (Materialize View).

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.

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

Ex2:    SQL> select empno,ename,job,dname,loc from emp e,dept d where 

             e.deptno=d.deptno(+);

Right Outer Join

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

Ex: SQL> select empno,ename,job,dname,loc from emp e right 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.

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

                                    OR

SQL> select p.part_id, s.supplier_name

             from part p, supplier s

             where p.supplier_id = s.supplier_id (+)

             union

             select p.part_id, s.supplier_name

             from part p, supplier s

             where p.supplier_id (+) = s.supplier_id;

Cross Join

This will gives the cross product.

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

Natural Join

Natural join compares all the common columns.

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

Self Join

Joining the table itself is called self join.

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

Non-Equi Join

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

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

Equi Join/Inner Join

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

USING CLAUSE

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

ON CLAUSE

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