SOME IMPORTENT DEFFERENCE

Difference between TRUNCATE and DELETE commands
===========================================================

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause




difference between decode and case.

=========================================
in which case we are using case and in which case we are using decode?

   
      DECODE can be used Only inside SQL statement But CASE can be used any where even as a parameter of a function/procedure
  
      DECODE can only compare discrete values (not ranges) continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1 Oracle introduced the searched CASE statement which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting).
   
      CASE is almost always easier to read and understand and therefore it's easier to debug and maintain.
   
      Another difference is CASE is an ANSI standard where as Decode is proprietary for Oracle.
   
      Performance wise there is not much differences. But Case is more powerful than Decode.



LAG

========
The LAG function is used to access data from a previous row.

LEAD

==========
The LEAD function is used to return data from the next row.



Rollup Note:
================>
ROLLUP enables a SELECT statement to across a specified group ofcalculate multiple levels of subtotals  dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

select deptno,job,sum(sal)
            from emp
            where deptno  < 60
            group by rollup (deptno,job);


Cube Note:
===============>
CUBE takes a specified set of grouping columns and creates subtotals for all of their possible combinations. In terms of multidimensional analysis, CUBE generates all the subtotals that could be calculated for a data cube with the specified dimensions. If you have specified CUBE(time, region, department), the result set will include all the values that would be included in an equivalent ROLLUP statement plus additional combinations.


select deptno,job,sum(sal)
            from emp
            where deptno  < 60
            group by cube (deptno,job);


FORM TRIGGER
==================>
(i) Isolated: - Masters Can be deleted when Child is existing

(ii) Non- Isolated: - Masters Cannot be deleted when Child is existing.

(iii) Cascading: - Child Record Automatically Deleted when Masters is deleted.