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

SQL Interview Questions and answers Part 32 | SQL Scenario based Interview Question and Answer
Input :- Employee Table has four columns namely EmpName, DeptName, DeptNo and Salary
Problem Statement :- Write a SQL query to get the output as shown in the Output tables
-------------------------------------------------------------------------
Follow me on Social Networking Sites :
-------------------------------------------------------------------------
*Twitter:
*Instagram :

-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------
Create Table Employee_2(
EmpName Varchar(30),
DeptName Varchar(25),
DeptNo Bigint,
Salary Bigint);

Insert into Employee_2 Values('Mark','HR',101,30000);
Insert into Employee_2 Values('John','Accountant',101,20000);
Insert into Employee_2 Values('Smith','Analyst',101,25000);
Insert into Employee_2 Values('Donald','HR',201,40000);
Insert into Employee_2 Values('James','Analyst',201,22000);
Insert into Employee_2 Values('Maria','Analyst',201,38000);
Insert into Employee_2 Values('David','Manager',201,33000);
Insert into Employee_2 Values('Martin','Analyst',301,22000);
Insert into Employee_2 Values('Robert','Analyst',301,56000);
Insert into Employee_2 Values('Michael','Manager',301,34000);
Insert into Employee_2 Values('Robert','Accountant',301,37000);
Insert into Employee_2 Values('Michael','Analyst',301,28000);

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

[kanchannatekar]

Thank you so so much for creating such a great question series.

[ayushipatra]

My solution considering there is no records with more than one max() or min() Salary:

select Name, Dept, DeptNo, min(Salary)
From Employee
Group by DeptNo, Dept, Name

Union all


Select Name, Dept, DeptNo, Max(Salary)
From Employee
Group by DeptNo, Dept, Name

[livelylife]

another way
select * from Employee_2 where salary in(select max(salary) from Employee_2 group by DeptNo)
union all
select * from Employee_2 where salary in(select min(salary) from Employee_2 group by DeptNo) order by DeptNo, salary

[sonurawat]

select * from employee_2
where salary in(select max(salary) from employee_2
group by deptno
union select min(salary) from employee_2
group by deptno )
order by deptno, deptname;

[aditichaudhary]

please remove the introductory music.Its painful to years.
your knowledge is great but pls remove music

[prajjwaljaiswal]

MySQl using Joins and union :

select e.EmpName, e.DeptName, e.DeptNo as DeptNo, A.sal as Salary from employee_2 e
inner join (select DeptNO, min(salary) as sal
from employee_2
group by DeptNO) A on e.DeptNo = A.DeptNo and e.Salary = A.sal
union
select e.EmpName, e.DeptName, e.DeptNo as DeptNo, B.sal as Salary from employee_2 e
inner join (select DeptNO, Max(salary) as sal
from employee_2
group by DeptNO) B on e.DeptNo = B.DeptNo and e.Salary = B.sal
order by DeptNo, salary;

[bishwarup]

with cte as(
select
*
, case when Salary = max(Salary) over(partition by DeptNo) then 1 else 0 end as max_sal_dep
, case when Salary = min(Salary) over(partition by DeptNo) then 1 else 0 end as min_sal_dep
from Employee_2)
select
EmpName
, DeptName
, DeptNo
, Salary
from cte where
max_sal_dep+min_sal_dep =1
order by DeptNo, Salary;

[moizshaikh]

WIth tab as (
SELECT --EmpName, DeptName
DeptNo--, Salary
, MAX(Salary) as Max_salary
FROM Employee_2
group by DeptNo
UNION ALL
SELECT --EmpName, DeptName
DeptNo--, Salary
, MIN(Salary) as Max_salary
FROM Employee_2
group by DeptNo
)

SELECT DISTINCT a.EmpName, a.DeptName, a.DeptNo, a.Salary, b.Max_salary FROM Employee_2 as a
LEFT JOIN tab as b ON a.Salary = b.Max_salary
WHERE b.Max_salary IS NOT NULL

[harshrr]

select EmpName, DeptName, DeptNo, Salary from
(select *
, min(salary) over (partition by deptno) Min_salary
, max(salary) over (partition by deptno) Max_salary
from Employee_2
) a
where salary in (Min_salary, Max_salary)
order by 3, 4

[susmitapanigrahi]

Nice Question. :)

Here is my solution to this question.

select E.EmpName, E.DeptName, E.DeptNo, E.Salary from
(select EmpName, DeptName, DeptNo, Salary, Dense_Rank() over(partition by Deptno order by Salary) as Min_Sal_Rank,
Dense_Rank() over(partition by Deptno order by Salary desc) as Max_Sal_Rank from Emp2) E
where E.Min_Sal_Rank=1 or E.Max_Sal_Rank=1 order by E.DeptNo;

[LiveWithDebasmita]

with cte as
(
select DeptNo, max(Salary) as Salary from Employee_2 group by DeptNo
union all
select DeptNo, min(Salary) as Salary from Employee_2 group by DeptNo
)
select a.*
from Employee_2 as a
inner join cte as b
on a.DeptNo = b.DeptNo and a.Salary = b.salary
order by a.DeptNo, a.Salary;

[tusartarai]

here is my solution

with new_table1
as
(
select *,
ROW_NUMBER() over(partition by deptno order by salary) as rn
from part_32_Employee_2
),
new_table2
as
(
select *,
min(rn) over(partition by deptno) as mi,
max(rn) over(partition by deptno) as mx
from new_table1
)
select EmpName, DeptName, DeptNo, salary from new_table2
where rn=mi or rn=mx

[mritunzaysingh]

1.Solution using case STATEMENT

select empname, deptname, deptno, salary
FROM
(
select *, case when salary = max(salary) over (PARTITION by deptno order by deptno) then empname end as max_salary_emp,
case when salary = min(salary) over (PARTITION by deptno order by deptno) then empname end as min_salary_emp
from employee_2
) A
where empname = min_salary_emp or empname = max_salary_emp

2.Solution using WINDOW FUNCTION

select empname, deptname, deptno, salary
FROM
(
select *, rank() over (PARTITION by deptno order by salary desc) as top_rank,
rank() over (PARTITION by deptno order by salary asc) as bottom_rank
from employee_2
) A
where A.top_rank = 1 or A.bottom_rank = 1 ;

[anirbanbiswas]

BE ACHEIVED EASILY BY ROW_NUMBER()
with cte as(select *, row_number() over(partition by deptno order by salary)as rn from Employee_2),
cte2 as( select *, row_number() over(partition by deptno order by salary desc)as rn2 from Employee_2)
select empname, deptname, deptno, salary from cte where rn=1
union all
select empname, deptname, deptno, salary from cte2 where rn2=1

[pratikdhok]

Nice explanation, thnks for such videos . please provide more in future

[ranjitsingh]

Here is without using window functions

select a.EmpName, a.DeptName, a.DeptNo, a.Salary from
employee_2 a
inner join
(
select max(salary) as max_Sal, deptNo from employee_2 group by deptNo) b on a.deptNo=b.deptNo
inner join
(
select min(salary) as min_Sal, deptNo from employee_2 group by deptNo) c on a.deptNo=c.deptNo
where a.salary=b.max_sal or a.salary=c.min_sal

[krishnamanda]

Hi Sunil, could you please make help me on how to convert comma separated values in a column to individual rows ? Like if a table has category & sub category id as columns and each row has values like soap | 10, 20, 30 then how to convert them into 3 rows ?

[DEEPAKSINGH-xbgk]

PLEASe note:
Select * from (
Select EMPNAME, DEPTNAME, DEPTNO, case when salary=min1 then min1 when salary=max1 THEN max1 end SalQ from(
Select *, MIN(salary) over(partition by Deptno order by salary) min1
, MAX(salary) over(partition by Deptno order by salary desc) max1
from Employee_2_new) B)m where salq is not null order by DeptNo, SALQ

[navratanagarwal]

select empname, deptname, deptno, salary from (select *, MIN(salary) over(partition by deptno) as a, MAX(salary) over(partition by deptno) as b from employee_2) x where salary In(a, b) order by deptno, salary

[digitaltechconnect]

please ping me the create and insert queries for practice