Table creation
create table EMP_T1
(
EMPNO NUMBER,
EMPNAME VARCHAR2(40),
SAL NUMBER
)
insert into emp_t1 values (1, pankaj ,1000);
insert into emp_t1 values (1, pankaj ,1000);
insert into emp_t1 values (1, pankaj ,1000);
insert into emp_t1 values (1, pankaj ,1000);
--1 --Distinct
------------------
select distinct empno,empname,sal from emp_t1
=========================================================
--2-- Unique
-------------
select unique empno,empname,sal from emp_t1
==============================================================
--3--group by
-------------------------------------
select empno,empname,sal from emp_t1 group by empno,empname,sal
=========================================================
---4 union
-----------------------------------
select empno,empname,sal
from emp_t1
union
select empno,empname,sal
from emp_t1
============================================================
---5 Intersect
--------------------------------
select empno,empname,sal
from emp_t1
intersect
select empno,empname,sal
from emp_t1
===========================================================
---6 minus
---------------------------------
select empno,empname,sal
from emp_t1
minus
select null,null,null
from emp_t1
=========================================================
---7 --row_number ()
-----------------------------------------
select * from (
select empno,empname,sal, row_number() over (partition by empno,empname,sal order by empno,empname,sal ) R
from emp_t1 ) Emp
where R=1
===============================================================
----8 row id
-----------------------------------
select empno,empname,sal
from emp_t1 where rowid in
( select min(rowid) from emp_t1 group by empno,empname,sal )
===============================================================
----9 corelated subquery
----------------------------------------
select empno , empname , sal
from emp_t1 A
where 1 = ( select count(1) from emp_t1 b where
b.empno=a.empno
and b.empname=a.empname
and a.sal=b.sal
and a.rowid >=b.rowid)
================================================================
