Examine the structure of the customers table:
CUSTNO is the primary key in the table. You want to find out if any customers’ details have
been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result?
A.
Self-join
B.
Subquery
C.
Full outer-join with self-join
D.
Left outer-join with self-join
E.
Right outer-join with self-join
Answer A,B is correct,
Example
—find duplicate using self join where employee_id is primary key
select e1.FIRST_NAME,e2.LAST_NAME from employees e1
inner join employees e2
on (e1.first_name=e2.first_name and e1.last_name=e2.last_name)
and e1.employee_id e2.employee_id
—find duplicate using group by
select FIRST_NAME,LAST_NAME
from employees
group by FIRST_NAME,LAST_NAME
having count(*) > 1
Hi Deepak,
The last condition in the first example is not valid. It will not give the duplicates.
Instead we can use the below sub query
SELECT FIRST_NAME , LAST_NAME,COUNT(FIRST_NAME), COUNT(LAST_NAME) FROM SCOTT.EMP WHERE (FIRST_NAME,LAST_NAME) = ANY (SELECT FIRST_NAME , LAST_NAME FROM SCOTT.EMP)
GROUP BY FIRST_NAME , LAST_NAME HAVING COUNT(FIRST_NAME) > 1 AND COUNT(LAST_NAME) > 1;
A, B