Wow, watched fully and had covered all basic needed things, I recommend everyone to watch if you are looking for a revision of basic SQL functions. Thank you Cherry Academy
4th question ans , -- i find this ans very easy for getting any rank of the salary as we want. select * from( select * , dense_rank() over(order by salary desc) rank from bemployee) s where rank = 2
Great, I have one more query to find Nth sal. SELECT ID, NAME, SALARY FROM EMPLOYEE A WHERE N - 1 = (SELECT COUNT(1) FROM EMPLOYEE B WHERE B.SALARY > A.SALARY) In place of N , we can put values we want to find out , for 3rd highest we can put 3 .. Happy learning :)
Using Row_number to identify the nth highest salary is not ideal because when 2 or more emps having salary will not return correct output. Using Dense rank instead of row number is ideal.
Hi there, Great explanation. I had a query though. The last line of DELETE statement is deleting the record from cte. How does it impact the duplicate record in the actual table (tblEmployee)? I thought CTE only existed within the query.
Hi, Yes you are right, the delete query will delete the duplicates from the CTE only. It is my bad. We can use the below query to delete duplicates from the table using Rank function and also it needs a unique Id column, here I am using EmpId to achieve this. DELETE E FROM tblEmployee E INNER JOIN ( SELECT *, RANK() OVER(PARTITION BY EmpName,Salary ORDER BY EmpId) rank FROM tblEmployee ) T ON E.EmpId = t.EmpId WHERE rank > 1;
@@cherryacademy7692 Hi there, I have a doubt that needs a clarification. will the above query using rank and rank>1 actually delete the duplicate? How about the below one DELETE T FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpName,Salary ORDER BY EmpId) as [RN] FROM tblEmployee ) AS t WHERE RN > 1 Thanks a lot
even if you delete from the CTE table, it will delete the records from the original table only. I have tried it and it is working. with cte as ( select EmployeeId, ROW_NUMBER() over(partition by employeeid order by employeeid) as rno from emp_duplicate ) delete from cte where rno>1
Hi, Thanks for this video!! I have a question - what if we have a table of more than 100 columns and if we want to find duplicate records, like here in this case we assumed we wont find more than one record with same empname and salary and that's why we selected those columns in group by, but in the case of 100 columns , how to select columns to put into group by?
Hello Priyanka, not just for you, generally, most of the people including me fail to write queries on the spot. It doesn't mean you are not good. Interviews are spontaneous and mostly theory-based. To improve: Whenever you attend an interview, note down the questions/queries that are asked, prepare answers and practice them. Over a period of time, you will become familiar with the queries that are asked. If you need further support, please connect with me in linkedin www.linkedin.com/in/james-fredric-631139141/
Hey hai Column names " empid,empname,empsalary,department," Who draw the hight salary based on the department, department are ( it,hr, manager,admin) based on this I want two maximum salaries for each department. how could u write the queary plase let me now.
Wow, watched fully and had covered all basic needed things, I recommend everyone to watch if you are looking for a revision of basic SQL functions. Thank you Cherry Academy
super finally i got the good video on sql interview questions
need more video like this
Watch full videos here....
Excellent work u done on Queries thank you bro
4th question ans ,
-- i find this ans very easy for getting any rank of the salary as we want.
select * from(
select * , dense_rank() over(order by salary desc) rank from bemployee) s
where rank = 2
Great, I have one more query to find Nth sal.
SELECT ID, NAME, SALARY FROM EMPLOYEE A WHERE N - 1 = (SELECT COUNT(1) FROM EMPLOYEE B WHERE B.SALARY > A.SALARY)
In place of N , we can put values we want to find out , for 3rd highest we can put 3 .. Happy learning :)
So informative and explained precisely. Need more videos on Pl Sql tricky questions
Well done! Crystal clear explanation thank you sir!
Nice video
it helps a lot to understand basics of SQL queries
Thank you @cherry academy
Thank you...nice and simple explanation
Excellent presentation. appreciate your efforts.
plz make more vedios on sql questions
Watch full interview questions on Sql here....
Thanks
Kudos to you.. You provided the links in description.. Thanks.
Thank you, all of these are what I expected! No need to watch again, but this is good.
Good sir 👍thank you
very helpful video. Thank you
Nice video.. Can you please make more videos ON SQL Interview based questions
Excellent explanation 👌👌 we need more videos 👍
Watch full interview questions here.....
Using Row_number to identify the nth highest salary is not ideal because when 2 or more emps having salary will not return correct output. Using Dense rank instead of row number is ideal.
Hi there,
Great explanation. I had a query though. The last line of DELETE statement is deleting the record from cte. How does it impact the duplicate record in the actual table (tblEmployee)?
I thought CTE only existed within the query.
Hi, Yes you are right, the delete query will delete the duplicates from the CTE only. It is my bad. We can use the below query to delete duplicates from the table using Rank function and also it needs a unique Id column, here I am using EmpId to achieve this.
DELETE E
FROM tblEmployee E
INNER JOIN
(
SELECT *,
RANK() OVER(PARTITION BY EmpName,Salary
ORDER BY EmpId) rank
FROM tblEmployee
) T ON E.EmpId = t.EmpId
WHERE rank > 1;
@@cherryacademy7692 Hi there, I have a doubt that needs a clarification. will the above query using rank and rank>1 actually delete the duplicate? How about the below one
DELETE T FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpName,Salary ORDER BY EmpId) as [RN] FROM tblEmployee
) AS t WHERE RN > 1
Thanks a lot
@@cherryacademy7692 Thank you!
@@cherryacademy7692 query looks incorrect
even if you delete from the CTE table, it will delete the records from the original table only. I have tried it and it is working.
with cte as
(
select EmployeeId, ROW_NUMBER() over(partition by employeeid order by employeeid) as rno
from emp_duplicate
)
delete from cte where rno>1
Quick Review for Sql stuffs. Thanks. Would you post some videos of c#
Hi ..please upload more videos regarding SQL and c#..this video is good..
I have a question on
2)
We are deleting from 'CTE' temp table but how it is reflecting emp table?
nothing is going to reflect
This video is very helpful to me. Can you upload about user define functions
Wow, a great video with quality question. Looking forward to more such advanced level questions ❤️
You have really done well. sums up almost everything that can come in interview. I really do appreciate your effort. :-)
Very nice query & presentation
Simple awesome
Hi, Thanks for this video!! I have a question - what if we have a table of more than 100 columns and if we want to find duplicate records, like here in this case we assumed we wont find more than one record with same empname and salary and that's why we selected those columns in group by, but in the case of 100 columns , how to select columns to put into group by?
Nice Video
Thank, that was helpful!! I was looking for this: find 3rd largest salary from each dept, could you try that...
very usefull bro expecting more videos like this bro
Nice bro all queries 👍
queries provided in the video are great, really helpful, thank you
Glad to know
So helpful
The background music is distracting. But the video is good. In your next video if you remove the music could be great
sure, noted, thank you for the feedback
Great explanations and it will help everyone, thank you so much for your effort!!
We are happy to hear from you. Thank you for the valuable feedback 🙏
True Legend
Hi, Could you please make a video related to tricky date functions and trigger functions for SQL
Thank you
good job bro
how do you use top while selecting top n salaries. it is throwing an error that top cannot be used anymore.
Ms sql course i possibly to add
where exactly is the data and how to upload it? in the link given there are only pdfs and some instructions, help me
This video was very helpful Thankyou !!
Hi, I can't crack sql queries questions in interview. What to do?how to do practice?
Hello Priyanka, not just for you, generally, most of the people including me fail to write queries on the spot. It doesn't mean you are not good. Interviews are spontaneous and mostly theory-based.
To improve:
Whenever you attend an interview, note down the questions/queries that are asked, prepare answers and practice them. Over a period of time, you will become familiar with the queries that are asked.
If you need further support, please connect with me in linkedin
www.linkedin.com/in/james-fredric-631139141/
Thanks bro for sharing your knowledge. Definitely it will help a lot to most of us. :-)
Where are that tables to create and insert ...is there source code for this
Hey hai
Column names
" empid,empname,empsalary,department,"
Who draw the hight salary based on the department, department are ( it,hr, manager,admin) based on this I want two maximum salaries for each department. how could u write the queary plase let me now.
From mobile phone we are not able see the query bcz font is very small
the video is good , but the background sound is worst
Which JOIN is used in the last question? Is it SELF JOIN?
No we can use self join also
The departmen having less than 3 emp? => that was inner join
Easy questions explained with tough answers❌❌
Thank you for the feedback, we will put more efforts to improve in our subsequent videos.
ye background music is so much disturbing and annoying
worest explanation for nth and top not worh to watch
Thank you for the feedback brother, we will try to improve in the upcoming videos