Most Asked SQL Interview Question - Solved Using 3 Methods

[@NidaHasan-h3n]

create table travel ( source varchar(50), destination varchar(50), distance int); insert into travel (source, destination, distance) values ('Mumbai', 'Bangalore', 500), ('Bangalore','Mumbai', 500), ('Delhi', 'Mathura', 150), ( 'Mathura','Delhi', 150), ('Nagpur', 'Pune', 500), ( 'Pune','Nagpur', 500);

[@duhflamingo]

should have checked comments before starting the video lol

[@thealchemist8212]

Answer of Ouestion 1 :- select t1.source, t2.destination, t2.distance from travel as t1 join travel as t2 on t1.source = t2.source and t1.destination = t2.destination and t1.distance = t2.distance where t1.source < t1.destination and t2.source < t2.destination

[@alenb5190]

In method 1 using Greatest() and Least(), in our output (6:00) 3rd column we are getting Pune -> Nagpur but we are required to show(0:21) Nagpur -> Pune.

[@subhradipbarik2032]

In the first approach without doing group by and all,we can just apply distinct after greatest and and least things

[@govindshah6037]

Please find the code to create the mentioned table in this tutorial: create table travel ( source varchar(50), destination varchar(50), distance varchar(50)); insert into travel (source, destination, distance) values ('Mumbai', 'Bangalore', 500), ('Bangalore','Mumbai', 500), ('Delhi', 'Mathura', 150), ( 'Mathura','Delhi', 150), ('Nagpur', 'Pune', 500), ( 'Pune','Nagpur', 500);

[@RishabhMishraOfficial]

thanks for sharing :)

[@jayveersingh9803]

thnx alot man....how these greatest is working and giving output i got confused in that...u saved me

[@shraddhadhakad1154]

Using greatest and least method select greatest(source,destination),least(source,destination),max(distance) from travel group by greatest(source,destination),least(source,destination);

[@ishika7585]

with cte as( select 1 as id,source,destination,distance from travel union all select 2 as id,destination,source,distance from travel) select * from cte where source>destination and id=1

[@venuamrutham6179]

My personal two methods, M1: select distance,GROUP_CONCAT(source) from (select distinct * from table) as sub_query GROUP BY distance; (:: "select distinct * from table" important if you have original duplicates cities pair) M2: select DISTINCT greatest(source,destination) as "from",least(source,destination) as "to",distance from table;

[@NiteshKumar-ys1kq]

Bhaiya direct bhi ho skta h na ye to Select * from travel Where source>deatination; Aise

[@tanishqtomar9786]

usme last wala source thdi aayega kyuki p>n hta h na shyd

[@rahul1994negi]

using cte : with cte1 as (select source, row_number() over(order by (select null)) as rn_s from travel ) select t1.source, t1.destination, t1.distance from travel as t1 join ( select c1.source as source, c2.source as destination from cte1 c1 join cte1 c2 where c1.source c2.source and c1.rn_s < c2.rn_s ) as t2 on t1.source = t2.source and t1.destination = t2.destination ;

[@shivendusharma3592]

Mishra ji 🙏, you are wonderful teacher and it is reflected in your videos. 🙂 It is also evident from your prof. experience.

[@RishabhMishraOfficial]

Glad you liked ✅️

[@floyedmoras1882]

Hi Rishabh, I was trying with multiple methods and i found if we use the query like select * from Travel where source > destination; the answer would be the same and this is the shortest method. though i m not sure if there is any discrepancy with this query, Please clarify or suggest.

[@sahil-cv7tq]

yes its working but canu explain logic behind source>destination i didnt get it.

[@mohitsanjaymahajan3246]

@@sahil-cv7tq Either sourse/ Desitination , any city can be greater, correct, So we can use anyone either all greater or either all smaller source-destination pair... So its giving correct ans...

[@ashwanikumarkaushik2531]

Sir, I enjoyed this session. Great explanation. I learnt SQL from a course recommended by you in one of your videos. SQL Go from Zero to hero by Jose Portilla. Few concepts were not covered in that course, such as windows functions, greatest, least etc. Can you please tell where can I also learn such concepts?

[@RishabhMishraOfficial]

You can watch my video on SQL, it has all the topics u mentioned

[@JALAL-b4s]

We can also use greatest destination and apply distinct

[@hassamulhaq7762]

I go with inner join. Method 2. Method 3 slightly difficult.

[@vinaygupta7469]

hi, love your teaching technique, please make a video on advance power bi interview Q&A. it will really appreciated. thankyou

[@RishabhMishraOfficial]

Today uploaded a video on advanced power bi DAX

[@proud_indian0161]

Hello Rishabh, Thanks for creating such simple explanation for complex problems, Your approach was really easy to understand. I have seen the same question on other youtube channels but it was quite difficult for me to understand the same problem.

[@RishabhMishraOfficial]

Glad it's helpful ✅️

[@OmkarShinde-bz7oy]

here we can use mod function mod(id,2)=0 actully we can solve many methods but sir you solved very easy way great

[@Amann7040]

Hi, sir aap data ka file post kr diya kijiye so practice kr sake hmlog

[@RishabhMishraOfficial]

Hi, it's a simple data set with 3 columns. You can prepare on your own 😄

[@Amann7040]

@@RishabhMishraOfficial ok sir

[@Shubhangisingh55]

Sir, u have given 3 method bt we can simply write it as follows - Select * from travel Where source IN ( Mumbai, mathura, nagpur); Plz tell me if it is correct?

[@gauravsingh-gn4zz]

What if there is n no. Of city. And you don't know the names .. will you pass n no. Of city.

[@piyushgupta5044]

great and excellence sir

[@RishabhMishraOfficial]

Glad you liked ✅️

[@pavanigoud98]

please comeup with more interview questions (sql) that would be helpful. Thank you

[@RishabhMishraOfficial]

Already uploaded 4 videos on sql interview questions and answers

[@navedadenwala5922]

with Travel as (select * ,row_number() over() as SrNo from Table_Name) select * from Travel where SrNo%2=0 another Easiest Solution

[@SayedReyaz]

I am sorry but I must say beginners will find it hard to understand your Queries In your 3rd method beginners will gona ask why is there NOT EXISTS clause. Even after watching your whole SQL playlist 😅

[@RishabhMishraOfficial]

Is it, okay next time will make it more understandable ✅️

[@irfanansari3811]

sir group by distance kr denge to ho jaega

[@RishabhMishraOfficial]

Share the sql query here

[@amitsakhare17]

Sir in using self join I am getting only 2 rows of output but as you doing in your slide it's having 3 rows of data

[@anuradhavaradha2257]

Do you have in English version?

[@SatyaMishra-s9v]

Hello, could you please help on min vs least ,while doing least are we need partion on table ?

[@thealchemist8212]

Answer of Ouestion 1 :- select t1.source, t2.destination, t2.distance from travel as t1 join travel as t2 on t1.source = t2.source and t1.destination = t2.destination and t1.distance = t2.distance where t1.source < t1.destination and t2.source < t2.destination

[@Itstajnoor1]

Why u use not exist in subquery method?

[@SrikantPal-jl1he]

SELECT source,destination,distance FROM travel WHERE source LIKE 'M%' OR source LIKE 'N%';

[@eeshangautam]

But it will fail as soon as the use case is changed.

[@sauravlabade1366]

If you give the link of the tables in description which you used to solved the problems in this video then it will be better rishabh

[@Xavier-jq4ex]

platform to practise advance sql??

[@JayantSorteBCS]

why not just use select statement SELECT * FROM travel WHERE source > destination

[@craft_by_moons]

* doesn't exist now it's sb kuch 😃 I've also started saying like select sb kuch after watching all your videos🤣

[@RishabhMishraOfficial]

Keep practicing & keep learning! 🚀

[@shubhamvarude8898]

table download link description mai nhii diya sir?

[@RishabhMishraOfficial]

Query is there just copy paste n you will get the same table created

[@Subh32]

Being from non IT background, If i learn SQL and other required things how i get job ???

[@Mr.tejaspatil96k]

Are bhai jo hain unhe nhi lag rahi tum kaha bhid badha rahe ho😂

[@whyme_141]

U can get, ignore these Auswholes, learn basics from KZbin and than Start getting certification from Microsoft or Google. If u get these Certificates, u can easily switch

[@arpitgupta520]

sirji third method mein agar dono city same alphabet se start hogi to appka favorite method fail kr jayega (iss table mein it worked) lol!

[@RishabhMishraOfficial]

Yess issiliye 3 methods share kiya hai.. and already mentioned in the video that 3rd method is specifically gor this problem. Watch video properly 🤦‍♂️

[@SyedHaidy]

Comparison operator doesn't just compare first letter. It works by comparing lexicographically. For example: Mumbai < Mvmbai In this case, first letter is equal but in the second letter, "u" comes before "v" so u < v and hence the result Mumbai < Mvmbai.

[@putulsaini6788]

Sir I am using 1st and 2nd method in Microsoft SQL server and it is showing error. In 2nd method it is saying 'the row_number must have an over clause with order by'. And in 1st method it is showing ' greatest ' is not a recognised built-in function name. Please help me

[@shubhamswami7275]

He is using postgre SQL, in MS SQL it doesn't support least and greatest commands. And secondly, u need to specify an order by clause in MS SQL Use order by source, destination it will work.

[@DharmendraKumar-DS]

first method was the easiest...other 2 I did not understand😅

[@RishabhMishraOfficial]

Awesome 👌

[@kumarlalit1840]

Can't understand why the max function is used with distance? We are not using distance in the group by then why is it throwing in an error to add an aggregate function. Is it necessary to add all the called variables while using the group by on any one of them?🤔 Also, why the *where not exists* is added in third method? ThanQ in advance. @rishabh mishra gr8 going...🖖

[@EnglishwithAjayraj]

When we use GROUP BY, it's a must to use an aggregate function. That's why MAX is used here.

[@spokenenglishworld1650]

Sir, Could u pls tell me in Sub query what is the use of NOT EXISTS Function.....what will this not exists function suppose to do here?pls help

[@coursetotal6570]

Sir yeh cte ka concept samjh nhi aarha 2,3 bar video dekhli morning se apki Ki playlist dekh raha hu, all SQL topics are understand properly but cte ke topics me problem aarhi Hai, vo jisme Apne 3 level SQL question krvaye the usme BHI advance level me Jo cte or recursive methods Hai vo samjh nhi Aaya, what can I do now 😞.

[@Mohdibran-j8x]

Good work bro keep it up. My simple approach Select * from (Select *,ROW_NUMBER()over( order by distance) as number from travel) n where n.number % 2=1

[@1997somnathsaha]

I have a query, there are multiple data sciences projects available in KZbin can you please tell me if I can inspire by those projects and make similar projects for my project portfolio, please reply me so that I can start building my project portfolio

[@pandeyRaman]

why not just SELECT * FROM travel WHERE source > destination, also only exists keyword in place of not exists can be used...

[@DharmendraKumar-DS]

I solved it this way: SELECT * FROM travel WHERE source IN ('Mumbai','Mathura','Nagpur') is it right ??...coz it is giving the same results

[@govindshah6037]

This is not correct because if the data is huge then how you gonna get this list ('Mumbai','Mathura','Nagpur')...?

[@SayedReyaz]

Hardcoding Values which is Updateble or replacable in any way is not recommended in any programming language. Always Remember

[@CctnsHelpdesk]

using sub query ,, inside query is giving the same results, then why we need to use outer not exists

[@RishabhMishraOfficial]

If it works then u can use it 😉

[@paragsharma1764]

Sir, Is there any chance of connecting with you over a call?

[@vishnudatt6000]

select * from travel where source>destination; is this query right or wrong it show the same result can anyone please explain this query how this is working ????

[@SyedHaidy]

It's nice, I also came up with it but it won't work if we change the question a little by adding a single entry with no reversed entry, Method 2 will also fail in that situation.

[@govindshah6037]

Method 2 Will not work If there is duplicate value in either source or destination please have a thought on that e.g You can add a new record like ('Mumbai', 'Goa', 700) & ('Goa', 'Mumbai', 700) then try this method-2 and it will not work

[@arupchandra8602]

Then another condition need to add t1.Source = t2.destination AND t1.destination = t2.source AND t1.source > t2.source

[@SyedHaidy]

It will still work, because we are checking both Source and Destination by that AND statement. t1.source = t2.destination AND t1.destination = t2.source -------------------------------------------------------- The situation where Method 2 will fail is if we add a single entry with no reversed entry.

[@sayalidige3500]

Hi sir, i am not getting the exact output

[@RishabhMishraOfficial]

Check the join condition and try again

[@UECSayandeepBanik-et5ev]

i found out the easiest one select source, destination, distance FROM travel WHERE length(source) between 6 and 7

[@superrv0023]

this method is not dynamic. If a new value comes up then the code needs to be changed

[@Hellyeah22916]

I don't understand the leaste and greatest concept of text not number only text how it work?

[@SyedHaidy]

It compares a string lexicographically. For example: "Mumbai < Mvmbai" returns true. In this case, first letter is equal but in the second letter, "u" comes before "v" so u < v and hence the result Mumbai < Mvmbai. Now use this concept in GREATEST and LEAST. If we find GREATEST(Mumbai, Mvmbai), it will give us "Mvmbai".

[@bricknkeyproperties1464]

code created below. create table Travel ( source varchar(20), destination varchar(20), distance int8 ); insert into travel (source,destination,distance) values ('Mumbai','Bnagalore',500), ('bangalore','Mumbai',500), ('delhi','mathura',150), ('mathura','delhi',150), ('nagpur','pune',500), ('pune','nagpur',500);

[@kumarlalit1840]

ThanQ use this insert into create table Travel ( source varchar(20), destination varchar(20), distance int8 ); insert into travel (source,destination,distance) values ('mumbai','bangalore',500), ('bangalore','mumbai',500), ('delhi','mathura',150), ('mathura','delhi',150), ('nagpur','pune',500), ('pune','nagpur',500); I hope this helps 😊

[@abhigyapranshu4791]

In MySQL, it is working even without Group By Clause using Greatest and Least functions. Any idea how its working

[@pandeyRaman]

Explanation should be improved...

[@RishabhMishraOfficial]

Okayy Raman bhai, will work on it 🙏

[@web.developer-d1j]

Sir aur bhi question kaa prepration karwaye plz

[@rutujamohanty5716]

I have found a normal easy way where we can just filter with distance. SELECT C1.SOURCEID, C1.SOURCE, C1.DESTINATION , C1.DISTANCE FROM CITY C1 INNER JOIN CITY C2 ON C1.DISTANCE = C2.DISTANCE WHERE C1.SOURCEID < C2.SOURCEID ;

[@RS_02_]

I m failed to do also understand