SQL Interview Questions and answers Part 27 | SQL Scenario based Interview Question and Answer

SQL Interview Questions and answers Part 21 | SQL Scenario based Interview Question and Answer

Input :- SampleTable has columns namely Start_Range and End_Range
Problem Statement :- Write a SQL query to print the Sequence Number from the given range of number.
---------------------------------------------
Table And Insert Script:
---------------------------------------------
Create Table SampleTable
(
Start_Range Bigint,
End_Range Bigint
);
Insert into SampleTable Values (1,4)
Insert into SampleTable Values (6,6)
Insert into SampleTable Values (8,9)
Insert into SampleTable Values (11,13)
Insert into SampleTable Values (15,15)

---------------------------------------------------------------------------
Follow me on Social Networking Sites :
----------------------------------------------------------------------------
*Twitter:
*Instagram :

#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #ETLTesting

[priyakaja-vvsl]

This SQL interview playlist really helped me and learned a lot. thank you great work!!

[LiveWithDebasmita]

declare @maxv int;
set @maxv = (select max(End_range) from SampleTable);
declare @minv int;
set @minv = (select min(Start_range) from SampleTable);


with cte as
(
select
@minv as id
union all
select
id+1 as id from cte
where id< @maxv
)
select id from cte, SampleTable where id between Start_Range and End_Range;

[reachrishav]

Thanks a lot for such wonderful problems.

with total_range as(
SELECT value as num
from GENERATE_SERIES((SELECT min(start_range) from SampleTable),
(SELECT max(end_range) from SampleTable))
)

SELECT r.num
FROM total_range r
JOIN SampleTable t on r.num between t.start_range and t.end_range

[kapoorrishb]

this is really good work. Thank you so much!

[YASHKUMARJAIN]

mysql
with recursive cte as (
select start_range as id, end_range from SampleTable
union all
select id +1 as id, end_range from cte
where id <end_range)
select id from cte
order by id

[arunkumarn]

with cteSeq as

(select start_range, end_range from sampletable
union all

select start_range +1, end_range from cteSeq where start_range +1<=end_range)
select start_Range from cteseq order by start_range

[umrbeksabirov]

;with cte as
(
select 1 as id
union all
select id+1
from cte where id<15
)
select id from cte a
join SampleTable b
on a.id>=b.Start_Range and a.id<=b.End_Range

[somasreebiswas]

Another 1:
with cte as
(
select Start_Range, 1 as cnt, End_Range
from SampleTable
union all
select Start_Range +1, cnt +1, end_range
from cte where cnt+1 <= End_Range and Start_Range <> End_Range
)
select Start_Range id from cte
order by Start_Range

[SnappTrends]

with cte as(
select start_range, end_range from #sampletable
union all
select start_range+1, end_range from cte where start_range+1<=end_range)

select start_range as id from cte order by start_range

[somasreebiswas]

nice explanation, i tried the below:

with cals as
(
select max(end_range) maxno from SampleTable
)
, ctenum as(
select 1 as num
union all
select num +1
from ctenum
where num < (select maxno from cals)
)
SElect num from ctenum
join SampleTable on num between start_range and end_range

[payalchauhan]

This channel is a gem. Your videos are really helpful. Kudos to your spirit of doing such great efforts !! :)

[belugastripe]

Here is my solution:
WITH TEMP AS (
SELECT
START_RANGE,
END_RANGE,
START_RANGE AS ITER
FROM
SAMPLETABLE
UNION ALL
SELECT
START_RANGE,
END_RANGE,
ITER+1
FROM
TEMP
WHERE
ITER < END_RANGE
)

SELECT ITER FROM TEMP ORDER BY ITER

[pankajkharade]

In Oracle:
With DS As
(
Select START_RANGE, END_RANGE, (END_RANGE-START_RANGE)+1 Diff From SampleTable
), DS1 AS
(
Select * From DS,
Lateral(Select (Rownum-1) RN From Dual Connect By Level<=Diff)
)
Select START_RANGE+RN Final_Output From DS1;

[rohitthapliyal]

table A
1
2
3
4
5
6

table B
1 4
6 6

after cross join
1 1 4
1 6 6
2 1 4
2 6 6
3 1 4
3 6 6
4 1 4
4 6 6
5 1 4
5 6 6
6 1 4
6 6 6


after filer id >= start_range and id <= end_range
1 1 4
2 1 4
3 1 4
4 1 4
6 6 6

[smallik]

declare
a number(2);
b number(2);
start_range1 number(2);
end_range1 number(2);
max_rn number(2);

begin

insert into sample_table_tmp
select row_number() over (order by start_range) rn, start_range, end_range from SampleTable;

select max(rn) into max_rn from sample_table_tmp;

for a in 1..max_rn loop
select start_range into start_range1 from sample_table_tmp where rn=a;
select end_range into end_range1 from sample_table_tmp where rn=a;
for b in start_range1..end_range1 loop
dbms_output.put_line(b);
end loop;
end loop;
end;