Difference between Group By and Partition By.

Group By Clause	Partition By Clause
Reduces the no. of records	No. of records will not be reduced
Any non group by column is not allowed in the select clause. Example : SELECT deptno ,COUNT(*) DEPT_COUNT FROM emp GROUP BY deptno;	Any non group by column is  allowed in the select clause. Example: SELECT empno, deptno ,COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT FROM emp;
Group by actually groups the result set returning one row per group.	Analytic functions (Partition by) give aggregate result they do not group the result set means They return the group value multiple times with each record.

How to find duplicate records if one of the column has unique value.

SELECT Count(*)
FROM   (
        SELECT DISTINCT
               Col1
             , Col2
             , Col3
        FROM   table_name
       ) As AAA

Difference Between IN and EXISTS

	IN	EXIST
1	IN is faster than EXISTS, when the sub-query results is very small.	EXISTS is much faster than IN, when the sub-query results is very large.
2	IN can't compare anything with null, so it has no result for null. For Example:Select * from Table where Table_ID in (select null) Above one would return empty.	EXISTS can compare everything with null. Select *  from Table where exists (select null). Above query will return all the records
3	If you are using the IN operator, the SQL engine will scan all records fetched from the inner query	On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.
4	If Most of the filter criteria is in subquery then better to use IN	If most of the filter criteria is in main query then better to use EXISTS.

Difference Between VIEW and Materialized View

View	Materialized view
1.  In Views query result is not stored in the disk or database.	1. Materialized view allows to store query result in disk or table.
2. When we create view using any table, rowid of view is same as original table.	2. In case of Materialized view rowid is different.
3.  In case of View we always get latest data.	3. Materialized view we need to refresh the view for getting latest data.
4. In case of view we don't need extra trigger or some automatic method to refresh the view.	4. In case of Materialized view we need extra trigger or some automatic method so that we can keep Materialized view refreshed.

Difference Between CASE and DECODE

CASE	DECODE
1.CASE expression complies with ANSI SQL	1. DECODE is specific to Database vendors (like Oracle, SQL Server)
2. Case can compare discrete as well as Range value	2. DECODE compare discrete values not Range
3. CASE can work with logical operators, predicates and searchable subqueries.	3. DECODE can work with only scaler values
4. CASE is a statement.	4. DECODE is a function.
5. CASE expects datatype consistency.	5. DECODE does not expects datatype consistency.

Cumulative Sum or LAG function Example

 SELECT EMPNO, ENAME, SAL, NVL(SAL,0)+LAG(SAL,1,0) OVER (ORDER BY SAL)  SUM_SAL
 FROM EMP
 ORDER BY SAL

EMPNO	ENAME	SAL	SUM_SAL
7369	SMITH	800	800
7900	JAMES	950	1750
7876	ADAMS	1100	2050
7521	MORGAN	1250	2350
7654	MARTIN	1250	2500
7934	MILLER	1300	2550
7844	TURNER	1500	2800
7499	SMALL	1600	3100
7782	FOOTE	2450	4050
7698	BLAKE	2850	5300
7566	NORGAARD	2975	5825
7788	SCOTT	3000	5975
7902	LOFSTROM	3000	6000
7839	ELLISON	5000	8000