should have checked comments before starting the video lol
@thealchemist821214 күн бұрын
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
@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 :)
@jayveersingh98036 ай бұрын
thnx alot man....how these greatest is working and giving output i got confused in that...u saved me
@alenb519015 күн бұрын
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.
@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 ✅️
@proud_indian01614 ай бұрын
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.
@RishabhMishraOfficial4 ай бұрын
Glad it's helpful ✅️
@NiteshKumar-ys1kq Жыл бұрын
Bhaiya direct bhi ho skta h na ye to Select * from travel Where source>deatination; Aise
@tanishqtomar97862 ай бұрын
usme last wala source thdi aayega kyuki p>n hta h na shyd
@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
@vinaygupta746911 ай бұрын
hi, love your teaching technique, please make a video on advance power bi interview Q&A. it will really appreciated. thankyou
@RishabhMishraOfficial11 ай бұрын
Today uploaded a video on advanced power bi DAX
@floyedmoras18824 ай бұрын
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-cv7tq2 ай бұрын
yes its working but canu explain logic behind source>destination i didnt get it.
@mohitsanjaymahajan32465 күн бұрын
@@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...
@JALAL-b4sАй бұрын
We can also use greatest destination and apply distinct
@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.
@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 ;
@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);
@ishika75852 ай бұрын
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
@pavanigoud9811 ай бұрын
please comeup with more interview questions (sql) that would be helpful. Thank you
@RishabhMishraOfficial11 ай бұрын
Already uploaded 4 videos on sql interview questions and answers
@Mohdibran-j8x5 ай бұрын
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
@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;
@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
@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
@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 ✅️
@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 😞.
@SatyaMishra-s9v5 ай бұрын
Hello, could you please help on min vs least ,while doing least are we need partion on table ?
@hassamulhaq7762 Жыл бұрын
I go with inner join. Method 2. Method 3 slightly difficult.
@spokenenglishworld165011 ай бұрын
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
@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
@piyushgupta5044 Жыл бұрын
great and excellence sir
@RishabhMishraOfficial Жыл бұрын
Glad you liked ✅️
@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
@shubhamswami72752 ай бұрын
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.
@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
@Itstajnoor1 Жыл бұрын
Why u use not exist in subquery method?
@anuradhavaradha2257 Жыл бұрын
Do you have in English version?
@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
@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.
@Xavier-jq4ex11 ай бұрын
platform to practise advance sql??
@Subh32 Жыл бұрын
Being from non IT background, If i learn SQL and other required things how i get job ???
@Mr.tejaspatil96k3 ай бұрын
Are bhai jo hain unhe nhi lag rahi tum kaha bhid badha rahe ho😂
@whyme_1413 ай бұрын
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
@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...🖖
@EnglishwithAjayraj3 ай бұрын
When we use GROUP BY, it's a must to use an aggregate function. That's why MAX is used here.
@JayantSorteBCS3 ай бұрын
why not just use select statement SELECT * FROM travel WHERE source > destination
@navedadenwala59225 ай бұрын
with Travel as (select * ,row_number() over() as SrNo from Table_Name) select * from Travel where SrNo%2=0 another Easiest Solution
@irfanansari3811 Жыл бұрын
sir group by distance kr denge to ho jaega
@RishabhMishraOfficial Жыл бұрын
Share the sql query here
@shubhamvarude8898 Жыл бұрын
table download link description mai nhii diya sir?
@RishabhMishraOfficial Жыл бұрын
Query is there just copy paste n you will get the same table created
@pandeyRaman3 ай бұрын
why not just SELECT * FROM travel WHERE source > destination, also only exists keyword in place of not exists can be used...
@paragsharma1764 Жыл бұрын
Sir, Is there any chance of connecting with you over a call?
@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
@SrikantPal-jl1he Жыл бұрын
SELECT source,destination,distance FROM travel WHERE source LIKE 'M%' OR source LIKE 'N%';
@eeshangautam11 ай бұрын
But it will fail as soon as the use case is changed.
@DharmendraKumar-DS Жыл бұрын
first method was the easiest...other 2 I did not understand😅
@RishabhMishraOfficial Жыл бұрын
Awesome 👌
@CctnsHelpdesk11 ай бұрын
using sub query ,, inside query is giving the same results, then why we need to use outer not exists
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 😊
@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
@abhigyapranshu47919 ай бұрын
In MySQL, it is working even without Group By Clause using Greatest and Least functions. Any idea how its working
@thealchemist821214 күн бұрын
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
@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".
@UECSayandeepBanik-et5ev Жыл бұрын
i found out the easiest one select source, destination, distance FROM travel WHERE length(source) between 6 and 7
@superrv00238 ай бұрын
this method is not dynamic. If a new value comes up then the code needs to be changed
@pandeyRaman3 ай бұрын
Explanation should be improved...
@RishabhMishraOfficial3 ай бұрын
Okayy Raman bhai, will work on it 🙏
@web.developer-d1j2 ай бұрын
Sir aur bhi question kaa prepration karwaye plz
@RS_02_ Жыл бұрын
I m failed to do also understand
@rutujamohanty57165 ай бұрын
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 ;