SQL Interview questions | Data Analyst | Part - 3

This video is the third 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) Finding the duplicates and deleting the entries
2) Finding the entries to keep and delete the rest of everything

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),
(998, 'Aviral', 24, 20000);

Timestamps:
00:00 Introduction
00:05 Interview question
00:46 MySQL workbench
01:30 Method 1 - Solution 1
06:50 Method 1 - Solution 2
10:30 Temporary tables in MySQL
11:49 Method 2
14:59 Outro

Music credits:
-- License: Attribution NonCommercial 4.0

[akashkhantwal]

Amazing video sir, Very informative❤❤❤, Its help me alot

[rajm]

thnks fr xplng wndws fnctins clearly, if possble cn u xpln any healthcare project

[sweetsubha]

Your efforts are highly appreciated 👍👍

[mahenderchilagani]

Very well explained, thanks for the videos.

[akashkhantwal]

Sir, when i am applying this command why it is showing error for delete duplicate entry method which you apply first

delete from emp
where emp_id in(select emp_id from (
select *, row_number() over(partition by name, age, salary order by emp_id) as row_num from emp
) as table1 where row_num>1);

this error is coming

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.016 sec

[yashikachugh]

In case of Ans 1)
basis approach of row_number
Here Aviral has 2 id's and where row_num is greater than 1 you deleted that record randomly, generally if nothing is specified what is the best way to explain the interviewer that how to decide which record to delete) also in that case the same solution would not work row_number >1, then should we use rank or dense rank function as it give same value same rank and we can pull those values and then decide? (just thinking)
group by approach is fine

[BE__jagdishChintamani]

Hello sir,
Please make detailed videos on
Python libraries
Pandas
Numpy
Matplotlib
Seaborn

[AbhishekSharma-vmtr]

nice video are they ask these type of questions to fresher or not do they ask hard questions for data analytics

[rokithkumar]

Appreciated bro 👍🏻 is there any chance we get matplotlib after this series?