Most Asked SQL Interview Question - Solved Using 3 Methods
HTML-код
- Опубликовано: 31 июл 2024
- In this video, we will learn solving a most asked SQL interview question, which was asked in Google, Amazon and Uber interview. We will solve this question using 3 different methods.
If you liked this video, then please hit LIKE button and SUBSCRIBE this channel for more videos related to Data Science- skills and jobs
Timestamp:
00:00 Intro
00:17 SQL Interview Question
01:30 Method 1- SQL Query Solution
06:51 Method 2- SQL Query Solution
11:28 Method 3- SQL Query Solution
14:26 Outro
➖➖➖➖➖➖➖➖➖➖➖➖➖
Related Videos
Introduction to SQL - What Is SQL + Database | SQL Tutorial In Hindi 1
• What Is SQL ? | Databa...
Data Types, Primary-Foreign Keys & Constraints in SQL | SQL Tutorial In Hindi 2
• Data Types, Primary-Fo...
Create Table In SQL & Create Database | SQL Tutorial In Hindi 3
• Create Table In SQL & ...
INSERT UPDATE, DELETE & ALTER Table in SQL With Example | SQL Tutorial in Hindi 4
• INSERT UPDATE, DELETE ...
SELECT Statement & WHERE Clause with Example | Operators in SQL | SQL Tutorial in Hindi 5
• SELECT Statement & WHE...
How To Import Excel File (CSV) to SQL | Import Data From File | SQL Tutorial in Hindi 6
• How To Import Excel Fi...
String Functions in SQL | Functions in SQL | SQL Tutorial in Hindi 7
• String Functions in SQ...
Aggregate Functions in SQL - COUNT, SUM, AVG, MAX, MIN | SQL Tutorial in Hindi 8
• Aggregate Functions in...
Group By and Having Clause in SQL | SQL Tutorial in Hindi 9
• Group By and Having Cl...
TimeStamp and Extract Function | Date Time Function | SQL Tutorial in Hindi 10
• TimeStamp and Extract ...
Complete SQL JOINS For Beginners | SQL JOIN Queries with Examples | SQL Tutorial in Hindi 11
• Complete SQL JOINS For...
SQL SELF JOIN | UNION & UNION ALL in SQL | SQL Tutorial in Hindi 12
• SQL SELF JOIN | UNION ...
Complete SQL Subquery Using Comparison and Logical Operators | SQL Tutorial in Hindi 13
• Complete SQL Subquery ...
Useful videos
How to Become Data Analyst in 2022 | Data Analyst Roadmap
• How to Become Data Ana...
Data Analyst skills, jobs and Roadmap
• Data Analyst- Skills, ...
Excel Tutorial for Beginners
• Excel for Beginners | ...
➖➖➖➖➖➖➖➖➖➖➖➖➖
Connect on other Social Media handles:
Instagram: / rishabhnmishra
LinkedIn: / rishabhnmishra
Twitter: / rishabhnmishra
➖➖➖➖➖➖➖➖➖➖➖➖➖
If this is the first ever video you're watching allow me to introduce myself, Hey I'm Rishabh Mishra and currently working as Senior Data Analyst at Bangalore. On this channel I like to guide and help my juniors and data science aspirants regarding Data Science jobs and useful tips for college students.
Have a great day buddy!
#sqlinterviewquestions #sqlinterviewquestionsandanswers
In the first approach without doing group by and all,we can just apply distinct after greatest and and least things
here we can use mod function mod(id,2)=0 actully we can solve many methods but sir you solved very easy way great
Mishra ji 🙏, you are wonderful teacher and it is reflected in your videos. 🙂 It is also evident from your prof. experience.
Glad you liked ✅️
I go with inner join. Method 2.
Method 3 slightly difficult.
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.
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
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);
should have checked comments before starting the video lol
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
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.
Glad it's helpful ✅️
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?
You can watch my video on SQL, it has all the topics u mentioned
hi, love your teaching technique, please make a video on advance power bi interview Q&A. it will really appreciated.
thankyou
Today uploaded a video on advanced power bi DAX
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;
great and excellence sir
Glad you liked ✅️
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);
thanks for sharing :)
thnx alot man....how these greatest is working and giving output i got confused in that...u saved me
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 ;
Using greatest and least method
select greatest(source,destination),least(source,destination),max(distance)
from travel
group by greatest(source,destination),least(source,destination);
Hello, could you please help on min vs least ,while doing least are we need partion on table ?
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
I have a query, there are multiple data sciences projects available in RUclips 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
please comeup with more interview questions (sql) that would be helpful. Thank you
Already uploaded 4 videos on sql interview questions and answers
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
why not just use select statement
SELECT * FROM travel WHERE source > destination
Why u use not exist in subquery method?
Bhaiya direct bhi ho skta h na ye to
Select * from travel Where source>deatination;
Aise
usme last wala source thdi aayega kyuki p>n hta h na shyd
Do you have in English version?
Sir, Is there any chance of connecting with you over a call?
platform to practise advance sql??
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 😅
Is it, okay next time will make it more understandable ✅️
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...🖖
When we use GROUP BY, it's a must to use an aggregate function. That's why MAX is used here.
Hi, sir aap data ka file post kr diya kijiye so practice kr sake hmlog
Hi, it's a simple data set with 3 columns. You can prepare on your own 😄
@@RishabhMishraOfficial ok sir
first method was the easiest...other 2 I did not understand😅
Awesome 👌
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
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.
why not just SELECT * FROM travel WHERE source > destination, also only exists keyword in place of not exists can be used...
with Travel as (select * ,row_number() over() as SrNo from Table_Name)
select * from Travel where SrNo%2=0
another Easiest Solution
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 ????
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.
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?
What if there is n no. Of city. And you don't know the names .. will you pass n no. Of city.
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!
Yess issiliye 3 methods share kiya hai.. and already mentioned in the video that 3rd method is specifically gor this problem. Watch video properly 🤦♂️
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.
Being from non IT background, If i learn SQL and other required things how i get job ???
Are bhai jo hain unhe nhi lag rahi tum kaha bhid badha rahe ho😂
U can get, ignore these Auswholes, learn basics from RUclips and than Start getting certification from Microsoft or Google. If u get these Certificates, u can easily switch
sir group by distance kr denge to ho jaega
Share the sql query here
I solved it this way:
SELECT * FROM travel
WHERE source IN ('Mumbai','Mathura','Nagpur')
is it right ??...coz it is giving the same results
This is not correct because if the data is huge then how you gonna get this list ('Mumbai','Mathura','Nagpur')...?
Hardcoding Values which is Updateble or replacable in any way is not recommended in any programming language.
Always Remember
table download link description mai nhii diya sir?
Query is there just copy paste n you will get the same table created
using sub query ,, inside query is giving the same results, then why we need to use outer not exists
If it works then u can use it 😉
SELECT source,destination,distance FROM travel WHERE source LIKE 'M%' OR source LIKE 'N%';
But it will fail as soon as the use case is changed.
I don't understand the leaste and greatest concept of text not number only text how it work?
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".
Hi sir, i am not getting the exact output
Check the join condition and try again
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
Then another condition need to add
t1.Source = t2.destination AND
t1.destination = t2.source AND
t1.source > t2.source
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.
i found out the easiest one
select source, destination, distance FROM travel
WHERE length(source) between 6 and 7
this method is not dynamic. If a new value comes up then the code needs to be changed
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);
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 😊
In MySQL, it is working even without Group By Clause using Greatest and Least functions. Any idea how its working
Explanation should be improved...
Okayy Raman bhai, will work on it 🙏
I m failed to do also understand
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 ;