L &T Infotech SQL Interview Question | SQL Intermediate Question 12

[@jjayeshpawar]

select dep_id, max(salary), min(salary)
from em
GROUP by dep_id

[@rajkumarrajan8059]

is it working, when i tried this query it throws an error

[@rajkumarrajan8059]

thereby used this query
SELECT
distinct DEP_ID
,MAX(SALARY) OVER (PARTITION BY DEP_ID ) AS [MAX_SALARY]
,MIN(SALARY) OVER (PARTITION BY DEP_ID ) AS [MIN_SALARY]
FROM EMPLOYEE

[@sidmoitra007]

@@rajkumarrajan8059
select dep_id, MAX(salary) as max_sal, Min(salary) as min_sal
From employee
Group by dep_id
working perfectly fine

[@dhiraj_sahu_96]

This is the simplest and effective query, no need to conplicate things

[@themightyvk]

select dep_id,max(salary), min(salary) from employee group by dep_id

[@rajkumarrajan8059]

SELECT
distinct DEP_ID
,MAX(SALARY) OVER (PARTITION BY DEP_ID ) AS [MAX_SALARY]
,MIN(SALARY) OVER (PARTITION BY DEP_ID ) AS [MIN_SALARY]
FROM EMPLOYEE

[@dhiraj_sahu_96]

Instead of window function just use group by

[@Mohdibran-j8x]

Simple Query
select Dep_id,avg(salary) as avg_salary,min(salary) as Min_salary, Max(salary) as max_salary from table name
group by Dep_id
order by dep_id

[@Skd833]

select [dep_id],max([salary]) as higest_salary, min([salary]) as lowest_salary from [Sambit].[dbo].[emp]
group by [dep_id]

[@themightyvk]

With cte as(
select *, min(salary) over(partition by dep_id order by dep_id) as min_salary,
max(salary) over(partition by dep_id order by dep_id) as max_salary
from employee)
select distinct dep_id, min_salary, max_salary from cte

[@krishnakishore993]

Hi
Select dep_id,MAX(salary) as Highestsalary,MIN(salary) as Lowest _salary from employees
Group by dep_id
I got output from this query.
Is it write way or need to follow Cte and ranking,joins
Please let me know
Thanks 😊

[@Code-Con]

Absolutely a right approach 👏

[@rajm5349]

i have doubt what type of join is this , is it simple join , does it lead to cartesian product

[@Code-Con]

it's inner join

[@DEwithDhairy]

LnT PySpark Interview Question and Answer:
ruclips.net/video/Kuv_d5uybIU/видео.htmlsi=_1KLGDMhIPXCB52f

[@abhinavkumar2662]

We can check by use of Max and min..select Max(salary)from employee

[@Code-Con]

Yeah that's a better approach 👏

[@jayavani2223]

SELECT D.dep_Name, MAX (salary) AS highest_salary, MIN(Salary) AS lowest_salary
FROM EMployees E
LEFT JOIN DEPARTMENTS D ON E.DEP_ID = D.DEPT_ID_DEP
GROUP BY D.DEP_NAME
Please let me know is it correct or wrong

[@Tech_with_Srini]

with cte as (
select empNo,eName,sal,deptno , max(sal) over (partition by deptno) as max_sal ,
min(sal) over (partition by deptno) as Min_sal
from emp2)
select c1.empNo,c1.eName,c1.deptno,c2.max_sal,c2.min_sal from cte c1 join cte c2
on c1.empno=c2.empno
where c1.sal= c2.max_sal or c1.sal=c2.min_sal
order by c1.deptno