-- DDL Required
drop table employees;
create table employees(
emp_id number,
emp_name varchar2(200),
salary number,
department varchar2(30)
);
insert into employees(emp_id,emp_name,salary,department) values (1,'Dhaval',30000,'IT');
insert into employees(emp_id,emp_name,salary,department) values (1,'Dhaval',30000,'IT');
insert into employees(emp_id,emp_name,salary,department) values (2,'Aman',45000,'IT');
insert into employees(emp_id,emp_name,salary,department) values (3,'Tejas',32000,'Admin');
insert into employees(emp_id,emp_name,salary,department) values (4,'Vinay',30000,'IT');
insert into employees(emp_id,emp_name,salary,department) values (3,'Tejas',32000,'Admin');
insert into employees(emp_id,emp_name,salary,department) values (6,'Payal',40000,'HR');
insert into employees(emp_id,emp_name,salary,department) values (6,'Payal',40000,'HR');
insert into employees(emp_id,emp_name,salary,department) values (4,'Vinay',30000,'IT');
select * from employees;
-- 1. using distinct
select distinct emp_id,emp_name,salary,department from employees
order by 1;
-- 2. using unique
select unique emp_id,emp_name,salary,department from employees
order by 1;
-- 3. using group by
select * from employees
group by emp_id,emp_name,salary,department
order by 1;
-- 4. using Analytical function
--row_number
select emp_id,emp_name,salary,department from
(select e.*,
row_number() over(partition by emp_id,emp_name,salary,department order by emp_id) rn
from employees e
)
where rn =1;
-- 5. rank
select emp_id,emp_name,salary,department from
(select e.*,
rank() over(partition by emp_id,emp_name,salary,department order by rownum) rn
from employees e
)
where rn =1;
-- 6. dense_rank
select emp_id,emp_name,salary,department from
(select e.*,
dense_rank() over(partition by emp_id,emp_name,salary,department order by rownum) rn
from employees e
)
where rn =1;
-- 7. using min/max rowid
select emp_id,
emp_name,
salary,
department
from
(select e.emp_id,
e.emp_name,
e.salary,
e.department,
min(rowid) from employees e
group by e.emp_id,e.emp_name,e.salary,e.department
order by 1);
select emp_id,
emp_name,
salary,
department
from
(select e.emp_id,
e.emp_name,
e.salary,
e.department,
max(rowid) from employees e
group by e.emp_id,e.emp_name,e.salary,e.department
order by 1);
-- 8. we can also put the order by in the outer query
select emp_id,
emp_name,
salary,
department
from
(select e.emp_id,
e.emp_name,
e.salary,
e.department,
max(rowid) from employees e
group by e.emp_id,e.emp_name,e.salary,e.department
)
order by 1;
-- 9. the same above query can be used with the IN clause
select * from employees
where rowid in (select min(rowid)
from employees e
group by e.emp_id,e.emp_name,e.salary,e.department
);
-- 10. the same above query can be used with the correlated query
select * from employees e
where 1 = (select count(1) from
employees r
where e.emp_id = r.emp_id
and e.emp_name = r.emp_name
and e.salary = r.salary
and e.department = r.department
and e.rowid >= r.rowid
)
order by 1;
-- 11. using UNION operation
select emp_id,emp_name,salary,department from employees
union
select emp_id,emp_name,salary,department from employees;
-- 12. using better UNION Operation
select emp_id,emp_name,salary,department from employees
union
select null, null, null, null from dual where 1=2
; -- This is never going to be executed
-- 13. other set operators like Intersect, Minus will do our work
select emp_id,emp_name,salary,department from employees
intersect
select emp_id,emp_name,salary,department from employees;
select emp_id,emp_name,salary,department from employees
minus
select null,null,null,null from employees;
