SQL Interview questions | Data Analyst | Part - 4

This video is the fourth part of our series on SQL interview questions and answers. This series is specifically designed for people targeting jobs as data analysts, data scientists, data engineers, and business analysts.

In this video, I have explained how to delete duplicate entries in our database using two types of methods
1) Fetch unique values using the DISTINCT keyword and store the output in a table
2) Add a new temporary column using the ALTER TABLE command and use the column to remove the duplicate entries

Use the below the commands to create your own database and table:
CREATE DATABASE THEMLMINE;
USE THEMLMINE;

CREATE TABLE emp
(
emp_id INT,
name varchar(20),
age INT,
salary FLOAT
);

INSERT INTO emp VALUES
(102, 'Aviral', 24, 20000),
(103, 'Arohi', 28, 350000),
(104, 'James',35, 120000),
(102, 'Aviral', 24, 20000);

Timestamps:
00:00 Introduction
00:06 Interview question
01:18 MySQL workbench
02:50 Method 1 - Solution 1 (using DISTINCT keyword)
05:05 Method 1 - Solution 2 (using DISTINCT keyword)
07:37 Method 2 (adding a temporary column)
14:09 Summary
14:16 Outro
14:24 Call to action

Music credits:
-- License: Attribution NonCommercial 4.0

[akashkhantwal]

Sir, Very informative video, your this playlist cover almost all the important question for fresher

[prajwaladhav]

I think using CTE for this kind of case will be a good practice, with cte as
(
SELECT *,
ROW_NUMBER() OVER (partition by emp_id ORDER BY emp_id) as row_num
from emp
)
DELETE FROM cte
where row_num>1

[hridayjain]

Heyyy stumbled across this video in my fyp and found it reallyy great!!!
I liked your method of teaching a lot!!🤗

[butharajuanusha]

Hello! Can you please make videos on scala spark, hive and also about the real time projects, the way you explain is simply amazing and clear I have been searching for the interview questions on data analysts like hell on internet but this video got me.thank you so much for your efforts and please do videos on what I request

[micro_parameshi]

DELETE FROM employee
WHERE id NOT IN (
SELECT MIN(id)
FROM table_name
GROUP BY column1, column2, ...
);