You have posted this question 9 yrs before but i attended interview on 29 June 2023 where this question is asked. But i saw it now the next day..other wise i would have answered 😢
I have 10,000 records of hierarchy data and if i go by this method it would have a serious impact on performance . So Sql has introduced a new datatype named hierarcyid which would give a better performance . Can you please post a video with above as example .
The way you break the problem into pieces and then one by one solve them....its really great...really impressed by your work..... keep it up. god bless you :D
Good tutorials... We can achieve the hierarchy of the organization with simple query also... Example here is select A.EMPLOYEEID,A.ENAME,A.DOB,A.GENDER,A.DESIGNATION, A.SALARY, Z.ENAME AS MANAGER FROM employees a inner join employees z on a.managerid = z.employeeid go select A.EMPLOYEEID,A.ENAME,A.DOB,A.GENDER,A.DESIGNATION, A.SALARY, ISNULL(Z.ENAME, 'NO BOSS') AS MANAGER FROM employees a left join employees z on a.managerid = z.employeeid
Hi @kudvenkat, If there is Union All, for every level up, David Record has to be appended once correct? There must be some duplicates atleast? But your output doesn't have any duplicates. Can you let me if I'm thinking correct, will there be any duplicates at all?
Hello Venkat, i am a big fan of you. You really are a great person indeed. I just pray may ALLAH the almighty shower HIS Countless blessings upon you. Aameen
+Kamlesh Nakade Thank you for the feedback. This means a lot. Very pleased you found these videos useful. Dot Net & SQL Server training videos to help you become a web developer ru-vid.complaylists?view=1&sort=dd Download videos for offline viewing www.pragimtech.com/Order.aspx Slides, Code Samples & Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use our youtube channel ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-y780MwhY70s.html To receive alerts, when new videos are uploaded, please subscribe to our channel ru-vid.com If you like these videos, please click on the THUMBS UP button below the video May I request you to please share the link with your friends who you think would also benefit from them To teach is to learn Thanks Venkat
Sounds like you created an infinite loop, unless if the query you are running is different from this tutorial. If this is a different query, then you can solve this issue by using OPTION (MAXRECURSION 0)
Sir, can you kindly explain how the second query after the UNION ALL within the EmployeeCTE works because I am finding the recursive CTE difficult to understand even after watching the Part 51 Recursive CTE
Sure, I think it would be easy to explain this in a video rather in text. Will try to record and upload a video in a few days. Thank you for your patience. Good Luck.
For MySQL with recursive cte (EmployeeID, EmployeeName, ManagerID) as ( select EmployeeID, EmployeeName, ManagerID from employeestemp where EmployeeID = 5 union all select p.EmployeeID, p.EmployeeName, p.ManagerID from employeestemp p inner join cte on p.ManagerID = cte.EmployeeID ) Select E1.EmployeeName, IFNULL(E2.EmployeeName, 'No Boss') as ManagerName From cte E1 LEFT Join cte E2 ON E1.ManagerID = E2.EmployeeId;
This is a great case! I went through self-join, CTE, and recursive CTE videos, and last but not least this interview case. Very comprehensive understanding of these concepts now. Thank you!
Hi Venkat, below query is not working in Declare @ID int ; Set @ID = 7; WITH EmployeeCTE AS ( Select e.EmployeeId, e.EmployeeName, e.ManagerID From Employees e Where EmployeeId = @ID UNION ALL Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID From Employees JOIN EmployeeCTE ON Employees.EmployeeId = EmployeeCTE.ManagerID ) Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName From EmployeeCTE E1 inner Join EmployeeCTE E2 ON E1.ManagerID = E2.EmployeeId In MSSQL 2012
@kudvenkat Sir, why don't you provide carrer advice us as a successful person. I mean how to make plan to become successful developer in .net from fresher level.
Hi venkat sir recently I attended interview and there interviewer asked me SQL query as there are two tables item and item Rate item table consist of item id Item Name 1. Parker 2. Marie 3. Good Day 4. Monaco item Rate consist of ItemId ItemRate 1. 5 3. 20 and Query was. select I.iemName from item I left join ItemRate R on I.itemId!=R.itemId where R.itemRate>5 what is the output of above query please explain
Hi Hiraji Jadhav, I try to give the answer. It is an actually inner joins with a condition . Answer is "Marie, Good Day, Monaco" item names. Please correct me, if i am wrong. Regards,
The output will be:- Parker Marie Monaco Answer Explanation:- These two tables has left join and not equal condition so the below query gives the 6 results on not equal condition select I.itemName from item I left join ItemRate R on I.itemId!=R.itemId output:- ItemName ICode RCode Rate Parker 1 3 20 Marie 2 1 5 Marie 2 3 20 Good Day 3 1 5 Monaco 4 1 5 Monaco 4 3 20 And in the last query has where clause rate>5 so the final query output will be Parker Marie Monaco Tx, -Deepak Vashishtha
OK i got it. When the query before union runs already the CTE has one row. Then after union query, it starts processing from that available first row. Am i correct? Thanks.
As a fresher for interview is it important to know about sql server . As i have college placement for sql developer? how to preprare for interview and which portion
Hello Sir, I have a doubt, This result can be get by following query select a.Employeename,isnull(b.employeename,'No boss') as manager from Employees a left join Employees b on a.ManagerID = b.EmployeeId then why we use CTE or recursive query?
UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will ignore the duplicate records whereas UNION ALL will include duplicate records. It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.
Hi, I faced the similar kind of question in an interview but the requirement was little different. If you pass the id as 7 which is David's id you should get the output of your last manager which is ben's record only. How we can do that?
sir, One Interview qustion i Faced ......find third highest number in arrays without using sorting..... and values will be give at run time...values must be distinct..no duplicate values... this is one of interview question
I was asked below question today in #Microsoft technical interview. There are 5 micro services writing telemetry to Log Table Log schema includes the following. a. SourceID b. Message Description c. Severity d. Timestamp. Logs written to Log Table are asynchronous. configure an alert and notification to be sent when more than 5 severity 3 logs are written in a 5 mins window. Basically the question is, result set should contain a alert record of a particular sourceID whose severity is 3 and occurred more than 5 times and also the occurances with in 5 min window. Can anyone help me with the approach and query for this question?
Great stuff but please stop saying "Superboss". I have never in my life heard anyone use that word and it gets repeated endlessly in these videos. How about "CEO" or "Owner" or "Department Head".
