SQL Interview Problem - Solution(Part-XXIX) |

#dataanalyst #sqlfordataengineer #education #dataanalytics
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mah...
Table Create and insert statements:
----------------------------------------------------------
create table Alphabetwords(letter_word varchar(10))
insert into Alphabetwords values ('D'),('A'), ('B'), ('C'), ('E'), ('Elephant'),('Apple'),('Cat'), ('Donkey'), ('Ball')

[@Ujjwalmishra-t1w]

Nice sir

[@rakeshkoli4384]

nice

[@Soul-f3v]

with cte1 as(
select letter_word as single_letter from Alphabetwords where LEN(letter_word)=1),
cte2 as(
select letter_word as multiple_letter from Alphabetwords where LEN(letter_word)>1)
select distinct single_letter,multiple_letter from cte1,cte2 where SUBSTRING(single_letter,1,1) = SUBSTRING(multiple_letter,1,1);

[@arjundev4908]

with cte as(SELECT *,
case when length(substring(letter_word,1))> 1 then substring(letter_word,1,1) else '' end as stat
FROM alphabetwords)
select c.letter_word as Alphabet, c1.letter_word as Words
from cte as c
join cte as c1 on
c.letter_word = c1.stat
order by 1;

[@hairavyadav6579]

My approach
select * from words w1 join words w2 on w1.letter_word=left(w2.letter_word,1) and length(w1.letter_word)!=length(w2.letter_word) order by w1.letter_word;

[@raobbulusu]

select a1.letter_word as l1, a2.letter_word l2 from Alphabetwords a1 join Alphabetwords a2 on a1.letter_word != a2.letter_word
where l1 = substring(l2, 1,1)

[@devrajpatidar5927]

here is my approch using self join :-
select a.letter_word as Alphabet,b.letter_word as Words
from Alphabetwords as a
inner join Alphabetwords as b
on a.letter_word = left(b.letter_word,1) and len(b.letter_word) 1
order by Alphabet;

[@Chathur732]

with cte as
(
select *, case when length(letter_word) = 1 then letter_word end as letter,
case when length(letter_word)>1 then letter_word end as word
from Alphabetwords
order by case when length(letter_word) = 1 then letter_word end
)
select c1.letter, c2.word
from cte c1 inner join cte c2 on c1.letter = substring(c2.word,1,1)
OR
SIMPLE SOLUTION:
select *
from Alphabetwords A join Alphabetwords B on A.letter_word = substring(B.letter_word,1,1) and
length(A.letter_word)

[@VenkateshMarupaka-gn3rp]

My solution:
SELECT a.letter_word AS Alphabet, b.letter_word AS Word
FROM Alphabetwords a
JOIN Alphabetwords b
ON a.letter_word = SUBSTRING(b.letter_word,1,1) AND LEN(a.letter_word) < LEN(b.letter_word)
ORDER BY a.letter_word

[@jokerman1051]

bro your voice is very low

[@MeanLifeStudies]

Sorry. At my end, it was good while I was checking the video and audio. I will look into into again. I will make sure I will rectify it from next onwards. Thank You.

[@jokerman1051]

@@MeanLifeStudies Thanks bro for uploading solutions. it helps me alot

[@harshitsalecha221]

SELECT c1.letter_word, c2.letter_word FROM alphabetwords as c1
INNER JOIN alphabetwords as c2
ON c1.letter_word=LEFT(c2.letter_word,1) AND length(c2.letter_word)>1
ORDER BY c1.letter_word;

[@MeanLifeStudies]

Yes, this is perfect for a given problem. I have used windows and join just if incase counter questions asked to return differently like
a for ball, b for cat, c for donkey........