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 :)
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
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, 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?
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.
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/
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
Excellent work u done on Queries thank you bro
Well done! Crystal clear explanation thank you sir!
So informative and explained precisely. Need more videos on Pl Sql tricky questions
Nice video
it helps a lot to understand basics of SQL queries
Thank you @cherry academy
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.
Thank you...nice and simple explanation
need more video like this
Watch full videos here....
Excellent presentation. appreciate your efforts.
Excellent explanation 👌👌 we need more videos 👍
Watch full interview questions here.....
Wow, a great video with quality question. Looking forward to more such advanced level questions ❤️
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
Good sir 👍thank you
very helpful video. Thank you
You have really done well. sums up almost everything that can come in interview. I really do appreciate your effort. :-)
Thanks
Very nice query & presentation
plz make more vedios on sql questions
Watch full interview questions on Sql here....
Nice video.. Can you please make more videos ON SQL Interview based questions
Hi ..please upload more videos regarding SQL and c#..this video is good..
queries provided in the video are great, really helpful, thank you
Glad to know
Nice bro all queries 👍
Quick Review for Sql stuffs. Thanks. Would you post some videos of c#
True Legend
This video is very helpful to me. Can you upload about user define functions
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 :)
very usefull bro expecting more videos like this bro
Nice Video
Simple awesome
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
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 🙏
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
So helpful
Thank you
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.
good job bro
how do you use top while selecting top n salaries. it is throwing an error that top cannot be used anymore.
Thank, that was helpful!! I was looking for this: find 3rd largest salary from each dept, could you try that...
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?
Thanks bro for sharing your knowledge. Definitely it will help a lot to most of us. :-)
Hi, Could you please make a video related to tricky date functions and trigger functions for SQL
This video was very helpful Thankyou !!
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
Where are that tables to create and insert ...is there source code for this
From mobile phone we are not able see the query bcz font is very small
where exactly is the data and how to upload it? in the link given there are only pdfs and some instructions, help me
Ms sql course i possibly to add
the video is good , but the background sound is worst
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.
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/
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