REAL SQL Interview Problem | Hierarchical data in SQL
HTML-код
- Опубликовано: 13 мар 2023
- 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/?ref=thoufiqmoha...
✅ Practice SQL Queries: www.stratascratch.com/?via=te...
✅ Learn Python: codebasics.io/courses/python-...
✅ Learn Power BI: codebasics.io/courses/power-b...
🔴 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
How smoothly you clear the concept of recursive cte....great sir🙌🙌🔥🔥
Thank you sir. You are the only hope I have right now
Superb! Explanation.Each and every minute is worth watching.
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.
Perfect explanation. Thanks Thoufiq!!
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 :)
Excellent video sir💯
Thankyou so much for the consistent amazing content 🙏
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 🙏🏼
Nicely explained...! Understood the concept of recursive in SQL easily..! Thank you so much.
Super glad to hear that ☺️
I don't think anyone else could do better in explaining this. crystal clear✅
debugging iteration of recursive is amazing.
great job Toiq bhai.
Excellent explanation on queries. This has helped a lot in my prob leg still having in SQL query
Step by step nice explanation 💗
Thanks again for the wonderful explanation.
What an explanation! It was flawless!
Great explanation mate!
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
Thank you Sir. Excellent explanation on queries.
Super useful and we'll explained video! Thank you
Learnt something new today🎉
Very complicated but nice explanation you need to watch 2 times minimum
explanation is very next level Thank You Sir
Great explanation
Thanks you sir thank you very much it is very useful to us
Very useful 👍
Hello sir, very useful concept. Kindly include with exit condition which is missing. Thank you.
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!
Request:
Lateral joins & postgres functions.
Among the best tutorials are in this channel so thank you.
Noted , will do soon
best tutorial about Hierarchical data in SQL so far.
Glad it helped
Thank you !
Great video
Thank you so much for sharing 🙂
Thanks for watching!
very well explained
Thank You sir
Thank u soooooooo much sir
Amazing
-- 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;
Great
Nice
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.
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
I am new subscriber
👏👏👏
👍
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.
good question good explanation good approach
only bad thing is the noob me
Hi can u tell what is the shortcut key to display data of table in sql
May I know what workbench you use?
I am beginning to understand recursive cte
Sir may I know when is your next training session?
Why aliasing emp_hierarchy same in both same in base query and recursive query.
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.....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
Make video for print type question in SQL
Which SQL editor are you using?
Postgre SQL
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.
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"¿
Hi
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;
Can u please provide more problem query, so that we can practice more recursive query.
I tried same way however I don’t get the all hierarchy.. please help
can we solve this problem with the help of self join ?
if any company gave me this kind questions, I'll assume they don't like me
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
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.
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
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
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?