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

SQL Interview Questions and answers Part 40 | SQL Scenario based Interview Question and Answer
Problem Statement :- Write a SQL query to print the desired Output as shown below using two tables i.e:- Emp_Table and Month_Table

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

-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------
Create Table Emp_Table (
SerialNo int,
Name Varchar(30),
Month_ID int,
Amount Bigint )

Insert into Emp_Table Values (1,'JOHN',1,1000)
Insert into Emp_Table Values (1,'JOHN',2,3000)
Insert into Emp_Table Values (8,'DAVID',3,4000)
Insert into Emp_Table Values (8,'DAVID',5,2000)

Create Table Month_Table(
Month_ID int,
Month Varchar(30))

Insert into Month_Table Values (1, 'JAN')
Insert into Month_Table Values (2, 'FEB')
Insert into Month_Table Values (3, 'MAR')
Insert into Month_Table Values (4, 'APR')
Insert into Month_Table Values (5, 'MAY')
Insert into Month_Table Values (6, 'JUN')
Insert into Month_Table Values (7, 'JUL')
Insert into Month_Table Values (8, 'AUG')
Insert into Month_Table Values (9, 'SEP')
Insert into Month_Table Values (10, 'OCT')
Insert into Month_Table Values (11, 'NOV')
Insert into Month_Table Values (12, 'DEC')

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

[ping]

Can you please do explain how to debug the stored procedures or else provide me the link

[notavi]

with recursive emp as (
select serialno, name, 1 as month
from (select distinct serialno, name from emp_table)

union all

select a.serialno, a.name, b.month + 1
from (select distinct serialno, name from emp_table) a
join emp b
on a.serialno = b.serialno
and a.name = b.name
where b.month < 12
)

select a.*, b.amount
from emp a
left join emp_table b
on a.serialno = b.serialno
and a.name = b.name
and a.month = b.month_id
order by 1
;

[bruhhhhh]

with cte as (
select distinct serialno, name, cnt = 1 from emp_table33--anchor
union all
select serialno, name, cnt+1 --recursive
from cte
where cnt < 12
)
select a.serialno, a.name, b.month, c.amount
from cte a
left join month_table b
on a.cnt = b.month_id
left join emp_table33 c
on a.serialno = c.serialno and a.cnt = c.month_id
order by a.serialno, b.month_id


i feel like this would fail edge cases

[ashutoshrai]

Can you please make a video on how to find top 10percent customers from customers table who have not bought anything from the sales table in last 30 days and are male and live in banglore

[mohunnkrishna]

hi i have a scenario which i faced in interview
can u help me with the answer

input table

gender marks
male 100
men 80
boy 90
man 100
women 20
female 100
girl 80

output

gender marks
M 370
F 200

[sarvesht]

Avoid using the bell sound. It's very annoying when we listen to the video in headphones

[reachrishav]

I tried a similar approach:

with cte as(
SELECT *
FROM (select distinct serialno, name from Emp_Table) e
CROSS APPLY Month_Table
)
SELECT cte.serialno, cte.name, cte.month, e.amount
FROM cte
LEFT JOIN Emp_Table e
on cte.month_id = e.month_id and cte.serialno = e.serialno

[TheAbhishekdada]

Below query is without using
select g.srno, g.name, g.mon, g.month_id, l.amount from
(select a.srno, a.s as name, b.s as mon, b.month_id from emp_tab a cross join month_tab b group by a.srno, a.s, b.s, b.month_id) G left outer join
emp_tab l on g.srno=l.srno and l.month_id=g.month_id order by g.srno, g.month_id

[vinaykumar-fybs]

select A.SerialNo, A.Name, B.month,
max(case when A.Month_ID=B.Month_ID then A.Amount else NULL end) Amount
from Emp_Table A, Month_Table B
group by A.SerialNo, A.Name, B.month, B.Month_ID
order by A.Name desc, B.Month_ID

[bishwarup]

with recursive base as(
/*Anchor part */
select Serialno, Name, 1 as Month_id
from Emp_Table
UNION
/*Recursive part */
select Serialno, Name, Month_id+1 as Month_id
from base where Month_id+1 <= 10
),
cte2 as(
select b.SerialNo, b.Name, b.Month_id, e.Amount from base b
left join Emp_Table e
on b.month_id = e.month_id
and b.Name = e.Name
order by b.Name desc, b.month_id)
select a.*, b.Month from cte2 a
inner join Month_Table b on a.month_id = b.month_id
order by b.Name desc, b.month_id;