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

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

Input :- Emp_Detail Table four columns namely EmpName , Age, Salary and Department
Problem Statement :- Write a SQL to find Top 2 records from each department

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

Insert Scripts
------------------------
Create table Emp_Detail
(
EmpName Varchar(25),
Age int,
Salary Bigint,
Department Varchar(20)
)

Insert into Emp_Detail Values('James',25,25000,'Admin')
Insert into Emp_Detail Values('Robert',33,39000,'Admin')
Insert into Emp_Detail Values('Richard',41,48000,'Admin')
Insert into Emp_Detail Values('Thomas',28,30000,'Admin')
Insert into Emp_Detail Values('Tom',40,55000,'Finance')
Insert into Emp_Detail Values('Donald',35,38000,'Finance')
Insert into Emp_Detail Values('Sara',32,44000,'Finance')
Insert into Emp_Detail Values('Mike',28,25000,'HR')
Insert into Emp_Detail Values('John',35,45000,'HR')
Insert into Emp_Detail Values('Mary',23,30000,'HR')
Insert into Emp_Detail Values('David',32,43000,'HR')

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

[GanesanDinesh]

Thanks for your videos

;with cte_1 as
(
select *, row_number() over (partition by department order by salary desc) max_2 from Emp_Detail
)
select empname, age, salary, department from cte_1 where max_2 in (1, 2)

[amolpayghon]

row no won, t work in case of duplicate salary within a department. use dense_rank
select * from(
select e.*, dense_rank() over (partition by dept order by salary desc) drank from emp e ) where drank in (1, 2);

[saran.n]

Gud One...

Can you also please upload some vidoes for validating various file formats like .csv, .txt, .dat, .jason, .xml etc

[nihal]

asked in dunzo, shit i missed your videos man

[susmitapanigrahi]

Nice Question, I have solved this question by using Dense_Rank() using subquery.

select A.EmpName, A.Age, A.salary, A.Department from
(select Empname, Age, Salary, Department, dense_rank() over (partition by department order by salary desc) as Ranking from Emp_detail) A
where A.Ranking<=2;

[faizraina]

with cte As(
select empname, age, salary, department, dense_rank()over(partition by department order by salary desc ) as r
from Emp_Detail)

select empname, age, salary, department
from cte
where r <= 2

[gaganhs]

Hey, we can't use row_number, we just use dense_rank instead

[notavi]

select empname, age, salary, department from (
select empname, age, salary, department, rank () over (partition by department order by salary desc) rk
from Emp_Detail
)
where rk <= 2
;

[prabhatgupta]

select empname, age, salary,department from (select *, dense_rank()over(partition by department order by salary desc ) as rk from Emp_Detail)x
where x.rk<=2;

[soumeshkayast]

SELECT
A.EMP_NAME,
A.AGE,
A.SALARY,
A.DEPARTMENT
FROM
(SELECT
EMP_NAME,
AGE,
SALARY,
DEPARTMENT,
DENSE_RANK()OVER(PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS RANKING
FROM SCENARIO_25)A
WHERE A.RANKING<=2;

[cleversachin]

Row number won't work in case of duplicate salary within a department. I would use below


with preparedata as (select Empname, Age, Salary, Department, dense_rank() over (partition by department order by salary desc) as Ranking from Emp_detail)
select A.EmpName, A.Age, A.salary, A.Department from preparedata A
where A.Ranking<=2;