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

SQL / ORACLE Scenario Based Interview Questions Part 7
This question has been taken from Amazon's interview questions.
In this video it has been shown how to do row duplication in Sql Server.

INPUT :- Order_Tbl has four columns namely ORDER_ID, PRODUCT_ID, QUANTITY and PRICE
Problem Statements :-
Write a SQL which will explode the above data into single unit level records as shown below

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :
*Twitter:
-------------------------------------------------------------------------
#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sql InterviewQuestionsQuery #sqlInterviewQuestionsOnJoins

[nihal]

table for this question-

CREATE TABLE Order_Tbl(
ORDER_ID varchar(10),
PRODUCT_ID varchar(10),
QUANTITY int);

INSERT INTO Order_Tbl(ORDER_ID, PRODUCT_ID, QUANTITY)
VALUES('odr1', 'prd1', 5), ('odr2', 'prd2', 1), ('odr3', 'prd3', 3);

select * from Order_Tbl;

[shakiraafshan]

Hi sunil,
your every video is very helpful. the best channels to learn SQL yours and kudvenkat

[ashokrajann]

Initial first quantity count is coming from anchor part?
Since in recursive, quantity count gets incremented. Pls confirm

[vigneshb]

Good one bro
For oracle query goes like this
With temp(order_id, product_id, quantity, quantity_1) as (
Select order_id,
Product_id,
Quantity,
1 as quantity_1
From orders
Union all
Select order_id
, product_id
, quantity - 1 as quantity
, 1 as quantity_1
From temp
Where quantity >1)
Select order_id, product_id, quantity_1 from temp
Order by order_id

[pankajkharade]

Using Lateral join in oracle :

Select od.ORDER_ID, od.PRODUCT_ID, od.QUANTITY From Ord_Dtls od,
Lateral
(Select Rownum RN From Dual Connect By Level<=od.QUANTITY);

[vedasiva]

I used while loop to achieve this
while exists (select quantity from ordertbl where quantity>1)
begin
insert into OrderTbl
select order_id, product_id, 1 from ordertbl where quantity >1;

update ordertbl
set quantity = quantity-1 where quantity >1;

end
;Select * from ordertbl

[vijay.s-llyq]

with cte as
(select order_id, product_id, QUANTITY, 1 as rnk from Order_Tbl

union all
select order_id, product_id, QUANTITY, rnk+1 from cte
where rnk+1 <= QUANTITY)

select * from cte
order by 1

[kasidurai]

Hi Sunil
kindly Provide the Input data for the table

[shravanreddymuthyala]

with recursive temp_cte as
(select order_id, products_id, 1 as part, quantity - 1 as reminder from order_table
union all
select order_id, products_id, 1 as part, quantity - 1 as reminder from temp_cte
where reminder > 0
)
select order_id, products_id, part as quantity from temp_cte

[nihal]

can provide mysql query for this one please?

[reachrishav]

select order_id, product_id, 1 as quantity
from Order_Tbl o
CROSS APPLY GENERATE_SERIES(1, o.quantity)

[abhrajyotichanda]

with temp as (Select * from ORDER_TBL2 )
Select order_id, product_id, 1 qty, quantity
from
temp,
(Select level lvl from dual connect by level <= (Select max(quantity) from temp)) ref
where temp.quantity >= lvl
order by temp.product_id;

[livelylife]

another way
;with a as( select SUBSTRING(replicate(concat(order_id, ', '), quantity), 1, len(replicate(concat(order_id, ', '), quantity))-1)as order_id
, PRODUCT_ID,1 as quantity from Order_Tbll)
select value order_id, product_id, quantity from a cross apply string_split(order_id, ', ')

[shiwenyang]

MS sql server
;with tally as
(select top 100 row_number() over(order by name) as id from sys.all_columns
)
select * from orderinfo
cross apply (select 1 as d from tally where id<=orderinfo.quantity) b
order by ord, prod

[notavi]

with recursive cte as (
select order_id, product_id, 1 as cnt from order_tbl

union all

select a.order_id, a.product_id, b.cnt + 1
from order_tbl a
join cte b
on a.order_id = b.order_id
and a.product_id = b.product_id
where b.cnt + 1 <= a.quantity
)

select order_id, product_id, 1 as quantity from cte
order by 1, 2;

[praveenagokul]

Alternate solution:
;WITH CTE(Order_ID, Product_ID, Quantity, num, increment)
AS

(SELECT o.[Order_ID], o.[Product_ID], o.[Quantity], 1 as n, 1 as i
FROM [dbo].[order_table] o
union all
SELECT Order_ID, Product_ID, Quantity, num, increment+1
FROM CTE
where increment+1<= Quantity)

select Order_ID, Product_ID, num as Quantity from CTE
order by Order_ID

[prasannaboyapati]

Can you please give the query for below?
Input
date | N
2020-01-01 | 2
2020-02-01 | 1
2020-03-01 | 3
2020-04-01 | 4

Expected output
date | N
2020-01-01 | 2
2020-01-02 | 2
2020-02-01 | 1
2020-03-01 | 3
2020-03-02 | 3
2020-03-03 | 3
2020-04-01 | 4
2020-04-02 | 4
2020-04-03 | 4
2020-04-04 | 4

[sonurawat]

in oracle insert statment
CREATE TABLE Order_Table(
ORDER_ID varchar(10),
PRODUCT_ID varchar(10),
QUANTITY int);

INSERT all
INTO Order_Table VALUES('odr1', 'prd1', 5)
INTO Order_Table VALUES('odr2', 'prd2', 1)
INTO Order_Table VALUES('odr3', 'prd3', 3)
select * from dual;

[anirvansen]

MYSQL Solution


with recursive base as(
select order_id, product_id, quantity, 1 as q, 1 as counter
from Order_Tbl
UNION ALL
select order_id, product_id, quantity, q, counter+1 as counter
from base where counter+ 1 <= quantity
)
select order_id, product_id, q as quantity from base order by order_id;