Top 10 SQL interview Questions and Answers | Frequently asked SQL interview questions.

In this video we will discuss most frequently asked top 10 SQL interview questions. If you have any interview I am sure 2-3 questions will be from this list.

[@ankitbansal6]

Master the art of with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch. www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english Course contains 2 portfolio projects and a bonus session on Tableau. 100+ interview problems to crack any SQL INTERVIEW.

[@avinashmishra4995]

Is it in udemy ?

[@ankitbansal6]

@@avinashmishra4995 nope

[@Rohitraj-mv3pz]

​@@ankitbansal6 bro i really need your help.. is there any way i can reach out to you..

[@bhartisingh3750]

Delete duplicate records query in not working in databricks notebook for deleting duplicate records from a table.. is there any way to do this in databricks notebook table?

[@OBLIVIONSHREE]

Bro you have taught everything for free in your channel only, why would I will but the course.

[@abhishek_grd]

Bang on ! Started product based companies interview prepration for Sr. DE role ( Google/Microsoft/Ubder/Amazon/Expedia / AirBnB) etc...and your content is crisp and clear. Thanks

[@ankitbansal6]

🙏🙏

[@mariecurie5245]

create table emp( emp_id int, emp_name varchar(20), department_id int, salary int, manager_id int, emp_age int); insert into emp values (1, 'Ankit', 100,10000, 4, 39; insert into emp values (2, 'Mohit', 100, 15000, 5, 48); insert into emp values (3, 'Vikas', 100, 10000,4,37); insert into emp values (4, 'Rohit', 100, 5000, 2, 16); insert into emp values (5, 'Mudit', 200, 12000, 6,55); insert into emp values (6, 'Agam', 200, 12000,2, 14); insert into emp values (7, 'Sanjay', 200, 9000, 2,13); insert into emp values (8, 'Ashish', 200,5000,2,12); insert into emp values (9, 'Mukesh',300,6000,6,51); insert into emp values (10, 'Rakesh',300,7000,6,50);

[@ankitbansal6]

Thank you 😊

[@satyanathparvatham4406]

thanks

[@pranav9808]

god bless you

[@kartikpidurkar9590]

create table orders( customer_name char(10), order_date date, order_amount int, customer_gender char(6) ); insert into orders values('Shilpa','2020-01-01',10000,'Male'); insert into orders values('Rahul','2020-01-02',12000,'Female'); insert into orders values('Shilpa','2020-01-02',12000,'Male'); insert into orders values('Rohit','2020-01-03',15000,'Female'); insert into orders values('Shilpa','2020-01-03',14000,'Male');

[@kartikpidurkar9590]

create table department( dept_id int, dept_name varchar(10) ); insert into department values(100,'Analytics'); insert into department values(300,'IT');

[@Ian15278]

with dup as ( select ROW_NUMBER() over(partition by emp_id order by emp_id desc) as Find_duplicate from emp) delete from emp where emp_id in ( select * from dup where Find_duplicate > 1);

[@saurabhtekale5084]

Performance wise why joins are better than subqurey??

[@manjumohan7731]

#For swap gender : update orders set customer_gender=IF(customer_gender='Male','Female','Male');

[@yashsoni2113]

DDL and Insert values for emp table. create table emp( emp_id int, emp_name varchar(20), department_id int, salary int, manager_id int, emp_age int); insert into emp values (1, 'Ankit', 100,10000, 4, 39), (2, 'Mohit', 100, 15000, 5, 48), (3, 'Vikas', 100, 10000,4,37), (4, 'Rohit', 100, 5000, 2, 16), (5, 'Mudit', 200, 12000, 6,55), (6, 'Agam', 200, 12000,2, 14), (7, 'Sanjay', 200, 9000, 2,13), (8, 'Ashish', 200,5000,2,12), (9, 'Mukesh',300,6000,6,51), (10, 'Rakesh',300,7000,6,50);

[@ankitbansal6]

Thanks brother 😊

[@yashsoni2113]

@@ankitbansal6 welcome ☺

[@addhyasumitra90]

create table department( dept_id int, dept_name varchar(10) ); insert into department values(100,'Analytics'); insert into department values(300,'IT'); create table orders( customer_name char(10), order_date date, order_amount int, customer_gender char(6) ); insert into orders values('Shilpa','2020-01-01',10000,'Male'); insert into orders values('Rahul','2020-01-02',12000,'Female'); insert into orders values('Shilpa','2020-01-02',12000,'Male'); insert into orders values('Rohit','2020-01-03',15000,'Female'); insert into orders values('Shilpa','2020-01-03',14000,'Male');

[@tupaiadhikari]

For the First time in 10 years, I am feeling confident with using SQL, only after watching your series of videos. Earlier I used Python to do data preprocessing after doing Vanilla Select * from Table Statement, which was not a recommended approach for memory efficiency.

[@ankitbansal6]

This is great. Best place to play with data is where it is residing 😊

[@rajmathichithramani5864]

Really your videos are clear and helpfull for the person who dont understand sql Properly. likes me ...Now i love SQL Very much

[@manishgnagpure]

Hi Ankit ji, I seen some of the videos and those are very interesting, and useful. I request you to create a video on SQL Index in details with examples. 1. Before creating index how the data loding in page level. And after creating index what kind of table ( page level) structure will be. 2. What will be structure while create only cluster and only noncluster and both. Mean every one can understand beginners to exp students. Waiting for your new video.

[@SubhamDas-tb3xf]

Hey Ankit! I was thinking about that delete duplicate question. U r deleting the duplicate data from cte which is a temporary result set , right? It is working fine but how it's deleting from original table? MySQL doesn't support this from 8+ versions I guess..we have to use that cte query like a subquery in mysql. But in ssis it is working. Even in leetcode ! But I think it is always better to reference the original table rather than querying from cte i.e a temporary result set. Selection is fine with cte but dml operation! Let me know about your thoughts, I would love to hear!

[@ammarfasih3866]

In the first question where we wanna find out the duplicates, we knew that the duplicates would be as per emp_id. How about if we wanna check if the entire row is unique or not rather than checking any particular column?

[@bhartisingh3750]

Delete duplicate records query in not working in databricks notebook for deleting duplicate records from a table.. is there any way to do this in databricks notebook table? @ankit

[@Ghost-of-uchihaaa]

This is my one-stop video before every interview for the past years. Brushes up my SQL skills like no other

[@harini2933]

Hey Ankit, I'm running below query for deleting duplicates -Q2 : WITH cte as (SELECT * , row_number() OVER( PARTITION BY emp_id ORDER BY emp_id ) AS rn FROM emp) DELETE FROM cte WHERE rn>1 but, I'm getting error : "Error Code: 1288. The target table cte of the DELETE is not updatable" . Kindly reply. TIA 😇

[@CoolGuy]

Try delete * . Which rdbms are you using?

[@yatinshekhar787]

hi , did you got the solution for the above query coz i am facing the same issue

[@harini2933]

@@yatinshekhar787 no I couldn't

[@masoodahmed6600]

before delete alias a will be give

[@Nagaj719]

with cte as ( select * ,row_number() over(partition by emp_id order by emp_id) as RN from emp) delete from cte where RN > 1

[@siddharthchoudhary103]

5th question can we also done used left anti right? like where dep.deptid is null?

[@shivangnirawat9536]

In the 2 question, delete from cte is written so how the records got deleted from original table

[@kunalmjain]

For me it is showing target table cte of the delete is not updatable

[@SK-wp4tm]

Please provide dataset for practice 🙏

[@vlog.444]

Bro good vide class Bren you have to increase your sound

[@moumitadebbarma8505]

how can I get the dataset to practice this?

[@NitishKumar-xr9tx]

In 2nd Question of removing duplicates: We are deleting values from the CTE table which is a TEMP table. Then how it is deleting values from the MAIN table?

[@DEwithDhairy]

PySpark Step by step solution of duplicate finding problem: kzbin.info/www/bejne/kJiTiWx3q5WFZ6csi=PfOt0c_pN99bNgqA

[@kavyareddykonakati9657]

Hi Ankit, what if I have same rows with all same values in 2 rows, how can I do that.? As the code you explained is not working in MySQL workbench. Thanks!

[@SubhamDas-tb3xf]

1. We might need the primary key in the original table. Without that how can you tell database which record to delete, if you can anyhow tell database 😂 that delete the records which I mentioned it will be exactly same as duplicates so everything will be deleted. First u need to assign primary key. 2. MySQL 8+ versions doesn't support deleting data from cte( it's a temporary result set so logically it should not change the original table. Not sure about ssis implementation. ) so u have to reference the original table after selecting duplicate records u gotta tell database to delete those using a subquery. Do this: With cte as (Select * , row_number() over(partion by all the columns names) as rn From Ur_table_name) Delete from ur_table_name where id in (select * from cte where rn > 1)

[@perumalbalachandranjj2358]

Thanks for this Video. I have one doubt, Question number 2. How to delete duplicates from a table? Shall we do it in mysql? I tried but I didn't get correct result.

[@manjumohan7731]

+1

[@sheikhshah2593]

Literally great. In all my SQL interviews one or the other is asked. Subscribed

[@yashsoni2113]

Delete duplicates query is not working in MySQL why so? is there any other query for MySQL to delete duplicates?

[@ankitbansal6]

Yes that works only in SQL server.

[@tradingtexi]

For q1, what does “1” mean in count(1)?

[@swarooppabbineedi1820]

Do you got what it is about..?

[@suman3316]

Very Useful...I hope part2 will come soon

[@ankitbansal6]

Yes it will

[@akp7-7]

Wow it wsd asked today delete duplicates..thanks for the videos.it is really really helpful

[@dhrubajyotichatterjee1883]

hi Ankit , can you please make some videos for product based companies. Thank yoou for your effort. Appriciatable

[@ankitbansal6]

I have a lot of them. Check out complex SQL playlist

[@nammibhargav9498]

Ankit bhai if you have time can you solve some hacker rank test questions for better understanding for people who are learning sql newly..that would be helpfull

[@ankitbansal6]

Do you have some links to the good problems?

[@ashuvermashorts7167]

the sql course amount is showing 2499/ do you have any link for your all the courses and if i join your courses then what will the total amount

[@ankitbansal6]

www.namastesql.com/buy-now/sql-python-tableau-zero-to-hero-self-paced

[@SuperMohit95]

When we do where rn = 2 we don't take into account the department that only has one employee for e.g in this video there is dep_id 900 that has only one employee.

[@ankitbansal6]

In that case there is no second highest 🙂

[@jenishkothari262]

Leetcode Top 50 Interview Problems - SQL Playlist kzbin.info/aero/PLsgq4reKtSzyEoZh2_Leks0CxfQb-zfPw

[@sushritsaha5287]

CREATE TABLE order_table ( customer_name VARCHAR(255), order_date DATE, order_amount INT, customer_gender VARCHAR(10) ); INSERT INTO order_table (customer_name, order_date, order_amount, customer_gender) VALUES ('Shilpa', '2020-01-01', 10000, 'Male'), ('Rahul', '2020-01-02', 12000, 'Female'), ('SHILPA', '2020-01-02', 12000, 'Male'), ('Rohit', '2020-01-03', 15000, 'Female'), ('shilpa', '2020-01-03', 14000, 'Male');

[@muruganselva7431]

with CTE as ( select * ,row_number() over(partition by emp_id order by emp_id) as rnk from emp order by emp_id asc ) delete from CTE where rnk > 1 From CTE we can't delete records?

[@shakthimaan007]

Questions: Q1 - How to find duplicates in a given table Q2 -> How to delete duplicates Q3 -> difference berween union ana union all Q4 -> diffrence between rank.row number and dense rank Q5 -> employees who are not present in deparament table Q6 > second highest salary in each dep Q7 -> find all transaction done by Shilpa Q8 -> self join, manager salary > emp salary Q9 -> Joins Left join/äner join Q10 -› update query to swap gender

[@avinashmishra4995]

@Ankit Bansal bhai if we have left whats the use of right join ?

[@ankitbansal6]

Not required 😁

[@SN-mp7hc]

Hi Ankit , when to use count(1) and when to use count(*) can you explain?

[@ankitbansal6]

Both are the same. Use anyone.

[@hairavyadav6579]

Hi Sir, Big thank to you i learn lot of things in sql after watching your video . I want to add to solution from my side please let me know this will fine or not? 1) with cte as ( select *,row_number() over(partition by emp_id order by time desc) as rnk from hospital) select count(*) from cte where rnk =1 and action ="in"; 2) select count(*) as total_person from hospital h where time =(Select max(time) from hospital h1 where h1.emp_id = h.emp_id) and action ="in"; Waiting for your response. Again big thank to you.

[@commentator2407]

query 2 : delete duplicates im getting this errror The target table cte of the DELETE is not updatable

[@ankitbansal6]

This works only in SQL server.

[@commentator2407]

@@ankitbansal6 is there an alternative for mysql?

[@gauravghosh8002]

Sir by looking at this video it seems SQL is very easy but actually it's not so easy sir, in interview same questions I faced nervous and end up giving wrong query.

[@High_jacked_by_Hormones]

In the 7th question you have taken only first name , if there is a surname or middle name would be there , can we also proceed with the same rule?

[@ankitbansal6]

Yes same will work.

[@DebolinaBanerjee-mz3bt]

@ankitbansal6 Hello Ankit, I need a small help ..Getting this error while running the query. Error Code: 1288. The target table cte of the DELETE is not updatable

[@ankitbansal6]

It works only in sql server

[@abhishek_grd]

Hello Ankit, I have onre request along with the explanation if you can share the create table command as well , will be helpful to dummy it to my own schema. Like here employee table

[@ankitbansal6]

I have create and insert in most of the problems description. I will make sure I don't miss in future. Thanks for pointing out 😊

[@suketshah7697]

very simple way to explain...Nice!

[@BeingSam7]

I just discovered you, subscribed you and started watching your videos. I was asked a question in an interview which is similar to q 6 but a li'l twist in it. Q - get 2nd highest salary from each dept and if a dept does not have a second salary then first salary should appear in result.

[@gouthamstar6558]

I asked a question a employee enters and leaves office and he swipes in and swipes out in multiple instances, so I want to find the total office hours he spent in the office( he may come at 10 PM and take a break at 12:30 AM in that case it shows that he is spending 2 hours in that day but that's not true right), so now I have to calculate total time he spent employee id, Office_swipe_record given

[@amangupta1959]

Hi Ankit, the only doubt I have is while deleting duplicate records, we deleted from CTE and not actual employee table, how records were deleted from emp table?

[@ankitbansal6]

Hi Aman , glad you asked this question. Since the CTE is referring to the table directly, it was possible. This might not be possible in all the databases but works in SQL server.

[@amangupta1959]

@@ankitbansal6 Right. I tried and even the below query is not working in MySQL. DELETE FROM (select *, row_number() over(partition by empno) as rn from emp) where rn>1;

[@DataRevolution10]

@@ankitbansal6 Hi Ankit, I am also facing same issue. I assume CTE will delete from parent table. it fails with "The target table CTE of the DELETE is not updatable "

[@ankitbansal6]

@@DataRevolution10 it works only in SQL server

[@manjumohan7731]

+1

[@ririraman7]

Thank you so much. Make vidoes on JOINS.

[@ankitbansal6]

Sure

[@muhammedar3071]

1m likes 👍

[@KaushikChowdri]

Your approach to Delete duplicate valuse from the table is not working in my sql (gettting this pop up "The target table cte1 of the DELETE is not updatable")

[@fired_developer]

Sir please raise the editor font more than 130% while recording , coz it's very hard to watch on the phone

[@mmohammedsadiq2483]

Where can i get dataset ,

[@KapilKumar-hk9xk]

but CTE doesn't support delete operations right...I tried it but got below error "The target table cte of the DELETE is not updatable" with cte as (select *, row_number() over (partition by md5(concat_ws(",",emp_id,emp_name,emp_age))) as dup_row_id from employee) delete from cte where dup_row_id>1;

[@ankitbansal6]

It works only in SQL server

[@KapilKumar-hk9xk]

@@ankitbansal6 ok. thanks for replying 😊

[@garimabatra2658]

@@ankitbansal6 What should be the approach for MYSQL?. Thanks in advance

[@adilbsh]

First time I am seeing delete on cte , I thought we can only do select on cte

[@ankitbansal6]

It works only in SQL server

[@suryanshsinghkarchuli9892]

for better reach, keep creating content

[@ankitbansal6]

sure.

[@pavitrashailaja850]

Thnks for the video. Very helpful 👍

[@gameply347]

I guess we can use subquery table as a normal table to display information using alias and where clause , but when it comes to comparison it needs to return only 1 value :) !!

[@saurav_gupta_1998]

make the join question's video please

[@ankitbansal6]

Here you go kzbin.info/www/bejne/roObaJaqhMyjd6c

[@mdjahidulislamrazan4209]

I solved it using self join: as we are performing row level aggregation, I guess this is a good case for that: SELECT DISTINCT(e1.emp_id), e2.val as salary, e3.val as bonus, e4.val as hike_percent FROM emp_compensation e1 JOIN emp_compensation e2 ON e1.emp_id = e2.emp_id AND e2.salary_component_type = 'salary' JOIN emp_compensation e3 ON e1.emp_id = e3.emp_id AND e3.salary_component_type = 'bonus' JOIN emp_compensation e4 ON e1.emp_id = e4.emp_id AND e4.salary_component_type = 'hike_percent'; Approach 2: with stats as ( SELECT emp_id, CASE WHEN salary_component_type = 'salary' THEN val END as salary, CASE WHEN salary_component_type = 'bonus' THEN val END as bonus, CASE WHEN salary_component_type = 'hike_percent' THEN val END as hike_percent FROM emp_compensation) SELECT emp_id, SUM(salary) as salary, SUM(bonus) as bonus, SUM(hike_percent) as hike_percent FROM stats GROUP BY emp_id

[@mdjahidulislamrazan4209]

Sorry I wanted to post in the section of this question: kzbin.info/www/bejne/hWfQdZeLfturb80

[@rakeshgopidi4066]

@Md Jahidul Islam Razan - Approach 1 - 2.44 seconds vs Approach 2 - 1.31 seconds , i have tested in databricks community edition ,since underlying database is usually mysql, postgressql i think, CTE is more quicker to output both approaches are good !

[@ritikajaiswal3824]

can you make a video on stored procedures.. from basic to everything needed to know

[@ankitbansal6]

Sure will plan.

[@yatinshekhar787]

5/123 Stuck with 2 ques , coz i'm using mysql

[@k.hariharasudan9335]

Q7. MSSQL does not depend on case-sensitive but in the Oracle is depend on the case-sensitive

[@shrishti8332]

Hi Ankit, what is diff btwn count(*), count (1) and count(0) ? please clear this concept.

[@ankitbansal6]

Sure

[@user-jc6te1vm8v]

absolutely no difference at all even performance wise ... all will be counting rows including NULL values ... however COUNT(COL_NAME) will act differently by counting all rows excluding NULL's ...

[@uttamx2016]

@@user-jc6te1vm8v you are right , I checked SELECT count([AddressLine2] ) FROM [AdventureWorks2017].[Person].[Address] -- 362 SELECT count([AddressLine2] ) FROM [AdventureWorks2017].[Person].[Address] -- 362 WHERE AddressLine2 IS NOT NULL SELECT [AddressLine2] FROM [AdventureWorks2017].[Person].[Address] -- 19,252 where [AddressLine2] is null

[@NaveenKumar-fq4sb]

Sir in gender swap operation NULL is coming entire column where is error???

[@KK-qc2qs]

these will be asked to freshers during interview?

[@ankitbansal6]

Some of them

[@AnandaKrishna-t3h]

Ankit sir added humour silently in this video with q10 😃

[@AhmadUmais]

I was asked 3 questions out of 5 from your list. Thanks alot

[@abb_raj1107]

what if i wanna to swap from row 4 and 5 value , I think 'case' only do for the first occurrences of match value.Thus why, only the first two rows are evaluated with case clause and rest remain the same. Is it true bhai? I am not clear about it. Plz shed some light .

[@gourabnandy826]

Hi Ankit, loved your all videos. Looking forward to see a video on subqueries,

[@ankitbansal6]

Sure 🙂

[@prakharsrivastava6571]

revised today for deloiite interview and suscribed too

[@ankitbansal6]

Cool . all the best.

[@mukuldhote03]

Sir in Q2. it is showing error : 'Target table CTE of the DELETE is not updatable '

[@ankitbansal6]

That works in SQL server only

[@mukuldhote03]

@@ankitbansal6 thankyou for replying. If asked in an interview which query should I answer. This one or some other which works in MySql as I use MySql mostly?

[@ankitbansal6]

@@mukuldhote03 check this out : kzbin.info/www/bejne/aXPLoYR4rKejatUsi=JI6rwhsHXpkqmtjj

[@chaithanyag1669]

Your videos are very good and helpful. Thank you for providing data with create and insert statements.

[@zamm-3527]

Hi @ankitbansal6 if possible can you provide any code for your package course really want to learn but due to inefficient fund can't. Please provide any code if possible

[@ankitbansal6]

Send me an email on [e-mail]

[@balajisundar9867]

Good explanation👏 Please do more intermediate and advanced level of concepts in SQL.

[@nitinnagpalofficial]

Ankit, I love your educational content. Is it possible for you to record the whole PL/SQL course for beginner/Intermediates?

[@ankitbansal6]

Thank you. I will plan in future 🙂

[@aasthamehtatech]

Crisp & insightful

[@dondapatinaveenkumar2920]

Can you please tell me how to delete duplicates in mysql?

[@vishalsonawane.8905]

Done

[@lokeshsharma2852]

Thanks sir for this amazing video

[@ankitbansal6]

Most welcome

[@gobinathv2506]

I am not a expert in SQL, I have a doubt regarding the last question (swapping gender) what else if we use IF condition in that query.

[@ankitbansal6]

If condition not available in SQL. Only pl SQL

[@gobinathv2506]

@@ankitbansal6 update set gender = if(gender = "M" ,"F", "M") whether it work?

[@ankitbansal6]

@@gobinathv2506 No if keyword not present in plain SQL

[@gobinathv2506]

@@ankitbansal6 ok but it working in MySQL. If we have both case and IF statement which one performs efficiently.

[@ankitbansal6]

@@gobinathv2506 case

[@jayrajkakde2813]

Diff between count* and count1

[@ankitbansal6]

Thanks for adding 😊

[@Ks-yi8ky]

Sir pls make latest interview sql quetions

[@kishorekumarrock2475]

Hlo sir is oracle sql is outdated

[@ankitbansal6]

No

[@shubhiverma2571]

please give data of emp table

[@ankitbansal6]

I have pinned the comment with script

[@karthikrful]

Hello Ankit Sir, I am getting this error while trying to delete the duplicate record (The with cte query) "Error Code: 1288. The target table cte of the DELETE is not updatable". Is it because I am using MySQL?

[@manjumohan7731]

+1

[@jaitiwari241]

Sir how I practice..this without dataset

[@RiyaAggarwal-f2e]

Hi, I've got a query here. In the 2nd question, we are deleting the duplicate values from cte, how is it getting reflected in emp1 table?

[@mohakjain1404]

+1 to the question. Also @AnkitBansal, why did we go ahead and use Partiton concept to find and delete duplicate and why couldnt we have used the first logic of count(1) and use it in where clause and delete the entries cause thats what we actually did with rownumber as well. isn't it?

[@lykavalladolid688]

thanks, it actually let me through so i could download it.

[@aadarshchoudhary9648]

Thanks

[@RK-wf7re]

Good content, i have a question why table name used as emp1, it should be emp right?

[@ankitbansal6]

Simply

[@sumanacharya461]

What is the difference between union and union distinct

[@shwetadubey8880]

Just wanted to know can we use left anti join for the 5th question?

[@priyav3249]

Helpful video, Thanks. could you please share the dataset used in the video, to practice please.

[@ankitbansal6]

It's there in the comments section

[@rudrakasha-t1v]

in question number 2 you took select two times ...can you please explain that .

[@gauraavthakurr]

Second select was just to review table data, it's not past of code

[@aashishmalhotra]

Deleting using CTE is not working in sql 8+. any other way around to delete using cte

[@ankitbansal6]

It works only in SQL server

[@sasidharreddy5008]

you don't need the motivation from others because, you yourself is a motivation 🤗

[@ankitbansal6]

🙏