Infosys SQL Interview Question

SQL questions recently asked in Infosys interview. We need to find origin and final destination details.
To find out the expected output, I use joins.

--Create table syntax
CREATE TABLE Flights (cust_id INT, flight_id VARCHAR(10), origin VARCHAR(50), destination VARCHAR(50));

-- Insert data into the table
INSERT INTO Flights (cust_id, flight_id, origin, destination)
VALUES (1, 'SG1234', 'Delhi', 'Hyderabad'), (1, 'SG3476', 'Kochi', 'Mangalore'), (1, '69876', 'Hyderabad', 'Kochi'), (2, '68749', 'Mumbai', 'Varanasi'), (2, 'SG5723', 'Varanasi', 'Delhi');

For more SQL interview questions. Check out our playlist.

Contact us:

Follow us on

[yashkumarmundada]

Another approach -

select c as cust_id, max(origin) as origin, max(final_destination) as final_destination from (
select cust_id as c,
case
when origin not in (select destination from flights where cust_id = c) then origin
end origin,
case
when destination not in (select origin from flights where cust_id = c) then destination
end final_destination
from flights) t
group by cust_id

[adityavamsi]

Amazing...please bring complex questions like this❤

[AkashPabbisetti]

Another approach -

SELECT CUST_ID,
ORIGIN,
final_destination
FROM(
SELECT *,
LEAD(DESTINATION) OVER(PARTITION BY CUST_ID) AS final_destination
FROM flights) AS t
WHERE ORIGIN IN ('Delhi', 'Mumbai');

[manishsathe]

Logic is important, If you have logic then you can write query
Well Done !

[Chathur]

SIMPLE SOLUTION:

select cust_id,
min(case when (cust_id, origin) not in (select cust_id, destination from flights) then origin end) as origin,
max(case when (cust_id, destination) not in (select cust_id, origin from flights) then destination end) as final_destination
from flights
group by cust_id
order by cust_id

[subradip]

select * from flights ;
select distinct cid, first_value(origin) over(partition by cid order by cid ) as actual_origin,
last_value(Destination) over(partition by cid order by cid) as final_destination
from flights

[arjunv]

With cte as
(select cust_id, origin as place, 'origin' as type
from Flights
Union all
select cust_id, destination, 'destination' as type
from Flights), cte2 as
(select *, count(*) over(partition by cust_id, place) as cnt from cte
)
select cust_id, max(case when type='origin' then place end) as origin,
max(case when type='destination' then place end) as destination
from cte2
where cnt=1
group by cust_id

[dreamer]

Can you please help me with infosys SQL recruitment process my resume does not selected everytime

[motiali]

My solution
SELECT a.cust_id,
max(case when b.cust_id is null then a.origin end ) as origin,
max(case when c.cust_id is null then a.destination end ) as destination
FROM Flights a
left join Flights b on a.cust_id=b.cust_id and a.origin=b.destination
left join Flights c on a.cust_id=c.cust_id and a.destination=c.origin
group by
a.cust_id;

[chandanpatra]

Thanks for bringing a valuable question. Actually this question is a leetcode DSA question which was solved in ankitbansal youtube channel before 6 months.
Since you have used only 1 method to solve i.e. using self joins. But he explained another method also along with self joins method using "union all", "cte", "case stat" and "windows function". I like the second method.
Logic used to solve using 2nd method:
- First uinion all the start & end locations
- then use count(*) of those start & end locations
- then only filter where count=1 using max/min & case statement to ignore null values.

keep it up. Bring such good questions.
already liked the video.

[hairavyadav]

My approach
select cust_id, max(case when (cust_id, origin) not in (select cust_id, destination from flights group by cust_id, destination) then origin end) as origin,
max(case when (cust_id, destination) not in (select cust_id, origin from flights group by cust_id, origin) then destination end) as destination
from flights group by cust_id;

[bestquotes]

Can we use recursive CTE to solve this problem

[tanujreddy]

can this question be asked for freshers

[jrtrishi]

bro how to apply for sql job roles? is it possible for 2022 batch as a fresher?

[ranjeetdeshmukh]

How much salary will a fresher get for SQL?

[anuragsarkar]

If I want to make it where the output will be..

customerid, origin, destination, via

via column will contains the values of the cities other than source and destination.

Like for customerid = 2, via will be Pune

For customerid = 1, via = Hyderabad, Kochi