3 Ways to Check Duplicate Record in table Oracle Sql

1CREATE TABLE TEST_EMP
2(
3  ENAME   VARCHAR2(10 BYTE),
4  DEPTNO  NUMBER(2),
5  ID      NUMBER
6)

 1SET DEFINE OFF;
 2Insert into TEST_EMP (ENAME, DEPTNO, ID)
 3 Values ('SMITH', 20, 1);
 4Insert into TEST_EMP (ENAME, DEPTNO, ID)
 5 Values ('ALLEN', 30, 2);
 6Insert into TEST_EMP (ENAME, DEPTNO, ID)
 7 Values ('SMITH', 20, 3);
 8Insert into TEST_EMP (ENAME, DEPTNO, ID)
 9 Values   ('VIKAS', 10, 4);
10Insert into TEST_EMP   (ENAME, DEPTNO, ID)
11 Values   ('VIKAS', 10, 6);
12Insert into TEST_EMP   (ENAME, DEPTNO, ID)
13 Values   ('ASHISH', 40, 7);
14Insert into TEST_EMP   (ENAME, DEPTNO, ID)
15 Values   ('SATISH', 50, 8);
16Insert into TEST_EMP   (ENAME, DEPTNO, ID)
17 Values   ('SATISH', 50, 9);
18COMMIT;

1SELECT * FROM test_emp
2WHERE ID NOT IN 
3(SELECT MIN (ID)  FROM test_emp GROUP BY ename);
4
5Or
6
7SELECT * FROM test_emp
8WHERE ID NOT IN 
9(SELECT max (ID)  FROM test_emp GROUP BY ename);

1SELECT * FROM test_emp a
2      WHERE ID > (SELECT MIN (ID)
3                  FROM test_emp b
4                  WHERE a.deptno = b.deptno AND a.ename = b.ename);

1SELECT * FROM test_emp
2      WHERE ID IN (
3       SELECT ID FROM 
4       			(SELECT ID,
5                        ROW_NUMBER () OVER (PARTITION BY ename, deptno ORDER BY ID) r
6                     FROM test_emp)
7               WHERE r > 1);