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

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

Problem Statement :- ITEM Table has two columns namely ItemName and TotalQuantity. Write a SQL query to duplicate the rows based on total count in output table by adding two new columns ID and CatID

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

*Twitter:

-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------
Create Table Item(
ItemName Varchar(30),
TotalQuantity int
)
Insert into Item Values('Apple',2)
Insert into Item Values('Orange',3)

-----------------------------------------------------------------------------------------------------------------
#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #ETLTesting #ITJUNCTION4ALL #AmazonInterview

[YASHKUMARJAIN]

MYSQL:


with recursive cte as
(select itemname, 1 as catid, totalquantity from item
union all
select itemname, catid+1,totalquantity from cte
where catid <totalquantity) # jase hi id =q hua ab recur= or run nhi hoga for that particular row and moves to next row from main table

select row_number() over(order by itemname, catid) as id, c.* from cte c

[notavi]

with recursive cte(n) as (
select 1
union all
select n + 1
from cte
where n < (select max(TotalQuantity) from item)
)
select row_number() over(order by itemname, n) id, itemname, n as catid, totalquantity
from item i
left join cte c
on i.totalquantity >= c.n;

[bishwarup]

with recursive cte as (
select ItemName
, TotalQuantity
, 1 as catid from item
union
select ItemName,
TotalQuantity,
catid+1 as catid
from cte where catid+1 <= TotalQuantity)
select *,
row_number() over(order by ItemName) as Id from cte
order by ItemName, catid;

[sonurawat]

in oracle:
select itemname, totalquantity, level from item
connect by level<=totalquantity
group by itemname, totalquantity, level
order by itemname;

[reachrishav]

with cte as(
select itemname, 1 as catid, totalquantity
FROM item
UNION ALL
SELECT itemname, catid + 1, totalquantity
FROM cte
where catid < totalquantity
)
SELECT row_number() over(order by itemname, catid) id, *
from cte
order by itemname

[saktidey]

this question for how many year of experience for them?

[aravindashok]

you can also try this query(sql oracle database)

select row_number()over(order by itemname)as id, itemname, catid, totalquantity from
(

select itemname, tq as catid, totalquantity, max(tq)as totq from

(

select 1 as id, itemname, totalquantity, totalquantity-(level-1)as tq
from item connect by level<=totalquantity

)GROUP BY itemname, tq, totalquantity order by itemname
);

[vijay.s-llyq]

WITH CTE AS
(SELECT ItemName, TotalQuantity, 1 AS RNK FROM ITEM
UNION ALL
SELECT ItemName, TotalQuantity, RNK+1 FROM CTE
WHERE RNK+1 <=TotalQuantity
)
SELECT ITEMNAME, RNK, TotalQuantity FROM CTE
ORDER BY 1

[soumeshkayast]

SELECT ID, NAME, GENDER FROM (
SELECT
ID,
NAME,
GENDER,
ROW_NUMBER()OVER(ORDER BY ID) AS RANK
FROM EVEN_ODD_RECORDS)A
WHERE RANK %2=0;

hints-- %2=0 for even number and %2=1 for odd number