SQL Interview Questions/Removing Duplicate Records :-6

6.HOW TO DELETE THE DUPLICATES RECORDS  IN A TABLE.

 create table duplicate_records

(

id int,

name varchar(100),

age int,

address varchar(100),

gmail nvarchar(100),

aadhar bigint,

)

insert into duplicate_records values (1,'vinela',34,'nellore','vinni@gmail.com',5547895),

(1,'vinela',34,'nellore','vinni@gmail.com',5547895),

(3,'divya',12,'vijaywada','divya@gmail.com',2345667),

(4,'kavya',12,'kovur','kavya@gmail.com',2123265),

(4,'kavya',12,'kovur','kavya@gmail.com',2123265),

(2,'srivedi',45,'muthukur','srivedi@gmail.com',4563544),

(2,'srivedi',45,'muthukur','srivedi@gmail.com',4563544),

(2,'srivedi',45,'muthukur','srivedi@gmail.com',4563544)

select * from duplicate_records


method 1:-
 
SELECT DISTINCT * FROM duplicate_records

output:-
                                        

method 2:-
  
SELECT id,name,address,gmail,aadhar,COUNT(id) AS CountOfRecords 
FROM duplicate_records GROUP BY  id,name,address,gmail,aadhar

output:-         

        



method 3:-

SELECT id,name,address,gmail,aadhar 

FROM duplicate_records GROUP BY  id,name,address,gmail,aadhar

output:-

                                

method 4:-

;WITH CTE

AS (SELECT id,name,age,gmail,address,aadhar,

           ROW_NUMBER() OVER(PARTITION BY name

           ORDER BY id) AS DuplicateCount

    FROM duplicate_records)

SELECT id,name,age,address,gmail,aadhar FROM CTE WHERE DuplicateCount=1 ORER BY  id;

output:-

                             

method 5:-

SELECT id,name,age,address,gmail,aadhar FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS DuplicateCount FROM duplicate_records) A WHERE DuplicateCount=1

output:-

                                  

method 6:-

with cte as (  SELECT *, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS DuplicateCount FROM duplicate_records  ) DELETE from cte where DuplicateCount >1

output:-

                                   


















                                 



   


Comments

Post a Comment

Popular posts from this blog

SQL Interview Questions:3

Image
3. FETCH THE EMPLOYEES WHOSE SALARY IS GREATER THAN AVERAGE SALARY OF THEIR DEPARTMENT. CREATE   table employee ( id int, name varchar (100), dept_Id int, salary int ) insert into employee values(1,'vineela',1,20000),(2,'lakshmi',1,30000),(3,'gayithri',1,30000), (4,'mukundh',2,45000),(5,'kavya',2,300000),(6,'sithara,2,45000) select * from employee method1:-         select a.name,a.dept_Id,a.salary  from employee a where a.salary >(select avg(salary) from         employee b where b.dept_Id=a.dept_Id)                                              method2:- select t1.name,t1.dept_Id, ,t1.salary  from employee as t1 join (select dept_Id,avg(salary) as avgsalary from employee group by dept_Id) as t2 on t1.dept_Id=t2.dept_Id where salary>avgsalary             ...
Read more

AZURE DATA FACTORY INTERVIEW QUESTIONS: FILES FORMATS

  21.Diffrence types of file formats? 1.Delimited text files       CSV Files and TSV Files   CSV Files:-            *Comma Separated Values           *  In which comma ,pipe etc. are used to separate the fields and values in CSV files.  TSV Files:-           * Tab separated values.           *In which tab or space are used to separate the fields and values.           *Fixed width data in which each field is allocated a fixed number of characters. 2.JSON           *Java Script Object Notation (JSON).            * JSON format file is a standard data interchange format.            *JSON files are text-based, human-readable, can be edited easily.     example:-          {         ...
Read more

AZURE DATA FACTORY INTERVIEW QUESTIONS :ACTIVITIES AND TYPES.

  ACTIVITIES:-      The Activities in a pipeline defines actions or operations to perform on your data. TYPES:-        1.DATA MOVEMENT ACTIVITIES.       2.DATA TRANSFORMATION  ACTIVITIES.       3. CONTROL FLOW ACTIVITIES.   1. DATA MOVEMENT ACTIVITIES :-         Copy Data :-                   The Copy Data Activity is the core tool in Azure data factory that can move the data from Source to Sink (destination). 2. DATA TRANSFORMATION  ACTIVITIES :-         Data flow:-               Data flow activity is a cloud native graphical data transformation tool.             types:-             1.Mapping Data flow             2.Wrangling Data flow.          1.Mapping Data flow:- ...
Read more