Interview Questions -- SQL Scenarios -- Part-1
Scenario-1: How to Get/See/Display duplicate records from the given table
Sample Scripts :-
Scenario-2: How to Delete Duplicate records from the given table
Expected Output :-
-- How to go inside to the Database
USE tempdb
GO
-- How to Create New Table
CREATE TABLE EMP
(
EMPID INT
,EMPNAME VARCHAR(50)
,SAL MONEY
)
-- INSERT Sample Data Into the Table.
INSERT INTO EMP
SELECT 1,'Santhosh',10000
UNION ALL
SELECT 2,'Kumari',8000
UNION ALL
SELECT 2,'Kumari',8000
UNION ALL
SELECT 3,'Kamesh',75000
UNION ALL
SELECT 3,'Kamesh',75000
UNION ALL
SELECT 3,'Kamesh',75000
SELECT * FROM EMP
Expected Output :-
Scenario-2: How to Delete Duplicate records from the given table
Expected Output :-
Guys you can share your Query Through Comments.
Q1.By Using CTE Table, Row_Number and Where condition To find Duplication records.
ReplyDeleteQuery:
WITH EMP_CTE
AS
(
Select EMPID,
EMPNAME,
SAL,
ROW_NUMBER() over(Partition by EMPID Order by EMPID) As Rank_Empid
from emp
)
SELECT*FROM EMP_CTE
where Rank_Empid>1
Q2.To Delete Duplicate records use Distinct.
ReplyDeleteQuery:
Select Distinct * from emp
Thanks Bala for providing the description and queries in this blog. Really useful for all... :)
ReplyDelete- Shashi, Bangalore
Thank You Guys for your time.
ReplyDeleteQ1)TO DISPLAY THE DUPLICATE RECORDS
ReplyDeleteSELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY EMPID DESC)ROW_ID
FROM EM
)E1
WHERE ROW_ID>1
Q2)DELETE DUPLICATE RECORDS
ReplyDeleteFIRST CREATE IDENTITY COLUMN (ID) FRO DUPLICATE RECORDS IDENTIFICATION
DELETE FROM EMP
WHERE ID IN
(
SELECT ID FROM
(
SELECT *
,ROW_NUMBER() OVER(PARTITION BY EMPID,EMPNAME,SAL ORDER BY ID DESC)AS ROW_ID
FROM EMP
)EMP1
WHERE ROW_ID>1
)
;with cte as
ReplyDelete(
select empid,empname,DENSE_RANK() over(order by sal desc) as rn from emp1
)
delete from cte where rn>1