How to delete duplicate rows from a table in SQL ?

How to delete duplicate rows from a table in SQL ?

Recently, I have attended written test for a company. The written test consists of 2 java programs, and 3 sql queries. In those 3 queries, this was also one question.

More Details about the question:

the table data is like below.

the output should be like below. That means, you need to remove duplicate rows from table.

first lets create the table.

CREATE TABLE EMP (EMP_ID INTEGER, EMP_NAME VARCHAR2(20), SALARY INTEGER);

Now insert the values into table EMP.

INSERT INTO EMP VALUES  (1,'sreenath',10000);
INSERT INTO EMP VALUES  (1,'sreenath',10000);
INSERT INTO EMP VALUES  (2,'ulasala',20000);
INSERT INTO EMP VALUES  (3,'ulasala sreenath',30000);
INSERT INTO EMP VALUES  (3,'ulasala sreenath',30000);

To solve this problem, first you need to query only distinct records from EMP and then insert those records into a temporary table. for that use below query.

query for distict records is

i.e select distinct * from emp;

query for copying distinct records to temporary table is

create table emp_distinct as select distinct * from emp;

Now drop the existing table EMP.

drop table EMP;

now rename emp_distinct to emp, query is

rename emp_distinct to emp;

finally commit and query the records from emp

select * from emp;

Finally, you'll have unique records in the table.