REAL SQL Interview Problem | Hierarchical data in SQL
Вставка
- Опубліковано 29 вер 2024
- In this video let us solve a real SQL interview problem. This problem is to find the hierarchy of employees.
We shall first understand the problem statement and the given data before solving the problem.
During the video, we shall also see how to debug a recursive SQL query. We shall try to come up with the different queries SQL generates during each iteration of the recursive SQL query. This will hopefully give you a complete understanding of how recursive SQL query works.
🔴 My Recommended courses 👇
✅ Learn complete SQL: learnsql.com/?...
✅ Practice SQL Queries: www.stratascra...
✅ Learn Python: codebasics.io/...
✅ Learn Power BI: codebasics.io/...
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq
![[UA] NAVI проти G2 Esports | Blast Premier Fall Final](http://i.ytimg.com/vi/QWlIm4FGESQ/mqdefault.jpg)
Hi, I completed my be 2013, ME 2015. Then I prepared government exams but not selected. Can I prepared these courses. I'm eligible for IT jobs
Great
-- Hierachy data SQl
create table hierData (
emp_id integer,
reporting_id integer
);
insert into hierData values
(1,null),
(2,1),
(3,1),
(4,2),
(5,2),
(6,3),
(7,3),
(8,4),
(9,4)
;
select * from hierData;
with recursive cte as(
select emp_id,emp_id as emp_hierarchy
from hierData
union all
select cte.emp_id, eh.emp_id as emp_hierarchy from cte JOIN hierData eh
on cte.emp_hierarchy = eh.reporting_id
)
select * from cte
order by emp_id;
Thank you sir. You are the only hope I have right now
How smoothly you clear the concept of recursive cte....great sir🙌🙌🔥🔥
Nicely explained...! Understood the concept of recursive in SQL easily..! Thank you so much.
Super glad to hear that ☺️
Thanks again Thoufiq for this wonderful explanation of recursive queries. Every video in your channel has something new to learn. Thanks a lot for sharing the knowledge.
Hi Thoufiq,
Thanks for considering my question.
This question was asked to me in one of my interviews.
That time I didn't answer properly.
Now I got complete clarity on that recursive part of the query.
Once again thanks allot!!!!
Thanks Halesha for sharing it with me ..
Much appreciated 🙏🏼
create table employee_hierarhy
(
emp_id int,
reporting_id int
)
insert into employee_hierarhy (emp_id,reporting_id)
select 1,null
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 5,2
union all
select 6,3
union all
select 7,3
union all
select 8,4
union all
select 9,4
Hello sir, very useful concept. Kindly include with exit condition which is missing. Thank you.
Thanks you sir thank you very much it is very useful to us
Hi, same query in have written but get missing keywords in sql developer
Request:
Lateral joins & postgres functions.
Among the best tutorials are in this channel so thank you.
Noted , will do soon
I’m your fan! Love how you explained your thought process and how to approach the problem, you are not just showing the solution but empowering others to analyze the problem and equipping them to think the solution themselves! I don’t think anyone has ever done this as effectively as you do 🙌 🙏 your passion for your work shows through, and it’s an inspiration to watch you! We are lucky to have you here!
Thank you, Pavana 🙏
It's such an amazing feedback. loved reading it (even though I read it 2 months late 😬)
Really very glad you liked the video and my approach :)
Great content! Thank you! Please do a video on sql tuning, explain plans and how to approach tuning queries for better performance! Any ideas on these are much appreciated!
Perfect explanation. Thanks Thoufiq!!
Excellent video sir💯
Thankyou so much for the consistent amazing content 🙏
Superb! Explanation.Each and every minute is worth watching.
Why aliasing emp_hierarchy same in both same in base query and recursive query.
Hi Thoufiq! Your videos and playlists on SQL have been very helpful. I went through many of your videos before appearing for an interview this week and it came in very handy. Thanks for these videos. I was asked a question in interview today with a service based startup that a table is given called Trips: Trip_id ,rider_id, city_id, trip_timestamp (actual data was not given). Question was Find users who have taken trips 5 consecutive days. I got an initial approach to use lag and then datediff to find difference between continuous trips but could not reach the final answer. Can you please help? Thanks in advance.
I tried running query in snowflake .However its giving me wrong result .
with cte as(
select empid, empid manager from emp where empid=1
union all
select e.empid,c.empid manager from cte c join emp e on e.manager =c.manager
)
select * from cte ;
Sir may I know when is your next training session?
Super useful and we'll explained video! Thank you
Very complicated but nice explanation you need to watch 2 times minimum
What if employee id is not increasing order, will the output be same as I am getting output with few employee id missing 😮. Could u help"¿
Try retrieving a tree in forest like what if there are multiple hierarchies? for example a table with data about countries and their president, prime minister and ministers. Here every country will have its own Hierarchy and hence more than one base cases.
Thank you Thoufiq... You are superb 🙏
Thank you so much for sharing 🙂
Thanks for watching!
Learnt something new today🎉
👍
I tried same way however I don’t get the all hierarchy.. please help
Make video for print type question in SQL
Step by step nice explanation 💗
can we solve this problem with the help of self join ?
I am beginning to understand recursive cte
Which SQL editor are you using?
Postgre SQL
Hello Sir,
input string: hello, how are you? output characters: helo,wareyu? this is my question. please ans. me this qu.
Sir, Plz give the solution
Great explanation
Great explanation mate!
great job Toiq bhai.
Superb explanation Sir,Clean and Crystal clear explanation.Thank you very much Sir.
👏
What an explanation!!, i like your videos, but this is the best . Your teaching skill really amazing...Thank you
Very useful 👍
So I'm learning SQL now, and i would like to believe I know more than just the basic. I believe I'm between basic and intermediate. i practice writing queries almost everyday.
Does anyone know what job/s I can get with what I know?
Please be realistic.
Thankyou
Can You make a video for sending email to specific recipient with summary attached on mail body use SQL Procedure. Please help me with this.
Sir.....Can you please suggest that which SQL course we should opt for beginner to advance level learning from Learn.sql . there are showing too many different courses
Hi Taufic, Please help me in hoe to get Age in the of 24 Years, 8 months, 10 days from Date of birth in sql. This question is asked in my previous interview. I have i verified all your videos for this type of scenario
Thank you Sir. Excellent explanation on queries.
Excellent explanation on queries. This has helped a lot in my prob leg still having in SQL query
if any company gave me this kind questions, I'll assume they don't like me
I don't think anyone else could do better in explaining this. crystal clear✅
Can u please provide more problem query, so that we can practice more recursive query.
Thank You sir
good question good explanation good approach
only bad thing is the noob me
explanation is very next level Thank You Sir
Thanks again for the wonderful explanation.
Hi can u tell what is the shortcut key to display data of table in sql
Hi
What an explanation! It was flawless!
debugging iteration of recursive is amazing.
Nice
👏👏👏
hey @techTFQ how to solve this if we dont want to include the employee himself in the hierarchy list
(ex: if we dont want to include in 1 in 1 itself & 5,6,7,8,9)\
with recursive cte as (
select eh2.emp_id, eh1.emp_id as emp_hierarchy from
Employee_Hierarchy as eh1 join Employee_Hierarchy as eh2 on eh2.emp_id =eh1.reporting_id
union
select cte.emp_id,eh.emp_id as emp_hierarchy
from cte join Employee_Hierarchy eh on cte.emp_hierarchy = eh.reporting_id
)select * from cte order by 1,2;
I am new subscriber
Thank u soooooooo much sir
very well explained
best tutorial about Hierarchical data in SQL so far.
Glad it helped
Great video
Thank you !
Amazing
Hello sir plz help me to choose career. I hv done BE n mtech in CS. I have teaching experience around 7+ year. I am on break from last 4 yr for my kids. Now I want to restart my career as a devloper . I have strong knowledge of core Java n SQL queries. Which type of job profile best suited me?
I can't stop being amazed the way you teach complex queries in a simple way. And your channel is really a boon for person like me who wants to be skilled in SQL. Thank you so much for your examples and explanation.
May I know what workbench you use?