For the second one in oracle this query also does the trick in one single query SELECT NVL(A.ID,B.ID) AS ID, CASE WHEN B.NAME IS NULL THEN 'New in Source' WHEN A.NAME IS NULL THEN 'New in Target' ELSE 'Mismatch' END as Comment1 FROM source A FULL JOIN target B ON A.ID = B.ID WHERE NVL(A.NAME,'$') NVL(B.NAME,'$'); I used full join and excluded the names that matched then is just added the Case. Great Video!
I am really thankful to you .. few months ago I was not so confident on myself to give interviews.. after started learning from your videos given around 4-5 interviews confidently. Thank you😊..
He is a savior. I am lucky that I came across his videos. Such a lucid explanation. Thank You. Please upload more of SQL Queries needed for Data Science Folks !!
Logic is correct... In second query you have used union operators and same table is scanned 3 times which may not be suitable for big tables... We can use cte for the same
I could come up with this - select comment from (select * from comments_and_translations where translation is null) x union select translation from (select * from comments_and_translations where translation is not null) x
I just got to know He is from Malesiya , such really amazing contents not finding anywhere in youtube , please keep posting more videos like this 🙏🏻🙏🏻, really feeling like big brother is helping to understand such complex things in a simpler way 😇, Love from India 🥰
The way you explained and the pain you have taken to make such wonderful videos is way beyond saying thanks...you are a rockstar man....keep posting such kind of tricky queries...will help someone get a job...all the best bro...
Sir, can you explain about what exactly a sql/ plsql developer do in software industry and what are the career growth options in future for sql developers in IT industry. Is it better to go through in that domain?
Thanks a lot TFQ. Your videos is not just a video it is a service for many techs like me. Beautiful explanation that too for tricky questions. More than that the SQL commands to execute and practice in our IDE's that you share get me more to my fullest satisfaction. A special thanks for that too buddy. Please keep up the good work.
You are simply amazing, Thoufiq! I took your SQL Bootcamp last year and often come back to your content for additional practice and learning. Stay blessed!
Kindly come up with real life Day to day activity a SQL Developer or a data analyst does in the industry or the company. Like please explain when we join any company how are these Query useful there with some kind of live examples. For instance how a data analyst uses SQL in his day to day work at the office. Also please continue providing such videos and please come up with full tutorial videos in a sincere format like a course
Hey Taufiq, Could you please make a video on indexing .You explain very well. Want to understand SQL indexing thoroughly but couldn't find any video on your channel. If you already have made video on it, could you please share the link here?
query 2: without joins select id , case when id = 3 then 'new in source' else 'mismatch' end as output from source where id in(3,4) union select id ,'new in target' as output from target where id in (5) OR select id , decode (id ,3,'new in source',4,'mismatch') as output from source where id in(3,4) union select id ,'new in target' as output from target where id in (5);
Hi sir, we can write it like this select id , case when id=3 then "New in source" When id =4 then "New in target" When id =5 then "mismatch" End as comment From source Full outer join target Where source.nametarget.name
Thank you sir, for making such informative videos. it's really helping me practice for my upcoming interviews. One request is to please make videos on puzzles and guesstimates for full practice for the role of data analyst.
Thanks a lot TFQ for giving such wonderful explanation for all these questions. I started watching your YT channel a month back your teaching style and your logical explanation for each concepts MINDBLOWING. Thank you again for sharing your knowledge.
Hi, Your videos are very unique and easy to learn Can you please make videos related to Grouping sets, Rollup, Within group. And also please make related to dynamic sql Thanks for your time.
Alternate solution for problem 2 in postgres-- It's essentially 3 steps: 1 Use a FULL OUTER JOIN on id to combine all the records 2. Use WHERE to pick out the rows where a) Source has a null id, b) Target has a null id, or c) Source name differs from Target name. 3. Use two CASE statements in SELECT to pick out the non null data and hard code in our comments.
Guyz watch everyday Data science channel where he already solved so many leetcode interview questions.. Probable qsn for interview.. Do watch tfq.. Of you stuck somewhere get help there.
hello all , here is my query with easy understanding for the last IPL match query . For each team play with every other team twice , here we can use cross join ... select IPL.team_name , O.team_name from IPL ,IPL as O Where IPL . teamcode O. teamcode I never imagined that i can also write a query with understanding SQL thank you techTFQ
Solving question 2 you should perform a full outer join, select rows that have null values in either source or target name or names that mismatch. and use an ifelse function to name the occurences in the variable Comment. Here is the code (with data) using data.table package in R: library(data.table) x1=c(1,2,3,4) x2=c("a","b","c","d") y1=c(1,2,4,5) y2=c("a","b","x","f") X=data.table(id=x1,name=x2) Y=data.table(id=y1,name=y2) merge(X,Y,by="id",all=TRUE)[name.x!=name.y | is.na(name.x) | is.na(name.y),.(id=id,Comment=ifelse(is.na(name.y), "New in Source (X)", ifelse(is.na(name.x),"New in Targe (Y)","Mismatch")))]
For 2 Q - select case when id1 is not null and id2 is null then id1 when id2 is not null and id1 is null then id2 when id1=id2 then id1 end as ID, case when name1 is not null and name2 is null then 'New in Source' when name1 is null and name2 is not null then 'New in Target' when id1=id2 and name2name1 then 'Mismatch' end as Comment from (select s.id as id1,s.name as name1,t.id as id2,t.name as name2 from source s full outer join target t on s.id=t.id where s.id is null or t.id is null or (s.id=t.id and s.namet.name))x
Thank you so much for the thoughtful explanation. 😊 One thing came into mind, 2nd query with union of 3, instead, can we use full join with case statement to print the desire output?
My version of problem 2: select ID ,Case when ID = 3 then 'New in source' when ID = 4 then 'Mismatch' when ID = 5 then 'New in target' End as Comment from source where ID in (3,4,5) union select ID ,Case when ID = 3 then 'New in source' when ID = 4 then 'Mismatch' when ID = 5 then 'New in target' End as Comment from target where ID in (3,4,5)
For the last we can use cross join select t1.team_name as team, t2.team_name as opponent from teams t1 cross join teams t2 where t1.team_name t2.team_name This will give the result for each team playing against each other twice.
For the second one in oracle this query also does the trick in one single query
SELECT NVL(A.ID,B.ID) AS ID,
CASE WHEN B.NAME IS NULL THEN 'New in Source'
WHEN A.NAME IS NULL THEN 'New in Target'
ELSE 'Mismatch'
END as Comment1
FROM source A
FULL JOIN target B ON A.ID = B.ID
WHERE NVL(A.NAME,'$') NVL(B.NAME,'$');
I used full join and excluded the names that matched then is just added the Case.
Great Video!
அருமையான எஸ் பி எல் வினாவல் அருமையான எஸ் பி எல் லாங்குவேஜ் சொல்லி கொடுத்ததற்கு நன்றி
I am really thankful to you .. few months ago I was not so confident on myself to give interviews.. after started learning from your videos given around 4-5 interviews confidently. Thank you😊..
Miss likshma how to apply interview
Plz tellme
I wanna ask too
Please add this video to the SQL playlist! Started watching the joins video and I have never seen a better explanation before. Love the content!
He is a savior. I am lucky that I came across his videos. Such a lucid explanation. Thank You. Please upload more of SQL Queries needed for Data Science Folks !!
Glad you liked my contents :) thank you
@@techTFQ My Pleasure, its for my benefit
I wouldn't ever hire the one who solves the second task like you did.
This is the clear task for the FULL JOIN
Logic is correct... In second query you have used union operators and same table is scanned 3 times which may not be suitable for big tables... We can use cte for the same
That last example was lit! I was having difficulty understanding recursive CTEs before, but now I clearly get it.
I could come up with this -
select comment from (select * from comments_and_translations
where translation is null) x
union
select translation from (select * from comments_and_translations
where translation is not null) x
I just got to know He is from Malesiya , such really amazing contents not finding anywhere in youtube , please keep posting more videos like this 🙏🏻🙏🏻, really feeling like big brother is helping to understand such complex things in a simpler way 😇, Love from India 🥰
The best channel to learn sql topics as well as solve questions.
The way you explained and the pain you have taken to make such wonderful videos is way beyond saying thanks...you are a rockstar man....keep posting such kind of tricky queries...will help someone get a job...all the best bro...
Thanks a lot bro
Sir, can you explain about what exactly a sql/ plsql developer do in software industry and what are the career growth options in future for sql developers in IT industry. Is it better to go through in that domain?
You are really explaining the complex queries which will be very helpful for professionals as well
Glad you liked it
Thanks a lot TFQ. Your videos is not just a video it is a service for many techs like me. Beautiful explanation that too for tricky questions. More than that the SQL commands to execute and practice in our IDE's that you share get me more to my fullest satisfaction. A special thanks for that too buddy.
Please keep up the good work.
You are simply amazing, Thoufiq! I took your SQL Bootcamp last year and often come back to your content for additional practice and learning. Stay blessed!
Thanks TFQ the last question was everything, I was blocked on how to remove mirror effect records from my query.
hi taufiq, the approach for 3rd problem was out of the world. thanks for such a mind-blowing approach.
Thank you Sumit :)
Kindly come up with real life Day to day activity a SQL Developer or a data analyst does in the industry or the company. Like please explain when we join any company how are these Query useful there with some kind of live examples. For instance how a data analyst uses SQL in his day to day work at the office. Also please continue providing such videos and please come up with full tutorial videos in a sincere format like a course
Noted on the request bro, let me think about it
Hey Taufiq, Could you please make a video on indexing .You explain very well. Want to understand SQL indexing thoroughly but couldn't find any video on your channel. If you already have made video on it, could you please share the link here?
query 2: without joins
select id , case when id = 3 then 'new in source' else 'mismatch' end as output from source where id in(3,4)
union
select id ,'new in target' as output from target where id in (5) OR
select id , decode (id ,3,'new in source',4,'mismatch') as output from source where id in(3,4)
union
select id ,'new in target' as output from target where id in (5);
Hi sir, we can write it like this select id , case when id=3 then "New in source"
When id =4 then "New in target"
When id =5 then "mismatch"
End as comment
From source
Full outer join target
Where source.nametarget.name
Yes right.. I have provided solution using full outer join in my blog
For question 1 I would use
Select isnull(translation, comment) from table
Me too, much easier…
@@jameses4413 yes
is it isnull or ifnull
@@atwineian375 isnull .it will return the non null value.
Great explanation. Looks like for the third query you had already solved the second part first using not equal on team names.
I did but I wanted to provide a more meaningful and easier to understand solution at the end hence used row number concept
Nice question and answers
However the third question can be solved without rownumber()
as the < can be directly applied on team code
Not exactly, it depends on the lexicographical order if you want do it that way.
Thank you sir, for making such informative videos. it's really helping me practice for my upcoming interviews. One request is to please make videos on puzzles and guesstimates for full practice for the role of data analyst.
God bless you. Taking your precious time and helping others.
you are best educator ever for sql 🙏
Really you are born to teach .just loved your way of explaining Thank you so much
there's many way to do the second query.. it's pretty easy.. the third one now that is challenging!
Thanks a lot TFQ for giving such wonderful explanation for all these questions.
I started watching your YT channel a month back your teaching style and your logical explanation for each concepts MINDBLOWING.
Thank you again for sharing your knowledge.
Thank you for the kind words bro 🙏🏼🙏🏼
Thanks for your videos, very helpful for me when I started to find a job. Thanks a lot
Hi, Your videos are very unique and easy to learn
Can you please make videos related to Grouping sets, Rollup, Within group.
And also please make related to dynamic sql
Thanks for your time.
Thank you bro and noted on the request
the way you explained is next level Sir Thank You so much
great your way of explaining problem and solving them adds fun for them also who thinks sql very tough .great job sir.
Alternate solution for problem 2 in postgres--
It's essentially 3 steps:
1 Use a FULL OUTER JOIN on id to combine all the records
2. Use WHERE to pick out the rows where a) Source has a null id, b) Target has a null id, or c) Source name differs from Target name.
3. Use two CASE statements in SELECT to pick out the non null data and hard code in our comments.
The conditions can directly be used in the case statement I think.
Then Order By DESC COMMENTS
Nice one I feel very relax to solve them
Thanku very much
Guyz watch everyday Data science channel where he already solved so many leetcode interview questions.. Probable qsn for interview.. Do watch tfq.. Of you stuck somewhere get help there.
Thank you Rakesh :)
Your exemples with the excel really helped understand the logics, thanks!
Glad you liked it
Very smart query. Thanks for sharing this query
Your welcome buddy
The video is quite informative, and way of teaching is great.
This guys explanation is perfect and amazing
your videos helps me a lot to solve complex queries...
I am glad to hear that buddy
very helpful all the sql queries examples thanks brother please share more the complex sql queries real life examples
Awesome Explanation on Third Query... Thank You so much.
How to split a string into 2 strings and make first and second as capital letter. Example goodmorning(no space) -Good Morning
case- when -then- else ....like if ,elif, else condition (python )
hello all , here is my query with easy understanding for the last IPL match query . For each team play with every other team twice , here we can use cross join ... select IPL.team_name , O.team_name from IPL ,IPL as O
Where IPL . teamcode O. teamcode
I never imagined that i can also write a query with understanding SQL thank you techTFQ
dear ftq salute on your explanation on sql subject
Thank you buddy
Thank you this was powerful and expanded my understanding on the application of SQL
good explanation with all examples. Thank you
Really good explanation.. easy to understand even the problem is very complex.. thank you so much for your time
you have excellent way of explaining the query and how system works.. really great videos.. good work
Wonderful expiations, especially the last question has a newer nuance..
Amazing explanation, these 37 minutes were totally productive
very very helpfull, thank you. will be usefull if more such videos are brought out
Excellent! Thank you very much. Such a hard concept to understand, you made so easy sir.
Good job Towfiq Jan!
Glad you liked it bro
really, very appreciative video and the channel itself fro learning sql.
Thanks a lot sir for providing this.
Thanks you Wagh :)
Thank tou for serving us the knowledge we need!
thanks teacher, im learning alot your a great teacher
Useful video, really helps for learning as well as for interview, great job ya, please post more videos.... 🙂. It helps alot
Ur way of explanation is super,
Keep go like this sir
Just loved the way you teach....thanks a ton brother....Respect from INDIA.
I just got to know He is from Malesiya , such really amazing contents not finding anywhere in youtube.
Thanks. Thanks a lot. Your concepts are easily understandable.
Glad to hear that bro
We want more n more videos like this... excellent work...keep it up ...
Thank you for sharing another great video..
Please make video on data import, export and back-up.
Thanks for your time..
Thank you Abhishek and sure will do :)
Words are not enough to say thank you , love this bro , GBU
Glad you liked it buddy :)
This video was really interesting. Your explanations are great! Thank you
Sahi video, sahi samay par!
Hope this helps 👍
it was amazing learning. Please make something on normalization if possible
Thank you and will consider your request
It will be very helpful
Excellent explanation...thank u so much for such kind of sessions.
Most welcome bro!
The 3rd question is very good.
Nice keep posting such videos .
Please share video about complex topic in sql
Thank you Saurav and will do
Hi ,
Please make a Vedio on Normalisation and Indexing , everywhere they ask these concepts
noted, will plan it
Excellent.... it's completely worth spending time in watching your videos. . .
I am so glad to hear that ☺️
@@techTFQ you deserve 👍
Thank you Nivedha 🙏🏼
excellent work sir !! please come with more practical example like this ,,
Thank you Das and will do
Well presented and solved ! Mnay thanks TFQ !
its great to learn this query
Just Awesome Brother ! SQL is an ocean and you're helping others to voyage !!
Thank you buddy
awesome. would like more such videos
Very nice, Please make videos on Stored procedure and User defined functions wrt its usage in job.🙏
Thank you and sure will do
Hello, You are doing a great job with a good explanation. please make a detailed video on Database Performance Tuning
Thank you Sulaiman and sure will do
Solving question 2 you should perform a full outer join, select rows that have null values in either source or target name or names that mismatch. and use an ifelse function to name the occurences in the variable Comment. Here is the code (with data) using data.table package in R:
library(data.table)
x1=c(1,2,3,4)
x2=c("a","b","c","d")
y1=c(1,2,4,5)
y2=c("a","b","x","f")
X=data.table(id=x1,name=x2)
Y=data.table(id=y1,name=y2)
merge(X,Y,by="id",all=TRUE)[name.x!=name.y | is.na(name.x) | is.na(name.y),.(id=id,Comment=ifelse(is.na(name.y),
"New in Source (X)", ifelse(is.na(name.x),"New in Targe (Y)","Mismatch")))]
Sir, Could you please make a video on Index in SQL? How it will be useful in real time?
Sure will do
For 2 Q -
select
case
when id1 is not null and id2 is null then id1
when id2 is not null and id1 is null then id2
when id1=id2 then id1 end as ID,
case
when name1 is not null and name2 is null then 'New in Source'
when name1 is null and name2 is not null then 'New in Target'
when id1=id2 and name2name1 then 'Mismatch' end as Comment
from
(select s.id as id1,s.name as name1,t.id as id2,t.name as name2
from source s
full outer join target t
on s.id=t.id
where s.id is null or t.id is null or
(s.id=t.id and s.namet.name))x
thats right. Ive provided similar solution in my blog too for this question
I got some tricky SQL queries with answers,
I hope it will help me
very helpful tutorial with realistic examples..
Thank you buddy
Brilliant Query approach
This is really wonderful video. Thank you so much. Please do come up with such wonderful contents.
Thank you bro
Thank you for taking your time to explain this.
Glad you liked
Thank you so much for the thoughtful explanation. 😊
One thing came into mind, 2nd query with union of 3, instead, can we use full join with case statement to print the desire output?
He said yes - see his blog
Totally different , easy to understand 👌
Thank you 🙏🏼
It's really helpfull. Thank you...
Thank you 🙏🏼
I am always eagerly waiting for your next vedio.. to learn new things.. keep going..
Thank you for the support Prajakta 🙏🏼
Appreciate it ☺️
My version of problem 2:
select
ID
,Case when ID = 3 then 'New in source'
when ID = 4 then 'Mismatch'
when ID = 5 then 'New in target'
End as Comment
from source
where ID in (3,4,5)
union
select ID
,Case when ID = 3 then 'New in source'
when ID = 4 then 'Mismatch'
when ID = 5 then 'New in target'
End as Comment
from target
where ID in (3,4,5)
Really appreciate your efforts on this sir..🙏
Thank you .. for the great explanation!
For the last we can use cross join
select t1.team_name as team, t2.team_name as opponent from teams t1
cross join teams t2
where t1.team_name t2.team_name
This will give the result for each team playing against each other twice.
Loved you. Amazing session and videos. Love you more. Thanks a ton. You get lot of fame and money and you are amazing. God bless you.