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.

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);

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

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.

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

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.

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);

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

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');

Crisp & insightful

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

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

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

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

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

very simple way to explain...Nice!

Very Useful...I hope part2 will come soon

Thnks for the video. Very helpful 👍

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.

Good one .. !!

Awesome

Thanks for this video.

Hi Ankit, first of all this is really a great initiative that you are sharing all this knowledge and explaining the complex queries in a very easy way. And because of this I am really glad to tell you that your channel has majorly helped me to crack one of the SQL interview. Thank you so much for such a great effort and we all wish to see some more complex queries and concepts in coming future, once again thank you so much. Wish you a great health!

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

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

keep doing more videos like it. It really helped me in my job interview today. I am able to answer the question of how to remove duplicates from a table because of your videos.

Thanks sir for this amazing video

Great video! Loved that last question. Subbed and eager to practice more SQL with your other videos

Very Good explanation!

revised today for deloiite interview and suscribed too

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

Amazing video, tomorrow I have an interview, I was clueless how to start and where to start, now I know what to do for tomorrow :)) Thanks for sharing :)

simple and clear explanation!!

for better reach, keep creating content

yeah it was really important vid as see for the interveiw sceneriao

Thank you

Ankit sir added humour silently in this video with q10 😃

Really Master class videos Ankit, Thanks.

I am watching your one video each morning. So one day I will work as data engineer in good product based company.

You just earned a subscribe from me bro. I haven't used SQL in years, but due to a career change I'm planning to have soon, I'll need to learn more about SQL in-depth alongside bettering my skillset in C++ programming.

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 :) !!

Thanks Ankit its very helpful in interivews wish i had come across your videos early.. thnks much

Leetcode Top 50 Interview Problems - SQL Playlist kzfaq.info/sun/PLsgq4reKtSzyEoZh2_Leks0CxfQb-zfPw

Thank for the video 🙏

Very well explained... 👌

Bro good vide class Bren you have to increase your sound

Thanks ankit, ur videos always encourage and boost confidence to prac more n go beyond our limit. Thanks Man

Great content. Thank you Ankit

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

Yes, you are absolutely right, Many questions are based on your teaching. Thanks again for this great help Ankit!!

Beautiful presentation

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

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

It will be helpful if you can create videos on views and stored procedures

thanks Ankit..good tutorial..

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?

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

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?

Thanks a lot, Learned lots of new concepts.

very helpful ..thanks

Thank you so much. Make vidoes on JOINS.

Big fan sir❤

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

Thanks

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.

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

Thanks for this!

Very helpful sir, thank you!

Million thanks

Just loving your content.. Very well explained.

finished watching

Just landed here bro you are doing amazing job

Superb video..liked every second of the video 💖

You are doing really awesome. Please keep doing this. One suggestion: Please don't forget to add create table script.

I have subscribed and liked . you are doing wonderful job

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

Tqsm for awesome explanation 🔥

Thanks brother, nice explanation

Wow such a nice content. Please zoom a bit while making videos. Because most audience watch it over phone it strain the eyes

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

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!

please make a part 2 of this video Ankit. thank you for your efforts

Great video, I took down some notes!

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

1m likes 👍

Wonderful crisp and short explanation Ankit. I have my interviews lined up can you share resources for SQL interview questions, Data Modelling and Normalization scenario based questions? I know all basics and all theories but I want to practice on questions which have answers as well.

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 😇

Love your valuable contents. I have few question sets from facing different screening. Can I share with you for help with solutions? Your way of explanation helps a lot

Thanks 👍

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?

You just earned a subscriber really great content🥺

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

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

For question 7: SELECT * FROM orders WHERE customer_name LIKE ‘Shilpa’. In this case it will fetch only 1 record Shilpa. As you said, if we apply where UPPER(customer_name) LIKE ‘SHILPA’ then it will fetch all the 3 records. Correct me if I am wrong.

Hi, Ankit. you made them look so easy. Thank you💌. it would be great if you could provide the code of the creation of tables so i can practice it on my workbench.

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

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

Please make a playlist for sql for the beginners which includes everything like joins, Subquery, functions which used in ms sql..

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

Diff between count* and count1